Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Одним из главных требований к каталогу является возможность быстро искать и находить его элементы по различным критериям.
Существует множество подходов к реализации таких требований. Это и nosql решения и механизмы работы с json в реляционных СУБД. До nosql эпохи, решать такие задачи приходилось средствами реляционных БД.
Основная причина по которой реляционные СУБД плохо подходят для решения таких задач это разнообразие характеристик товаров. Набор характеристик к примеру для одежды и смартфонов будет совершенно разный. В самом деле не создавать же для каждой категории товаров отдельную таблицу с со своим набором реквизитов.
По этой причине в большинстве случаев в реляционных БД используется EAV (Entity Attribute Value) модель данных в тех или иных вариациях.
Недостатков у такой модели множество. Чаще всего EAV модель критикуют чрезмерную сложность, так же за то, что по сути схема данных храниться в самих данных.
Есть мнение, что EAV вообще является анти паттерном, что тоже не лишено оснований, однако надо заметить, что есть и другое мнение, что лучше такая схема, чем вообще отсутствие таковой.
Рискуя навлечь на себя гнев сообщества хочу представить свой вариант реализации каталога. Это не совсем EAV, скорее его по мотивам.
Я использовал его в различных проектах много лет, его достоинства чрезвычайная простота, действительная универсальность и хорошая скорость выборки, что на первый взгляд не кажется очевидным.
Всё описанное далее предполагает использование СУБД Postgresql.
Довольно преамбул, вот упрощённая ER-диаграмма.
Из диаграммы видно, что товар в таблице products ссылается на категорию из таблицы product_categories.
Таблица product_categories может иметь иерархическую структуру.
На таблицу products ссылаются три таблицы:
Цены товаров (product_prices)
Фото товаров (products_images)
Значения свойств характеристик товаров (property_values).
Все значения характеристик всех товаров хранятся целочисленном поле таблицы property_values.
Целочисленное поле в данном случае позволяет хранить разумеется численные, а кроме того булевы и ссылочные значения характеристик.
Текстовое представление ссылочных значений хранятся в таблице property_values_references.
Эта таблица не принимает участие в основных выбирающих запросах и нужна только для формирования в клиентском приложении выпадающих списков возможных вариантов отбора.
Она ссылается на таблицу свойств категорий (product_category_properties) полем property_id.
Таблица свойств категорий (product_category_properties) полем parent_id ссылается на саму себя, что позволяет реализовать в клиентском приложении каскадные фильтры неограниченной вложенности.
Скрипт создания таблиц и индексов каталога
BEGIN;
CREATE TABLE IF NOT EXISTS product_categories
(
id serial NOT NULL,
parent_id integer REFERENCES product_categories (id),
title character varying(64),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS products
(
id serial NOT NULL,
category_id integer NOT NULL REFERENCES product_categories (id),
title character varying(128) NOT NULL,
description text NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS id_and_category_id
ON products USING btree
(id ASC NULLS LAST, category_id ASC NULLS LAST);
DO $$ /* вот так, поскольку CREATE TYPE не поддерживает IF NOT EXISTS параметр */
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'property_value_type') THEN
CREATE TYPE property_value_type AS enum ('boolean', 'integer', 'reference');
END IF;
END
$$;
CREATE TABLE IF NOT EXISTS product_category_properties
(
id serial NOT NULL,
parent_id integer REFERENCES product_category_properties (id),
category_id integer NOT NULL REFERENCES product_categories (id),
title character varying(64) NOT NULL,
property_type property_value_type NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS property_values
(
property_id integer NOT NULL REFERENCES product_category_properties (id),
product_id integer NOT NULL REFERENCES products (id),
value smallint NOT NULL,
PRIMARY KEY (property_id, product_id)
);
CREATE INDEX IF NOT EXISTS property_id_and_value_and_product_id
ON property_values USING btree
(property_id ASC NULLS LAST, value ASC NULLS LAST, product_id ASC NULLS LAST);
CREATE TABLE IF NOT EXISTS product_prices
(
product_id integer NOT NULL REFERENCES products (id),
period timestamp NOT NULL,
price integer NOT NULL,
PRIMARY KEY (product_id, period)
);
CREATE INDEX IF NOT EXISTS product_id_and_price_and_period
ON product_prices USING btree
(product_id ASC NULLS LAST, price ASC NULLS LAST, period ASC NULLS LAST);
CREATE TABLE IF NOT EXISTS product_images
(
id serial NOT NULL,
product_id integer NOT NULL REFERENCES products (id),
title character varying(64) NOT NULL,
filename character varying(64) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS property_value_references
(
id integer NOT NULL,
property_id integer NOT NULL REFERENCES product_category_properties (id),
value character varying(128) NOT NULL,
PRIMARY KEY (id, property_id)
);
END;У предложенной схемы есть положительный side-эффект, ничто не мешает нам наследовать свойства вышестоящих категорий.
К примеру, у корневой или любой категории в иерархии есть некоторые свойства.
Следующий запрос вернёт все свойства указанной категории плюс свойства всех его родительских категорий.
Будет полезен при формировании формы фильтра на клиенте.
WITH RECURSIVE r AS (
SELECT * FROM product_categories
WHERE id = ?
UNION ALL
SELECT product_categories.*
FROM product_categories JOIN r ON r.parent_id = product_categories.id
)
SELECT pcp.id, pcp.parent_id, pcp.category_id, pcp.property_type, pcp.title
FROM product_category_properties pcp
WHERE pcp.category_id IN (SELECT id FROM r)Следующий запрос вернёт все свойства, как заполненные значениями так и пустые.
Будет полезен при динамическом формировании формы создания, редактирования и просмотра карточки товара.
WITH RECURSIVE r AS (
SELECT * FROM product_categories
WHERE id = (
SELECT category_id FROM products
WHERE id = ?
)
UNION ALL
SELECT product_categories.*
FROM product_categories JOIN r ON r.parent_id = product_categories.id
)
SELECT pcp.id, pcp.parent_id, pcp.category_id, pcp.value_type, pcp.title, pv.value
FROM product_category_properties pcp
LEFT JOIN property_values pv ON pcp.id = pv.property_id AND product_id = ?
WHERE pcp.category_id IN (SELECT id FROM r)Следующий запрос вернёт наименование, описание и последнюю цену товара.
SELECT id, title, description, price
FROM products p
LEFT JOIN product_prices pp ON p.id = pp.product_id AND pp.period = (
SELECT MAX(period)
FROM product_prices
WHERE pp.product_id = product_prices.product_id
)
WHERE p.id = ?Вариантов основного для каталога выбирающего запроса, множество, но все они - разновидности двух подходов: правильного, и не правильного :).
Вот так, к примеру может выглядеть основной выбирающий запрос по семи различным характеристикам в заданной категории и диапазоне цен.
SELECT id
FROM products
WHERE id IN (
SELECT product_id
FROM product_prices pp
WHERE period = (
SELECT max(period)
FROM product_prices
WHERE pp.product_id = product_prices.product_id
) AND ("price" >= ? AND "price" <= ?) AND product_id IN (
SELECT product_id
FROM property_values
WHERE (property_id = ? AND value = ?)
OR (property_id = ? AND value = ?)
OR (property_id = ? AND value = ?)
OR (property_id = ? AND value = ?)
OR (property_id = ? AND value = ?)
OR (property_id = ? AND value = ?)
OR (property_id = ? AND value = ?)
GROUP BY product_id HAVING COUNT(*) = ? /* здесь количество условий, в данном случае должно быть 7 */
)
) AND category_id = ?На первый взгляд выглядит элегантным, однако, такой запрос не может полноценно использовать индекс и выполняется не самым быстрым образом. Следующий вариант гораздо быстрее.
SELECT id
FROM products p
JOIN property_values pv ON (p.id = pv.product_id AND pv.property_id = 1 AND pv.value >= ?)
JOIN property_values pv2 ON (pv.product_id = pv2.product_id AND pv2.property_id = ? AND pv2.value = ?)
JOIN property_values pv3 ON (pv.product_id = pv3.product_id AND pv3.property_id = ? AND pv3.value = ?)
JOIN property_values pv4 ON (pv.product_id = pv4.product_id AND pv4.property_id = ? AND pv4.value = ?)
JOIN property_values pv5 ON (pv.product_id = pv5.product_id AND pv5.property_id = ? AND pv5.value = ?)
JOIN property_values pv6 ON (pv.product_id = pv6.product_id AND pv6.property_id = ? AND pv6.value = ?)
JOIN property_values pv7 ON (pv.product_id = pv7.product_id AND pv7.property_id = ? AND pv7.value = ?)
JOIN product_prices pp ON p.id = pp.product_id AND pp.period = (
SELECT MAX(period)
FROM product_prices
WHERE pp.product_id = product_prices.product_id AND "price" >= ? AND "price" <= ?
)
WHERE p.category_id = ?Но самым быстрым оказался следующий вариант запроса.
SELECT id
FROM products p
JOIN (
SELECT pv.product_id
FROM property_values pv
JOIN property_values pv2 ON pv.product_id = pv2.product_id AND pv2.property_id = ? AND pv2.value = ?
JOIN property_values pv3 ON pv.product_id = pv3.product_id AND pv3.property_id = ? AND pv3.value = ?
JOIN property_values pv4 ON pv.product_id = pv4.product_id AND pv4.property_id = ? AND pv4.value = ?
JOIN property_values pv5 ON pv.product_id = pv5.product_id AND pv5.property_id = ? AND pv5.value = ?
JOIN property_values pv6 ON pv.product_id = pv6.product_id AND pv6.property_id = ? AND pv6.value = ?
JOIN property_values pv7 ON pv.product_id = pv7.product_id AND pv7.property_id = ? AND pv7.value = ?
WHERE pv.property_id = ? AND pv.value >= ?
) AS pv ON p.id = pv.product_id
JOIN product_prices pp ON p.id = pp.product_id AND pp.period = (
SELECT MAX(period)
FROM product_prices
WHERE pp.product_id = product_prices.product_id AND "price" >= ? AND "price" <= ?
)
WHERE category_id = ?Очень просто, не правда ли?
В последней строке запросов проверяется вхождение выбранных товаров в целевую категорию, это условие здесь не обязательное, его стоит добавить в запросы в случае если ни каких других условий не задано, что бы избежать выборки всех значений.
Такой запрос легко генерируется алгоритмически используя параметры из url вида:
?filter[price][gte]=100&filter[price][lte]=1000&filter[property][1][eq]=1&filter[property][2][gte]=1&filter[property][3][lte]=1 и т.д. и позволяет накладывать любые фильтры ( >, <, >=, <=, =, <>, in, not in) в любых комбинациях.
Используя индексы созданные по полям property_id, value, product_id таблицы property_values и product_id, price, period таблицы product_prices получилось не только избавиться от полного сканирования таблиц, а добиться "index scan only" на всех этапах составления плана запроса оптимизатором.
Правда тут есть нюанс, при большом количестве условий выборки, возрастает время оптимизации плана запроса планировщиком и уже после 6 условий, время планирования превышает время исполнения запроса.
Поэтому, чтобы указать планировщику, что бы он не пытался искать оптимальный порядок соединений можно уменьшить join_collapse_limit параметр конфигурации postgresql, по умолчанию он равен 8.
Тестирование
Стало любопытно, после какого количества товаров и значений их свойств, время исполнения запросов станет непозволительно большим и для этих целей набросал скрипт генерации данных.
Скрипт генерации тестовых данных
-- Сгенерировать 5 корневых категорий
TRUNCATE product_categories RESTART IDENTITY CASCADE;
INSERT INTO product_categories(id, parent_id, title)
VALUES (1, null, 'Product category 1'),
(2, null, 'Product category 2'),
(3, null, 'Product category 3'),
(4, null, 'Product category 4'),
(5, null, 'Product category 5');
-- 25 дочерних категорий
INSERT INTO product_categories(id, parent_id, title)
SELECT id+1, (id/5), 'Product category ' || id+1
FROM generate_series(5, 29) id;
-- 300 свойств категорий
TRUNCATE product_category_properties RESTART IDENTITY CASCADE;
INSERT INTO product_category_properties(id, title, parent_id, category_id, value_type)
SELECT id+1, 'Product category property ' || id+1, null, (SELECT * FROM generate_series(6, 30, 1) LIMIT 1 OFFSET (id/12)),
(array['boolean', 'boolean', 'boolean', 'boolean', 'boolean', 'integer', 'reference'])[ceil(random() * 7)]::property_value_type
FROM generate_series(0, 299) id;
-- 1 млн. товаров
TRUNCATE products RESTART IDENTITY CASCADE;
INSERT INTO products(id, title, description, category_id)
SELECT id+1 as id, 'Product title ' || id+1, 'Product title ' || id+1, (SELECT * FROM generate_series(6, 30, 1) LIMIT 1 OFFSET id/40000) as category_id
FROM generate_series(0, 999999) id;
-- 2 млн. цен товаров
TRUNCATE product_prices;
INSERT INTO product_prices(product_id, price, period)
SELECT (id/2)+1, floor(random()*(10000-1+1))+1, now() - (
interval '1 day' * round(random() * 100) + interval '1 hours' * round(random() * 100) + interval '1 minutes' * round(random() * 100) +
interval '1 seconds' * round(random() * 100) + interval '1 milliseconds' * round(random() * 100)) as timestamp
FROM generate_series(0, 1999999) id;
/* 12 млн. значений свойств товаров */
TRUNCATE property_values;
INSERT INTO property_values(product_id, property_id, value)
SELECT
(
SELECT id FROM products WHERE id =
(
SELECT CASE
WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
ELSE pid+1
END
)
) AS product_id,
(
SELECT id FROM product_category_properties WHERE category_id = (
SELECT category_id FROM products WHERE id = (
SELECT CASE
WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
ELSE pid+1
END
)
) ORDER BY id LIMIT 1 OFFSET pid/1000000
) AS property_id,
(
SELECT
CASE
WHEN (
SELECT value_type FROM product_category_properties WHERE category_id = (
SELECT category_id FROM products WHERE id = (
SELECT CASE
WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
ELSE pid+1
END
)
) ORDER BY id LIMIT 1 OFFSET pid/1000000
) = 'reference' THEN ceil(random()*10)
WHEN (
SELECT value_type FROM product_category_properties WHERE category_id = (
SELECT category_id FROM products WHERE id = (
SELECT CASE
WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
ELSE pid+1
END
)
) ORDER BY id LIMIT 1 OFFSET pid/1000000
) = 'integer' THEN ceil(random()*100)
ELSE 1
END
) AS value
FROM generate_series(0, 11999999) pid;
-- Таким образом все товары в одинаковых категориях имеют полный набор значений всех доступных свойств
-- Теперь удалить половину всех значений свойств, чтобы обеспечить правдоподобное распределение
DELETE FROM property_values WHERE ctid = ANY (
SELECT ctid FROM property_values TABLESAMPLE BERNOULLI(50)
);Выводы
На базе заполненной случайными данными в количестве: 1 млн. товаров в 25 категориях (+5 корневые), 300 свойств категорий, 6 млн. значений свойств категорий и весьма скромной машине Intel Pentium G6400 4GHz + 16Gb + HDD, такой запрос отрабатывает за 35 - 60 ms.
На самом деле, на практике мне не приходилось иметь дело с таким количеством товаров в каталоге. От нескольких сотен до полутора тысяч максимум, но длинная изоляция сподвигла на тест и статью.
Безусловно, недостатки в таком подходе тоже имеются. Самым очевидным кажется тщательная проработка категорий и их свойств, просто так переместить товары из категории в категорию не получится, это может вылиться в дополнительною головную боль при администрировании данного решения.
