Индексы на примере Mysql

Свежая статья об использовании индексов в 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). А на первый взгляд казалось, что нужно строить совсем не такой индекс.


Подпишитесь на Хайлоад с помощью Google аккаунта
или закройте эту хрень