mysql json数据格式的查询性能测试

环境准备

数据库环境：mysql 5.7.20

数据准备：

1 . test_json表，存储10万行数据，每行数据的json列值为10个元素的json数组

2. test_varchar_index表，存储100万条数据，不包含json列，并且不针对列建立索引
3. test_varchar_noindex 表，存储100万条数据，不包含json 列，但建立索引

数据脚本如下：

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();

测试

1. 查询指定值的 name 列:

   • 查询 test_json 表：

     SELECT id FROM test_json where JSON_CONTAINS(name -> '$[*]','["e705da2cd3bd11e986f6e0d55ea04e32"]')
     
     # 运行结果 0.8s左右（多次运行）
     

   • 查询 test_varchar_noindex

     select id from test_varchar_noindex where name = 'e7056b78d3bd11e986f6e0d55ea04e32'
     # 运行结果 0.3s左右（多次运行）
     

   • 查询 test_varchar_index

     select id from test_varchar_index where name = '000255bdd3bd11e986f6e0d55ea04e32'
     # 运行结果 0.02s左右（多次运行）
     

研读了一下mysql json列建立索引是需要创建虚拟列的，由于这里的数据用的是json 数组，就没法去做了。创建虚拟列应该是针对的json 对象吧，这个需要多考虑考虑表的设计了。

如果你觉得我的文章对你有所帮助的话，欢迎关注我的公众号。赞！
认认真真学习，做思想的产出者，而不是文字的搬运工。错误之处，还望指出！