Лекция
Привет, Вы узнаете о том , что такое концепция eav, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое концепция eav, entity-attribute-value , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.
Соблазн использовать модель EAV ( entity-attribute-value ) при организации структуры БД весьма велик, особенно когда предметная область заранее плохо известна (или разработчик просто не хочет в нее углубляться). Это ведь так удобно - создать "универсальный" способ описания характеристик объектов, который больше не потребует доработок базы ни при появлении новых типов объектов, ни при возникновении новых атрибутов...
Однако, за любую универсальность приходится платить сложностью и производительностью запросов - так что json[b] может оказаться более эффективной заменой. Но если уж такая модификация невозможна - давайте попробуем выжать максимум производительности из доставшегося нам legacy на самом простом примере.
Ограничимся работой с единственной таблицей значений:
CREATE TABLE tst_eav AS
SELECT
(random() * 1e4)::integer e -- 10k объектов
, (random() * 1e2)::integer a -- 100 характеристик
, (random() * 1e2)::integer v -- 100 вариантов значений
FROM
generate_series(1, 1e6); -- 1M записей о значениях
Попробуем найти такие объекты e, для которых одновременно существуют записи с (a, v) = (1, 1) и (a, v) = (2, 2) - это типичный вариант множественного фильтра в любом интернет-магазине: "смартфоны с экраном 6" и памятью 64GB".
Самым первым вариантом решения, пришедшим в голову разработчика уровня "я уже освоил SQL!", наверняка, будет соединение:
SELECT
e
FROM
tst_eav r1
JOIN
tst_eav r2
USING(e)
WHERE
(r1.a, r1.v) = (1, 1) AND
(r2.a, r2.v) = (2, 2);
Очевидно, для этого нам понадобится, как минимум, индекс по (a, v):
CREATE INDEX eav_idx1 ON tst_eav(a, v);
Посмотрим, что у нас получится в плане:
JOIN'им два набора строк
Сначала отбор по одной паре значений и сортировка по e, потом - по второй паре и сортировка, а потом уже - слияние двух отсортированных наборов.
Этот вариант станет для нас отправной точкой: 432мкс + 207 buffers.
Неплохо для отбора из миллиона записей, но можно лучше!
Ведь в предыдущем запросе мы искали вовсе не соединение, а пересечение множеств - так давайте его и попробуем использовать:
SELECT
e
FROM
tst_eav
WHERE
(a, v) = (1, 1)
INTERSECT
SELECT
e
FROM
tst_eav
WHERE
(a, v) = (2, 2);
INTERSECT'им наборы
А в плане теперь все получше - читаем ровно столько же, зато не пришлось тратить время на две сортировки: 301мск + 207 buffers.
А можно как-то читать меньше данных? В принципе, да - ведь у нас дважды происходит чтение страниц данных по маске - сначала для одного условия, потом для другого. Об этом говорит сайт https://intellect.icu . А избежать этого можно с помощью группировки, читая все за один раз:
SELECT
e
FROM
tst_eav
WHERE
(a, v) IN ((1, 1), (2, 2))
GROUP BY
e
HAVING
count(*) = 2; -- присутствуют оба условия
GROUP BY сразу пары условий
Сэкономили "копеечку": 296мкс + 202 buffers.
Конечно, тут мы пошли на допущение, что каждая пара (a, v) внутри одного объекта заведомо уникальна. Потому что если это не так, запрос станет существенно сложнее:
SELECT
e
FROM
tst_eav T
WHERE
(a, v) IN ((1, 1), (2, 2))
GROUP BY
e
HAVING
array_length(array_agg(DISTINCT T), 1) = 2; -- оба уникальных условия
Но всегда терзает мысль - может быть, можно сделать запрос еще быстрее?.. Оказывается, в нашем случае - можно!
Заметим, что львиная доля времени уходит на Bitmap Heap Scan - то есть вычитку страниц таблицы ради получения значения e, ведь его нет в нашем индексе, иначе мы могли бы обойтись Index Only Scan.
Но ведь еще с PostgreSQL 11 есть способ добавить неключевые поля в индекс:
CREATE INDEX eav_idx2 ON tst_eav(a, v) INCLUDE(e);
INCLUDE избавляет от Bitmap Heap Scan
И вот теперь наш план для INTERSECT-варианта: 121мкс + 9 buffers.
А ведь чем меньше страниц данных (buffers) читается, тем меньше шансов сходить за ними на диск и потерять в скорости.
Исследование, описанное в статье про концепция eav, подчеркивает ее значимость в современном мире. Надеюсь, что теперь ты понял что такое концепция eav, entity-attribute-value и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Из статьи мы узнали кратко, но содержательно про концепция eav
Комментарии