为什么使用索引后查询仍然很慢？

各位老铁们，大家好，今天由我来为大家分享为什么使用索引后查询仍然很慢？，以及的相关问题知识，希望对大家有所帮助。如果可以帮助到大家，还望关注收藏下本站，您的支持是我们最大的动力，谢谢大家了哈，下面我们开始吧！

案例剖析　

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

CREATETABLE`T`(`id`int(11)NOTNULL,`a`int(11)DEFAUTNULL,PRIMARYKEY(`id`),KEY`a`(`a`))ENGINE=InnoDB;该表有3个字段，其中id为主键索引，a为普通索引。

首先，SQL通过语句的执行时间来判断该语句是否为慢查询语句。它将语句执行时间与系统参数long_query_time进行比较。如果语句执行时间大于它，则该语句将被记录在慢查询日志中。该参数的默认值为10 秒。当然，在生产中，我们不会设置这么大的值。一般我们将其设置为1秒。对于一些比较敏感的业务，我们可能会设置一个小于1秒的值。

语句执行时是否使用表索引，通过解释语句的输出可以看到KEY的值不为NULL。

我们看一下解释select * from t; KEY 结果为NULL

（图1）

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

（图2）

解释select a from t的KEY结果；是a，表示使用索引a。

（图3）

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

假设这张表的数据量是100万行，图2的语句还是可以很快执行的，但是图3肯定会很慢。如果是比较极端的情况，比如这个数据库的CPU压力很大，那么第二条语句的执行时间可能会超过long_query_time，就会进入慢查询日志。

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

全索引扫描的不足

如果我们更深层次地看这个问题，其实有一个隐藏的问题需要弄清楚，这就是使用索引的意义。

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

可以看到数据是放在主键索引上的。从逻辑上讲，所有对InnoDB表的查询都至少使用一个索引，所以现在让我问你一个问题。如果你执行select from t where id0 ，你认为这条语句对索引有用吗？

让我们看看上面的解释语句的输出显示PRIMARY。其实从数据上就知道，这条语句一定是被全面扫描过的。但优化器认为，这条语句执行过程中，需要根据主键索引定位到第一个满足ID0的值，可以认为是使用了索引。

所以即使explain结果中写入的KEY不为NULL，实际上也可能是全表扫描。因此，InnoDB中只有一种情况称为无索引，即从主键索引最左边的叶子节点开始，向右扫描。整个索引树。

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

可以使用全表扫描来表示一个查询遍历整个主键索引树；您还可以使用完整索引扫描来指示查询，例如select a from t;它扫描整个普通索引树；而select * from t where id=2 这样的语句就是我们通常所说的使用索引。他的意思是我们利用索引的快速搜索功能，有效减少扫描行数。

索引的过滤性要足够好

根据上面的分析，我们知道全索引扫描会减慢查询速度。接下来我们要讲一下索引的可过滤性。

假设你目前维护着一张表，记录了中国14亿人的基本信息。现在您想要找出所有年龄在10 岁到15 岁之间的人的姓名和基本信息。那么你的语句将是这样的，select * from t_people 其中年龄在10到15之间。

你看这条语句的时候，一定要在age字段上开始建索引，不然就是全盘扫描，但是你会发现，创建了索引之后，这条语句执行起来还是很慢，因为满足这个条件的数据可能会超过1亿OK。

我们看一下这张表创建索引后的组织图：

该语句的执行流程如下：

使用树形搜索从索引中获取第一条age等于10的记录，获取其主键id的值，根据id值去主键索引获取整行信息，并作为部分返回结果集；在索引age上向右扫描，获取下一个id的值，从主键索引中获取整行信息，并作为结果集的一部分返回；重复上述步骤，直到遇到第一个年龄大于15的记录；看这个语句，虽然他使用了索引，但是他扫描了超过1亿行。现在你知道了，当我们讨论是否使用索引时，我们实际上关心的是扫描的行数。

对于大表来说，不仅要有索引，而且索引的过滤性能也要足够好。

