Производительность некластеризованных индексов на кучах против кластерных индексов
В этой Белой книге 2007 года сравнивается производительность для отдельных операторов выбора / вставки / удаления / обновления и выбора диапазона в таблице, организованной в виде кластеризованного индекса, по сравнению с таблицей, организованной в виде кучи с некластеризованным индексом в тех же ключевых столбцах, что и элемент конфигурации. Таблица.
Как правило, опция кластеризованного индекса показала лучшие результаты в тестах, поскольку поддерживается только одна структура и нет необходимости в поиске закладок.
Одним из потенциально интересных случаев, не рассматриваемых в статье, было бы сравнение некластеризованного индекса в куче с некластеризованным индексом в кластеризованном индексе. В этом случае я ожидал, что куча может работать даже лучше, так как однажды на уровне листьев NCI SQL Server имеет RID, которому нужно следовать напрямую, а не обходить кластерный индекс.
Кто-нибудь знает о подобных формальных тестах, которые были проведены в этой области, и если да, каковы были результаты?
2 ответа
Для проверки вашего запроса я создал 2 таблицы по этой схеме:
- 7,9 миллиона записей, представляющих информацию о балансе.
- поле идентичности от 1 до 7,9 миллионов
- числовое поле, объединяющее записи примерно в 500 тыс. групп.
Первая таблица называется heap получил некластерный индекс на поле group, Вторая таблица называется clust получил кластерный индекс в последовательном поле под названием key и некластеризованный индекс на поле group
Тесты проводились на процессоре I5 M540 с 2-мя гиперпоточными ядрами, 4 Гб памяти и 64-битной Windows 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 года.
ВЫБЕРИТЕ производительность
Чтобы проверить номера производительности, я выполнил следующие запросы один раз для таблицы кучи и один раз для таблицы clust:
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 ЦП и влияет на более чем 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 ЦП и влияет на более чем 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 года.
ВЫБЕРИТЕ С СОЕДИНЕНИЕМ производительности
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 ЦП и влияют более чем на 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 процессоров и влияют более чем на 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 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 процессоров и влияют на более чем 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 ЦП и влияют более чем на 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 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 ЦП и влияют более чем на 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 процессоров и влияет на более чем 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 года.
ВСТАВИТЬ отметки
Последняя часть теста - это выполнение операторов вставки.
вставить в кучу / clust (...) значения (...), (...), (...), (...), (...), (...)
Результат этого теста для 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% медленнее в таблице с кластерным индексом.
Некоторые дополнительные графики, где я составил график взвешенной продолжительности на запрос для кучи против clust.
Как видите, профиль производительности для операторов вставки довольно интересен. Пики вызваны несколькими точками данных, выполнение которых занимает намного больше времени.
Конец обновления 9 марта 2011 года.
Как хорошо объясняет Кимберли Трипп, королева индексации, в своем посте в блоге Дебаты о кластеризованных индексах продолжаются..., имея ключ кластеризации в таблице базы данных, в значительной степени ускоряет все операции - не только SELECT,
SELECT обычно медленнее в куче по сравнению с кластеризованной таблицей, если вы выбираете хороший ключ кластеризации - что-то вроде INT IDENTITY, Если вы используете действительно очень плохой ключ кластеризации, такой как GUID или составной ключ с большим количеством компонентов переменной длины, тогда, но только тогда, куча может быть быстрее. Но в этом случае вам действительно нужно сначала очистить дизайн базы данных...
Так что, в общем, я не думаю, что в куче есть смысл - выбирайте хороший, полезный ключ кластеризации, и вы должны извлечь выгоду во всех отношениях.