Давайте поговорим о 10 сценариях отказа индекса, это слишком жалко

предисловие

Статья, которую я написал ранееРасскажите о 15 советах по оптимизации sql", с момента публикации она получила широкую известность в Интернете и была перепечатана многими крупными шишками, что показывает ценность таких статей.

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

Я не знаю, сталкивались ли вы со следующими двумя ситуациями в своей реальной работе:

• Очевидно, что индекс добавляется к полю, но на самом деле он не действует.
• Индекс иногда работает, иногда нет.

Недавно мне случайно попалась заметка о чистке, написанная крупным производителем БАТ, и я сразу же открыла вторую линейку Рен и Ду.Я все больше чувствую, что алгоритм не так сложен, как я себе представляла.

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

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

1. Подготовка

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

Поэтому я решил построить таблицы и данные и показать вам эффект шаг за шагом, стараясь быть разумным и обоснованным.

Я верю, что если вы терпеливо прочитаете эту статью, вы обязательно многое приобретете.

1.1 Создайте пользовательскую таблицу

Создайте пользовательскую таблицу, содержащую:id,code,age,nameа такжеheightполе.

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Дополнительно создаются три индекса:

• id: первичный ключ базы данных
• idx_code_age_name: совместный индекс, состоящий из трех полей: код, возраст и имя.
• idx_height: нормальный индекс

1.2 Вставка данных

Для того, чтобы облегчить вам демонстрацию, я специально вставил в пользовательскую таблицу 3 куска данных:

