У меня есть приложение, которое вставляет сразу несколько строк в SQL Server.
Я использую класс SqlBulkCopy
или SqlBulkCopy
код, который генерирует гигантскую insert into table_name(...) values (...)
.
Моя таблица имеет несколько индексов и кластерную.
Вопрос в следующем: как обновляются эти индексы? Для каждой строки я вставляю? Для каждой транзакции?
Несколько странный вопрос - есть ли общий термин для этого сценария, например, «поведение индексирования с массовой вставкой»? Я попытался Google несколько комбинаций ключевых слов, ничего не нашел. Причина, по которой я спрашиваю, заключается в том, что иногда я работаю с Postgres и хочу знать ее поведение.
Я пытался найти статью по этой теме несколько раз, не повезло.
Если вы можете указать мне на любые документы, статью или книгу с соответствующей главой, это было бы здорово
Вы можете увидеть, как индексы обновляются, изучая план запроса. Рассмотрим эту таблицу кучи только с некластеризованными индексами.
CREATE TABLE dbo.BulkInsertTest(
Column1 int NOT NULL
, Column2 int NOT NULL
, Column3 int NOT NULL
, Column4 int NOT NULL
, Column5 int NOT NULL
);
CREATE INDEX BulkInsertTest_Column1 ON dbo.BulkInsertTest(Column1);
CREATE INDEX BulkInsertTest_Column2 ON dbo.BulkInsertTest(Column2);
CREATE INDEX BulkInsertTest_Column3 ON dbo.BulkInsertTest(Column3);
CREATE INDEX BulkInsertTest_Column4 ON dbo.BulkInsertTest(Column4);
CREATE INDEX BulkInsertTest_Column5 ON dbo.BulkInsertTest(Column5);
GO
Ниже приведен план выполнения одиночного INSERT
.
INSERT INTO dbo.BulkInsertTest(Column1, Column2, Column3, Column4, Column5) VALUES
(1, 2, 3, 4, 5);
В плане выполнения показан только оператор таблицы Insert, поэтому новые некластеризованные строки индекса были вставлены по существу во время самой операции вставки таблицы. Большая партия однотонных инструкций INSERT даст этот же план для каждого оператора insert.
Я получаю аналогичный план с одним оператором INSERT с большим количеством строк, заданных с помощью конструктора строк, с той лишь разницей, что добавление оператора Constant Scan для испускания строк.
INSERT INTO dbo.BulkInsertTest(Column1, Column2, Column3, Column4, Column5) VALUES
(1, 2, 3, 4, 5)
,(1, 2, 3, 4, 5)
,(1, 2, 3, 4, 5)
,...
,(1, 2, 3, 4, 5);
Вот план выполнения BULK INSERT
T-SQL (с использованием пустого файла в качестве источника). С помощью BULK INSERT
SQL Server добавила дополнительные операторы плана запросов для оптимизации вставки индексов. Строки были намотаны после вставки в таблицу, а затем строки из буфера отсортированы и вставлены в каждый индекс отдельно в качестве операции массовой вставки. Этот метод уменьшает накладные расходы для больших операций вставки. Вы также можете увидеть похожие планы для запросов INSERT...SELECT
.
BULK INSERT dbo.BulkInsertTest
FROM 'c:\Temp\BulkInsertTest.txt';
Я подтвердил, что SqlBulkCopy
генерирует тот же план выполнения, что и SqlBulkCopy
BULK INSERT
путем сбора фактических планов с помощью трассировки расширенного события. Ниже приведен скрипт DDL и PowerShell, который я использовал.
Trace DDL:
CREATE EVENT SESSION [SqlBulkCopyTest] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
ACTION(sqlserver.client_app_name,sqlserver.sql_text)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SqlBulkCopyTest')
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'insert bulk%')
))
ADD TARGET package0.event_file(SET filename=N'SqlBulkCopyTest');
GO
Скрипт PowerShell:
$connectionString = "Data Source=.;Initial Catalog=YourUserDatabase;Integrated Security=SSPI;Application Name=SqlBulkCopyTest"
$dt = New-Object System.Data.DataTable;
$null = $dt.Columns.Add("Column1", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column2", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column3", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column4", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column5", [System.Type]::GetType("System.Int32"))
$row = $dt.NewRow()
[void]$dt.Rows.Add($row)
$row["Column1"] = 1
$row["Column2"] = 2
$row["Column3"] = 3
$row["Column4"] = 4
$row["Column5"] = 5
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.BulkInsertTest"
$bcp.WriteToServer($dt)
РЕДАКТИРОВАТЬ
Благодарим Владимира Баранова за предоставление этой статьи в блоге Microsoft Data Platform MVP Пол Уайт , в котором подробно описывается стратегия обслуживания индексов SQL Server.
EDIT 2
Из вашего пересмотренного вопроса я вижу, что ваша фактическая ситуация - это таблица с кластеризованным индексом, а не кучей. Планы будут аналогичны приведенным выше примерам кучи, за исключением того, что данные будут вставляться с использованием оператора кластеризованной индексной вставки вместо таблицы.
Подсказка ORDER
может быть указана во время операций массовой вставки в таблицу с кластерным индексом. Когда указанный порядок совпадает с указанным кластеризованным индексом, SQL Server может исключить оператор сортировки перед вложением кластерного индекса, поскольку он предполагает, что данные уже отсортированы по подсказке. К сожалению, SqlBulkCopy
не поддерживает подсказку ORDER
.
Вопрос в следующем: как обновляются эти индексы? Для каждой строки я вставляю? Для каждой транзакции?
С низкой точки зрения индексы всегда обновляются последовательно, это следствие внутренней структуры данных индексов. Индексы SQL Server - это деревья B +. Существует не алгоритм для обновления нескольких строк в индексе дерева B + сразу, вам нужно их обновлять один за другим, потому что вы не можете заранее знать, куда будет идти одна строка, прежде чем обновлять o вставлять предыдущие строки.
Однако с точки зрения транзакций индексы обновляются одновременно, это связано с тем, что SQL Server реализует транзакционную семантику. На уровне изоляции по умолчанию READ COMMITTED другая транзакция не может видеть строки (строки индекса или таблицы), которые вы вставили в операцию объемной вставки до тех пор, пока транзакция не будет выполнена. Таким образом, это выглядит так, как строки были вставлены все сразу.