MySQL5 7 JSON欄位效能測試

json欄位表

create table `invt_bin_dim_test` (

​`id` bigint(20) unsigned not null auto_increment,
​`dim` json default null,
​primary key (`id`)
​) engine=innodb auto_increment=1 default charset=utf8mb4;

create table `invt_bin_dim_tr_test` (

`id` bigint(20) unsigned not null auto_increment,
`fty_code` varchar(50) not null default '',
`location_code` varchar(50) not null default '',
`wh_code` varchar(50) not null default '',
`area_code` varchar(50) not null default '',
`bin_code` varchar(50) not null default '',
primary key (`id`)
) engine=innodb default charset=utf8mb4;

插入語句

json欄位表語句

insert into invt_bin_dim_test (dim) 

values (concat(''));

insert into `wms_2`.`invt_bin_dim_tr_test` (`fty_code`, `location_code`, `wh_code`, `area_code`, `bin_code`) values ('2000', '0001', 'w1', 'pb801', '01-011'); 

基礎資料量

傳統表 (10次平均)

json格式 (10次平均)

1條0.005s

0.013s

100000條

0.166s

0.019s

說明：百萬級插入資料時均出現了首次插入較慢，後續操作很快的現象（相差兩至三個數量級）

查詢指定條件的單條資料記錄

json欄位表語句

select 

dim ->> '$.fty_code' as fty_code,
dim ->> '$.location_code' as location_code
from invt_bin_dim_test
where 
dim ->> '$.fty_code' = '2000'
and dim ->> '$.location_code' = '0001'
and dim ->> '$.wh_code' = 'w1'
and dim ->> '$.area_code' = 'p801'
and dim ->> '$.bin_code' ='01-005'

select * from invt_bin_dim_tr_test

where 
fty_code = '2000'
and location_code = '0001'
and wh_code = 'w1'
and area_code = 'pb801'
and bin_code = '01-011'

基礎資料總量

傳統表 (10次平均)

json格式 (10次平均)

1條0.007s

0.006s

100000條

1.01s

5.915s

使用虛列+索引後，json欄位查詢效能得到大幅度優化

json欄位表語句

# 虛列

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 * from invt_bin_dim_test 
where 
fty_code = '2000' 
and location_code = '0001'
and wh_code = 'w1'
and area_code = 'p801'
and bin_code ='01-005'
​

基礎資料總量

傳統表 (10次平均)

json格式 (10次平均)

1條0.007s

0.006s

100000條

0.007s

0.007s

更新單條記錄

json欄位表語句

update invt_bin_dim_test set dim = json_insert(dim, '$.wh_code', 'w202', '$.bin_code', '01-02-03') 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_test set dim = json_remove(dim, '$.area_code') where id=3;

update invt_bin_dim_tr_test

set bin_code = '01-012' 
where 
fty_code = '2000'
and location_code = '0001'
and wh_code = 'w1'
and area_code = 'pb801'
and bin_code = '01-011'

基礎資料量

傳統表 (10次平均)

json格式 (json_insert) (10次平均)

json格式 (json_set) (10次平均)

json格式(json_remove) (10次平均)

1條0.006s

0.003s

0.004s

0.003s

100000條

0.015s

0.003s

0.005s

0.003s

批量更新記錄

更新1000000條記錄

結果對比

基礎資料量

傳統表json格式 (json_insert)

json格式 (json_set)

json格式(json_remove)

100000條

2 min 32 sec

2 min 42 sec

4 min 41 sec

3 min 7 sec

MySQL JSON型別欄位的查詢與更新

mysql 提供了豐富的函式用於 json 型別欄位的查詢與更新，詳見官方文件。簡單記一下使用方法，使用的mysql版本 5.7.28 log 建立一個表 t1，basic info 欄位為json型別 create table t1 id int 11 not null auto incremen...

DHCP Option欄位的測試方法

1.背景 dhcp server有很多option欄位，但是測試儀不支援那麼多，那麼就需要構造discover報文，模擬client發起請求。2.dhcp discover報文格式 dhcp報文是承載於udp上的協議報文，採用67 dhcp伺服器 和68 dhcp客戶端 兩個埠號。4.科來資料包生成...