Что делать, если в PK Identity закончились значения?

  • Tutorial
Иногда, при дизайне БД разработчики недооценивают масштабы проекта. А потом, проект выстреливает и становится высоконагруженным. Затем, в какой-то момент, кто-то замечает, что в качестве первичного ключа большой таблицы выбран identity типа INT, с ограничением 2,147,483,647.

Изначально кажется, что 2 миллиарда записей – это много. Но если, у вас ежедневно добавляется 10 млн. новых записей? И уже израсходовано более 1 млрд. значений? У вас приложение, работающее в режиме 24/7? То у вас осталось всего 114 дней, чтобы это исправить тип первичного ключа. Это не так уж и много, если у вас используется значение ключа как в веб-приложении, так и в клиентском.


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

В худшем случае, Вы столкнулись с ошибкой “Arithmetic overflow error converting IDENTITY to data type int”. Это означает, что значения первичного ключа уже закончились, и Ваше приложение перестало работать. В данном случае Вы можете использовать следующие решения:

  1. Поменять тип первичного ключа на BIGINT. Всем и каждому понятно, что лучший вариант — это сесть в машину времени и изменить INTна BIGINT там, в прошлом. Но Вы можете сделать это и сейчас, если поле TableWithPKViolationId не используется в серверном и клиентском приложении, то у вас есть возможность оперативно и безболезненно поменять тип. Сделайте это, и не тратьте время на остальную статью. Обратите внимание, что если в Вашей таблице больше 1 млрд. записей, то изменение будет применяться, т.е. может занять больше 3 часов, в зависимости от мощности вашего сервера, и потребует дополнительного места в логе транзакций (если можете, переключитесь на модель Recovery Mode в Simple). Скрипт для изменения следующий:

    ALTER TABLE TableWithPKViolation ALTER COLUMN TableWithPKViolationId BIGINT;

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

  2. Использовать отрицательные значения. Обычно, при использовании identity используется по умолчанию IDENTITY(1,1). Когда значение подходит к 2 миллиардам записей, Вы можете сделать сброс начального значения, используя следующую команду:

    DBCC CHECKIDENT (TableWithPKViolation, - 2147483647, reseed) 

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

  3. III. Сформировать таблицу с неиспользованными значениями. Посчитайте значения, которые пропущены, и сформируйте таблицу со списком неиспользованных значений. Это даст Вам дополнительное время для перехода на BIGINT.

    Данный способ Вам подойдет, если Вы не опираетесь на порядок записей в таблице, то есть не используете ORDERY BY Id. Либо мест, где есть такая сортировка не много, и Вы можете изменить сортировку на другое поле, например, на дате добавления записи.

    Сформировать таблицу с неиспользованными значениями можно двумя способами:

    Способ А. Пропущенные значения.

    Когда вы используете Identity, у вас всегда есть пропущенные значения, так как, значения резервируются при начале транзакции, и, в случае ее отката, следующей транзакции присваивается новое, следующее за зарезервированным, значение первичного ключа. Зарезервированное значение, которое было сформировано для отмененной транзакции, так и останется неиспользованным. Данные неиспользованные значения можно сформировать в отдельную таблицу и применить, используя код, который будет приведен ниже.

    Способ В. Удаленные значения.

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

    Исходная таблица TableWithPKViolation.

    CREATE TABLE [dbo].[TableWithPKViolation](
    	[TableWithPKViolationId] [int] IDENTITY(1,1) NOT NULL
    ) ON [PRIMARY]


    1. Создаем таблицу для хранение свободных ID
    10-CreateNewId.sql

    
    CREATE TABLE [dbo].[NewIds](
    	[NewId] [int] NOT NULL,
    	[DateUsedUtc] [datetime] NULL
    ) ON [PRIMARY]
    

    Далее, в зависимости от способа:

    Для генерации последовательности способом А. Пропущенные значения:

    2. Генерируем последовательность из пропущенных идентификаторов
    20-GenerateGaps.sql
    «Option1 FindGaps\20-GenerateGaps.sql»

    
    CREATE PROCEDURE [dbo].[spNewIDPopulateInsertFromGaps]
    	@batchsize INT = 10000,
    	@startFrom 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;
    
    	SELECT @maxId = MAX(TableWithPKViolationId)
    	FROM dbo.TableWithPKViolation;
    
    	WHILE @startId < @maxId
    	BEGIN
    		INSERT INTO dbo.NewIds
    		([NewId])
    		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
    			AND NOT EXISTS 
    			  (
    			   SELECT 1 
    			   FROM [dbo].[TableWithPKViolation] as Tb WITH (NOLOCK)
    			   WHERE Tb.TableWithPKViolationId = genids.id
    			  ); 
    
    		SET @rowscount = @@ROWCOUNT;
    				
    		SET @startId = @startId + @batchsize;
    
    		PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50));
    	END
    END
    

    Для генерации последовательности способом B Удаленные значения:
    2. Создаем таблицу для генерации последовательности и заполняем ее данными от 1 до 2,147,483,647
    15-CreateInt.sql

    
    CREATE TABLE [dbo].[IntRange](
    	[Id] [int] NOT NULL
    ) ON [PRIMARY]
    

    20-GenerateInt.sql

    
    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
    

    25-PopulateRange.sql

    
    exec dbo.spNewIDPopulateInsert
    	@batchsize = 10000000
    

    В скрипте используется таблица TableWithPKViolation для генерации последовательности, вы можете использовать любой способ для этого, в том числе, последовательность встроенную в MS SQL (Sequence). Данный способ был выбран, потому что работал быстрее по сравнению с другими.

    30-CreateIndexOnInt.sql

    
    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]
    

    и заполняем ее
    50-GenerateNewId.sql

    
    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
    

    55-ExecGeneration.sql

    
    -----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
    

    3. Таблица свободных идентификаторов, сгенерированная способом A или B готова. Создаем индексы на таблице со свободными ключами
    60-CreateIndex.sql

    
    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
    

    Проверяем, что все было правильно сгенерировано. Не должно быть ID в таблице NewId, которые есть в основной таблице TableWithPKViolation.
    70-CheckData.sql

    
    declare @maxId INT
    
    
    select @maxId = max(TableWithPKViolationId) 
    from [dbo].[TableWithPKViolation]
    
    IF EXISTS (select 1 from [dbo].[NewIds] WHERE [NewId] > @maxId)
    BEGIN
    	PRINT 'PROBLEM. Wait for cleanup';
    	
    	declare @batchsize INT = 10000
    	DECLARE @rowcount int = @batchsize;
    
    	while @rowcount = @batchsize 
    	begin 
    
    		delete top (@batchsize)
    		from [dbo].[NewIds]
    		where DFVId > @maxId;
    
    		SET @rowcount = @@rowcount;
    	end;
    END
    ELSE
    	PRINT 'OK';
    

    Если вы генерируете последовательно на другом сервере (например на сервере с восстановленной резервной копией БД), то выгрузить данные в файл, можно с помощью скрипта:
    80-BulkOut.sql

    
    declare @command VARCHAR(4096),
    		@dbname VARCHAR(255),
    		@path VARCHAR(1024),
    		@filename VARCHAR(255),
    		@batchsize INT
    
    
    SELECT @dbname = DB_NAME();
    SET @path = 'D:\NewIds\';
    SET @filename = 'NewIds-'+@dbname+'.txt';
    SET @batchsize = 10000000;
    SET @command = 'bcp "['+@dbname+'].dbo.NewIds" out  "'+@path+@filename+'" -c -t, -S localhost -T -b '+CAST(@batchsize AS VARCHAR(255));
    
    PRINT @command
    
    exec master..xp_cmdshell @command
    

    4. Создаем процедуру, которая помечает нужное количество доступный ID и возвращает их в результате
    90-GetNewId.sql

    
    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
    

    5. Добавляем во все процедуры, в которых была вставка данных, в таблицу и возвращался 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 ON нужно, чтобы пользователь вызывающий процедуру имел разрешение на ALTER для таблицы TableWithPKViolation.

    6. Затем можно настроить JOB, который будет очищать таблицу с используемыми идентификаторами
    95-SPsCleanup.sql

    
    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
    

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

    Я бы все равно порекомендовала при этом запланировать переход на BIGINT.

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

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

