MySQL EXPLAIN и проталкивание индекса из экземпляров LIKE и REGEXP

MySQL

предисловие

Всем известный способ измерения производительности оператора SQL.EXPLAINЗаявления и поля должны быть известны всем, но я думаю, что лучше понять на примерах. В этой статье мы не будем вдаваться в подробности каждой области, многие боги в Интернете могут обобщить лучше, чем я. Эта статья основана наПримеры LIKE и регулярных выраженийсуществуетEXPLAINПредставление.

  • При анализе выполнения операторов SQL используются следующие основные столбцы:type,Extra, следующие тестовые случаи предоставлены официальным сайтомsakilaбаза данных, прилагаетсяссылка на скачивание.

  • filmСитуация с таблицей следующая, на самом деле нужно толькоidx_titleпоказатель:

    Alt text

  • Версия MySQL:8.0+

  • пс: можно комбинироватьэта статьяСмотри, это дополнение.

Примеры и анализ

  • Сценарий: СогласноtitleПод стать соответствующей пленке, от самой простой=Начинать.
  • Примечание. Поскольку механизм innodb является показателем эффективности,rowsможет быть неточным.
  • filtered: Столбец filtered показывает расчетный процент строк таблицы, которые будут отфильтрованы по условию таблицы. Максимальное значение — 100, что означает отсутствие фильтрации строк. Значения, уменьшающиеся от 100, указывают на увеличение объемов фильтрации. количество проверенных строк и количество отфильтрованных строк показывает количество строк, которые будут объединены со следующей таблицей. Например, если строк 1000 и отфильтровано 50,00 (50%), количество строк, которые будут объединены со следующей таблицей, равно 1000 × 50% = 500.

Преобразование: это оценка в процентах того, сколько строк будет отфильтровано по условию таблицы, обычно такое же, какrowsСтолбцы объединены, чтобы увидеть,filtered × rowsЭто количество строк, отфильтрованных по условию таблицы.

=

