фон

Являетесь ли вы гуру технологий,Все еще новичок в отрасли,Я наступаю на него время от времениMysqlбаза данныхНе принимайте на себя опасности индексации。Распространенным явлением является：Очевидно, к полю добавляется индекс,Но это не подействовало.

Несколько дней назад я столкнулся с немного особенным сценарием. Тот же оператор SQL вступил в силу при определенных параметрах, но не при определенных параметрах. Почему это так?

Кроме того, будь то собеседование или повседневная жизнь, вы должны понимать и изучать распространенные ситуации сбоя индекса Mysql.

Чтобы облегчить обучение и запоминание, в этом документе обобщаются 15 распространенных ситуаций несоблюдения указателя и показаны их на примерах, чтобы помочь каждому лучше избегать ошибок. Рекомендуется сохранить его на случай чрезвычайной ситуации.

Подготовка базы данных и индекса

Создать структуру таблицы

Чтобы проверить использование индекса поэлементно, мы сначала подготавливаем таблицу t_user:

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT «Идентификационный номер»,
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'имя пользователя',
  `age` int(11) DEFAULT NULL COMMENT 'возраст',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT «Время создания»,
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

В приведенной выше структуре таблицы есть три индекса:

• id：длябаза данныхпервичный ключ；
• union_idx：дляid_no、username、Совместный индекс, состоящий из возраста;
• create_time_idx：сделан изcreate_timeОбычный индекс, состоящий из；

данные инициализации

Данные инициализации разделены на две части: базовые данные и данные пакетного импорта.

В базовые данные вставляются 4 фрагмента данных, из которых время создания 4-го фрагмента данных находится в будущем и используется для последующей проверки особых сценариев:

INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

Помимо основных данных, существует также хранимая процедура и вызываемый ею SQL, который облегчает пакетную вставку данных и используется для проверки сценариев с большим количеством данных:

-- Удалить исторические хранимые процедуры
DROP PROCEDURE IF EXISTS `insert_t_user`

-- Создать хранимую процедуру
delimiter $

CREATE PROCEDURE insert_t_user(IN limit_num int)
BEGIN
	DECLARE i INT DEFAULT 10;
    DECLARE id_no varchar(18) ;
    DECLARE username varchar(32) ;
    DECLARE age TINYINT DEFAULT 1;
    WHILE i < limit_num DO
        SET id_no = CONCAT("NO", i);
        SET username = CONCAT("Tom",i);
        SET age = FLOOR(10 + RAND()*2);
        INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
        SET i = i + 1;
    END WHILE;

END $
-- Вызов хранимой процедуры
call insert_t_user(100);

Что касается создания и хранения хранимых процедур, то вы не можете выполнять их временно и выполнять по мере их использования.

Версия базы данных и план выполнения

Проверьте текущую версию базы данных:

select version();
8.0.18

Вышеупомянутое - это то, что я проверял. данных Версия：8.0.18。конечно,Все примеры ниже,Вы можете выполнить верификацию в другой версии.

Просмотр плана выполнения оператора SQL,Обычно мы используемexplainКлючевые слова,Судите об использовании индекса по результатам выполнения.

Пример выполнения:

explain select * from t_user where id = 1;

Результат выполнения:

Вы можете видеть, что приведенный выше оператор SQL использует индекс первичного ключа (PRIMARY).,key_lenдля4；

вkey_lenиззначениедля：Указывает количество байтов, используемых индексом.,На основании этого значения можно судить об использовании индекса.,Особенно при объединении индексов,Важно определить, какая часть индекса используется.

Подготовив приведенные выше данные и знания, давайте начнем объяснять конкретные примеры сбоя индекса.

1 Совместный индекс не удовлетворяет принципу крайнего левого сопоставления

Совместный индекс соответствует принципу крайнего левого соответствия.,Как следует из названия,В объединенном индексе сначала сопоставляется самое левое поле.。поэтому,При создании совместного индекса,Наиболее часто используемые поля в предложенииwhere располагаются в крайней левой части объединенного индекса.

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

В примере,union_idxСостав совместного индекса：

KEY `union_idx` (`id_no`,`username`,`age`)

