还在滥用模糊查找？这类场景下 MySQL 多值索引性能更加强悍！

1. 背景

商家在后台系统中根据商品名称检索商品，在商品数量有限时，可以使用模糊查找方式实现简单地检索。如使用 WHERE columName like '%inputName%' 实现模糊查找。

然而有一种场景正在滥用模糊查找。

在优惠券配置后台中，需要具备优惠券模版检索能力，其中模板检索条件是根据商品 ID，检索哪些优惠券模版关联了指定商品。一个优惠券模版可以在多个商品上使用，两者关联关系为 1：N；

业界通常的实现方案有两种：

• 使用 ElasticSerach实现全文检索。
• 使用 MySQL 检索。

当数据量较少，检索条件有限时，可优先先选择方案更加简单地MySQL方案。

如何实现 指定商品 ID 检索关联的优惠券模版 功能呢？MySQL 有两种方案

方案 1：模糊查找

通过模糊查找实现检索，如 coupon_template 中 relatedProductIds 属性记录了 关联的商品 Id 列表。

WHERE related_product_Ids like  '%${inputProductId}%'

这种方式并不准确，例如模版 A 的related_product_ids = 110,111,112, 当用户输入 11 时，模板 A 会被检索出来。

如何优化呢？可以将related_product_ids 存储为 "110,111,112,"，增加 1 个逗号。SQL语句调整为

WHERE related_product_Ids like  '%${inputProductId},%'

此种方式就万无一失了吗？不然，当用户输入12 时，依然会错误地检索到模版 A。

再次优化后，related_product_ids 存储为 ",110,111,112," 即前后均添加逗号，SQL 语句调整为

WHERE related_product_Ids like  '%,${inputProductId},%' 

前后添加逗号，用来截断。

方案 2：多值索引查找

MySQL 8.0版本以后，可以建立多值索引，应用此种查询场景。相比模糊查找方案，多值索引的检索性能更好，方案更加简单。

什么是多值索引

对普通的索引来说，每一条记录仅对应一条索引记录，对多值索引而言，一条记录可以对应多条索引记录。

普通索引可以对 user_id、order_id 字段建立索引。多值索引可以对 JSON 字段建立索引，例如 related_product_Ids 字段在数据库中是 JSON 类型，值为 [110, 111, 112]，当对该字段建立多值索引时，可以建立三条索引记录，110，111，112 ，因此单独查询任一商品Id，均可以索引到 记录 A。

接下来查看下如何使用多值索引

如何使用多值索引

1）创建JSON 类型的字段

alter table coupon_template add column related_prodcut_ids JSON default NULL;

2）创建一条 JSON 记录

insert into  coupon_template(related_product_ids) values ('[110,111,112]');

3）对 JSON 类型创建多值索引

alter table coupon_template add INDEX relatedProductIdsIndex((cast(json_extract(related_product_ids, '$[*]') as unsigned array)));

创建多值索引部分，需要指定表达式。范式是 ((cast(json_extract(columnName, 'expression') as unsigned array)))

因为 product_ids是 Json 数组，所以表达式部分 是 $[*]。如果是一个 Json Object，例如如下的 JSON

{
  "name": "xxxx",
  "ids": [110,111,112]
}

表达式应该为 $.ids

例如下面的代码示例，创建了 ids_ext JSON 属性，对 json 中的 ids属性建立二级索引。注意 表达式变成了，ids_ext-> '$.ids'，这是创建二级索引的另一种简洁写法，无需嵌套 json_extract 方法了。

alter table coupon_template add INDEX ids_ext_index ((CAST(ids_ext-> '$.ids' AS UNSIGNED ARRAY)));

如何查询多值索引

使用 MEMBER OF (表达式) 可查询多值索引，例如以下示例

select * from coupon_template WHERE 110 MEMBER OF (ids_ext-> '$.ids');

使用 explain 查看执行计划，如下截图所示，确实可以使用到 ids_ext_index 索引。

最后

MySQL 8.0 在 2018 年发布，目前已经成熟。该版本引入了一系列新特性如 JSON类型、窗口函数、多值索引和OnlineDDL Instant瞬时改表等。

此外官方已经在 2023 年底停止 MySQL 5.7版本，所以还是要尽快迁移到 8.0 版本。