• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB联合索引学习

MySQL InnoDB 彭东稳 7年前 (2017-09-11) 24177次浏览 已收录 0个评论

联合索引是指对表上的多个列进行索引,其创建方法与单个索引一样,不同之处仅在于有多个索引列。例如,以下代码创建了一张t表,并且索引idx_a_b是联合索引,联合的列为(a,b)。

那么何时需要使用联合索引呢?在讨论这个问题之前,先来看一下联合索引内部的结构。从本质上来说,联合索引也是一棵B+树,不同的是联合索引的键值的数量不是1,而是大于等于2。接着来讨论两个整型列组成的联合索引,假设两个键值的名称分别为a、b,如下图:

MySQL InnoDB联合索引学习

从上图可以观察到多个键值的B+树情况,其实和单个键值的B+树并没有什么不同,键值都是排序的,通过叶子节点可以逻辑上顺序地读出所有数据,就上面的例子来说,即(1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2)。其数据按(a,b)的顺序进行了存放,第一列肯定是升序排序的,第二列是根据第一列排序而排序的,跟order by a,b数据排序规则是相同的。如下示例:

如果给a,b添加一个组合索引,那么这个组合索引的数据分布规则其实就是select a,b from t1 order by a,b的结果,如下:

第一列a肯定是排序好的(默认是升序),而第二个字段b其实就不是排序的了。但是如果a字段有相同的值时,那么对应的a字段的b字段就是排序的了。

当然,插入数据到联合索引时也是一样的道理(明白了这个,对理解gap锁会有帮助),比如我们现在对(a,b)插入(3,2)这样一条数据:

  • 首先判断a字段,找到比3小的记录,然后插入到下一条记录的位置;
  • 如果已经存在3这条记录,那么会接着比较b字段,如果比b字段大,就插入到下一条记录的位置,如果比b字段小,就插入到上一条记录的位置;
  • 所以按照这个规则,(3,2)这条记录就会插入到(2,2)这条记录后面。

回到上图中,对于查询“SELECT * FROM TABLE WHERE a=xxx and b=xxx”,显然是可以使用(a,b)这个联合索引的。对于单个的a列查询“SELECT * FROM TABLE WHERE a=xxx”也是可以使用这个(a,b)这个联合索引的。但对于b列的查询“SELECT * FROM TABLE WHERE b=xxx”则不可以使用这棵B+树索引。可以发现叶子节点上的b值为1、2、1、4、1、2,显然不是排序的,而B+树的特性就是数据是排序的才能使用二分查找法,因此对于b列的查询使用不到(a,b)的索引。

但是,对于“b=xxx”或者“b>xxx and b<xxx”的WHERE条件也不是说就100%无法使用(a,b)联合索引,有些情况是可以进行索引扫描,这主要看MySQL优化器Cost(成本)计算得出来的结果。如“SELECT * FROM TABLE WHERE b>xxx AND b<xxx”查询,如果优化器经过成本计算后觉得通过索引扫描比全表扫描快,那么就可能把整个索引扫描了一遍,然后筛选出来需要的行,再回表查找数据(所以这种查询有时候也可能是选择全表扫描)。在覆盖查询的场景下也是可以使用(a,b)联合索引的。比如“SELECT COUNT(*) FROM TABLE WHERE b>xxx AND b<xxx”时就可以用到(a,b)联合索引的b列,因为是要得到一个COUNT()结果,所以可以使用b列索引进行索引扫描。或者查询为“SELECT b FROM TABLE WHERE b>xxx AND b<xxx”时也属于覆盖场景,也可以使用b列进行索引扫描操作。

联合索引的第二个好处是已经对第二个键值进行了排序处理。例如,在很多情况下应用程序都需要查询某个用户的购物情况,并按照时间进行排序,最后取出最近三次的购买记录,这时使用联合索引可以避免多一次的排序操作,因为索引本身在叶子节点已经排序了。

来看一个例子,首先根据如下代码来创建测试表buy_log:

以上创建了两个索引来进行比较,两个索引都包含了userid字段,如果只对于userid进行查询,如:

从执行计划可以看出,possible_keys在这里有两个索引可供使用,分别是单个的userid索引和(userid,buy_date)的联合索引。但是优化器最终的选择是索引userid,因为该索引的叶子节点包含单个键值,索引理论上一个页能存放的记录应该更多。

