【MySQL精通之路】SQL优化(1)-查询优化(2)-范围查询优化

发布于：2024-05-24 ⋅ 阅读:(119) ⋅ 点赞:(0)

主博客：

【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客

上一篇：

【MySQL精通之路】SQL优化(1)-查询优化(1)-WHERE子句-CSDN博客

下一篇：

【MySQL精通之路】SQL优化(1)-查询优化(3)-索引合并-CSDN博客

目录

1.单列索引的范围访问方法

2.联合索引的范围访问方法

3.多值比较的等式区间优化

4.跳过扫描范围访问方法

5.行构造函数表达式的范围优化

6.限制内存用于范围优化

范围查询优化使用单个索引来检索包含在一个或多个索引值间隔内的表行的子集。

它可以用于单个部分索引（Single-Part），也可以用于多个部分索引（multiple-part）。

以下部分描述优化器使用范围查询的条件。

1.单列索引的范围访问方法

对于单列索引，索引值区间可以方便地由WHERE子句中的相应条件表示，表示为范围条件而不是“区间”

单列索引的范围条件定义如下：

对于BTREE和HASH索引，当使用=、<=>、IN（）、IS NULL或IS NOT NULL运算符时，将键部分与常数值进行比较是一个范围条件。

此外，对于BTREE索引，当使用>、<、>=、<=、BETWEEN、！=时，将关键部分与常数值进行比较是一个范围条件，或者＜＞运算符，或者LIKE比较（如果LIKE的参数是不以通配符开头的常量字符串）。

对于所有索引类型，多个范围条件与OR或AND组合形成一个范围条件。

前述描述中的“常数值”是指以下其中一种：

查询字符串中的常量

来自同一联接的常量表或系统表的列

不相关子查询的结果

完全由前面类型的子表达式组成的任何表达式

以下是WHERE子句中具有范围条件的查询的一些示例：

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没有索引：

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');

key1的查询过程如下：

1.从原始WHERE子句开始：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

2.删除nonkey=4和key1 LIKE'%b'，因为它们不能用于范围扫描。删除它们的正确方法是将它们替换为TRUE，这样我们在进行范围扫描时就不会错过任何匹配的行。用TRUE替换它们会产生以下结果：

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

3.始终为true或false的折叠条件：

(key1 LIKE 'abcde%' OR TRUE) is always true

(key1 < 'uux' AND key1 > 'z') is always false

用常数代替这些条件会产生：

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

删除不必要的TRUE和FALSE常量会产生：

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

4.将重叠间隔组合为一个产生将用于范围扫描的最终条件：

(key1 < 'bar')

通常（如前面的示例所示），用于范围扫描的条件比WHERE子句的限制性更小。MySQL执行额外的检查，以筛选出满足范围条件但不满足完整WHERE子句的行。

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

MySQL不支持为空间索引的范围访问方法合并多个范围。

为了克服这一限制，可以将UNION与相同的SELECT语句一起使用，只是将每个空间语句放在不同的SELECT中。

2.联合索引的范围访问方法

联合索引上的范围查询是单个部分索引范围查询的扩展。多部分索引上的范围条件限制索引行位于一个或多个key元组间隔内。key元组间隔是在一组key元组上定义的，使用索引中的排序。

例如，考虑定义为key1（key_part1、key_part2、key_part 3）的多部分索引，以及按key顺序列出的以下key元组集：

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件key_part1=1定义了此间隔：

（1，-inf，-inf）<=（key_part1，key_part2，key_part 3）<（1，+inf，+inf）

该间隔涵盖前一数据集中的第4、第5和第6个元组，可由范围访问方法使用。

相比之下，条件key_part3=“abc”没有定义单个间隔，范围访问方法不能使用它。

以下描述更详细地说明了范围条件如何适用于多个零件索引。

对于HASH索引，可以使用包含相同值的每个间隔。这意味着只能在以下形式的条件下产生间隔：

    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;

这里，const1、const2、…是常量，cmp是=、<=>或is NULL比较运算符之一，条件涵盖所有索引部分。（也就是说，有N个条件，N部分索引的每个部分一个。）例如，以下是三部分HASH索引的范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

有关被视为常量的定义，请参见单部分索引的范围访问方法。

