总条数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`);
9、添加索引后,插入测试,单次插入100条,json 31ms,传统 29ms,慢接近2ms
10、添加索引后,更新测试,单次更新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.003s
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