注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL InnoDB联合索引学习

MySQL InnoDB 彭东稳 3606次浏览 已收录 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字段有相同的值时,那么b字段就是排序的了。

回到上图中,对于查询“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'为第一列,那么索引排列是如下这样的:

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

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

这个时候数据库就需要从第一个满足条件的行“ 2017-10-09 |      1”开始扫描,这里就需要扫描4行数据了,并且需要过滤掉不满足条件的数据。其开销比(userid,buy_date)组合索引大。

这里案例是数据量小,如果是大数据量情况下,就可以发现其差距了。所以对于联合索引的创建基本都是“=”条件字段在前,其余条件再后。而对于都是“=”的条件字段,选择性越高的字段就在前面,选择性低的在后面,这样扫描的数据也会少些。

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

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

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

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

如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (4)or分享 (0)
关于作者:

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