Всем привет, я третий ребенок, сегодня поделюсь небольшим знанием - index push down.

Если вы слышите в интервью такие слова, как «MySQL 5.6» и «оптимизация индекса», вы должны понять это немедленно. Это вопрос «расширение индекса».

Что такое проталкивание индекса

Index Condition Pushdown (сокращенно ICP) — это новая функция MySQL версии 5.6, которая может уменьшить количество запросов обратно к таблице и повысить эффективность запросов.

Принцип оптимизации индекса pushdown

Давайте сначала кратко разберемся с общей архитектурой MySQL:

MySQL大概架构

Уровень службы MySQL отвечает за синтаксический анализ синтаксиса SQL, создание планов выполнения и т. д., а также за вызов уровня механизма хранения для хранения и извлечения данных.

索引下推изтолкать внизНа самом деле это относится к передаче части ответственности верхнего уровня (сервисного уровня) нижнему уровню (механизму) для обработки.

Давайте посмотрим на запрос MySQL без использования ICP:

Механизм хранения читает запись индекса;
В соответствии со значением первичного ключа в индексе найдите и прочитайте полную запись строки;
Механизм хранения передает записьServerслой, чтобы проверить, удовлетворяет ли записьWHEREусловие.

В случае использования ICP процесс запроса:

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

Конкретная практика проталкивания индекса

Теория более абстрактна, перейдем к практике.

использовать пользовательскую таблицуtuser, создать совместный индекс (имя, возраст) в таблице.

用户表

Если есть требование сейчас: получить таблицу名字第一个字是张，而且年龄是10岁的所有用户. Итак, оператор SQL записывается так:

select * from tuser where name like '张%' and age=10;

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

B+树联合索引

Итак, каковы следующие шаги?

ИКП не используется

До MySQL 5.6 механизм хранения был основан на федеративном индексе для поиска.name like '张%'идентификатор первичного ключа (1, 4), выполнить обратное сканирование таблицы один за другим, перейти к кластеризованному индексу, чтобы найти полную запись строки, а затем уровень сервера проанализирует данные в соответствии сage=10进行筛选.

Давайте посмотрим на схему:

未使用ICP

Вы можете видеть, что нам нужно дважды вернуться к таблице и добавить еще одно поле нашего совместного индекса.ageвпустую.

с использованием ПМС

После MySQL 5.6 механизм хранения использует объединенный индекс (имя, возраст) для поискаname like '张%', так как совместный индекс содержитageстолбец, поэтому механизм хранения напрямую присоединяется к индексу в соответствии сage=10фильтр. Выполните обратное сканирование таблицы одно за другим в соответствии с отфильтрованными данными.

Давайте посмотрим на схему:

使用ICP的示意图

Видно, что таблица возвращается только один раз.

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

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

Условия использования проталкивания индекса

можно использовать только дляrange,ref,eq_ref,ref_or_nullметод доступа;
можно использовать только дляInnoDBа такжеMyISAMмеханизм хранения и его таблица разделов;
правильноInnoDBДля механизмов хранения индексирование может применяться только к вторичным индексам (также называемым вспомогательными индексами);

Целью проталкивания индекса является сокращение количества возвратов к таблице, то есть сокращение операций ввода-вывода. заInnoDBизкластеризованный индексДругими словами, данные и индексы вместе, и нет такой вещи, как таблица возврата.

Условия, которые ссылаются на подзапросы, не могут быть переданы вниз;
Условие, которое ссылается на хранимую функцию, не может быть передано вниз, потому что механизм хранения не может вызвать хранимую функцию.

Связанные системные параметры

Выталкивание условия индекса включено по умолчанию, и можно использовать системные параметры.optimizer_switchчтобы проверить, включен ли контроллер.

Просмотр состояния по умолчанию:

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

Состояние переключения:

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

Ссылаться на:

[1] «Механизм хранения данных InnoDB технологии MySQL».

[2] «Практика MySQL 45 лекций»

[3]. Простое понимание и пример проталкивания индекса MySQL (ICP)

[4]. Одна статья, чтобы понять, что такое проталкивание индекса MySQL (ICP)