生产环境中，随着数据库表数据量的不断增加或者表结构的变化，经常会导致慢查询的问题，慢查询即SELECT……WHERE……形式的查询语句响应速度慢。优化慢查询是一个很重要的工作，无论是满足应用程序的快速响应还是保证数据库系统的稳定安全地运行都有重要意义。

优化慢查询，首先要检查的是是否可以添加索引，对WHERE子句中使用的列设置索引，以加快计算、筛选和最终检索结果的速度。索引对于使用联接和外键等功能引用不同表的查询尤其重要，为了避免浪费磁盘空间，还要考虑索引的合理性，比如使用复合索引代替多个单键索引等。

本文主要介绍MySQL范围查询与优化(MySQL8.0)。范围查询是我们经常使用的一类查询，是指WHERE子句使用=、<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE或IN()等运算符将列与常量进行比较作为查询条件。比如下面这些查询：

SELECT * FROM tbl_name WHERE column = 10;
SELECT * FROM tbl_name WHERE column BETWEEN 10 and 20;
SELECT * FROM tbl_name WHERE column IN (10,20,30);
SELECT * FROM tbl_name WHERE column1 = 10 AND column2 IN (10,20,30);

创建一个用户表：

CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_code` varchar(10) NOT NULL COMMENT '编号',
  `user_name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint UNSIGN NOT NULL COMMENT '年龄',
  `score` tinyint UNSIGN NOT NULL COMMENT '年龄',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

存入以下数据：

EXPLAIN简介

为了分析一个SQL语句是不是慢查询，可以使用EXPLAIN语句来确定SELECT是否使用了索引，EXPLAIN是MySQL自带的性能分析工具，当EXPLAIN与SQL语句一起使用时，将会分析并显示MySQL优化器对该SQL语句的执行计划信息，通过执行计划信息，我们可以了解MySQL将如何处理这条SQL查询。例如：

//执行
EXPLAIN SELECT * FROM sys_user WHERE id = 1;
//输出
+----+-------------+-----------+------------+--------+---------------+--------+---------+-------+--------+--------------------+
| id | select_type | table     | partitions | type   | possible_keys | key    | key_len | ref   | rows   | filtered | Extra   |
+----+-------------+-----------+------------+--------+---------------+--------+---------+-------+--------+--------------------+
|  1 | SIMPLE      | sys_user  | NULL       | const  | PRIMARY       | PRIMARY| 8       | const | 1      | 100.00   | NULL    |
+----+-------------+-----------+------------+--------+---------------+--------+---------+-------+--------+--------------------+

EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。

EXPLAIN输出的type列描述了如何联接表，是数据访问性能的一个重要指标，从最佳性能到最差性能分别为：

• system：使用 MyISAM存储引擎的表，且表中只有一行数据，这是const联接类型的一种特殊情况。
• const：表中最多有一行匹配，该行在查询开始时只读取一次，查询非常快。通常用于主键或唯一索引作为查询条件的情况。
• eq_ref：使用PRIMARY KEY或UNIQUE NOT NULL索引关联查询。eq_ref是除了system和const类型之外最好的联接类型。
• ref：使用非主键或唯一索引关联查询，即WHERE条件匹配了多行。
• fulltext：使用FULLTEXT索引执行关联查询。
• ref_or_null：这种联接类型类似于ref，但MySQL对包含NULL值的行进行了额外的搜索。这种联接类型优化最常用于解析子查询。
• index_merge：使用了索引合并优化，查询使用了两个或以上的索引。在这种情况下，explain执行结果输出行中的key列包含所使用索引的列表，key_len包含所使用的索引的最长键部分的列表。
• unique_subquery：类似eq_ref，查询条件中使用了IN子查询。类似这样的格式：...value IN (SELECT primary_key FROM one_table WHERE ...)。
• index_subquery：类似于unique_subquery，IN子查询中使用了非唯一索引。类似下面格式：...value IN (SELECT key_column FROM one_table WHERE ...)。
• range：范围查询，检索给定范围内的行，使用索引选择行。范围查询是指使用了=、<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE或IN()运算符将索引列与常量进行比较的查询。
• index：index类型表示MySQL会对整个索引进行扫描，通常是查询条件中只使用了索引中的部分列。这种情况有两种方式：只扫描索引树(比ALL性能好，因为索引的大小通常小于表数据。)和使用从索引中读取数据来执行全表扫描。
• ALL：全表扫描，性能最差的查询，通常是需要建立索引进行优化。

可以看到：范围查询(type=range)性能是要优于全索引扫描的(type=index)。

单键索引的范围查询

单键索引（索引只包括一列）时，范围查询条件有如下特点：

