Лет так 6 назад, когда слоник был только в 8.0, а я плотно сидел на MySql, часто слышал призывы сменить DB. Я помню как это было болезненно начать. Но после того, как решился, ни разу не жалел и на мускул уже вряд ли вернусь. Уж очень много тут плюсов, но пост не об этом.
Пришла задача: написать магазин, большой в перспективе. А-ля Фотос, Хотлайн. Ну и стандартная задача для таких площадок — это фильтр.
Как это делаются в обычных «движках»? Верно: параметры = фильтры. Ну все понятно, что это просто, но не всегда красиво, особенно когда нужны фильтры с диапазонами (например: диагональ 10"-11"). Да и тогда приходится думать о том, что фильтры — это независимая от параметров сущность. В моей версии фильтры «вяжутся» к параметрам. Не знаю, как это сделано на том же хотлайне (есть подозрение, что там фильтры вяжутся непосредственно на товары), но такой вариант требует много человеческого внимания. Не буду вдаваться в детали архитектуры, это тема другого поста. Но рассматривать будем именно упрощенный вариант, дабы не потеряться.
Проблема в таких фильтрах — это их построение. Просчитать количество товаров при выбранных (или не выбранных ещё) фильтрах.
Итак…
Создаем таблицу для товаров:
CREATE TABLE public.products ( id SERIAL, title VARCHAR NOT NULL, CONSTRAINT products_pkey PRIMARY KEY(id) ); Таблицу фильтров:
CREATE TABLE public.filters ( id SERIAL, title VARCHAR NOT NULL, CONSTRAINT filters_pkey PRIMARY KEY(id) ); Таблицу для связей между filters и products:
CREATE TABLE public.products_ref_filters ( id_product INTEGER NOT NULL, id_filter INTEGER NOT NULL, CONSTRAINT products_ref_filters_pkey PRIMARY KEY(id_product, id_filter), CONSTRAINT products_ref_filters_fk FOREIGN KEY (id_product) REFERENCES public.products(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT products_ref_filters_fk1 FOREIGN KEY (id_filter) REFERENCES public.filters(id) ON DELETE CASCADE ON UPDATE CASCADE ); И получается стандартный вариант. Тривиально.
Дальше…
В таблицу товаров добавляем поле filters:
ALTER TABLE public.products ADD COLUMN filters INTEGER[] DEFAULT ARRAY[]::integer[] NOT NULL; Как вы заметили, это массив чисел, тут будут дублироваться айдишки фильтров, да, это денормализация. Для целостности пишем процедуры и вешаем на триггера. Вставка:
CREATE OR REPLACE FUNCTION public.products_ref_filters__insert_tr () RETURNS trigger AS' BEGIN UPDATE products SET filters = filters + NEW.id_filter --push element onto array WHERE id = NEW.id_product; RETURN NEW; END; 'LANGUAGE 'plpgsql'; CREATE TRIGGER products_ref_filters__insert_tr AFTER INSERT ON public.products_ref_filters FOR EACH ROW EXECUTE PROCEDURE public.products_ref_filters__insert_tr(); Удаление:
CREATE OR REPLACE FUNCTION public.products_ref_filters__delete_tr () RETURNS trigger AS' BEGIN UPDATE products SET filters = filters - OLD.id_filter --remove entries matching right argument from array WHERE id = OLD.id_product; RETURN OLD; END; 'LANGUAGE 'plpgsql'; Обновление:
CREATE OR REPLACE FUNCTION public.products_ref_filters__update_tr () RETURNS trigger AS' BEGIN UPDATE products SET filters = filters - OLD.id_filter WHERE id = OLD.id_product; UPDATE products SET filters = filters + NEW.id_filter WHERE id = NEW.id_product; RETURN NEW; END; 'LANGUAGE 'plpgsql'; CREATE TRIGGER products_ref_filters__update_tr AFTER UPDATE ON public.products_ref_filters FOR EACH ROW EXECUTE PROCEDURE public.products_ref_filters__update_tr(); Очистка:
CREATE OR REPLACE FUNCTION public.products_ref_filters__truncate_tr () RETURNS trigger AS' BEGIN UPDATE products SET filters = ARRAY[]::INTEGER[]; RETURN NULL; END; 'LANGUAGE 'plpgsql'; CREATE TRIGGER products_ref_filters__truncate_tr AFTER TRUNCATE ON public.products_ref_filters FOR EACH STATEMENT EXECUTE PROCEDURE public.products_ref_filters__truncate_tr(); Теперь, при вставке, обновлении, удалении, данных из таблицы связей будет записываться массив фильтров в таблицу товаров. И мы избавились от JOIN. Теперь не нужно клеить таблицы в запросе. Это дает много плюсов, легче строить запросы, они быстрее, меньше памяти и т.д. Но статья ведь о intarray? Да.
Устанавливаем расширение:
CREATE EXTENSION intarray; После исполнения этой команды в базе появятся функции, индексы, операторы для работы с массивами типа INTEGER. Теперь работать будет намного быстрее и удобнее.
Наполняем нашу БД. Фильтров 10 000. Товаров 100 000. Каждому товару по 10 фильтров. Итого: промежуточная таблица 1 000 000 строк. Этот блок запросов у меня исполнялся 8 мин. Так что дождитесь.
INSERT INTO filters (title) SELECT 'filter_' || num FROM generate_series(1, 10000) as num; INSERT INTO products (title) SELECT 'product_' || num FROM generate_series(1, 100000) as num; DO $$ DECLARE idp INTEGER; BEGIN FOR idp IN SELECT id FROM products LOOP INSERT INTO products_ref_filters SELECT idp, f.id FROM filters f ORDER BY random() LIMIT 10; END LOOP; END$$; Создаем индекс на массив чисел:
CREATE INDEX products_idx ON public.products USING gin (filters public.gin__int_ops); Итого, наша БД заполнена. Давайте посмотрим, что теперь с этим делать. Давайте найдем самые популярные фильтры и запомним их.
SELECT id_filter FROM products_ref_filters GROUP BY id_filter ORDER BY count(*) DESC LIMIT 10 Мой результат такой: 7267, 4889, 6364, 5376, 3556, 7292, 11188, 2643, 9005, 10235.
Найдем товары с определенным фильтром, пусть будет 7267:
-- обычный JOIN SELECT t1.* FROM products t1, products_ref_filters t2 WHERE t1.id = t2.id_product AND t2.id_filter = 7267 -- 140 rows returned (execution time: 125 ms; total time: 141 ms) -- SUB SELECT SELECT * FROM products WHERE id IN ( SELECT id_product FROM products_ref_filters WHERE id_filter = 7267 ) -- 140 rows returned (execution time: 125 ms; total time: 141 ms) -- поиск по массиву SELECT * FROM products WHERE filters @> ARRAY[7267] -- 140 rows returned (execution time: 0 ms; total time: 0 ms) Один из фильтров
-- JOIN SELECT DISTINCT t1.* FROM products t1, products_ref_filters t2 WHERE t1.id = t2.id_product AND t2.id_filter IN (7267,4889,6364,5376,3556,7292,11188,2643,9005,10235) -- 1347 rows returned (execution time: 297 ms; total time: 297 ms) -- SUB SELECT SELECT * FROM products WHERE id IN ( SELECT id_product FROM products_ref_filters WHERE id_filter IN (7267,4889,6364,5376,3556,7292,11188,2643,9005,10235) ) -- 1347 rows returned (execution time: 234 ms; total time: 250 ms) -- INTARRAY SELECT * FROM products WHERE filters && ARRAY[7267,4889,6364,5376,3556,7292,11188,2643,9005,10235] -- 1347 rows returned (execution time: 16 ms; total time: 16 ms) Лень составлять другие запросы, уж простите. Но когда нужно найти товары с несколькими фильтрами, тут вообще этот подход оставляет джойны и сабселекты далеко позади.
-- JOIN -- ЛЕНЬ, но тут отрыв ещё больше будет ;) -- Товары содержащие оба фильтра SELECT * FROM products WHERE filters @> ARRAY[9844,9957]; -- 1 rows returned (execution time: 0 ms; total time: 16 ms) Оф. дока тут.
Обратите внимание на операторы, это просто сказка! Где-то читал, что даже есть патч, установив который, можно внешние ключи строить на массив, и база сама будет следить за целостностью. Еще где-то читал, что разработчики даже планируют это сделать без всяких патчей. Да, здорово. В свое время, когда я нашел (для себя) это решение, радости было…
ссылка на оригинал статьи http://habrahabr.ru/post/269823/
Добавить комментарий