Составные индексы, ключевое слово «Включить» и как они работают

В SQL Server (и большинстве других реляционных баз данных) «составной индекс» — это индекс с несколькими ключами. Допустим, у нас есть этот запрос, который часто выполняется, и мы хотим создать покрывающий индекс для этого запроса, чтобы ускорить его;

SELECT a, b FROM MyTable WHERE c = @val1 AND d = @val2

Это все возможные составные индексы, которые охватывают этот запрос;

CREATE INDEX ix1 ON MyTable (c, d, a, b)
CREATE INDEX ix2 ON MyTable (c, d) INCLUDE (a, b)
CREATE INDEX ix3 ON MyTable (d) INCLUDE (a, b, c)
CREATE INDEX ix4 ON MyTable (c) INCLUDE (a, b, d)

Но, видимо, они не работают одинаково. По словам Эрлана Соммарскога (Microsoft MVP), первые два быстрее 3-го и 4-го, а 4-й быстрее 3-го.

Он продолжает объяснять;

ix2 — «лучший» индекс, потому что a и b не будут занимать место на более высоких уровнях дерева индексов. Кроме того, если a или b обновляются, в ix2 не может быть разделения страниц и т.п., поскольку дерево индекса не затрагивается.

Однако мне трудно понять, что именно происходит. У меня есть общие знания об индексах b-tree и о том, как они работают, но я не понимаю логики составных ключей. Например;

CREATE INDEX ix1 ON MyTable (c, d, a, b)

Порядок столбцов здесь имеет значение? Если да, то почему? Также;

CREATE INDEX ix2 ON MyTable (c, d) INCLUDE (a, b)

В чем разница между этим составным ключом и приведенным выше? Я не понимаю, какая разница, "ВКЛЮЧИТЬ".

Примечание. Я знаю, что на Composite Keys есть много сообщений, но я считаю, что мои последние два вопроса достаточно конкретны, чтобы не повторяться.

Индексируемые столбцы используются для создания b-дерева, поэтому в первом примере все 4 столбца являются частью индекса. Включенные столбцы просто означают добавление к столбцам, которые индексируются, поэтому дополнительные данные включаются на конечный уровень, что позволяет избежать необходимости выполнять ключевой поиск для получения информации не в индексе. Итак, для примера, который вы привели, у вас будет немного более быстрый поиск, потому что b-дерево состоит всего из 2 столбцов, и быстрый возврат данных, потому что поиск ключа не требуется.

Dale K 08.02.2023 08:41

Вы увидите это намного яснее, если настроите эти примеры и проверите планы выполнения.

Dale K 08.02.2023 08:43

Отсортированные столбцы являются только ключевыми

Bogdan Sahlean 08.02.2023 08:51

@DaleK спасибо за ваш комментарий, я понимаю «смысл» составных ключей, я просто пытаюсь понять, как именно они работают. Не могли бы вы ответить на мои вопросы?

Mephisto 08.02.2023 08:57

@BogdanSahlean Я не понимаю, что вы подразумеваете под «отсортированными столбцами являются только ключевые»

Mephisto 08.02.2023 08:58

Я не знаю, что вы имеете в виду под "как они работают"? Но вы говорите, что понимаете, как работает b-дерево? Что ты не понимаешь?

Dale K 08.02.2023 09:00

Один ключ, несколько столбцов.

jarlh 08.02.2023 09:33

Если вы напишете несколько запросов на выборку, которые возвращают столбцы в столбцах «ключи» и «включает» и отсортируете по ключам, вы увидите, как именно выглядит индекс. Ключи индекса отсортированы, а включенные столбцы — нет. Если в таблице 100 строк и c является уникальным числом от 1 до 100, то наличие d в индексе бесполезно, поскольку оно полезно только как средство разрешения конфликтов для повторяющихся значений c, которых не было бы.

Stu 08.02.2023 09:42
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
8
65
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

CREATE INDEX ix1 ON MyTable (c, d, a, b)

Порядок столбцов здесь имеет значение? Если да, то почему? Также;

Да, порядок очень важен при создании индекса, потому что каждый столбец - это (слева) следующий уровень глубины в индексе, поэтому, чтобы определить компилятор для использования этого индекса, вам всегда нужно искать c, который является "открывателем" этого набора.

CREATE INDEX ix2 ON MyTable (c, d) INCLUDE (a, b)

В чем разница между этим составным ключом и приведенным выше? Я не понимаю, какая разница, "ВКЛЮЧИТЬ".

Но имейте в виду, что для каждого уровня индекса он становится менее эффективным, поэтому, если вы знаете, что > 80% ваших запросов будут искать только по c и d, а не по a и b, но вам понадобится эта информация в вашем SELECT (ни в WHERE), вы должны ВКЛЮЧИТЬ их как часть листа на последнем уровне индекса.

Есть лучшие объяснения, чем мои, так что не стесняйтесь смотреть на них:

ВКЛЮЧИТЬ эквивалент в Oracle -> ВКЛЮЧИТЬ Насколько важен порядок столбцов в индексах? -> ЗАКАЗАТЬ в наборе ИНДЕКС

Ответ принят как подходящий

Порядок столбцов здесь имеет значение?

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

CREATE INDEX ix1 ON MyTable (c, d, a, b);
CREATE INDEX ix2 ON MyTable (c, d) INCLUDE (a, b);
CREATE INDEX ix3 ON MyTable (d, c, a, b);
CREATE INDEX ix4 ON MyTable (d, c, b, a);
CREATE INDEX ix5 ON MyTable (d, c) INCLUDE (a, b);

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

Еще одно соображение относительно порядка ключей, которое может противоречить приведенным выше общим рекомендациям, — это когда индекс поддерживает разные запросы и указаны только некоторые из ключевых столбцов (например, SELECT a, b FROM MyTable WHERE d = @val2;). В этом случае было бы лучше указать d как крайний левый столбец независимо от селективности, чтобы позволить одному индексу оптимизировать несколько запросов вместо создания отдельного индекса для оптимизации второго запроса.

В чем разница между этим составным ключом и приведенным выше? я не понимаю, какая разница "ВКЛЮЧИТЬ".

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

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

Mephisto 08.02.2023 14:27

@Lilith, для запроса в вашем вопросе либо ix1, либо ix2 обеспечат одинаковую производительность выбора. Преимущество включенных столбцов ix2 по сравнению с ключевыми столбцами ix1 заключается в том, что они позволяют избежать накладных расходов на поддержку дополнительных столбцов в B-дереве во время вставок, обновлений и удалений, а также немного меньше места. Я бы выбрал idx2, если у вас нет других запросов, которые выиграли бы от idx1 (например, также столбец a в предложении WHERE).

Dan Guzman 08.02.2023 14:41

Другие вопросы по теме