【性能优化】还在滥用模糊查找？这类场景得用 MySQL 多值索引

五阳
1,266 阅读5分钟

1. 背景

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

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

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

业界通常的实现方案有两种，1）使用 ElasticSerach实现全文检索。 2）使用 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 (表达式) 可查询多值索引，例如以下示例

image.png

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

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

image.png

最后

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

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

我的开源项目

最后夹带一点私货，五阳最近花了3个月的时间完成一个开源项目。

开源3周以来，已有近 230 多个关注和Fork

Gitee：gitee.com/juejinwuyan…

GitHub github.com/juejin-wuya…

开源平台上有很多在线商城系统，功能很全，很完善，关注者众多，然而实际业务场景非常复杂和多样化，开源的在线商城系统很难完全匹配实际业务，广泛的痛点是

  • 功能堆砌，大部分功能用不上，需要大量裁剪；
  • 逻辑差异点较多，需要大量修改；
  • 功能之间耦合，难以独立替换某个功能。

由于技术中间件功能诉求较为一致，使用者无需过多定制化，技术中间件开源项目以上的痛点不明显，然而电商交易等业务系统虽然通用性较多，但各行业各产品的业务差异化极大，所以导致以上痛点比较明显

所以我在思考，有没有一个开源系统，能提供电商交易的基础能力，能让开发者搭积木的方式，快速搭建一个完全契合自己业务的新系统呢？

  • 他们可以通过编排和配置选择自己需要的功能，而无需在一个现成的开源系统上进行裁剪
  • 他们可以轻松的新增扩展业务的差异化逻辑，不需要阅读然后修改原有的系统代码！
  • 他们可以轻松的替换掉他们认为垃圾的、多余的系统组件，而不需要考虑其他功能是否会收到影响

开发者们，可以择需选择需要的能力组件，组件中差异化的部分有插件扩展点能轻松扩展。或者能支持开发者快速的重新写一个完全适合自己的新组件然后编排注册到系统中？

memberclub 就是基于这样的想法而设计的。 它的定位是电商类交易系统工具箱， 以SDK方式对外提供通用的交易能力，能让开发者像搭积木方式，从0到1，快速构建一个新的电商交易系统！

image.png

具体介绍可参见

Gitee开源地址：gitee.com/juejinwuyan…

GitHub开源地址 : github.com/juejin-wuya…

在这个项目中你可以学习到 SpringBoot 集成 以下框架或组件。

  1. Mybatis、Mybatis-plus 集成多数据源
  2. Sharding-jdbc 多数据源分库分表
  3. redis/redisson 缓存
  4. Apollo 分布式配置中心
  5. Spring Cloud 微服务全家桶
  6. RabbitMq 消息队列
  7. H2 内存数据库
  8. Swagger + Lombok + MapStruct

同时你也可以学习到以下组件的实现原理

  1. 流程引擎的实现原理
  2. 扩展点引擎实现原理
  3. 分布式重试组件实现原理
  4. 通用日志组件实现原理 参考：juejin.cn/post/740727…
  5. 商品库存实现原理： 参考：juejin.cn/post/731377…
  6. 分布式锁组件： 参考：
  7. Redis Lua的使用
  8. Spring 上下文工具类 参考： juejin.cn/post/746927…
avatar
文章
阅读
粉丝