为什么用了索引，查询还是慢？

经常有同学问我，我的一个SQL语句使用了索引，为什么还是会进入到慢查询之中呢？今天我们就从这个问题开始来聊一聊索引和慢查询。

另外插入一个题外话，个人认为团队要合理的使用ORM，可以参考 ORM的权衡和抉择(http://rrd.me/f9jCg)。合理利用的是ORM在面向对象和写操作方面的优势，避免联合查询上可能产生的坑(当然如果你的Linq查询能力很强另当别论)，因为ORM屏蔽了太多的DB底层的知识内容，对程序员不是件好事，对性能有极致追求，但是ORM理解不透彻的团队更加要谨慎。

案例剖析　

言归正传，为了实验，我创建了如下表：

CREATE TABLE `T`(
`id` int(11) NOT NULL,
`a` int(11) DEFAUT NULL,
PRIMARY KEY(`id`),
KEY `a`(`a`)
) ENGINE=InnoDB;

该表有三个字段，其中用id是主键索引，a是普通索引。

首先SQL判断一个语句是不是慢查询语句，用的是语句的执行时间。他把语句执行时间跟long_query_time这个系统参数作比较，如果语句执行时间比它还大，就会把这个语句记录到慢查询日志里面，这个参数的默认值是10秒。当然在生产上，我们不会设置这么大，一般会设置1秒，对于一些比较敏感的业务，可能会设置一个比1秒还小的值。

语句执行过程中有没有用到表的索引，可以通过explain一个语句的输出结果来看KEY的值不是NULL。

我们看下 explain select * from t;的KEY结果是NULL

explain select * from t where id=2;的KEY结果是PRIMARY，就是我们常说的使用了主键索引

explain select a from t;的KEY结果是a，表示使用了a这个索引。

虽然后两个查询的KEY都不是NULL，但是最后一个实际上扫描了整个索引树a。

假设这个表的数据量有100万行，图二的语句还是可以执行很快，但是图三就肯定很慢了。如果是更极端的情况，比如，这个数据库上CPU压力非常的高，那么可能第2个语句的执行时间也会超过long_query_time，会进入到慢查询日志里面。

所以我们可以得出一个结论：是否使用索引和是否进入慢查询之间并没有必然的联系。使用索引只是表示了一个SQL语句的执行过程，而是否进入到慢查询是由它的执行时间决定的，而这个执行时间，可能会受各种外部因素的影响。换句话来说，使用了索引你的语句可能依然会很慢。

全索引扫描的不足

那如果我们在更深层次的看这个问题，其实他还潜藏了一个问题需要澄清，就是什么叫做使用了索引。

我们都知道，InnoDB是索引组织表，所有的数据都是存储在索引树上面的。比如上面的表t，这个表包含了两个索引，一个主键索引和一个普通索引。在InnoDB里，数据是放在主键索引里的。如图所示：

可以看到数据都放在主键索引上，如果从逻辑上说，所有的InnoDB表上的查询，都至少用了一个索引，所以现在我问你一个问题，如果你执行select from t where id>0，你觉得这个语句有用上索引吗？

我们看上面这个语句的explain的输出结果显示的是PRIMARY。其实从数据上你是知道的，这个语句一定是做了全面扫描。但是优化器认为，这个语句的执行过程中，需要根据主键索引，定位到第1个满足ID>0的值，也算用到了索引。

所以即使explain的结果里写的KEY不是NULL，实际上也可能是全表扫描的，因此InnoDB里面只有一种情况叫做没有使用索引，那就是从主键索引的最左边的叶节点开始，向右扫描整个索引树。

也就是说，没有使用索引并不是一个准确的描述。

• 你可以用全表扫描来表示一个查询遍历了整个主键索引树；
  
• 也可以用全索引扫描，来说明像select a from t;这样的查询，他扫描了整个普通索引树；
  
• 而select * from t where id=2这样的语句，才是我们平时说的使用了索引。他表示的意思是，我们使用了索引的快速搜索功能，并且有效的减少了扫描行数。
  

索引的过滤性要足够好

• 从索引上用树搜索，取到第1个age等于10的记录，得到它的主键id的值，根据id的值去主键索引取整行的信息，作为结果集的一部分返回；
  
• 在索引age上向右扫描，取下一个id的值，到主键索引上取整行信息，作为结果集的一部分返回；
  
• 重复上面的步骤，直到碰到第1个age大于15的记录；
  

回表的代价

在MySQL5.5和之前的版本中，这个语句的执行流程是这样的:

• 首先从联合索引上找到第1个年龄字段是张开头的记录，取出主键id，然后到主键索引树上，根据id取出整行的值；
  
• 判断年龄字段是否等于8，如果是就作为结果集的一行返回，如果不是就丢弃。
  
• 在联合索引上向右遍历，并重复做回表和判断的逻辑，直到碰到联合索引树上名字的第1个字不是张的记录为止。
  

• 首先从联合索引树上，找到第1个年龄字段是张开头的记录，判断这个索引记录里面，年龄的值是不是8，如果是就回表，取出整行数据，作为结果集的一部分返回，如果不是就丢弃；
  
• 在联合索引树上，向右遍历，并判断年龄字段后，根据需要做回表，直到碰到联合索引树上名字的第1个字不是张的记录为止；
  

虚拟列

alter table t_people add name_first varchar(2) generated (left(name,1)),add index(name_first,age);

CREATE TABLE `t_people`(
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAUT NULL,
`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,KEY `name_first`(`name_first`,'age')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

总结

• 全表扫描
  
• 全索引扫描
  
• 索引过滤性不好
  
• 频繁回表的开销
  

如果看到这里，说明你喜欢这篇文章，请转发、点赞。同时标星（置顶）本公众号可以第一时间接受到博文推送。

推荐阅读

1. Spring Boot+Redis+实现接口自动幂等

2. Spring Boot 整合微信小程序

3. 安利一款 IDEA 中强大的代码生成利器

4. Typora 终于支持图片上传了！