Как выбрать колонки для индексирования в MySQL

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

Как это можно выяснить? Простой и очень интересный пример взят с блога mysqlPerformanceBlog. Этот способ анализа индексов применим не только к MySQL, а и к другим СУБД.

Задача

Допустим, у нас есть запрос, который возвращает 0 рядков:

SELECT * FROM tbl WHERE STATUS='waiting' AND source='twitter'
AND no_send_before <= '2009-05-28 03:17:50' AND tries <= 20
ORDER BY date ASC LIMIT 1;

Не будем вдаваться в подробности (что это за таблица, и что это за запрос такой), это не так важно. На данный момент таблица не содержит индексов, что показывает EXPLAIN (full table scan). Нам необходимо определить, какой индекс будет наиболее оптимальным для такого запроса.

Анализируем

Для эффективного индекса, первой должна быть наиболее селективная колонка (т.о. количество возможных рядков выборки будет минимальным). Учитывая это, мы можем просто посчитать количество рядков, удовлетворяющих каждому условию в нашем запросе:

SELECT sum(STATUS='waiting'), sum(source='twitter'),
sum(no_send_before <= '2009-05-28 03:17:50'), sum(tries <= 20), count(*)
FROM tbl\G
*************************** 1. row ***************************
sum(STATUS ='waiting'): 550
sum(source='twitter'): 37271
sum(no_send_before <= '2009-05-28 03:17:50'): 36975
sum(tries <= 20): 36569
count(*): 37271

Наиболее селективной колонкой в этом случае будет STATUS (после выборки по ней придется проверить максимум 550 рядков). Значит эту колонку ставим первой в индексе.

Идем далее. Вытащим выбранную колонку из выборки и поставим ее в условие. Это поможет таким же образом определить следующую колонку в индексе:

SELECT sum(source='twitter'),
sum(no_send_before <= '2009-05-28 03:17:50'), sum(tries <= 20), count(*)
FROM tbl WHERE STATUS='waiting'\G
*************************** 1. row ***************************
sum(source='twitter'): 549
sum(no_send_before <= '2009-05-28 03:17:50'): 255
sum(tries <= 20): 294
count(*): 5

Тут видим следующую важную информацию. Похоже, что условие (source=’twitter’), а следовательно и колонка SOURCE вовсе не является селективной, т.к. почти не уменьшит количество рядков в выборке (549 и 550 — практически никакой разницы). Т.о. эту колонку не имеет смысла ставить в индекс.

Теперь, мы можем посмотреть на селективность колонок no_send_before и tries. Ставя первую из них в условие, вторая выдаст 0 совпадений в выборке и наоборот:

SELECT sum(source='twitter'),
sum(no_send_before <= '2009-05-28 03:17:50'), sum(tries <= 20), count(*)
FROM tbl WHERE STATUS='waiting' AND no_send_before <= '2009-05-28 03:17:50'\G

*************************** 1. row ***************************
sum(source='twitter'): 255
sum(no_send_before <= '2009-05-28 03:17:50'): 255
sum(tries <= 20): 0
count(*): 255

SELECT sum(source='twitter'),
sum(no_send_before <= '2009-05-28 03:17:50'), sum(tries <= 20), count(*)
FROM tbl WHERE STATUS='waiting' AND tries <= 20\G

*************************** 1. row ***************************
sum(source='twitter'): 294
sum(no_send_before <= '2009-05-28 03:17:50'): 0
sum(tries <= 20): 294
count(*): 294

Следовательно, следующая колонка, которую стоит добавить в индекс будет no_send_before. Хотя она незначительно отличается по селективности от колонки tries и тут выбор будет зависеть, скорее, от предназначения таблицы.

Вывод

Вот таким образом мы определили, что самым эффективным индексом для этой таблицы будет составной индекс (status, no_send_before). А на первый взгляд казалось, что нужно строить совсем не такой индекс… Оригинал на английском можно почитать тут.

Скорая компьютерная помощь всегда поможет в решении проблем с компьютерами и ноутбуками!

А как вам это - "Казань брал, Астрахань брал, Ревель брал... Шпака не брал." Ремонт volvo s80: надо показать сыну!