背景
宠物门店优惠券业务，同一个优惠券主体，需要同时支持多家门店可用。
在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. 多值索引限制较多，目前仅整形数组支持得比较好；优化器会根据实际情况选择索引进行使用。
程序猿老龚（龚杰洪）原创，版权所有，转载请注明出处.