摘录：《极客时间》APP 视频内容
由于讲的太好了，没忍住，全部摘录下来了 (^▽^)
疑问：为什么 SQL 语句使用了索引，却还还是慢查询
索引和慢查询的关系
创建一个表来演示这个现象
CREATE TABLE `test`.`t`(`id` INT(11) NOT NULL,`a` INT(11),`b` INT(11),PRIMARY KEY (`id`),KEY `a` (`a`)) ENGINE=INNODB;insert into `t` (`id`, `a`, `b`) values('1','1','1');insert into `t` (`id`, `a`, `b`) values('2','2','2');
MySQL 判断一条语句是否是慢查询语句，用的是语句的执行时间，它把当前语句的执行时间跟 long_query_time 这个系统参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，可以根据自己的业务要求调整这个系统参数。
而对于语句是否使用了索引，它的意思是语句执行过程中有没有用到表的索引，具体到表象中，是 explain 一个语句的时候，数据结果里面 key 的值不是 NULL。
图1是执行 explain select * from t; 语句的结果，这里的 key 值等于 NULL，表示这条语句就没有使用任何索引
图2是执行 explain select * from t where id = 2; 语句的结果，这里的 key 值等于 PRIMARY，表示这条语句使用了主键索引
图3是执行 explain select a from t; 语句的结果，这里的 key 值等于 a，表示这条语句使用索引 ‘a’。
可以看到图2和图3的输出结果里 key 的字段都不是 NULL，而实际上，图3是扫描了整个索引树 ‘a’，这个示例的表里面只有2行数据，那如果有100万行数据呢？有100万行的时候，图2的语句还是可以执行很快，但是图3就肯定很慢了。
所以如果简单地回答这个问题，是否使用索引只是表示一个 SQL 语句的执行过程，而是否记录慢查询，是由它的执行时间决定的，也就是说是否使用索引和是否记录慢查询之间没有必然的联系。
什么叫做使用了索引
我们知道，InnoDB 是索引组织表，所有的数据都是存储在索引树上面的。比如上面创建的表 t，包含了两个索引，一个主键索引和一个普通索引 ‘a’，在 InnDB 里，数据是放在主键索引里面的，我们来看一下这个表的数据示意图：
可以看到，数据都是放在主键索引上的。如果从逻辑上说，所有的 InnDB 表上的查询，都至少用了一个索引。
现在我们来看看下面语句的 explain 的结果，你觉得这条语句有用上索引吗？
在输出结果里面 key 显示的是 PRIMARY，其实从数据上我们知道，这条语句一定是做了全表扫描，但是优化器认为，这个语句的执行过程中，需要根据主键索引定位到第一个满足 id>0 的值，也算用到了索引，所以即使 explain 的结果里写了 key 不是 NULL，实际上也可能是全表扫描。
所以 InnDB 里面只有一种情况，叫做没有使用索引，那就是从主键索引的最左边的叶节点开始向右扫描整个索引树。也就是说，没有使用索引，并不是一个准确的描述，你可以用全表扫描来表示一个查询遍历了整个主键索引树。
也可以用全索引扫描来说明，像 select a from t; 这样的查询，它扫描了整个普通索引树。
而像 select * from t where id = 2; 这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。
索引的过滤性
那么除了全索引扫描，还有那些是使用了索引，但是执行速度不够快的例子呢？这就要说到索引的过滤性。
假设你维护了一张表，这表记录了全中国人的基本信息，然后你现在要查出所有年龄在10到15岁之间的小朋友的姓名和基本信息，那么的语句会这么写 select * from t_people where age between 10 and 15; 你一看，这个语句一定要在 age 字段上建索引了，否则就是一个全表扫描，但是你会发现，在你建了 age 上的索引以后，这个语句还是执行慢，因为满足这个条件的数据有超过了一亿行。
我们来看看，建立了这个索引以后，这个表的组织结构图，这个语句的执行流程是这样的，从索引 age 上用树搜索，取到第一个 age=10 的记录，得到它的主键 ID 的值，根据 ID 值去主键索引取整行的信息，作为结果集的一部分返回，在索引 age 上向右扫描，取下一个 ID 值，到主键索引上取整行信息，作为结果集的一部分返回，重复上面的步骤，知道碰到第一个 age>15 的记录。
这个语句虽然用了索引，但是它扫描了超过一亿行，而上面 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;，在 MySQL 5.5 和之前的版本中，这个语句的执行流程是这样的，首先，从联合索引树上找到第一个字段是张开头的记录，取出主键 ID ，然后到主键索引上，根据 ID 取出整行的值，判断年龄字段是否等于8，如果是就作为结果集的一行返回，如果不是就丢弃，我们把根据 ID 到主键索引上查找整行数据的动作称为回表，在联合索引上向右遍历，并重复做回表和判断的逻辑，直到碰到联合索引树上，名字第一个字不是张的记录为止，可以看到，这个执行过程里面，最耗费时间的步骤就是回表。
假设全国名字第一个字是张的人有8000万，那么这个过程就要回表8000万次，在定位第一行记录的时候只能使用索引和联合索引的最左前缀，称为最左前缀原则。
那你可以看到，这个执行过程它的回表次数特别多，性能不够好，那有没有优化的方法呢？
在 MySQL 5.6 版本引入了 index condition pushdown 的优化，我们来看看这个优化的执行流程。首先，从联合索引树上找到第一个年龄字段是张开头的记录，判断这个索引记录里面年龄的值是不是8，如果是就回表，取出整行数据，如果不是就丢弃，在联合索引树上向右遍历，并判断年龄字段后，根据需要做回表，直到碰到联合索引树上名字的第一个字不是张的记录为止，这个过程跟上面过程的差别是在遍历联合索引的过程中，将 age=8 这个条件下推到索引遍历的过程中，减少了回表的次数，假设全国名字第一个字是张的人里面有100万个是8岁的小朋友，那么这个查询过程中，在联合索引里要遍历8000万次，而回表只需要100万次。
可以看到，index condition pushdown 优化的效果还是很不错的，但是这个优化还是没有绕开最左前缀原则的限制，因此在联合索引里还是要扫描8000万行，那有没有更进一步的优化方法呢？
我们可以把名字的第一个字和年龄做一个联合索引来试试，这里可以使用 MySQL 5.7 引入的虚拟列来实现，对应的修改表结构的 SQL 语句是这么写的：
alter table t_people add name_first varchar(2) generated always as(left(name, 1)), add index(name_first, age);
我们来看这个 DDL 语句的执行效果：
首先它在 t_people 表上创建一个字段叫做 name_first 虚拟列，然后给 name_first 和 age 上，创建一个联合索引，并且让这个虚拟列的值，总是等于 name 字段的前两个子节，虚拟列在插入数据的时候，不能指定值，在更新的时候也不能主动修改，它的值会根据定义自动生成，在 name 字段修改的时候，也会自动修改。
有了这个新的联合索引，我们在找名字的第一个字是张，并且年龄是8的小朋友的时候，这个 SQL 语句就可以这么写：
select * from t_people where name_first='张' and age=8;
这样，这个语句的执行过程就只需要扫描联合索引的100万行，并回表100万次。这个优化的本质是我们创建了一个更紧凑的索引，来加速了查询的过程。
作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/khcrnh 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
若有收获，就点个赞吧
0 人点赞
