#sql #sql_server
Допустим, есть пустая таблица, которую нужно заполнить большим кол-ом данных, а далее
использовать в запросе=> нужен индекс.
Собственно вопрос, будет ли существенная разница во времени, если данные вставлять
с индексом и если индекс вешать после вставки?
Ответы
Ответ 1
Может быть существенная разница за счет page split'ов. При наполнении индекса данными новые вставки могут вставляться в заполненные страницы. Page split - это запись двух страниц вместо одной на листовом уровне, плюс изменение данных на уровне дерева. При создании же индекса после заливки данных он будет создаваться последовательно, без дополнительной нагрузки на page split.Ответ 2
Если ваша цель - ускорить массовую загрузку данных в таблицу, и на время загрузки есть возможность монополизировать доступ к таблице, то в отношении индексов можно рекомендовать следующую стратегию. Если на таблице есть некластерные индексы, то на время загрузки данных их нужно отключить. Необязательно для этого делать DROP INDEX (на мой взгляд - даже нежелательно, т.к. при этом теряется статистика использования индекса, - та, что в sys.dm_db_index_usage_stats, - и могут быть потеряны и другие метаданные - например, extended properties), достаточно использовать ALTER INDEX ... DISABLE. После загрузки данных некластерные индексы можно восстановить (ALTER INDEX ... REBUILD). Это позволит минимизировать фрагментацию индексов, избежать понижения производительности при вставке данных, из-за возможного разбиения страниц индекса (т.н. page splits, о чём - в соседнем ответе) и оптимально использовать место в страницах индекса, что в дальнейшем (при использовании индекса) скажется на производительности самым положительным образом. Особо стоит остановиться на случае, когда primary key (и вообще - любой уникальный ключ или индекс) таблицы является некластерным, и в других таблицах есть внешние ключи, ссылающиеся на него. Если отключить индекс ключа (ALTER INDEX [PK_TableName] ON [TableName]), то внешние ключи в соответствующих таблицах станут непроверяемыми (в sys.foreign_keys им будет установлен флаг is_not_trusted, его наличие, кстати говоря, делает невозможным применение join elimination оптимизаций при исполнении запросов). В зависимости от ситуации такие индексы можно либо не отключать, либо отключать, но после не забывать восстановить проверку ограничений по внешним ключам в ссылающихся таблицах (ALTER TABLE [RefTable] WITH CHECK CHECK CONSTRAINT [FK_RefTable_Table]). Если на таблице есть кластерный индекс, то, на мой взгляд, его на время загрузки данных лучше сохранить (чтобы сразу заполнять его данными и не тратить время на его построение после загрузки). Если кластерный индекс строится на столбце с инкрементальным автозаполнением (IDENTITY, sequence), и в него не происходит прямая вставка - это лучший вариант (можно загружать данные, не беспокоясь о page splits). Если же кластерный индекс и/или данные таковы, что при загрузке данных вставка происходит и в ключевые столбцы кластерного индекса, то возможны page splits и замедление вставки. Чтобы этого избежать (или, по крайней мере, минимизировать влияние), можно задать индексу какой-либо разумный fillfactor, плюс, возможно, установить padding (ALTER INDEX ... REBUILD WITH (FILLFACTOR = ..., PAD_INDEX = ON), но лучше подготовить данные для вставки так, чтобы они сразу были отсортированы сообразно столбцам кластерного индекса (в bcp и BULK INSERT соответственно потребуется указать опцию ORDER). Также, если есть возможность, постарайтесь задействовать минимальное протоколирование (т.н. minimal logging) - напрямую (пустая таблица к этому располагает), либо с использованием Trace Flag 610 (об этом здесь). будет ли существенная разница во времени Это будет сильно зависеть от соотношения объёмов данных в таблице и её некластерных индексах. Далее пара примеров. Пример 1. Пусть есть две таблицы: CREATE TABLE [Lookup] (ID int NOT NULL, CONSTRAINT PK_Lookup PRIMARY KEY (ID)); INSERT INTO [Lookup] (ID) VALUES (1), (2), (3); CREATE TABLE [Data] ( ID int IDENTITY (1, 1) NOT NULL, ID1 int NULL CONSTRAINT FK_Data_Lookup_1 FOREIGN KEY REFERENCES [Lookup] (ID), ID2 int NULL CONSTRAINT FK_Data_Lookup_2 FOREIGN KEY REFERENCES [Lookup] (ID), ID3 int NULL CONSTRAINT FK_Data_Lookup_3 FOREIGN KEY REFERENCES [Lookup] (ID), ID4 int NULL CONSTRAINT FK_Data_Lookup_4 FOREIGN KEY REFERENCES [Lookup] (ID), ID5 int NULL CONSTRAINT FK_Data_Lookup_5 FOREIGN KEY REFERENCES [Lookup] (ID), CONSTRAINT PK_Data PRIMARY KEY (ID) ); CREATE INDEX IX_Data_1 ON [Data] (ID1); CREATE INDEX IX_Data_2 ON [Data] (ID2); CREATE INDEX IX_Data_3 ON [Data] (ID3); CREATE INDEX IX_Data_4 ON [Data] (ID4); CREATE INDEX IX_Data_5 ON [Data] (ID5); В таблицу [Data] (в столбцы ID1, ID2, ... ID5) будем делать вставку одного миллиона записей вида 1 2 3 1 2 2 3 1 2 3 ... 3 1 2 3 1 Сначала со всеми индексами на месте и с проверкой ограничений: BULK INSERT [Data] FROM 'D:\temp\bulkdata_1M.txt' WITH ( CHECK_CONSTRAINTS, FORMATFILE = 'D:\temp\bulkdata_format.xml'); По затратам получилось: Duration = 27375 ms CPU = 25350 ms Reads = 20482577 pages Writes = 28340 pages Log Writes = 12053 Log Written, MB = 703.2 Теперь то же самое (таблицы создадим заново), но перед вставкой отключим некластерные индексы, загрузим данные, задействовав минимальное протоколирование, затем восстановим индексы и ограничения: ALTER INDEX IX_Data_1 ON [Data] DISABLE; ALTER INDEX IX_Data_2 ON [Data] DISABLE; ALTER INDEX IX_Data_3 ON [Data] DISABLE; ALTER INDEX IX_Data_4 ON [Data] DISABLE; ALTER INDEX IX_Data_5 ON [Data] DISABLE; BULK INSERT [Data] FROM 'D:\temp\bulkdata_1M.txt' WITH ( FORMATFILE = 'D:\temp\bulkdata_format.xml', TABLOCK, BATCHSIZE = 0); ALTER INDEX IX_Data_1 ON [Data] REBUILD; ALTER INDEX IX_Data_2 ON [Data] REBUILD; ALTER INDEX IX_Data_3 ON [Data] REBUILD; ALTER INDEX IX_Data_4 ON [Data] REBUILD; ALTER INDEX IX_Data_5 ON [Data] REBUILD; ALTER TABLE [Data] WITH CHECK CHECK CONSTRAINT ALL; Всё вместе получилось: Duration = 9635 ms CPU = 13429 ms Reads = 86689 pages Writes = 14564 pages Log Writes = 169 Log Written, MB = 1.9 Выигрыш в несколько раз. Приблизительное соотношение объёма данных в таблице и индексах при этом: index_name index_size_Mb ------------------------- PK_Data 45.5 IX_Data_1 13.6 IX_Data_2 13.6 IX_Data_3 13.6 IX_Data_4 13.6 IX_Data_5 13.6 (т.е. индексы в сумме занимают больше места, чем данные) Пример 2. Пусть есть таблица: CREATE TABLE [Data2] ( ID int IDENTITY (1, 1) NOT NULL, UID uniqueidentifier NOT NULL, Filler char(4000) NOT NULL, CONSTRAINT PK_Data2 PRIMARY KEY (ID), CONSTRAINT UQ_Data2 UNIQUE (UID) ); В столбцы (UID, Filler) будем делать вставку 200 тыс. записей вида UID Filler ------------------------------------ ------------ 4C548E62-B877-4097-A29F-10AA9B5551EC AAAAAAA...AA 894F3F0A-1F87-430D-AEC1-56850C5F21CE BBBBBBB...BB ... Сначала с включенным индексом UQ_Data2: BULK INSERT [Data2] FROM 'D:\temp\bulkdata_200K.txt' WITH (FORMATFILE = 'D:\temp\bulkdata_format_2.xml'); По затратам получилось: Duration = 22406 ms CPU = 14552 ms Reads = 1995312 pages Writes = 101390 pages Log Writes = 15891 Log Written, MB = 930.0 Теперь с отключением индекса, минимальным протоколированием, и включением индекса после загрузки данных: ALTER INDEX UQ_Data2 ON [Data2] DISABLE; BULK INSERT [Data2] FROM 'D:\temp\bulkdata_200K.txt' WITH ( FORMATFILE = 'D:\temp\bulkdata_format_2.xml', TABLOCK, BATCHSIZE = 0); ALTER INDEX UQ_Data2 ON [Data2] REBUILD; Всё вместе получилось: Duration = 15377 ms CPU = 10622 ms Reads = 349347 pages Writes = 100834 pages Log Writes = 628 Log Written, MB = 7.6 Выигрыш уже не в разы, но всё-таки ощутим (впрочем, по нагрузке на журнал транзакций - в разы). Соотношение объёма данных в таблице и индексе: index_name index_size_Mb ----------- ------------- PK_Data2 782.5 UQ_Data2 5.1 (т.е. данные занимают значительно больше места, чем индекс)
Комментариев нет:
Отправить комментарий