Бэкенд-программисты должны: десять самых разных симптомов сбоя индекса

MySQL

задний план

Недавно в продакшене случился медленный sql, причина в том, что использовались или и !=, что приводило к сбою индекса. Итак, я резюмирую десятку самых разных заболеваний индекса отказа, я надеюсь помочь всем, давай.

1. Условие запроса содержит или, что может привести к сбою индекса

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

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. Выполните запрос sql, он пройдет по индексу, как показано на следующем рисунке:
  2. Добавление условия или + возраст без индекса не приведет к индексу, как показано на рисунке:

Анализ и заключение:

  • В случае or+age без индекса предположим, что он использует индекс userId, но когда он достигает условия запроса возраста, он должен выполнить полное сканирование таблицы, которое требует трехэтапного процесса: полное сканирование таблицы + сканирование индекса. + объединить
  • Если он выполняет полное сканирование таблицы в начале, оно будет выполнено за одно сканирование.
  • У MySQL есть оптимизатор, который с точки зрения эффективности и стоимости.При возникновении условий или индекс может дать сбой, что кажется разумным.

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

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

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

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId является строковым типом и является общим индексом дерева B+.Если в условии запроса передано число, то оно не попадет в индекс, как показано на рисунке:

Если к числу добавить '', то есть передать строку, конечно, берется индекс, как показано ниже:

Анализ и заключение:

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

В-третьих, подобный подстановочный знак может привести к сбою индекса.

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

Структура таблицы:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Как и запрос начинается с%, индекс недействителен, как показано:

Поместите % позади и обнаружите, что индекс все еще работает нормально, как показано ниже:

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

в заключении:

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

  • Используйте покрывающий индекс
  • поставить % после

Прикрепил:Индекс содержит все данные, соответствующие запросам, и называется индексом покрытия (Covering Index).

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

Структура таблицы: (с индексом объединенияidx_userid_age,userIdспереди,ageпозади)

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

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

Если условие не объединяет столбец с индексом столбца, индекс отказа выглядит следующим образом:

Анализ и заключение:

  • Когда мы создаем совместный индекс, такой как (k1, k2, k3), это эквивалентно созданию трех индексов (k1), (k1, k2) и (k1, k2, k3), что является самым левым принципом сопоставления.
  • Совместный индекс не соответствует крайнему левому принципу, и индекс вообще не будет работать, но это также связано с оптимизатором Mysql.

5. Используйте встроенную функцию mysql для столбца индекса, и индекс не будет работать.

Структура таблицы:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `loginTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE,
  KEY `idx_login_time` (`loginTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Хотя loginTime имеет индекс, поскольку используется встроенная функция Date_ADD() mysql, индексом является непосредственно GG, как показано на рисунке:

В-шестых, операция столбца индекса (например, +, -, *, /), индекс недействителен.

Структура таблицы:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Хотя у возраста есть индекс, но из-за его действия индекс напрямую теряется. . . Как показано на рисунке:

7. Использование (!= или , а не in) в поле индекса может привести к сбою индекса.

Структура таблицы:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Хотя возраст индексируется, он используется! = или , не в этих, индекс как пустышка. следующее:

8. Использование null и not null в поле индекса может привести к сбою индекса.

Структура таблицы:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_card` (`card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

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

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

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

9. Левый запрос соединения или запрос правого соединения имеют разные форматы кодирования полей, связанных с запросом, что может привести к сбою индекса.

Создайте две новые таблицы, одну для пользователя и одну для user_job.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `user_job` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Кодировка поля имени таблицы user — utf8mb4, а кодировка поля имени таблицы user_job — utf8.

При выполнении запроса левого внешнего соединения таблица user_job по-прежнему выполняет полное сканирование таблицы следующим образом:

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

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

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

  • Не указывайте «пол» и т. д. Если столбец данных содержит такие значения, как «0/1» или «Д/Н», то есть содержит много повторяющихся значений, даже если для него установлен индекс, индексный эффект будет не очень хорошим, и это может привести к сканированию всей таблицы.

Ради эффективности и стоимости Mysql оценивает, какой из них быстрее выполняет полное сканирование таблицы или использует индекс. Это связано с его оптимизатором, давайте взглянем на его логическую диаграмму архитектуры (источник изображения в Интернете)

Суммировать

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

CREATE TABLE `user_session` (
  `user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `device_id` varchar(64) NOT NULL,
  `status` varchar(2) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain 
update user_session set status =1
where  (`user_id` = '1' and `device_id`!='2')
or (`user_id` != '1' and `device_id`='2')

анализировать:

  • Выполненный sql, используяorусловие, так как составной первичный ключ (user_id,device_id), похоже, что каждый столбец проиндексирован, и индекс вступит в силу.
  • но появляется!=, что может привести к сбою индекса. этоor+!=Два основных синдрома приводят к медленному обновлению sql.

решение:

Итак, как это решить? мы кладемorУсловие снимается и делится на два исполнения. дать в то же времяdevice_idДобавьте нормальный индекс.

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

Личный публичный аккаунт

  • Если вы хороший ребенок, который любит учиться, вы можете подписаться на мой официальный аккаунт, чтобы учиться и обсуждать вместе.
  • Если вы считаете, что в этой статье есть какие-либо неточности, вы можете прокомментировать или подписаться на мой официальный аккаунт, пообщаться со мной в частном порядке, и все смогут учиться и прогрессировать вместе.
Категории