15.MySQL优化Condition Filtering

介绍

在连接处理中，前缀行是从连接中的一个表传递到下一个表的那些行。通常，优化器尝试在连接顺序的早期放置具有低前缀计数的表，以保持行组合的数量不会快速增加。在某种程度上，优化器可以利用一些条件信息查询表中需要的行传递给下一个，它可以更准确地计算行估计并选择最佳执行计划。

假如没有条件过滤，表的前缀行数是基于各种优化器的访问方法根据Where条件进行的估量估计。Condition filtering使优化器能够使用WHERE访问方法未考虑的子句中的其他相关条件 ，从而改进其前缀行计数估计。例如，可能存在可用于从连接中的当前表中选择行的基于索引的访问方法，但在该表中可能还存在其他条件。WHERE 可以过滤（进一步限制）传递给下一个表的合格行的估计的子句。

只有在以下情况下，条件才会影响过滤估算：

• 条件过滤指的是当前表
• 它取决于连接序列中早期表的常量值或值。
• 它不考虑已经存在的访问方法

在EXPLAIN输出中，rows列指示所选访问方法的行估计值，该filtered 列反映条件筛选的效果。 filtered值以百分比表示。最大值为100，这意味着不会对行进行过滤。值从100开始减少表示过滤量增加。

前缀行计数（估计从连接中的当前表传递到下一个的行数）是rows和 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';

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

• 该employee表有1024行。

• 该department表有12行。

• 两个表都有一个索引dept_no。

• 该employee表有一个索引 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索引上的访问方法会 获取与名称匹配的8行’John’。没有进行过滤（filtered是100％），因此所有行都是下一个表的前缀行：前缀行计数为 rows× filtered= 8×100％= 8。

通过条件过滤，优化器还考虑WHERE了访问方法未考虑的子句中的条件。在这种情况下，优化器使用试探法来估计所述的BETWEEN的条件下employee.hire_date过滤效果为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，这更接近地反映了实际数据集。

通常，优化器不会计算最后一个连接表的条件过滤效果（前缀行数减少），因为没有下一个表要传递行。

如果优化器过高估计条件过滤的影响，则性能可能比不使用条件过滤时更差。在这种情况下，这些技术可能有所帮助：

• 如果未对列建立索引，请对其进行索引，以便优化程序具有有关列值分布的一些信息，并可以改进其行估计值。

• 同样，如果没有可用的列直方图信息，则生成直方图（参见 第8.9.6节“优化程序统计”）。

• 更改连接顺序。完成此操作的方法包括连接顺序优化器提示（请参见 第8.9.2节“优化程序提示”）， STRAIGHT_JOIN紧跟在 SELECT和 STRAIGHT_JOIN连接运算符之后。

• 禁用会话的条件筛选：

    SET optimizer_switch = 'condition_fanout_filter=off';
  

• 或者，对于给定查询，使用优化程序提示：

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