mysql 5.7 json 类型 json 数组类型普通字符串类型 10w数据查询速度差异-Toy模板网

这篇具有很好参考价值的文章主要介绍了mysql 5.7 json 类型 json 数组类型普通字符串类型 10w数据查询速度差异。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

mysql 5.7.10

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

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

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

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

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文章来源地址https://www.toymoban.com/news/detail-441524.html

到了这里，关于mysql 5.7 json 类型 json 数组类型普通字符串类型 10w数据查询速度差异的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

mysql 5.7 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 数组

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2

mysql 5.7 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 数组

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2

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

10w 数据插入存储过程

10w 数据插入存储过程