Крайнее левое поле — id_no. В обычных обстоятельствах, пока id_no присутствует в условиях запроса, будет использоваться объединенный индекс.

Пример 1：

explain select * from t_user where id_no = '1002';

объяснить результаты:

Это видно из результатов выполнения объяснения.,вышеSQLПредложение исчезлоunion_idxЭтот индекс。

Вот общее введение в расчет key_len:

• id_no Тип — varchar(18), а набор символов — utf8mb4_bin, что означает, что для представления полного UTF-8 используются 4 байта. В это время key_len = 18* 4 = 72；
• Поскольку тип поля varchar является типом данных переменной длины, необходимо добавить 2 дополнительных байта. В это время key_len = 72 + 2 = 74;
• Поскольку это поле имеет значение NULL (по умолчанию NULL), необходимо добавить еще 1 байт. В это время key_len = 74 + 1 = 75;

Выше приведен процесс расчета key_len в одном случае. В дальнейшем мы не будем выполнять вычисление по одному. Достаточно знать базовую структуру и принципы. Остальные случаи можно проверить самостоятельно.

Пример 2：

explain select * from t_user where id_no = '1002' and username = 'Tom2';

объяснить результаты:

очевидно,Все еще ушелunion_idxиндекс,Согласно анализу key_len выше,Сделайте дикое предположение,При использовании индекса,не только используетсяid_noСписок,Также используетсяusernameСписок。

Пример третий：

explain select * from t_user where id_no = '1002' and age = 12;

объяснить результаты:

Ушелunion_idxиндекс,Но то же, что и Пример 1,Только используетсяid_noСписок。

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

Обратный пример：

explain select * from t_user where username = 'Tom2' and age = 12;

объяснить результаты:

На данный момент вы можете видеть, что индекс не используется, а это означает, что индекс недействителен.

Аналогично, пока комбинация крайних левых условий не появится ниже, индекс также будет недействителен:

explain select * from t_user where age = 12;
explain select * from t_user where username = 'Tom2';

Так,Первый вид ошибки индекса Сцена：В сценарии объединенного индекса условия запроса не удовлетворяют принципу крайнего левого сопоставления.。

2 использования выберите *

существовать《Руководство по разработке Alibaba》изORMкартографированиеВ разделе есть статья【сила】изспецификация：

[Обязательно] В табличных запросах не используйте * в качестве списка полей запроса. Необходимо четко указать, какие поля являются обязательными. Описание: 1) Увеличение стоимости парсинга анализатора запросов. 2) Добавление или удаление полей может легко противоречить конфигурации resultMap. 3) Бесполезные поля увеличивают потребление сети, особенно поля текстового типа.

Хотя в руководстве по спецификации нет упоминания о проблемах индексации.,Но запрещено использоватьselect * заявления могут принестииз Побочная выгода – это：В некоторых случаях можно пойтииндекс покрытия。

например,В индексе союза выше,Если условием запроса является возраст или имя пользователя,При использованииselect * , он точно не будет проиндексирован.

Но если вы хотите запросить три результата: id_no, имя пользователя и возраст на основе имени пользователя (все поля индекса),Укажите поля результатов запроса,Да, мы можем пойтииндекс покрытияиз：

explain select id_no, username, age from t_user where username = 'Tom2';
explain select id_no, username, age from t_user where age = 12;

объяснить результаты:

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

Второй вид Ошибка индексасцена：В объединенном индексе попробуйте использовать явные столбцы запроса, чтобы охватить индексы.；

Эта ситуация неиспользования индекса является элементом оптимизации. Если бизнес-сценарий выполняется, оператору SQL будет предложено использовать индекс. Что касается спецификации в руководстве по разработке Alibaba, то они просто столкнулись. Сама спецификация не была определена для этого правила индексации.

В операции участвуют 3 столбца индекса.

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

explain select * from t_user where id + 1 = 2 ;

объяснить результаты:

Видно, что даже если столбец id имеет индекс, его нельзя нормально проиндексировать из-за обработки вычислений.

