Индекс нажмите вниз, yyds!

интервью задняя часть MySQL
Индекс нажмите вниз, yyds!

О проблеме индексации я уже говорил с вами в двух статьях~ Сегодня я расскажу о другой проблеме проталкивания индекса, которая тоже очень интересна!

Перемещение индекса — это функция, появившаяся в MySQL 5.6.index condition pushdown, обычно именуемыйICP, проталкивание индекса повышает эффективность запросов к базе данных за счет уменьшения количества возвратов к таблице.

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

1. Перемещение индекса вниз

Чтобы продемонстрировать проталкивание индекса, я установил два MySQL с докером, один — MySQL5.5.62, а другой — 5.7.26, потому что проталкивание индекса — это новая функция, представленная в MySQL5.6, поэтому эти две версии. Вы можете продемонстрировать функции index pushdown для вас (друзья, которые не понимают docker, могут ответить на docker в фоновом режиме общедоступной учетной записи, есть вводное руководство, написанное Song Ge).

1.1 Подготовка

Сначала у меня есть следующая таблица:

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`(191),`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Я выполнил приведенный выше SQL в MySQL 5.5 и MySQL 5.7 соответственно, чтобы убедиться, что такая таблица есть в обоих MySQL. В этой таблице есть составной индекс, состоящий из имени пользователя и возраста, а имя индекса — имя пользователя.В оставшейся части этой статьи я говорю, что индекс имени пользователя относится к составному индексу.

После того, как таблица будет успешно создана, добавьте в каждую из них некоторые смоделированные данные.Я не буду это демонстрировать.Вы можете добавить смоделированные данные через хранимые процедуры или код Java.Друзья могут решить это сами.

Итак, наши приготовления завершены.

1.2 MySQL 5.5

Давайте сначала покажем вам случай запроса в MySQL 5.5.

Чтобы потом облегчить выражение, я буду помечать каждый SQL:

Рассмотрим следующий SQL (SQL1):

select * from user2 where username='1' and age=99;

Запросите запись на основе имени пользователя и возраста, давайте посмотрим на план выполнения этого SQL (для удобства чтения я добавил\GОтображение данных в столбцах):

С первого взгляда мы обнаружили, что этот индекс используется, но как его использовать, позвольте мне рассказать вам об этом!

В MySQL 5.5, поскольку нет функции проталкивания индекса, поток выполнения приведенного выше SQL выглядит следующим образом:

  1. Во-первых, серверный уровень MySQL вызывает механизм хранения для получения первой записи с именем пользователя='1'.
  2. После того, как механизм хранения находит первую запись с именем пользователя = «1», идентификатор первичного ключа сохраняется в конечном узле B + Tree. В это время с помощью операции таблицы возврата полные данные записи находятся в индекс первичного ключа и возвращается на серверный уровень.
  3. После получения данных серверный уровень определяет, равен ли возраст записи 99. Если age=99, запись возвращается клиенту.Если age!=99, запись отбрасывается.
  4. Поскольку составной индекс, состоящий из имени пользователя+возраст, является обычным индексом, а не уникальным индексом (если это уникальный индекс, то на этом запрос заканчивается), необходимо продолжить поиск записей, удовлетворяющих условиям.

Но обратите внимание на метод поиска на четвертом шаге, а не переходите непосредственно к B+Tree для поиска. Потому что в индексе имени пользователя хранение поля имени пользователя упорядочено, то есть записи с username='1' находятся рядом друг с другом, а листовые узлы B+Tree связаны через двусвязный список, и через конечный узел, можно найти следующий конечный узел (или предыдущий конечный узел).В данных, возвращаемых на втором шаге, есть атрибут next_record, который непосредственно указывает на следующую запись вторичного индекса.После нахождения следующего запись, вернитесь к таблице, чтобы получить все данные и вернуться на уровень сервера, затем повторите шаги 3 и 4.

Давайте взглянем на приведенный выше план выполнения, который согласуется с нашим анализом:

  • Предыдущий тип — ref, что означает поиск данных по индексу.Как правило, при совпадении равного значения используется тип ref.
  • Последнее дополнение — «Используется где», что указывает на то, что данные также фильтруются на уровне сервера.

Давайте посмотрим на другой SQL (SQL2):

select * from user2 where username like 'j%' and age=99;

