As the name implies, multiple secondary index entries can be created for the same Primary key in the index. In fact, the basic functions of array type have been supported, and the secondary index is built based on array.
This means that the number of records in the secondary index can be more than the number of records in the clustered index, so the index can not be used for queries in general sense, and can only be used through specific interface functions, as shown in the following example.

Example

Excerpt from official documents
*Please swipe left and right

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)

The index can be used by the following three functions: member of, JSON contain, and JSON overlaps

*Please swipe left and right

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)

Application program interface

Multi value index is an implementation of functional index. The definition of column is a virtual column, and the value is an array taken from json column.

If the same value exists on the array, only one will be stored on the index. Supported types: DECIMAL, INTEGER, DATETIME,VARCHAR/CHAR. In addition, there can only be one multi value column on the index.
Here is a brief introduction to the relevant interface functions

Maximum capacity of array:

Entry function:
ha_innobase::mv_key_capacity

Insert record:

Entry function:
row_ins_sec_index_multi_value_entry
The tuple is built through the class Multi_value_entry_builder_insert, and then the normal interface function row_ins_sec_index_entry is called into the two level index.
The sorted and de duplicated data is stored in the structure struct multi value data, and the pointer is in the field:: data. The multi value data structure is also the memory representation of the specific value of multi value

Delete record:

Entry function:
row_upd_del_multi_sec_index_entry
Build a tuple based on the multi value entry builder normal class, and then remove it from the index

Update record

Entry function:
row_upd_multi_sec_index_entry
Since not all the secondary index records need to be updated, it is necessary to calculate the diff, find out the record to be updated, call "row" difference -- > innobase "get" multi value "and" diff, and set a bitmap to be updated

Transaction rollback

Correlation function:

row_undo_ins_remove_multi_sec
row_undo_mod_upd_del_multi_sec
row_undo_mod_del_mark_multi_sec

During rollback, obtain the value of multi value column from undo log through TRX undo rec get multi value, and build and store it in field data through the interface multi value logger:: read

Record undo log

Function: TRX undo store multi value
The information of multi value is stored in Undo log through multi value ﹣ logger:: log. 'multi value ﹣ logger' is an auxiliary class used to record the value of multi value column and how to read it

purge secondary index record

Entry function:
*Please swipe left and right

row_purge_del_mark
row_purge_upd_exist_or_extern_func
    |--> row_purge_remove_multi_sec_if_poss