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( '{"fty_code": "2000","location_code":"0001","wh_code":"W102","area_code":"P801","bin_code":"014"}' ) ) ;
传统表语句
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