Одним из главных требований к каталогу является возможность быстро искать и находить его элементы по различным критериям.
Существует множество подходов к реализации таких требований. Это и 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.
На самом деле, на практике мне не приходилось иметь дело с таким количеством товаров в каталоге. От нескольких сотен до полутора тысяч максимум, но длинная изоляция сподвигла на тест и статью.
Безусловно, недостатки в таком подходе тоже имеются. Самым очевидным кажется тщательная проработка категорий и их свойств, просто так переместить товары из категории в категорию не получится, это может вылиться в дополнительною головную боль при администрировании данного решения.
