关于mysql:为什么-SQL-语句使用了索引但却还是慢查询

3次阅读

共计 4185 个字符，预计需要花费 11 分钟才能阅读完成。

聊一聊索引和慢查问，常常遇到的一个问题：一个 SQL 语句应用了索引，为什么还是会记录到慢查问日志之中？
为了阐明，创立一个表 t，该表 3 个字段，一个主键索引，一个一般索引

CREATE TABLE `t` (`id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

insert into t values (1, 1, 1), (2, 2, 2);

首先 MySQL 判断一个语句是不是慢查问语句，用的是语句执行工夫，它把语句执行工夫跟 long_query_time 这个零碎参数做比拟，如果语句执行工夫比 long_query_time 还大，就会把这个语句记录到慢查问日志里。

long_query_time 这个参数它的默认值是 10s，在生产上咱们不会设置这么大的值，个别会设置 1s，对于一些对提早比拟敏感的业务，会设置一个比 1 还小的值，而对于语句是否应用了索引，它的意思是语句执行过程中有没有用到表的索引。

具体到表象中是 explain 一个语句的时候，输入后果外面 key 的值不是 NULL，图 1 就是执行 explain select from t; 的后果。能够看到 key 这一列显示的是 NULL。图 2 就是执行 explain select from t where id = 2 的后果，这里 key 显示的是 PRIMARY，就是咱们常说的应用了主键索引。图 3 就是执行 select a from t 的后果，这里 key 这一列显示的是 a，示意应用了 a 这个索引。能够看到图 2 和图 3 的后果里 key 的字段都不是 NULL，而实际上图 3 是扫描了整个索引树 a。

这个示例的表外面只有两行，那如果有 100 万行呢，有 100 万行的时候图 2 的语句还是能够执行很快，然而图 3 就必定慢了，如果是更极其的状况，比方如果这个数据库上 CPU 压力十分地高，那可能第二个语句的执行工夫也会超过 long_query_time，会记录到慢查问日志外面，所以如果简略地答复这个问题，是否应用索引只是示意了一个 SQL 语句的执行过程，而是否记录到慢查问日志中是由它的执行工夫决定的，而这个执行工夫可能会受各种内部因素的影响，也就是说 是否应用索引和是否记录慢查问之间没有必然的分割。

如果咱们再深层次的看这个问题其实它还潜藏着一个问题须要廓清就是，什么叫做应用了索引。咱们晓得 InnoDB 是索引组织表，所有的数据都是存储在索引树下面的，比方表 t，这个表它蕴含了两个索引，一个主键索引一个一般索引 a，在 InnoDB 里数据是放在主键索引里的。咱们来看一下这个表的数据示意图，能够看到数据都放在主键索引上，如果从逻辑上说，所有的在 InnoDB 表上的查问，都至多用了一个索引，当初有一个问题：如果执行 explain select * from t where id > 0; 这个语句有用上索引吗？

当初咱们来看看这个语句的 explain 的后果，在输入后果里，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 上建了索引当前，这个语句还是执行慢，因为满足这个条件的数据有超过 1 亿行。咱们来看看建设了这个索引当前这个表的组织结构图，这个语句的执行流程是这样的。从索引 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; 在 MySQL5.5 和之前的版本中，这个语句的执行流程是这样的。首先从联结索引树上找到第一个姓名字段是张结尾的记录，取出主键 ID，而后到主键索引上，依据 ID 取出整行的值，判断年龄字段是否等于 8 如果是就作为后果集的一行返回，如果不是就抛弃，咱们把依据 ID 到主键索引上查找整行数据这个动作称为回表，在联结索引上向右遍历，并反复做回表和判断的逻辑直到碰到联结索引树上名字第一个字不是张的记录为止。你能够看到这个执行过程外面最消耗工夫的步骤就是回表，假如全国名字第一个字是张的人有 8000 万，那么这个过程就要回表 8000 万次，在定位第一行记录的时候，只能应用索引和联结索引的最左前缀，称为最左前缀准则。那你能够看到这个执行过程它的回表次数特地多，性能不够好，那有没有优化的办法呢？有的在 MySQL5.6 版本引入了 index condition pushdown 的优化，咱们来看看这个优化的执行流程。

首先从联结索引树上找到第一个姓名字段是张结尾的记录，判断这个索引记录外面年龄的值是不是 8，如果是就回表，取出整行数据作为后果集的一部分返回，如果不是就抛弃。在联结索引树上向右遍历，并判断年龄字段后依据须要做回表，直到碰到联结索引树上名字的第一个字不是张的记录为止。这个过程跟下面过程的差异是在遍历联结索引的过程中，将年龄等于 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);

CREATE TABLE `t_people` (`id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  `name_first` varchar(2) GENERATED ALWAYS AS (left(`name`, 1)) VIRTUAL,
  KEY `name_first` (`name_first`, `age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

上图是这个 DDL 语句的执行成果，首先它在 t_people 上创立一个字段叫 name_first 虚构列，而后给 name_first 和 age 上创立一个联结索引，并且让这个虚构列的值，总是等于 name 字段的前两个字节，虚构列在插入数据的时候，不能指定值，在更新的时候也不能指定批改，它的值会依据定义主动生成，在 name 字段批改的时候，也会主动批改，有了这个新的联结索引，咱们再找名字第一个字是张并且年龄是 8 的小朋友的时候，这个 SQL 语句就能够这么写：select * from t_people where name_fist = ‘ 张 ’ and age = 8; 这样这个语句的执行过程，就只须要扫描联结索引的 100 万行并回表 100 万次。这个优化的实质是创立了一个更紧凑的索引来减速了查问的过程。

明天介绍了索引的根本构造和一些查问优化的基本思路，当初咱们晓得了：
1、应用索引和慢查问没有必然联系，应用索引的 SQL 也有可能是慢查问语句；
2、查看一个查问语句的执行效率最终要看的是扫描行数，咱们查问优化的过程往往就是缩小扫描行数的过程；
3、应用虚构列和联结索引来晋升简单查问的执行效率。

正文完