Производительность некластеризованных индексов для кластеров и кластеризованных индексов

В этом документе 2007 года сравнивается эффективность отдельных операций выбора select/insert/delete/update и выбора диапазона в таблице, организованной как кластеризованный индекс vs, что на таблице, организованной как куча с некластеризованным индексом в тех же ключевых столбцах, что и таблица CI.

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

Одним потенциально интересным случаем, не охватываемым документом, было бы сравнение между некластеризованным индексом в куче по сравнению с некластеризованным индексом в кластерном индексе. В этом случае я ожидал, что куча может даже работать лучше, как только на уровне листа NCI. SQL Server имеет RID, чтобы следовать непосредственно, а не нуждаться в пересечении кластерного индекса.

Кто-нибудь знает о аналогичных формальных испытаниях, которые были проведены в этой области, и если да, то каковы были результаты?

Ответ 1

Чтобы проверить ваш запрос, я создал две таблицы, следующие этой схеме:

7,9 миллиона записей, представляющих информацию о балансе.
поле идентификатора от 1 до 7.9 млн.
числовое поле, группирующее записи в около 500 тыс. групп.

Первая таблица с именем heap получила некластеризованный индекс в поле group. Вторая таблица под названием clust получила кластерный индекс в последовательном поле под названием key и некластеризованный индекс в поле group

Тестирование проводилось на процессоре I5 M540 с двумя гиперпотоковыми ядрами, памятью 4 ГБ и 64-битными окнами 7.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Обновление от 9 марта 2011 г.. Я сделал второй более обширный тест, выполнив следующий .net-код и протоколирование Duration, CPU, Reads, Writes и RowCounts в Sql Server Profiler. (Используемый CommandText будет указан в результатах.)

ПРИМЕЧАНИЕ. ЦП и продолжительность выражаются в миллисекундах

1000 запросов

нулевые запросы ЦП исключаются из результатов

0 затронутых строк исключаются из результатов

int[] idList = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here.
using (var conn = new SqlConnection(@"Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;"))
            {
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from heap where common_key between @id and @id+1000"; 
                    cmd.Parameters.Add("@id", SqlDbType.Int);
                    cmd.Prepare();
                    foreach (int id in idList)
                    {
                        cmd.Parameters[0].Value = id;

                        using (var reader = cmd.ExecuteReader())
                        {
                            int count = 0;
                            while (reader.Read())
                            {
                                count++;
                            }
                            Console.WriteLine(String.Format("key: {0} => {1} rows", id, count));
                        }
                    }
                }
            }

Конец обновления 9 марта 2011 года.

Производительность SELECT

Для проверки номеров элементов выполнения я выполнил следующие запросы один раз в таблице кучи и один раз в таблице кластеров:

select * from heap/clust where group between 5678910 and 5679410
select * from heap/clust where group between 6234567 and 6234967
select * from heap/clust where group between 6455429 and 6455729
select * from heap/clust where group between 6655429 and 6655729
select * from heap/clust where group between 6955429 and 6955729
select * from heap/clust where group between 7195542 and 7155729

Результаты этого теста для heap:

rows  reads CPU   Elapsed 
----- ----- ----- --------
1503  1510  31ms  309ms
401   405   15ms  283ms
2700  2709  0ms   472ms
0     3     0ms   30ms
2953  2962  32ms  257ms
0     0     0ms   0ms

Обновление от 9 марта 2011 года: cmd.CommandText = "select * from heap where group between @id and @id+1000";

721 Строки имеют > 0 CPU и влияют на более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    6368         -         
Cpu            15        374      37   0.00754
Reads        1069      91459    7682   1.20155
Writes          0          0       0   0.00000
Duration   0.3716   282.4850 10.3672   0.00180

Окончание обновления 9 марта 2011 г..

для таблицы clust результаты:

rows  reads CPU   Elapsed 
----- ----- ----- --------
1503  4827  31ms  327ms
401   1241  0ms   242ms
2700  8372  0ms   410ms
0     3     0ms   0ms
2953  9060  47ms  213ms
0     0     0ms   0ms

Обновление от 9 марта 2011 года: cmd.CommandText = "select * from clust where group between @id and @id+1000";

721 Строки имеют > 0 CPU и влияют на более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    6056         -
Cpu            15        468      38   0.00782
Reads        3194     227018   20457   3.37618
Writes          0          0       0       0.0
Duration   0.3949   159.6223 11.5699   0.00214

Окончание обновления 9 марта 2011 г..

ВЫБЕРИТЕ С РАБОТОЙ JOIN

cmd.CommandText = "select * from heap/clust h join keys k on h.group = k.group where h.group between @id and @id+1000";

Результаты этого теста для heap:

873 Строки имеют > 0 CPU и влияют более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1009       4170    1683         -
Cpu            15         47      18   0.01175
Reads        2145       5518    2867   1.79246
Writes          0          0       0   0.00000
Duration   0.8215   131.9583  1.9095   0.00123

Результаты этого теста для clust:

865 Строки имеют > 0 CPU и влияют более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       4143    1685         -
Cpu            15         47      18   0.01193
Reads        5320      18690    8237   4.97813
Writes          0          0       0   0.00000
Duration   0.9699    20.3217  1.7934   0.00109

Производительность UPDATE

Вторая партия запросов - это операторы обновления:

update heap/clust set amount = amount + 0 where group between 5678910 and 5679410
update heap/clust set amount = amount + 0 where group between 6234567 and 6234967
update heap/clust set amount = amount + 0 where group between 6455429 and 6455729
update heap/clust set amount = amount + 0 where group between 6655429 and 6655729
update heap/clust set amount = amount + 0 where group between 6955429 and 6955729
update heap/clust set amount = amount + 0 where group between 7195542 and 7155729

