MySQL8.0官方文档第八章优化（十四）——条件过滤

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.13 条件过滤

在连接处理中&＃xff0c;前缀行是那些在连接中从一个表传递到下一个表的行。通常&＃xff0c;优化器会尝试在连接顺序的早期放置前缀行数较低的表&＃xff0c;以防止行组合的数量迅速增加。如果优化器可以使用从一个表中选择并传递给下一个表的行上的条件信息&＃xff0c;那么它就可以更准确地计算出行数的估计值&＃xff0c;并选择最佳的执行计划。

在没有条件过滤的情况下&＃xff0c;表的前缀行数会根据优化器选择的访问方法不同&＃xff0c;基于WHERE子句所选择的行估计数进行估算。条件过滤使优化器可使用访问方法还没有考虑到的WHERE子句中的其他相关条件&＃xff0c;从而改进其前缀行数的估计。例如&＃xff0c;在连接的当前表中&＃xff0c;尽管可能会有一个基于索引选择行的访问方法&＃xff0c;也有可能还有该表的WHERE子句其他附加条件&＃xff0c;所以&＃xff0c;利用该附加条件&＃xff0c;可以过滤掉&＃xff08;进一步限制&＃xff09;一些本来会传递给下一个表的符合条件的行数。

只有在以下情况下&＃xff0c;条件才有助于过滤估计行数&＃xff1a;

• 它引用当前表。

• 它依赖于一个常量值或连接序列中早期表中的值。

• 它尚未被访问方法考虑到。

在EXPLAIN输出中&＃xff0c;rows列表示所选访问方法的行估计值&＃xff0c;而filtered列反映出条件过滤的效果。过滤的值表示为百分比。最大值是100&＃xff0c;这意味着没有对行进行过滤。从100开始减小的值表示过滤的数量在增加。

前缀行数量&＃xff08;在连接中从当前表传递到下一个表的估计行数&＃xff09;是行和筛选比例的乘积。也就是说&＃xff0c;前缀行数量是估计的行数&＃xff0c;通过估计的过滤效果而减少。例如&＃xff0c;如果行数为1000&＃xff0c;而筛选比例为20%&＃xff0c;则条件筛选使估计的行数1000减少为前缀行数1000× 20% &＃61; 1000 × 0.2 &＃61; 200。

考虑以下查询&＃xff1a;

SELECT *FROM employee JOIN department ON employee.dept_no &＃61; department.dept_noWHERE employee.first_name &＃61; &＃39;John&＃39;AND employee.hire_date BETWEEN &＃39;2018-01-01&＃39; AND &＃39;2018-06-01&＃39;;


假设数据集具有以下特征&＃xff1a;

• 雇员表&＃xff08;employee&＃xff09;有1024行数据。

• 部门表&＃xff08;department&＃xff09;有12行数据。

• 两个表都在dept_no&＃xff08;部门编号&＃xff09;上创建了一个索引dept。

• 雇员表中在名字&＃xff08;first_name&＃xff09;上有一个索引name。

• 在employee.first_name上满足以下条件的有8行&＃xff1a;

  employee.first_name &＃61; &＃39;John&＃39;


• 在employee.hire_date上满足以下条件的有150行&＃xff1a;

  employee.hire_date BETWEEN &＃39;2018-01-01&＃39; AND &＃39;2018-06-01&＃39;


• 满足以下两个条件的只有1行&＃xff1a;

  employee.first_name &＃61; &＃39;John&＃39;
AND employee.hire_date BETWEEN &＃39;2018-01-01&＃39; AND &＃39;2018-06-01&＃39;


如果没有条件过滤&＃xff0c;EXPLAIN会产生如下输出&＃xff1a;

&＃43;----&＃43;------------&＃43;--------&＃43;------------------&＃43;---------&＃43;---------&＃43;------&＃43;----------&＃43;
| id | table | type | possible_keys | key | ref | rows | filtered |
&＃43;----&＃43;------------&＃43;--------&＃43;------------------&＃43;---------&＃43;---------&＃43;------&＃43;----------&＃43;
| 1 | employee | ref | name,h_date,dept | name | const | 8 | 100.00 |
| 1 | department | eq_ref | PRIMARY | PRIMARY | dept_no | 1 | 100.00 |
&＃43;----&＃43;------------&＃43;--------&＃43;------------------&＃43;---------&＃43;---------&＃43;------&＃43;----------&＃43;


