Всем привет, я третий ребенок, сегодня поделюсь небольшим знанием - index push down.
Если вы слышите в интервью такие слова, как «MySQL 5.6» и «оптимизация индекса», вы должны понять это немедленно. Это вопрос «расширение индекса».
Что такое проталкивание индекса
Index Condition Pushdown (сокращенно ICP) — это новая функция MySQL версии 5.6, которая может уменьшить количество запросов обратно к таблице и повысить эффективность запросов.
Принцип оптимизации индекса pushdown
Давайте сначала кратко разберемся с общей архитектурой 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.
Итак, каковы следующие шаги?
ИКП не используется
До MySQL 5.6 механизм хранения был основан на федеративном индексе для поиска.name like '张%'
идентификатор первичного ключа (1, 4), выполнить обратное сканирование таблицы один за другим, перейти к кластеризованному индексу, чтобы найти полную запись строки, а затем уровень сервера проанализирует данные в соответствии сage=10进行筛选
.
Давайте посмотрим на схему:
Вы можете видеть, что нам нужно дважды вернуться к таблице и добавить еще одно поле нашего совместного индекса.age
впустую.
с использованием ПМС
После MySQL 5.6 механизм хранения использует объединенный индекс (имя, возраст) для поискаname like '张%'
, так как совместный индекс содержитage
столбец, поэтому механизм хранения напрямую присоединяется к индексу в соответствии сage=10
фильтр. Выполните обратное сканирование таблицы одно за другим в соответствии с отфильтрованными данными.
Давайте посмотрим на схему:
Видно, что таблица возвращается только один раз.
В дополнение к этому мы также можем посмотреть план выполнения и увидеть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)