前 言本文是作者对林晓斌“为什么SQL语句使用了索引，但却还是慢查询？”视频课程的内容总结与扩展，如有勘误，请在微信公众号内发送消息指出。以下内容将基于MySQL详述。为了方便阅读，将尽量不涉及除SQL外的语言代码。阅读这篇文章你需要：SQL基本语法阅读能力；熟悉树状结构。一、什么是索引根据某度百科定义：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。这里有两个重要关键词，排序与指针。接下来，我将就MySQL索引的主要两种数据结构——B+Tree和Hash，进行简单解读。B+Tree，中文读作B加树，基于B-Tree（读作B树，千万不要读B减树）改进的一种树状数据结构。假设现在建立一个以ID为自增主键的B+Tree索引，则如下图所示。(图片来自cnblogscoshaho)可以看到，树节点是由顺序访问指针组成，数据则以类链表形式存储在叶子节点中。举个例子，当我要查询 ID = 20的那一行数据，若不使用索引，则将从 ID = 1 开始扫描，扫描到第二十行，where条件成立，返回数据；若使用上述索引，从第一层开始 -> 15-56，命中 -> 进入第二层 -> 15-20，未命中 -> 20-49，命中 ->进入第三层 -> 20，where条件成立，返回数据。对比两者操作数，20比4（索引实际上可能使用二分查找等多种查找方法来节省时间复杂度，此处仅做简单示例讲解），索引的优势就在于此：减少IO次数（而这实际上是所有MySQL优化的根本目的）。而Hash索引就是采用一定的哈希算法，生成一个哈希表，在大学必修课程中有讲解，故本文跳过。本文将采用B+Tree索引进行论述。而对上述两者的详述与比较，我将另开文（又给自己挖个坑）。二、什么是慢查询MySQL在执行查询语句时，会将语句的执行时间与long_query_time（默认值为10秒，实际生产中一般设置为1秒）系统参数作比较，若语句的执行时间大于long_query_time，就会把这个语句记录到慢查询日志中。三、为什么使用了索引还是会慢查询这里我新建一个表格t，其中有三个字段：id、a、b。其中，对id设置主键索引，对a设置普通索引。
下面我进行三个查询：
1) select * from t;2) select * from t where id = 2;3) select a from t;
由上图可以看出，1)没有使用查询，2)使用了主键查询，3)使用了a索引。但是这里值得注意的是，2)的结果是一行，3)则会执行全表扫描。如果表内有一百万行数据时，2)的执行仍然很快，而3)的执行可能就会拖慢。甚至更极端的情况下，CPU的压力非常高，那么2)的执行时间也有可能超过long_query_time，会记录到慢查询日志里。所以如果简单地回答这个问题，是否使用索引，只是表示了一个SQL语句的执行过程，而是否慢查询，是由SQL语句的执行时间决定的，而这个执行时间可能受各种外部因素的影响。也就是说是否使用索引和是否慢查询之间并没有必然的联系。如果我们更深层次地看待这个问题，其实它还潜藏着一个问题需要澄清：什么叫做使用了索引？InnoDB是索引组织表，所有的数据都是存储在索引树上面的。比如这个表里有两个索引——主键索引id和普通索引a，这个表的数据是存放在主键索引上的。数据示意图如下：
从逻辑上说，所有在InnoDB表上的查询，都至少使用了一个索引。那么现在执行
select*fromtwhereid>0
这个语句会使用索引吗？
可以看到，key一栏显示“PRIMARY”，使用了主键索引。其实从数据上我们是知道的，这个语句一定做了全表扫描。但是优化器认为，这个语句的执行过程中需要根据主键索引，定位到第一个满足id>0的值。所以即使explain里写了key不是NULL，实际上也可能是全表扫描的。因此，InnoDB里没有使用索引只有一种情况，就是从主键索引树的最左边叶结点向右扫描整个结点树。“没有使用索引”并不是一个准确的描述，可以用全表扫描来表示一个查询遍历了整个主键索引树，亦可以用全索引扫描来说明像
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索引后的结构图。那么这个语句的执行过程如下：1）从索引age上用树搜索取到第一个age=10的记录，得到它主键ID的值；2）根据ID值去主键索引取整行的信息，作为结果集的一部分返回；3）在索引age上向右扫描，取下一个ID值；4）重复2）、3），直到3）碰到第一个age>15的记录；5）返回所有结果集。由此看出，这个语句虽然使用了索引，但是扫描超过一亿行，而最开始
select * from t;
这个语句，虽然没有使用索引，但其实也只扫描了两行。事实上讨论有没有使用索引时，我们真正关心的是扫描行数。对于一个大表，不止要有索引，索引的过滤性还要足够好。像刚才这个索引age的过滤性就不够好。在设计表结构的时候，我们要让索引的过滤性足够好，也就是区分度足够高。那么过滤性好了是不是表示查询的扫描行数就一定少呢？再来看一个例子。如果在t_people表上，有一个索引是姓名年龄的联合索引，那这个联合索引的过滤性应该不错。如果你的执行语句是
select * from t_people where name = "张三" and age = 8;
就可以在联合索引上快速找到姓名"张三"年龄为8的记录。当然符合条件的记录不多，因此扫描行数就很少。
但是查询的过滤性和索引的过滤性并不一定是一样的。如果要查找姓名为"张"年龄为8的记录，那么SQL应该这么写：
select * from t_people where name like "张%" and age = 8;
在MySQL5.5和之前的版本中，这个语句的执行流程是这样的：1）从联合索引树上找到第一个name字段是"张"开头的记录，取出主键ID；2）到主键索引上，根据ID取出整行的值；3）判断age字段是否等于8，如果是，作为结果集的一行返回；4）重复上述步骤，知道name字段不为"张"。我们把根据ID到主键索引上查整行数据这个动作称为"回表"。而整个过程中，最耗时间的步骤就是回表。假设全国姓张的人有8000万，那么这个过程就要回表8000万次。在定位第一行记录的时候，只能使用索引和联合索引的最左前缀，称为最左前缀原则。那可以看到，这个执行过程的回表次数特别多，性能不够好，有没有优化的方法呢？在MySQL5.6版本引入了index condition pushdown的优化。这个优化的执行过程是：1）从联合索引树上找到第一个name字段是"张"开头的记录；2）判断这个索引的age字段是不是8，若是则回表，取出整行数据；若不是则丢弃，向右遍历下一个记录；3）重复2）步骤，直到第一个name字段不是"张"开头的记录。这个过程和上个过程的差别在于判断在回表操作前，可以大幅减少回表操作，节省了大量时间。假设全国姓张、年龄为8的人有100万，那这个过程只需回表100万次。index condition pushdown的优化效果还是很不错的，但是这个优化还是没有绕开最左前缀原则的限制（联合索引遍历次数仍为8000万次）。那么有没有更进一步的优化方法呢？我们可以把name字段的第一个字和age做联合索引来试试。这里可以用到MySQL5.7引入的虚拟列来实现。
可以看到，根据第一张图内的DDL语句后，表内创建了一个名字为name_first的虚拟列，并让这个虚拟列的值为name字段的第一个字。同时创建了一个name_first和age的联合索引。
虚拟列在插入时不能指定值，在更新的时候也不能主动修改，它的值会根据定义自动生成，在name字段修改的时候，也会自动修改。有了这个联合索引，在找姓"张"且年龄为8的记录时，语句就可以这么写：
select * from t_people where name_first = "张" and age = 8;
这个SQL执行时，只需扫描100万行，同时回表100万次。而这个优化的本质是创建了一个更紧凑的索引，来加速了查询的过程。五、总 结全文通过分析索引，讲述了索引在查询过程中是如何执行，并达到减少查询时间的目的的。总之还是一句话，所有的优化均是为了减少IO操作数（扫描行数和回表）。希望今天的分享能对你有所帮助。
