В чем разница между сканированием таблицы и сканированием кластерного индекса?

Поскольку и Table Scan, и Clustered Index Scan по существу сканируют все записи в таблице, почему сканирование кластерного индекса предположительно лучше?

В качестве примера - какая разница в производительности между следующими, когда есть много записей?:

declare @temp table(
    SomeColumn varchar(50)
)

insert into @temp
select 'SomeVal'

select * from @temp

-----------------------------

declare @temp table(
    RowID int not null identity(1,1) primary key,
    SomeColumn varchar(50)
)

insert into @temp
select 'SomeVal'

select * from @temp

sql sql-server indexing
person Seibar    schedule 20.08.2008    source источник

Ответы (3)


arrow_upward
80
arrow_downward

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

Однако в кластеризованной таблице страницы данных связаны двусвязным списком - сделать последовательное сканирование немного быстрее. Конечно, взамен вам придется поддерживать порядок страниц данных на INSERT, UPDATE и DELETE. Однако таблица кучи требует второй записи в IAM.

Если в вашем запросе есть оператор RANGE (например: SELECT * FROM TABLE WHERE Id BETWEEN 1 AND 100), то кластеризованная таблица (находящаяся в гарантированном порядке) будет более эффективной, поскольку она может использовать страницы индекса для поиска соответствующих страниц данных. Куча должна была бы сканировать все строки, так как она не может полагаться на порядок.

И, конечно же, кластеризованный индекс позволяет вам выполнять CLUSTERED INDEX SEEK, который в значительной степени оптимален для производительности... куча без индексов всегда приводит к сканированию таблицы.

So:

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

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

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

  • Для INSERT, UPDATE и DELETE куча может выиграть или не выиграть. Куча не должна поддерживать порядок, но требует второй записи в IAM. Я думаю, что относительная разница в производительности будет незначительной, но также сильно зависит от данных.

У Microsoft есть технический документ, в котором кластеризованный индекс сравнивается с эквивалентным не -кластеризованный индекс в куче (не совсем то, что я обсуждал выше, но близко). Их вывод в основном состоит в том, чтобы поместить кластеризованный индекс во все таблицы. Я сделаю все возможное, чтобы обобщить их результаты (опять же, обратите внимание, что они действительно сравнивают некластеризованный индекс с кластеризованным индексом здесь, но я думаю, что это относительно сопоставимо):

  • INSERT производительность: кластеризованный индекс выигрывает примерно на 3% из-за второй записи, необходимой для кучи.
  • UPDATE производительность: кластеризованный индекс выигрывает примерно на 8% из-за второго поиска, необходимого для кучи.
  • DELETE производительность: кластеризованный индекс выигрывает примерно на 18% из-за необходимости второго поиска и второго удаления из IAM для кучи.
  • одиночная производительность SELECT: кластеризованный индекс выигрывает примерно на 16% из-за второго поиска, необходимого для кучи.
  • производительность диапазона SELECT: кластеризованный индекс выигрывает примерно на 29% из-за случайного упорядочения кучи.
  • concurrent INSERT: таблица кучи выигрывает на 30% под нагрузкой из-за разделения страниц для кластеризованного индекса.
person Mark Brackett    schedule 20.08.2008
comment
Именно этот вопрос крутился у меня сегодня в голове. Спасибо @Terrapin за вопрос и спасибо @Marc за такой хороший ответ! - person peakit; 01.05.2011
comment
MS Exam 70461 Querying Microsoft SQL Server 2012 — глава 15 Урок 1 подробно описывает использование. - person Shin Kazama; 14.01.2015
comment
Кажется, я не могу получить предполагаемый импульс, указанный в этом вашем утверждении: для запроса с предложением WHERE, который может быть (по крайней мере частично) удовлетворен кластеризованным индексом, вы выйдете вперед из-за порядка - так вам не придется сканировать всю таблицу. У меня есть таблица из 10 миллионов строк. SELECT Id FROM Customer WHERE Id › X выполняется за одно и то же время независимо от того, есть ли у меня кластеризованный индекс по Id или нет. Почему? Я вижу, как он меняется от сканирования таблицы к сканированию кластерного индекса. - person Mattias Nordqvist; 30.04.2015
comment
@MattiasNordqvist - Если вы просто смотрите на затраченное время, вы делаете это неправильно. Из-за кэширования, одновременного доступа, процессорного и дискового времени и т. д. сложно настроить MS-SQL только на время. Загляните в SET STATISTICS IO ON, чтобы проверить чтение с диска, откуда и будет прирост. Во-вторых, это будет зависеть от количества возвращаемых строк — если это достаточно высокий процент, оптимизатор может вместо этого выбрать чтение + фильтр. - person Mark Brackett; 30.04.2015

arrow_upward
4
arrow_downward

http://msdn.microsoft.com/en-us/library/aa216840(SQL.80).aspx

Логический и физический оператор Clustered Index Scan сканирует кластеризованный индекс, указанный в столбце Argument. При наличии необязательного предиката WHERE:() возвращаются только те строки, которые удовлетворяют этому предикату. Если столбец Argument содержит предложение ORDERED, обработчик запросов запросил, чтобы выходные данные строк были возвращены в том порядке, в котором они были отсортированы кластеризованным индексом. Если предложение ORDERED отсутствует, механизм хранения будет сканировать индекс оптимальным образом (не гарантируя сортировки вывода).

http://msdn.microsoft.com/en-us/library/aa178416(SQL.80).aspx

Логический и физический оператор Table Scan извлекает все строки из таблицы, указанной в столбце Argument. Если в столбце Аргумент появляется предикат WHERE:(), возвращаются только те строки, которые удовлетворяют этому предикату.

person Stu    schedule 20.08.2008

arrow_upward
-2
arrow_downward

Сканирование таблицы должно проверять каждую строку таблицы. Сканирование кластеризованного индекса требует только сканирования индекса. Он не сканирует каждую запись в таблице. В этом и смысл индексов.

person DrPizza    schedule 20.08.2008
comment
-1 Это неправильно. Конечным уровнем кластеризованного индекса является таблица. - person Martin Smith; 14.02.2014