mysql时间范围查询优化

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

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

本文主要介绍MySQL范围查询与优化(MySQL8.0)。范围查询是我们经常使用的一类查询，是指WHERE子句使用=、、>、>=、=、 ‘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_column1

key_column2

key_column3

NULL

1

‘abc‘

NULL

1

‘xyz‘

NULL

2

‘foo‘

1

1

‘abc‘

1

1

‘xyz‘

1

2

‘abc‘

2

1

‘aaa‘

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

(1, -X, -X) ，=，，=，=，但不再考虑其他条件：

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 < 2Skip Scan范围查询

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

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));ｉｎｓｅｒｔ INTO t1 VALUES(1,1), (1,2), (1,3), (1,4), (1,5),(2,1), (2,2), (2,3), (2,4), (2,5);ｉｎｓｅｒｔ INTO t1 ｓｅｌｅｃｔ f1, f2 + 5 FROM t1;ｉｎｓｅｒｔ INTO t1 ｓｅｌｅｃｔ f1, f2 + 10 FROM t1;ｉｎｓｅｒｔ INTO t1 ｓｅｌｅｃｔ f1, f2 + 20 FROM t1;ｉｎｓｅｒｔ INTO t1 ｓｅｌｅｃｔ f1, f2 + 40 FROM t1;ANALYZE TABLE t1;

EXPLAIN ｓｅｌｅｃｔ 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 ｓｅｌｅｃｔ * 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优化器能够将范围扫描应用于以下形式的查询：

ｓｅｌｅｃｔ ... FROM t1 WHERE ( col_1, col_2 ) IN (( ‘a‘, ‘b‘ ), ( ‘c‘, ‘d‘ ));

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

ｓｅｌｅｃｔ ... FROM t1 WHERE ( col_1 = ‘a‘ AND col_2 = ‘b‘ )OR ( col_1 = ‘c‘ AND col_2 = ‘d‘ );

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

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