На самом деле это очень похоже на предыдущий SQL1, с той лишь разницей, что имя пользователя использует нечеткое соответствие.'j%', Сонг Гэ уже поделился с вами в предыдущей статье.На самом деле, эта ситуация также может быть использована для индексации.Для получения дополнительной информации вы можете обратиться к:На самом деле подобное ключевое слово в MySQL тоже может быть проиндексировано!.

Процесс выполнения этого SQL в основном такой же, как и процесс выполнения первого SQL1.Я не буду здесь вдаваться в подробности.Давайте посмотрим на план выполнения этого SQL:

По сравнению с приведенным выше планом выполнения основная причина в том, что тип изменился на диапазон, что означает поиск по диапазону, т.к.'j%'На самом деле он представляет собой интервал сканирования, я не понимаю'j%'Маленький партнер, представляющий интервал сканирования, ткнитеПредыдущая статья.

Первые два SQL, потому что время запросаselect *, поэтому всем нужно вернуть табличную операцию, хотя это составной индекс, и имя пользователя, и возраст в индексе, но только имя пользователя может быть передано в условиях запроса механизму хранения, а полные данные строки данных могут быть возвращены из механизма хранения.После записи он возвращается на серверный уровень, а затем серверный уровень оценивает, соответствует ли возраст условиям. На самом деле невооруженным глазом видно, что эффективность запроса относительно низкая.Очевидно, что в индексе есть значение возраста, но он не сравнивает возраст в индексе.Сравнение возраста, если сравнение не удается, запись отбрасывается. .Если мы можем напрямую передать возраст в подсистему хранения, мы можем напрямую судить, соответствует ли возраст условиям в подсистеме хранения, а затем вернуться к таблице.Повысить эффективность запросов.

Именно это и делает технология проталкивания индекса, представленная начиная с MySQL 5.6.

1.3 MySQL 5.7

Давайте также посмотрим на выполнение двух вышеупомянутых SQL-запросов в MySQL 5.7, давайте посмотрим на первый (SQL3):

select * from user2 where username like 'j%' and age=99;

Взгляните на план запроса:

Можно увидеть, что по сравнению с планом запроса SQL2 этот план запроса в основном связан с тем, что последнее дополнение использует условие индекса.Что это значит?

Это введение ICP с проталкиванием индекса начиная с MySQL 5.6 Давайте посмотрим на конкретный процесс работы:

  1. Уровень сервера MySQL сначала вызывает механизм хранения, чтобы найти первое имя пользователя, начинающееся с j.
  2. Найдя запись, механизм хранения не спешит обратно к таблице, а продолжает судить, равен ли возраст этой записи 99. Если age=99, возвращаемся к таблице и продолжаем читать следующую запись.
  3. Механизм хранения возвращает прочитанные строки данных на серверный уровень. В это время, если есть другие неиндексированные условия запроса, серверный уровень продолжит фильтрацию. В нашем случае выше, в это время нет других условий запроса. Если предположить, что на уровне сервера есть другие условия фильтрации, и это условие фильтрации отфильтровывает только что найденную запись, то следующая запись будет прочитана через атрибут next_record записи, а затем будет повторен второй шаг.

Это проталкивание индекса (index condition pushdown, ICP), эффективно снижая количество возвратов к таблице и повышая эффективность запросов.

Иногда, когда мы смотрим на старую версию MySQL, мы обнаруживаем, что это очень необъяснимо.Понижение индекса — такая логичная функция, но, к сожалению, в то время ее не существовало! К счастью, в конечном итоге они будут.

Давайте рассмотрим частный случай и посмотрим на следующий SQL (SQL4):

select * from user2 where username='1' and age=99;

По сравнению с предыдущими условия запроса здесь стали равнозначными сравнениями.Давайте посмотрим на его план выполнения следующим образом:

Видно, что по сравнению с планом запроса SQL1, этот план запроса в основном потому, что последний Extra равен нулю, и нет никакой дополнительной операции.На самом деле, это просто специальная обработка.Использование условий поиска username='1' and age=99После нахождения данных из механизма хранения нет необходимости повторять оценку (когда индекс помещается вниз в SQL3, оценивается не только значение возраста, но и значение имени пользователя).

2. Резюме

Что ж, благодаря сравнению MySQL 5.5 и MySQL 5.7 теперь все понимают, что такое проталкивание индекса? По сути, одно предложение: в поисковике заранее определить, выполняются ли соответствующие условия поиска, а затем вернуться к таблице, если они выполняются, и повысить эффективность запросов за счет уменьшения количества возвращаемых таблиц.

Категории