Starting from Mysql8.0.17, it supports adding multi-value indexes on json columns. Multi-value indexing will add multiple index records to a record. When searching, the record can be quickly positioned through the index.
To use multi-value indexing, first check whether the version is supported by select version().
Create and query JSON fields
Mysql's json type is schemaless and can insert any data that conforms to the json format, including arrays and objects.
For example, there is a user table, including user tags and extended information (extInfo), both are json types. The tag stores a string array, and the extInfo stores an object containing age (value) and address (string).
create table demo_user
(
name varchar(64),
tag json,
extInfo json
);
Without indexing, any data that conforms to the json format can be inserted, and the content recorded in each line can be different.
insert into demo_user(name, tag, extInfo) values ('Xiao Ming', '["t1","t2"]', '{"age": 20, "address": "a1"}');
insert into demo_user(name, tag, extInfo) values ('Xiaohong', '["t1","t2"]', '{"age": 20, "address": 20}');
insert into demo_user(name, tag, extInfo) values ('Xiao Li', '["t1","t2"]', '{"age": "aaa", "address": "a1"}');
insert into demo_user(name, tag, extInfo) values ('Xiaohua', '{"age": "aaa", "address": "a1"}', '{"age": "aaa", "address": "a1"}');
select * from demo_user;
+------+-------------------------------+-------------------------------+
|name |tag |extInfo |
+------+-------------------------------+-------------------------------+
|Xiao Ming |["t1", "t2"] |{"age": 20, "address": "a1"} |
|Xiaohong |["t1", "t2"] |{"age": 20, "address": 20} |
|Xiao Li |["t1", "t2"] |{"age": "aaa", "address": "a1"}|
|Xiaohua |{"age": "aaa", "address": "a1"}|{"age": "aaa", "address": "a1"}|
+------+-------------------------------+-------------------------------+The index has not been added yet. The index will be scanned by the full table according to the tag, age, and adress query.
explain select * from demo_user where json_contains(tag, '"t1"');
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |ALL |null |null|null |null|4 |100 |Using where|
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
explain select * from demo_user where json_contains(extInfo->'$.age', '20');
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |ALL |null |null|null |null|4 |100 |Using where|
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
explain select * from demo_user where json_contains(extInfo->'$.address', '"a1"');
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |ALL |null |null|null |null|4 |100 |Using where|
+--+-----------+---------+----------+----+-------------+----+-------+----+----+--------+-----------+
Add multi-value index
The way to add multi-value indexes is similar to adding a normal index. You can add an index through create table, alter table or create index. The only difference is that there are differences in the description of the index column. json is a schemaless. When creating an index, you must clarify which key to index and tell the column what type it is.
Convert json to the specified type ARRAY through cast(expr AS type [ARRAY]). column is the json column that needs to be indexed, and type is the type of key in json.
There are many optional values for type, we need to use strings and plastic shaping, and the others are described in the official documentation[cast function]
- char(n), convert to a varchar type string
- unsigned, convert to unsigned bigint
-- Convert the value in the tag to a character type
alter table demo_user add index tag( (cast(tag as char(64) array)) );
-- Convert the age in extInfo into unsigned plastic surgery
alter table demo_user add index age( (cast(extInfo->'$.age' as unsigned array)) );
-- Convert the address in extInfo into character type
alter table demo_user add index address( (cast(extInfo->'$.address' as char(255) array)) );At this time, because the data inserted previously may not be converted to the corresponding type, an error will be reported when creating the index.
For example, when adding an index to a tag, because the tag value of the fourth record is {"age": "aaa", "address": "a1"}, it prompts that the JSON Object type cannot be converted to the array type.
alter table demo_user add index tag( (cast(tag as char(64) array)) );
---
[42000][1235] This version of MySQL doesn't yet support 'CAST-ing JSON OBJECT type to array'
When adding an index to an age, the data is prompted to be truncated because the value of the age is "aaa" in the third item, and it is empty to convert it into an integer.
alter table demo_user add index age( (cast(extInfo->'$.age' as unsigned array)) );
---
[22001][3903] Data truncation: Invalid JSON value for CAST for functional index 'age'.
After we correct the data, we can add the index normally. Then check to execute explain to see if the query is indexed:
explain select * from demo_user where json_contains(tag, '"t1"');
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type |possible_keys|key|key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |range|tag |tag|259 |null|4 |100 |Using where|
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
explain select * from demo_user where json_contains(extInfo->'$.age', '20');
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type |possible_keys|key|key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |range|age |age|9 |null|2 |100 |Using where|
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
explain select * from demo_user where json_contains(extInfo->'$.address', '"a1"');
+--+-----------+---------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |range|address |address|1023 |null|3 |100 |Using where|
+--+-----------+---------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
You can see that all three query statements have reached the index.
In addition to json_contains can index, member of() and json_overlapses() can also use indexes, but the functions of these functions are different. For details, you need to query the document description.
explain select * from demo_user where 't1' member of (tag);
+--+-----------+---------+----------+----+-------------+---+-------+-----+----+--------+-----------+
|id|select_type|table |partitions|type|possible_keys|key|key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+----+-------------+---+-------+-----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |ref |tag |tag|259 |const|4 |100 |Using where|
+--+-----------+---------+----------+----+-------------+---+-------+-----+----+--------+-----------+
explain select * from demo_user where JSON_OVERLAPS(tag, '"t1"');
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
|id|select_type|table |partitions|type |possible_keys|key|key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
|1 |SIMPLE |demo_user|null |range|tag |tag|259 |null|4 |100 |Using where|
+--+-----------+---------+----------+-----+-------------+---+-------+----+----+--------+-----------+
Summarize
The json column can add multi-value indexes, which improves the query performance of json. At the same time, because the index needs to specify the type of the column, the index column cannot use any type, which will lose some of the schemaless capabilities. It should be noted that when adding indexes using the cast function, you should select the appropriate type, otherwise an exception of type conversion failure will occur.
refer to
JSON type documentation:MySQL :: MySQL 8.3 Reference Manual :: 11.5 The JSON Data Type
Multi-value index documentation:MySQL :: MySQL 8.3 Reference Manual :: 13.1.15 CREATE INDEX Statement
cast function documentation:MySQL :: MySQL 8.3 Reference Manual :: 12.10 Cast Functions and Operators