Pull to refresh

Comments 50

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

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

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

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

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

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

Это все относится к случаям, когда в старой таблице хранятся все значения начиная с 1. У нас такая проблема была только с таблицами логов, которые регулярно чистятся, поэтому решение было простым — ресид на 1.
Это отличный способ — у нас тормозили триггеры. но insert делался только в паре процедур — после изменения все было отлично!
Собственно да, если вставка делается только из хранимых процедур, что является хорошим тоном, то достаточно изменения процедур, триггеры не нужны.
ну если только в каком-то стареньком проекте. сейчас уже вроде все дошли до того что делают ключи как sequential uniqueidentifier
и тут даж статья была с бенчмарками по которым было видно, что производительность от этого как минимум не страдает.
хм… именно эту статьи и имел ввиду. Видимо запамятовал. Но и там рассмотрено вроде только время вставки. Еще где-то тут же было про время чтения (статья про индексы была) и там не страдало. к сожалению на скорую руку найти не получилось. Но так или иначе Вы правы — вставка значительно дольше с гуидами, так что дальше можно не копать =)
сравнение 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(,)

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

Articles