mysql 5.7 和 8.0 json 类型 json 数组类型普通字符串类型 10w数据查询速度差异

mysql 5.7.10

json 非数组

建表语句ddl

CREATE TABLE tb_json_test (
  id INT NOT NULL AUTO_INCREMENT,
  user_no VARCHAR(100),
  user_name VARCHAR(100),
  score INT,
  create_time date,
  update_time date,
  remark VARCHAR(100),
  field1 VARCHAR(100),
  field2 VARCHAR(100),
  field3 VARCHAR(100),
  field4 VARCHAR(100),
  field5 VARCHAR(100),
  field6 VARCHAR(100),
  field7 VARCHAR(100),
  field8 VARCHAR(100),
  json_field json,
  PRIMARY KEY (id)
);

10w 数据插入存储过程

create PROCEDURE `demo02`.`jsonQueryTest`()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 2011;
    DECLARE user_name VARCHAR(20);
    DECLARE user_no VARCHAR(20);
    DECLARE score INT;
    DECLARE create_time DATETIME DEFAULT NOW();
    DECLARE update_time DATETIME;
    DECLARE remark VARCHAR(50);
    declare field1 VARCHAR(100); 
    declare field2 VARCHAR(100); 
    declare field3 VARCHAR(100); 
    declare field4 VARCHAR(100); 
    declare field5 VARCHAR(100); 
    declare field6 VARCHAR(100); 
    declare field7 VARCHAR(100); 
    declare field8 VARCHAR(100); 
    declare id VARCHAR(100); 
    declare json_field json;
        set autocommit=0; -- 关闭自动提交事务，提高插入效率
    
    WHILE i <= 100000 DO
        SET user_name = CONCAT(
            SUBSTRING('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水竺苏潘范雷', FLOOR(RAND() * 54) + 1, 1),
            SUBSTRING('安宝彪彬冰博财成程达德东斗政法菲飞丰歌根光国海恒弘鸿宏洪华晖惠建健金景', FLOOR(RAND() * 30) + 1, 1),
            SUBSTRING('静俊凯克莉良亮林玲龙茂梅民敏明娜宁鹏平奇琪全仁荣瑞森帅顺涛韬', FLOOR(RAND() * 30) + 1, 1)
        );
        SET user_no = CONCAT(j, LPAD(i, 7, '0'));
        SET score = FLOOR(RAND() * 101);
        SET remark = CONCAT('remark_', i);
        SET update_time = DATE_ADD(create_time, INTERVAL FLOOR(RAND() * 100) DAY); -- update_time则随机生成在create_time基础上加上一定天数的时间。
        set field1 = replace(uuid(),"-","");
           set field2 = replace(uuid(),"-","");
        set field3 = replace(uuid(),"-","");
        set field4 = replace(uuid(),"-","");
        set field5 = replace(uuid(),"-",""); 
        set field6 = replace(uuid(),"-","");
        set field7 = replace(uuid(),"-","");
        set field8 = replace(uuid(),"-","");
        set id = i;
        set json_field = JSON_OBJECT('user_no', user_no, 'user_name', user_name, 'score', score);
        INSERT INTO demo02.tb_json_test(id, user_no, user_name, score, create_time, update_time, remark, field1, field2, field3, field4, field5, field6, field7, field8, json_field)
        VALUES(id, user_no, user_name, score, create_time, update_time, remark,field1, field2, field3, field4, field5, field6, field7, field8, json_field);
        
                SET create_time = DATE_ADD(create_time, INTERVAL 1 SECOND); -- create_time初始值为当前时间，每生成一行数据就自增1分钟，以保证创建时间的递增。
        SET i = i + 1;
        IF i % 100000 = 0 THEN
            SET j = j + 1;
        END IF;
    END WHILE;
end

call jsonQueryTest();

json 类型 vs 普通字符串类型

