Environmental preparation

Database environment: mysql 5.7.20

Data preparation:

1. Test_json table, which stores 100,000 rows of data with JSON column values of 10 elements

1. The test_varchar_index table, which stores 1 million pieces of data, does not contain json columns, and does not index columns
2. The test_varchar_noindex table, which stores 1 million pieces of data and does not contain json columns, is indexed

The data script is as follows:

delimiter $$
drop procedure if exists varcharDataready;
create procedure varcharDataready()
BEGIN
declare i int(11);
set i = 1; 
WHILE i <= 1000000 do
insert INTO test_varchar_index(name) VALUES(REPLACE(UUID(),'-',''));
insert INTO test_varchar_noindex(name) VALUES(REPLACE(UUID(),'-',''));
set i = i + 1;
end WHILE;
end $$

drop procedure if exists dataready;
create procedure dataready()
BEGIN
declare i int(11);
set i = 1; 
WHILE i < 100000 do
insert INTO test_json(name) VALUES(JSON_ARRAY(REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-','')
));
set i = i + 1;
end WHILE;
end $$
delimiter ;

CALL varcharDataready();
call dataready();

test

1. Query the name column of the specified value:

   • Query the test_json table:

     SELECT id FROM test_json where JSON_CONTAINS(name -> '$[*]','["e705da2cd3bd11e986f6e0d55ea04e32"]')
     
     # Running result is about 0.8s (multiple runs)
     

   • Query test_varchar_no index

     select id from test_varchar_noindex where name = 'e7056b78d3bd11e986f6e0d55ea04e32'
     # Running result is about 0.3s (multiple runs)
     

   • Query test_varchar_index

     select id from test_varchar_index where name = '000255bdd3bd11e986f6e0d55ea04e32'
     # Running result is about 0.02s (multiple runs)
     

After reading about mysql json column indexing, you need to create virtual columns, because the data here is using json arrays, so you can't do it. Creating virtual columns should be for json objects, which requires more consideration of table design.