mysql json性能测试

总条数100-150万，2核4线程4g内存参考https://www.cnblogs.com/amerkor/p/13646581.htmlhttps://blog.csdn.net/ai_xao/article/details/100711808---------未加索引测试-------------------------------------------------------------

清风2556

4895人浏览 · 2021-09-23 17:36:43

清风2556 · 2021-09-23 17:36:43 发布

总条数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';

------------------------------------------------------------------------------------

---------------------下面是造数据

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