mysql 5.7 json 型別 json 陣列型別 普通字串型別 10w資料 查詢速度差異

linzm14發表於2023-05-13
MySqlJSON型別陣列字串

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_test where user_name = '博玲'
-- 40 條記錄 無索引 json 陣列 多次查詢 700多ms
select * from tb_json_test where JSON_CONTAINS(json_field -> '$[0].user_name', '"博玲"')
-- json 陣列 如果裡面的物件不同 無法很好地建立虛擬索引

總結

json 型別 建立虛擬索引的查詢速度不差於建立索引的普通字串

建立索引的普通字串 > 建立虛擬索引 json > 不建立索引的普通字串 > 不建立虛擬索引 json

相關文章