Программы
Вредные привычки в SQL: не создавайте ID колонку в каждой таблице

Вредные привычки в SQL: не создавайте ID колонку в каждой таблице

Многие начинающие или просто ленивые программисты бездумно лепят поле ID в каждую таблицу: нужно это или нет. Вот небольшое объяснение: когда это полезно, а когда нет.

Перевод Bad Habits to Kick : Putting an IDENTITY column on every table.

Существует некая тенденция класть в каждую таблицу колонку идентификатора (суррогатного auto increment первичного ключа). Обычно это происходит потому что это самый простой способ определить каждую отдельную строку таблицы. Вот простой пример с таблицой связи многие-ко-многим, соединяющей продукты и теги на них:

CREATE TABLE dbo.Products
(
  ProductID int IDENTITY(1,1) PRIMARY KEY,
  ...
);

CREATE TABLE dbo.Tags
(
  TagID int IDENTITY(1,1) PRIMARY KEY,
  ...
);

CREATE TABLE dbo.ProductTags
(
  ProductTagID  int IDENTITY(1,1),
  ProductID     int NOT NULL FOREIGN KEY REFERENCES dbo.Products(ProductID),
  TagID         int NOT NULL FOREIGN KEY REFERENCES dbo.Tags(TagID),
  ...
);

А теперь скажите мне, в чём польза для бизнеса от колонки ProductTagID – того самого искусственного идентификатора? Когда вам нужно будет обратиться к записи по ProductTagID, при этом не имея ProductID и/или TagID? И почему именно эта колонка должна уникально идентифицировать строку в таблице? Если у вас есть эта колонка, значит вы хотите разрешить наличие многих строк с одними и теми же парами продукт-тег (что вряд ли).

Что произойдёт, если эта колонка исчезнет? Польза от этой колонки в лучшем случае сомнительна. По сути же, на каждую запись вы потратите дополнительно 8 байт. Плюс сам индекс требует места, ещё и нагрузка на пропускную способность между базой данных и приложением, да и перебалансировка индексного дерева не бесплатна.

Вот другой пример, с таблицей журнала изменений. К примеру, мы имеем таблицу, в которой храним информацию об ошибках / исключительных ситуациях, а может быть и просто вызовы API, или ещё что-то подобное. Данные постоянно добавляются в таблицу запись за записью в реальном времени. Выглядеть такая таблица будет как-то так:

CREATE TABLE dbo.ActivityLog
(
  LogID     int      IDENTITY(1,1) PRIMARY KEY,
  EventDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ...
);

Думаю, многие при проектировании таблицы лога захотят добавить в неё суррогатный ключ. Это происходит из-за того, что для неё тяжело подобрать естественный публичный ключ. Звучит разумно, но в части моих таблиц нет публичного ключа или уникального индекса. Но у них есть кластерный индекс (естественно), для этого не нужна колонка идентификатора.

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

Для подобных таблиц также есть интересная особенность: их нужно периодически чистить от старых записей, чтобы они не разрастались до огромных размеров. В целом, необходимо поддерживать только какое-то временное окно, например, в 30 дней. Так что раз в день нужно удалять записи старше 30 дней. Если бы привязали кластерный индекс к колонке идентификатора, базе данных пришлось бы:

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

Если кластерный индекс будет ориентироваться на поле datetime (время события), а поле идентификатора мы выбросим, база данных выполнит удаление проще и быстрее (не нужен будет предварительный поиск по ID).

Теперь давайте рассмотрим похожую таблицу логгирования, где, как мне кажется, колонка идентификатора полезна. В данном случае мы будем хранить события с некоторой продолжительностью / бекапом / активным изменением индекса (данных в полях):

CREATE TABLE dbo.ActivityLog
(
  LogID     int IDENTITY(1,1) UNIQUE NONCLUSTERED,
  EventType tinyint, -- возможно, какой-то внешний ключ на таблицу с типами событий
  StartDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  EndDate   datetime NULL,
  ...
);

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

DECLARE @LogID int;

INSERT dbo.ActivityLog(EventType) SELECT 1;

SELECT @LogID = SCOPE_IDENTITY();

-- Делаем работу

UPDATE dbo.ActivityLog
    SET EndDate = CURRENT_TIMESTAMP
    WHERE LogID = @LogID;

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

Если же вернуться к теме. Стоит отказаться от колонок идентификаторов? Естественно нет! Просто не нужно бездумно добавлять в каждую таблицу ещё одно поле просто так.

Также может быть вам интересно:

Вредные привычки в SQL: старомодные JOINы

Как лучше писать много-табличные запросы: просто перечисляя таблицы в SELECT через запятую, или же использовать JOIN?

Читать »

Кошка бросила котят – OpenSource всё виноват

Давненько не было крупных скандалов вокруг OpenSource. И вот Log4j дал повод пошуметь и высказаться экспертам всех мастей. Не буду отмалчиваться и я :-)

Читать »
Фото Python: Встроенные типы данных (list, set, dict, etc)

Python: Встроенные типы данных (list, set, dict, etc)

В Python есть множество встроенных типов данных. Их использование значительно упрощает жизнь и ускоряет разработку программных продуктов.

Фото Python: типы данных, переменные, логическое ветвление и циклы

Python: типы данных, переменные, логическое ветвление и циклы

Первая часть заметок о Python. О базовых типах, переменных, ветвлении и циклах.

Фото Как установить PostgreSQL на Linux и создать базу и пользователя

Как установить PostgreSQL на Linux и создать базу и пользователя

PostgreSQL - система управления базой данных общего назначения. Одна из самых распространённых баз данных, используемая на многих коммерческих и некоммерческих проектах.

Фото Как сделать свою middleware в Django (с примерами)

Как сделать свою middleware в Django (с примерами)

Middleware или "промежуточное программное обеспечение" - элегантный способ установить общие правила обработки запросов и ответов приложения. Давайте напишем парочку middleware, чтобы понять, как они работают.

Фото Добавляем постраничную пагинацию на Django сайт

Добавляем постраничную пагинацию на Django сайт

На сайтах часто встречаются многостраничные объекты: список товаров, список заметок и т.д. Поэтому важно уметь добавить навигацию по страницам на Django-проекте.

Фото Нет слов, одни... однострочники

Нет слов, одни... однострочники

На днях вышел пост со списком полезных однострочников для JavaScript программистов. Памятуя Perl-овую молодость, заглянул туда.

Фото Пример своей консольной команды в Django проекте

Пример своей консольной команды в Django проекте

Если вы работали с Django проектом, то, скорее всего, запускали команды из консоли (manage.py). В Django есть простой способ писать свои команды для управления проектом.

Фото Разграничение прав доступа на Django сайте

Разграничение прав доступа на Django сайте

Почти на любом веб-сайте необходимо разделять пользователей на группы и предоставлять им разные возможности. В Django есть довольно серьёзная система прав доступа для пользователей - давайте её рассмотрим!