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

MySQL InnoDB联合索引讲解

MySQL InnoDB 彭东稳 426次浏览 已收录 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列的查询使用不到(a,b)的索引。

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

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

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

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

接着假定要取出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' 为第一列,那么索引排列是如下这样的:

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

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

这个时候数据库就需要扫描4行数据了,并且需要过滤掉不满足条件的数据。其开销比(userid,buy_date)组合索引大。

这里是数据量小,如果是大数据量情况下,就可以发现差距了。


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

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