soldatov.info

Блог Солдатова Евгения

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

Источник: http://highload.com.ua/index.php/2009/06/09/Как выбрать колонки для индексирования в MySQL/

Автор: Евгений

Дата: 06.12.2013 в 08:37:02

Рубрики: MySQL

Метки: , ,

Leave a Reply

You must be logged in to post a comment.