[Эта статья была написана Combaudon]
Недавно я видел несколько случаев, когда производительность запросов MySQL к одной таблице была ужасной. Причина была проста: были добавлены неправильные индексы, и поэтому план выполнения был плохим. Вот рекомендации, которые помогут вам оптимизировать различные типы запросов к одной таблице.
Отказ от ответственности: я буду представлять общие руководящие принципы, и я не намерен охватывать все сценарии. Я уверен, что вы можете найти примеры, в которых то, что я пишу, не работает, но я также уверен, что это поможет вам большую часть времени. Также я не буду обсуждать функции, которые вы можете найти в MySQL 5.6+, такие как Index Condition Pushdown, для простоты. Помните, что такие функции могут существенно повлиять на время ответа на запрос (хорошо или плохо).
Что индекс может сделать для вас
Индекс может выполнять до 3 действий: фильтровать, сортировать / группировать и покрывать. Хотя первые два действия говорят сами за себя, не каждый может знать, что такое «индекс покрытия». На самом деле это очень просто. Общий рабочий процесс для базового запроса:
1. Используйте индекс, чтобы найти совпадающие записи и получить указатели на данные.
2. Используйте указатели на соответствующие данные.
3. Вернуть записи
Когда можно использовать индекс покрытия, он уже охватывает все поля, запрошенные в запросе, поэтому шаг № 2 можно пропустить, и рабочий процесс теперь:
1. Используйте индекс, чтобы найти совпадающие записи.
2. Верните записи.
Во многих случаях индексы невелики и могут помещаться в памяти, тогда как данные велики и не помещаются в памяти: с помощью покрывающего индекса вы можете избежать большого количества операций с диском, и производительность может быть на порядок выше.
Давайте теперь посмотрим на разные распространенные сценарии.
Одиночное равенство
Это самый основной сценарий:
SELECT * FROM t WHERE c = 100
Идея, конечно, состоит в том, чтобы добавить индекс (c). Однако обратите внимание, что если критерии недостаточно избирательны, оптимизатор может выбрать сканирование всей таблицы, что, безусловно, будет более эффективным.
Также обратите внимание, что частым вариантом этого запроса является случай, когда вы выбираете только небольшое подмножество полей вместо всех полей:
SELECT c1, c2 FROM t WHERE c = 100
Здесь может иметь смысл создать индекс, (c, c1, c2) потому что он будет индексом покрытия. Не создавайте индекс по (c1, c2, c)! Он по-прежнему будет охватывать, но не будет использоваться для фильтрации (помните, что для фильтрации можно использовать только самый левый префикс индекса).
Множественные равенства
SELECT * FROM t WHERE c = 100 and d = 'xyz'
Это также очень легко оптимизировать: просто добавьте индекс (c, d) или (d, c).
Основная ошибка здесь состоит в том, чтобы добавить 2 индекса: один включен (c) и один включен (d). Конечно, MySQL может использовать оба индекса с index_merge алгоритмом, но это почти всегда очень плохой вариант .
Равенство и неравенство
SELECT * FROM t WHERE c > 100 and d = 'xyz'
Здесь мы должны быть осторожны, потому что, пока мы используем столбец с неравенством, это не позволит нам использовать дополнительные столбцы в индексе.
Поэтому, если мы создадим индекс для (d, c), мы сможем фильтровать как c и d, это хорошо.
Но если мы создадим индекс (c, d), мы будем только фильтровать c, что менее эффективно.
Таким образом, в отличие от ситуации, когда у вас есть равенства, порядок столбцов имеет значение, когда используются неравенства.
Многочисленные неравенства
SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'
Поскольку у нас есть 2 неравенства, мы уже знаем, что не сможем фильтровать оба условия (*). Поэтому мы должны принять решение: будем ли мы фильтровать (d, b) или продолжать (d, c)?
Невозможно определить, какой вариант лучше, не глядя на данные: просто выберите столбец, где неравенство является наиболее избирательным. Суть в том, что вы должны сначала поставить столбец (ы) с равенством.
(*) На самом деле существует способ «фильтровать» оба неравенства: разделить b и добавить индекс (d, c) или разделить c и добавить индекс (d, b). Подробности выходят за рамки этого поста, но это может быть вариантом для некоторых ситуаций.
Равенства и сортировки
SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b
Как упоминалось в первом абзаце, индекс может фильтровать и сортировать, поэтому этот запрос легко оптимизировать. Однако, как и в случае неравенств, мы должны тщательно выбирать порядок столбцов в индексе: правило состоит в том, что мы сначала фильтруем, а затем сортируем.
Имея это в виду, легко понять, что (c, d, b) или (d, c, b) будут хорошими показателями, в то время как (b, c, d) или (b, d, c) не так хороши (они будут сортировать, но не фильтровать).
И если у нас есть:
SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b
Мы можем создать супер эффективный индекс , который будет фильтровать, сортировать и покрывать: (c, d, b, c1, c2).
Неравенство и сортировка
У нас есть 2 основных варианта здесь. Первый из них:
SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b
В этом случае разумны два варианта:
1. Фильтровать d и сортировать по b.
2. фильтр на d и c.
Какая стратегия более эффективна? Это будет зависеть от ваших данных, поэтому вам придется экспериментировать.
Второй вариант:
SELECT * FROM t WHERE c > 100 ORDER BY b
На этот раз у нас нет равенства, поэтому мы должны выбирать между фильтрацией и сортировкой. Скорее всего, вы выберете фильтрацию.
Вывод
Не все случаи были рассмотрены в этом посте, но вы уже можете видеть, что в некоторых случаях вы будете создавать плохие индексы MySQL, если не будете осторожны. В следующем посте я представлю случай, который на первый взгляд может показаться запутанным, но который легко понять, если вы уже знаете все, что упомянуто здесь.