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

Без фильтрации условий число префиксных строк для таблицы основано на оценочном количестве строк, выбранных с помощью условия 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 (см. Раздел 8.9.2, «Переключаемые оптимизации»). Этот флаг включен по умолчанию, но может быть отключен для подавления фильтрации условий (например, если обнаруживается, что для определенного запроса производительность лучше без нее).

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