Наилучшим решением является мониторинг пограничных значений и переход на соответствующие типы данных заранее.

Архив с кодом
Share post

Comments 50

    –4
    Поменять тип первичного ключа на BIGINT

    A может сразу на varchar и написать свой обработчик beforeInsert?
      +2
      если можете, переключитесь на модель Recovery Mode в Simple
      Не очень то ответственно давать такой совет, не предупреждая о последствиях, например о проблемах с бэкапом лога.
        0
        Согласна, тут в зависимости от срочности — если это таблица из-за которой работа дальше не идет, что нибудь типа информации о продажах и пока не починят, то продавать не могут это одно. А если что-то не очень важное, то совсем другое.
          +1

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

            +2
            Более того — непонятна цель такого совета, т.к. эта операция вроде бы не относится к минимально-логируемым операциям и сэкономить место не получится. Для общего развития автору статьи советую мой перевод (https://habrahabr.ru/post/327606/) Рэндала почитать. Там как раз об этом.
              0
              Спасибо, отличная статья. Вообще поняла, что вообще не надо было писать ничего кроме 3го способа — так как это то, чем я хотела поделиться, первые 2 в реальности не использовала. Хотя опыт с переходом на BigInt описанный вами — через вью и триггер был.
            0
            Думаю, мне это не пригодится :)
              +10
              Про три часа поржал, да. Табличка в лучшем случае на сотни гигов, если не на терабайты. Даунтайм может и несколько суток занять.
              Правильный способ:
              1. Делаем новую таблицу с бигинтом, но пока без индексов.
              2. Копируем данные через insert… select
              3. Запоминаем последний скопированный ид, либо таймстемп, если есть поле таймстемп.
              4. Добавляем индексы.
              5. Глушим клиентов, копируем свежие данные, переименовываем таблицы.
              6. Возвращаем клиентов.
              7. Проверяем, что все ок и старую таблицу можно грохнуть
                0
                Да, переход на BIGINT самый нормальный способ, если это позволяет сделать код в клиентах.
                  +4

                  Если это PK, то придётся ещё тоже самое сделать во всех подчинённых таблицах, и, соответственно, грохнуть и восстановить связи.

                    +4
                    Можно еще быстрее. Создаем новую таблицу с бигинтом, с новым именем. ресидим в ней значение identity последним значением +1, переименовываем старую таблицу. Создаем вьюху с union-ом двух таблиц, именем старой таблицы и триггером instead of insert, который будет вставлять в новую таблицу. запускаем джоб, который будет переливать данные из старой таблицы в новую с identity_insert. Когда все перельется — грохаем старую таблицу и вьюху, переименовываем новую таблицу в старую.

                    Это все относится к случаям, когда в старой таблице хранятся все значения начиная с 1. У нас такая проблема была только с таблицами логов, которые регулярно чистятся, поэтому решение было простым — ресид на 1.
                      0
                      Это отличный способ — у нас тормозили триггеры. но insert делался только в паре процедур — после изменения все было отлично!
                        0
                        Собственно да, если вставка делается только из хранимых процедур, что является хорошим тоном, то достаточно изменения процедур, триггеры не нужны.
                    0
                    ну если только в каком-то стареньком проекте. сейчас уже вроде все дошли до того что делают ключи как sequential uniqueidentifier
                    и тут даж статья была с бенчмарками по которым было видно, что производительность от этого как минимум не страдает.
                      0
                      А ссылкой не поделитесь — в статье, что я нашел про MSSQL от 2015 года, как раз таки страдает https://habrahabr.ru/post/265437/
                        0
                        хм… именно эту статьи и имел ввиду. Видимо запамятовал. Но и там рассмотрено вроде только время вставки. Еще где-то тут же было про время чтения (статья про индексы была) и там не страдало. к сожалению на скорую руку найти не получилось. Но так или иначе Вы правы — вставка значительно дольше с гуидами, так что дальше можно не копать =)
                          +1
                          сравнение PK с 3 типами:

                          -- IDENTITY
                          create table TestTable (
                             id int identity(1,1) not null primary key clustered,
                             sequence int not null,
                             data char(250) not null default '');
                          go
                          
                          -- NEWID
                          create table TestTable (
                             id uniqueidentifier default newid() not null primary key clustered,
                             sequence int not null,
                             data char(250) not null default '');
                          go
                          
                          -- NEWSEQUENTIALID
                          create table TestTable (
                             id uniqueidentifier default newsequentialid() not null primary key clustered,
                             sequence int not null,
                             data char(250) not null default '');
                          go
                          


                          тестируем вставкой 50.000 записей

                          -- Insert 50,000 rows.
                          declare @count int;
                          set @count = 0;
                          while @count < 50000 begin
                             insert TestTable (sequence)
                             values (@count);
                          
                             set @count = @count + 1;
                          end;
                          go
                          


                          смотрим что получилось:

                          -- Get the number of read / writes for this session...
                          select reads, writes
                          from sys.dm_exec_sessions
                          where session_id = @@spid;
                          
                          -- Get the page fragmentation and density at the leaf level.
                          select index_type_desc, index_depth, page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent, record_count
                          from sys.dm_db_index_physical_stats(db_id(), object_id('TestTable'), null, null, 'detailed')
                          where index_level = 0;
                          go
                          


                          • insert(sec) Writes Leaf Pages Avg Page Used Avg Fragmentation
                          • IDENTITY(,) 16 1,720 1,667 98.9% 0.36%
                          • NEWID() 19 7,908 2,488 69.3% 99.2%
                          • NEWSEQUENTIALID() 17 1,788 1,725 99.9% 0.7%


                          на скорости записи и на фрагментации NEWSEQUENTIALID() практически ничем не уступает
                          IDENTITY(,)
                          0

                          Нет, нет, нет, и еще раз — нет!
                          Sequientiall Uniqueidentifier = GUID. Да, Sequential в качестве ID — быстрее вставляется, чем рандомный GUID в качестве ID. Хотя используя GUID как ID + bigint столбец для clustered index — получите такую же скорость, как и с Sequential.


                          Проблема при использовании GUID в качестве ключа не в скорости вставки ряда (по большому счёту в 99% ситуаций скорость вставки одного ряда не влияет на скорость всей системы). Проблема появляется при джойнах GUID'овых столбцов: просадка в производительности по сравнению с int'ами в качестве ключа — до 80 раз!

                            0

                            А при джойнах-то откуда просадка производительности берется?

                              +1

                              Разница в чтении и в работе со 128-битными числами против 64/32-битных чисел.
                              На маленьких таблицах это не заметно, но очень сильно заметно когда количество рядов превышает определённое количество (конкретные числа для появления просадки зависят от дисков, памяти, процессора, и самой архитектуры DB).

                              +1
                              Ерунду вы, батенька, написали, причем полную. Если у вас просадка производительности в 80 раз, то пора пересматривать архитектуру БД, а не GUID-INT типы. Не раз сталкивался с таблицами с несколькими миллиардами записей, все нормально с GUID. Нужно уметь их просто правильно готовить.
                              Недостаток GUID — это физический размер самого ключа и невозможность использвать в качестве смыслового значения (а вот инт неплохо юзать, так как это число).
                              Из своего 10+ летнего опыта работы с БД вынес простую вещь, если предполагается большой проект (высоконагруженая бд или большая база в несколько Тб), с шардингом, сегментированием, выделением отдельного вэахауса/оперативной БД, соответственно с ETL, репликой и проч. Используйте GUID. реально меньше проблем будет. Для маленьких БД int/bigint оптимальный вариант.

                              Насчет советов по переконвертации. Ребята, изменение типа поля — это огромный геморой. Очень. Если это первичный ключ, то надо будет еще грохнуть все внешние ключи и пересоздать их и соответвенно поля в зависимых таблицах… Все это вам придется делать батчиками что б не положить работу системы. причем сначало копировать данные в новые таблицы, потом лочить старые, докачивать остатки, переименовывать все… Перестравить with online все ключи… Тут целая песня. Врагу не пожелаешь такого.

                              P.S. Архитектора БД, который проспал такую ситуацию, надо гнать в шею!
                                0
                                Не люблю мерить опыт годами, но если вам актуально, то у меня опыт с БД 18+ лет — я вас победил :P

                                > с шардингом, сегментированием, выделением отдельного вэахауса/оперативной БД, соответственно с ETL, репликой и проч

                                Вот это всё — как относится к типу ключа (GUID vs INT)? Может мне не хватает практики, но еще ни разу не видел систем, где BIGINT'а не хватило бы. Но не суть. Вы связываете белое с резиновым.

                                И из своей практики по оптимизации архитектур баз данных говорю: GUID проигрывает по скорости INT'у и BIGINT'у. Короче: не спорьте, а протестируйте сами. Да вам сотни DBA тоже самое расскажут и покажут.

                                  0
                                  Вот это всё — как относится к типу ключа (GUID vs INT)? Может мне не хватает практики, но еще ни разу не видел систем, где BIGINT'а не хватило бы. Но не суть. Вы связываете белое с резиновым.

                                  Очень просто. Генерируемые GUIDы глобально уникальны. А IDENTITY на основе BIGINT уникальна только в пределах одной таблицы.

                                    –1
                                    Генерируемые GUIDы глобально уникальны. А IDENTITY на основе BIGINT уникальна только в пределах одной таблицы

                                    Капитан очевидность?

                                    Так может архитектура приложения кривая, если уникальность на уровне таблицы вызывает проблемы?
                                    Конечно это классно — минусовать, а не дискутировать, но было бы действительно интересно узнать: какую такую нерешаемую проблему решает GUID? Кроме того, что позволяет проектировать не ориентируясь на будущее.
                                      0

                                      Разумеется, этот тип данных он не решает никаких нерешаемых проблем. Он просто делает решения проще. Классический пример — логическая master-master репликация.

                                        0
                                        На некоторых ресурсах принято пояснять минусы.
                                      0
                                      Немного встряну в вашу дискуссию.
                                      На мой взгляд тут есть 2 стороны медали.
                                      Обычно уникальность в рамках таблицы (BIGINT) достаточна, если мы не собираемся объединять сущности в одну таблицу — что делается редко, хотя бывает.
                                      Кроме того вспомним, что чем «шире» запись тем дольше будет чтение, а опять же есть вариант не поместиться в стандартный для записи размер в MS SQL сервере.
                                      Про уникальны глобально — наверное это неплохо, если у вас например список сотрудников и ключ guid — и вы покупаете другую компанию и вам надо как то слить данные и у них (ура ура!) тоже есть таблица сотрудников, в которой тоже ключ guid.
                                      В принципе мне кажется, простите за банальность — для каждой задачи свое решение.
                                      К сожалению каждый из нас ограничен своим опытом, поэтому можно друг другу доказывать что одно решение лучше другого, а окажется, что решаемые задачи были разными — и в одном случае лучше решение первого человека, а в другом — второго.
                                        0
                                        для каждой задачи свое решение

                                        Согласен полностью.
                                          0
                                          Вот вам простой пример. В силу определенных обстоятельств, вы разнесли свою базу по нескольким серверам. Например, каждый филиал со совей базой. Филиалов таких пару десятков, соответственно столько же баз. Раз в сутки (или чаще, неважно) данные сливаются на центральный сервер, где в последсвии обрабатываются, делаются отчеты и т.п.
                                          У вас во всех базах структура идентична, пусть там будет таблица Sale с уникальным ключем SaleID. Ну вот мы стартуем синхронизацию и оказывается, что во всех офисах куча вставок записей. А поле то identity(1,1).Ай-яй-яй, получаем проблемы, начинается пляска с бубном, типа пусть в первом офисе SaleID будет строго нечетный, во 2-ом четным, в третьем четным отрицательным и т.п. Когда кол-во офисов переваливает за десятку, начинается полный отказ от идентити и переход на сиквенсы со сложном правилом генерации для каждой базы и т.п. И так по КАЖДОЙ таблице.
                                          Зато INT быстрее GUID © :-D
                                        0
                                        Я с вами и не собираюсь спорить. Я лишь указал на то, что разница в скорости в 80 раз, как вы писали выше — это мягко говоря неправда. Да, разница будет, GUID медленнее в силу своего размера. Но разница мизерная, львиная часть приложений её даже не заметит (я еще ни разу не сталкивался в своей практике). Мы же гвоорим про корпоративный софт, безнес приложения, а не научные исследования с расчетами…
                                        А вот как только вы сталкнетесь с распределенной системой (а вы с ней столкнетесь фактически в лобой интерпрайз системе), то получите вполне себе конкретные проблемы с синхронизацией данных. Если интересно, рекомендую ознакомится, как работет двустаронняя репликация (merge)… Возможно, вы измените свое мнение насчет GUID.
                                  +1
                                  Спасибо за напоминание — прямо сейчас делаю БД для проекта, увидел заголовок, задумался и полез проверить самую здоровую таблицу — конечно там integer был
                                    +1
                                    столкнулся как раз с такой ситуацией
                                    выяснил что у нас около 380млн неиспользовано — пересоздал таблицу и перелил данные.
                                    сначала актуальные — за последний год, а ночью курсором по 50 млн все остальное.

                                    так как был запланирован переход на другое ПО, то 380млн хватило и bigint делать не потребовалось
                                      0

                                      интересная тема, но местами очень косноязычные и неотредактированные фразы — приходится по нескольку раз перечитывать

                                        0
                                        Спасибо, перечитала еще раз, исправила некоторые описки и разбила пару предложений. Напишите, пожалуйста, какие места особенно кривые — поправлю.
                                          +1
                                          У вас приложение, работающее в режиме 24/7? То у вас осталось всего 114 дней, чтобы это исправить тип первичного ключа. Это не так уж и много, если у вас используется значение ключа как в веб-приложении, так и в клиентском.

                                          должно выглядеть как-то так


                                          У вас приложение, работающее в режиме 24/7? Значит у вас осталось всего 114 дней, чтобы исправить тип первичного ключа. Это не так уж и много, если значение ключа у вас используется как в веб-приложении, так и в клиентском.

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

                                            0
                                            Спасибо за пример! Про замыленный глаз — это точно!
                                            Про это конкретное предложение: получается корявый смысл, ведь я пыталась сказать, если значение ключа используется где-то вне БД, то 114 дней — мало, потому что надо менять и внешние приложения, и саму базу.
                                        0

                                        Аналитик, который проспал таблицу, в которую ежедневно льют по 10 млн записей — это что-то из области фантастики.

                                          +1
                                          Смотря что за проект, если это бывший стартап — там если начинается лавинообразный рост, работы столько, что команда не знает за что хвататься, и это при том, что идет найм, в таком случае это и происходит. Возможно есть еще сценарии, но в нормальном enterprise такого конечно быть не должно.
                                            +2

                                            Это не фантастика, а реалии госпроектов, где минимальный интервал между обнаружением проблемы и получением бюджета на ее решение — полгода.

                                              +2
                                              Похоже мне надо перестать жаловаться на то, что я в «медленном» на принятие решений enterprise
                                            +1

                                            Так же замечу, что на том же int вместо IDENTITY(1,1) хорошей практикой является использование IDENTITY(-2147483648, 1).

                                              +1
                                              Верно, тут дело в «по умолчанию» — и по умолчанию это (1,1) — и часто этот момент упускается. Кроме того, если по каким то причинам ключ отображается в приложении клиента — отрицательные значения уже не используют.
                                              0
                                              Есть ещё решение, которое конечно совсем не серебряная пуля, но в некоторых ситуациях вполне реально: удалить суррогатный ключ. О нём не думаешь сразу, но мы, когда столкнулись с переполнением IDENTITY, поняли, что можем вообще выкинуть суррогатный ключ и ничего не потеряем. Так и сделали и довольны :)
                                              Может конечно возникнуть вопрос, зачем он был изначально :)
                                                0
                                                Вы абсолютно правы, иногда у разработчиков замылевается глаз и никто не замечает, что ключ нигде не используется. Тут либо поменять тип — либо что тоже прекрасно — удалить его.
                                                В принципе мне кажется хорошим советом удалить вообще все, что не используется.
                                                  0
                                                  enum в БД делать отдельной таблицей или оставить целым числом?
                                                    0
                                                    Неожиданный вопрос. И хотелось его расширить, но могу пофантазировать.
                                                    Я бы перефразировала и разделила на 2 вопроса:
                                                    1) надо ли делать отдельный тип в базе или использовать INT (или TINYINT)
                                                    2) надо ли делать таблицу справочник или а вот тут не ясно — а расшифровка что значат целые числа где будет?

                                                    На первый вопрос — смотрите вы когда делаете свой перечисляемый типа в БД — вы получается автоматически получите качественные данные — то есть в БД будут только те данные которые вы ждете. И если у вас 5 возможных значений — то их и будет 5 и туда не прокрадется какая-нибудь неожиданная штука.
                                                    Этот же эффект можно достичь чек constraint ами или внешним ключом.
                                                    Если вариант внешний ключ — вы делаете таблицу справочник, и используете внешний ключ. Мне такой способ нравится. Потому что тут у вас и тип стандартный, и вы получаете данные которых ждали, и у вас тут же расшифровка.
                                                    Есть минус — если у вас например таких таблиц будет много, ну например 150. Тогда делается одна таблица справочник в дополнении к описанию и значению — добавляете тип. Тут уже с внешними ключами хуже, но зато нет 150 таблиц.
                                                    0
                                                    Крайне спорное решение. Как обеспечивать уникальность записей тогда? Если создавать натуральный ключ, то как быть с редактированием?.. К тому же это все равно приводит к пересозданию внешних ключей и всех индексов. Да еще и запросы переписывать, связка то поменялась. А если архитектор проспал 20млн. вставок, то про абстракный слой он и подавно не думал… Не-не, палка о двух концах.
                                                      0

                                                      Быстрее всего переполняются суррогатные ключи в таблицах-связках для реализации связей вида many-to-many. Как правило, выкидывание суррогатного ключа из такой таблицы лишь упрощает работу с ней.


                                                      Как обеспечивать уникальность записей тогда?

                                                      Не вижу как наличие суррогатного ключа обеспечивает уникальность записей.

                                                        0
                                                        Согласен, в связках — вполне возможно, т.к. это чисто техническая реализация many2many отношения. Но если же эта связка имеет смысловую нагрузку, то я б рассматривал только суррогатный ключ.
                                                        Простой пример. Таблица Cars, таблицы Drivers. Связка между ними через CarDrivers. Если в простейшем случае можно обойтись CarID, DriverID и создать первичный ключ по этим двум полям (классическое many2many), то при появлении смысловой нагрузки (например, сопаставление водителя и машины только через приказ) и появлении даты (водитель взял машину, отдал, взял другую и т.п.) теряется уникальность для связки CarID-DriverID…
                                                        Техническая таблица превратилась в полноценную сущность. Такое частенько бывает, особенно при интенсивной разработке. Так что либо кастыли либо болезненное изменение схемы
                                                        Не вижу как наличие суррогатного ключа обеспечивает уникальность записей.

                                                        Я имел в виду, что сурогатный ключ обеспечит вам беспроблемную уникальность (а значит и ссылочность) сегодня и завтра, чего не скажешь про натуральные ключи. Кто ж знает, как бизнес будет развиваться через 1-2-5 лет?.. Базы данных существуют ооочень долго.
                                                    0
                                                    Много лет назад использование int32 на ключ повсеместно и в бд, и в приложении, стало основной причиной разработки новой системы с нуля. Темпы роста показывали дно в ближней перспективе — пары лет. А разработчиков старой уже не было.

                                                    Only users with full accounts can post comments. Log in, please.