Backend-программист
0,0
рейтинг
25 сентября 2009 в 17:52

Разработка → Индексы в MySQL: многоколоночные индексы против комбинированных индексов перевод

Я часто вижу ошибки, связанные с созданием индексов в MySQL. Многие разработчики (и не только новички в MySQL) создают много индексов на тех колонках, которые будут использовать в выборках, и считают это оптимальной стратегией. Например, если мне нужно выполнить запрос типа AGE=18 AND STATE='CA', то многие люди просто создадут 2 отдельных индекса на колонках AGE и STATE.

Намного лучшей (здесь и далее прим. переводчика: а обычно и единственной верной) стратегией является создание комбинированного индекса вида (AGE,STATE). Давайте рассмотрим почему это так.



Обычно (но не всегда) индексы в MySQL являются BTREE-индексами — такой тип индекса способна быстро просматривать информацию, содержащуюся в своих префиксах, и перебирать диапазоны отсортированных значений. Например, когда Вы запрашиваете AGE = 18 с BTREE-индексом по колонке AGE MySQL найдёт в таблице первую отвечающую запросу строку и продолжит поиск до тех пор, пока не найдёт первую неподходящую строку — тогда он останавливает поиск, т.к. считает, что дальше ничего подходящего не будет. Диапазоны, например запросы вида BETWEEN 18 AND 20, работают сходным образом — MySQL останавливается на других значениях.

Несколько сложнее ситуация с запросами типа AGE IN (18,20,30), т.к. на самом деле MySQL приходится несколько раз проходить по индексу.

Итак, мы обсудили как MySQL ищет по индексу, но не определили что же он возвращает после поиска — обычно (если речь не идёт о покрывающих (covering) индексах) получает «указатель строки», который может быть значением первичного ключа (если используется движок InnoDB), физическое смещение в файле (для MyISAM) или что-нибудь в этом роде. Важно, что внутренний движок MySQL может по этому указателю найти полную строку со всеми необходимыми данными, отвечающими заданному значению индекса.

А какие есть варианты у MySQL, если Вы создали два отдельных индекса? Он может либо использовать только один из них, чтобы отобрать подходящие строки (а потом отфильтровать извлечённые данные, руководствуясь WHERE — но уже без использования индексов), либо может получить указатели на строки от всех подходящих индексов и вычислить их пересечение, а затем уже вернуть данные.

Какой из способов будет более подходящим зависит от избирательности и корреляции индексов. Если после отработки WHERE по первой колонке будет отобрано 5% строк, а применение далее WHERE по второй колонке отфильтровывает строки до 1% от общего количества, то применение пересечений, конечно, имеет смысл. Но если второй WHERE отфильтрует только до 4.5%, то обычно значительно выгоднее использовать только первый индекс и отфильтровать ненужные нас строки после извлечения данных.