В данном случае это не только проблема с индексом, но и увеличивает вычислительную нагрузку базы данных. Взяв в качестве примера приведенный выше оператор SQL, базе данных необходимо просканировать всю таблицу, чтобы найти все значения поля id, затем вычислить их, а затем сравнить со значениями параметров. Если каждое выполнение проходит через описанные выше шаги, можно представить себе потерю производительности.

Рекомендуемое использование: сначала вычислите ожидаемое значение в памяти или вычислите значение параметра в правой части условия оператора SQL.

Оптимизация для приведенного выше примера будет следующей:

-- Расчет памяти, известно, что запрашиваемый идентификатор равен 1
explain select * from t_user where id = 1 ;
-- Расчет параметров
explain select * from t_user where id = 2 - 1 ;

Третий вид Ошибка индекса Состояние：В операции участвует индексный столбец, что приведет к полному сканированию таблицы и сбою индекса.。

4 Параметры столбца индекса используют функции

Пример:

explain select * from t_user where SUBSTR(id_no,1,3) = '100';

объяснить результаты:

В приведенном выше примере столбец индекса использует функцию (SUBSTR, перехват строки), в результате чего индекс становится недействительным.

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

В примере указана только функция SUBSTR. Подобные ситуации могут возникнуть и с аналогичными функциями, такими как CONCAT. Решение может относиться к третьему сценарию, и вы можете рассмотреть возможность сокращения базы данных за счет вычислений в памяти или других методов обработки содержимого.

Четвертый вид Ошибка индекса Состояние：Столбцы индекса участвуют в обработке функций, что приведет к полному сканированию таблицы и сбою индекса.。

5 Неправильное использование лайка

Пример:

explain select * from t_user where id_no like '%00%';

объяснить результаты:

противlikeизиспользоватьочень часто,Но неправильное использование часто приводит к отсутствию индексации. Общие способы использования Like включают в себя:

• Способ 1: например «%abc»;
• Способ 2: например, «abc%»;
• Способ 3: например «%abc%»;

В первом и третьем методах индекс использовать нельзя, поскольку в заголовке появляется заполнитель. Причину отсутствия индексации в этом случае легко понять. Сам индекс эквивалентен каталогу, отсортированному по одному слева направо. Заполнитель используется в левой части условия, что делает невозможным сопоставление с обычным каталогом, и сбой индекса является нормальным явлением.

Пятый тип Ошибка индекса Состояние：Во время нечеткого запроса (например, оператора) заполнитель нечеткого соответствия расположен в начале условия.。

6 Неявное преобразование типов

Пример:

explain select * from t_user where id_no = 1002;

объяснить результаты:

id_noТип полядляvarchar,Но тип int используется в инструкции SQL,Вызывает полное сканирование таблицы.

Причина сбоя индекса заключается в том, что varchar и int — это два разных типа.

Решение — изменить параметры1002Добавьте одинарные или двойные кавычки。

Шестой вид Ошибка индекса Состояние：Тип параметра не соответствует типу поля, что приводит к неявному преобразованию типа и сбою индекса.。

Это особый случай этой ситуации. Если тип поля имеет тип int и условие запроса добавляет одинарные или двойные кавычки, Mysql преобразует параметры в тип int, хотя используются одинарные или двойные кавычки:

explain select * from t_user where id = '2';

Приведенное выше заявление по-прежнему будет индексироваться.

7. Используйте операцию ИЛИ

OR — наиболее часто используемое ключевое слово операции в повседневной жизни, но неправильное его использование также может привести к сбою индекса.

Пример:

explain select * from t_user where id = 2 or username = 'Tom2';

объяснить результаты:

Вы удивлены, увидев приведенные выше результаты выполнения?,Очевидно, поле id индексируется,из-за использованияorКлючевые слова,Индекс фактически провалился.

на самом деле,Подумайте об этом под другим углом,Если использовать отдельноusernameполевые работыдлясостояниеочевидно Это полное сканирование таблицы,Теперь, когда полное сканирование таблицы выполнено,,Переднийidизсостояние再走一次индекс Наоборот, оно потрачено впустую.。так,При использовании ключевого слова или,Не забудьте добавить индексы к обоим условиям.,В противном случае индекс станет недействительным.

