8.2.1.5 Index Condition Pushdown Optimization — MySQL 5.7

8.2.1.5 Оптимизация сдвигом условия индекса

Оптимизация сдвигом условия индекса (ICP) предназначена для случаев, когда MySQL извлекает строки из таблицы с использованием индекса. Без ICP движок хранения проходит по индексу, чтобы найти строки в основной таблице, и возвращает их серверу MySQL, который оценивает условие WHERE для строк. При включенном ICP, если части условия WHERE могут быть оценены, используя только столбцы из индекса, сервер MySQL сдвигает эту часть условия WHERE вниз к движку хранения. Затем движок хранения оценивает сдвинутое условие индекса, используя запись индекса, и только если это выполняется, строка считывается из таблицы. ICP может уменьшить количество раз, когда движок хранения должен обращаться к основной таблице, и количество раз, когда сервер MySQL должен обращаться к движку хранения.

Применимость оптимизации сдвигом условия индекса зависит от следующих условий:

ICP используется для методов доступа range, ref, eq_ref и ref_or_null, когда необходимо получить все строки таблицы.
ICP может использоваться для таблиц InnoDB и MyISAM, включая разделяемые InnoDB и MyISAM таблицы.
Для InnoDB таблиц ICP используется только для вторичных индексов. Цель ICP – уменьшить количество чтений полных строк и, таким образом, уменьшить операции ввода-вывода. Для InnoDB кластеризованных индексов вся запись уже считывается в буфер InnoDB. Использование ICP в этом случае не уменьшает ввода-вывода.
ICP не поддерживается для вторичных индексов, созданных на виртуальных сгенерированных столбцах. InnoDB поддерживает вторичные индексы на виртуальных сгенерированных столбцах.
Условия, которые ссылаются на подзапросы, не могут быть сдвинуты вниз.
Условия, которые ссылаются на хранимые функции, не могут быть сдвинуты вниз. Движки хранения не могут вызывать хранимые функции.
Триггерные условия не могут быть сдвинуты вниз. (Для получения информации о триггерных условиях см. Раздел 8.2.2.3, «Оптимизация подзапросов с помощью стратегии EXISTS».)

Чтобы понять, как работает эта оптимизация, сначала рассмотрим, как происходит сканирование индекса, когда сдвиг условия индекса не используется:

Получение следующей строки, сначала считывая кортеж индекса, а затем используя кортеж индекса для поиска и чтения полной строки таблицы.
Проверка части условия WHERE, которая относится к этой таблице. Принятие или отклонение строки на основе результата проверки.

Используя сдвиг условия индекса, сканирование происходит следующим образом:

Получение кортежа индекса следующей строки (но не полной строки таблицы).
Проверка части условия WHERE, относящейся к этой таблице и проверяемой только с помощью столбцов индекса. Если условие не выполняется, переходим к кортежу индекса следующей строки.
Если условие выполняется, используйте кортеж индекса для поиска и чтения полной строки таблицы.
Проверка оставшейся части условия WHERE, относящейся к этой таблице. Принятие или отклонение строки на основе результата проверки.

Вывод EXPLAIN показывает Using index condition в столбце Extra, когда используется сдвиг условия индекса. Он не показывает Using index, потому что это не применимо, когда необходимо читать полные строки таблицы.

Предположим, что таблица содержит информацию о людях и их адресах, и что в таблице определен индекс как INDEX (zipcode, lastname, firstname). Если мы знаем значение zipcode человека, но не уверены в фамилии, мы можем выполнить поиск следующим образом:

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

MySQL может использовать индекс для сканирования людей с zipcode='95054'. Вторая часть (lastname LIKE '%etrunia%') не может быть использована для ограничения количества строк, которые необходимо просканировать, поэтому без сдвига условия индекса этот запрос должен извлечь полные строки таблицы для всех людей, у которых zipcode='95054'.

При использовании сдвига условия индекса MySQL проверяет часть lastname LIKE '%etrunia%' перед чтением полной строки таблицы. Это позволяет избежать чтения полных строк, соответствующих кортежам индекса, которые соответствуют условию zipcode, но не условию lastname.

Сдвиг условия индекса включен по умолчанию. Его можно контролировать с помощью системной переменной optimizer_switch путем установки флага index_condition_pushdown:

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

См. Раздел 8.9.2, «Переключаемые оптимизации».