在联接处理中,前缀行是从联接中的一个表传递到下一个表的那些行。通常,优化器会尝试将前缀计数较低的表放在连接顺序的早期,以防止行组合的数量快速增加。在某种程度上,优化器可以使用有关从一个表中选择并传递给下一个表的行的条件信息,它可以更准确地计算行估计并选择最佳执行计划。
在没有条件过滤的情况下,表的前缀行计数基于
WHERE
根据优化器选择的访问方法由子句选择的估计行数。条件过滤使优化器能够使用
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
在这种情况下,优化器使用启发式方法估计对条件的过滤效果为 16.31% employee.hire_date
。结果,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,这更接近于实际数据集。
通常情况下,优化器不会计算最后一个连接表的条件过滤效果(前缀行数减少),因为没有下一张表可以传递行。发生异常
EXPLAIN
:为了提供更多信息,对所有联接表(包括最后一个)计算过滤效果。
要控制优化器是否考虑额外的过滤条件,请使用
系统变量
的condition_fanout_filter
标志(请参阅第 8.9.2 节,“可切换优化”)。默认情况下启用此标志,但可以禁用以抑制条件过滤(例如,如果发现特定查询在没有它的情况下会产生更好的性能)。
optimizer_switch
如果优化器高估了条件过滤的效果,性能可能会比不使用条件过滤时更差。在这种情况下,这些技术可能会有所帮助:
如果一个列没有被索引,那么就为它建立索引,这样优化器就有了一些关于列值分布的信息,并且可以改进它的行估计。
更改加入顺序。实现此目的的方法包括连接顺序优化器提示(请参阅 第 8.9.3 节,“优化器提示”),
STRAIGHT_JOIN
紧跟在SELECT
, 和STRAIGHT_JOIN
连接运算符之后。禁用会话的条件过滤:
SET optimizer_switch = 'condition_fanout_filter=off';