Перевод: CYBERTEC. Дизайн Сущность-Атрибут-Значение (EVA) — не делайте этого!

PostgreSQLПродолжаю изучать английский язык и PostgreSQL. И снова у меня Cybertec, на этот раз статья про универсальный дизайн схемы баз данных, при котором сущность, её атрибуты и значения хранятся отдельно. Было дело, даже использовал такой способ хранения данных.

Ссылка на оригинал.

Клиенты часто спрашивают меня о том, что я думаю про дизайн «Сущность-Атрибут-Значение» (Entity-Attribute-Value, EAV). Я подумал, что было бы хорошо изложить свои мысли по этому поводу письменно.

Что такое дизайн Сущность-Атрибут-Значени?

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

CREATE TABLE objects (
   objectid bigint PRIMARY KEY
   /* другие свойства уровня объекта */
);
 
CREATE TABLE attstring (
   objectid bigint
      REFERENCES objects ON DELETE CASCADE NOT NULL,
   attname text NOT NULL,
   attval text,
   PRIMARY KEY (objectid, attname)
);
 
CREATE TABLE attint (
   objectid bigint
      REFERENCES objects ON DELETE CASCADE NOT NULL,
   attname text NOT NULL,
   attval integer,
   PRIMARY KEY (objectid, attname)
);
 
/* больше таблиц для других типов данных */

Название данной модели происходит от таблиц, начинающихся на «att…», которые имеют три колонки entity «ID», «attribute name» и «value».

Есть несколько вариаций данной модели, среди которых:

  • опустить таблицу объектов

  • добавлять дополнительные таблицы, которые будут определять «типы объектов», так что каждый тип может иметь только определенные атрибуты

Почему кто-то рассматривает Entity-Attribute-Value дизайн?

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

Я предполагаю, что еще одна причина почему люди рассматривают эту модель кроется в том, что они более знакомы с хранилищем ключ-значением, чем с реляционными базами данных.

Соображения, касающиеся производительности EAV-дизайна

По моему мнению, EAV-дизайн БД — самый худший дизайн с точки зрения производительности. Вам никогда не получить производительную базу данных используя такую модель.

Единственный вариант когда EAV работает хорошо — когда такая модель используется в качестве хранилища ключ-значение.

INSERT

Вставка объекта будет выглядеть так: 

INSERT INTO objects (objectid) VALUES (42);
 
INSERT INTO attstring (objectid, attname, attval)
VALUES (42, 'name', 'myobject');
 
INSERT INTO attint (objectid, attname, attval)
VALUES (42, 'start', 100),
       (42, 'end',   1000);

Это означает что мы вставляем четыре строки в три таблицы и четыре раза изменяем индекс. Кроме того, три оператора будут требовать три клиент-серверных обмена данными. Вы можете сэкономить на обмене, используя CTE, объединив три оператора в один. Или использовать новый конвейерный режим libpq. В любом случае, это будет дороже чем вставка одной строки.

DELETE

Если вы используете каскадное удаление, вы можете сделать это одним оператором: 

DELETE FROM objects WHERE objectid = 42;

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

UPDATE

Обновление одной колонки могло бы быть быстрее при EAV-дизайне, потому что только одна маленька табличная строка модифицируется: 

UPDATE attint
SET attval = 2000
WHERE objectid = 42 AND attname = 'end';

Однако, если вы должны модифицировать несколько колонок, вам нужно будет запукать несколько операторов обновления. Это будет медленнее, чем если бы вам нужно было обновить одну (хотя и большую) строку.

SELECT

Запрос всех атрибутов сущности требует соединение: 

SELECT ast.attval AS "name",
       ai1.attval AS "start",
       ai2.attval AS "end"
FROM objects AS o
   LEFT JOIN attstring AS ast USING (objectid)
   LEFT JOIN attint AS ai1 USING (objectid)
   LEFT JOIN attint AS ai2 USING (objectid)
WHERE objectid = 42
  AND ast.attname = 'name'
  AND ai1.attname = 'start'
  AND ai2.attname = 'end';

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

Одноколоночные агрегаты

В качестве примера запроса, который может быть быстрее с EAV-моделью, рассмотрим запрос, который объединяет данные из одного столбца: 

SELECT sum(attval) AS total
FROM othertab
   JOIN attint USING (objectid)
WHERE othertab.col = 'x'
  AND attint.attname = 'attendants';

С покрывающим индексом на attint(objectid, attname) INCLUDE (attval), это может быть немного быстрее, чем агрегирование столбца из более широкой таблицы.

Более сложные запросы

После этих примеров становится ясно, что написание более сложных запросов станет проблемой при использовании EAV-дизайна. Представьте себе простое соединение: 

SELECT e1a1.attval AS person_name,
       e1a2.attval AS person_id,
       e2a1.attval AS address_street,
       e2a2.attval AS address_city
FROM attint AS e1a2
   JOIN attstring AS e1a1
      ON e1a2.objectid = e1a1.objectid
   LEFT JOIN attint AS e2a0
      ON e1a2.attval = e2a0.attval
   LEFT JOIN attstring AS e2a1
      ON e2a0.objectid = e2a1.objectid
   LEFT JOIN attstring AS e2a2
      ON e2a0.objectid = e2a2.objectid
WHERE e1a1.attname = 'name'
  AND e1a2.attname = 'persnr'
  AND e2a0.attname = 'persnr'
  AND e2a1.attname = 'street'
  AND e2a2.attname = 'city';

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

SELECT person.name AS person_name,
       persnr AS person_id
       address.street,
       address.city
FROM person
   LEFT JOIN address
      USING (persnr);

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

Но нам нужен EAV-дизайн для гибкости!

Реляционные модели данных не славятся своей гибкостью. В конце концов, именно это — движущая сила NoSQL. Тем не менее, есть поводы использовать переменные объекты.

Создание таблиц на лету

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

Некоторые проблемы остаются:

  • Модель данных, которая растет «на лету», может оказаться не очень хорошо продуманной. Но в EAV-дизайне всё обстоит точно также.

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

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

Использование JSON для гибкой модели данных

PostgreSQL имеет обширную поддержку JSON, которую можно использовать для моделирования объектов с переменным количеством атрибутов.

Для этого вы моделируете важные и часто встречающиеся атрибуты как обычные столбцы таблицы. Затем вы добавляете JSONB-столбец c GIN-индексом на нём. Этот столбец содержит «редкие атрибуты» объекта в виде пар ключ-значение.

При использовании такой модели вы должны позаботиться о том, чтобы атрибуты

  • которые используются в объединениях

  • на которых вам нужно ограничение базы данных

  • которые вы хотите использовать в условии WHERE с оператором, отличным от «=»

были созданы как обычные столбцы таблицы.

Заключение

Избегайте дизайна Сущность-Атрибут-Значение в своих реляционных базах данных. EAV вызывает плохую работу и есть другие способы иметь гибкую модель данных в PostgreSQL.

Laurenz Albe

Лоренц Альбе (Laurenz Albe)

Лоренц Альбе — старший консультант и инженер службы поддержки CYBERTEC. Он работает с PostgreSQL и вносит свой вклад в развитие PostgreSQL с 2006 года.

Еще раз ссылка на оригинал.

Примечание переводчика: в студенческие годы я «изобрел» супер-пупер-универсальный способ хранения информации в БД — с помощью хранения сущностей, их атрибутов и их значений в трех разных таблицах. Но, почему-то, не стал никому сообщать о своём открытии…


Be the first to comment

Leave a Reply

Ваш Mail не будет опубликован.


*