explain select title from film where title= 'ACE GOLDFINGER';
  • Результаты и анализ: индекс равен постоянной величине,typeдляref,в использованииkeyдляidx_title, который на самом деле использует индекс с'ACE GOLDFINGER'сравнить (т.ref=CONST, так как поиск по индексу соответствует константе (количество прочитанных строкrowsдля1), так как этоПри использовании покрывающего индекса нет возврата к таблице для получения строк данных.,Extraто естьUsing index.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL ref idx_title idx_title 767 const 1 100.00 Using index 
explain select * from film where title= 'ACE GOLDFINGER';
  • Результаты и анализ. Единственная разница в том, что это не покрывающий индекс, потому что он должен запрашивать другие поля в таблице.Extraто естьNULL.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL ref idx_title idx_title 767 const 1 100.00 NULL

LIKE

  • Всем известно, что есть префиксный индекс,LIKEОдним из ключей к предложению является то, что%позицию, проанализируйте следующие четыре ситуации.

% не впереди

explain select title from film where title LIKE 'A%';
  • Результаты и анализ: Здесь важно отметить, чтоtypeа такжеExtra:

1,type: здесьLIKEРассматривается как ограниченное сканирование индекса вместо обхода всех индексов, например, индексаBETWEEN 1 AND 100Эффект. 2. считывается 46 строк данных 3.Extra:Using where; Using indexЧто значит, когда эти два значения вместе одновременно? Давайте сначала продадим его, а потом я расскажу об этом подробно~

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL range idx_title idx_title 767 NULL 46 100.00 Using where; Using index 
explain select * from film where title LIKE 'A%';
  • Результаты и анализ: новыйExtra:Using index condition, как будет подробно описано ниже, остальные поля ничем не отличаются от предыдущего оператора
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL range idx_title idx_title 767 NULL 46 100.00 Using index condition

%спереди

explain select title from film where title LIKE '%A';

Результаты и анализ: 1. Прежде всего, вместо поиска по ограниченному индексу запрашивается вся таблица с использованием индекса, поэтомуtypeдаindex. 2. Прочитать 1000 строк и отфильтровать 1000*11,11 строк.

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL index null idx_title 767 NULL 1000 11.11 Using where; Using index 
explain select * from film where title LIKE '%A';
  • Результаты и анализ: 1. Индекс не используется, полное сканирование таблицы, фильтрация по условиям. 2,Using where: После того, как механизм хранения извлечет строку, сервер MySQL фильтрует и возвращает ее (из High-Performance MySQL, Приложение D EXPLAIN).
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL ALL null null null NULL 1000 11.11 Using where

REGEXP

explain select title from film where title REGEXP '^A';
  • Результаты и анализ. В запросе с регулярным выражением используется полное сканирование таблицы индекса, и нет строк, отфильтрованных по условиям таблицы.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL INDEX null idx_title 767 NULL 1000 100 Using where; Using index 
explain select * from film where title REGEXP '^A';
  • Результаты и анализ: Индекс не используется, полное сканирование таблицы.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL ALL null null null NULL 1000 100 Using where

Using where; Using indexа такжеUsing index condition

  • Здесь я ссылаюсь на официальный сайт, а затем добавляю свой собственный анализ, добро пожаловать на исправление ошибок.
  • Using where; Using index: Проще говоря, мы можем посмотреть на это отдельно,Using whereТо есть сервер MySQL фильтрует строки после того, как механизм хранения извлекает их.Using indexЭто запрос с покрывающим индексом, которому не нужно возвращаться к таблице для получения данных строки. Это составляет:Фильтровать в MySQL Server после извлечения строк с использованием покрывающего индекса.

Бонус: у вас может быть что-то не так в вашем запросе, если дополнительное значение не использует, где и тип соединения таблицы - ВСЕ или индекс.

  • Using index Condition: Официальная документация говорит, что это то же самое, что иIndex Condition Pushdown OptimizationСвязанный, официальный сайт упоминается какICP,существуетInnoDBтак же какMyISAMможно использовать, здесь я перехватываюInnoDBОсновная информация.

1. Сценарий использования: случай, когда MySQL извлекает строки из таблицы с помощью индекса, индекс используется при извлечении строк.Используйте, когда тип ref, range, index, только для диапазона, ref, eq_ref, ref_or_null 2. Исходная информация: без ICP механизм хранения просматривает индекс, чтобы найти строки в базовой таблице, и возвращает их серверу MySQL, который оценивает условие WHERE для строк. Без ICP механизм хранения просматривает индекс, чтобы найти строки в базовой таблице. базовую таблицу и возвращает их на сервер MySQL. Слой выполняет условную фильтрацию WHERE (привычно, используя where). 3. Принцип: с включенным ICP, и если части условия WHERE могут быть оценены с использованием только столбцов из индекса, сервер MySQL помещает эту часть условия WHERE в подсистему хранения, если условие WHERE или его части, Только Используя столбцы в индексе, сервер MySQL может передать эту часть предложения WHERE механизму хранения для обработки. (Поскольку он передается механизму хранения, он выталкивается...) 4. Функция: для таблиц InnoDB ICP используется только для вторичных индексов. Цель ICP — уменьшить количество чтений полных строк и тем самым уменьшить количество операций ввода-вывода. Для кластеризованных индексов InnoDB полная запись уже считывается в буфер InnoDB.Использование ICP в этом случае не уменьшает ввод-вывод, его можно использовать только во вторичных индексах, уменьшая количество раз, чтобы получить данные строки обратно в таблицу и уменьшая количество операций ввода-вывода между сервером MySQL и хранилищем Все данные были загружены в буфер InnoDB без сокращения операций ввода-вывода.

  • Дополнительная информация:

1. ICP используется для методов доступа range, ref, eq_ref и ref_or_null, когда необходимо получить доступ к полным строкам таблицы. 2. Для получения дополнительной информации см.Официальный сайт

  • Это объясняет приведенные выше результаты:

1. объясните, выберите * из фильма, где заголовок LIKE 'A%'; диапазон и нужно вернуть таблицу 2. объясните, выберите заголовок из фильма, где заголовок LIKE 'A%'; не нужно возвращать форму, ICP не активирован 3. объяснить, выбрать заголовок из фильма, где заголовок LIKE '%A'; индекс, нельзя использовать ICP

  • Как включить и выключитьICP:

SET optimizer_switch = 'index_condition_pushdown=off'; SET optimizer_switch = 'index_condition_pushdown=on';

  • Запустите объяснение выбора * из фильма, где заголовок LIKE 'A%' после закрытия ICP:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film NULL range idx_title idx_title 767 NULL 46 100.00 Using where

Заключение (вInnoDBсередина):ICPэтоMySQLДля вторичных индексов вrange,ref,eq_ref,ref_or_nullТип доступа к данным и не переопределять механизм оптимизации индекса (с использованием индекса), помещать данные строки в сервер MySQL (может быть обработан вторичный индекс) для обработки механизма хранения, уменьшать обратное чтение Количество данных уменьшается между I / O операции между сервером MySQL и механизмом хранения.

резюме

Эта статья запускается из экземпляраEXPLAINПоехали, краткое введениеICP, и для тех, кто легко игнорируетсяLIKEА также представлены результаты регулярного выражения.

В дальнейшем я постараюсь цитировать информацию на официальном сайте или в книге и анализировать ее на примерах, надеюсь, это будет полезно для всех, и буду рад внести исправления~

Справочная статья

Категории