新功能初探 | MySQL 8.0 Multi-Valued Indexes功能簡述

顧名思義，索引上對于同一個Primary key, 可以建立多個二級索引項，實際上已經對array類型的基礎功能做了支援，并基于array來建構二級索引。

這意味着該二級索引的記錄數可以是多于聚集索引記錄數的，因而該索引不可以用于通常意義的查詢，隻能通過特定的接口函數來使用，下面的例子裡會說明。

關注公衆号“阿裡資料庫技術”，回複“MySQL”擷取相關文檔。

範例

摘錄自官方文檔

*請左右滑動閱覽

root@test 04:08:50>show create table customers\G                                                                                                                                  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `custinfo` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zips` ((cast(json_extract(`custinfo`,_latin1'$.zip') as unsigned array)))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@test 04:08:53>select * from customers;
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-08-14 16:08:50 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-08-14 16:08:50 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94536]}         |
|  4 | 2019-08-14 16:08:50 | {"user": "Mary", "user_id": 72, "zipcode": [94536]}               |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)
           

通過如下三個函數member of, json_contains, json_overlaps可以使用到該索引

root@test 04:09:00>SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@test 04:09:41>SELECT * FROM customers  WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@test 04:09:54>SELECT * FROM customers   WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-08-14 16:08:50 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)           

接口函數

multi-value index是functional index的一種實作，列的定義是一個虛拟列，值是從json column上取出來的數組。

數組上存在相同值的話，會隻存儲一個到索引上。支援的類型：DECIMAL, INTEGER, DATETIME,VARCHAR/CHAR。另外index上隻能有一個multi-value column。

下面簡單介紹下相關的接口函數

數組最大容量：

入口函數：

ha_innobase::mv_key_capacity

插入記錄:

row_ins_sec_index_multi_value_entry

通過類Multi_value_entry_builder_insert來建構tuple, 然後調用正常的接口函數row_ins_sec_index_entry插入到二級索引中。

已經解析好，排序并去重的資料存儲在結構struct multi_value_data , 指針在dfield_t::data中. multi_value_data結構也是multi-value具體值的記憶體表現

删除記錄：

入口函數:

row_upd_del_multi_sec_index_entry

基于類Multi_value_entry_builder_normal建構tuple, 并依次從索引中删除

更新記錄

row_upd_multi_sec_index_entry

由于可能不是所有的二級索引記錄都需要更新，需要計算出diff，找出要更新的記錄calc_row_difference --> innobase_get_multi_value_and_diff, 設定一個需要更新的bitmap

事務復原

相關函數：

row_undo_ins_remove_multi_sec
row_undo_mod_upd_del_multi_sec
row_undo_mod_del_mark_multi_sec           

復原的時候通過trx_undo_rec_get_multi_value從undo log中擷取multi-value column的值，通過接口Multi_value_logger::read來建構并存儲到field data中

記錄undo log

函數: trx_undo_store_multi_value

通過Multi_value_logger::log将multi-value的資訊存儲到Undo log中. 'Multi_value_logger'是一個輔助類，用于記錄multi-value column的值以及如何讀出來

purge 二級索引記錄

row_purge_del_mark
row_purge_upd_exist_or_extern_func
    |--> row_purge_remove_multi_sec_if_poss