MYSQL面试八股文-索引优化之多值索引（解决JSON数据查询走索引问题）

背景

宠物门店优惠券业务，同一个优惠券主体，需要同时支持多家门店可用。

在MySQL8以前，一种方案是通过建立关系表记录优惠券ID和门店ID之间的关系；另一种方案是用一个字段然后用特殊字符分隔门店ID（例如：JSON字符串）。

在查询某个门店可以使用的优惠券列表式前一种方案虽然能在查询的时候走到索引，但是会使用子查询；后一种方案无法走到索引。

MySQL8以后增加了JSON数据类型，而且支持索引，可以很好的用一张数据表解决上面的问题。

建表

1
2
3
4
5
6
7
8
9

CREATE TABLE `czt_coupon_main_body` (
`coupon_body_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '优惠券主体自增id',
`coupon_name` VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '优惠券名字',
………… 此处省略三五十个业务字段
`extra_contains` json NOT NULL DEFAULT (_utf8mb4'{"contain_store_ids": []}') COMMENT '可用门店ID',
PRIMARY KEY (`coupon_body_id`) USING BTREE,
KEY `contain_store_ids_index` ((CAST(json_extract(`extra_contains`,_utf8mb4'$.contain_store_ids') AS UNSIGNED array))),
FULLTEXT KEY `coupon_name_index` (`coupon_name`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB AUTO_INCREMENT=1265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='优惠券主体数据表';

此处我们通过extra_contains作为一个JSON格式的字段来存储可用门店的ID，里面的结构为{“contain_store_ids”: []}’，门店ID为无符号整形数组。

再通过
KEY `contain_store_ids_index` ((cast(json_extract(`extra_contains`,_utf8mb4’$.contain_store_ids’) as unsigned array)))
建立一个无符号整形的数组索引。

coupon_name 字段上建立一个全文索引，用于优惠券名模糊查询。

查询

1
2
3
4
5
6
7
8
9
10
11
12

EXPLAIN SELECT
*
FROM
czt_coupon_main_body
WHERE
JSON_CONTAINS(
extra_contains -> '$.contain_store_ids',
CAST( '[1000010002]' AS JSON ))
-- AND MATCH ( coupon_name ) AGAINST ( '体验' IN BOOLEAN MODE )
ORDER BY
coupon_body_id DESC
LIMIT 20;

explain解析索引使用情况

从explain可以看出，使用JSON格式的数据结合多值索引可以利用多值索引进行查询优化从而简化查询操作。

如果开启了优惠券名模糊查询，则优化器会自行根据实际情况选择使用多值索引还是全文索引。