Sql Server: Производительность При Вставке Данных В Таблицу С Кластерным Индексом И Без Него

Привет, Хабр! Представляю вашему вниманию перевод статьи Производительность вставки 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;

   

таблица и индекс — это отдельные структуры.

В кластеризованном индексе данные хранятся в листах индекса и поэтому занимают меньше места.

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

Ниже приведен скрипт, который загружает в каждую таблицу дополнительно 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

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

Обе таблицы увеличились в размерах примерно одинаково.

Т.

к.

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

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

Сценарий, выполняющий удаление и вставку, приведен ниже.

Скрипт 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 показывает увеличение разрыва в производительности между двумя таблицами в пользу кластеризованного индекса.

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

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

Также стоит обратить внимание на пространство, занимаемое столами.

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

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

Заключение

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

Это пространство можно уменьшить, перестроив индекс.

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

Теги: #sql #sql-сервер #индекс #производительность #оптимизация #настройка #базы данных #индексы #производительность #оптимизация #sql #Microsoft SQL Server

• Краткое Введение В Программное Обеспечение Для Управления Цепочками Поставок

  19 Oct, 24

• Apple Начала Показывать Ндс При Покупках В Itunes И App Store

  19 Oct, 24

• Цвет В Игровом Дизайне: Тенденции, Варианты Использования И Ключевые Технологии

  19 Oct, 24

• Надежность Флэш-Памяти: Ожидаемая И Неожиданная. Часть 1. Xiv Конференция Ассоциации Usenix. Технологии Хранения Файлов

  19 Oct, 24

• Выбираем Дешевый Хостинг

  19 Oct, 24

• Женские Bluetooth-Часы От Sony Ericsson

  19 Oct, 24

• Расширение Разработки Visual C++ Для Linux

  19 Oct, 24

• Новая Версия Ruby До 5 Раз Быстрее

  19 Oct, 24

• 3500 Бесплатных Видеоуроков По Школьной Программе Для Ipad И Планшетов На Базе Android

  19 Oct, 24

• Психология Звукового Зрения. Светлана Лебедева Рассказала, Как Учат Людей Новому Способу Восприятия

  19 Oct, 24