предисловие
Я видел много подобных статей, в которых упоминалось, что использование некоторых операторов SQL может привести к сбою индекса mysql. Существуют также некоторые «военные правила» или стандарты mysql, согласно которым некоторые SQL не могут быть записаны, иначе индекс будет недействительным.
Большая часть контента одобрена автором, но некоторые примеры слишком абсолютны, и нет никакого оригинального происхождения, многие люди не знают, почему. Поэтому автор обязательно упорядочит распространенные сцены сбоя индекса в MySQL и проанализирует исходную ссылку.
Конечно помните,объяснять - хорошая привычка!
Распространенные сценарии сбоя индекса MySQL
При проверке следующих сценариев подготовьте достаточный объем данных, потому что, когда объем данных мал, оптимизатор MySQL иногда решит, что полное сканирование таблицы безвредно и не повлияет на индекс.
1. Когда оператор where содержит или, это может привести к сбою индекса
Использование или не обязательно делает индекс недействительным, вам нужно увидеть, соответствуют ли столбцы запроса слева и справа от одного и того же индекса.
Предположим, что столбец user_id в таблице USER имеет индекс, а столбец age не имеет индекса.
Следующий оператор фактически попадает в индекс (говорят, что это новая версия MySQL, если вы используете старую версию MySQL, вы можете использовать объяснение для проверки).
select * from `user` where user_id = 1 or user_id = 2;
Но это утверждение не может попасть в индекс.
select * from `user` where user_id = 1 or age = 20;
Предполагая, что у столбца возраста также есть индекс, он все равно не может попасть в индекс.
select * from `user` where user_id = 1 or age = 20;
Поэтому рекомендуется избегать использования оператора or как можно чаще, а вместо этого использовать union all или in в зависимости от ситуации.Эффективность выполнения этих двух операторов также выше, чем у or.
2. Столбец индекса в операторе where использует отрицательный запрос, что может привести к сбою индекса.
К отрицательным запросам относятся: НЕ, !=, , !, НЕ В, НЕ НРАВИТСЯ и т. д.
Некий «военный устав» гласил, что использование отрицательного запроса обязательно приведет к аннулированию индекса. Автор проверил некоторые статьи, и некоторые пользователи сети опровергли этот пункт и представили доказательства.
На самом деле, отрицательный запрос не обязательно приводит к сбою индекса, это зависит от суждения оптимизатора MySQL, который является более низкой стоимостью полного сканирования таблицы или индекса.
3. Поле индекса может иметь значение null.При использовании значения null или не равно null индекс может стать недействительным.
На самом деле, одно поле индекса может попасть в индекс, когда используется значение null или не равно null, но пользователи сети сказали в доказательстве, что когда два разных поля индекса связаны с или, индекс будет недействительным.Автор считает, что индекс действительно недействительно, но этот горшок должен быть подкреплен или, что относится к первому сценарию~~
Предположим, что столбец user_id в таблице USER имеет индекс и допускает значение null, а столбец age имеет индекс и допускает значение null.
select * from `user` where user_id is not null or age is not null;
Однако в некоторых «военных уставах» и спецификациях подчеркивается, что поля должны быть установлены не нулевыми и предоставлять значения по умолчанию, и есть причины для справки.
- Пустые столбцы усложняют сравнение индексации/статистики индекса/значения и затрудняют оптимизацию для MySQL.
- Этот тип внутреннего null MySQL требует специальной обработки, увеличивая сложность обрабатываемых записей базы данных; раз при тех же условиях, в таблице много пустых полей, производительность обработки базы данных будет значительно снижена.
- Нулевые значения требуют больше места для хранения, а столбцы, которые являются нулевыми в каждой строке таблицы или индекса, требуют дополнительного места для идентификации.
- При работе с нулевым значением можно использовать только значение null или не значение null, но нельзя использовать символы операций, такие как =, in, , !=, not in. Например: где name!='shenjian', если есть запись с именем null, результат запроса не будет включать записи с именем null.
4. Использование встроенных функций для индексированных столбцов определенно приведет к сбою индекса.
Например, если функция используется для столбца индекса login_time в следующем операторе, индекс будет недействительным:
select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;
Предложения по оптимизации, попробуйте сделать расчеты и преобразования в приложении.
На самом деле пользователи сети упоминают два типа сценариев сбоя индекса, и все они должны быть связаны с использованием функций в столбцах индекса.
4.1 Отказ индекса, вызванный преобразованием неявного типа
Например, столбец индекса user_id в следующем операторе имеет тип varchar и не попадет в индекс:
select * from `user` where user_id = 12;
Это связано с тем, что MySQL выполняет неявное преобразование типов, а вызывающая функция преобразует user_id.
select * from `user` where CAST(user_id AS signed int) = 12;
4.2 Аннулирование индекса, вызванное неявным преобразованием кодировки символов
При выполнении связанного запроса между двумя таблицами, если кодировка символов связанных полей в двух таблицах несовместима, MySQL может вызвать функцию CONVERT для неявного преобразования различных кодировок символов для достижения единства. При применении к связанному полю это приведет к сбою индекса.
Например, в следующем операторе кодировка символов d.tradeid — utf8, а кодировка символов l.tradeid — utf8mb4. Поскольку utf8mb4 является расширенным набором utf8, MySQL будет использовать CONVERT для преобразования utf8 в utf8mb4 при преобразовании. Проще говоря, CONVERT действует на d.tradeid, поэтому индекс терпит неудачу.
select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
Обычно есть два выхода из этой ситуации.
план 1:Унифицируйте кодировку символов связанных полей.
Сценарий 2:Когда невозможно унифицировать кодировку символов, вручную примените функцию ПРЕОБРАЗОВАТЬ к правой стороне = при связывании, чтобы достичь цели унификации кодировки символов.Вот принудительное преобразование utf8mb4 в utf8.Конечно, есть есть риск усечения данных при преобразовании из надмножества в подмножество. следующее:
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
5. Вычисление столбца индекса определенно приведет к сбою индекса.
Такие операции, как +, -, *, / и т. д., следующие:
select * from `user` where age - 1 = 10;
Для оптимизации поставьте операцию над значением, либо вычислите его прямо в приложении, например:
select * from `user` where age = 10 - 1;
6. Подобный подстановочный знак может привести к сбою индекса.
Когда тот подобный запрос начинается с%, индекс будет недействительным. Есть два решения:
- Переместите % назад, например:
select * from `user` where `name` like '李%';
- Используйте покрывающий индекс, чтобы попасть в индекс.
select name from `user` where `name` like '%李%';
7. В объединенном индексе столбец индекса в where нарушает принцип крайнего левого сопоставления, что определенно приведет к сбою индекса.
При создании объединенного индекса, такого как (k1, k2, k3), это эквивалентно созданию трех индексов (k1), (k1, k2) и (k1, k2, k3), что является самым левым принципом сопоставления.
Например, следующий оператор не попадет в индекс:
select * from t where k2=2;
select * from t where k3=3;
slect * from t where k2=2 and k3=3;
Следующий оператор будет использовать только индекс (k1):
slect * from t where k1=1 and k3=3;
8. Окончательный выбор оптимизатора MySQL, без индексации
Как упоминалось выше, даже если он полностью соответствует сценарию, в котором индекс вступает в силу, с учетом фактического объема данных и других причин, использование индекса в конечном итоге зависит от суждения оптимизатора MySQL. Конечно, вы также можете указать в операторе sql, чтобы принудительно указать определенный индекс.
Некоторые предложения по оптимизации индексов
- Запрещается создавать индексы для атрибутов, которые очень часто обновляются и не являются дискриминационными.
- Обновления изменят B+-дерево, а индексация часто обновляемых полей сильно снизит производительность базы данных.
- «Пол» не является атрибутом этой дискриминации, индексирование бессмысленно, не может эффективно фильтровать данные, а производительность аналогична полному сканированию таблицы.
- Чтобы построить композитный индекс, вы должны поставить высокоразличимые поля спереди.
Ссылаться на
«Почему эти операторы SQL имеют одинаковую логику, но производительность сильно различается? 》
«Бэкенд-программисты должны: десять основных болезней, связанных с отказом индекса»