16.10.2017 13:52
rt001 3 2200 Источник
Привет, Хабр! Представляю вашему вниманию перевод статьи SQL Server Insert Performance for Clustered Indexes vs. Heap Tables

Вопрос


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

Решение


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

Скрипт 1
-- 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;


Первое, что я сразу заметил, — куча использовала больше места, т.к. таблица и индекс являются раздельными структурами. При наличии кластеризованного индекса данные хранятся в листьях индекса и поэтому используют меньше места. Ниже представлена таблица, показывающая объем использованного пространства каждым объектом по окончании загрузки данных.
Таблица Индекс Использовано (КБ) Зарезервировано (КБ) Число строк
testtable PK__testtabl__357D0D3E3D086A66 257952 257992 4999999
testtable2 HEAP 256992 257032 4999999
testtable2 PK__testtabl__357D0D3F2CBA35D8 89432 89608 4999999

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

Скрипт 2
-- 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


Для начала посмотрим на использованное пространство, которое, как и ожидалось (ведь страницы были наполнены до конца и ничего не удалялось), увеличилось пропорционально для каждого объекта. Обе таблицы увеличились в размере примерно одинаково.
Таблица Индекс Использовано (КБ) Зарезервировано (КБ) Число строк
testtable PK__testtabl__357D0D3E3D086A66 263128 263176 5099999
testtable2 HEAP 262392 262472 5099999
testtable2 PK__testtabl__357D0D3F2CBA35D8 91216 91272 5099999

А вот SQL Profiler нам показывает более интересные вещи. Т.к. при вставке данных в кучу требуется обновление двух объектов — некластеризованного индекса и самой таблицы, то это требует и дополнительных ресурсов от ЦП, плюс ко всему в этот момент выполняется больше операций чтения и записи, чем при вставке данных в таблицу с кластеризованным индексом, следовательно, это потребует немного больше времени.
Тип индекса ЦП (мс) Операций чтения Операций записи Длительность (мс)
Кластеризованный 3500 304919 654 11288
Куча 3890 406083 904 11438

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

Скрипт 3
-- 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

На этот раз в SQL Profiler видно увеличение разрыва по производительности между двумя таблицами в пользу кластеризованного индекса.
Тип индекса ЦП (мс) Операций чтения Операций записи Длительность (мс)
Кластеризованный 3562 304859 653 10334
Куча 4973 422142 7053 13042


Скорее всего, эта разница возникла из-за того, что при вставке записей в кучу СУБД будет искать пустое пространство на каждой странице для размещения в нем данных. Причиной этому служит тот факт, что данные в куче не отсортированы, а значит, вставляемые записи можно размещать куда угодно. Когда речь идет о таблице с кластеризованным индексом, то при наращивании значения первичного ключа вставка всегда осуществляется в конец таблицы. Также стоит обратить внимание на использованное таблицами пространство. В случае с кучей, при повторном использовании пространства, объем занимаемого таблицей места не должен увеличиваться, т.к. мы вставили столько же, сколько и удалили. Ниже приведена информация о таблицах, зафиксированная после выполнения последнего скрипта, в которой видно, что занимаемое кучей пространство не изменилось и это подтверждает высказанное ранее предположение.
Наименование таблицы Наименование индекса Использовано (КБ) Зарезервировано (КБ) Число строк
testtable PK__testtabl__357D0D3E3D086A66 268304 268360 4199999
testtable2 HEAP 262392 262472 4199999
testtable2 PK__testtabl__357D0D3F2CBA35D8 93008 93064 4199999

Вывод


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

Комментарии (3)


  1. Alozar
    16.10.2017 17:24
    #10475858

    Немного позанудствую

    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

    Если делается сравнение, было бы логично выполнять абсолютно одинаковые запросы.
    Скорее всего результат от этого не улучшиться, но всё же.


    1. VMichael
      16.10.2017 22:27
      #10476458

      Возможно производительность автор замерял только на вставке. А удаление было подготовительной операцией и в замере не участвовало. Как вариант.


      1. Alozar
        17.10.2017 10:52
        #10477028

        Возможно и так, не спорю, но в глаза бросается.