-- json 非数组
-- 40 条记录 无索引 多次查询 400多ms 
select * from tb_json_test where user_name = '博玲'
-- 创建 user_name 索引
CREATE INDEX idx_user_name ON tb_json_test(user_name);
-- 40 条记录 有索引 多次查询 3左右ms 
select * from tb_json_test where user_name = '博玲'
-- 40 条记录 无索引 json 多次查询 700多ms 
select * from tb_json_test where JSON_CONTAINS(json_field -> '$.user_name', '"博玲"')
-- json类型 字段 创建虚拟列索引
ALTER TABLE tb_json_test ADD COLUMN v_user_name VARCHAR(255) AS (JSON_EXTRACT(json_field, '$.user_name')) VIRTUAL;
CREATE INDEX idx_v_user_name ON tb_json_test(v_user_name);
-- 40 条记录 有索引 多次查询 3左右ms 
select * from tb_json_test where v_user_name = '"博玲"'

json 数组

建表语句ddl

CREATE TABLE tb_json_array_test (
id INT NOT NULL AUTO_INCREMENT,
user_no VARCHAR(100),
user_name VARCHAR(100),
score INT,
create_time date,
update_time date,
remark VARCHAR(100),
field1 VARCHAR(100),
field2 VARCHAR(100),
field3 VARCHAR(100),
field4 VARCHAR(100),
field5 VARCHAR(100),
field6 VARCHAR(100),
field7 VARCHAR(100),
field8 VARCHAR(100),
json_field json,
PRIMARY KEY (id)
);

10w 数据插入存储过程

create PROCEDURE `demo02`.`jsonArrayQueryTest`()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE j INT DEFAULT 2011;
DECLARE user_name VARCHAR(20);
DECLARE user_no VARCHAR(20);
DECLARE score INT;
DECLARE create_time DATETIME DEFAULT NOW();
DECLARE update_time DATETIME;
DECLARE remark VARCHAR(50);
declare field1 VARCHAR(100);
declare field2 VARCHAR(100);
declare field3 VARCHAR(100);
declare field4 VARCHAR(100);
declare field5 VARCHAR(100);
declare field6 VARCHAR(100);
declare field7 VARCHAR(100);
declare field8 VARCHAR(100);
declare id VARCHAR(100);
declare json_field json;
set autocommit=0; -- 关闭自动提交事务，提高插入效率

WHILE i <= 100000 DO
SET user_name = CONCAT(
SUBSTRING('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水竺苏潘范雷', FLOOR(RAND() * 54) + 1, 1),
SUBSTRING('安宝彪彬冰博财成程达德东斗政法菲飞丰歌根光国海恒弘鸿宏洪华晖惠建健金景', FLOOR(RAND() * 30) + 1, 1),
SUBSTRING('静俊凯克莉良亮林玲龙茂梅民敏明娜宁鹏平奇琪全仁荣瑞森帅顺涛韬', FLOOR(RAND() * 30) + 1, 1)
);
SET user_no = CONCAT(j, LPAD(i, 7, '0'));
SET score = FLOOR(RAND() * 101);
SET remark = CONCAT('remark_', i);
SET update_time = DATE_ADD(create_time, INTERVAL FLOOR(RAND() * 100) DAY); -- update_time则随机生成在create_time基础上加上一定天数的时间。
set field1 = replace(uuid(),"-","");
set field2 = replace(uuid(),"-","");
set field3 = replace(uuid(),"-","");
set field4 = replace(uuid(),"-","");
set field5 = replace(uuid(),"-","");
set field6 = replace(uuid(),"-","");
set field7 = replace(uuid(),"-","");
set field8 = replace(uuid(),"-","");
set id = i;
set json_field = JSON_ARRAY(JSON_OBJECT('user_no', user_no, 'user_name', user_name, 'score', score), JSON_OBJECT('user_no', user_no, 'user_name', user_name, 'score', score));
INSERT INTO demo02.tb_json_array_test(id, user_no, user_name, score, create_time, update_time, remark, field1, field2, field3, field4, field5, field6, field7, field8, json_field)
VALUES(id, user_no, user_name, score, create_time, update_time, remark,field1, field2, field3, field4, field5, field6, field7, field8, json_field);

SET create_time = DATE_ADD(create_time, INTERVAL 1 SECOND); -- create_time初始值为当前时间，每生成一行数据就自增1分钟，以保证创建时间的递增。
SET i = i + 1;
IF i % 100000 = 0 THEN
SET j = j + 1;
END IF;
END WHILE;
end

call jsonArrayQueryTest();

json 类型 vs 普通字符串类型

