Иногда в процессе проектирования базы данных разработчики могут недооценивать масштаб проекта. Если бы этот проект стал успешным, это привело бы к значительным нагрузкам и чрезмерной нагрузке на систему и ее разработчиков в будущем. В конце концов люди понимают, что используют ключ идентификации INT с максимальным значением 2 147 483 647 в качестве первичного ключа огромной таблицы.
С одной стороны, 2 миллиардов значений должно быть вполне достаточно. Но что, если к вам ежедневно добавляется более 10 миллионов записей, а вы уже использовали более миллиарда из них? Это дает вам только 114 дней, чтобы внести необходимые исправления или пострадать от последствий. Это не так уж много, особенно если ключ идентификации «жестко подключен» как к веб-интерфейсу, так и к клиентскому приложению.
Если вы достигли этого печального момента, не бойтесь, эта статья здесь, чтобы помочь, даже если уже слишком поздно что-то менять, а ваше приложение работает 24/7. В худшем случае вы должны были уже увидеть следующее сообщение об ошибке: Ошибка арифметического переполнения при преобразовании IDENTITY в тип данных INT. Мы в Дистилери можем предложить вам несколько полезных решений, когда эта ситуация оказывается чрезвычайной в вашем MS SQL Server.
- Переключитесь на БОЛЬШОЙ. В идеальном мире лучшим вариантом было бы изобрести машину для путешествий во времени, вернуться в прошлое и изменить INT на BIGINT. Однако изменение INT на BIGINT на более продвинутом этапе процесса разработки вызовет изменения не только в базе данных, но и в серверных, и в клиентских приложениях. Если у вас есть возможность изменить INT на BIGINT без серьезных последствий, сделайте это и сделайте это быстро.
- Используйте отрицательные значения. Шаг приращения по умолчанию равен единице — IDENTITY(1,1). В тот момент, когда вы собираетесь достичь 2 миллиардов, вы можете использовать
DBCC CHECKIDENT (TableWithPKViolation, −2147483647, reseed)
тем самым добавляя дополнительное время перед переключением на BIGINT. Однако это может создать проблему, если логика вашего домена не позволяет использовать отрицательные значения. Прежде чем переключаться, убедитесь, что логика домена может приспособиться.
3. Создайте таблицу с неиспользуемыми значениями. Вам нужно найти те значения ключа идентификации, которые были пропущены, создав таким образом пул свободных и неиспользуемых значений. Такой подход позволяет создать дополнительное время перед переходом к BIGINT. В любой ситуации, когда вы используете ключи инкремента, вы увидите пропущенные значения, потому что такие значения резервируются в начале транзакции и никогда не возвращаются в основной пул в случае отката. Таким образом, новая транзакция получит новое значение. Вы можете использовать больше свободных значений, если удалите данные из таблицы. Ваша основная задача — сформировать таблицу, содержащую все доступные значения и логическое поле, которое работает как атрибут резервирования значений. Вы можете удалить зарезервированные значения из такой таблицы в соответствии с расписанием, например. один раз в день. После этого вам нужно создать функцию, которая предоставит необходимое количество идентификационных ключей, и использовать полученные результаты с помощью команды SET IDENTITY INSERT ON. Если вы регулярно удаляете информацию из основной таблицы, вы можете добавить такие удаленные значения в свою таблицу как свободные значения. Этот метод можно использовать только в том случае, если вам не нужно сохранять порядок записей в таблице, другими словами, если вы не используете ORDER BY Id. В противном случае вам придется выбрать другой вариант сортировки.
Ниже вы найдете примеры третьего метода. Если вы уже удалили некоторую информацию из таблицы, имея менее одного миллиарда записей, будет более эффективно создать полную таблицу со значениями, удалив используемые идентификационные ключи.
- Создайте таблицу для создания последовательности и заполните ее значениями от 1 до 2 147 483 647.
CREATE TABLE [dbo].[IntRange](
[Id] [int] NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spNewIDPopulateInsert]
@batchsize INT = 10000,
@startFrom INT = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @startFrom IS NULL
BEGIN
SELECT @startFrom = MAX(id)
FROM dbo.IntRange;
END;
DECLARE @startId INT = ISNULL(@startFrom,0);
DECLARE @rowscount INT = @batchsize;
DECLARE @maxId INT = 2147483647;
WHILE @rowscount = @batchsize
BEGIN
INSERT INTO dbo.IntRange
(id)
SELECT id
FROM (
SELECT TOP (@batchsize)
@startId + ROW_NUMBER() OVER(ORDER BY
TableWithPKViolationId) AS id
FROM dbo.TableWithPKViolation --any table where you have @batchsize rows
) AS genids
WHERE id < @maxId;
SET @rowscount = @@ROWCOUNT;
SET @startId = @startId + @rowscount;
PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50));
END
END
GO
exec dbo.spNewIDPopulateInsert
@batchsize = 10000000
- Этот скрипт использует таблицу TableWithPKViolation для создания последовательности. Однако вы можете использовать любой желаемый или любимый метод, включая команду SEQUENCE, встроенную в MS SQL Server. Мы выбрали этот метод как наиболее продуктивный.
ALTER TABLE [dbo].[IntRange] ADD PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
2. Создайте таблицу свободных значений и заполните ее.
SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE TABLE [dbo].[NewIds]( [NewId] [int] NOT NULL, [DateUsedUtc] [datetime] NULL ) ON [PRIMARY]GOSET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spNewIDPopulateInsertFiltered] @batchsize INT = 10000, @startFrom INT = NULL, @endTill INT = NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF @startFrom IS NULL BEGIN SELECT @startFrom = MAX([NewId]) FROM dbo.NewIds; END; DECLARE @startId INT = ISNULL(@startFrom,0); DECLARE @rowscount INT = @batchsize; DECLARE @maxId INT = ISNULL(@endTill,2147483647); DECLARE @endId INT = @startId + @batchsize; WHILE @startId < @maxId BEGIN INSERT INTO [NewIds] ([NewId]) SELECT IR.id FROM [dbo].[IntRange] AS IR WHERE IR.id >= @startId AND IR.id < @endId AND NOT EXISTS ( SELECT 1 FROM [dbo].[TableWithPKViolation] as Tb WITH (NOLOCK) WHERE Tb.TableWithPKViolationId = IR.id ); SET @rowscount = @@ROWCOUNT; SET @startId = @endId; SET @endId = @endId + @batchsize; IF @endId > @maxId SET @endId = @maxId; PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50)); END END GO ----- -----Run each part in separate window in parallel ----- --part 1 exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 1, @endTill= 500000000 --end of part 1 --part 2 exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 500000000, @endTill= 1000000000 --end of part 2 --part 3 exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 1000000000, @endTill= 1500000000 --end of part 3 --part 4 DECLARE @maxId INT SELECT @maxId = MAX(TableWithPKViolationId) FROM dbo.TableWithPKViolation exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, @startFrom = 1500000000, @endTill= @maxId --end of part 4 ALTER TABLE [dbo].[NewIds] ADD CONSTRAINT [PK_NewIds] PRIMARY KEY CLUSTERED ( [NewId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_NewIds_DateUsedUtc] ON [dbo].[NewIds] ( [DateUsedUtc] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) GO ALTER TABLE [dbo].[NewIds] SET ( LOCK_ESCALATION = DISABLE ) GO
Убедитесь, что таблица сформирована правильно. Таблица NewId не должна содержать идентификационных ключей, включенных в основную таблицу TableWithPKViolation.
3. Создайте процесс, который будет помечать и возвращать все доступные значения.
create PROCEDURE [dbo].[spGetTableWithPKViolationIds]
@batchsize INT = 1
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @rowcount INT,
@now DATETIME = GETUTCDATE();
BEGIN TRAN
UPDATE TOP (@batchsize) dbo.NewIds
SET DateUsedUtc = @now
OUTPUT inserted.[NewId]
WHERE DateUsedUtc IS NULL;
SET @rowcount = @@ROWCOUNT;
IF @rowcount != @batchsize
BEGIN
DECLARE @msg NVARCHAR(2048);
SET @msg = 'TableWithPKViolationId out of ids.'
+ 'sp spGetTableWithPKViolationIds, table NewIds. '
+ 'Ids requested '
+ CAST(@batchsize AS NVARCHAR(255))
+ ', IDs available '
+ CAST(@rowcount AS NVARCHAR(255));
RAISERROR(@msg, 16,1);
ROLLBACK;
END
ELSE
BEGIN
COMMIT TRAN
END;
END
GO
4. Возьмите все процедуры, которые добавляют информацию в основную таблицу и возвращают SCOPE_IDENTITY(), и добавьте вызов процедуры. Если ваше время ограничено или ваше оборудование относительно мощное, вы можете создать триггер INSTEAD OF INSERT, избежав таким образом изменений в огромном количестве процедур. Я покажу вам пример изменения процедуры.
CREATE TABLE #tmp_Id (Id INT); INSERT INTO #tmp_Id EXEC spGetTableWithPKViolationIds @batchsize=@IDNumber; SELECT @newVersionId = Id FROM #tmp_Id; SET IDENTITY_INSERT [dbo].[TableWithPKViolation] ON;
Обратите внимание, что для параметра SET IDENTITY_INSERT требуется пользователь с достаточными правами для использования команды ALTER для таблицы TableWithPKViolation.
5. Далее нам нужно установить очистку таблицы идентификационных ключей в соответствии с расписанием.
create PROCEDURE dbo.spCleanupNewIds @batchSize INT = 4999 AS BEGIN SET NOCOUNT ON DECLARE @minId INT DECLARE @maxId INT SELECT @minId = Min([NewId]), @maxId = MAX([NewId]) FROM dbo.NewIds WITH (NOLOCK) WHERE DateUsedUtc IS NOT NULL; DECLARE @totRowCount INT = 0 DECLARE @rowCount INT = @batchSize WHILE @rowcount = @batchsize BEGIN DELETE TOP (@batchsize) FROM dbo.NewIds WHERE DateUsedUtc IS NOT NULL AND [NewId] >= @minId AND [NewId] <= @maxId SET @rowcount = @@ROWCOUNT SET @totRowCount = @totRowCount + @rowcount END PRINT 'Total records cleaned up - ' + CAST(@totRowCount AS VARCHAR(100)) END GO
В случае, когда последовательность идентификационных ключей играет важную роль, этот метод будет очень полезен, поскольку все новые значения задаются постепенно. Однако нужно решить проблему переходного периода. Лучшее решение — переключить существующие процедуры, требующие надлежащей последовательности, на другое поле в той же таблице, например. в поле даты.
Ну вот! Это интеллектуальное решение позволяет выиграть дополнительное время перед переходом на BIGINT. Тем не менее, мы настоятельно рекомендуем вам избегать подобных сложных ситуаций любой ценой, планируя свой переход заранее. Если вам нужна помощь в администрировании вашей базы данных, наша команда DBA будет рада помочь!
