Pull to refresh

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

Reading time 5 min
Views 21K
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:
Hubs:
+42
Comments 17
Comments Comments 17

Articles