Но еслиorИспользуйте обе стороны одновременно“>”и“<”,Тогда индекс также потерпит неудачу:

explain select * from t_user where id  > 1 or id  < 80;

объяснить результаты:

седьмой вид Ошибка индекса Состояние：В условии запроса используется ключевое слово или, и если одно из полей не проиндексировано, индекс всего оператора запроса будет недействительным; orобе стороныдля“>”и“<”При запросе диапазона,Ошибка индекса。

8 Сравните два столбца

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

Вот неподходящий пример, например, два столбца, где возраст меньше id (реальный сценарий может заключаться в сравнении данных в двух столбцах одного и того же измерения, поэтому здесь учитывается существующая структура таблицы):

explain select * from t_user where id > age;

объяснить результаты:

Хотя существует индекс для id и age, он также может создать индекс, индекс все равно будет недействительным при сравнении двух столбцов.

Восьмой вид Ошибка индекса Состояние：При сравнении двух столбцов данных, даже если для обоих столбцов созданы индексы, индекс будет недействительным.。

9 не равно сравнению

Пример:

explain select * from t_user where id_no <> '1002';

объяснить результаты:

Когда условие запроса является строкой,использовать”<>“или”!=“делатьдлясостояние查询,Индекс можно не использовать,Но не совсем.

explain select * from t_user where create_time != '2022-02-27 09:56:42';

В приведенном выше SQL, поскольку «2022-02-27 09:56:42» генерируется хранимой процедурой в ту же секунду, большой объем данных относится к этому времени. После выполнения вы обнаружите, что если доля набора результатов запроса относительно мала, индекс будет использоваться, а когда доля относительно велика, индекс не будет использоваться. Это связано с долей набора результатов в общем объеме.

нужно вниманиеизда：выше语句如果даidвыполнять операции ожидания,Тогда индексируйте нормально.

explain select * from t_user where id != 2;

объяснить результаты:

Девятый вид Ошибка индекса Состояние：При использовании неравных сравнений в условиях запроса необходимо соблюдать осторожность. Обычные индексы не будут работать, если набор результатов запроса занимает большую часть.。

10 is not null

Пример:

explain select * from t_user where id_no is not null;

объяснить результаты:

Десятый тип Ошибка индекса Состояние：Используемое условие запроса: Если значение равно нулю, обычно используется индекс. not Если значение равно нулю, индекс не используется.。

11 нет в не существует

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

explain select * from t_user where id in (2,3);

explain select * from t_user where id_no in ('1001','1002');

explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain select * from t_user where id_no between '1002' and '1003';

Вышеупомянутые четыре оператора будут индексироваться обычным образом при выполнении, а конкретные результаты объяснения не будут отображаться. Главное, чего вы не видите, это индекс:

explain select * from t_user where id_no not in('1002' , '1003');

объяснить результаты:

когдаиспользоватьnot inчас,Не индексируете? Попробуйте изменить столбец условия на первичный ключ:

explain select * from t_user where id not in (2,3);

объяснить результаты:

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

Тип 11Ошибка индекса Состояние：Если в условии запроса используется not in, если это первичный ключ, будет использоваться индекс. Если это обычный индекс, индекс будет недействительным.。

Давайте посмотрим еще разnot exists：

explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

объяснить результаты:

когда查询состояниеиспользоватьnot existsчас,Никакой индексации.

Тип 12Ошибка индекса Состояние：Если в условии запроса используется «не существует», индекс становится недействительным.。

12 порядок по причинам сбоя индекса

Пример:

explain select * from t_user order by id_no ;

объяснить результаты:

На самом деле, сбой индекса в этом случае легко понять: ведь нужно отсортировать все данные таблицы.

Итак, приведет ли добавление или удаление ключевого слова limit к индексации?

explain select * from t_user order by id_no limit 10;

объяснить результаты:

Результат остается прежним Никакой индексации.существовать Я видел в Интернете, что еслиorder byсостояние满足最左匹配则会正常走индекс,Он не отображается в текущей версии 8.0.18. так,существоватьна основеorder byиlimitруководитьиспользоватьчас,Обратите особое внимание. Вопрос о том, использовать ли индекс, касается не только версии базы данных.,Это также зависит от того, как с этим справляется оптимизатор Mysql.

