10.2.1.6 Index Condition Pushdown Optimization — MySQL 9.2

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

Оптимизация с использованием условия индекса (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 поддерживает вторичные индексы на виртуальных сгенерированных столбцах.
Условий, ссылающихся на подзапросы, нельзя передать вниз.
Условий, ссылающихся на хранимые функции, нельзя передать вниз. Движки хранения не могут вызывать хранимые функции.
Срабатывающие условия нельзя передать вниз. (Дополнительную информацию о срабатывающих условиях см. в разделе 10.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';

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