像刚才例子中的年龄，它的过滤性还不够好。在设计表结构的时候，一定要让所有的过滤性足够好，也就是区分度足够高。

回表的代价

那么如果过滤得好，是不是意味着查询中扫描的行数一定会少呢？

让我们看另一个例子：

如果你的执行语句是select * from t_people where name=’张三’ and Age=8

t_people表上有一个索引，是姓名和年龄的联合索引。这个联合索引的过滤性能应该不错。你可以很快地在联合索引上找到第一个孩子，名字叫张三，年龄为8。当然，这样的子项应该不会很多，这样向右扫描的行数就很少，查询效率就很高。

然而，查询的可过滤性和索引的可过滤性不一定相同。如果你现在的需求是找到所有第一个字是张、年龄是8岁的孩子，你的语句会怎么写？

你应该如何写你的陈述？显然你会这样写： select * from t_people where name like ‘张%’ andage=8;

在MySQL5.5及以前的版本中，该语句的执行流程如下：

首先从联合索引中找到第一个age字段为open header的记录，取出主键id，然后到主键索引树中，根据id取出整行的值；判断age字段是否等于8，如果等于则作为结果集返回一行，如果不等于则丢弃。向右遍历联合索引，重复回表判断逻辑，直到遇到联合索引树名首字符不是Zhang的记录。我们把根据主键索引上的id查找整行数据的动作称为返回表。可以看到这个执行过程中最耗时的一步就是返回表。假设全国有8000万人的名字是张，那么这个过程就需要回表8000万次，并且记录在定位的第一行时，只需要索引最左边的前缀和联合可以使用索引，这就是所谓的最左前缀原则。

可以看到这个执行过程。表返回较多，性能不够好。有什么办法可以优化吗？

MySQL 5.6版本中，引入了索引条件下推的优化。我们看一下本次优化的执行流程：

首先从联合索引树中找到第一个age字段为open header的记录，判断这条索引记录中的age值是否为8，如果是，则返回表，取出整行数据，将其作为结果集的一部分返回。如果没有，则丢弃；在联合索引树中向右遍历，判断出年龄字段后，根据需要返回表，直到遇到联合索引树中姓名首字符不是Zhang的记录；这个过程和上面是一样的，不同的是，在遍历联合索引的过程中，将age等于8的条件推送到所有的遍历中，这样就减少了表返回的次数。假设全国有100 万人，名字叫张。如果孩子是8岁的孩子，那么这次查询的时候联合索引要遍历8000万次，而表返回只需要100万次。

虚拟列

可以看出，本次优化的效果还是很好的，但是本次优化依然没有规避最左前缀原则的限制。因此，你仍然需要扫描联合索引中的8000万行。还有进一步的优化方法吗？

我们可以考虑在名字的第一个字符和年龄之间建立一个联合索引。这可以使用MySQL 5.7 中引入的虚拟列来实现。修改表结构对应的SQL语句：

altertablet_peopleaddname_firstvarchar(2) generated(left(name,1)),addindex(name_first,age);我们看一下这条SQL语句的执行效果：

CREATETABLE`t_people`(`id`int(11)DEFAULTNULL,`name`varchar(20)DEFAUTNULL,`name_first`varchar(2)GENERATEDALWAYSAS(left(`name`,1))VIRTUAL,KEY`name_first`(`name_first `,’年龄’))ENGINE=InnoDBDEFAULTCHARSET=utf8;首先，他在people上创建了一个名为name_first的字段的虚拟列，然后在name_first和age上创建了联合索引，并使得这个虚拟列的值始终等于name字段的前两个字节，虚拟列不能插入数据时指定一个值，更新时也不能主动修改。它的值会根据定义自动生成，并且当name字段修改时会自动修改。

有了这个新的联合索引，当我们查找名字为“张”、年龄为8 的孩子时，这条SQL 语句可以这样写： select * from t_people where name_first=’Zhang’ andage=8。

这样，这条语句的执行过程只需要扫描联合索引100万行，返回表100万次。这种优化的本质是我们创建更紧凑的索引来加速查询过程。