Продолжаю изучать английский язык и 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.
Лоренц Альбе — старший консультант и инженер службы поддержки CYBERTEC. Он работает с PostgreSQL и вносит свой вклад в развитие PostgreSQL с 2006 года.
—
Еще раз ссылка на оригинал.
Примечание переводчика: в студенческие годы я «изобрел» супер-пупер-универсальный способ хранения информации в БД — с помощью хранения сущностей, их атрибутов и их значений в трех разных таблицах. Но, почему-то, не стал никому сообщать о своём открытии…
Leave a Reply