В чем разница между созданием индекса по 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, теперь я должен индексировать каждый по отдельности (один для UserName, один для ApplicationType) или индексировать их в целом (один индекс по UserName, ApplicationType вместе)? Есть ли ограничение на количество индексов, которые я могу иметь, прежде чем делать это плохой практикой? Какое общее правило на этот счет?
Спасибо,
Рэй.
Ответы 3
Ответ на этот вопрос действительно зависит от того, как вы собираетесь искать в таблице. Если ваш поиск почти всегда будет включать оба столбца, тогда целесообразно создать индекс для обоих столбцов. Если вы будете часто выполнять поиск по каждому полю отдельно, тогда целесообразно создать отдельные индексы для каждого. В конце концов, у вас могут быть все 3 индекса (один составной, 2 одиночных столбца) - в зависимости от того, как вы выполняете поиск по столбцам. Думайте об этом как о телефонной книге - если вы всегда ищете по фамилии и имени, вы найдете то, что ищете. Но если вы хотите найти в телефонной книге всех, у кого есть имя Скотт, вам нужен новый индекс, который не был (LName, FName). Если вы хотите найти всех с заданной фамилией, вы все равно можете сделать это с помощью многостолбцового индекса (LName, FName).
Каждая база данных имеет свои собственные ограничения на количество индексов на таблицу, количество столбцов на индекс и т. д. Они обычно достаточно высоки, и если вы смотрите здесь на 3 индекса, вам не придется о них беспокоиться. Также имейте в виду, что чем больше у вас индексов, тем больше затрат на их обслуживание (вставки, обновления, удаления и т. д.).
IIRC, эмпирическое правило заключается в том, что индекс может использоваться только для поиска, который использует все столбцы с некоторой точки и слева. Например, индекс по столбцам (a, b, c, d) можно использовать, если вы запрашиваете по (a), (a, b), (a, b, c) или (a, b, c, d) но не на (a, c) например.
Это результат того, как построены индексы; индексируется крайний левый столбец, затем для каждого значения этого столбца создается индекс для следующего столбца и так далее.
Обновлено: как указывает BQ, СУБД может сканировать всю часть индекса «a» и выполнять поиск в части «b» (я не знал, что это было сделано на самом деле). Однако это не так быстро, как индекс, который может использовать правило, как описано выше (OTOH может быть быстрее, чем полное сканирование таблицы).
Лично я не думаю, что это следует намеренно использовать. Если перфоманс является достаточным для данного запроса, и вы рассматриваете, какие индексы необходимы, вы также можете указать ему правильные.
Имейте в виду правило телефонной книги для составных индексов: телефонная книга эффективно индексируется по фамилии, имени. Это составной индекс.
Если вы ищете людей с именем «Смит, Джон», то полезно, чтобы имя было частью индекса. Как только вы найдете записи с фамилией «Смит», вы сможете быстро найти «Джон».
Но если вам нужно найти все с именем «Джон», то индексация телефонной книги бесполезна - вам все равно придется искать всю книгу.
Таким образом, составные индексы удобны, если вы выполняете поиск по первому столбцу, указанному в индексе, и, возможно, по второму и т. д. Но если ваш поиск пропускает самые левые столбцы в индексе, он бесполезен для этого поиска.
Это НЕ верно для большинства текущих (и последних) версий большинства СУБД. Часто, если вы ищете что-то в столбце «c», быстрее сканировать индекс, а не всю таблицу. Как всегда, проанализируйте свой план запроса.