为什么我的 SQL 语句明明使用了索引，但却还是会记录到慢查询语句中?

背景描述：

为什么我的 SQL 语句明明使用了索引，但却还是会记录到慢查询中？我有一个大概 13 亿行数据的 MySQL 表 t_people，其中包括字段 ID、AGE、NAME、ADDRESS 等，现在我想查询所有年龄在 10 到 15 岁之间的小朋友，为了提高查询效率，于是我给 AGE 字段建立了索引。但建完索引之后，我使用 SQL 语句“select * from t_people where age between 10 and 15”开始查询，查询之后发现这条语句居然是个慢查询。你知道为什么吗？我应该如何优化？

---

索引

首先看一下表结构

id主键，a为普通的索引

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `a_index` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

首先sql判断一个语句是不是满查询跟是否使用索引无关。

而是通过查询时间来判断该语句是不是满查询语句。如果语句的执行时间大于long_query_time，则该语句被标记为满查询语句。该执行的时间还和其他很多因素有关，例如当前CPU的使用率很高，之前不是满查询，只会当CPU的使用率特别高的时候，当前的语句也有可能进入到满查询当中来。

long_query_time的默认值是10s，，当然生产上不会设置这么大劲畅是1s或者更短。

key为null表示没有使用索引 

key为primary标示使用了主键id索引 

 表示使用了a_index a这个普通索引。

图三实际上是扫描了整个索引树a，如果这个表有100w行，则会扫面100w行。图2回执行很快，但是图3就会变慢了。

InnoDB中数据是放在主键索引中的。逻辑上讲，所有的InnoDB表上的查询都会至少使用一个索引。

使用索引的理解 

这个查询一定是做了全表扫描，但是我们看到他还是使用了索引，优化器认为，这个语句的执行过程，需要根据主键索引，找到第一个id>0的值。也算用到了索引。所以key不是null，也有可能是全表扫描的。

InnoDB中只有一种情况是没有使用索引的。从主键索引的最左边的叶节点起，向右扫描整个索引树。也就是说没有使用索引并不是一个准确的描述，你可以用全表扫描来表示，一个查询遍历了整个主键索引树。页可以用全索引扫描来说明像select a from t这样的查询，他扫描了整个普通索引树。而向select * from t where ID=‘1’；这样的语句才是我们平时所说的使用了索引。他表示的是我们使用了索引的快速搜索功能，并且有效的减少了扫描行数。

除了全索引扫描，其他那些还使用了索引？但是执行速度还不够快的例子你=呢？？？

说到这个问题，还需要说到索引的过滤性。这就要说引言部分的例子。

假设现在维护了一个表，维护了全中国人的基本信息。然后你要查出全国年龄在10-15岁的全部人员的基本信息。

select * from t_people where age between 10 and 15；

这个语句一定在age字段上建立索引了。否则就是一个全表扫描。但是你会发现，既是建立了age这个索引之后，语句的执行速度还是很慢，因为满足这个条件的数据有超过1亿行，所以扫描的行数太多，语句的执行速度依然会很慢。

建立这个索引之后，表的组织结构图是什么样子的呢？？

这个语句的执行流程是这样的，从 索引age上，用树搜索，取到第一个age=10的记录。得到他的主键id的值。根据这个ID值，到主键索引取整行的信息，作为结果集的一部分返回。在索引age上向右扫描。取下一个id值，之后再到主键索引取整行信息，作为结果集的一部分返回重复上述步骤，直到age不满足条件。虽然这个语句使用了索引，但是他的扫描行数超过了1亿行。

上面select * from t；这个语句，虽然没有使用索引但是只是扫描了两行。所以我们现在知道了，我们平时所说的是否使用了索引，最本质的目的是我们关注最终数据库扫描的行数。对于一个大表，我们不止是要使用索引，索引的过滤行还要很好才行。

像刚才age这个索引，他的过滤行就不是很好。过滤性就是索引的区分度足够高。那么过滤性好了，是不是就一定表示查询扫描的行数就一定少呢？？

例如这个t_people表上有一个姓名，年龄 的联合索引。这个联合索引的过滤性应该不错。

如果你的执行语句是

select * from t_people where name=‘张三’ and age=8；

就能够在表上迅速找到姓名是张三，年龄是8的人。当然这样的人的数量应该不多，当然向右扫描的行数应该不多。查询的效率就很高。

但是查询的过滤性和索引的过滤性可不一定是一样的。

例如当前的需求是查下行吗第一个字是张，年龄是8的人。

select * from t_people where name like ‘张%’ and age=8；

在mysql5.5和之前的版本中该语句的执行流程是这样的。

1、首先从联合索引树上找到第一个年龄字段是张开头的记录，取出主键ID，然后到主键索引上根据ID取出整行的值，之后判断年龄字段是否=8，如果是就作为结果集的一行返回，如果不是就丢弃。

我们把根据ID到主键索引上查找的数据这个动作叫做回表。

在联合索引上向右遍历，并重复做回表操作和判断的逻辑直到碰到联合索引树上，名字的第一个字不是张的记录为止，我们可以看到这里最耗时的操作就是回表。

假设姓张的人有8000w，那么这个过程就要回表8000w。在定位第一行记录的时候，只能使用索引和联合索引的最左前缀。成为最左前缀原则。我们可以看到这个执行过程，他的回表次数特别多，性能不够好。那么有没有优化到方法呢？

在mysql5.6版本，index condition pushdown的优化。这个优化的执行流程是这样的

首先在联合索引树上，找到第一个年龄字段是张开头的记录。判断这个索引记录里面年龄值是不是8，如果是就回表，取出整行数据作为结果集的一部分返回。如果不是就丢弃。

在联合索引数上向右遍历，并判断年龄字段后，再判断是否需要回表（年龄在联合索引这就判断了），知道遇到联合索引树上姓名第一个字不是张的为止。

和之前的版本差别是，在遍历联合索引的过程中将age=8的判断，下推到索引遍历中。减少了回表次数。假设全国人口中姓名为张的人有200w age=8的人。在联合索引中要遍历8000w次，而回表只需200w次。可以看到index condition pushdown的优化效果还是很不错的。但是我们看到还是没有绕开最左前缀原则的限制。因此在联合索引中，还是要扫描8000w次。

那么还有没有跟进一步的优化呢？？

我们可以把名字的第一个字和年龄做一个联合索引来试一试。

这里使用mysql5.7引入的虚拟列，来实现，对应的sql为。

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

这个DDL的执行效果如下，首先增加了name_first这个虚拟列，然后给name_first和age上创建一个联合索引name_first，并且让这个虚拟列的值总是等于name字段的前两个字节，虚拟列在插入字段的时候不能指定值，在更新的时候不能主动修改，他的值会根据定义主动生成。在name字段修改的时候也会自动修改。

 有了这个新的联合索引，我们再找第一个字段是张，并且年龄是8的人的时候，sql语句可以这样写。

 select * from t_people where name_first=‘张’ and age=8；

这样，只需要扫描联合索引的200w行，并回表200w次。

这个优化的本质是我们创建了跟加紧凑的索引。来加速了查询的过程

总结

所以搜索的快慢与是否使用了索引没有必然的联系，既是使用了索引，扫描的表行数太多，改语句依然有可能是满查询语句。所以，我们在创建索引的时候，要尽量的减少扫描的行数。

只有减少扫描的行数，我们的查询速度才会更加快速。嗯，下次我们肯定就知道该如何增加和设置索引了，原则就是增加索引的区分度，尽量的减少扫描的行数，缩短查询时间，而不是随便找一个字段就设置成索引。