Разница между двумя индексами со столбцами, заданными в обратном порядке

Есть ли различия между следующими двумя индексами?

  • IDX_IndexTables_1
  • IDX_IndexTables_2

Если таковые имеются, каковы различия?

create table IndexTables (
    id int identity(1, 1) primary key,
    val1 nvarchar(100),
    val2 nvarchar(100),
)

create index IDX_IndexTables_1 on IndexTables (val1, val2)
GO

create index IDX_IndexTables_2 on IndexTables (val2, val1)
GO
Источник

5 ответов

Решение

Да. Есть разница.

Сводный указатель IDX_IndexTables_1 может быть использован для любого запроса, где val1 столбец используется в предложении where.

Сводный указатель IDX_IndexTables_2 может быть использован для любого запроса, где val2 столбец используется в предложении where.

Так, например IDX_IndexTables_2 нельзя использовать для этого запроса (но можно использовать IDX_IndexTables_1):

SELECT val1, val2 FROM IndexTables
WHERE val1 = some_value

но может быть использован для этого запроса:

SELECT val1, val2 FROM IndexTables
WHERE val2 = some_value AND val1 = some_other-value

Способ думать о составном индексе - думать о бумажном телефонном справочнике; Он индексируется по столбцу фамилии, а затем по столбцу имени: вы можете искать по фамилии, но не по имени.

Источник

Индекс, состоящий из нескольких столбцов, концептуально не отличается от того, как брать все поля столбцов и объединять их вместе - индексировать результат как одно поле.

Поскольку индексы являются b-деревьями, они всегда ищутся слева направо. Вы должны начать свой поиск слева, чтобы объединить в пару результаты по мере перемещения вправо, чтобы индекс выполнял свою работу и предоставлял полезные результаты.

Только с одним индексированным полем:

WHERE val1 LIKE 'myvalue%' (uses index)
WHERE val1 LIKE '%myvalue' (cannot use index)

Та же концепция применяется для многостолбцовых индексов:

Когда порядок равен val1,val2

WHERE val1='value1' (uses index)
WHERE val2='value2' (cannot use index)

Когда порядок равен val2,val1

WHERE val1='value1' (cannot use index)
WHERE val2='value2' (uses index)

Если оба поля совпадают, порядок индексов в этом случае не имеет значения.

WHERE val1='value1' AND val2='value2' (uses index in any order)
Источник

У вас есть составной индекс. Порядок важен, когда ваше предложение WHERE не использует все столбцы в составном индексе.

Рассмотрим этот запрос:

SELECT val1
FROM IndexTables
WHERE val1 = 'MyValue'

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

IDX_IndexTables_1 (val1, val2): чтение слева направо val1 существует, и это наш единственный столбец, поэтому этот индекс будет рассматриваться

IDX_IndexTables_2 (val2, val1): Чтение слева направо val2 не существует в этом запросе, поэтому он не будет использоваться.

Источник

Предыдущие ответы описывают, как использовать первый столбец каждого индекса. (в предложении where).

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

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

SELECT val2 from IndexTables where val1 = @someVal1

Аналогично, обратный индекс оптимизирует этот запрос:

SELECT val1 from IndexTables where val2 = @someVal2

Однако только один (неважно, какой) из двух индексов необходим для оптимизации следующего запроса:

SELECT val1, val2 from IndexTables where val1 = @someVal1 and val2 = @someVal2

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

Источник

Другие люди ответили, что они разные, и я согласен.

Я добавлю некоторые другие мысли, хотя...

  • индекс (col1, col2) означает, что вам не нужен индекс только для col1
  • индекс (col2, col1) означает, что вам не нужен индекс только для col2
  • порядок имеет значение, если это распространяется (например, ГДЕ на col1, SELECT col2)
  • Направление (ASC/DESC) также имеет значение ( Другой вопрос 1, Другой вопрос 2)
Источник
Другие вопросы по тегам