对于employee表&＃xff0c;使用name索引的访问方法将拾取与名字“John”匹配的8行。本次查询没有过滤&＃xff08;筛选为100%&＃xff09;&＃xff0c;所以&＃xff0c;这些行都是下一个表的前缀行&＃xff1a;前缀行数是行 × 过滤率 &＃61; 8 × 100% &＃61; 8。

通过条件过滤&＃xff0c;优化器还会考虑访问方法没有考虑到的WHERE子句中的条件。在本例中&＃xff0c;优化器使用启发式方法估计employee.hire_date的BETWEEN条件的过滤率为16.31%。因此&＃xff0c;EXPLAIN会产生如下输出&＃xff1a;

&＃43;----&＃43;------------&＃43;--------&＃43;------------------&＃43;---------&＃43;---------&＃43;------&＃43;----------&＃43;
| id | table | type | possible_keys | key | ref | rows | filtered |
&＃43;----&＃43;------------&＃43;--------&＃43;------------------&＃43;---------&＃43;---------&＃43;------&＃43;----------&＃43;
| 1 | employee | ref | name,h_date,dept | name | const | 8 | 16.31 |
| 1 | department | eq_ref | PRIMARY | PRIMARY | dept_no | 1 | 100.00 |
&＃43;----&＃43;------------&＃43;--------&＃43;------------------&＃43;---------&＃43;---------&＃43;------&＃43;----------&＃43;


现在的前缀行数为行数 × 过滤率 &＃61; 8 × 16.31% &＃61; 1.3&＃xff0c;更能反映实际数据集。

通常&＃xff0c;优化器不会计算最后连接表的条件筛选效果&＃xff08;前缀行计数减少&＃xff09;&＃xff0c;因为没有可传递行的下一个表。EXPLAIN出现了一个异常&＃xff1a;为了提供更多信息&＃xff0c;将对所有连接的表&＃xff08;包括最后一个表&＃xff09;计算过滤效果。

要控制优化器是否考虑额外的过滤条件&＃xff0c;请使用optimizer_switch系统变量的condition_fanout_filter&＃xff08;条件展开过滤&＃xff09;标志&＃xff08;见8.9.2节“可切换优化”&＃xff09;。该标志在默认情况下是启用的&＃xff0c;但想要抑制条件过滤&＃xff0c;可以禁用它&＃xff08;例如&＃xff0c;如果发现某个特定查询在不启用它的情况下会产生更好的性能&＃xff09;。

如果优化器高估了条件过滤的效果&＃xff0c;则性能可能会比不使用条件过滤时差。在这种情况下&＃xff0c;这些技术可能有助于&＃xff1a;

• 如果一个列没有索引&＃xff0c;则对它创建索引&＃xff0c;这样优化器就可以获得一些关于列值分布的信息&＃xff0c;并可以改进它的行数估计。

• 类似地&＃xff0c;如果没有可用的列直方图信息&＃xff0c;则生成直方图&＃xff08;参见第8.9.6节“优化器统计”&＃xff09;。

• 更改连接顺序。实现这一点的方法包括连接顺序优化器提示&＃xff08;请参阅第8.9.3节“优化器提示”&＃xff09;、紧跟在SELECT之后的STRAIGHT_JOIN&＃xff08;直连接&＃xff09;以及STRAIGHT_JOIN连接操作符。

• 禁用会话的条件筛选&＃xff1a;

  SET optimizer_switch &＃61; &＃39;condition_fanout_filter&＃61;off&＃39;;


  或者&＃xff0c;对于给定的查询&＃xff0c;使用优化器提示&＃xff1a;

  SELECT /*&＃43; SET_VAR(optimizer_switch &＃61; &＃39;condition_fanout_filter&＃61;off&＃39;) */ ...


上一集 MySQL 8.0 官方文档 第八章 优化&＃xff08;十三&＃xff09;—— 块嵌套循环和批量键访问连接