INSERT INTO sue.user (id, code, age, name, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (3, '103', 23, '苏三', 174,'成都');

Стивен Чоу и Джей Чоу — мои кумиры, однажды я влюбился сюда и собрал их вместе с собой. Хахаха.

1.3 Просмотр версии базы данных

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

select version();

Узнайте текущий номер версии mysql:8.0.21

1.4 Просмотр плана выполнения

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

Например:

explain select * from user where id=1;

Результаты:Как видно из рисунка, поскольку поле id является первичным ключом, оператор sql использует主键索引.

Конечно, если вы хотите узнать больше об использовании ключевого слова объяснения, вы можете прочитать мою другую статью "объясните | Этот несравненный меч оптимизации индексов, вы действительно знаете, как им пользоваться?, который описан более подробно.

2. Не удовлетворяет принципу крайнего левого соответствия

Я уже построил три поля кода, возраста и имени раньше.联合索引:idx_code_age_name.

Порядок полей индекса:

• code
• age
• name

Если вы не будете обращать внимание на принцип крайнего левого префикса при использовании объединенного индекса, это может привести к сбою индекса.Если вы мне не верите, давайте посмотрим вниз.

2.1 Когда действительны индексы?

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

explain select * from user
where code='101';

explain select * from user
where code='101' and age=21 

explain select * from user
where code='101' and age=21 and name='周星驰';

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

На самом деле, есть более особенная сцена:

explain select * from user
where code = '101'  and name='周星驰';

Результаты:Исходный порядок условий запроса: код, возраст, имя, но между кодом и именем есть только пробел, а поле возраста опущено.В этом случае также можно использовать индекс в поле кода.

Увидев это, я удивляюсь, нашел ли ты, умный, такое правило:В этих 4 sql есть поле кода, которое является первым полем в поле индекса, которое является самым левым полем. Пока это поле существует, SQL уже может быть проиндексирован.

Это то, что мы называем最左匹配原则.

2.2 При каких обстоятельствах индекс не работает?

Я уже представил перед этим, после того, как объединенный индекс установлен, в каких случаях индекс действителен в условиях запроса.

Далее давайте сосредоточимся на ситуациях, в которых индекс не работает.

explain select * from user
where age=21;

explain select * from user
where name='周星驰';

explain select * from user
where age=21 and name='周星驰';

Результаты:Из рисунка видно, что в этих трех случаях индекс не работает.

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

3. Используйте выбор *

В «Руководстве по разработке Alibaba» четко указано, что использование запроса sql запрещено.select *.

Ну, знаешь почему?

Не много ерунды, по мировой практике первый sql:

explain 
select * from user where name='苏三';

Результаты:используется в этом sqlselect *, из результата выполнения выполняется полное сканирование таблицы, индекс не используется, а эффективность запроса очень низкая.

Если мы будем запрашивать только те столбцы, которые нам действительно нужны, а не все столбцы, что произойдет с результатом?

Очень быстро изменил приведенный выше sql, чтобы проверить только столбцы кода и имени, слишком просто:

explain 
select code,name from user 
where name='苏三';

Результаты:Из результатов выполнения на рисунке нетрудно увидеть, что на этот раз оператор sql исчез.全索引扫描,Сравнивать全表扫描более высокая эффективность.

На самом деле используется здесь:覆盖索引.

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

при использованииselect *При запросе данных всех столбцов велика вероятность того, что будут запрошены данные неиндексированных столбцов, а неиндексированные столбцы не будут проиндексированы, поэтому эффективность запроса очень низкая.

4. Есть вычисления по индексированным столбцам

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

explain select * from user where id=1;

Результаты:Как видно из рисунка, поскольку поле id является первичным ключом, оператор sql использует主键索引.

Но если в столбце id есть расчет, например:

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

Результаты:Из результатов выполнения на приведенном выше рисунке ясно видно, что индекс первичного ключа поля id недействителен при условии вычисления.

5. Индексный столбец использует функцию

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

Если сейчас есть требование: я хочу узнать всех людей, чей рост начинается с 17, если оператор SQL написан так:

explain select * from user  where height=17;

Оператор sql использует обычный индекс:Но должно быть что-то не так с оператором sql, потому что он может узнать только высоту, точно равную 17, но в случае 174 он не может узнать.

Чтобы выполнить вышеуказанные требования, нам нужно немного изменить оператор sql:

explain select * from user  where SUBSTR(height,1,2)=17;

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

Результаты:Обнаружили ли вы, что после использования этой функции оператор sql прошел полное сканирование таблицы, и индекс оказался неудачным.

6. Различные типы полей

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

Что происходит на самом деле?

Обратите внимание на поле кода в таблице t_user, оноvarcharтип персонажа.

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

explain 
select * from user where code="101";

Результаты:Как видно из рисунка выше, поле кода проиндексировано.

Напоминаем, что при запросе символьных полей используйте двойные кавычки.“и одинарные кавычки'Все будет хорошо.

Но если вы случайно сбросите кавычки при написании sql, оператор sql станет таким:

explain 
select * from user where code=101;

Результаты:Вы будете удивлены, обнаружив, что оператор sql превращается в полное сканирование таблицы. Эта небольшая ошибка сделала индекс поля кода недействительным из-за отсутствия кавычек.

В это время у вас может быть 10 000 причин, и одно из них должно быть: почему индекс терпит неудачу?

Ответ. Поскольку тип поля кода — varchar, а тип передаваемого параметра — int, эти два типа различаются.

Кроме того, есть интересное явление: если поле высоты типа int запрашивается с кавычками, оно все равно может быть проиндексировано:

explain select * from user 
where height='175';

Результаты:Из рисунка видно, что оператор sql действительно переходит в индекс. Параметры типа int можно индексировать независимо от того, заключены они в кавычки или нет.

Это магия? Это не научно.

Ответ: mysql считает, что еслиintКогда поле типа используется в качестве условия запроса, оно автоматически обрабатывает параметры поля.隐式转换, преобразовать строку в тип int.

MySQL преобразует строку 175 в приведенном выше столбце в число 175, поэтому он все еще может пройти через индекс.

Далее посмотрите на более интересное выражение sql:

select 1 + '1';

Является ли его результат выполнения 2 или 11?

Что ж, не будем продавать сомненья, а прямо огласим ответ и результат выполнения 2.

MySQL автоматически преобразует строку 1 в 1 типа int, а затем становится: 1+1=2.

Но что, если вы хотите объединить строки?

Ответ: можно использоватьconcatключевые слова.

Конкретный SQL для сплайсинга выглядит следующим образом:

select concat(1,'1');

Далее следует ключевой вопрос:Почему происходит сбой индекса поля строкового типа при передаче параметра типа int?

Ответ: Согласно объяснению на официальном сайте mysql, строки '1', '1' и '1a' могут быть преобразованы в 1 типа int, что означает, что может быть несколько строк, соответствующих параметру введите внутр. Итак, как mysql узнает, в какую строку преобразовать 1 типа int и какой индекс использовать для быстрого поиска значения?

Заинтересованные друзья могут взглянуть на официальную документацию:https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

7. Левая часть лайка содержит %

Нечеткий запрос, в нашей повседневной работе частота использования все еще относительно высока.

Например, сейчас есть потребность: хочу узнать, кто одноклассники по фамилии Ли?

использоватьlikeОператоры могут быть реализованы очень быстро:

select * from user where name like '李%';

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

Если вы мне не верите, давайте посмотрим вниз.

В настоящее время существует три основных случая подобного запроса:

• like '%a'
• like 'a%'
• like '%a%'

Если сейчас есть требование: я хочу узнать всех пользователей, чей код начинается с 10.

Это требование слишком простое, оператор sql выглядит следующим образом:

explain select * from user
where code like '10%';

Результаты:На рисунке показано это%существует10Прямо при ходьбе index.

И если требования изменились: я хочу показать всех пользователей, чей код заканчивается на 1.

Оператор запроса sql изменен на:

explain select * from user
where code like '%1';

Результаты:Из рисунка видно, что это%существует1Слева индекс в поле кода недействителен, и sql становится полным сканированием таблицы.

Кроме того, если появляется следующий sql:

explain select * from user
where code like '%1%';

Индекс оператора sql также будет недействительным.

Вот краткое изложение правил в одном предложении:likeв предложении%, когда он появится в левой части условия запроса, индекс будет недействительным.

Итак, почему происходит это явление?

О: На самом деле это легко понять: индекс — это как директория в словаре. Общий каталог отсортирован по алфавиту или пиньинь от мала до велика, слева направо, есть порядок.

Когда мы просматриваем каталог, мы обычно сначала находим первую букву слева, если она совпадает, затем находим вторую букву слева, если она совпадает, находим другие буквы и так далее.

Таким образом, мы можем быстро заблокировать конкретный каталог или сузить область каталога.

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

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

Вышеупомянутое содержание является общим требованием. Теперь давайте возьмем что-то другое.

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

Это требование очень простое, sql можно написать так:

explain select * from user 
where id=height

Результаты:Вы удивлены, вы удивлены? Индекс недействителен.

Почему возникает такой результат?

Само поле id имеет индекс первичного ключа, а поле height тоже имеет общий индекс, и оба поля имеют тип int, и типы одинаковы.

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

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

9. Используйте ключевое слово или

Когда мы обычно пишем запрос sql, мы используемorСуществует множество сценариев для ключевых слов, но если вы не будете осторожны, существующий индекс может стать недействительным.

Если вы мне не верите, посмотрите ниже.

Однажды вы сталкиваетесь с таким требованием: вы хотите проверить пользователя с id=1 или height=175.

Вы пишете sql, разделив два на три:

explain select * from user 
where id=1 or height='175';

Результаты:Верно, на этот раз индекса действительно нет, поздравляем с ошибкой, потому что поля id и height были проиндексированы.

Но на следующую ночь, когда ночь была темной и ветреной, требование изменилось: в дополнение к предыдущим условиям запроса я также хотел добавить address='Chengdu'.

Это не просто, sql запускается:

explain select * from user 
where id=1 or height='175' or address='成都';

Результаты:Результат трагичен, предыдущий индекс недействителен.

Вы можете быть сбиты с толку, почему? что я сделал?

Ответ: Поскольку последнее добавленное поле адреса не индексируется, индексы других полей недействительны.

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

10. нет и не существует

Он также больше используется в нашей повседневной работе, а также в запросах диапазона.Обычные из них:

• in
• exists
• not in
• not exists
• between and

Сегодня мы сосредоточимся на первых четырех.

10.1 в ключевом слове

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

explain select * from user
where height in (173,174,175,176);

Результаты:Как видно из рисунка, оператор sql используетinКлючевые слова индексируются.

10.2 существует ключевое слово

Иногда используетсяinЭффективность ключевого слова низкая, и его можно использовать в настоящее время.existsКлючевое слово было оптимизировано для sql, и оно дает тот же эффект, что и ключевое слово in:

explain select * from user  t1
where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

Результаты:Как видно из рисунка, сexistsКлючевые слова также попадают в индекс.

10.3 Ключевое слово not in

Два примера, продемонстрированные выше, являются положительными диапазонами, т.е. в пределах определенных диапазонов.

Тогда через индекс может идти обратный диапазон, то есть не в определенных диапазонах?

Без лишних слов, давайте посмотрим, как его использоватьnot inСлучай:

explain select * from user
where height not in (173,174,175,176);

Результаты:Вы правильно прочитали, индекс недействителен.

Давайте посмотрим, изменились ли требования сейчас: я хочу проверить пользователей, чьи идентификаторы не равны 1, 2 или 3. В настоящее время оператор sql может быть изменен на это:

explain select * from user
where id  not in (173,174,175,176);

Результаты:Вы можете быть удивлены, обнаружив, что, используя ключевое слово not in в поле первичного ключа для запроса диапазона данных, вы по-прежнему можете использовать индекс. Для обычных полей индекса, если ключевое слово not in используется для запроса диапазона данных, индекс будет недействительным.

10.4 ключевое слово не существует

В дополнение к этому, если оператор sql используетnot exists, индекс также не будет выполнен. Конкретный оператор sql выглядит следующим образом:

explain select * from user  t1
where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

Результаты:Из рисунка видно, что после использования ключа not exists в операторе sql таблица t1 проходит полное сканирование таблицы и не проходит через индекс.

11. Яма порядка

В операторах SQL сортировка результатов запроса является очень распространенным требованием, обычно мы используем ключевые слова:order byсделает это.

Но я всегда чувствую, что порядок по очень сложен в использовании, он похож наwhereилиlimitКлючевые слова неразрывно связаны между собой, и если вы не будете осторожны, могут возникнуть проблемы.

Let go

11.1 В каких случаях следует использовать индекс?

Прежде всего, конечно, будьте нежны, давайте посмотрим, какие случаи order by можно проиндексировать.

Как я уже говорил, объединенный индекс был построен на трех полях кода, возраста и имени: idx_code_age_name.

11.1.1 Удовлетворение принципу крайнего левого соответствия

Условия, лежащие в основе порядка, также следуют принципу крайнего левого соответствия объединенного индекса. В частности, следующий sql:

explain select * from user
order by code limit 100;

explain select * from user
order by code,age limit 100;

explain select * from user
order by code,age,name limit 100;

Результаты:Из рисунка видно, что эти три sql могут нормально проходить индекс.

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

11.1.2 Работа с местом

order by также может следовать принципу крайнего левого сопоставления вместе с where.

explain select * from user
where code='101'
order by age;

Результаты:code — это первое поле совместного индекса, которое используется в where, а age — второе поле совместного индекса, которое затем используется по порядку.

Если в середине произойдет ошибка и оператор sql станет таким, каков будет результат выполнения?

explain select * from user
where code='101'
order by name;

Результаты:Хотя имя является третьим полем объединенного индекса, в соответствии с принципом самого левого сопоставления оператор sql все еще может проходить через индекс, поскольку в where используется код первого крайнего слева поля. Просто при заказе по эффективность сортировки относительно низкая, и зайти нужно один разfilesortПросто сортировать.

11.1.3 Тот же порядок

Если порядок содержит несколько полей сортировки объединенного индекса, если их правила сортировки одинаковы (по возрастанию или по убыванию одновременно), индекс также может использоваться.

Конкретный sql выглядит следующим образом:

explain select * from user
order by code desc,age desc limit 100;

Результаты:В этом примере поля code и age после order by расположены в порядке убывания, поэтому индекс все равно используется.

11.1.4 Оба

Если совместное поле индекса существует и в том, где и в порядке, каков будет результат?

explain select * from user
where code='101'
order by code, name;

Результаты:В поле code есть и where, и order by, в этом случае из результатов на рисунке видно, что индекс все еще можно использовать.

11.2 При каких обстоятельствах не идти на индекс?

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

Ну а дальше давайте сосредоточимся на том, по каким обстоятельствам нельзя использовать индекс по порядку?

11.2.1 Нет, где или ограничение добавлено

Если ключевое слово where или limit не добавлено к оператору order by, оператор sql не будет проходить через индекс.

explain select * from user
order by code, name;

Результаты:Из рисунка видно, что индекс действительно недействителен.

11.2.2 Сортировка по разным индексам

Вышеупомянутые в основном совместные индексы, это случай этого индекса. Но что, если сортировка выполняется по нескольким индексам?

explain select * from user
order by code, height limit 100;

Результаты:Из рисунка видно, что индекс также недействителен.

11.2.3 Не удовлетворяет принципу крайнего левого соответствия

Как упоминалось ранее, если order by удовлетворяет принципу крайнего левого сопоставления, он все равно будет использовать индекс. Давайте рассмотрим случай, когда самый левый принцип соответствия не выполняется:

explain select * from user
order by name limit 100;

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

11.2.4 Различные виды

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

Но что, если у них разные сопоставления?

explain select * from user
order by code asc,age desc limit 100;

Результаты:Как видно из рисунка, несмотря на то, что поля кода и возраста в порядке следования следуют принципу сопоставления крайнего левого поля, поскольку одно поле находится в порядке возрастания, а другое поле — в порядке убывания, индекс в конечном итоге станет недействительным.

Недавно мне случайно попалась заметка о чистке, написанная крупным производителем БАТ, и я сразу же открыла вторую линейку Рен и Ду.Я все больше чувствую, что алгоритм не так сложен, как я себе представляла.

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

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