查看“筛选条件”的源代码

在连接处理中，前缀行是从一个表传递到下一个表的行。通常，优化器尝试将前缀行数较低的表放在连接顺序的前面，以防止行组合数迅速增加。优化器可以利用有关从一个表选择并传递给下一个表的行的条件信息，从而更准确地计算行估计值并选择最佳执行计划。


没有条件筛选，表的前缀行数基于优化器选择的访问方法根据 WHERE 子句选择的行数估计值。条件筛选使优化器能够使用 WHERE 子句中未被访问方法考虑的其他相关条件，从而改进前缀行数的估计。例如，即使可能有基于索引的访问方法可以用于从当前表中选择行，但 WHERE 子句中可能还有额外的用于该表的条件，可以进一步筛选（限制）传递到下一个表的合格行的估计值。


只有满足以下条件的条件才会对筛选估计值做出贡献：

*它涉及当前表。

*它依赖于连接顺序中较早表中的常量值。

*它尚未被访问方法考虑。


在 EXPLAIN 输出中，rows 列表示所选择的访问方法的行估计值，filtered 列反映了条件筛选的影响。filtered 值以百分比表示。最大值为100，意味着没有对行进行筛选。从100递减的值表示有越来越多的筛选发生。


前缀行数（从当前表在连接中传递到下一个表的行数估计值）是行和filtered 值的乘积。换句话说，前缀行数是估计行数减去筛选效果的结果。例如，如果 rows 是1000，filtered 是20%，条件筛选将1000的估计行数减少到前缀行数为1000 × 20% = 1000 × .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';


假设数据集具有以下特点：

*员工表有1024行。

*部门表有12行。

*两个表都有dept_no的索引。

*员工表有first_name的索引。


在employee.first_name上满足此条件的行数为8行：

 employee.first_name = 'John'


在employee.hire_date上满足此条件的行数为150行：

 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   |
 +----+------------+--------+------------------+---------+---------+------+----------+


对于员工表，使用姓名索引的访问方法选择了8行与名字为'John'匹配的行。没有进行筛选（filtered为100%），因此所有行都是传递到下一个表的前缀行：前缀行数为行数 × 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   |
 +----+------------+--------+------------------+---------+---------+------+----------+


现在前缀行数为行数 × filtered = 8 × 16.31% = 1.3，更接近实际数据集。


通常，优化器不会计算最后一个连接表的筛选效果（前缀行数的减少），因为没有下一个表可以传递行。但在EXPLAIN中会有例外情况：为了提供更多信息，筛选效果会计算所有连接表，包括最后一个表。


要控制优化器是否考虑额外的筛选条件，请使用optimizer_switch系统变量的condition_fanout_filter标志。该标志默认启用，但可以禁用以取消条件筛选（例如，如果发现特定查询在没有条件筛选的情况下性能更好）。


如果优化器高估了条件筛选的效果，性能可能会比不使用条件筛选更差。在这种情况下，以下技巧可能有所帮助：

*如果列没有建立索引，请建立索引，这样优化器就可以获得关于列值分布的一些信息，并改进其行估计值。

*类似地，如果没有列直方图信息可用，请生成直方图。

*改变连接顺序。实现这一点的方法包括连接顺序的优化提示，在SELECT语句之后使用STRAIGHT_JOIN，以及使用STRAIGHT_JOIN连接操作符。

*对于会话，禁用条件筛选：

 SET optimizer_switch = 'condition_fanout_filter=off';

或者，对于给定的查询，使用优化器提示：

 SELECT /*+ SET_VAR(optimizer_switch = 'condition_fanout_filter=off') */ ...