При обработке объединений префиксные строки — это те строки, которые передаются из одной таблицы в другую при объединении. В общем случае оптимизатор пытается поместить таблицы с низким количеством префиксных строк в начале порядка объединения, чтобы количество комбинаций строк не увеличивалось слишком быстро. В той мере, в какой оптимизатор может использовать информацию о условиях, примененных к строкам, выбранным из одной таблицы и переданным в следующую, тем точнее он может вычислить оценки количества строк и выбрать лучший план выполнения.

Без фильтрации условий количество префиксных строк для таблицы основано на оценке количества строк, выбранных в соответствии с клаузой WHERE, в зависимости от выбранного оптимизатором метода доступа. Фильтрация условий позволяет оптимизатору использовать другие соответствующие условия в клаузе WHERE, которые не учитываются методом доступа, и тем самым улучшить оценки количества префиксных строк. Например, даже если может быть использован метод доступа на основе индекса для выбора строк из текущей таблицы в объединении, могут также существовать дополнительные условия для таблицы в клаузе WHERE, которые могут отфильтровать (дальше ограничить) оценку подходящих строк, передаваемых следующей таблице.

Условие вносит вклад в оценку фильтрации только в том случае, если:

В выводе EXPLAIN столбец rows указывает оценку количества строк для выбранного метода доступа, а столбец filtered отражает эффект фильтрации условий. filtered значения выражаются в процентах. Максимальное значение — 100, что означает, что фильтрация строк не произошла. Значения, уменьшающиеся от 100, указывают на увеличение объема фильтрации.

Количество префиксных строк (оцениваемое количество строк, передаваемых из текущей таблицы в объединении в следующую) является произведением 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';

Предположим, что набор данных имеет следующие характеристики:

Без фильтрации условий 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, которые не учитывались методом доступа. В этом случае оптимизатор использует эвристику для оценки эффекта фильтрации 16,31% для условия BETWEEN по полю 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 системной переменной optimizer_switch (см. Раздел 10.9.2, «Переключаемые оптимизации»). Этот флаг включен по умолчанию, но может быть отключен для подавления фильтрации условий (например, если обнаружено, что определённый запрос работает быстрее без неё).

Если оптимизатор переоценивает эффект фильтрации условий, производительность может быть хуже, чем если фильтрация условий не используется. В таких случаях эти методы могут помочь: