MySQL联合索引的排列组合应用实战

我们都知道,当数据表中的数据日益增长后,查询会变得越来越慢,当初在表设计之初,尚未考虑创建索引的话,那么现在正是必要的时候。可是,如果对于MySQL使用索引的策略不了解,或是脱离了具体业务场景,那么,创建出来的索引,也发挥不了多大的作用。本文,就从我刚刚完成的一个项目入手,介绍如何正确的设计联合索引,以在实战项目中真正的发挥作用。

实际的业务场景

下图展示了项目所涉及的实际业务场景:

可以看到,查询条件主要涉及:视频分区、产品类别、产品、视频时长 和 发布时间。

其中,视频分区下,还有子分区:

产品下面,还有所属品牌:

所以,我们可以把查询条件做个梳理:1、视频分区 > 子分区,2、产品类别 > 产品 > 品牌。其中 产品类别产品 会根据用户选择的 视频分区子分区 的不同而变化,这样就会衍生出很多不同的业务逻辑 和 查询条件的组合:

  • 视频分区 > 子分区 > 产品类别 > 产品 > 品牌

这代表了用户既选了视频分区和子分区,同时又基于所属子分区下的产品类别,又选择了产品和品牌,即 所有关键的查询条件都用上了。下面的示意逻辑相同,只是组合不同而已。

  • 视频分区 > 子分区 > 产品类别
  • 视频分区 > 子分区 > 产品 > 品牌
  • 视频分区 > 产品类别 > 产品 > 品牌
  • 视频分区 > 产品类别
  • 视频分区 > 产品 > 品牌
  • 产品类别 > 产品 > 品牌
  • 产品 > 品牌

这里只列举了部分查询条件的组合,其实还有更多。其中特别标注出来的,是在实际使用场景下,常用的查询组合。下面我们就来看一看,如何基于这些查询条件,设计出合理的索引。

基于业务场景的联合索引的设计方案

我们把索引简单的分为 单列索引多列索引,多列索引被称为 联合索引复合索引,对于查询语句中的 where 条件,如果某些条件是这个查询中频繁用到的组合,那么,通常会创建联合索引,来提升查询效率。

但是,对于一个没有深谙其道的人来讲,他可能会这样设计联合索引:

  1. 给每个查询字段创建一个 单列索引

    CREATE INDEX 索引名称 ON 表名 (视频分区);
    
    CREATE INDEX 索引名称 ON 表名 (子分区);
    

    ...

  2. 只创建一个能覆盖到所有查询条件的 联合索引

    CREATE INDEX 索引名称 ON 表名 (视频分区, 子分区, 产品类别, 产品, 品牌);
    

以为这样,就可以让索引发挥全部作用,适用于所有查询条件的组合了。其实,联合索引 有一个最左匹配原则,从左至右匹配你的查询条件,直至断掉终止,如果索引列的第一个字段都尚不能匹配,则用不上此索引。比如:

  • 我们查询 视频分区 > 子分区 > 产品类别 > 产品 > 品牌 这些条件组合,当然可以用到上面创建的索引,因为查询条件与索引列完全对的上。
  • 如果查询 产品类别 > 产品 > 品牌 的话,则索引就无效了,因为索引列的第一个索引字段 视频分区 不在 where 查询条件中,最左匹配原则一开始就失败了,所以用不上索引。
  • 如果查询 视频分区 > 子分区,这是符合最左匹配原则的【英文叫 leftmost prefix of the index】,这个 leftmost 就告诉我们,虽然索引列并不完全匹配查询条件,但是部分匹配,而且必须顶着头的、排着队的、中间没有断掉的匹配了,即使尾巴断掉了没有关系,依然可以用上此索引。
  • 最后,如果查询 视频分区 > 子分区 + 发布时间 这组条件,能否用的上此索引呢?答案是肯定的,我不管你 where 条件中,哪些列不是索引列,我只关心你 where 条件中,哪些列在索引列之中,并且符合最左匹配就行了。

好了,到这里,我们应该逐渐清楚的认识到,仅仅创建一个包含所有查询条件的联合索引 视频分区, 子分区, 产品类别, 产品, 品牌 是远远不够的,而是需要根据业务需求和使用场景,将可能会频繁用到的查询条件,进行不同的排列组合,设计出一个折中的 联合索引 的方案。

正如你在本文前一部分看到的,在我列举出的部分查询条件中,着重标注出来的那些,就是我认为会最频繁使用到的组合,所以,需要相应的创建多个不同组合的 联合索引,以此来应对前端操作用户选择不同查询条件时,能够最大限度的命中索引,提升查询效率。

索引无法解决的问题

你应该也能看得出来,即使合理的创建了 联合索引,也无法覆盖到全部的查询条件的各种组合。好吧,就算你真的根据所有的组合,都相应的创建了 联合索引,但还有最后一关你过不去,那就是 模糊查询。你注意到,我的项目中,用到了根据用户输入的内容,来进行搜索,这必然需要使用模糊查询来实现,但是,%xxx% 这种形式的模糊查询,是无论如何也用不上索引的。

针对以上问题,我突发奇想,想到了一个绝妙的方案,并加以实施,应用到了我的项目中,结果效果非常好。在下一篇,我再作具体介绍。

原文链接:,转发请注明来源!