Индексы MySQL - сколько достаточно?
Я пытаюсь настроить мой сервер MySQL, чтобы проверить свои настройки, проанализировать журнал медленных запросов и, по возможности, упростить свои запросы.
Иногда достаточно, если я правильно индексирую, иногда нет. Я где-то читал (пожалуйста, поправьте меня, если это глупость), что больше индексов, чем мне нужно, дают тот же эффект, например, если у меня нет ни одного индекса.
Сколько индексов достаточно? Вы можете сказать, что это зависит от сотен факторов, но мне любопытно, как я могу очистить свой mysql-slow.log достаточно, чтобы уменьшить нагрузку на сервер.
Кроме того, я увидел несколько "интересных" записей в журнале, например:
# Query_time: 0 Lock_time: 0 Rows_sent: 22 Rows_examined: 44
SELECT * FROM `categories` ORDER BY `orderid` ASC;
В рассматриваемой таблице содержится ровно 22 строки, индекс установлен в orderid, Почему этот запрос появляется в журнале в конце концов? Зачем проверять 44 строки, если они содержат только 22?
6 ответов
Количество индексации и линия выполнения слишком много будет зависеть от множества факторов. Для небольших таблиц, таких как таблица "категорий", вы обычно не хотите или не нуждаетесь в индексе, и это может фактически снизить производительность. Причина в том, что для считывания индекса требуется ввод-вывод (т. Е. Время), а затем требуется больше ввода-вывода и времени для извлечения записей, связанных с соответствующими строками. Исключение составляют случаи, когда вы запрашиваете только столбцы, содержащиеся в индексе.
В вашем примере вы извлекаете все столбцы и только с 22 строками, и может быть быстрее просто выполнить сканирование таблицы и отсортировать их вместо использования индекса. Оптимизатор может / должен делать это и игнорировать индекс. Если это так, то индекс просто занимает место без пользы. Если к вашей таблице "категорий" часто обращаются, вы можете подумать о том, чтобы закрепить ее в памяти, чтобы сервер БД оставил ее доступной без необходимости постоянно заходить на диск.
При добавлении индексов необходимо сбалансировать дисковое пространство, производительность запросов и производительность обновления и вставки в таблицы. Вы можете обойтись без дополнительных индексов для таблиц, которые являются статическими и не сильно меняются, в отличие от таблиц с миллионами обновлений в день. В этот момент вы почувствуете влияние поддержки индекса. Что приемлемо в вашей среде, тем не менее, может быть определено только вами и вашей организацией.
При проведении анализа не забудьте сгенерировать / обновить таблицу и индексировать статистику, чтобы быть уверенными в точных вычислениях.
Как правило, у вас должны быть индексы для всех первичных ключей (у вас нет выбора), всех внешних ключей и любых других полей, которые вы обычно используете для выборки строк.
Например, если я обычно ищу пользователей по имени пользователя, я бы проиндексировал это, даже если идентификатор пользователя был первичным ключом.
Сколько индексов полностью зависит от выполняемых вами запросов, какие типы соединений выполняются (если есть), типа данных, хранящихся в таблице, и размера таблиц (а также многих других факторов). Там действительно нет точной науки к этому. Лучший инструмент в вашем арсенале для выяснения того, как оптимизировать запрос, это объяснить. Используя объяснение, вы можете узнать, какие типы соединений отключаются, какие возможные ключи можно использовать и какой ключ (если есть) использовался, а также сколько строк было проверено для каждой таблицы в объединении.
Используя эту информацию, вы можете решить, как вводить таблицы и / или изменять запросы, чтобы сделать их более эффективными. Синтаксис объяснения очень прост.
EXPLAIN SELECT * FROM `categories` ORDER BY `orderid` ASC;
Обратите внимание, объяснение на самом деле не запускает запрос. Так что, если вы используете это для отладки запроса, который занимает 5 минут, объяснение все равно будет очень быстрым.
Вы должны быть осторожны при добавлении индексов, хотя они и приводят к медленному выполнению операций вставки и обновления, и на очень больших таблицах это снижение производительности может стать заметным. Особенно, если эта же таблица используется для большого количества чтений. Хотя добавление большого количества индексов обычно не снижает производительность запроса, вы все равно должны добавлять их только как
Также имейте в виду, что MySQL будет использовать максимум один индекс на оператор выбора (хотя, если вы используете соединение, он также может использовать один для каждого соединения). Так что индексация только потому, что это пустая трата дискового пространства и замедлит работу базы данных при записи. Если вы обычно используете оператор where для двух столбцов, сделайте один индекс, содержащий оба этих столбца, это будет значительно быстрее, чем индексирование только одного.
Индекс может ускорить запрос SELECT, но он замедлит запросы INSERT/UPDATE/DELETE, потому что им также необходимо обновить индекс, а не только строку.
Это всего лишь личное мнение (у меня нет фактов, подтверждающих это), но я думаю, что если есть запрос, который занимает много времени и индекс ускорит его - сделайте это! "Слишком много" индексов было бы, если бы вы добавляли индексы, которые не приносили никакой пользы (например, не было запросов, которые бы ускорялись). Например, глупо было бы разместить индекс в каждом столбце "только потому, что".
Не существует магического числа для "лучшего" числа индексов. Основное правило таково: добавляйте индексы для запросов, которые часто используются и / или должны выполняться быстро.
Наличие "слишком большого количества" индексов не должно замедлять запросы, но каждый добавленный индекс добавляет небольшое количество времени для добавления / обновления элементов в БД (так как это также изменяет индексы) и небольшой объем пространства. Однако, если вы просто добавляете индексы по мере необходимости, это, вероятно, не является большой проблемой.