接着假定要取出userid为1的最近3次的购买记录,其SQL语句如下,执行计划如下图:

同样,优化器使用了联合索引,因为在这个联合索引中buy_date已经排序好了。根据该联合索引取出数据,无须再对buy_date做一次额外的排序操作。若强制使用userid索引,在Extra则会看到Using filesort,即需要一次额外的排序操作才能完成查询。

正如前面介绍的那样,联合索引(a,b)其实是根据列a、b进行排序的,因此下列语句可以直接使用联合索引得到结果。

然而对于联合索引(a,b,c)来说,下列语句同样可以直接通过联合索引得到结果:

但是对于下面的语句,联合索引不能直接得到结果,其还需要执行一次filesort排序操作,因为索引(a,c)并未排序:

另外对于下面的语句,联合索引也不能直接得到结果,其需要执行一次filesort排序操作:

跟上面的语句相比,不同之处就是b字段从等于变成了大于,就会导致ORDER BY C需要filesort排序操作。因为b=xxx时,c字段必然是排序的,但是b>xxx时,c字段就不是排序的了,所以需要filesort重新排序。联合索引(a,b,c)的索引排序规则为SELECT * FROM TABLE ORDER BY a,b,c。

另外对于buy_log表,如果有如下查询,统计userid等于1,时间大于’2017-10-09’的购买记录:

该如何建立联合索引?对于这种联合索引有一个原则就是查询条件为 “=” 的排在组合索引的最左边,如果有多个条件为 “=”,那么选择性高的排在前面。所以这个联合索引应该创建成下面这样的:

看一下执行计划是否走了这个联合索引:

下面简单描述一下为什么建立组合索引时有查询条件为“=”的排在组合索引的最左边,这跟索引原理有关系。如上组合索引建立应该是 (userid,buy_date),而不应该是 (buy_date,userid)。因为如果把buy_date >= '2017-10-09'条件放在组合索引第一列,会扫描更多的数据。

我们知道索引是排序的,所以如果以userid = '1'为第一列,那么索引排列是如下这样的:

数据库只需要扫描 3 行数据就可以返回结果了,因为数据库会使用二分查找法定位到第一条满足条件的数据页,也就是(1,2017-10-09),然后往后扫描,直到不满足条件时即可。由于组合索引排序的特性,连数据过滤都不需要了。

如果以buy_date >= '2017-10-09'为第一列,那么索引排列是如下这样的:

这个时候数据库就需要从第一个满足条件的行(2017-10-09,1)开始扫描,这里就需要扫描 4 行数据了,并且需要过滤掉不满足条件的数据。其开销比(userid,buy_date)组合索引大。这里案例是数据量小,如果是大数据量情况下,就可以发现其差距了。

如果查询条件由buy_date >= '2017-10-09'变成buy_date > '2017-10-09',可以发现少了 = 号,这个时候对于联合索引 (buy_date, userid) 就更尴尬了,MySQL Server 在生成区间的时候只会使用 buy_date 字段,也就是说这个联合索引只能使用一个字段了。至于为啥,MySQL 如果用上 2 个字段形成扫描区间的话,就意味着要从大于 “2017-10-09” 的记录里找第一条第二个字段是 1 的记录,这个过程是不能用二分法的。

所以对于联合索引的创建基本都是“=”条件字段在前,其余条件再后。而对于都是“=”的条件字段,选择性越高的字段就在前面,选择性低的在后面,这样扫描的数据也会少些。

通过上面的说明,我们知道了只有多个条件为并集的情况下才能很好地使用组合索引。如果是 or 逻辑操作符,如下语句:

这个查询是否能够使用到索引呢?这也同样要看MySQL得到的Cost值,有可能是进行索引扫描,然后刷选出需要的行,再回表。如果这种开销比直接全表扫描大,那么优化器就可能会直接全表扫描了。当然,还有MySQL中还有索引合并这个技术。

总结一下,组合索引创建遵循的基本规则:

  • 多条件混合时,等值查询放在前面。
  • 如果都是等值查询,选择性高的放在前面。
  • 如果都是非等值查询,选择性高的放在前面。

如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (6)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!