Изначально кажется, что 2 миллиарда записей – это много. Но если, у вас ежедневно добавляется 10 млн. новых записей? И уже израсходовано более 1 млрд. значений? У вас приложение, работающее в режиме 24/7? То у вас осталось всего 114 дней, чтобы это исправить тип первичного ключа. Это не так уж и много, если у вас используется значение ключа как в веб-приложении, так и в клиентском.
Если описанная ситуация вам знакома, и Вы заметили эту прискорбную деталь – у Вас заканчиваются значения первичного ключа – слишком поздно, то данная статья – для Вас. В статье Вы найдете скрипты, которые приведены для таблицы TableWithPKViolation, в которой поле TableWithPKViolationId вызывает проблему.
В худшем случае, Вы столкнулись с ошибкой “Arithmetic overflow error converting IDENTITY to data type int”. Это означает, что значения первичного ключа уже закончились, и Ваше приложение перестало работать. В данном случае Вы можете использовать следующие решения:
- Поменять тип первичного ключа на BIGINT. Всем и каждому понятно, что лучший вариант — это сесть в машину времени и изменить INTна BIGINT там, в прошлом. Но Вы можете сделать это и сейчас, если поле TableWithPKViolationId не используется в серверном и клиентском приложении, то у вас есть возможность оперативно и безболезненно поменять тип. Сделайте это, и не тратьте время на остальную статью. Обратите внимание, что если в Вашей таблице больше 1 млрд. записей, то изменение будет применяться, т.е. может занять больше 3 часов, в зависимости от мощности вашего сервера, и потребует дополнительного места в логе транзакций (если можете, переключитесь на модель Recovery Mode в Simple). Скрипт для изменения следующий:
ALTER TABLE TableWithPKViolation ALTER COLUMN TableWithPKViolationId BIGINT;
Если данный способ Вам недоступен, необходимо запланировать переход ключа на BIGINT как можно быстрее.
- Использовать отрицательные значения. Обычно, при использовании identity используется по умолчанию IDENTITY(1,1). Когда значение подходит к 2 миллиардам записей, Вы можете сделать сброс начального значения, используя следующую команду:
DBCC CHECKIDENT (TableWithPKViolation, - 2147483647, reseed)
и таким образом получить гораздо больше времени для перехода на BIGINT. Единственное неудобство данного решения – это отрицательные значения первичного ключа. Проверьте, что Ваша бизнес-логика допускает отрицательные значения. Пожалуй, это самое легкое решение.
- 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.
Новые идентификаторы, конечно, будут выдаваться по нарастающей, однако, необходимо продумать логику сортировки новых идентификаторов таким образом, чтобы они шли после ранее выданных старых идентификаторов, даже если арифметические значение новых меньше.
Описанные в данной статье временные решения проблемы того, что значения первичного ключа внезапно закончились, помогают Вам выиграть время и поддержать систему в рабочем состоянии, пока Вы будете изменять систему и программу на новый тип данных.
Наилучшим решением является мониторинг пограничных значений и переход на соответствующие типы данных заранее.
Архив с кодом
Комментарии (50)
Varim
29.06.2017 15:39+2если можете, переключитесь на модель Recovery Mode в Simple
Не очень то ответственно давать такой совет, не предупреждая о последствиях, например о проблемах с бэкапом лога.
KristinaMyLife
29.06.2017 15:43Согласна, тут в зависимости от срочности — если это таблица из-за которой работа дальше не идет, что нибудь типа информации о продажах и пока не починят, то продавать не могут это одно. А если что-то не очень важное, то совсем другое.
mayorovp
29.06.2017 15:47+1Подобное изменение схемы все равно не может быть сделано параллельно со вставками новых значений. Достаточно сделать бэкап перед обновлением и полный бэкап после обратного переключения на Full, и никаких проблем с бэкапом лога не будет.
minamoto
29.06.2017 18:12+2Более того — непонятна цель такого совета, т.к. эта операция вроде бы не относится к минимально-логируемым операциям и сэкономить место не получится. Для общего развития автору статьи советую мой перевод (https://habrahabr.ru/post/327606/) Рэндала почитать. Там как раз об этом.
KristinaMyLife
30.06.2017 09:49Спасибо, отличная статья. Вообще поняла, что вообще не надо было писать ничего кроме 3го способа — так как это то, чем я хотела поделиться, первые 2 в реальности не использовала. Хотя опыт с переходом на BigInt описанный вами — через вью и триггер был.
vanxant
29.06.2017 16:47+10Про три часа поржал, да. Табличка в лучшем случае на сотни гигов, если не на терабайты. Даунтайм может и несколько суток занять.
Правильный способ:
1. Делаем новую таблицу с бигинтом, но пока без индексов.
2. Копируем данные через insert… select
3. Запоминаем последний скопированный ид, либо таймстемп, если есть поле таймстемп.
4. Добавляем индексы.
5. Глушим клиентов, копируем свежие данные, переименовываем таблицы.
6. Возвращаем клиентов.
7. Проверяем, что все ок и старую таблицу можно грохнутьKristinaMyLife
29.06.2017 16:59Да, переход на BIGINT самый нормальный способ, если это позволяет сделать код в клиентах.
QuickJoey
29.06.2017 18:04+4Если это PK, то придётся ещё тоже самое сделать во всех подчинённых таблицах, и, соответственно, грохнуть и восстановить связи.
minamoto
29.06.2017 18:19+4Можно еще быстрее. Создаем новую таблицу с бигинтом, с новым именем. ресидим в ней значение identity последним значением +1, переименовываем старую таблицу. Создаем вьюху с union-ом двух таблиц, именем старой таблицы и триггером instead of insert, который будет вставлять в новую таблицу. запускаем джоб, который будет переливать данные из старой таблицы в новую с identity_insert. Когда все перельется — грохаем старую таблицу и вьюху, переименовываем новую таблицу в старую.
Это все относится к случаям, когда в старой таблице хранятся все значения начиная с 1. У нас такая проблема была только с таблицами логов, которые регулярно чистятся, поэтому решение было простым — ресид на 1.KristinaMyLife
29.06.2017 19:08Это отличный способ — у нас тормозили триггеры. но insert делался только в паре процедур — после изменения все было отлично!
minamoto
29.06.2017 19:15Собственно да, если вставка делается только из хранимых процедур, что является хорошим тоном, то достаточно изменения процедур, триггеры не нужны.
StallinHrusch
29.06.2017 16:57ну если только в каком-то стареньком проекте. сейчас уже вроде все дошли до того что делают ключи как sequential uniqueidentifier
и тут даж статья была с бенчмарками по которым было видно, что производительность от этого как минимум не страдает.
Stas911
29.06.2017 17:16А ссылкой не поделитесь — в статье, что я нашел про MSSQL от 2015 года, как раз таки страдает https://habrahabr.ru/post/265437/
StallinHrusch
29.06.2017 17:22хм… именно эту статьи и имел ввиду. Видимо запамятовал. Но и там рассмотрено вроде только время вставки. Еще где-то тут же было про время чтения (статья про индексы была) и там не страдало. к сожалению на скорую руку найти не получилось. Но так или иначе Вы правы — вставка значительно дольше с гуидами, так что дальше можно не копать =)
BalinTomsk
29.06.2017 19:14+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(,)
greendimka
30.06.2017 12:19Нет, нет, нет, и еще раз — нет!
Sequientiall Uniqueidentifier = GUID. Да, Sequential в качестве ID — быстрее вставляется, чем рандомный GUID в качестве ID. Хотя используя GUID как ID + bigint столбец для clustered index — получите такую же скорость, как и с Sequential.
Проблема при использовании GUID в качестве ключа не в скорости вставки ряда (по большому счёту в 99% ситуаций скорость вставки одного ряда не влияет на скорость всей системы). Проблема появляется при джойнах GUID'овых столбцов: просадка в производительности по сравнению с int'ами в качестве ключа — до 80 раз!
mayorovp
30.06.2017 12:24А при джойнах-то откуда просадка производительности берется?
greendimka
30.06.2017 12:30+1Разница в чтении и в работе со 128-битными числами против 64/32-битных чисел.
На маленьких таблицах это не заметно, но очень сильно заметно когда количество рядов превышает определённое количество (конкретные числа для появления просадки зависят от дисков, памяти, процессора, и самой архитектуры DB).
fillrate
30.06.2017 14:46+1Ерунду вы, батенька, написали, причем полную. Если у вас просадка производительности в 80 раз, то пора пересматривать архитектуру БД, а не GUID-INT типы. Не раз сталкивался с таблицами с несколькими миллиардами записей, все нормально с GUID. Нужно уметь их просто правильно готовить.
Недостаток GUID — это физический размер самого ключа и невозможность использвать в качестве смыслового значения (а вот инт неплохо юзать, так как это число).
Из своего 10+ летнего опыта работы с БД вынес простую вещь, если предполагается большой проект (высоконагруженая бд или большая база в несколько Тб), с шардингом, сегментированием, выделением отдельного вэахауса/оперативной БД, соответственно с ETL, репликой и проч. Используйте GUID. реально меньше проблем будет. Для маленьких БД int/bigint оптимальный вариант.
Насчет советов по переконвертации. Ребята, изменение типа поля — это огромный геморой. Очень. Если это первичный ключ, то надо будет еще грохнуть все внешние ключи и пересоздать их и соответвенно поля в зависимых таблицах… Все это вам придется делать батчиками что б не положить работу системы. причем сначало копировать данные в новые таблицы, потом лочить старые, докачивать остатки, переименовывать все… Перестравить with online все ключи… Тут целая песня. Врагу не пожелаешь такого.
P.S. Архитектора БД, который проспал такую ситуацию, надо гнать в шею!greendimka
30.06.2017 15:10Не люблю мерить опыт годами, но если вам актуально, то у меня опыт с БД 18+ лет — я вас победил :P
> с шардингом, сегментированием, выделением отдельного вэахауса/оперативной БД, соответственно с ETL, репликой и проч
Вот это всё — как относится к типу ключа (GUID vs INT)? Может мне не хватает практики, но еще ни разу не видел систем, где BIGINT'а не хватило бы. Но не суть. Вы связываете белое с резиновым.
И из своей практики по оптимизации архитектур баз данных говорю: GUID проигрывает по скорости INT'у и BIGINT'у. Короче: не спорьте, а протестируйте сами. Да вам сотни DBA тоже самое расскажут и покажут.
mayorovp
30.06.2017 15:16Вот это всё — как относится к типу ключа (GUID vs INT)? Может мне не хватает практики, но еще ни разу не видел систем, где BIGINT'а не хватило бы. Но не суть. Вы связываете белое с резиновым.
Очень просто. Генерируемые GUIDы глобально уникальны. А IDENTITY на основе BIGINT уникальна только в пределах одной таблицы.
greendimka
30.06.2017 15:27-1Генерируемые GUIDы глобально уникальны. А IDENTITY на основе BIGINT уникальна только в пределах одной таблицы
Капитан очевидность?
Так может архитектура приложения кривая, если уникальность на уровне таблицы вызывает проблемы?
Конечно это классно — минусовать, а не дискутировать, но было бы действительно интересно узнать: какую такую нерешаемую проблему решает GUID? Кроме того, что позволяет проектировать не ориентируясь на будущее.mayorovp
30.06.2017 15:43Разумеется, этот тип данных он не решает никаких нерешаемых проблем. Он просто делает решения проще. Классический пример — логическая master-master репликация.
KristinaMyLife
30.06.2017 15:38Немного встряну в вашу дискуссию.
На мой взгляд тут есть 2 стороны медали.
Обычно уникальность в рамках таблицы (BIGINT) достаточна, если мы не собираемся объединять сущности в одну таблицу — что делается редко, хотя бывает.
Кроме того вспомним, что чем «шире» запись тем дольше будет чтение, а опять же есть вариант не поместиться в стандартный для записи размер в MS SQL сервере.
Про уникальны глобально — наверное это неплохо, если у вас например список сотрудников и ключ guid — и вы покупаете другую компанию и вам надо как то слить данные и у них (ура ура!) тоже есть таблица сотрудников, в которой тоже ключ guid.
В принципе мне кажется, простите за банальность — для каждой задачи свое решение.
К сожалению каждый из нас ограничен своим опытом, поэтому можно друг другу доказывать что одно решение лучше другого, а окажется, что решаемые задачи были разными — и в одном случае лучше решение первого человека, а в другом — второго.fillrate
05.07.2017 15:28Вот вам простой пример. В силу определенных обстоятельств, вы разнесли свою базу по нескольким серверам. Например, каждый филиал со совей базой. Филиалов таких пару десятков, соответственно столько же баз. Раз в сутки (или чаще, неважно) данные сливаются на центральный сервер, где в последсвии обрабатываются, делаются отчеты и т.п.
У вас во всех базах структура идентична, пусть там будет таблица Sale с уникальным ключем SaleID. Ну вот мы стартуем синхронизацию и оказывается, что во всех офисах куча вставок записей. А поле то identity(1,1).Ай-яй-яй, получаем проблемы, начинается пляска с бубном, типа пусть в первом офисе SaleID будет строго нечетный, во 2-ом четным, в третьем четным отрицательным и т.п. Когда кол-во офисов переваливает за десятку, начинается полный отказ от идентити и переход на сиквенсы со сложном правилом генерации для каждой базы и т.п. И так по КАЖДОЙ таблице.
Зато INT быстрее GUID © :-D
fillrate
05.07.2017 15:19Я с вами и не собираюсь спорить. Я лишь указал на то, что разница в скорости в 80 раз, как вы писали выше — это мягко говоря неправда. Да, разница будет, GUID медленнее в силу своего размера. Но разница мизерная, львиная часть приложений её даже не заметит (я еще ни разу не сталкивался в своей практике). Мы же гвоорим про корпоративный софт, безнес приложения, а не научные исследования с расчетами…
А вот как только вы сталкнетесь с распределенной системой (а вы с ней столкнетесь фактически в лобой интерпрайз системе), то получите вполне себе конкретные проблемы с синхронизацией данных. Если интересно, рекомендую ознакомится, как работет двустаронняя репликация (merge)… Возможно, вы измените свое мнение насчет GUID.
Stas911
29.06.2017 17:10+1Спасибо за напоминание — прямо сейчас делаю БД для проекта, увидел заголовок, задумался и полез проверить самую здоровую таблицу — конечно там integer был
qwertEHOK
29.06.2017 19:08+1столкнулся как раз с такой ситуацией
выяснил что у нас около 380млн неиспользовано — пересоздал таблицу и перелил данные.
сначала актуальные — за последний год, а ночью курсором по 50 млн все остальное.
так как был запланирован переход на другое ПО, то 380млн хватило и bigint делать не потребовалось
Dreyk
29.06.2017 23:01интересная тема, но местами очень косноязычные и неотредактированные фразы — приходится по нескольку раз перечитывать
KristinaMyLife
30.06.2017 06:32Спасибо, перечитала еще раз, исправила некоторые описки и разбила пару предложений. Напишите, пожалуйста, какие места особенно кривые — поправлю.
Dreyk
30.06.2017 08:20+1У вас приложение, работающее в режиме 24/7? То у вас осталось всего 114 дней, чтобы это исправить тип первичного ключа. Это не так уж и много, если у вас используется значение ключа как в веб-приложении, так и в клиентском.
должно выглядеть как-то так
У вас приложение, работающее в режиме 24/7? Значит у вас осталось всего 114 дней, чтобы исправить тип первичного ключа. Это не так уж и много, если значение ключа у вас используется как в веб-приложении, так и в клиентском.
но даже так последнее предложение немного корявое, так как получается, что если у ключ используется как в веб, так и в клиентском — то 114 дней — не так уж и много, а если только в веб — то те же 114 — много?
кое-где лишние запятые
просто дайте прочесть кому-то другому, самому найти все ошибки для автора обычно очень сложно. глаз замыливается, читаешь и не видишь ошибок =)KristinaMyLife
30.06.2017 08:37Спасибо за пример! Про замыленный глаз — это точно!
Про это конкретное предложение: получается корявый смысл, ведь я пыталась сказать, если значение ключа используется где-то вне БД, то 114 дней — мало, потому что надо менять и внешние приложения, и саму базу.
questor
30.06.2017 07:13Аналитик, который проспал таблицу, в которую ежедневно льют по 10 млн записей — это что-то из области фантастики.
KristinaMyLife
30.06.2017 08:16+1Смотря что за проект, если это бывший стартап — там если начинается лавинообразный рост, работы столько, что команда не знает за что хвататься, и это при том, что идет найм, в таком случае это и происходит. Возможно есть еще сценарии, но в нормальном enterprise такого конечно быть не должно.
mayorovp
30.06.2017 08:47+2Это не фантастика, а реалии госпроектов, где минимальный интервал между обнаружением проблемы и получением бюджета на ее решение — полгода.
KristinaMyLife
30.06.2017 08:59+2Похоже мне надо перестать жаловаться на то, что я в «медленном» на принятие решений enterprise
greendimka
30.06.2017 12:24+1Так же замечу, что на том же int вместо IDENTITY(1,1) хорошей практикой является использование IDENTITY(-2147483648, 1).
KristinaMyLife
30.06.2017 12:35+1Верно, тут дело в «по умолчанию» — и по умолчанию это (1,1) — и часто этот момент упускается. Кроме того, если по каким то причинам ключ отображается в приложении клиента — отрицательные значения уже не используют.
timramone
30.06.2017 22:57Есть ещё решение, которое конечно совсем не серебряная пуля, но в некоторых ситуациях вполне реально: удалить суррогатный ключ. О нём не думаешь сразу, но мы, когда столкнулись с переполнением IDENTITY, поняли, что можем вообще выкинуть суррогатный ключ и ничего не потеряем. Так и сделали и довольны :)
Может конечно возникнуть вопрос, зачем он был изначально :)KristinaMyLife
01.07.2017 20:46Вы абсолютно правы, иногда у разработчиков замылевается глаз и никто не замечает, что ключ нигде не используется. Тут либо поменять тип — либо что тоже прекрасно — удалить его.
В принципе мне кажется хорошим советом удалить вообще все, что не используется.
Varim
01.07.2017 21:20enum в БД делать отдельной таблицей или оставить целым числом?
KristinaMyLife
02.07.2017 00:34Неожиданный вопрос. И хотелось его расширить, но могу пофантазировать.
Я бы перефразировала и разделила на 2 вопроса:
1) надо ли делать отдельный тип в базе или использовать INT (или TINYINT)
2) надо ли делать таблицу справочник или а вот тут не ясно — а расшифровка что значат целые числа где будет?
На первый вопрос — смотрите вы когда делаете свой перечисляемый типа в БД — вы получается автоматически получите качественные данные — то есть в БД будут только те данные которые вы ждете. И если у вас 5 возможных значений — то их и будет 5 и туда не прокрадется какая-нибудь неожиданная штука.
Этот же эффект можно достичь чек constraint ами или внешним ключом.
Если вариант внешний ключ — вы делаете таблицу справочник, и используете внешний ключ. Мне такой способ нравится. Потому что тут у вас и тип стандартный, и вы получаете данные которых ждали, и у вас тут же расшифровка.
Есть минус — если у вас например таких таблиц будет много, ну например 150. Тогда делается одна таблица справочник в дополнении к описанию и значению — добавляете тип. Тут уже с внешними ключами хуже, но зато нет 150 таблиц.
fillrate
05.07.2017 15:33Крайне спорное решение. Как обеспечивать уникальность записей тогда? Если создавать натуральный ключ, то как быть с редактированием?.. К тому же это все равно приводит к пересозданию внешних ключей и всех индексов. Да еще и запросы переписывать, связка то поменялась. А если архитектор проспал 20млн. вставок, то про абстракный слой он и подавно не думал… Не-не, палка о двух концах.
mayorovp
05.07.2017 15:38Быстрее всего переполняются суррогатные ключи в таблицах-связках для реализации связей вида many-to-many. Как правило, выкидывание суррогатного ключа из такой таблицы лишь упрощает работу с ней.
Как обеспечивать уникальность записей тогда?
Не вижу как наличие суррогатного ключа обеспечивает уникальность записей.
fillrate
07.07.2017 18:21Согласен, в связках — вполне возможно, т.к. это чисто техническая реализация many2many отношения. Но если же эта связка имеет смысловую нагрузку, то я б рассматривал только суррогатный ключ.
Простой пример. Таблица Cars, таблицы Drivers. Связка между ними через CarDrivers. Если в простейшем случае можно обойтись CarID, DriverID и создать первичный ключ по этим двум полям (классическое many2many), то при появлении смысловой нагрузки (например, сопаставление водителя и машины только через приказ) и появлении даты (водитель взял машину, отдал, взял другую и т.п.) теряется уникальность для связки CarID-DriverID…
Техническая таблица превратилась в полноценную сущность. Такое частенько бывает, особенно при интенсивной разработке. Так что либо кастыли либо болезненное изменение схемы
Не вижу как наличие суррогатного ключа обеспечивает уникальность записей.
Я имел в виду, что сурогатный ключ обеспечит вам беспроблемную уникальность (а значит и ссылочность) сегодня и завтра, чего не скажешь про натуральные ключи. Кто ж знает, как бизнес будет развиваться через 1-2-5 лет?.. Базы данных существуют ооочень долго.
mik
08.07.2017 13:53Много лет назад использование int32 на ключ повсеместно и в бд, и в приложении, стало основной причиной разработки новой системы с нуля. Темпы роста показывали дно в ближней перспективе — пары лет. А разработчиков старой уже не было.
SyrexS
A может сразу на varchar и написать свой обработчик beforeInsert?