引言

本文通过一个简单的慢SQL优化案例，介绍下索引下推与覆盖索引相关知识点。

介绍

遇到一条慢SQL索引失效全表扫描，分析过程中首先需要定位导致索引失效的原因，最终发现是由于在需要回表的前提下当查询的数据量较大时，优化器会认为全表扫描的成本更低因此导致索引失效。

优化方法很简单，创建联合索引实现覆盖索引就可以，不过结合处理过程可以加深对于执行计划中 type 与 extra 列的理解。

分析

执行计划

查看执行计划发现 type=ALL，possible_keys 不为空 key 为空，表明索引失效全表扫描。

mysql> explain select id from board_chute where status=1 and create_time <= date_sub(now(), interval 24 hour)  G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: ALL
possible_keys: idx_create_time
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 407632
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

简化后的表结构如下所示，发现查询字段中 create_time 有索引，但是没有使用。

mysql> show create  table board_chute  G
*************************** 1. row ***************************
       Table: board_chute
Create Table: CREATE TABLE `board_chute` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `status` tinyint(1) DEFAULT '1' COMMENT '0无效,有效',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_ts` (`ts`)
) ENGINE=InnoDB AUTO_INCREMENT=8264771 DEFAULT CHARSET=utf8 COMMENT='xxx'
1 row in set (0.00 sec)

函数与索引

怀疑与索引判断时的函数有关，因此将时间函数替换为随机常量，发现使用到了索引。

mysql> explain select id from board_chute where status=1 and create_time <= '2022-11-08 00:03:53' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time
          key: idx_create_time
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 10.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.01 sec)

执行计划中显示 Using index condition 表明将使用索引下推。

查看执行用时发现查询结果为空，其中 status=1 表示最新数据，0 表示过期数据，可见只有当天的的数据有效，之前的数据都被逻辑删除。

mysql> select id from board_chute where status=1 and create_time <= '2022-11-08 00:03:53';
Empty set (0.00 sec)

mysql> select min(create_time) from board_chute where status=1;
+---------------------+
| min(create_time)    |
+---------------------+
| 2022-11-15 14:47:24 |
+---------------------+
1 row in set (0.10 sec)

经查看，全表数据 40w 左右，符合查询条件的数据 4000 行左右。

mysql> select count(*) from board_chute where status=1;
+----------+
| count(*) |
+----------+
|     4309 |
+----------+
1 row in set (0.16 sec)

mysql> select count(*) from board_chute;
+----------+
| count(*) |
+----------+
|   411626 |
+----------+
1 row in set (0.07 sec)

而如果将查询时间调整为当天，会发现索引依然失效。

mysql> explain select id from board_chute where status=1 and create_time <= '2022-11-15 00:03:53' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: ALL
possible_keys: idx_create_time
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 407632
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

因此索引失效的原因与 create_time 字段无关，很可能与 status 字段有关。

回表

如果将查询条件 status 删除，发现索引生效。

mysql> explain select id from board_chute where create_time <= date_sub(now(), interval 24 hour)  G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time
          key: idx_create_time
      key_len: 6
          ref: NULL
         rows: 203816
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

因此索引失效的原因是 idx_create_time 索引中没有查询条件中的 status 字段，因此需要回表。

当查询的数据量较小时（比如查询结果为空），可以用到 idx_create_time 索引，而当查询的数据量较大时，优化器会认为全表扫描的成本更低因此导致索引失效。

因此优化方法也很简单，根据查询字段创建联合索引即可。

创建联合索引

查询条件中有两个字段 create_time、status，查询列表中有一个字段 id，因此可以创建由 create_time、status 两个字段组成的联合索引，唯一的问题就是两个字段的顺序。

将 select c1,c2 from tb where c3=xx 中 c1、c2 称为查询列表，c3 称为查询条件。

如下所示，status 字段的区分度很低。

mysql> select status,count(*) from board_chute group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
|      0 |   407317 |
|      1 |     4309 |
+--------+----------+
2 rows in set (0.17 sec)

因此，如果仅针对该 SQL，也就是仅查询 status=1 的数据的话，建议联合索引的顺序是 (status,create_time)，否则建议联合索引的顺序是 (status,create_time)，需要结合业务场景进行判断。

最终选择创建联合索引 (status,create_time)，将区分度低的字段放在后面。

mysql> alter table board_chute add index idx_create_time_status(create_time,status);
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

重新查看执行计划，type=range，Using index 表明将使用覆盖索引，不需要回表。

mysql> explain select id from board_chute where status=1 and create_time <= date_sub(now(), interval 24 hour)  G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time,idx_create_time_status
          key: idx_create_time_status
      key_len: 8
          ref: NULL
         rows: 203816
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

下面分别介绍覆盖索引与索引下推的原理。

原理

覆盖索引

上文中新建的联合索引生效后执行计划中显示 extra=Using index，表明将使用覆盖索引。

mysql> explain select id from board_chute where status=1 and create_time <= date_sub(now(), interval 24 hour)  G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time,idx_create_time_status
          key: idx_create_time_status
      key_len: 8
          ref: NULL
         rows: 203816
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

那么，覆盖索引是什么概念呢？

当二级索引中包含（覆盖）要查询的所有字段时，这个索引称为覆盖索引。

否则就需要通过二级索引查出主键索引，再通过主键索引查询二级索引中没有的其他列的数据，这个过程叫做回表。因此在 InnoDB 表中使用二级索引进行查询时，需要两次索引查找，即两次 B+ 树查找。

这是由聚簇索引与二级索引的数据结构所决定的。

每个 InnoDB 表具有一个特殊的索引称为聚簇索引，表中行的物理顺序与索引的逻辑顺序相同。因此在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

InnoDB 表中在非主键的其他列上建的索引就是二级索引，二级索引的叶子节点中除了存储索引列的值，还存储这一行对应的主键值用于回表。

因此：

• InnoDB 会将主键字段放到索引定义字段后面，假设主键是 a，定义索引 b 时，实际上的索引元组是（b, a）；
• InnoDB 表中的查询操作中所有二级索引都需要引用主键索引，所以不建议使用过长的字段作为主键。主键长度越小，二级索引的叶子结点就越小，二级索引占用的空间越小。

这里强调存储引擎的原因是不同存储引擎中索引实现不同。

比如 InnoDB 与 MyISAM，虽然都是基于 B+ 树实现，但也有很大区别，简单对比下：

• InnoDB，聚簇索引，数据文件本身就是索引文件，二级索引的叶子节点中保存主键值，更适合更新；
• MyISAM，非聚簇索引，索引文件与数据文件相分离，二级索引的叶子节点中保存数据地址，更适合查询。

索引下推

上文中如下 SQL 的执行计划中显示 extra=Using index condition，表明使用到了索引下推特性。

mysql> explain select id from board_chute where status=1 and create_time <= '2022-11-08 00:03:53' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time
          key: idx_create_time
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 10.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.01 sec)

那么，索引下推（Using index condition，ICP）是什么概念呢？

ICP 是 MySQL 5.6 版本中引入的新特性，用于优化使用二级索引从表中检索行的实现。

没有 ICP 之前，条件过滤都在 server 层进行，因此存储引擎层需要遍历索引并回表，然后将数据发送给 server 层进行条件过滤。

有了 ICP 之后，条件过滤可以下推到存储引擎层进行，先由索引元组（index tuple）根据查询条件进行过滤，满足条件的前提下才回表，否则跳过，相当于延迟加载数据行。

因此，ICP 可以降低回表次数与 IO 次数，降低存储引擎层发送给 server 层的数据量，最终提高查询性能。

Using index condition (JSON property: using_index_condition)

Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.

假设表中有索引 INDEX (zipcode, lastname, firstname)，执行下面的 SQL，由于 lastname 字段使用模糊匹配，违背最左前缀法则，因此索引中保存的该字段无法限制扫描行数。

如果没有 ICP，存储引擎层需要遍历满足条件 zipcode=’95054′ 的全部记录，而如果有 ICP，存储引擎曾可以在回表前过滤掉 zipcode=’95054′ 但 lastname 不满足条件的记录。

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

需要注意的是对于 InnoDB 存储引擎，ICP 仅适用于二级索引。原因是 ICP 的目标是降低回表次数与 IO 次数，而对于 InnoDB 表中的聚簇索引，完整的行记录已经加载到了缓存，因此通过 ICP 无法降低 IO 次数。

For InnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.

ICP 默认开启，可以通过系统参数 optimizer_switch 控制开关。

如果将索引下推关闭，extra 发生变化，直接回表。

mysql> set session optimizer_switch = 'index_condition_pushdown=off';
Query OK, 0 rows affected (0.01 sec)

mysql> explain select id from board_chute where status=1 and create_time <= '2022-11-08 00:03:53' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time
          key: idx_create_time
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

下面在测试环境复现并分析该现象。

复现

准备数据

创建测试表，其中 create_time 字段已创建二级索引。

mysql> show create table t_index_func G
*************************** 1. row ***************************
       Table: t_index_func
Create Table: CREATE TABLE `t_index_func` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from t_index_func;
+----+---------------------+------+
| id | create_time         | name |
+----+---------------------+------+
|  1 | 2022-11-16 14:53:03 | a    |
|  2 | 2022-11-16 14:53:03 | b    |
|  3 | 2022-11-16 14:53:03 | c    |
+----+---------------------+------+
3 rows in set (0.00 sec)

回表

查询条件中仅包括 create_time，理论上可以用到索引。

但是由于执行 select *，需要回表，因此导致索引失效全表扫描。

mysql> explain select * from t_index_func where create_time>date_sub(now(), interval 24 hour) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_index_func
   partitions: NULL
         type: ALL
possible_keys: idx_create_time
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

因此，不建议使用 select *，而是仅将业务中需要的列放在查询列表中。

索引下推

众所周知，索引列上使用函数时将导致索引失效，那么如果与索引列相比较的常量上使用函数会导致索引失效吗？

将查询条件中的 > 号改为 <= 号，查询结果为空，索引生效，并使用到了索引下推，因此表明索引失效与和 create_time 进行比较的常量上的函数没有关系。

mysql> select * from t_index_func where create_time <= date_sub(now(), interval 24 hour);
Empty set (0.00 sec)

mysql> explain select * from t_index_func where create_time <= date_sub(now(), interval 24 hour) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_index_func
   partitions: NULL
         type: range
possible_keys: idx_create_time
          key: idx_create_time
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

索引函数

如下所示，索引列上使用函数时导致索引失效，无论比较大小时使用的是  > 号还是 <= 号。

mysql> explain select * from t_index_func where date_sub(create_time, interval 24 hour)>now() G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_index_func
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_index_func where date_sub(create_time, interval 24 hour)<=now() G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_index_func
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

因此，索引列上使用函数时导致索引失效的原因是索引本身有序，而使用函数后无法保证有序。

最后介绍下执行计划中评估查询语句性能时常用的两个字段，type 与 extra。

执行计划

准备数据

建表并初始化数据。

mysql> show create table t_assess_method G
*************************** 1. row ***************************
       Table: t_assess_method
Create Table: CREATE TABLE `t_assess_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`),
  UNIQUE KEY `uk_key2` (`key2`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t_assess_method limit 3;
+----+------+------+------+-----------+-----------+-----------+
| id | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 |
+----+------+------+------+-----------+-----------+-----------+
|  1 | test |    1 | test | test      | test      | test      |
|  2 | test |    2 | test | test      | test      | test      |
|  3 | test |    3 | test | test      | test      | test      |
+----+------+------+------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

type

type 列表示 MySQL 针对单表中数据行的访问方法。

下面是最重要的的访问方法，性能依次下降。

• ALL，全表扫描，从头到尾扫描整张表找到需要的行；

mysql> explain select * from t_assess_method G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9861
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

• index，全索引扫描，表明可以使用覆盖索引，但是需要扫描全部索引记录，根据查询条件无法形成合适的扫描区间来减少需要扫描的记录数量。主要优点是避免排序，主要缺点是需承担按索引顺序读取整张表的开销；

mysql> explain select key_part2 from t_assess_method where key_part3='abc' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_key_part
      key_len: 909
          ref: NULL
         rows: 9861
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

• range，范围索引扫描，有限制的索引扫描，不需要遍历全部索引。比如带有 BETWEEN、IN、OR、>、< 的查询；

mysql> explain select * from t_assess_method where key1>'a' and key1<'b' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: range
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

• ref，索引访问，返回所有匹配某个单个值的行，可能找到多个符合条件的行，使用非唯一索引或唯一索引的非唯一前缀时发生，索引需要与某个参考值进行比较。比如普通二级索引的等值匹配；

mysql> explain select * from t_assess_method where key1='a' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

• eq_ref，最多只返回一条符合条件的行，使用主键或非空唯一二级索引时发生，索引需要与某个参考值进行比较。比如唯一二级索引的等值判断，不过单表中没有复现，在多表关联查询时如果被驱动表是通过主键或非空唯一键进行等值匹配的方式进行访问时可以复现；

mysql> create table t_assess_method_join like t_assess_method;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t_assess_method_join select * from t_assess_method;
Query OK, 10000 rows affected (0.25 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql> explain select * from t_assess_method  as t1 inner join t_assess_method_join as t2 on t1.id=t2.id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9861
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_zk.t1.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

• const, system，表明 MySQL 能对查询的某部分进行优化并将其转换成一个常量。比如根据主键或非空唯一二级索引与常数进行等值匹配；

mysql> explain select * from t_assess_method where key2=100 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: const
possible_keys: uk_key2
          key: uk_key2
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

• NULL，表明 MySQL 能在优化阶段分解查询语句，在执行阶段不需要再访问表或索引。比如从索引列中选择最小值或最大值时可以通过单独查找索引的第一条或最后一条记录来完成，不需要在执行时访问表。

mysql> explain select max(id) from t_assess_method G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)

备注：

• 二级索引对应的主键值无序；
• 多表关联查询的结果可能会有差异，待测试。

extra

extra 列中最重要的取值如下。

• Using index，表明将使用覆盖索引，以避免访问表。注意覆盖索引和 index 访问类型不同；

mysql> explain select key1 from t_assess_method where key1='a' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

• Using where，表明查询条件需要在 server 层判断，注意其中 key1>’a’；

mysql> explain select * from t_assess_method where key1>'a' and key1 like '%a' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: ALL
possible_keys: idx_key1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9861
     filtered: 49.99
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

• Using index condition，表明将使用索引下推，查询条件在存储引擎层判断，注意其中 key1>’z’；

mysql> explain select * from t_assess_method where key1>'z' and key1 like '%a' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: range
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

• Using filesort，表明 MySQL 会对结果进行外部排序，无法使用到索引的有序性，只能在内存中（记录较少时）或磁盘中（记录较多时）进行排序。MySQL 中将这种在内存中或磁盘中进行排序的方式称为文件排序（filesort），但是从执行计划中无法看出是哪种文件排序。

mysql> explain select * from t_assess_method order by key_part2 limit 3 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9861
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

• Using temporary，表明对查询结果排序时将使用到临时表，比如 group by、order by、distinct、union 中如果无法有效利用索引，MySQL 就有可能需要通过建立内部临时表来执行查询，如果内部临时表超过阈值，将自动转换成外部临时表；

mysql> explain select * from t_assess_method group by key_part2 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_assess_method
   partitions: NULL
         type: ALL
possible_keys: idx_key_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9861
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

注意查询语句中并没有 order by 子句，但是 extra 列中还包括 Using filesort，原因是 MySQL 中包含 group by 子句的查询中默认添加 order by 子句。因此下面两条查询语句等价。

select * from t group by key_part2;
select * from t group by key_part2 order by key_part2;

小技巧

索引失效

在优化慢 SQL 时，首先需要定位导致索引失效或索引异常的原因。

比如可以通过调整查询条件、查询列表、查询值等方式进行测试对比。

执行计划

执行计划中评估查询语句性能时常用的两个字段，type 与 extra。

下面简单对比下常见的 type 与 extra。

type，性能依次下降。

extra

结论

本案例中回表与数据量较大的情况下导致索引失效全表扫描，通过创建联合索引实现索引下推的方式进行优化。

覆盖索引指二级索引中包含（覆盖）要查询的所有字段时，可以将两次 B+ 树查找降低为一次 B+ 树查找。

索引下推用于优化二级索引的条件过滤，将条件过滤下推到存储引擎层进行，而不是在回表后由 server 层判断。ICP 可以降低回表次数与 IO 次数，降低存储引擎层发送给 server 层的数据量，最终提高查询性能。

慢 SQL 优化的主要思路通过限制扫描区间降低扫描行数与 IO 次数。

执行计划中评估查询语句性能时常用的两个字段是 type 与 extra。

其中 type 表示 MySQL 针对单表中数据行的访问方法，同一个查询语句可以使用多种不同的访问方法来执行，尽管查询结果相同，但是执行用时可能有差异。

待办

• 索引下推
• index_merge

参考教程

• 《高性能 MySQL》
• 《MySQL 是怎样运行的》
• 《MySQL 5.7 Reference Manual》 Index Condition Pushdown Optimization