Вот еще частный случай,就дапервичный ключиспользоватьorder byчас,Вы можете индексировать нормально.

explain select * from t_user order by id desc;

объяснить результаты:

Видно, что для первичного ключа,все ещеorder byВы можете индексировать нормально.

Кроме того, автор протестировал следующие операторы SQL:

explain select id from t_user order by age;
explain select id , username from t_user order by age;
explain select id_no from t_user order by id_no;

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

сейчассуществовать Воляidиid_noКомбинированныйorder by：

explain select * from t_user order by id,id_no desc;
explain select * from t_user order by id,id_no desc limit 10;
explain select * from t_user order by id_no desc,username desc;

объяснить результаты:

Два приведенных выше оператора SQL не индексируются.

тринадцатый Ошибка индекса Состояние：Когда условие запроса включает порядок Когда используются такие условия, как by и limit, ситуация с использованием индекса более сложна и зависит от версии Mysql. Как правило, индекс является обычным. Если лимит не используется, индекс не будет использоваться. заказ При наличии нескольких полей индекса индекс использовать нельзя. В других случаях рекомендуется выполнить проверку expapin перед использованием.

13. Разные параметры приводят к сбою индекса

На этом этапе, если вы не выполнили изначально созданную хранимую процедуру, рекомендуется сначала выполнить хранимую процедуру, а затем выполнить следующий SQL:

explain select * from t_user where create_time > '2023-02-24 09:04:23';

Среди них есть время в будущем, чтобы гарантировать, что данные могут быть найдены.

объяснить результаты:

Как видите, индекс работает нормально.

Затем меняем параметры условия запроса на дату:

explain select * from t_user where create_time > '2022-02-27 09:04:23';

объяснить результаты:

На этом этапе выполняется полное сканирование таблицы. Это также странное явление, упомянутое в начале.

Почему для одного и того же оператора запроса различаются только значения параметров запроса, один будет использовать индекс, а другой не будет использовать индекс?

Ответ прост：Вышеупомянутый индекс не удался, поскольку СУБД обнаружила, что полное сканирование таблицы более эффективно, чем индексирование, и отказалась от индексации.。

Другими словами, когда MySQL обнаруживает, что количество записей строк, сканируемых через индекс, превышает 10–30% всей таблицы, оптимизатор может отказаться от индекса и автоматически перейти к полному сканированию таблицы. В некоторых сценариях, даже если оператор SQL принудительно проходит через индекс, он все равно будет недействителен.

Похожие вопросы,существоватьруководить范围查询（например>、< 、>=、<=、inусловие равенства）час往往会出сейчасвыше Состояние,Критические значения, упомянутые выше, будут различаться в зависимости от сценария.

Четырнадцатый Ошибка индекса Состояние：Когда условиями запроса являются запросы диапазона, такие как «больше или равно», «в» и т. д., в зависимости от доли результатов запроса во всей таблице данных, оптимизатор может отказаться от индекса и выполнить полное сканирование таблицы.

еще 14

Конечно, существуют и другие правила использования индексов, которые также связаны с тем, является ли индекс индексом B-дерева или индексом растрового изображения, поэтому я не буду вдаваться в подробности.

Что тут сказать другой,можно обобщитьдляпятнадцатый Ошибка индексаиз Состояние：Другие стратегии оптимизации оптимизатора Mysql Например, если оптимизатор считает, что в некоторых случаях полное сканирование таблицы происходит быстрее, чем индексирование, он откажется от индекса.

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

краткое содержание

В этой статье обобщены 15 распространенных сценариев сбоя индекса для всех. Из-за разных версий MySQL стратегии сбоя индекса также различаются. Большинство ситуаций сбоя индекса очевидны, а небольшое количество сбоев индекса будет варьироваться в зависимости от версии Mysql. Поэтому рекомендуется сохранить эту статью и сравнить ее во время практики. Если вы не можете понять ее точно, вы можете напрямую выполнить объяснение для проверки.