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

Original author: Baron Schwartz
  • Translation
Хочу поделиться простым эмпирическим методом, который я использую для определения того, в каком порядке должны идти столбцы в составном индексе. Этот способ подходит не только для 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) и мы найдем наши ноль строк очень эффективно. Какой же из них лучше, зависит от того, для чего действительно используется эта таблица (а также от наличия и структуры других запросов к этой таблице — прим. пер.).
Share post

Comments 17

    –3
    Это хорошая заметка от mysqlperformanceblog.com
    Народ как всегда делится вроде бы мелочами, но какими!
      +7
      В топике-переводе, как обычно, внизу указана ссылка на статью-источник и автора.
        –5
        Всё так, капитан.
      +1
      Что-то я не понял: а индекс тут делается уже после того, как таблица заполнена?
      А как быть при расстановке индексов при проектировании базы данных, когда невозможно провести такой анализ?
        +1
        Часто можно «прикинуть» селективность индекса ещё на этапе проектирования в зависимости от характера данных. Например, селективность по полю «возраст» будет очевидно выше чем селективность по полю «пол».
        В остальных случаях лучше не торопиться.
          +2
          Все-таки при проектировании базы можно предварительно провести анализ. А практически, при проектировании сначала планируются те индексы, которые «must have», а потом уже добавляются по необходимости. А главное, это не забывать, что использование индексов не всегда идет во благо.
          –6
          «Как видно, наиболее избирательным условием является «status=waiting»» — про понимание избирательности индекса автору оригинальной статьи ещё далеко.
            +1
            Спасибо за комментарий с отлично аргументированной точкой зрения. Барону Шварцу далеко до понимания, ага…
            +2
            Несколько странно слышать слово «эмпирический» — я не особый специалист по БД, но, например, в такой книжке High Performance MySQL про определение и использование индексов все подробно расписано.

            Селективность индекса имеет значение (насколько я помню) имеет значение в случае когда индексируется только одно поле — причина в том, что в таком случае при низкой селективности индекса гораздо производительнее пробежаться во всем строкам, чем выгребать треть записей в хаотичном порядке по индексу.

            В составном индексе, не может быть более одного поля входящего в диапазонный критерий — и такое поле должно быть последним.

            Если вы ходите чтобы ORDER эффективно использовал индекс — тот же самый принцип.

            Еще вы фиксированно задали значения критериев в запросе. Как будет работать такой запрос при source='yahoo', если там будет 2-3 записи?

            В свете всего сказанного и учитывая что вам требуется всего одна запись — я бы попробовал сначала индекс (status, source, date) — это позволило бы избежать временной таблицы и filesort — а запись (одна) удовлетворяющая дополнительному критерию MySQL (no_send_before <= '2009-05-28 03:17:50' AND
            tries <= 20) нашлась бы сканированием записей по индексу
              +5
              Хочу поделиться простым эмпирическим методом...

              Все-таки не стоит начинать перевод от первого лица. А то читаешь и думаешь, неужели mysqlperformanceblog.com начали переводить и выкладывать свои статьи на Хабр. По-моему, лучше делать вводное предложение. Допустим: Барон Шварц из mysq… предложил свой способ определения порядка столбцов в индексе. Сугубо личное мнение.
                0
                Да и так, не плохо вышло.
                  0
                  Я считаю, что при переводе вмешательство переводчика в оригинал должно быть минимальным (это не касается адаптации к русской аудитории, если она необходима). То, что предлагаете вы сделает топик просто топиком, имхо. Кстати из опыта знаю, что даже если статья написана не от первого лица — обязательно найдется возмущенный читатель, из уже видевших оригинал, который кинется намекать что я тут источников не указал и типа выдаю статью за свою. Думаю это проблема скорее интерфейса Хабра, то что топик — перевод, в глаза не бросается. Но все равно писать в топике-переводе что это топик-перевод и еще раз указывать ссылки мне кажется бредовой затеей

                  >> А то читаешь и думаешь
                  Вы и правда так подумали, если честно? :)
                    0
                    Если често, то я подумал, что кто-то перепечатал статью от своего имени.
                    Я не предлагал изменять перевод, я предложил только добавить введение в начало статьи, в котором бы описывалось, кто такой автор, о чем пишет и т.д. И, кстати, я закончил институт иностранных языков, где учат переводчиков.
                      0
                      Понимаете, когда я создаю топик-перевод — я в обязательном порядке указываю кто автор, ссылку на оригинал статьи, более того без этой информации топик-перевод и создать то нельзя. Вы предлагаете это делать дважды? Только для того, чтобы не самый, так скажем, внимательный читатель, не заметивший у заголовка иконку перевода и/или еще не заходя внутрь статьи или в ней самой не увидевший имя автора оригинала и ссылку, смог это прочитать непосредственно в тексте? Но ведь у тех, кто знает что такое топик-перевод никаких вопросов не возникает. Так же как и сомнений в присваивании чужих статей.

                      На Хабре как бы есть «формат» оформления таких топиков, я считаю правильным его придерживаться. И я не люблю дублирования информации в любом его виде. Делать же вступление, по моему это черезчур — ну тут заметка на несколько страниц все-таки, а не литературное произведение Драйзера.

                  0
                  При таком методе, самое главное — где-то в начале не наткнуться на исключение из общей картины.
                  В данном примере, при первом подсчёте важно было взять именно source='twitter' а не source='the_fresh_twitter_clone' для которого sum(source='the_fresh_twitter_clone') дало бы 27, после чего все последующие расчеты были бы уже ложны.

                  Среднестатистический запрос ещё нужно уметь построить…
                  Думаю, что в таком методе, важно делать несколько разных запросов на каждом шагу.
                    0
                    Мне кажеться, автор привел пример как увеличить скорость в уже существующих проектах.
                      0
                      а подскажите мне пожалуйста…
                      есть составной индекс из двух полей
                      при запросе
                      where field1=3
                      order by field2
                      все норм
                      при
                      where field1<3
                      order by field2
                      появляется filesort при изменении знака с '=' на '<' или '>'

                      Only users with full accounts can post comments. Log in, please.