对于BTREE索引，区间可能适用于与AND组合的条件，其中每个条件使用=，<=>，IS NULL，>，<，>=，<=，！=，<>将键部分与常数值进行比较，BETWEEN或LIKE“pattern”（其中“pattern“不以通配符开头）。只要可以确定包含所有符合条件的行的单个关键字元组，就可以使用区间（如果使用了<>或！=，则可以使用两个区间）。

只要比较运算符为=、<=>或is NULL，优化器就会尝试使用其他关键部分来确定间隔。如果运算符为>，<，>=，<=，！=，<>，BETWEEN或LIKE，优化器使用它，但不再考虑其他关键部分。对于以下表达式，优化器在第一次比较中使用=。它还使用了第二次比较中的>=，但没有考虑其他关键部分，也没有将第三次比较用于区间构造：

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

单个间隔为：

('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)

创建的间隔可能包含比初始条件更多的行。例如，前面的间隔包括不满足原始条件的值（'o'，11，0）。

如果覆盖区间内包含的行集的条件与OR组合，则它们形成覆盖区间并集内包含的一组行的条件。如果条件与AND组合，则它们形成一个条件，覆盖包含在它们的区间的交集内的一组行。例如，对于由两部分组成的索引上的此条件：

（key_part1=1 AND key_part2<2）OR（key_part 1>5）

间隔为：

(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)

在本例中，第一行上的间隔使用一个关键部分作为左边界，使用两个关键部分用于右边界。第二行的间隔仅使用一个关键部分。EXPLAIN输出中的key_len列指示所使用的密钥前缀的最大长度。

在某些情况下，key_len可能表示使用了关键部件，但这可能不是您所期望的。假设key_part1和key_part2可以为NULL。然后，key_len列显示以下条件下的两个关键零件长度：

key_part1 >= 1 AND key_part2 < 2

但是，事实上，条件转换为：

key_part1 >= 1 AND key_part2 IS NOT NULL

有关如何执行优化以组合或消除单个部分索引上范围条件的间隔的描述，请参阅单个部分索引的范围访问方法。对多个零件索引的范围条件执行类似的步骤。


3.多值比较的等式区间优化

考虑以下表达式，其中col_name是一个索引列：

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

如果col_name等于几个值中的任何一个，则每个表达式都为true。这些比较是相等范围比较（其中“范围”是单个值）。优化器估计读取符合条件的行以进行相等范围比较的成本，如下所示：

如果col_name上有一个唯一的索引，则每个范围的行估计值为1，因为最多一行可以具有给定的值。

否则，col_name上的任何索引都是非唯一的，优化器可以通过深入索引或索引统计信息来估计每个范围的行数。

使用索引俯冲，优化器在范围的每一端进行俯冲，并使用该范围中的行数作为估计值。例如，表达式col_name IN（10，20，30）有三个相等范围，优化器对每个范围进行两次潜水以生成行估计。每对潜水都会产生具有给定值的行数的估计值。

索引潜水提供了准确的行估计，但随着表达式中比较值数量的增加，优化器生成行估计所需的时间会更长。索引统计信息的使用不如索引潜水准确，但允许对大值列表进行更快的行估计。

eq_range_index_dive_limit系统变量使您能够配置优化器从一种行估计策略切换到另一种的值的数量。要允许使用索引潜水来比较多达N个相等范围，请将eq_range_index_dive_limit设置为N+1。要禁用统计信息并始终使用索引潜水（无论N），请将eq_range_index_dive_limit设置为0。

要更新表索引统计信息以获得最佳估计值，请使用ANALYZE TABLE.

在MySQL 8.0之前，除了使用eq_range_index_dive_limit系统变量之外，没有任何方法可以跳过使用索引潜水来估计索引的有用性。在MySQL 8.0中，对于满足所有这些条件的查询，可以跳过索引俯冲：

该查询针对的是单个表，而不是多个表上的联接。

存在单个索引FORCE index索引提示。其思想是，如果强制使用索引，那么执行深入索引的额外开销将不会带来任何好处。

该索引是非唯一的，不是FULLTEXT索引。

不存在子查询。

不存在DISTINCT、GROUP BY或ORDER BY子句。

对于EXPLAIN For CONNECTION，如果跳过索引潜水，输出将更改如下：

对于传统输出，行和过滤后的值为NULL。

对于JSON输出，rows_exempted_per_scan和rows_produced_per_join不会出现，skip_index_dive_due_to_force为true，并且成本计算不准确。

如果没有FOR CONNECTION，EXPLAIN输出在跳过索引转换时不会更改。

在执行跳过索引转换的查询后，信息架构OPTIMIZER_TRACE表中的相应行包含skipped_due_to_force_index的index_dives_for_range_access值。

4.跳过扫描范围访问方法

考虑以下场景：

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;

要执行此查询，MySQL可以选择索引扫描来获取所有行（索引包括要选择的所有列），然后应用WHERE子句中的f2>40条件来生成最终结果集。

范围扫描比全索引扫描更有效，但在这种情况下不能使用，因为在第一个索引列f1上没有条件。

但是，从MySQL 8.0.13开始，优化器可以使用一种称为Skip Scan的方法执行多个范围扫描，每个值为f1，该方法类似于松散索引扫描（请参阅“GROUP BY优化”）：

1.在第一个索引部分f1（索引前缀）的不同值之间跳过。

2.对剩余索引部分上f2>40条件下的每个不同前缀值执行子范围扫描。

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

4.获取第一个关键部分的第一个不同值（f1=1）。

5.根据第一个和第二个关键部分构建范围（f1=1，f2>40）。

6.执行范围扫描。

7.获取第一个关键部分的下一个不同值（f1=2）。

8.根据第一个和第二个关键部分构建范围（f1=2 and f2>40）。

9.执行范围扫描。

使用此策略可以减少访问的行数，因为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上的范围条件结合使用。

EXPLAIN输出中指示使用Skip Scan，如下所示：

Extra列的index_used_for_skip_scan表示跳过扫描范围访问方法被使用

如果该索引可用于跳过扫描，则该索引应在possible_keys列中可见。

跳过扫描的使用在优化器跟踪输出中由以下形式的"skip scan"元素指示：

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

您还可以看到一个“best_skip_scan_summary”元素。如果选择“跳过扫描”作为最佳范围访问变体，则会写入“chosen_range_access_summary”。如果选择“跳过扫描”作为总体最佳访问方法，则会出现“best_access_path”元素。

Skip Scan的使用取决于optimizer_switch系统变量的skip_scan 标志的值。

参见第10.9.2节“可切换优化”。

默认情况下，此标志处于打开状态。若要禁用它，请将skip_scan设置为关闭。

除了使用optimizer_switch系统变量控制优化器在整个会话范围内使用skip_scan之外，MySQL还支持优化器提示，以每条语句为基础影响优化器。

参见“优化器提示”。

5.行构造函数表达式的范围优化

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

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（）谓词的右侧，有多个行构造函数。

有关优化器和行构造函数的更多信息，请参阅“行构造函数表达式优化”

6.限制内存用于范围优化

要控制范围优化器可用的内存，请使用range_optimizer_max_mem_size系统变量：

值0表示“无限制”

如果值大于0，优化器将跟踪在考虑范围访问方法时消耗的内存。如果即将超过指定的限制，则放弃范围访问方法，而是考虑其他方法，包括全表扫描。

这可能不太理想。

如果发生这种情况，将出现以下警告（其中N是当前range_optimizer_max_mem_size值）：

Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.

对于UPDATE和DELETE语句，如果优化器返回到全表扫描，并且启用了sql_safe_updates系统变量，则会出现错误而不是警告，因为实际上，没有使用键来确定要修改哪些行。

有关详细信息，请参阅使用安全更新模式（--Safe Updates）。

对于超出可用范围优化内存并且优化器返回到不算太优化的查询计划的单个查询，增加range_optimizer_max_mem_size值可以提高性能。

SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;

类似地，对于以下查询，每个与AND组合的谓词使用大约125个字节：

SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;

对于带有IN（）谓词的查询：

SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

in（）列表中的每个文字值都算作与OR组合的谓词。如果有两个IN（）列表，则与OR组合的谓词数量是每个列表中文字值数量的乘积。因此，在前面的情况下，与OR组合的谓词的数量是M×N。

 

 主博客：

【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客

上一篇：

【MySQL精通之路】SQL优化(1)-查询优化(1)-WHERE子句-CSDN博客

下一篇：

【MySQL精通之路】SQL优化(1)-查询优化(3)-索引合并-CSDN博客

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布