Давайте рассмотрим несколько примеров:
CREATE TABLE `idxtest` (
`i1` int(10) UNSIGNED NOT NULL,
`i2` int(10) UNSIGNED NOT NULL,
`val` varchar(40) DEFAULT NULL,
KEY `i1` (`i1`),
KEY `i2` (`i2`),
KEY `combined` (`i1`,`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


Я создал колонки i1 и i2 независимыми друг от друга, причём каждая из них отбирает около 1% строк в таблице, которая содержит в общей сложности 10 млн. записей.

mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 AND i2=50;
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | idxtest | ref | i1,i2,combined | combined | 8 | const,const | 665 |
+----+-------------+---------+------+----------------+----------+---------+-------------+------+-------+
1 row IN SET (0.00 sec)


Как Вы можете видеть MySQL предпочёл использовать комбинированный индекс, и запрос выполнился меньше, чем за 10 мс!

А теперь предположим, что у нас есть индекс только по отдельным колонкам (сказать оптимизатору игнорировать комбинированный индекс):
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined) WHERE i1=50 AND i2=50;
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2 | i1,i2 | 4,4 | NULL | 1032 | USING intersect(i1,i2); USING WHERE
+----+-------------+---------+-------------+---------------+-------+---------+------+------+-------------------------------------+
1 row IN SET (0.00 sec)


Как Вы можете видеть в данном случае MySQL выполнил поиск пересечений индексов, а на выполнение запроса понадобилось 70 мс — в 7 раз дольше!

Теперь давайте посмотрим, что будет, если использовать только один индекс и отфильтровывать полученные данные:
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined,i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1 | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)


На этот раз MySQL пришлось обойти значительно больше строк, а выполнение запроса заняло 290 мс. Таким образом мы видим, что использование пересечения индексов намного лучше, чем использование одного индекса, но значительно лучше использовать комбинированные индексы.

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

mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX (combined) WHERE i1=50 AND i2 IN (49,50);
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1,i2 | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)


Как только запрос по одной из колонок становится не сравнением, а перечислением, MySQL больше не сможет использовать пересечение индексов, несмотря на то, что в данном случае при запросе i2 IN (49,50) это было бы более, чем разумно, т.к. запрос остаётся достаточно селективным.

Теперь давайте проведём ещё один тест. Я очистил таблицу и вновь наполнил её данными таким образом, чтобы значения в i1 и i2 сильно коррелировали. На самом деле они теперь вообще равны:
mysql [localhost] {msandbox} (test)> UPDATE idxtest SET i2=i1;
Query OK, 10900996 rows affected (6 min 47.87 sec)
Rows matched: 11010048 Changed: 10900996 Warnings: 0


Давайте посмотрим, что произойдёт в этом случае:
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 AND i2=50;
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i2,i1 | 4,4 | NULL | 959 | USING intersect(i2,i1); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+------+-------------------------------------+
1 row IN SET (0.00 sec)


Оптимизатор решил использовать пересечение индексов, хотя это было едва ли не самым худшим решением! Выполнение запроса заняло 360 мс. Также обратите внимания на большую погрешность в оценке примерного количества строк.

Это произошло из-за того, что MySQL считает значения в колонках i1 и i2 независимыми, и потому выбирает пересечение индексов. На самом деле он не может предположить другого, т.к. никакой статистики о корреляции значений в колонках у него нет.

mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxtest | ref | i1,combined | i1 | 4 | const | 106222 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+-------+--------+-------------+
1 row IN SET (0.00 sec)


А теперь, когда мы запретили MySQL использовать индекс по колонке i2 (а значит он не может и найти пересечение индексов), он использует индекс по одной колонке, а не комбинированный. Произошло так потому, что у MySQL есть статистика о примерном количестве подходящих строк, и так как оно равно для обоих индексов, то MySQL выбрал меньший по размеру. Выполнение запроса опять заняло 290 мс — в точности столько же, сколько и в прошлый раз.

Заставим MySQL использовать только combined индекс:
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i1,i2) WHERE i1=50 AND i2=50;
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
| 1 | SIMPLE | idxtest | ref | combined | combined | 8 | const,const | 121137 |
+----+-------------+---------+------+---------------+----------+---------+-------------+--------+-------+
1 row IN SET (0.00 sec)


Видно, что MySQL примерно на 20% ошибается в оценке количества перебираемых строк, что, конечно, неверно, т.к. используется тот же префикс, что и при использовании индекса только по колонке i1. MySQL не знает этого, т.к. просматривает статистику по отдельным индексам и не пытается согласовывать их.

Из-за того, что используемый комбинированный индекс больше, чем индекс по одной колонке, выполнение запроса заняло 300 мс.

Таким образом мы видим, что MySQL может решить использовать пересечение индексов даже в том случае, если это худший вариант, хотя с технической точки зрения это, конечно, будет лучший план, учитывая, что другой статистики у него нет.

Есть простые способы заставить MySQL не использовать пересечение индексов, но, к сожалению, мне не известно как заставить его использовать пересечения, если он считает этот вариант неоптимальным. Надеюсь, что такая возможность в будущем будет добавлена.

Наконец, давайте рассмотрим ситуацию, когда процедура нахождения пересечения индексов работает значительно лучше, чем комбинированные индексы по нескольким колонкам. Речь идёт о случае, когда мы используете OR при выборке между колонками. В этом случае комбинированный индекс становится совершенно бесполезным, и у MySQL есть выбор между полным сканированием таблицы (FULL SCAN) и выполнением объединения (UNION) значений вместо поиска пересечения на данных, которые он получил из одной таблице.

Я вновь изменил взначения в столбцах i1 и i2 таким образом, чтобы в них содержались независимые данные (типичная ситуация для таблиц).

mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 OR i2=50;
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 203803 | USING union(i1,i2); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+---------------------------------+
1 row IN SET (0.00 sec)


Такой запросы выполняется 660 мс. Отключив индекс по второй колонке мы получим FULL SCAN:
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest IGNORE INDEX(i2) WHERE i1=50 OR i2=50;
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | idxtest | ALL | i1,combined | NULL | NULL | NULL | 11010048 | USING WHERE
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
1 row IN SET (0.00 sec)


Обратите внимание, что MySQL указал ключи i1,combined как возможные к использованию, однако на самом деле такой возможности у него нет. Выполнение такого запросы занимает 3370 мс!

Также обратите внимание на то, что выполнение запроса заняло в 5 раз больше времени несмотря на то, что FULL SCAN прошёл примерно в 50 раз больше строк. Это показывает очень большую разницу в производительности между полным проходом по таблице и доступе по ключу, который занимает в 10 раз больше времени (в смысле «стоимости» доступа на строку), несмотря на то, что выполняется в памяти.

В случае UNION оптизатор действует более продвинуто и вполне способен справится с диапазонами:
mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM idxtest WHERE i1=50 OR i2 IN (49,50);
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
| 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 299364 | USING sort_union(i1,i2); USING WHERE
+----+-------------+---------+-------------+----------------+-------+---------+------+--------+--------------------------------------+
1 row IN SET (0.00 sec)


Подводя итоги


В большинстве случаев использование комбинированных индексов по нескольким колонкам является лучшим решением, если вы используете AND между подобными колонками в WHERE. Использование пересечения индексов в принципе улучшает производительность, но она всё равно значительно хуже, чем при использовании комбинированных ключей. В случае, если Вы используете OR между колонками Вам потребуется иметь по индексу на каждой из колонок, чтобы MySQL смог найти их пересечения, а комбинированные индексы не могут использоваться в таких запросах.
Перевод: peter
Вадим @WASD42
карма
85,2
рейтинг 0,0
Backend-программист
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (57)

  • –27
    срочно харбакат…
  • –28
    кат :'(
    • –33
      Чуть чуть опередили!
  • –28
    Сделайте хабракат пожалуйста, очень неудобно!
  • +9
    Значительно эффективнее было бы писать об этом в личные сообщения. Это если Вы, конечно, хотели именно чтобы я вспомнил о нём, а не чтоб написать об этом в комментариях…
  • 0
    а что делать, если в InnoBD у меня foreign keys и на поля, которые связываются я ставлю индексы? здесь же общий индекс делать бессмыслено?
  • 0
    Всегда интуитивно делал комбинированные индексы, а сейчас получил доказательства своей правоты. Спасибо за статью. :)
  • 0
    Спасибо за статью, давно хотел разобраться но руки не доходили.

    Хорошо, а как со скоростью апдейта комбинированного/двух индексов? Насколько скорость обновления индекса по двум полям отличается от индекса по одному полю?
    • 0
      +время удаления и вставки
  • +10
    Это всё фигня. Составные индексы, конечно, хорошо, но в случае B-Tree надо учитывать, что поиск по индексу может использовать только начальное подмножество от порядке индексируемых полей. Т.е., если есть составной индекс по полям (a, b, c, d), то такой индекс ускорит поиск по любой из следующих групп полей: (a), (a, b), (a, b, c), (a, b, c, d). Но не ускорит, например, поиск по (b, c, d). А поиск по (a, c, d) будет производиться с той же скоростью (тем же количеством итерированных элементов), как и поиск по (a).

    А вот в PostgreSQL есть GIST и GIN-индексы. С помощью хитрых contrib-модулей (впрочем, идущих в комплекте с PostgreSQL), с их помощью пожно создать составной индекс по нескольким полям, так, что любая выборка любых полей из проиндексированных будет находиться по индексу. Т.е., создав индекс по (a, b, c, d, e), можно будет выполнить запрос SELECT * FROM table WHERE a = 5 AND c = 7 AND e = 8, и (если планировщик посчитает, что надо использовать индекс) все результирующие записи можно будет получить по индексу.
    • 0
      Имеет ли значение, в каком порядке идут проиндексированные поля в выражении where? Или mysql самостоятельно оптимизирует поиск?

      • 0
        порядок выражений в условии влияния не оказывает, для оптимизатора есть другие приоритеты, например, первоначальное значение имеют константы.
    • НЛО прилетело и опубликовало эту надпись здесь
      • +2
        зачем ты зашел в этот блог, myserg?
        • НЛО прилетело и опубликовало эту надпись здесь
          • 0
            А ты тупенький наивно думаешь что все вот так все бросили и на постгре кинулись пересаживаться на тебя насмотревшись?

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

            PS
            Ничего против разработчиков постгре не имею. Свою работу они делают очень хорошо. Но вот комьюнити у них… Пусть ему спасибо скажут за то, что они сливали, сливают и будут сливать MySQL.
            • НЛО прилетело и опубликовало эту надпись здесь
      • +1
        А вам не кажется, что MySQL и PostgreSQL предназначены всё-таки для разных целей? Вам же не приходит в голову поставить Oracle, пусть даже OracleXE на сайт с посещаемостью в 100 человек в сутки? Так для чего тогда разводить холивары по поводу мускула и постреса? Постгрес является безусловно самой мощной из бесплатных СУБД, но его сложность не даст ему получить такую широкую популярность, как была у mSQL и как есть сейчас у MySQL.
        • НЛО прилетело и опубликовало эту надпись здесь
          • 0
            Во-первых, Oracle XE является бесплатной версией Oracle. Во-вторых, сложность СУБД оценивается далеко не возможностью выбрать движок. В-третьих, адекватнее надо быть, а то людям может показаться, что Ваша карма является реальным отражением Вашей ущербности.
            • НЛО прилетело и опубликовало эту надпись здесь
              • 0
                Плюсовая карма в профиле как минимум не говорит ни о чём ;) А вот Ваша ущербность и одноклеточность сквозит во всём. Учи матчасть, тролль ;)
    • 0
      Вообще, постгрес умеет совмещать результаты скана b-tree индексов в памяти (aka Bitmap index scan + Bitmap AND/OR)
      • +1
        Кстати о multicolumn GiST/GIN индексах — штука весьма экзотическая, в интернетах каких-либо тестов производительности найти не выходит. Есть мнение, что в случае выборки по нескольким полям такое сочетание выиграет лишь в исключительных случаях у bitmap-combine скана или обычного скана одного индекса с фильтрами по остальным полям.
        Ждем on-disk bitmap индексы, заготавливаем OLAP кубья :)
        • 0
          Есть, есть тесты.

          • +1
            Тьфу. Слишком рано отправилось. www.scribd.com/doc/4846380/-GIN-GiST-PostgreSQL — даже на русском :) У GIST insert-ы этак раз в 10 быстрее select-ов, у GIN — ровно наоборот.
    • +1
      Вот только давайте без холиваров.

      То что MySQL использует только первый столбец это понятно, было бы иначе и вопрос бы не стоял — делали бы один индекс по всем полям под все запросы (в общем случае) и все.
      • 0
        Я не в рамках холивара, а для сравнения B-Tree и GIST/GIN индексов. Если в MySQL появятся/существуют оные, я только за.

        А что, MySQL использует именно только первый столбец? Я думал, B-Tree в общем случае подразумевает возможность использования любой последовательности столбцов с начала (как я и упомянул).
        • 0
          Да, пардон, не верно выразился.
  • 0
    Раз тут знающие люди собрались, можно вопрос задать?

    Все банально: есть таблица, в ней есть FK на другую таблицу, нужно получить связанные значения. Насколько запрос вида `id in (18,20,30)` хуже джоина, и хуже ли он? Предположим, что id нужных связанных объектов уже известны, например, получены другим запросом. Понятно, что 2 запроса хуже, чем один, но тот первый запрос не учитываем, считаем, что id'шники известны нам и так, волшебным образом.
    • +1
      Если ID известны, то лучше без джоина — в слуае джоина он выберет данные из второй таблички а потом сделает тот же «id in (18,20,30)»
    • 0
      Запросы JOIN'ами, как правило, тяжело воспринимаются оптимизаторами БД, и MySQL — совсем не исключение :). Объясняется это тем, что ему приходится просматривать много возможных комбинаций индексов, которые ему предстоит использовать (ведь и таблиц же несколько...). Поэтому, в зависимости от количества записей и расстановки индексов, это может быть как быстрее, так и медленней. Если Вы работаете с огромными массивами данных, я бы предпочёл не испытывать судьбу и обойтись простыми запросами, пусть и бОльшим их количеством.

      Я слышал, что в SpyLog так вообще использование JOIN запрещено :).
      • 0
        Т.е. если (допустим) из огромной базы постов надо выбрать 1 пост, а потом комменты к нему из 2-й таблицы — выбголнее делать 2 простых запроса, чем один с джойном и WHERE post_id = N? Не бред ли? Неужели JOIN так неоптимален?
        • 0
          Если пост один, а комментов много, то да, ИМХО, лучше 2 запроса. Почему бред-то? JOIN — одна из самых труднооптимизируемых операций во всех СУБД. И чем больше записей, тем хуже.

          В общем, чего мне Вам рассказывать — попробуйте сами :). Может, на Вашем наборе данных всё будет наоборот. Тут не угадаешь…
        • +1
          А как вы собрались сделать один запрос? Получив ряды состоящие из идентичной части полей поста и меняющейся части комментариев? Ну т.е.

          PostID | PostText | CommentID | CommentText

          И так все строки, сколько есть комментариев? Конечно так будет намного медленнее.
          • 0
            Ну смотрите, мне казалось:

            1) одним запросом — MySQL выбирает данные поста, затем из второй таблицы комменты к нему (допустим ищет по индексу), и возвращает все это. С друго стороны, если MySQL сначала составит огромную объединенную таблицу «все посты— все комменты», то это конечно лежать будет ((

            2) 2 запроса — всяко займут времени больше чем один, т.к. накладные расходы на передачу/обработку запроса/анализ и прочее в 2 раза больше :)

            Ну ок. если что потом сгенерирую пару огромных таблиц и проверю, я чувствую тут по другому не проверить.
            • +1
              Накладные расходы занимают куда меньше времени, чем возврат присоединённого к каждой строке текста сообщения. Поэтому решение в таких случаев вполне естественное: сначала извлечь данные о посте, затем данные о комментариях.
              • 0
                Ну вот, а я уже начал придумывать хитрый модуль для работы с таблицами и автоматической генерацией джойнов, а оказалось все намного проще!
      • 0
        А как на счет варианта с подзапросом в IN()?

        SELECT c.* FROM comments c WHERE c.id IN ( SELECT comments_id FROM blabla WHERE blabla.id = 1)
        ?
        • 0
          Создаётся временная таблица… Не думаю, что это сильно лучше.
          • 0
            Если количество результатов разумное и влезает в HEAP (размер которого задаётся в настройках MySQL), то вариант не так уж и плох.
            • 0
              Ну-ну.

              Погуглите про подзапросы в MySQL.

              Потом на реальных данных верещать начинают, почему у меня запрос 5 мин. выполняется?!!!

              Он же такой быстрый был. И эксплайн такой красивый :-(

              Про коммент ниже. С константами там действительно все Ок.
              • 0
                т.е. подзапрос получается менее выгоден, чем джойн и два отдельных запроса?
                • 0
                  Не все и не всегда однозначно.

                  Нужно просто понимать как работают подзапросы в MySQL.
                  Проблема в том, что оптимизатор в очень многих случаях считает " c.id IN " неконстантным значением и выполняет подзапрос не один, а столько раз, сколько посчитает нужным. Причем эксплайн напишет — все замечательно, индексы правильные и все тип-топ. Вот только не видно сколько раз будет выполнен подзапрос.
                  На тестах, когда данных немного и нет реальных нагрузок — это незаметно, но на продакшнах проблемы могут повылазить и не по детски.

                  В общем случае лучше уходить к джойну. Хотя он тот еще не сахар.

                  В идеале лучшим вариантом мог бы быть подзапрос гарантированно выполняющийся один раз и отдающий набор констант в IN (бла, бла, бла).

                  Ну а дальше все просто. Я это для себя так решил
                  sql.ru/forum/actualthread.aspx?tid=650047&hl=%ec%e5%f1%fc%e5

                  Заворачиваю в ХП.
                  Выполняю подзапрос, его результат через GROUP_CONCAT в переменную, что-то типа
                  SELECT GROUP_CONCAT(fld) FROM table INTO myvar;

                  Собираю строку запроса
                  @query = CONCAT('SELECT fld1 FROM table1 WHERE fld2 IN (', myvar, ')';

                  Препарирую @query и выполняю его.
                  В IN константа, и оптимизатор не дуркует.

                  Грабли там только одни, счас не помню, но по моему при NULL или пустой строке в конкатенируемом поле, результат GROUP_CONCAT может выглядеть типа ",,,77,2". Ну и соответственно ошибка при выполнении.
    • 0
      Мануал гласит о том, что IN это очень быстрая функция при работе с константами в списке поиска.

      dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
      • 0
        Да, сейчас «трендово» брать списки у Sphinx'а, а потом делать выборку WHERE… IN :)
  • +2
    Также обратите внимание на то, что выполнение запроса заняло в 5 раз больше времени несмотря на то, что FULL SCAN прошёл примерно в 50 раз больше строк.


    Доступ к Б-деревьям, медленней, потому что для чтения даже одного значения из блока нужно просматривать пару килобайт данных (размер блока). Зато само время доступа падает как логарифм от количества записей по основанию, которое зависит от ветвистости дерева (которая может быть и переменной..). Но, как правило, количество ветвей будет порядка 100.

    Таким образом мы видим, что mySQL может решить использовать пересечение индексов даже в том случае, если это худший вариант, хотя с технической точки зрения это, конечно, будет лучший план, учитывая, что другой статистики у него нет.


    Вообще, я заметил, что MySQL, даже последних версий, не всегда правильно выбирает индексы, которые нужно использовать, особенно в случае составных индексов :). Поэтому в запросах, которые работают с составными индексами, крайне желательно писать FORCE INDEX(`combined`), а то производительность может отличаться в произвольное число раз, например в 50 :).
  • +2
    $topic = strtr($topic, array('mySQL' => 'MySQL'));
    
    • +1
      да, мне даже сначала показалось, что топик про mSQL
  • +2
    Намного лучшей стратегией является создание комбинированного индекса
    Скажите это разработчикам NetCat. Уже которую неделю они «тестируют» наше предложение внести совместный индекс на поля, участвующие в ключевых запросах системы.
  • 0
    Кстати, раз уж все здесь. Скажите, оптимизатор достаточно умен, чтобы считать «IN (1)» как "= 1" и использовать составной индекс?
    • 0
      Я бы это делал в разделе с упрощением WHERE выражения, а не вводил такую логику в оптимизатор:

      dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

      По ссылке выше об оптимизации, о которой Вы говорите, не написано, зато написано, что
      MySQL performs a great many optimizations, not all of which are documented here.

      Так что, возможно, он и делает эту оптимизацию :). А может и нет. Попробуйте посмотреть исходный код (в принципе, я думаю, достаточно посмотреть насчёт упоминания об этой оптимизации в комментариях :)).
  • 0
    1. в целях сравниловки думаю следует указать мощности, на которых производилось исследование.
    2. я так понимаю статья как намек на высокопроизводительные системы — давай пиши про вставку/удаление/перезапись на подобных таблицах, будет оч полезно почитать
  • 0
    Спасибо за исследование. Интуитивно понимал, но не проверял
  • –1
    Блять.

    Я уже насчитал как минимум два действительно умных комментария, которые бы я вряд ли где бы то ни было смог получить.

    Спасибо, Хабр.
  • 0
    спасибо за статью!
    интересно было бы почитать про инсерты/апдейты, и получить еще рекомендаций по построению индексов етц
  • 0
    Я бы ещё добавил что разумный предел — 3-4 колонки для составных индексов. Больше — либо что то надо подумать в структуре бд (ибо если одни и те же WHERE с 4 колонками делаются — это как раз хорошее поле для оптимизации). Во вторых размер индекса будет большой и в третьих его регенирация при изменении данных будет весьма ощутима.

    Например если есть 10 колонок по которым в разнобой делается выборка, то можно сделать несколько индексов на 2-3 колонках таких, которые будут оставлять как можно меньше данных. Т.е. это должны быть колонки где наиболее разношерстные данные. Таким образом если всего 1 млн записей, то после такого индекса на filesort будет оставаться, например, 100 при любой комбинации.

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