8.2.1.12 条件过滤

在联接处理中，前缀行是从联接中的一个 table 传递到下一个 table 的那些行。通常，优化程序会尝试在连接 Sequences 的早期放置前缀计数较低的 table，以防止行组合的数量迅速增加。在某种程度上，优化器可以使用有关从一个 table 中选择并传递到下一个 table 的行的条件的信息，它可以更准确地计算行估计并选择最佳执行计划。

如果不使用条件过滤，则 table 的前缀行数将基于WHERE子句选择的估计行数，根据优化程序选择的访问方法，该行数由WHERE子句选择。通过条件过滤，优化器可以使用WHERE子句中访问方法未考虑的其他相关条件，从而改善其前缀行数估计。例如，即使可能存在基于索引的访问方法，该方法可用于从联接中的当前 table 中选择行，但WHERE子句中的 table 也可能存在其他条件，可以过滤(进一步限制)传递给下一张 table 的合格行的估算值。

仅在以下情况下，条件才有助于过滤估计：

它引用当前 table。
它取决于连接序列中一个或多个常量值。
访问方法尚未考虑它。

在EXPLAIN输出中，rows列指示所选访问方法的行估计，而filtered列反映条件过滤的效果。 filtered值 table 示为百分比。最大值为 100，table 示没有行过滤发生。值从 100 减小 table 示过滤量增加。

前缀行数(估计从当前 table 通过联接传递到下一个 table 的行数)是rows和filtered值的乘积。即，前缀行数是估计的行数，该估计的行数由于估计的滤波效果而减少。例如，如果rows为 1000 且filtered为 20％，则条件过滤会将估算的行数 1000 减少为前缀行数 1000×20％= 1000×0.2 = 200.

考虑以下查询：

SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

假设数据集具有以下 Feature：

employeetable 具有 1024 行。
departmenttable 有 12 行。
两个 table 在dept_no上都有一个索引。
employeetable 在first_name上具有索引。
8 个行在employee.first_name上满足此条件：

employee.first_name = 'John'

150 个行在employee.hire_date上满足此条件：

employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

1 行满足以下两个条件：

employee.first_name = 'John'
AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

没有条件过滤，EXPLAIN会产生如下输出：

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

对于employee，在name索引上的访问方法将拾取与名称'John'匹配的 8 行。没有进行任何过滤(filtered为 100％)，因此所有行都是下一张 table 的前缀行：前缀行计数为rows×filtered = 8×100％= 8.

通过条件过滤，优化器还考虑了WHERE子句中的条件，而访问方法并未考虑这些条件。在这种情况下，优化器使用试探法来估计employee.hire_date上的BETWEEN条件的过滤效果为 16.31％。结果，EXPLAIN产生如下输出：

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

现在，前缀行计数为rows×filtered = 8×16.31％= 1.3，它更紧密地反映了实际数据集。

通常，优化器不会为最后一个联接 table 计算条件过滤效果(前缀行数减少)，因为没有下一个 table 可以将行传递给该 table。 EXPLAIN发生异常：为了提供更多信息，将为所有联接的 table(包括最后一个 table)计算过滤效果。

要控制优化器是否考虑其他过滤条件，请使用optimizer_switch系统变量的condition_fanout_filter标志(请参阅第 8.9.2 节“可切换的优化”)。默认情况下，此标志是启用的，但可以禁用它以抑制条件过滤(例如，如果发现特定查询不使用它会产生更好的性能)。

如果优化器高估了条件过滤的效果，则性能可能会比不使用条件过滤的情况差。在这种情况下，这些技术可能会帮助：

如果未对列进行索引，请对其进行索引，以便优化程序获得有关列值分布的一些信息，并可以改善其行估计。
更改加入 Sequences。完成此操作的方法包括加入 Sequences 优化器提示(请参阅第 8.9.3 节“优化程序提示”)，紧接SELECT的STRAIGHT_JOIN以及STRAIGHT_JOIN加入运算符。
禁用会话的条件过滤：

SET optimizer_switch = 'condition_fanout_filter=off';

首页

MySql 中文文档

8.2.1.12 条件过滤