Влияет ли порядок столбцов индекса в (у меня индекс из 6 столбцов) на скорость?
У меня есть таблица с 6 миллионами строк, мне нужно найти шесть столбцов, чтобы выбрать элементы. Итак, я создал индекс с шестью столбцами.
Влияет ли изменение порядка столбцов в индексе на скорость запроса?
Зависит ли скорость запроса от количества уникальных элементов в каждом столбце? или общее количество возможных результатов, которые имеет условие в столбце?
Я попытался переупорядочить столбцы в индексе, и, похоже, произошло значительное изменение скорости запроса.
таблица: pic_tag_relations
Столбцы:
- tag_id (int) (1-1000000) (количество элементов: наименьшее)
- контраст_скоре (с плавающей запятой) (0-24)
- quality_score (с плавающей запятой) (0-10)
- pic_ratio_type (tinyint) (0/1/2)
- is_okay (крошечный) (0/1)
- уже_использованное_count (целое число) (1-10)
Запрос:
select * from pic_tag_relations where tag_id in ($all_tag_ids) && contrast_score>2.5 && quality_score>3 && is_okay=1 && pic_ratio_type='2' && already_used_count<10
Да, эффект действительно сильный. Пожалуйста, добавьте к вопросу запросы, которые вы будете выполнять.
Я бы сказал, что более конкретные поля должны появляться в индексе первыми (но некоторые могут не согласиться).
Сначала столбцы с более высокой «селективностью».
@Barmar Не могли бы вы объяснить почему?
Моим первым побуждением было сказать: индекс - это индекс, независимо от того, как он был построен. Но руководство просит отличаться: «Если вы укажете столбцы в правильном порядке в определении индекса, один составной индекс может ускорить выполнение нескольких видов запросов к одной и той же таблице».
@TimBiegeleisen Я придерживаюсь того же мнения, что и вы, сначала отфильтруйте большинство результатов ... Я бы хотел прочитать, чтобы кто-нибудь объяснил это лучше, чем я представлял
@KIKOSoftware «несколько видов запросов» относится к запросам, которые определяют разные наборы столбцов для сопоставления.
@Barmar: да, верно, но в нем говорится, что порядок столбцов имеет значение.
@KIKOSoftware Потому что запрос, который пропускает некоторые столбцы в порядке, не сможет воспользоваться полным индексом. Но вопрос в запросах, в которых используются все столбцы.
@Barmar: Я знаю об этом.
@KIKOSoftware порядок индекса должен соответствовать порядку столбцов, упомянутых в запросе?
MySQL работает не так. Сначала он разбивает запросы на соответствующие части, а затем выбирает наилучший порядок столбцов. Это вполне может зависеть от порядка столбцов в вашем индексе. Конечно, свобода выбора зависит от конкретного запроса.
@KIKOSoftware да, это я тоже понимаю, спасибо за разъяснения. Как сказал Бармар, руководство, возможно, относится к использованию первых (1/2/3) столбцов, используемых в индексе, вместо того, чтобы пропускать порядок и запрашивать столбцы между ними в порядке индекса
@NicoHaase да, у меня такой же вопрос, больше столбцов в индексе, я ищу ответы, спасибо
@NicoHaase Этот вопрос значительно отличается от индекса из двух столбцов, к которому обращается официальное руководство MySQL, он также использует несколько операторов, что стоит оставить как отдельный вопрос
Ответы 2
Порядок столбцов в индексе влияет на то, какие запросы можно использовать. Если все условия равны, то порядок не имеет большого значения.
Между ключами с большим количеством значений и ключами с небольшим количеством значений может быть небольшая разница. Однако я бы не стал беспокоиться о разнице в производительности при оптимальном использовании индекса.
Гораздо важнее, чтобы столбцы в индексе с условиями равенства были первыми столбцами (в любом порядке), чтобы индекс использовался эффективно.
Ах, понятно, спасибо, это определенно помогает. Я выполняю 3 сравнения (<условие) в запросе. Следует ли поместить эти столбцы в конец индекса?
@Mrpoppins. . . Столбцы с условиями равенства должны быть первыми. Затем следует самый строгий из столбцов >.
Я ответил примерно на тот же вопрос здесь: Имеет ли значение порядок полей многостолбцового индекса в MySQL
Да, порядок столбцов имеет значение. Чтобы выбрать правильный порядок столбцов, вы должны иметь в виду конкретный запрос. Вы еще не указали запрос в этом вопросе.
Если все условия вашего запроса - это сравнения =, то порядок столбцов не имеет большого значения. Некоторые люди пытаются выполнить микрооптимизацию, помещая более селективные столбцы слева, но я считаю, что это не имеет существенного значения.
Например, если вы ищете кого-то в телефонной книге по его полному имени, разве вас действительно волнует, отсортирована ли книга по фамилии или имени? В любом случае вы собираетесь фильтровать оба поля.
Но если у вас есть сочетание сравнений = и других типов операторов сравнения, то порядок столбцов действительно имеет значение.
В принципе, у вас может быть столько терминов =, сколько вы хотите, и столбцы для этих сравнений должны находиться в левом конце списка столбцов в индексе. Тогда вы можете иметь столбец ОДИН в индексе для сравнения неравенства. Любые другие столбцы, в которых вы выполняете поиск, не будут использовать индекс.
Вы упомянули в комментарии, что у вас есть запрос с 3 сравнениями с <. Как это:
... WHERE a < 1 AND b < 2 AND c < 3
Это может использовать индекс не более чем для одного из этих столбцов. В этом случае вы должны выбрать условие, которое будет наиболее избирательным, т.е. которое будет соответствовать наименьшему подмножеству строк.
Если используются термины =:
... WHERE a = 1 AND b = 2 AND c = 3
Тогда все столбцы многостолбцового индекса на (a, b, c) могут помочь в поиске, и порядок столбцов не будет иметь значения.
Если у вас было несколько сравнений:
... WHERE a = 1 AND b < 2 AND c < 3
Тогда у вас может быть указатель с a в крайнем левом столбце и один из b или c после него. Но если бы у вас были и b, и c, для поиска по-прежнему использовался бы только один - первый, следующий за a.
См. Мою презентацию Как правильно разрабатывать индексы или видео.
Повторите свой обновленный вопрос с помощью примера запроса:
select * from pic_tag_relations where tag_id in ($all_tag_ids) && contrast_score>2.5 && quality_score>3 && is_okay=1 && pic_ratio_type='2' && already_used_count<10
У вас может быть индекс с (is_okay, pic_ratio_type) в качестве первых столбцов, а затем вы можете выбрать, какой из других столбцов должен быть третьим столбцом. Ссылки на другие столбцы используются в терминах, в которых используется какой-либо оператор сравнения, отличный от =. Выбор лучшего столбца будет зависеть от того, насколько хорошо каждое условие может выбрать меньшее подмножество строк.
Другие термины также будут отфильтровывать строки, но не с помощью индекса. Им придется сравнивать строки одну за другой, поскольку они возвращаются индексированным поиском.
Спасибо, у меня есть сочетание операций равенства и сравнения. Я еще не смотрел видео. на «тогда порядок столбцов действительно имеет значение». Я предполагаю, что вы имеете в виду, что столбцы операции " = " должны быть помещены слева (первыми в индексе)
Я обновил свой ответ еще несколькими примерами.
Спасибо. Это, безусловно, помогает, я уменьшу количество столбцов в индексе.
Вероятно, лучше всего сначала иметь индекс с наивысшей мощностью.