How to create an index for JSON fields in MySQL

Introduction to Multi-Valued Indexes

Starting from MySQL 8.0.17, InnoDB supports the creation of multi-valued indexes (Multi-Valued Indexes) , this index is a secondary index defined on the column of the JSON storage value array. There can be multiple index records for a single data record. The specific syntax definition for this type of index:

CAST(expression AS type ARRAY), for example, CAST(data->'$.zipcode' AS UNSIGNED ARRAY). Like ordinary indexes, they can also be viewed in EXPLAIN.

Create a multi-valued index

Like other indexes, a multi-valued index can be added when creating a table, or created through ALTER TABLE or CREATE INDEX.

JSON Object Field Index

Syntax

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );

Note: There are two layers of single brackets outside the CAST syntax! , if you write one less, an error will be reported!

Test Case

PS: The cases in the article refer to the cases in the official documents and are just for testing, so the naming and other aspects are not very standardized. They must be strict in the actual development process. Follow the development specifications of the company team and don’t be lazy!

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '{"key":94582,"value":"asdf"}' ),
	( NULL, NOW(), '{"key":94568,"value":"gjgasdasdf"}' ),
	( NULL, NOW(), '{"key":94477,"value":"ghasdfsdf"}' ),
	( NULL, NOW(), '{"key":94536,"value":"hagsdfgdf"}' ),
	( NULL, NOW(), '{"key":94507,"value":"wasfgjdf"}' );
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$.key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$.key',
	CAST( '[94582]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$.key',
	CAST( '[94477]' AS JSON ));

Check the execution plan and find that the index can be used:

If you need to create a multi-value index for the character type, it must be the utf8mb4 character set And the sorting rule is utf8mb4_0900_as_cs, otherwise an error will be reported. This version does not support it:

If you want to create a multi-value index for a binary binary string, the sorting rule must be binary, otherwise an error will be reported and it is not supported.

After modifying the sorting rules, the index can be added successfully:

JSON array object index

Syntax

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array ) ) );

Note: There are two layers of single brackets outside the CAST syntax! If you write one less, an error will be reported!

Test case

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, NOW(), '[{"key":94536}]'),
	( NULL, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$[*].key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$[*].key',
	CAST( '[94582, 94507]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$[*].key',
	CAST( '[94477, 94582]' AS JSON ));

View the execution plan and find that the index can be used:

Created in the combined index Multi-value index

Syntax

The syntax is similar to that of ordinary combined indexes. It also follows the leftmost matching principle:

ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified
( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );

Note: You need to use parentheses outside the CAST syntax here. stand up!

Test case

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	age tinyint(4) not null,
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, 21, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, 22, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, 23, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, 24, NOW(), '[{"key":94536}]'),
	( NULL, 25, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
alter table customers DROP INDEX idx_age_custinfo$list_modified ;
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )),modified );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ((CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), age,modified  );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age,modified, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;

View the execution plan and find that the index can be used:

Limitations of multi-valued indexes

• A multi-valued index is only allowed to contain the value of one attribute

• This index currently only supports three Syntax

Currently, only three syntaxes: MEMBER OF, JSON_CONTAINS(), and JSON_OVERLAB() can use multi-value indexes.

• The index value must be converted into an array

##( CAST( custinfo -> '$.key' AS UNSIGNED array)), the array in the syntax can be omitted. The reason why it is forced to be added is because if it is not added, it is not an array structure. If it is not an array structure, the above three syntaxes cannot be used directly. It needs to be converted through JSON_ARRAY() and other methods. It can only be used later, which will cause the index to become invalid! Therefore, regardless of whether the field to be indexed is a single value field or an array field, the array keyword must be added.

• This index does not support table association

• Cannot be combined with prefix index

• Does not support online creation of multi-value indexes

This sentence means that the operation uses ALGORITHM=COPY, that is, through Create a new table structure and then copy the data to create the index. Therefore DML operations are not allowed during this process.

• Multi-valued indexes have clear requirements for character set type fields

The collation rules of the binary character set must be binary

The collation of the utf8mb4 character set must be utf8mb4_0900_as_cs

Any other character set or collation cannot create a multi-value index, and an error will be reported when creating it. The current version does not support it.

Application scenarios

The application scenarios of multi-value index are very wide! With him, many relationship tables can no longer be used! Let’s take a simple example: user tags. In many scenarios, users will be given various tags, such as 1 tall, 2 rich, 3 handsome. In order to make subsequent statistics or filtering queries more efficiently, we cannot directly use this tag as a field. Storage, because query efficiency is not high without an index, an association table is often used to store the user-tag relationship. But now with multi-valued indexes, we can store the tag as a field!

This is just one of the small scenes. There are many similar scenes. The user can change it to anything, and the label can also be changed to any other attribute. As long as the thing has multiple attribute values, there is a many-to-many relationship. Then if there is no need for this attribute to be associated with other tables), you can use multi-valued indexes! Multi-valued indexes do not support table association, so it is not appropriate if you need to use this field for table association.

The above is the detailed content of How to create an index for JSON fields in MySQL. For more information, please follow other related articles on the PHP Chinese website!