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

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

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

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

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

39 голосов | спросил Martin Smith 23 FebruaryEurope/MoscowbWed, 23 Feb 2011 20:19:23 +0300000000pmWed, 23 Feb 2011 20:19:23 +030011 2011, 20:19:23

3 ответа


41

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

  • 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% медленнее в таблице с кластеризованным индексом.

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

Взвешенная длительность кучи против кластеризации для соединения

Взвешенная длительность кучи и кластеризация для обновления

Взвешенная длительность кучи против кластеризации для удаления

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

  

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

ответил Filip De Vos 5 MaramSat, 05 Mar 2011 01:57:50 +03002011-03-05T01:57:50+03:0001 2011, 01:57:50
12

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

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

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

ответил marc_s 23 FebruaryEurope/MoscowbWed, 23 Feb 2011 20:50:14 +0300000000pmWed, 23 Feb 2011 20:50:14 +030011 2011, 20:50:14
7

Просто случилось встретить этой статьи от Джо Чанга, который решает этот вопрос. Вложил свои выводы ниже.

  

Рассмотрим таблицу, для которой индексы имеют глубину 4, так что существует   уровень корня, 2 промежуточных уровня и уровень листа. Поиск индекса   для одного ключа индекса (то есть без поиска ключа) будет генерировать 4   логический IO (LIO). Теперь рассмотрим, требуется ли ключевой поиск. Если   таблица имеет кластерный индекс также глубины 4, каждый ключевой поиск генерирует   4 LIO. Если таблица была кучей, каждый ключевой поиск генерирует 1 LIO. В   актуальность, ключевой поиск кучи примерно на 20-30% дешевле   чем ключевой поиск кластеризованного индекса, а не где-нибудь рядом с 4: 1   LIO.

ответил Martin Smith 15 MarpmThu, 15 Mar 2012 14:44:37 +04002012-03-15T14:44:37+04:0002 2012, 14:44:37

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132