阅读 157

MySQL之索引系列(二)

有了索引一定会增加查询速度么,如何利用好索引呢

现在有以下表结构及数据,执行 select * from T between 3 and 5,需要执行几次树的搜索,会扫描多少行?
图1 表的结构:

表结构

图2 表中数据:

表中数据

图3 InnoDB的索引组织结构:

InnoDB的索引组织结构

由图1及图3可知,ID为主键索引,k为非主键索引,K的叶子节点值为主键ID。
以下是语句的执行过程:

  1. 在k索引树查找k=3的记录,得到ID=300;
  2. 根据ID=300查找对应的R3;
  3. 在k索引树查找下一个k=5的记录,得到ID=500;
  4. 根据ID=500找到R4;
  5. 在k索引树找下一个k=6,不满足,退出。

从非主键索引回到主键索引树搜索的过程,称之为回表。以上过程中,读了k索引树的三条记录(步骤1、3、5),回表了两次(步骤2、4)。 在引擎内部通过索引k上其实是读了三个记录:k=3、4、5,但是对于MySQL的server层来说,他在找引擎拿到了两条记录(具体查找过程中server与引擎的执行过程见一条SQL查询语句的执行过程 ),因此MySQL认为扫描行数为2。具体如何查看扫描行数后面的会介绍。
了解了回表的过程,下面来看如何优化索引来避免回表

覆盖索引

上文执行的SQL语句如果是 select ID from T where k between 3 and 5,只是请求ID值,那么这个值已经存在于k索引树上,已经可以直接提供结果,不需要回表。也就是说,新的SQL中索引k已经 "覆盖了" 语句的查询需求,以上称之为覆盖索引。
索引覆盖可以减少树的搜索次数,以此来提升查询性能,所以使用覆盖索引是常见的性能优化手段

基于覆盖索引,来看一个新的例子:
有一个用户表,定义如下:

CREATE TABLE `tuser` (
`id` INT ( 11 ) NOT NULL,
`id_card` VARCHAR ( 32 ) DEFAULT NULL,
`name` VARCHAR ( 32 ) DEFAULT NULL,
`age` INT ( 11 ) DEFAULT NULL,
`ismale` TINYINT ( 1 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `id_card` ( `id_card` ),
KEY `name_age` ( `name`, `age` ) 
) ENGINE = INNODB
复制代码

已知通过身份证号是唯一标识,为了满足根据身份证号查到一个用户的所有信息,只需要对身份证号建立索引就够了。在建立一个(id_Card,name)的联合索引,究竟有没有必要?

如果现在有这样一个高频请求:根据身份证号找到用户的姓名,那么这个联合索引的意义就体现出来了,这个高频的请求能够用到覆盖索引,因为在(id_Card,name)这个联合索引树上直接能根据身份证号查到姓名,不用在拿到主键ID后回表查找整行记录,减少语句的执行时间。 覆盖索引的目的就是尽量能够一次查到需要数据,避免有回表过程。

最左前缀原则

按照刚刚的需求,为了根据身份证号查到姓名,需要创建(id_card,name)联合索引,如果现在新的需求需要根据身份证号查到家庭住址,总不能再添加一个(id_card,addr)联合索引,为了每一种查询都设计一个索引,显然是不现实的。 这时候应该怎么做呢?
结论:B+树这种索引结构,可以利用索引的"最左前缀"来定位记录

以联合索引("name",age)为例
图4 (name,age)索引示意图:

图 4 (name,age)索引示意图

由上图可知索引项是按照索引定义的字段顺序排序的。

如果要查询 "name = '张四'" 的所有结果时,会快速定位到D4,然后向后遍历到D5结束,返回结果。
如果想查找所有姓张的结果,则条件为"where name like '张%'" ,此时还是可以用上这个联合索引,定位到D3,然后向后遍历,直到D5结束,返回结果。

由上面两个查询可知,只要满足最左边的N个字段或者M个字符,就可以用这个索引来查询。

以上结论又引发一个新的问题:考虑到最左前缀原则,建立联合索引时,内部的字段顺序该如何安排?

  1. 如果已经存在(a,b)联合索引,则不需要对a字段建立索引。如果能够调整联合索引的顺序,来减少一个索引,name这个顺序应该被优先考虑,以此来提高索引的复用能力。 回到本节开头的问题:没有必要为(id_card,addr)建一条索引。可以复用已经存在高频使用的(id_card,name)联合索引,在根据id_card查找地址时,也能够根据最左前缀原则匹配到(id_card,name)索引,以此来增加查找速度。

  2. 对于id_card和name字段,可以建立三个索引搜索树: 对id_card和name分别建立索引、对(id_card,name)建立联合索引。根据上一条原则,可以通过调整联合索引的字段顺序来减少一个索引,即保留"(id_card,name)和name索引"或者"保留(name,id_card)和id_card索引"。这时需要考虑空间的占用情况了,显然id_card要比name字段大,那就尽量复用id_card,建议使用(id_card,name)联合索引和name单字节索引。

索引下推

图 4 (name,age)索引示意图
还是以上图为例,分析以下SQL执行过程

select * from tuser where name like '张%' and age = 23;    
复制代码
  • 在MySQL5.6之前,通过最左前缀原则定位到D3,然后从D3开始一个一个回表,到主键索引树上找到对应行对比age字段的值,直到D5结束,不能匹配到'张%'后退出查询,以上共回表3次。虽然比遍历全表快,但是还是做了多次无用的回表操作。

  • 在MySQL5.6及以后,引入了索引下推优化(index condition pushdown),在对联合索引遍历的过程中,根据索引中包含的字段对查询语句的其他条件进行判断,以此过滤不满足条件的记录,减少回表次数。对于上述例子来说,就是innoDB在联合索引搜索树根据最左前缀定位到D3,接着判断age字段不等于23,继续向后遍历,D4中name符合"张%",但age不等于23,继续直到D5,name与age都符合,这时才会回表。以上过程只需要回表1次。

总结

三种优化方式:

  • 覆盖索引
  • 最左前缀原则
  • 索引下推
    以上三种方式都有同一个原则,也是数据库设计的重要原则之一:尽量少地访问资源达到同样的效果

查询语句中where里面顺序和联合索引顺序不一致,优化器会自动做优化。


本文为极客时间《MySQL实战45讲》 的学习笔记,其中含有部分原文,如有侵权行为请联系我立刻删除
再次感谢丁奇大佬
第一节:一条SQL查询语句的执行过程