Comments 50
Поменять тип первичного ключа на BIGINT
A может сразу на varchar и написать свой обработчик beforeInsert?
если можете, переключитесь на модель Recovery Mode в SimpleНе очень то ответственно давать такой совет, не предупреждая о последствиях, например о проблемах с бэкапом лога.
Подобное изменение схемы все равно не может быть сделано параллельно со вставками новых значений. Достаточно сделать бэкап перед обновлением и полный бэкап после обратного переключения на Full, и никаких проблем с бэкапом лога не будет.
Правильный способ:
1. Делаем новую таблицу с бигинтом, но пока без индексов.
2. Копируем данные через insert… select
3. Запоминаем последний скопированный ид, либо таймстемп, если есть поле таймстемп.
4. Добавляем индексы.
5. Глушим клиентов, копируем свежие данные, переименовываем таблицы.
6. Возвращаем клиентов.
7. Проверяем, что все ок и старую таблицу можно грохнуть
Если это PK, то придётся ещё тоже самое сделать во всех подчинённых таблицах, и, соответственно, грохнуть и восстановить связи.
Это все относится к случаям, когда в старой таблице хранятся все значения начиная с 1. У нас такая проблема была только с таблицами логов, которые регулярно чистятся, поэтому решение было простым — ресид на 1.
и тут даж статья была с бенчмарками по которым было видно, что производительность от этого как минимум не страдает.
-- 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(,)
Нет, нет, нет, и еще раз — нет!
Sequientiall Uniqueidentifier = GUID. Да, Sequential в качестве ID — быстрее вставляется, чем рандомный GUID в качестве ID. Хотя используя GUID как ID + bigint столбец для clustered index — получите такую же скорость, как и с Sequential.
Проблема при использовании GUID в качестве ключа не в скорости вставки ряда (по большому счёту в 99% ситуаций скорость вставки одного ряда не влияет на скорость всей системы). Проблема появляется при джойнах GUID'овых столбцов: просадка в производительности по сравнению с int'ами в качестве ключа — до 80 раз!
А при джойнах-то откуда просадка производительности берется?
Разница в чтении и в работе со 128-битными числами против 64/32-битных чисел.
На маленьких таблицах это не заметно, но очень сильно заметно когда количество рядов превышает определённое количество (конкретные числа для появления просадки зависят от дисков, памяти, процессора, и самой архитектуры DB).
Недостаток GUID — это физический размер самого ключа и невозможность использвать в качестве смыслового значения (а вот инт неплохо юзать, так как это число).
Из своего 10+ летнего опыта работы с БД вынес простую вещь, если предполагается большой проект (высоконагруженая бд или большая база в несколько Тб), с шардингом, сегментированием, выделением отдельного вэахауса/оперативной БД, соответственно с ETL, репликой и проч. Используйте GUID. реально меньше проблем будет. Для маленьких БД int/bigint оптимальный вариант.
Насчет советов по переконвертации. Ребята, изменение типа поля — это огромный геморой. Очень. Если это первичный ключ, то надо будет еще грохнуть все внешние ключи и пересоздать их и соответвенно поля в зависимых таблицах… Все это вам придется делать батчиками что б не положить работу системы. причем сначало копировать данные в новые таблицы, потом лочить старые, докачивать остатки, переименовывать все… Перестравить with online все ключи… Тут целая песня. Врагу не пожелаешь такого.
P.S. Архитектора БД, который проспал такую ситуацию, надо гнать в шею!
> с шардингом, сегментированием, выделением отдельного вэахауса/оперативной БД, соответственно с ETL, репликой и проч
Вот это всё — как относится к типу ключа (GUID vs INT)? Может мне не хватает практики, но еще ни разу не видел систем, где BIGINT'а не хватило бы. Но не суть. Вы связываете белое с резиновым.
И из своей практики по оптимизации архитектур баз данных говорю: GUID проигрывает по скорости INT'у и BIGINT'у. Короче: не спорьте, а протестируйте сами. Да вам сотни DBA тоже самое расскажут и покажут.
Вот это всё — как относится к типу ключа (GUID vs INT)? Может мне не хватает практики, но еще ни разу не видел систем, где BIGINT'а не хватило бы. Но не суть. Вы связываете белое с резиновым.
Очень просто. Генерируемые GUIDы глобально уникальны. А IDENTITY на основе BIGINT уникальна только в пределах одной таблицы.
Генерируемые GUIDы глобально уникальны. А IDENTITY на основе BIGINT уникальна только в пределах одной таблицы
Капитан очевидность?
Так может архитектура приложения кривая, если уникальность на уровне таблицы вызывает проблемы?
Конечно это классно — минусовать, а не дискутировать, но было бы действительно интересно узнать: какую такую нерешаемую проблему решает GUID? Кроме того, что позволяет проектировать не ориентируясь на будущее.
На мой взгляд тут есть 2 стороны медали.
Обычно уникальность в рамках таблицы (BIGINT) достаточна, если мы не собираемся объединять сущности в одну таблицу — что делается редко, хотя бывает.
Кроме того вспомним, что чем «шире» запись тем дольше будет чтение, а опять же есть вариант не поместиться в стандартный для записи размер в MS SQL сервере.
Про уникальны глобально — наверное это неплохо, если у вас например список сотрудников и ключ guid — и вы покупаете другую компанию и вам надо как то слить данные и у них (ура ура!) тоже есть таблица сотрудников, в которой тоже ключ guid.
В принципе мне кажется, простите за банальность — для каждой задачи свое решение.
К сожалению каждый из нас ограничен своим опытом, поэтому можно друг другу доказывать что одно решение лучше другого, а окажется, что решаемые задачи были разными — и в одном случае лучше решение первого человека, а в другом — второго.
для каждой задачи свое решение
Согласен полностью.
У вас во всех базах структура идентична, пусть там будет таблица Sale с уникальным ключем SaleID. Ну вот мы стартуем синхронизацию и оказывается, что во всех офисах куча вставок записей. А поле то identity(1,1).Ай-яй-яй, получаем проблемы, начинается пляска с бубном, типа пусть в первом офисе SaleID будет строго нечетный, во 2-ом четным, в третьем четным отрицательным и т.п. Когда кол-во офисов переваливает за десятку, начинается полный отказ от идентити и переход на сиквенсы со сложном правилом генерации для каждой базы и т.п. И так по КАЖДОЙ таблице.
Зато INT быстрее GUID © :-D
А вот как только вы сталкнетесь с распределенной системой (а вы с ней столкнетесь фактически в лобой интерпрайз системе), то получите вполне себе конкретные проблемы с синхронизацией данных. Если интересно, рекомендую ознакомится, как работет двустаронняя репликация (merge)… Возможно, вы измените свое мнение насчет GUID.
выяснил что у нас около 380млн неиспользовано — пересоздал таблицу и перелил данные.
сначала актуальные — за последний год, а ночью курсором по 50 млн все остальное.
так как был запланирован переход на другое ПО, то 380млн хватило и bigint делать не потребовалось
интересная тема, но местами очень косноязычные и неотредактированные фразы — приходится по нескольку раз перечитывать
У вас приложение, работающее в режиме 24/7? То у вас осталось всего 114 дней, чтобы это исправить тип первичного ключа. Это не так уж и много, если у вас используется значение ключа как в веб-приложении, так и в клиентском.
должно выглядеть как-то так
У вас приложение, работающее в режиме 24/7? Значит у вас осталось всего 114 дней, чтобы исправить тип первичного ключа. Это не так уж и много, если значение ключа у вас используется как в веб-приложении, так и в клиентском.
но даже так последнее предложение немного корявое, так как получается, что если у ключ используется как в веб, так и в клиентском — то 114 дней — не так уж и много, а если только в веб — то те же 114 — много?
кое-где лишние запятые
просто дайте прочесть кому-то другому, самому найти все ошибки для автора обычно очень сложно. глаз замыливается, читаешь и не видишь ошибок =)
Аналитик, который проспал таблицу, в которую ежедневно льют по 10 млн записей — это что-то из области фантастики.
Это не фантастика, а реалии госпроектов, где минимальный интервал между обнаружением проблемы и получением бюджета на ее решение — полгода.
Так же замечу, что на том же int вместо IDENTITY(1,1) хорошей практикой является использование IDENTITY(-2147483648, 1).
Может конечно возникнуть вопрос, зачем он был изначально :)
В принципе мне кажется хорошим советом удалить вообще все, что не используется.
Я бы перефразировала и разделила на 2 вопроса:
1) надо ли делать отдельный тип в базе или использовать INT (или TINYINT)
2) надо ли делать таблицу справочник или а вот тут не ясно — а расшифровка что значат целые числа где будет?
На первый вопрос — смотрите вы когда делаете свой перечисляемый типа в БД — вы получается автоматически получите качественные данные — то есть в БД будут только те данные которые вы ждете. И если у вас 5 возможных значений — то их и будет 5 и туда не прокрадется какая-нибудь неожиданная штука.
Этот же эффект можно достичь чек constraint ами или внешним ключом.
Если вариант внешний ключ — вы делаете таблицу справочник, и используете внешний ключ. Мне такой способ нравится. Потому что тут у вас и тип стандартный, и вы получаете данные которых ждали, и у вас тут же расшифровка.
Есть минус — если у вас например таких таблиц будет много, ну например 150. Тогда делается одна таблица справочник в дополнении к описанию и значению — добавляете тип. Тут уже с внешними ключами хуже, но зато нет 150 таблиц.
Быстрее всего переполняются суррогатные ключи в таблицах-связках для реализации связей вида many-to-many. Как правило, выкидывание суррогатного ключа из такой таблицы лишь упрощает работу с ней.
Как обеспечивать уникальность записей тогда?
Не вижу как наличие суррогатного ключа обеспечивает уникальность записей.
Простой пример. Таблица Cars, таблицы Drivers. Связка между ними через CarDrivers. Если в простейшем случае можно обойтись CarID, DriverID и создать первичный ключ по этим двум полям (классическое many2many), то при появлении смысловой нагрузки (например, сопаставление водителя и машины только через приказ) и появлении даты (водитель взял машину, отдал, взял другую и т.п.) теряется уникальность для связки CarID-DriverID…
Техническая таблица превратилась в полноценную сущность. Такое частенько бывает, особенно при интенсивной разработке. Так что либо кастыли либо болезненное изменение схемы
Не вижу как наличие суррогатного ключа обеспечивает уникальность записей.
Я имел в виду, что сурогатный ключ обеспечит вам беспроблемную уникальность (а значит и ссылочность) сегодня и завтра, чего не скажешь про натуральные ключи. Кто ж знает, как бизнес будет развиваться через 1-2-5 лет?.. Базы данных существуют ооочень долго.
Что делать, если в PK Identity закончились значения?