11 June 2009

Определяем порядок столбцов в составном индексе

MySQL
Original author: Baron Schwartz
Хочу поделиться простым эмпирическим методом, который я использую для определения того, в каком порядке должны идти столбцы в составном индексе. Этот способ подходит не только для MySQL, он также применим к любым СУБД, в которых используются b-tree индексы.

Давайте начнем с запроса, который возвращает пустой результат, но при этом делает полный скан таблицы. EXPLAIN покажет на нем, что нет доступных индексов (т.е. possible_keys = NULL)

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;

Не пытайтесь понять смысл данного запроса, он приведен только в качестве примера. В простейшем случае мы хотим поместить самый избирательный столбец в индекс первым, так чтобы возможное количество совпадающих строк было минимальным, таким образом мы будем находить нужные строки быстро, насколько это возможно. Считая, что все столбцы имеют некоторое распределение значений, мы можем просто посчитать количество совпадений для каждого условия.

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


Всё просто — я обернул каждое условие функцией SUM(), которая для MySQL эквивалентна COUNT(число_раз_когда_тру). Как видно, наиболее избирательным условием является «status=waiting». Давайте поместим этот столбец в индекс первым, после чего перенесем условие из SELECT в WHERE и снова выполним запрос, для подсчета совпадений в оставшемся наборе.

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(*): 549


Теперь мы опустились до приемлемого числа строк. Видно, что «source» совсем не обладает избирательностью, т.е. с помощью него ничего отфильтровать не получится, и добавление его в индекс не принесет никакой пользы. Можно отфильтровать оставшийся набор либо с помощью 'no_send_before', либо 'tries'. Выполнение запроса с любым из них в where уменьшит количество совпадений для другого условия до нуля.

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
* This source code was highlighted with Source Code Highlighter.


Это значит, что мы можем сделать индекс с любым из них — (status,tries) или (status,no_send_before) и мы найдем наши ноль строк очень эффективно. Какой же из них лучше, зависит от того, для чего действительно используется эта таблица (а также от наличия и структуры других запросов к этой таблице — прим. пер.).
Tags:mysqlсоставной индекс
Hubs: MySQL
+42
14.1k 160
Comments 17
Top of the last 24 hours