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

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

  • 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

Ответы

Ответ 1

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

Составной индекс 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

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

Ответ 2

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

Поскольку индексы являются 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)

Ответ 3

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

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

SELECT val1
FROM IndexTables
WHERE val1 = 'MyValue'

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

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

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

Ответ 4

В предыдущих ответах описывается, как использовать первый столбец каждого индекса. (в предложении 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

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

Ответ 5

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

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

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