MySQL8 下的 JSON（mysql json处理）

7版本开始提供了json数据类型，并在8.0版本中进行了大幅度的性能优化。

json类型的字段的内容在mysql中是按text类型存储的。

官方文档地址:

json数据类型: https://dev.mysql.com/doc/refman/8.0/en/json.html

json类型搜索的函数: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

给json类型创建索引: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

本文内容基于 win10+MySQL8.0.28 + MySQL workbench 环境编写。

mysql的JSON类型支持key-value及json数组格式，本文主要研究json数组格式，应用场景为信息的单、复选择项记录在筛选页面的过滤。

基本用法

数据转换

-- key-value格式

SELECT CAST('{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}' AS json) AS `key_value_format`

-- json数组格式

SELECT CAST( '[1,2,3]' AS json) AS `json_array_format`

key-value格式的查询

CREATE TABLE `dept` (

`id` INT UNSIGNED NOT NULL,

`dept` VARCHAR(255) DEFAULT NULL,

`json_value` JSON DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

INSERT INTO `dept`(id, dept, json_value)

VALUES(1,'部门1','{"deptName": "部门1", "deptId": 1, "deptLeaderId": 3}')

,(2,'部门2','{"deptName": "部门2", "deptId": 2, "deptLeaderId": 4}')

,(3,'部门3','{"deptName": "部门3", "deptId": 3, "deptLeaderId": 5}')

,(4,'部门4','{"deptName": "部门4", "deptId": 4, "deptLeaderId": 5}')

,(5,'部门5','{"deptName": "部门5", "deptId": 5, "deptLeaderId": 5}');

-- 查询

SELECT * FROM `dept` WHERE json_value->'$.deptLeaderId' IN (5, 3);

json数组格式查询

-- 必须全部包含

SELECT *

FROM (

SELECT JSON_ARRAY(1,2,3,4,5,6,7,8) AS ids

) AS a

WHERE JSON_CONTAINS(a.ids, json_array(4,3));

-- 只要包含一个即可

SELECT *

FROM (

SELECT JSON_ARRAY(1,2,3,4,5,6,7,8) AS ids

) AS a

WHERE JSON_OVERLAPS(a.ids, json_array(4,13));

索引

-- key-value格式

CREATE TABLE `employees` (

`student_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`data` JSON,

INDEX `IX_name`((data->>'$.name'))

) ENGINE=InnoDB;

-- json数组格式，要建立多值索引

CREATE TABLE `students` (

`student_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`class_ids` JSON COMMENT '所有的课程id的数组',

INDEX `IX_classIds`( (CAST(`class_ids` AS UNSIGNED ARRAY)) )

) ENGINE=InnoDB;

更多信息请参考官方文档。

key-value格式下的某一个key仍然可以使用json数组, 并建立多值索引

业务模拟测试

每一个信息对应一个分类(category_id)，每个分类下有多个项目(category_item_id)，每个项目下有多个选项(item_option_id)，项目可能是多选也可能是单选，把所选的选项id记录到字段value中。

信息的单、复选择项记录，我们之前使用的是一行一行的记录，但是这个在列表页过滤时，可能会将这个表多次innner join查询以过滤所有选择的项都要出现。

我们建两个表，一个是原始的一行一行记录格式，一个使用json格式保存所有数据.

-- 原始表: 一行一行记录

CREATE TABLE `info_to_category_item_options3` (

`id` int unsigned NOT NULL AUTO_INCREMENT,

`category_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '分类id',

`info_id` int unsigned NOT NULL COMMENT '信息id',

`category_item_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '分类的项目id',

`item_option_id` mediumint unsigned NOT NULL DEFAULT '0' COMMENT '选中的选项id',

PRIMARY KEY (`id`,`category_id`)

) ENGINE=InnoDB COMMENT='项目选择结果'

/*!50100 PARTITION BY HASH (`category_id`) PARTITIONS 10 */;

-- 新表: json格式

CREATE TABLE `info_to_category_item_options4` (

`id` int unsigned NOT NULL AUTO_INCREMENT,

`category_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '分类id',

`info_id` int unsigned NOT NULL COMMENT '信息id',

`category_item_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '分类的项目id',

`item_option_ids` JSON COMMENT '选中的选项id的json数组',

PRIMARY KEY (`id`,`category_id`)

) ENGINE=InnoDB COMMENT='项目选择结果'

/*!50100 PARTITION BY HASH (`category_id`) PARTITIONS 10 */;

模拟1000w信息的数据插入到原始表

//分类id在100-200之间

$arrCategoryItemIdAll = range(100, 200);

//保存sql的values字句内容

$arrSqlValues = [];

$batchSize = 100;

//批次插入时用来分批的计数

$k = 0;

for ($i = 1; $i <= 10000000; $i++) {

//信息id

$infoId = $i;

//分类id, 从1-200中随机取一个

$categoryId = rand(1, 200);

//随机1-5个项目

$t = rand(1, 5);

if ($t === 1) {

//注意: 只有一个时返回的是key

$arrCategoryItemId = [$arrCategoryItemIdAll[array_rand($arrCategoryItemIdAll, 1)]];

} else {

$arrCategoryItemId = array_rand($arrCategoryItemIdAll, $t);

}

$arrCategoryItemIdToItemOptionId = [];

foreach ($arrCategoryItemId as $key => $categoryItemId) {

//每个项目随机1-3个选中的属性

$t2 = rand(1, 3);

for ($j = 1; $j <= $t2; $j++) {

$arrCategoryItemIdToItemOptionId[] = [

'category_item_id' => $categoryItemId,

'item_option_id' => $j * 100 + rand(10, 50),

];

}

}

foreach ($arrCategoryItemIdToItemOptionId as $row) {

$categoryItemId = $row['category_item_id'];

$item_option_id = $row['item_option_id'];

$arrSqlValues[] = '(' . $categoryId . ', ' . $infoId . ', ' . $categoryItemId . ', ' . $item_option_id . ')';

}

if ($k === $batchSize) {

//达到100个信息的时候批量写入

$sql = 'INSERT INTO `info_to_category_item_options3`(`category_id`,`info_id`,`category_item_id`,`item_option_id`)VALUES'

. implode(',', $arrSqlValues);

echo $sql . PHP_EOL;

$db->query($sql);//数据库类执行sql

//重置

$arrSqlValues = [];

$k = 0;

} else {

$k++;

}

}

if ($k < $batchSize && count($arrSqlValues) > 0) {

echo '不足100的' . PHP_EOL;

$sql = 'INSERT INTO `info_to_category_item_options3`(`category_id`,`info_id`,`category_item_id`,`item_option_id`)VALUES'

. implode(',', $arrSqlValues);

echo $sql . PHP_EOL;

$db->query($sql);//数据库类执行sql

}

原始表实际行数6000w左右, 单分区表600w(文件大小200M左右)

将原始表数据复制到新表

INSERT INTO `info_to_category_item_options4`

(category_id, info_id, category_item_id, `item_option_ids`)

SELECT category_id, info_id, category_item_id, CONCAT('[', GROUP_CONCAT(`item_option_id`), ']') AS `item_option_ids`

FROM info_to_category_item_options3

GROUP BY category_id, info_id, category_item_id;

添加索引

-- 原始表

ALTER TABLE `info_to_category_item_options3`

ADD INDEX IX_categoryId_categoryItemId_itemOptionId(`category_id`, `category_item_id`, `item_option_id`);

-- 新表

ALTER TABLE `info_to_category_item_options4`

ADD INDEX IX_categoryId_categoryItemId_itemOptionIds(`category_id`, `category_item_id`, (CAST(`item_option_ids` AS UNSIGNED ARRAY)));

查询测试

本地测试数据中, 项目选项ID(item_option_id)为131的单独有6800行左右, 215的有4400行左右。

1. 多个值的"部分包含"查询

这里我们查询选项id包含131、215其中之一的数据。

-- 原始表

SELECT SQL_NO_CACHE category_id

FROM info_to_category_item_options3

WHERE category_id=98 AND category_item_id=98 AND `value` IN (131, 215)

LIMIT 0,100000;

-- 新表

SELECT SQL_NO_CACHE category_id

FROM info_to_category_item_options4 WHERE category_id=98 AND category_item_id=98

AND JSON_OVERLAPS(`values`, CAST('[131,215]' AS JSON))

LIMIT 0,100000;

在mysql workbench下执行查询，二者都比较快， 但是前者更快。

而且我还发现两个很奇怪的问题：

我本地workbench的配置是默认LIMIT 100，两个查询的SQL都不加LIMIT时，原表的查询返回100行数据，这是符合预期的；但是新表的查询却返回了所有1万多行数据

上面的SQL中返回的字段category_id是在索引中的，不需要再回表查询；但是如果换成不在索引中的info_id，原表的查询因为需要回表会变慢，这是符合预期的；但是新表的查询却仍然很快，貌似根本不需要回表。而原表和新表的单个分区表（加索引后）的物理文件大小都在340M左右。

2. 多个值的必须"全部包含"查询

这里我们查询选项id必须包含131和215的数据。

-- 原始表

SELECT SQL_NO_CACHE a.*

FROM info_to_category_item_options3 AS a

, info_to_category_item_options3 AS b

WHERE a.category_id=98 AND a.category_item_id=98 AND a.`item_option_id`=131

AND b.category_id=98 AND b.category_item_id=98 AND b.`item_option_id`=215

AND a.info_id=b.info_id;

-- 新表

SELECT SQL_NO_CACHE *

FROM info_to_category_item_options4

WHERE category_id=98 AND category_item_id=98

AND JSON_CONTAINS(`item_option_ids`, CAST('[131,215]' AS JSON));

使用原始表查询，时间很很很长，以至于不得不终止查询。因为inner join的条件字段info_id不在索引中, 需要回表11000多次，

使用新表查询，除了第一次1秒多，其余基本都在0.05秒左右。

很奇怪的是，新表的查询中，SQL_NO_CACHE 参数失效了，不然为什么第一次那么慢后面却那么快呢？

那我们现在修改一下原始表的索引:

ALTER TABLE `info_to_category_item_options3`

DROP INDEX IX_categoryId_categoryItemId_itemOptionId,

ADD INDEX IX_categoryId_categoryItemId_itemOptionId(`category_id`, `category_item_id`, `item_option_id`, `info_id`);

修改后，再次执行上面的查询，其性能已经比新表的json格式的快，而且是快3-4倍。

结论

综合本用例的测试结果来看，json数组在这种场景下性能不如传统方式，不建议使用。

原文链接：https://blog.csdn.net/aben_sky/article/details/125690495