总条数100-101万
2核4线程4g内存
Mysql版本：5.7.31
在底部制作数据 ---------测试--------------------------------------------------------------------------------------- 1.没有索引，id主键，count对比,json明显比传统慢很多，接近60ms。 count(*)耗时小于count(id)，大约8ms
select count(id) from invt_bin_dim_test > OK > 时间: 0.302s
select count(id) from invt_bin_dim_tr_test > OK > 时间: 0.215s
select count(id) from invt_bin_dim_test; select count(id) from invt_bin_dim_tr_test;
添加索引后count，json比传统慢很多，接近6ms。 select count(id) from invt_bin_dim_test > OK > 时间: 0.222s
select count(id) from invt_bin_dim_tr_test > OK > 时间: 0.186s
2.未加索引，插入100万后，插入单个测试
INSERT INTO invt_bin_dim_test (dim) VALUES (CONCAT('{"fty_code": "2000","location_code":"0001","wh_code":"W102","area_code":"P801","bin_code":"014"}'));
INSERT INTO `invt_bin_dim_tr_test` (`fty_code`, `location_code`, `wh_code`, `area_code`, `bin_code`) VALUES ('2000', '0001', 'W102', 'P801', '014');
3.未加索引，分批插入测试 单次插入10条,传统的比json的快接近1ms
4.没有索引，查询全部，json接近5s，传统的2.5s，差别较大. SELECT id, dim ->> '$.fty_code' AS fty_code, dim ->> '$.location_code' AS location_code, dim ->> '$.wh_code' as wh_code, dim ->> '$.area_code' as area_code, dim ->> '$.bin_code' as bin_code FROM invt_bin_dim_test WHERE dim ->> '$.fty_code' = '2000' AND dim ->> '$.location_code' = '0001' AND dim ->> '$.wh_code' = 'W102' AND dim ->> '$.area_code' = 'P801' AND dim ->> '$.bin_code' ='014';
SELECT * FROM invt_bin_dim_tr_test WHERE fty_code = '2000' AND location_code = '0001' AND wh_code = 'W102' AND area_code = 'P801' AND bin_code = '014';
5.未添加索引，添加虚拟列后查询对比，json接近5s，传统的2.5s，差别较大.
ALTER TABLE `invt_bin_dim_test` ADD `fty_code` VARCHAR(50) GENERATED ALWAYS AS (IFNULL(dim->>'$.fty_code', '')) VIRTUAL; ALTER TABLE `invt_bin_dim_test` ADD `location_code` VARCHAR(50) GENERATED ALWAYS AS (IFNULL(dim->>'$.location_code', '')) VIRTUAL; ALTER TABLE `invt_bin_dim_test` ADD `wh_code` VARCHAR(50) GENERATED ALWAYS AS (IFNULL(dim->>'$.wh_code', '')) VIRTUAL; ALTER TABLE `invt_bin_dim_test` ADD `area_code` VARCHAR(50) GENERATED ALWAYS AS (IFNULL(dim->>'$.area_code', '')) VIRTUAL; ALTER TABLE `invt_bin_dim_test` ADD `bin_code` VARCHAR(50) GENERATED ALWAYS AS (IFNULL(dim->>'$.bin_code', '')) VIRTUAL;
SELECT id,fty_code,location_code,wh_code,area_code,bin_code FROM invt_bin_dim_test WHERE fty_code = '2000' AND location_code = '0001' AND wh_code = 'W102' AND area_code = 'P801' AND bin_code = '014' > OK > 时间: 5.278s
SELECT id,fty_code,location_code,wh_code,area_code,bin_code FROM invt_bin_dim_tr_test WHERE fty_code = '2000' AND location_code = '0001' AND wh_code = 'W102' AND area_code = 'P801' AND bin_code = '014' > OK > 时间: 2.671s
6.未添加索引，添加虚拟列后插入对比，插入json慢1ms左右，略大于1ms
7.没有索引更新比较，传统比较json慢很多，一次更新100条，json大约6ms，传统的接近39ms
CALL testUpdateJson1() > OK > 时间: 0.06s
CALL testUpdateJson2() > OK > 时间: 0.039s
SELECT id,fty_code,location_code,wh_code,area_code,bin_code FROM invt_bin_dim_test WHERE id=3;
SELECT id,fty_code,location_code,wh_code,area_code,bin_code FROM invt_bin_dim_tr_test WHERE id=3;
UPDATE invt_bin_dim_test SET dim = JSON_SET(dim, '$.wh_code', 'W202', '$.bin_code', '01-02-03') where id=3;
UPDATE invt_bin_dim_tr_test SET bin_code = '01-012', wh_code='W202' WHERE id=3;
8.添加索引时，json 17.2s,传统14.2s。 ALTER TABLE `invt_bin_dim_test` ADD INDEX `t1`(`fty_code`), ADD INDEX `t2`(`location_code`), ADD INDEX `t3`(`wh_code`), ADD INDEX `t4`(`area_code`), ADD INDEX `t5`(`bin_code`);
ALTER TABLE `invt_bin_dim_tr_test` ADD INDEX `t1`(`fty_code`), ADD INDEX `t2`(`location_code`), ADD INDEX `t3`(`wh_code`), ADD INDEX `t4`(`area_code`), ADD INDEX `t5`(`bin_code`);
添加索引后，插入测试，单次插入100条，json 31ms,传统 29ms，慢接近2ms
添加索引后，更新测试，单次更新100条，json大约9ms，传统的接近39ms 传统的更新时间在添加索引前后变化不大，json由原先的6ms增加到9ms
11.添加索引后，更新100万次测试 ,json 141s,传统 123s。json慢18s左右 UPDATE invt_bin_dim_test SET dim = JSON_SET(dim, '$.wh_code', 'W203', '$.bin_code', '01-02-04');
UPDATE invt_bin_dim_tr_test SET bin_code = '01-014', wh_code='W203';
12.加索引后，查询所有时间 a、空记录结果时 json 1.75s,传统0.003 SELECT id, dim ->> '$.fty_code' AS fty_code, dim ->> '$.location_code' AS location_code, dim ->> '$.wh_code' as wh_code, dim ->> '$.area_code' as area_code, dim ->> '$.bin_code' as bin_code FROM invt_bin_dim_test WHERE dim ->> '$.fty_code' = '2000' AND dim ->> '$.location_code' = '0001' AND dim ->> '$.wh_code' = 'W102' AND dim ->> '$.area_code' = 'P801' AND dim ->> '$.bin_code' ='014';
SELECT * FROM invt_bin_dim_tr_test WHERE fty_code = '2000' AND location_code = '0001' AND wh_code = 'W102' AND area_code = 'P801' AND bin_code = '014';
b、结果空记录时 json <传统 ，相差小于1ms，大约 SELECT id,fty_code,location_code,wh_code,area_code,bin_code FROM invt_bin_dim_test WHERE fty_code = '2000' AND location_code = '0001' AND wh_code = 'W102' AND area_code = 'P801' AND bin_code = '014';
SELECT id,fty_code,location_code,wh_code,area_code,bin_code FROM invt_bin_dim_tr_test WHERE fty_code = '2000' AND location_code = '0001' AND wh_code = 'W102' AND area_code = 'P801' AND bin_code = '014';
------------------------------------------------------------------------------------
---------------------下面是造数据
CREATE TABLE `invt_bin_dim_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `dim` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
传统表 CREATE TABLE `invt_bin_dim_tr_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `fty_code` varchar(50) NULL DEFAULT '', `location_code` varchar(50) NULL DEFAULT '', `wh_code` varchar(50) NULL DEFAULT '', `area_code` varchar(50) NULL DEFAULT '', `bin_code` varchar(50) NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO invt_bin_dim_test (dim) VALUES (CONCAT('{"fty_code": "2000","location_code":"0001","wh_code":"W102","area_code":"P801","bin_code":"014"}'));
INSERT INTO `invt_bin_dim_tr_test` (`fty_code`, `location_code`, `wh_code`, `area_code`, `bin_code`) VALUES ('2000', '0001', 'W102', 'P801', '014');
drop procedure if exists testJson;
delimiter $$ create procedure testJson() BEGIN declare i int(11); set i = 1; WHILE i <= 1000000 do INSERT INTO invt_bin_dim_test (dim) VALUES (CONCAT('{"fty_code": "2000","location_code":"0001","wh_code":"W102","area_code":"P801","bin_code":"014"}'));
INSERT INTO `invt_bin_dim_tr_test` (`fty_code`, `location_code`, `wh_code`, `area_code`, `bin_code`) VALUES ('2000', '0001', 'W102', 'P801', '014'); set i = i + 1; end WHILE; end $$
CALL testJson();
drop procedure if exists testInsertJson1;
delimiter $$ create procedure testInsertJson1() BEGIN declare i int(11); set i = 1; WHILE i <= 100 do INSERT INTO invt_bin_dim_test (dim) VALUES ('{"fty_code": "2000","location_code":"0001","wh_code":"W102","area_code":"P801","bin_code":"014"}'); set i = i + 1; end WHILE; end $$
delimiter $$ create procedure testInsertJson2() BEGIN declare i int(11); set i = 1; WHILE i <= 100 do INSERT INTO `invt_bin_dim_tr_test` (`fty_code`, `location_code`, `wh_code`, `area_code`, `bin_code`) VALUES ('2000', '0001', 'W102', 'P801', '014'); set i = i + 1; end WHILE; end $$
CALL testInsertJson1(); CALL testInsertJson2();
delimiter $$ create procedure testUpdateJson1() BEGIN declare i int(11); set i = 1; WHILE i <= 100 do
UPDATE invt_bin_dim_test SET dim = JSON_SET(dim, '$.wh_code', 'W202', '$.bin_code', '01-02-03') where id=3;
set i = i + 1; end WHILE; end $$
delimiter $$ create procedure testUpdateJson2() BEGIN declare i int(11); set i = 1; WHILE i <= 100 do
UPDATE invt_bin_dim_tr_test SET bin_code = '01-012', wh_code='W202' WHERE id=3;
set i = i + 1; end WHILE; end $$
CALL testUpdateJson1(); CALL testUpdateJson2();
参考:
https://www.cnblogs.com/amerkor/p/13646581.html https://blog.csdn.net/ai_xao/article/details/100711808