-- json 数组
call jsonArrayQueryTest();
select * from tb_json_array_test
-- 40 条记录无索引多次查询 400多ms
select * from tb_json_array_test where user_name = '博玲'
-- 创建 user_name 索引
CREATE INDEX idx_user_name ON tb_json_array_test(user_name);
-- 40 条记录有索引多次查询 3左右ms
select * from tb_json_array_test where user_name = '博玲'
-- 40 条记录无索引 json 数组多次查询 800多ms
select * from tb_json_array_test where JSON_CONTAINS(json_field -> '$[*].user_name', '"博玲"')
-- json 数组如果里面的对象不同无法很好地建立虚拟索引

msyql 8.0.14

创建表和存储脚本和上面一样这里不列举了

json 非数组

json 类型 vs 普通字符串类型

-- json 非数组
-- 40 条记录无索引多次查询 150多ms
select * from tb_json_test where user_name = '德韬'
-- 创建 user_name 索引
CREATE INDEX idx_user_name ON tb_json_test(user_name);
-- 40 条记录有索引多次查询 1.5左右ms
select * from tb_json_test where user_name = '德韬'
-- 40 条记录无索引 json 多次查询 400多ms
select * from tb_json_test where JSON_CONTAINS(json_field -> '$.user_name', '"德韬"')
-- json类型字段创建虚拟列索引
ALTER TABLE tb_json_test ADD COLUMN v_user_name VARCHAR(255) AS (JSON_EXTRACT(json_field, '$.user_name')) VIRTUAL;
CREATE INDEX idx_v_user_name ON tb_json_test(v_user_name);
-- 40 条记录有索引多次查询 1.5左右ms
select * from tb_json_test where v_user_name = '"德韬"'

json 数组

-- json 数组
call jsonArrayQueryTest();
select * from tb_json_array_test
-- 40 条记录 无索引 多次查询 170多ms
select * from tb_json_array_test where user_name = '博玲'
-- 创建 user_name 索引
CREATE INDEX idx_user_name ON tb_json_array_test(user_name);
-- 40 条记录 有索引 多次查询 1.5左右ms
select * from tb_json_array_test where user_name = '博玲'
-- 40 条记录 无索引 json 数组 多次查询 600多ms
select * from tb_json_array_test where JSON_CONTAINS(json_field -> '$[*].user_name', '"博玲"')
-- json 数组 如果里面的对象不同 无法很好地建立虚拟索引

总结

数据不同机器和测试条件缘由仅供参考

5.7 版本中

json 类型创建虚拟索引的查询速度不差于创建索引的普通字符串，几乎相同

非json数组

创建索引的普通字符串3ms > 创建虚拟索引 json3ms > 不创建索引的普通字符串 400ms> 不创建虚拟索引 json 700ms

json数组

创建索引的普通字符串3ms > 创建虚拟索引 json3ms > 不创建索引的普通字符串 400ms> 不创建虚拟索引 json 800ms

8.0 版本中

json 类型创建虚拟索引的查询速度不差于创建索引的普通字符串，几乎相同

非json数组

创建索引的普通字符串1.5ms > 创建虚拟索引 json1.5ms > 不创建索引的普通字符串 170ms> 不创建虚拟索引 json 400ms

json数组

创建索引的普通字符串1.5ms > 创建虚拟索引 json1.5ms > 不创建索引的普通字符串 170ms> 不创建虚拟索引 json 600ms

posted @ 2023-05-13 01:43 linzm14 阅读(435) 评论(0) 编辑收藏举报

刷新页面返回顶部

linzm14

mysql 5.7 和 8.0 json 类型 json 数组类型 普通字符串类型 10w数据 查询速度差异

mysql 5.7.10

json 非数组

建表语句ddl

10w 数据 插入 存储过程

json 类型 vs 普通字符串类型

json 数组

建表语句ddl

10w 数据 插入 存储过程

json 类型 vs 普通字符串类型

msyql 8.0.14

json 非数组

json 类型 vs 普通字符串类型

json 数组

公告

mysql 5.7 和 8.0 json 类型 json 数组类型普通字符串类型 10w数据查询速度差异

10w 数据插入存储过程

10w 数据插入存储过程