Оптимизация проталкивания индекса — это новая функция, добавленная в MySQL 5.6.
Index Conditional Pushdown (ICP) — это оптимизация для случая, когда MySQL использует индекс для извлечения строк из таблицы. Без ICP механизм хранения просматривает индекс, чтобы найти строки в базовой таблице, и возвращает их на сервер MySQL для еще одного раунда фильтрации на уровне сервера. При включенном ICP, если для оценки некоторой части условия WHERE используется только столбец в индексе, сервер MySQL передаст эту часть условия WHERE механизму хранения. Затем механизм хранения использует запись индекса для оценки условия отправленного индекса и считывает строки из таблицы только в том случае, если это условие выполняется.
ICP может уменьшить количество раз, когда подсистема хранения должна обращаться к базовой таблице, и количество раз, когда сервер MySQL должен обращаться к подсистеме хранения.
Применимость ПМС зависит от следующих условий:
-
ICP используется для методов доступа range, ref, eq_ref и ref_or_null, когда требуется доступ к полным строкам таблицы.
-
ICP доступен для таблиц InnoDB и MyISAM. (Исключение: ICP не поддерживается для секционированных таблиц в MySQL 5.6; это исправлено в MySQL 5.7.)
-
Для таблиц InnoDB ICP используется только для вторичных индексов.. Цель ICP — уменьшить количество операций чтения полных строк и, следовательно, операций ввода-вывода. Для кластеризованных индексов InnoDB полная запись была прочитана в буфер InnoDB. Использование ICP в этом случае не ухудшит работу ввода-вывода.
-
Условия, которые ссылаются на подзапросы, не могут быть переданы вниз.
-
Условия, связанные с сохраненными функциями, не могут быть протолкнуты вниз. Механизм хранения не может вызвать хранимую функцию.
-
Условия триггера не могут быть снижены.
Чтобы понять, как работает эта оптимизация, сначала рассмотрим ход сканирования индекса без использования условного проталкивания индекса:
Чтобы получить следующую строку, сначала прочитайте кортеж индекса, а затем используйте кортеж индекса, чтобы найти и прочитать всю строку таблицы.
Проверьте часть условия WHERE, применимую к этой таблице. Примите или отклоните строку на основе результатов проверки.
С проталкиванием вниз по условию индекса сканирование происходит следующим образом:
Получить индексный кортеж следующей строки (но не всей строки таблицы).
Тест применяется к части условия WHERE для этой таблицы и может быть проверен только с использованием индексированных столбцов. Если условие не выполняется, продолжить с индексным кортежем следующей строки.
Если условие выполнено, используйте индексный кортеж, чтобы найти и прочитать всю строку таблицы.
Проверьте оставшуюся часть условия WHERE, применимого к этой таблице. Примите или отклоните строку на основе результатов проверки.
Выходные данные EXPLAIN показывают, что условия индексирования используются в дополнительных столбцах при использовании условий индексирования для нажатия вниз. Он не показывает использование индекса, потому что он неприменим, когда нужно прочитать всю строку таблицы.
Предположим, что таблица содержит информацию о людях и их адресах, а индекс таблицы определен как ИНДЕКС (почтовый индекс, фамилия, имя). Если мы знаем значение почтового индекса человека, но не фамилию, мы можем выполнить поиск следующим образом:
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
MySQL может использовать индекс для поиска людей с zipcode='95054'. Вторая часть (фамилия LIKE '%etrunia%') не может использоваться для ограничения количества сканируемых строк, поэтому без ICP этот запрос должен получить полную строку таблицы для всех с zipcode='95054'.
С ICP MySQL проверяет LIKE '%etrunia%' часть фамилии перед чтением всей строки таблицы. Это позволяет избежать чтения полной строки, соответствующей индексному кортежу, соответствующему условию почтового индекса, но не условию фамилии.
Условное проталкивание вниз по индексу включено по умолчанию. Это можно контролировать, установив флаг index_condition_pushdown с помощью системной переменной Optimizer_switch:
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';