• 对于BTREE和HASH索引，当使用=、<=>、IN()、is NULL或is NOT NULL运算符时，将键(索引列)与常数值进行比较即满足范围查询条件。
• 对于BTREE索引，当使用>、<、>=、<=、BETWEEN、！=时，将键(索引列)与常数值进行比较，或者<>、LIKE比较(右匹配，不以通配符开头的常量字符串)即满足范围查询条件。
• 对于所有索引类型，多个范围条件与OR或AND组合形成一个范围条件(下文有示例)。

以下是WHERE子句中具有范围条件的查询的一些示例(key_col表示建立索引的列)：

SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';

MySQL优化器会从WHERE子句中为每个可能的索引提取范围查询条件。在提取过程中，不能用于构建范围查询条件的条件被丢弃，产生重叠范围的条件被合并，产生空范围的条件则被移除。例如处理以下语句，其中key1是索引列，nonkey不是索引列：

//key1是索引列，nonkey不是索引列
SELECT * FROM t1 WHERE
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z');

(1) 删除nonkey=4和key1 LIKE'%b'，因为它们不能用于范围扫描。删除它们的方法是将它们替换为TRUE，这样不会丢失任何匹配的行：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

(2) 始终为true或false的条件处理：

(key1 LIKE 'abcde%' OR TRUE) 始终为true
(key1 < 'uux' AND key1 > 'z') 始终为false

替换这些常量条件后生成：

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

(3) 删除不必要的TRUE和FALSE常量得到：

(key1 < 'abc') OR (key1 < 'bar')

(4) 将重叠条件组合为一个适用于范围查询的最终条件：

(key1 < 'bar')

一般来说，用于范围查询的条件比WHERE子句的限制性更小，MySQL筛选出范围查询条件不满足完整WHERE子句的条件。

范围条件提取算法可以处理任意深度的嵌套AND/OR结构，其输出不取决于条件在WHERE子句中的出现顺序。

复合索引的范围查询

复合索引（索引包括多列）上的范围查询条件是单键索引范围查询条件的扩展。复合索引上的范围条件拆分为一个或多个key_column条件表示的范围区间，key_column条件组涉及复合索引的所有列，并使用复合索引定义中的列排序。

例如，对于复合索引idx_key(key_column1, key_column2, key_column3)，表中有这样一组数据：

当查询条件为：key_column = 1，将会定义这样的范围区间：

(1, -X, -X) <= (key_column1, key_column2, key_column3) < (1, +X, +X)

该区间包括数据集中的第4—6行，且适用于范围查询。相比之下，对于条件 key_column3 = 'abc'将不会定义范围区间，也不会使用范围查询检索数据。

对于BTREE索引，WHERE子句中用AND连接的条件将建立可能的范围区间，这里所说的“条件”是指复合索引中的key_column使用=，<=>，IS NULL，>，<，>=，<=，！=，<>，between，like(右匹配)与常数值进行比较。

按照WHERE子句中条件的顺序，如果靠前的key_column比较运算符为=、<=>或is NULL，MySQL优化器会继续尝试为其余条件确定范围区间。如果运算符为>，<，>=，<=，！=，<>，BETWEEN或LIKE，MySQL优化器将不考虑其余条件。

例如，对于以下表达式，MySQL优化器在第一次比较中使用=，它还使用了第二次比较中的>=，但不再考虑其他条件：

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

只确定单个范围区间为：

('foo',10,-X) < (key_column1,key_column2,key_column3) < ('foo',+X,+X)

这样创建的范围区间将比原始条件包含更多的行，比如区间中的值('o'，11，0)。

对于OR连接的条件，将确定两个范围区间且求并集，例如以下条件(假设在key_column1、key_column2两列上建立复合索引)：

(key_column1 = 1 AND key_column2 < 2) OR (key_column1 > 5)

使用到的范围区间为：

• (1,-X) < (key_column1,key_column2) < (1,2)
• (5,-X) < (key_column1,key_column2)

求并集，最终得到如下条件：

• key_column1 >= 1 AND key_column2 < 2

Skip Scan范围查询

