使用了索引查询还是慢的原因是什么

本文介绍了“使用索引查询或慢速查询的原因是什么”的知识。很多人在实际案例的操作中会遇到这样的困难。让边肖带领你学习如何处理这些情况。希望大家认真阅读，学点东西！

案例剖析　

总之，对于实验，我创建了下表：

createtable ` t `(` id ` int(11)NOTNULL，` int(11)DEFAUTNULL，PRIMARYKEY(`id `)，KEY ` a `(` a `))ENGINE=Innodb；该表有三个字段，其中id是主键索引，A是普通索引。

首先，SQL使用语句的执行时间来判断一条语句是否是慢速查询语句。他将语句执行时间与系统参数long_query_time进行了比较。如果语句执行时间比它长，该语句将被记录在慢速查询日志中。此参数的默认值为10秒。当然，在制作中，我们不会把它设置得那么大，但我们通常会设置为1秒。对于一些敏感业务，我们可能会设置小于1秒的值。

通过解释KEY的值不为NULL的语句的输出结果，可以看出在语句执行过程中是否使用了表的索引。

让我们来看看解释从t中选择*吧；的密钥结果为空。

(图1)

解释从t中选择*其中id=2；KEY结果是PRIMARY，也就是我们常说的使用主键索引。

(图2)

解释从t中选择a；的KEY结果是，表示使用了索引a。

(图3)

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

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

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

全索引扫描的不足

如果我们从更深层次来看这个问题，其实还有一个隐藏的问题需要澄清，那就是索引有什么用？

众所周知，InnoDB是一个索引组织表，所有的数据都存储在索引树上。例如，上面的表T包含两个索引，一个主键索引和一个公共索引。在InnoDB中，数据被放在主键索引中。如图所示：

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

当我们查看上面语句的解释输出时，它显示了PRIMARY。实际上，你从数据中知道，这份声明一定是经过全面扫描的。但是优化器认为在执行这个语句的过程中，需要根据主键索引来定位第一个满足ID0的值，索引也是使用的。

因此，即使解释结果中写入的KEY不是NULL，实际上也可能在整个表中被扫描。因此，InnoDB中只有一种情况叫做不使用索引，即从主键索引最左边的叶节点开始，向右扫描整个索引树。

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

您可以用全表扫描来表示查询。

遍历了整个主键索引树；

索引的过滤性要足够好

根据以上解剖，我们知道全索引扫描会让查询变慢，接下来就要来谈谈索引的过滤性。

假设你现在维护了一个表，这个表记录了中国14亿人的基本信息，现在要查出所有年龄在10~15岁之间的姓名和基本信息，那么你的语句会这么写，select * from t_people where age between 10 and 15。

你一看这个语句一定要在age字段上开始建立索引了，否则就是个全面扫描，但是你会发现，在你建立索引以后，这个语句还是执行慢，因为满足这个条件的数据可能有超过1亿行。

我们来看看建立索引以后，这个表的组织结构图：

这个语句的执行流程是这样的:

•  从索引上用树搜索，取到第1个age等于10的记录，得到它的主键id的值，根据id的值去主键索引取整行的信息，作为结果集的一部分返回；

•  在索引age上向右扫描，取下一个id的值，到主键索引上取整行信息，作为结果集的一部分返回；

•  重复上面的步骤，直到碰到第1个age大于15的记录；

你看这个语句，虽然他用了索引，但是他扫描超过了1亿行。所以你现在知道了，当我们在讨论有没有使用索引的时候，其实我们关心的是扫描行数。

对于一个大表，不止要有索引，索引的过滤性还要足够好。

像刚才这个例子的age，它的过滤性就不够好，在设计表结构的时候，我们要让所有的过滤性足够好，也就是区分度足够高。

回表的代价

那么过滤性好了，是不是表示查询的扫描行数就一定少呢？

我们再来看一个例子：

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

t_people表上有一个索引是姓名和年龄的联合索引，那这个联合索引的过滤性应该不错，可以在联合索引上快速找到第1个姓名是张三，并且年龄是8的小朋友，当然这样的小朋友应该不多，因此向右扫描的行数很少，查询效率就很高。

但是查询的过滤性和索引的过滤性可不一定是一样的，如果现在你的需求是查出所有名字的第1个字是张，并且年龄是8岁的所有小朋友，你的语句会怎么写呢？

你的语句要怎么写？很显然你会这么写：select * from t_people where name like '张%' and age=8;

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

•  首先从联合索引上找到第1个年龄字段是张开头的记录，取出主键id，然后到主键索引树上，根据id取出整行的值；

•  判断年龄字段是否等于8，如果是就作为结果集的一行返回，如果不是就丢弃。

•  在联合索引上向右遍历，并重复做回表和判断的逻辑，直到碰到联合索引树上名字的第1个字不是张的记录为止。

我们把根据id到主键索引上查找整行数据这个动作，称为回表。你可以看到这个执行过程里面，最耗费时间的步骤就是回表，假设全国名字第1个字是张的人有8000万，那么这个过程就要回表8000万次，在定位第一行记录的时候，只能使用索引和联合索引的最左前缀，最称为最左前缀原则。

你可以看到这个执行过程，它的回表次数特别多，性能不够好，有没有优化的方法呢？

在MySQL5.6版本，引入了index condition pushdown的优化。我们来看看这个优化的执行流程：

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

•  在联合索引树上，向右遍历，并判断年龄字段后，根据需要做回表，直到碰到联合索引树上名字的第1个字不是张的记录为止；

这个过程跟上面的差别，是在遍历联合索引的过程中，将年龄等于8的条件下推到所有遍历的过程中，减少了回表的次数，假设全国名字第1个字是张的人里面，有100万个是8岁的小朋友，那么这个查询过程中在联合索引里要遍历8000万次，而回表只需要100万次。

虚拟列

可以看到这个优化的效果还是很不错的，但是这个优化还是没有绕开最左前缀原则的限制，因此在联合索引你还是要扫描8000万行，那有没有更进一步的优化方法呢？

我们可以考虑把名字的第一个字和age来做一个联合索引。这里可以使用MySQL5.7引入的虚拟列来实现。对应的修改表结构的SQL语句:

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

我们来看这个SQL语句的执行效果:

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;

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

有了这个新的联合索引，我们在找名字的第1个字是张，并且年龄为8的小朋友的时候，这个SQL语句就可以这么写：select * from t_people where name_first='张' and age=8。

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

“使用了索引查询还是慢的原因是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！