Всем привет, меня зовут Сяолинь.
На работе, если мы хотим повысить скорость запроса оператора, мы обычно хотим создать индекс для поля.
Но индексы не панацея. Создание индекса не означает, что любой оператор запроса может пройти сканирование индекса.
Если вы не обратите внимание, написанный вами оператор запроса может привести к сбою индекса, и, таким образом, будет выполнено полное сканирование таблицы.Хотя результат запроса в порядке, производительность запроса значительно снижается.
Сегодня я поделюсь с вами 6 распространенными сценариями, в которых происходит сбой индекса.
Мы не только будем использовать экспериментальные примеры, чтобы объяснить всем, но также проясним причины каждого отказа индекса..
Идти!
Как выглядит структура хранения индексов?
Давайте сначала посмотрим, как выглядит структура хранения индексов? Потому что только знание структуры хранения индекса может лучше понять проблему сбоя индекса.
Структура хранения индекса связана с тем, какой механизм хранения использует MySQL, поскольку механизм хранения отвечает за сохранение данных на диске, а структура данных индекса, используемая разными механизмами хранения, будет отличаться.
Механизмом хранения MySQL по умолчанию является InnoDB, который использует B + Tree в качестве структуры данных индекса.Что касается того, почему B + Tree выбрано в качестве структуры данных индекса, для подробного анализа см. мою статью:Почему MySQL любит деревья B+?
При создании таблицы механизм хранения InnoDB по умолчанию создает индекс первичного ключа, то есть кластеризованный индекс, а остальные индексы относятся к вторичным индексам.
Механизм хранения MySQL MyISAM поддерживает различные структуры данных индексов, такие как индексы дерева B+, индексы дерева R и полнотекстовые индексы. Когда подсистема хранения MyISAM создает таблицу, созданный индекс первичного ключа по умолчанию использует индекс дерева B+.
Хотя и InnoDB, и MyISAM поддерживают индексы дерева B+, их структуры хранения данных реализованы по-разному. Разница в следующем:
- Механизм хранения InnoDB: конечные узлы индекса дерева B+ сами сохраняют данные;
- Механизм хранения MyISAM: физический адрес, по которому конечные узлы индекса дерева B+ хранят данные;
Далее я приведу пример, чтобы показать вам разницу между структурами хранения индексов двух механизмов хранения.
Вот таблица t_user, в которой поле id является индексом первичного ключа, а остальные — обычными полями.
Если используется механизм хранения MyISAM, конечный узел индекса дерева B+ хранит физический адрес данных, то есть указатель пользовательских данных, как показано на следующем рисунке:
Если используется механизм хранения InnoDB, конечные узлы индекса дерева B+ сами сохраняют данные, как показано на следующем рисунке:
Механизм хранения InnoDB разделен на кластеризованный индекс (на рисунке выше показан кластеризованный индекс) и вторичный индекс в соответствии с различными типами индексов. Разница между ними заключается в том, что листовые узлы кластеризованного индекса хранят фактические данные, все полные пользовательские данные хранятся в листовых узлах кластерного индекса, а листовые узлы вторичного индекса хранят значение первичного ключа, а не фактическое значение. данные.
Если в поле имени задан общий индекс, то вторичный индекс будет таким, как показано на рисунке ниже, а конечные узлы сохранят только значение первичного ключа.
Зная структуру хранения кластеризованного индекса и вторичного индекса механизма хранения InnoDB, давайте дадим несколько операторов запроса, чтобы объяснить, как процесс запроса выбирает, какой тип индекса использовать.
Когда мы используем поле «индекс первичного ключа» в качестве условного запроса, если запрашиваемые данные находятся в листовых узлах «кластеризованного индекса», тогда соответствующие конечные узлы будут извлечены из дерева B+ в «кластеризованном индексе». " , а затем непосредственно прочитать запрашиваемые данные. Например, следующее предложение:
// id 字段为主键索引
select * from t_user where id=1;
Когда мы используем поле «вторичный индекс» в качестве условного запроса, если запрашиваемые данные находятся в листовых узлах «кластеризованного индекса», нам нужно получить два дерева B+:
- Сначала найдите соответствующий конечный узел в дереве B+ «вторичного индекса» и получите значение первичного ключа;
- Затем используйте значение первичного ключа, полученное на предыдущем шаге, для извлечения соответствующего конечного узла в дереве B+ в «кластеризованном индексе», а затем получите запрашиваемые данные.
Вышеупомянутый процесс называетсяформа возврата, как в следующем утверждении:
// name 字段为二级索引
select * from t_user where name="林某";
Когда мы используем поле «вторичный индекс» в качестве условного запроса, если запрашиваемые данные находятся в листовом узле «вторичного индекса», нам нужно только найти соответствующий конечный узел в дереве B+ «вторичного индекса». ", а затем читать Получить данные для запроса, этот процесс называетсяиндекс покрытия. Например, следующее предложение:
// name 字段为二级索引
select id from t_user where name="林某";
Все условия приведенных выше операторов запроса используют столбец индекса, поэтому индекс используется в процессе запроса.
Но это не значит, что если условие запроса использует столбец индекса, то и процесс запроса должен использовать индекс.Далее посмотрим, какие условия приведут к реализации индекса, и произойдет полное сканирование таблицы.
Прежде всего, для следующего экспериментального случая я использую версию MySQL:8.0.26
.
Используйте левое или левое нечеткое сопоставление для индексов
Когда мы используем левое или левое нечеткое сопоставление, то естьlike %xx
илиlike %xx%
В любом случае это приведет к сбою индекса.
Например, в следующем подобном операторе для запроса пользователей с суффиксом имени «forest» type=ALL в плане выполнения представляет собой полное сканирование таблицы без прохождения индекса.
// name 字段为二级索引
select * from t_user where name like '%林';
Если запрос предназначен для пользователя, чье имя имеет префикс леса, то будет выполнено сканирование индекса.Тип=диапазон в плане выполнения указывает, что сканирование индекса выполняется, а ключ=имя_индекса может видеть, что индекс имя_индекса на самом деле ушел:
// name 字段为二级索引
select * from t_user where name like '林%';
Почему нечеткое левое или левое и правое нечеткое соответствие похожего ключевого слова не может пройти через индекс?
Поскольку индексное дерево B+ хранится в порядке, соответствующем «значению индекса», его можно сравнивать только по префиксу.
Например, следующая карта вторичного индекса хранится в порядке поля имени.
Предположим, мы хотим запросить данные, поле имени которых имеет префикс «лес», т.е.name like '林%'
, процесс сканирования индекса:
- Сравнение запроса первого узла: размер пиньинь слова Lin больше, чем слово Chen в первом значении индекса первого узла, но меньше, чем слово Zhou во втором значении индекса первого узла, поэтому выберите переход к узлу. 2, чтобы продолжить запрос;
- Сравнение запроса узла 2: размер пиньинь слова Chen в первом значении индекса узла 2 меньше, чем размер слова леса, поэтому продолжайте смотреть на следующее значение индекса и найдите, что узел 2 имеет значение индекса, которое соответствует префикс слова леса, поэтому переходим к запросу leaf Node, то есть к leaf node 4;
- Сравнение запросов узла 4: префикс первого значения индекса узла 4 соответствует слову леса, поэтому строка данных считывается, а затем продолжает сопоставляться справа до тех пор, пока значение индекса с префиксом леса не совпадет.
При использованииname like '%林'
Поскольку результаты запроса могут быть «Чэнь Линь, Чжан Линь, Чжоу Линь» и т. д., поэтому я не знаю, с какого значения индекса начать, поэтому я могу выполнить запрос только путем полного сканирования таблицы.
Чтобы узнать больше о процессе запроса дерева InnoDB B+, вы можете прочитать эту статью, которую я написал:Что хранится в узлах дерева B+? Каков процесс запроса данных?
Используйте функции для индексов
Иногда мы будем использовать некоторые функции, которые поставляются с MySQL, чтобы получить желаемые результаты.В настоящее время мы должны обратить внимание.Если функция используется в поле индекса в условии запроса, индекс будет недействительным.
Например, в условии запроса следующего оператора функция LENGTH используется для поля имени, а type=ALL в плане выполнения представляет полное сканирование таблицы:
// name 为二级索引
select * from t_user where length(name)=6;
Почему вы не можете использовать функцию для индекса, вы не можете использовать индекс?
Поскольку в индексе хранится исходное значение поля индекса, а не значение, вычисленное функцией, по индексу невозможно пройти.
Однако, начиная с MySQL 8.0, функция индекса добавила индекс функции, то есть индекс может быть установлен для значения, вычисленного функцией, то есть значением индекса является значение, вычисленное функцией, поэтому данные могут быть запрошены путем сканирования индекса.
Например, я использую следующий оператор для создания индекса с именем idx_name_length в результате вычисления длины (имени).
alter table t_user add key idx_name_length ((length(name)));
Затем я использую следующий оператор запроса, и в это время будет взят индекс.
Вычислить выражение по индексу
Также невозможно использовать индекс для выполнения вычисления выражения по индексу в условии запроса.
Например, в следующем операторе запроса тип = ALL в плане выполнения указывает, что данные запрашиваются путем полного сканирования таблицы:
explain select * from t_user where id + 1 = 10;
Однако если условие оператора запроса изменить на где id = 10 - 1, то вычисление выражения не будет выполняться в поле индекса, поэтому запрос индекса может быть выполнен.
Почему индекс нельзя использовать для выражения выражения индекса?
Причина аналогична использованию функций для индексов.
Поскольку индекс сохраняет исходное значение поля индекса, а не значение, рассчитанное выражением id + 1, индекс нельзя использовать, а можно только вынуть значение поля индекса, а затем по очереди вычисляется выражение Условное суждение, поэтому используется полное сканирование таблицы.
Некоторые учащиеся могут сказать, что такой простой расчет выражения для индекса должен уметь выполнять сканирование индекса при специальной обработке кода, например, id + 1 = 10 становится id = 10 - 1.
Да, это можно реализовать, но MySQL все равно ворует эту лень и не реализует.
Я думаю, что, может быть, также потому, что существуют различные ситуации для вычисления выражения, и если каждую из них необходимо учитывать, код может быть очень раздутым, поэтому просто сообщите программисту о таком сценарии отказа индекса, и пусть программист позаботится об этом. что Не выполнять вычисление выражения для индексов в условиях запроса.
Неявное преобразование типов для индексов
Если поле индекса имеет строковый тип, но в условном запросе входной параметр имеет целочисленный тип, вы обнаружите в результате плана выполнения, что этот оператор выполнит полное сканирование таблицы.
Я добавил поле телефона в исходную таблицу t_user, которая является вторичным индексом и имеет тип varchar.
Затем я использую целые числа в качестве входных параметров в условном запросе.В это время в плане выполнения введите = ALL, поэтому данные запрашиваются через полное сканирование таблицы.
select * from t_user where phone = 1300000001;
Однако если поле индекса имеет целочисленный тип, даже если входной параметр в условии запроса является строкой, это не приведет к сбою индекса, и сканирование индекса все равно будет выполнено.
Давайте посмотрим на второй пример, id является целым числом, но следующий оператор все еще проходит сканирование индекса.
explain select * from t_user where id = '1';
Почему первый пример делает индекс недействительным, а второй нет?
Чтобы понять эту причину, сначала нам нужно узнать, каковы правила преобразования типов данных MySQL? Это просто зависит от того, преобразовывает ли MySQL строки в числа для обработки или преобразует числа в строки для обработки.
Когда я читал «Когда mysql45 Talks», я увидел простой тестовый метод, который должен узнать, каковы правила преобразования типов данных MySQL, через результат выбора «10»> 9:
- Если правило состоит в том, что MySQL автоматически преобразует «строку» в «число», это эквивалентно выбору 10> 9, это сравнение чисел, поэтому результат должен быть 1;
- Если правило состоит в том, что MySQL автоматически преобразует «числа» в «строки», это эквивалентно выбору «10»> «9», это сравнение строк, и размер сравнения строк сравнивается побитно от старшего к младшему. (нажмите код ascii), тогда строка «10» эквивалентна комбинации символов «1» и «0», поэтому сначала сравните символ «1» с символом «9», потому что символ «1» меньше чем символ «9», поэтому результат должен быть 0.
В MySQL результат выполнения выглядит следующим образом:
Вышеприведенный результат равен 1, что указывает на то, чтоКогда MySQL встречает сравнение строки и числа, он автоматически преобразует строку в число, а затем сравнивает.
Оператор запроса в предыдущем примере 1, я также сказал вам, что он выполнит полное сканирование таблицы:
//例子一的查询语句
select * from t_user where phone = 1300000001;
Это связано с тем, что поле телефона является строкой, поэтому MySQL автоматически преобразует строку в число, поэтому этот оператор эквивалентен следующему:
select * from t_user where CAST(phone AS signed int) = 1300000001;
можно увидеть,Функция CAST воздействует на поле phone, а поле phone является индексом, то есть функция используется на индексе! Как мы уже говорили ранее, использование функции для индекса приведет к сбою индекса..
Оператор запроса в примере 2, я сказал вам, что он будет проходить сканирование индекса:
//例子二的查询语句
select * from t_user where id = "1";
В настоящее время, поскольку строковая часть является входным параметром, необходимо преобразовать строку в число, поэтому этот оператор эквивалентен следующему:
select * from t_user where id = CAST("1" AS signed int);
Видно, что поле индекса не использует никакой функции, функция CAST используется для входного параметра, поэтому он может пройти сканирование индекса.
союзный индекс не крайнее левое совпадение
Индекс, построенный на основе поля первичного ключа, называется кластерным индексом, а индекс, построенный на общем поле, называется вторичным индексом.
ТакИндекс, созданный путем объединения нескольких общих полей, называется объединенным индексом., также называемый составным индексом.
При создании объединенного индекса нам нужно обратить внимание на порядок создания, потому что объединенный индекс (a, b, c) и (c, b, a) при использовании будет отличаться.
Чтобы иметь возможность правильно использовать индекс сустава, он должен следоватьКрайний левый принцип соответствия, то есть сопоставление индексов выполняется в порядке слева.
Например, если вы создаете(a, b, c)
Совместный индекс, если условия запроса следующие, вы можете сопоставить совместный индекс:
- где а=1;
- где а=1 и b=2 и с=3;
- где а=1 и b=2;
Обратите внимание, что из-за оптимизатора запросов порядок полей a в предложении where не имеет значения.
Однако, если условия запроса следующие, из-за того, что крайний левый принцип сопоставления не выполняется, совместный индекс не может быть сопоставлен, и совместный индекс будет недействительным:
- где б=2;
- где с=3;
- где b=2 и c=3;
Существует специальное условие запроса: где a = 1 и c = 3, соответствует ли оно крайнему левому совпадению?
На самом деле это усечение индекса в строгом смысле, и разные версии обрабатывают его по-разному.
В MySQL 5.5 в индекс пойдет первый a.После того, как значение первичного ключа будет найдено в объединенном индексе, он начнет возвращаться в таблицу, читать строку данных из индекса первичного ключа, а затем сравнивать значение поле с.
Начиная с MySQL 5.6, существуетвыталкивание индекса, вы можете делать выводы о полях, содержащихся в индексе, в процессе обхода индекса и напрямую отфильтровывать записи, которые не соответствуют условиям, уменьшая количество возвратов к таблице.
Общий принцип таков: усеченное поле будет передано на уровень механизма хранения для условной оценки (поскольку значение поля c находится в(a, b, c)
в объединенном индексе), а затем отфильтровать данные, соответствующие условиям, и вернуть их на серверный уровень. Поскольку большой объем данных отфильтровывается на уровне механизма, нет необходимости считывать данные из таблицы, чтобы делать выводы, что сокращает количество обращений к таблице и тем самым повышает производительность.
Например, в следующем операторе where a = 1 и c = 0 мы можем использовать функцию проталкивания индекса из условия Extra=Using index в плане выполнения.
Почему совместный индекс не соответствует принципу самого левого сопоставления?
Причина в том, что в случае совместного индекса данные сортируются по первому столбцу индекса, а второй столбец будет отсортирован только тогда, когда данные в первом столбце совпадают.
То есть, если мы хотим использовать как можно больше столбцов в объединенном индексе, каждый столбец в условии запроса должен быть столбцом, последовательным с крайнего левого в объединенном индексе. Если мы будем искать только по второму столбцу, мы точно не сможем пройтись по индексу.
ИЛИ в предложении WHERE
В предложении WHERE, если условный столбец перед OR является индексированным столбцом, а условный столбец после OR не является индексированным столбцом, индексирование завершится ошибкой.
Например, в следующем операторе запроса id – это первичный ключ, а age – общий столбец. В результате выполнения плана получается полное сканирование таблицы.
select * from t_user where id = 1 or age = 18;
Это связано с тем, что значение ИЛИ состоит в том, что может быть выполнено только одно из двух, поэтому бессмысленно, чтобы только один условный столбец был столбцом индекса.Пока условный столбец не является столбцом индекса, полное сканирование таблицы будет выполненный.
Решение простое, просто установите поле age в качестве индекса.
Вы можете видеть, что слияние type=index, слияние индексов означает, что идентификатор и возраст сканируются отдельно, а затем два набора результатов объединяются Преимущество этого заключается в том, чтобы избежать полного сканирования таблицы.
Суммировать
Сегодня я представлю 6 ситуаций, в которых произойдет сбой индекса:
- Когда мы используем левое или левонечеткое сопоставление, то есть
like %xx
илиlike %xx%
Оба эти метода приведут к сбою индекса; - Когда мы используем функцию для столбца индекса в условии запроса, это приведет к сбою индекса.
- Когда мы выполняем вычисление выражения для столбца индекса в условии запроса, индекс нельзя использовать.
- Когда MySQL сталкивается со сравнением строки и числа, он автоматически преобразует строку в число, а затем выполняет сравнение. Если строка является столбцом индекса, а входным параметром в условном операторе является число, то столбец индекса будет подвергнут неявному преобразованию типа.Поскольку неявное преобразование типа реализовано через функцию CAST, это эквивалентно использованию функции для индексный столбец, что приведет к сбою индекса.
- Чтобы можно было правильно использовать объединенный индекс, он должен следовать принципу самого левого сопоставления, то есть сопоставление индекса выполняется самым левым первым способом, иначе индекс будет недействительным.
- В предложении WHERE, если условный столбец перед OR является индексированным столбцом, а условный столбец после OR не является индексированным столбцом, индексирование завершится ошибкой.