SQL Server: Производительность при вставке данных в таблицу с кластеризованным индексом и без

Привет! Представляю вашему вниманию перевод статьи SQL Server Insert Performance for Clustered Indexes vs. Heap Tables

Вопрос

Я прочел множество различной документации по современным методам работы с SQL Server, утверждающей, что каждая таблица должна иметь кластеризованный индекс и не быть кучей с некаластеризованными индексами. Большинство источников отмечает административную выгоду от использования кластеризованных индексов. Но есть ли в этом какое-то влияние на производительность и другие положительные или отрицательные стороны?

Решение

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

-- Create table and indexes
CREATE TABLE testtable ([col1]  [int] NOT NULL PRIMARY KEY CLUSTERED,
                        [col2] [int]  NULL,
                        [col3] [int]  NULL,
                        [col4] [varchar](50) NULL,
                        [col5] uniqueidentifier); 
 
-- Load sample data into table
DECLARE @val INT
SELECT @val=1
WHILE @val < 5000000
BEGIN  
   INSERT INTO testtable (col1, col2, col3, col4, col5) 
       VALUES (@val,round(rand()*100000,0),
               round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid())
   SELECT @val=@val+1
END
GO
 
-- Create sample table and indexes
CREATE TABLE testtable2 ([col1]  [int] NOT NULL PRIMARY KEY NONCLUSTERED,
                         [col2] [int]  NULL,
                         [col3] [int]  NULL,
                         [col4] [varchar](50) NULL,
                         [col5] uniqueidentifier); 
 
INSERT INTO testtable2 SELECT * FROM testtable;

Первое, что я сразу заметил, — куча использовала больше места, т.к. таблица и индекс являются раздельными структурами. При наличии кластеризованного индекса данные хранятся в листьях индекса и поэтому используют меньше места. Ниже представлена таблица, показывающая объем использованного пространства каждым объектом по окончании загрузки данных.

После того, как первичные данные были загружены, приступим ко второму этапу загрузки и посмотрим на производительность с помощью SQL Profiler, а также проверим использованное пространство. Ниже приведен скрипт, загружающий дополнительные 100 000 записей в каждую таблицу.

-- insert when all pages are full
DECLARE @val INT
SELECT @val=5000000
WHILE @val < 5100000
BEGIN  
   INSERT INTO testtable (col1, col2, col3, col4, col5) 
       VALUES (@val,round(rand()*100000,0),
               round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid())
   SELECT @val=@val+1
END
GO
 
DECLARE @val INT
SELECT @val=5000000
WHILE @val < 5100000
BEGIN  
   INSERT INTO testtable2 (col1, col2, col3, col4, col5) 
       VALUES (@val,round(rand()*100000,0),
               round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid())
   SELECT @val=@val+1
END
GO

Для начала посмотрим на использованное пространство, которое, как и ожидалось (ведь страницы были наполнены до конца и ничего не удалялось), увеличилось пропорционально для каждого объекта. Обе таблицы увеличились в размере примерно одинаково.

А вот SQL Profiler нам показывает более интересные вещи. Т.к. при вставке данных в кучу требуется обновление двух объектов — некластеризованного индекса и самой таблицы, то это требует и дополнительных ресурсов от ЦП, плюс ко всему в этот момент выполняется больше операций чтения и записи, чем при вставке данных в таблицу с кластеризованным индексом, следовательно, это потребует немного больше времени.

Теперь мы беспорядочно удалим часть данных и выполним вставку еще 100 000 записей, чтобы проверить как наличие пустого пространства на некоторых страницах скажется на производительности. Скрипт, выполняющий удаление и вставку, приведен ниже.

-- remove 1000000 random records from each table
DELETE FROM testtable WHERE col1 in (SELECT TOP 1000000 col1 FROM testtable ORDER BY newid());
DELETE FROM testtable2 WHERE col1 not in (SELECT col1 FROM testtable);
GO
 
-- insert when there is free space in pages
DECLARE @val INT
SELECT @val=5100000
WHILE @val < 5200000
BEGIN  
   INSERT INTO testtable (col1, col2, col3, col4, col5) 
       VALUES (@val,round(rand()*100000,0),
               round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid())
   SELECT @val=@val+1
END
GO
 
DECLARE @val INT
SELECT @val=5100000
WHILE @val < 5200000
BEGIN  
   INSERT INTO testtable2 (col1, col2, col3, col4, col5) 
       VALUES (@val,round(rand()*100000,0),
               round(rand()*100000,0),'TEST' + cast(@val AS VARCHAR), newid())
   SELECT @val=@val+1
END
GO

Скорее всего, эта разница возникла из-за того, что при вставке записей в кучу СУБД будет искать пустое пространство на каждой странице для размещения в нем данных. Причиной этому служит тот факт, что данные в куче не отсортированы, а значит, вставляемые записи можно размещать куда угодно. Когда речь идет о таблице с кластеризованным индексом, то при наращивании значения первичного ключа вставка всегда осуществляется в конец таблицы. Также стоит обратить внимание на использованное таблицами пространство. В случае с кучей, при повторном использовании пространства, объем занимаемого таблицей места не должен увеличиваться, т.к. мы вставили столько же, сколько и удалили. Ниже приведена информация о таблицах, зафиксированная после выполнения последнего скрипта, в которой видно, что занимаемое кучей пространство не изменилось и это подтверждает высказанное ранее предположение.

Вывод

В данном исследовании мы убедились, что использование кластеризованного индекса, в сравнении с кучей, дает лучшие результаты по всем замеряемым категориям: ЦП, ввод-вывод и время выполнения. Единственным побочным эффектом является то, что при использовании кластеризованного индекса требуется немного больше пространства. Это пространство можно сократить путем перестройки индекса. В любом случае, прежде чем вносить какие-то изменения в индексы на рабочей системе, необходимо все проверить на вашем оборудовании, чтобы убедиться в улучшении производительности.

Автор: rt001

Источник