В чем разница между созданием индекса по 2 столбцам и индекса по каждому столбцу отдельно?

Я новичок в индексации базы данных, если у меня есть 2 столбца в таблице, которые являются хорошим выбором для индексации, например,

[Posts](    
   [PostID] [int] IDENTITY(1,1) NOT NULL,
   [UserName] [nvarchar](64) NOT NULL,
   [ApplicationType] [smallint] NOT NULL,
   ...
)

в этом случае PostID будет индексом PRIMARY KEY CLUSTERED, тогда я хочу сделать больше индексации, так как это большая таблица, и я хочу сделать это с именем пользователя и типом приложения, теперь я должен индексировать каждый по отдельности (один по имени пользователя, один по типу приложения) или индексировать их целиком (один индекс по UserName, ApplicationType вместе)? Есть ли ограничение на количество индексов, которые я могу иметь, прежде чем это станет плохой практикой? Каково вообще эмпирическое правило по этому поводу?

Спасибо,

Рэй.


database-design indexing
person Ray    schedule 16.12.2008    source источник

Ответы (3)


arrow_upward
8
arrow_downward

Имейте в виду правило телефонной книги для составных индексов: телефонная книга эффективно индексируется по фамилии, имени. Это составной индекс.

Если вы ищете людей по имени «Смит, Джон», полезно, чтобы имя было частью указателя. Как только вы найдете записи с фамилией «Смит», вы сможете быстро найти «Джон».

Но если вам нужно найти всех по имени «Джон», то индексация телефонной книги бесполезна — вам все равно придется искать по всей книге.

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

person Bill Karwin    schedule 16.12.2008

arrow_upward
2
arrow_downward

Ответ на этот вопрос действительно зависит от того, как вы собираетесь искать в таблице. Если ваши поиски почти всегда будут включать оба столбца, то целесообразно создать индекс для обоих столбцов. Если вы будете часто выполнять поиск по каждому полю отдельно, целесообразно создать отдельные индексы для каждого. В конце концов, у вас могут быть все 3 индекса (один составной, 2 отдельных столбца) - в зависимости от того, как вы ищете столбцы. Думайте об этом как о телефонной книге - если вы всегда ищете по фамилии и имени, вы найдете то, что ищете. Но если вы хотите найти в телефонной книге всех с именем Скотт, вам понадобится новый индекс, которого нет (LName, FName). Если вы хотите найти всех с заданной фамилией, вы все равно можете сделать это с помощью многоколоночного индекса (LName, FName).

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

person Scott Ivey    schedule 16.12.2008

arrow_upward
1
arrow_downward

IIRC, эмпирическое правило заключается в том, что индекс может использоваться только для поиска, в котором используются все столбцы с некоторой точки и слева. Например, индекс столбцов (a,b,c,d) можно использовать, если вы запрашиваете (a), (a,b), (a,b,c) или (a,b,c,d). но не на (а, с), например.

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

Изменить: как указывает BQ, СУБД может сканировать всю часть индекса «a» и выполнять поиск в части «b» (я не знал, что это действительно было сделано). Однако это не так быстро, как индекс, который может использовать правило, как описано выше (OTOH может быть быстрее, чем полное сканирование таблицы).

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

person BCS    schedule 16.12.2008
comment
Это НЕ верно для большинства текущих (и последних) версий большинства СУБД. Часто, если вы ищете что-то в этом столбце «c», быстрее выполнить сканирование по индексу, а не по всей таблице. Как всегда, проанализируйте свой план запроса. - person BQ.; 17.12.2008