Skip Scan是MySQL8.0的新特性，是一种范围扫描数据的方法。假设有以下情形：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
    (1,1), (1,2), (1,3), (1,4), (1,5),
    (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40; 输出如下：

+----+-------------+-----------+------+---------------+--------+---------+------+--------+---------------------------------------+
| id | select_type | table     | type | possible_keys | key    | key_len | ref  | rows   | Extra                                 |
+----+-------------+-----------+------+---------------+--------+---------+------+--------+---------------------------------------+
|  1 | SIMPLE      | t1        | range| PRIMARY       | PRIMARY| 8       | NULL | 53     | Using where; Using index for skip scan|
+----+-------------+-----------+------+---------------+--------+---------+------+--------+---------------------------------------+

结果显示该查询使用了范围扫描，注意EXPLAIN的Extra列中的Using index for skip scan。

通常情况下，要执行此查询，MySQL可以选择索引扫描来获取所有行(索引包括要查询的所有列)，然后应用WHERE子句中的f2>40条件来生成最终结果集。而无法使用范围扫描，因为第一个索引列f1上没有条件。

但是，从MySQL 8.0.13开始，优化器可以执行多个范围扫描，f1的每个值一个，使用名为Skip Scan的方法，该方法类似于松散索引扫描：

• 在第一个索引部分f1(索引前缀)的不同值之间跳过。
• 对剩余索引部分上f2>40条件下的每个不同前缀值执行子范围扫描。

对于上面显示的数据集，算法的操作方式如下：

• 获取第一个关键部分的第一个不同值(f1=1)。
• 根据第一个和第二个关键部分构建范围(f1=1且f2>40)。
• 执行范围扫描。
• 获取第一个关键部分的下一个不同值(f1=2)。
• 根据第一个和第二个关键部分构建范围(f1=2且f2>40)。
• 执行范围扫描。

使用Skip Scan可以减少访问的行数，因为对于每个构建的范围，MySQL跳过了那些不符合条件的行。它适用于以下情况：

• 表T具有至少一个形式([A_1，…，A_k，]B_1，…，B_m，C[，D_1，……，D_n])构成的复合索引。索引中的A和D可以为空，B和C必须是非空的。
• 查询仅引用一个表。
• 查询不使用GROUP BY或DISTINCT。
• 仅查询索引中的列。
• 索引的第一部分(A_1, ..., A_k)必须等于一个常量，包括IN()操作。
• 查询必须是个连词查询。即多个OR条件使用AND连接： (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...。
• C上必须有一个范围条件。
• 可以在D列上具有条件，但是D列上的条件必须与C列上的范围条件在一起。

多值等式比较的范围查询

多值等式比较的范围查询是指这样的条件，其中 col_name 是索引列：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

col_name等于几个值中的任何一个，这些比较是相等范围比较(其中“范围”是单个值)。MySQL优化器预估符合条件的行，有两种情形：

(1) 如果col_name上是唯一索引，则每个值范围的行估计值为1，因为最多一行可以匹配。例如，对于unique索引idx_age(age)：

执行：explain select * from sys_user where age in(18,19,20);
输出：
+----+-------------+-----------+------------+--------+---------------+--------+---------+-------+--------+-------------------------------------+
| id | select_type | table     | partitions | type   | possible_keys | key    | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-----------+------------+--------+---------------+--------+---------+-------+--------+-------------------------------------+
|  1 | SIMPLE      | sys_user  | NULL       | range  | idx_age       | idx_age| 1       | NULL  | 3      | 100.00   | Using index condition    |
+----+-------------+-----------+------------+--------+---------------+--------+---------+-------+--------+-------------------------------------+

可以看到，explain输出的rows列值为3，即是in运算符参数个数。

(2) 如果col_name上不是唯一索引，MySQL优化器可以通过索引潜水(index dive)或索引统计(index statistics)来估算结果行数。

索引潜水是一种准确预估结果行数的方法，例如：

表达式age IN(18, 19, 20)有三个相等范围，MySQL优化器对每个范围进行两次潜水以生成行数估计值。索引潜水得到的预估值是比较准确的。

但随着IN表达式中比较值数量的增加，MySQL优化器生成行估计所需的时间会变长，比较值的数量超过临界值后，会使用索引统计的方法估算结果行数。索引统计估算的行数误差较大，其特点是对大值列表进行更快的行估计。

这个临界值就是系统变量eq_range_index_dive_limit，可以通过配置该变量使得MySQL优化器从一种行估算策略切换到另一种行估算策略。MySQL8中，该变量的默认值为200。可以通过以下命令查看：

show variables like 'eq_range_index_dive_limit';

• 要允许使用索引潜水来比较N个值，可将eq_range_index_dive_limit设置为N+1。
• 要禁用索引统计并始终使用索引潜水，可将eq_range_index_dive_limit设置为0。

当我们SQL中的IN运算符比较值数量较大时，需要避免索引潜水的方式，而使用索引统计的方式。在MySQL8.0以前，只能通过eq_range_index_dive_limit系统变量控制。在MySQL 8.0中，对于满足以下条件的查询，也可以跳过索引潜水：

• 查询针对的是单个表，而不是多个表上的联接。
• 存在单键索引FORCE index索引提示。(如果强制使用索引，那么执行索引潜水的额外开销无意义)
• 该索引不是唯一索引，不是FULLTEXT索引。
• 不存在子查询。
• 不存在DISTINCT、GROUP BY或ORDER BY子句。

行构造函数表达式的范围查询

MySQL优化器能够将范围扫描应用于以下形式的查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，要使用范围扫描，必须将查询写成：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

行构造器范围扫描的查询必须满足以下条件：

• 使用IN()运算符，而不使用NOT IN()。
• 在IN()运算符的左侧，行构造函数只包含列引用。
• 在IN()运算符的右侧，行构造函数只包含用于比较的常量值。
• 在IN()运算符的右侧，有多个行构造函数。