Categories: 技术原创

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格式的数据结合多值索引可以利用多值索引进行查询优化从而简化查询操作。

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

总结

1. MySQL8以后针对比较小的关系数据,可以使用JSON数据类型结合整数数组建立关系和索引,避免建立繁琐的关系表或无法走索引导致查询效率低下的问题。
2. 多值索引限制较多,目前仅整形数组支持得比较好;优化器会根据实际情况选择索引进行使用。

程序猿老龚(龚杰洪)原创,版权所有,转载请注明出处.

龚杰洪

Recent Posts

GOLANG面试八股文-并发控制

背景 协程A执行过程中需要创建…

1 年 ago

MYSQL面试八股文-常见面试问题和答案整理二

索引B+树的理解和坑 MYSQ…

1 年 ago

MYSQL面试八股文-InnoDB的MVCC实现机制

背景 什么是MVCC? MVC…

1 年 ago

MYSQL面试八股文-索引类型和使用相关总结

什么是索引? 索引是一种用于加…

1 年 ago

MYSQL面试八股文-索引优化之全文索引(解决文本搜索问题)

背景:为什么要有全文索引 在当…

1 年 ago

MYSQL面试八股文-常见面试问题和答案整理一

基础部分 1、什么是 MySQ…

1 年 ago