результаты этого теста для heap:

rows  reads CPU   Elapsed 
----- ----- ----- -------- 
1503  3013  31ms  175ms
401   806   0ms   22ms
2700  5409  47ms  100ms
0     3     0ms   0ms
2953  5915  31ms  88ms
0     0     0ms   0ms

Обновление от 9 марта 2011 года: cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000";

811 Строки имеют > 0 CPU и влияют на более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    5598       811         
Cpu            15        873      56   0.01199
Reads        2080     167593   11809   2.11217
Writes          0       1687     121   0.02170
Duration   0.6705   514.5347 17.2041   0.00344

Окончание обновления 9 марта 2011 г..

результаты этого теста для clust:

rows  reads CPU   Elapsed 
----- ----- ----- -------- 
1503  9126  16ms  35ms
401   2444  0ms   4ms
2700  16385 31ms  54ms
0     3     0ms   0ms 
2953  17919 31ms  35ms
0     0     0ms   0ms

Обновление от 9 марта 2011 года: cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000";

853 Строки имеют > 0 CPU и влияют более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    5420         -
Cpu            15        594      50   0.01073
Reads        6226     432237   33597   6.20450
Writes          0       1730     110   0.01971
Duration   0.9134   193.7685  8.2919   0.00155

Окончание обновления 9 марта 2011 г..

Тесты DELETE

третья партия запросов, которые я запускал, - это команды delete

delete heap/clust where group between 5678910 and 5679410
delete heap/clust where group between 6234567 and 6234967
delete heap/clust where group between 6455429 and 6455729
delete heap/clust where group between 6655429 and 6655729
delete heap/clust where group between 6955429 and 6955729
delete heap/clust where group between 7195542 and 7155729

Результат этого теста для heap:

rows  reads CPU   Elapsed 
----- ----- ----- -------- 
1503  10630 62ms  179ms
401   2838  0ms   26ms
2700  19077 47ms  87ms
0     4     0ms   0ms
2953  20865 62ms  196ms
0     4     0ms   9ms

Обновление от 9 марта 2011 года: cmd.CommandText = "delete heap where group between @id and @id+1000";

724 Строки имеют > 0 CPU и влияют более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts     192      69788    4781         -
Cpu            15        499      45   0.01247
Reads         841     307958   20987   4.37880
Writes          2       1819     127   0.02648
Duration   0.3775  1534.3383 17.2412   0.00349

Окончание обновления 9 марта 2011 г..

результат этого теста для clust:

rows  reads CPU   Elapsed 
----- ----- ----- -------- 
1503  9228  16ms  55ms
401   3681  0ms   50ms
2700  24644 46ms  79ms
0     3     0ms   0ms
2953  26955 47ms  92ms
0     3     0ms   0ms

Обновление от 9 марта 2011 г.:

cmd.CommandText = "delete clust where group between @id and @id+1000";

751 Строки имеют > 0 CPU и влияют более чем на 0 строк

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts     144      69788    4648         -
Cpu            15        764      56   0.01538
Reads         989     458467   30207   6.48490
Writes          2       1830     127   0.02694
Duration   0.2938  2512.1968 24.3714   0.00555

Окончание обновления 9 марта 2011 г..

Тесты INSERT

Последняя часть теста - это выполнение операторов insert.

вставить в кучу/группу (...) значения (...), (...), (...), (...), (...), (...)

Результат этого теста для heap:

rows  reads CPU   Elapsed 
----- ----- ----- -------- 
6     38    0ms   31ms

Обновление от 9 марта 2011 г.:

string str = @"insert into heap (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);
                    }
                    str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);

                    cmd.CommandText = str;

912 операторов имеют > 0 CPU

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -
Cpu            15       2138      25   0.02500
Reads        5212       7069    6328   6.32837
Writes         16         34      22   0.02222
Duration   1.6336   293.2132  4.4009   0.00440

Окончание обновления 9 марта 2011 г..

Результат этого теста для clust:

rows  reads CPU   Elapsed 
----- ----- ----- -------- 
6     50    0ms   18ms

Обновление от 9 марта 2011 г.:

string str = @"insert into clust (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);
                    }
                    str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);

                    cmd.CommandText = str;

946 операторов имеют > 0 CPU

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -      
Cpu            15       2403      21   0.02157
Reads        6810       8997    8412   8.41223
Writes         16         25      19   0.01942
Duration   1.5375   268.2571  6.1463   0.00614

Окончание обновления 9 марта 2011 г..

Выводы

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

Операторы SELECT сопоставимы
Операторы UPDATE быстрее с кластерным индексом на месте
Операторы DELETE быстрее с кластерным индексом на месте
Операторы INSERT быстрее с кластерным индексом на месте

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

Обновление от 9 марта 2011 г.:

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

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

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

Как вы можете видеть, профиль производительности для операторов вставки довольно интересен. Шипы вызваны несколькими точками данных, которые занимают намного больше времени.

Окончание обновления 9 марта 2011 г..

Ответ 2

Как Кимберли Трипп - Королева Индексации - объясняет довольно красиво в своем сообщении в блоге Дискуссия с кластерным индексом продолжается..., имея ключ кластеризации в таблице базы данных значительно ускоряет операции все, а не только SELECT.

SELECT обычно медленнее в куче по сравнению с кластеризованной таблицей, пока вы выбираете кластерный ключ хороший - что-то вроде INT IDENTITY. Если вы используете действительно очень плохой ключ кластеризации, например GUID или составной ключ с множеством компонентов переменной длины, то только тогда куча может быть быстрее. Но в этом случае вам действительно нужно очистить дизайн базы данных в первую очередь...

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