[recovery mode] Идеальный каталог, оптимизация выборки данных04.12.2017 05:33

Введение

На очередном собеседовании меня спросили о недостатках модели данных EAV (Entity Attribute Value), я не нашёл что сказать, на мой взгляд это идеальный способ хранения произвольных данных. После короткого раздумья, я сказал что единственная проблема это невозможность построить индексы для выборок.

После собеседования я озадачился этим вопросом на несколько дней, пришёл к каким то выводам, для очистки совести чуть чуть погуглил. Нагуглил подтверждения своим мыслям, но этого мне было мало — захотелось реализации с подтверждением цифрами.

Если и вам интересно к каким выводам я пришёл и какой выигрыш от оптимизации можно получить, то добро пожаловать под кат.

Оглавление по циклу статей «Идеальный каталог»

Идеальный каталог, набросок архитектуры
Идеальный каталог, вариант реализации
Идеальный каталог, оптимизация выборки данных

Краткие выводы

Для тех кто устал и много букв не осилит, вывод такой: для выборки данных надо использовать материализованные представления, для которых построены индексы по всем колонкам.
Выборка по материализованному представлению работает на 5–15% быстрее чем выборка из аналогичной таблицы с аналогичными индексами.
На этом краткие выводы заканчиваются и начинается последовательное изложение.

Методы работы с EAV моделью

Данные можно получать прямыми запросами, можно сделать представление для каждого вида Entity (рубрики каталога), можно сделать аналогичное материализованное представление. Как вариант можно сделать таблицу, но при каждом изменении Attribute, будет необходимо выполнять ALTER TABLE с DROP COLUMN и ADD COLUMN, а это именно то от чего уходят с помощью EAV, хотя если хранить данные в EAV, а таблицу использовать только для чтения, то нормальный вариант.
Нагуглить можно и другие способы оптимизации EAV, но я исследовал только эти.

Методика тестирования

Были написаны скрипты (PHP) для генерации содержимого «товарных позиций» и скрипты для генерации DDL-кода создания таблицы, создания представления и создания материализованного представления (PostrgeSql).
С этими скриптами были созданы соответствующие источники данных.
Теперь выбираем сущность (рубрику каталога), у которой будет много позиций и мало характеристик (атрибутов).

SELECT
  r.code,
  (SELECT COUNT(*)
   FROM rubric_property rp
   WHERE rp.rubric_id = r.id) property_count,
  (SELECT COUNT(*)
   FROM rubric_item ri
   WHERE ri.rubric_id = r.id) item_count,
  (SELECT COUNT(*)
   FROM rubric_property rp
   WHERE rp.rubric_id = r.id) + (
    SELECT COUNT(*)
    FROM rubric_item ri
    WHERE ri.rubric_id = r.id) summary
FROM
  rubric r
GROUP BY
  r.code,
  r.id
ORDER BY
  summary DESC,
  property_count DESC,
  item_count DESC;

Количество атрибутов на мой взгляд не имеет большого значения, но если кому то хочется оценить влияние количества атрибутов, то все скрипты лежат в репозитории.
Делаем два типа выборок, первый тип выборки возвращает одну строку, второй — несколько строк.
Конечно для полноценного тестирования, требуется больше типов выборок.

Для каждого типа выборки выполняем запросы к каждому источнику данных: делаем выборку из запроса, из представления, из таблицы, из материализованного представления и снова из запроса и так далее по кругу. Такая цикличность в теории снизит влияние кеширования запросов. Выполнять 1000 раз подряд один и тот же запрос мне кажется плохим способом тестирования.
Таким образом делаем пять замеров времени выполнения. Отбрасываем крайние измерения — самое больше время выполнения и самое маленькое время выполнения. Что осталось сводим в таблицу.

Тестирование проводилось на следующем наборе данных:

рубрик (entity, сущностей) — 323
характеристик товара (attribute, атрибутов) — 47 229
товарных позиций — 6 989
значений (value) — 1 102 279

Непосредственно в выбранной рубрике было 41 позиция и 22 характеристики.

Результаты тестирования

Запрос возвращает одну строку (много условий выборки)

WHERE
mv.tiger IN ('poor', 'white', 'orange', 'red')
AND mv.bowl BETWEEN 1000 AND 4000
AND mv.clock > 3000
AND mv.legs < 2000
AND mv.snake = 'crazy'

итерация	SELECT	VIEW	TABLE	MAT VIEW
1	61	53	22	20
2	49	50	20	15
3	50	49	20	14
среднее время ms	53	50	20	16

Запрос возвращает несколько строк (меньше условий выборки)

WHERE
mv.tiger IN ('poor', 'white', 'orange', 'red')
AND mv.bowl BETWEEN 1000 AND 4000

итерация	SELECT	VIEW	TABLE	MAT VIEW
1	92	70	31	19
2	63	56	19	14
3	54	54	22	18
среднее время ms	69	60	24	17

Для прямых запросов это ещё хорошие результаты, за время работы над тестированием я несколько раз менял набор данных и время выборки могло превышать одну секунду (1000 ms), при том что выборка из материализованного представления занимала не более 30 ms.
Как можно видеть, даже если хранить данные в таблице, то выборка из материализованного представления происходит быстрее.

Возможные оптимизации

Материализованное представление

Data Definition Language

 -- CREATE MATERIALIZED VIEW :

CREATE MATERIALIZED VIEW tea_mv AS
SELECT
    ri.item_id item_id,

(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'snake'
) "snake"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'tiger'
) "tiger"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'beans'
) "beans"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'sweater'
) "sweater"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'pudding'
) "pudding"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'bumper'
) "bumper"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'train'
) "train"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'alligator'
) "alligator"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'trousers'
) "trousers"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'bowl'
) "bowl"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'cabbage'
) "cabbage"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'ship'
) "ship"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'undershirt'
) "undershirt"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'simmer'
) "simmer"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'squirrel'
) "squirrel"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'ceiling'
) "ceiling"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'legs'
) "legs"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'jacket'
) "jacket"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'book'
) "book"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'stomach'
) "stomach"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'clock'
) "clock"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'furniture'
) "furniture"

  FROM
    rubric_item ri
  WHERE
    ri.rubric_id = ( SELECT id FROM rubric WHERE code = 'tea' )
;
/* [2017-12-03 14:27:34] completed in 163ms */
 -- CREATE INDEX FOR MATERIALIZED VIEW :

CREATE INDEX tea_mv_snake ON tea_mv ("snake");
CREATE INDEX tea_mv_tiger ON tea_mv ("tiger");
CREATE INDEX tea_mv_beans ON tea_mv ("beans");
CREATE INDEX tea_mv_sweater ON tea_mv ("sweater");
CREATE INDEX tea_mv_pudding ON tea_mv ("pudding");
CREATE INDEX tea_mv_bumper ON tea_mv ("bumper");
CREATE INDEX tea_mv_train ON tea_mv ("train");
CREATE INDEX tea_mv_alligator ON tea_mv ("alligator");
CREATE INDEX tea_mv_trousers ON tea_mv ("trousers");
CREATE INDEX tea_mv_bowl ON tea_mv ("bowl");
CREATE INDEX tea_mv_cabbage ON tea_mv ("cabbage");
CREATE INDEX tea_mv_ship ON tea_mv ("ship");
CREATE INDEX tea_mv_undershirt ON tea_mv ("undershirt");
CREATE INDEX tea_mv_simmer ON tea_mv ("simmer");
CREATE INDEX tea_mv_squirrel ON tea_mv ("squirrel");
CREATE INDEX tea_mv_ceiling ON tea_mv ("ceiling");
CREATE INDEX tea_mv_legs ON tea_mv ("legs");
CREATE INDEX tea_mv_jacket ON tea_mv ("jacket");
CREATE INDEX tea_mv_book ON tea_mv ("book");
CREATE INDEX tea_mv_stomach ON tea_mv ("stomach");
CREATE INDEX tea_mv_clock ON tea_mv ("clock");
CREATE INDEX tea_mv_furniture ON tea_mv ("furniture");

Материализованное представление работает в разы быстрее прямых запросов.
При изменении данных необходимо обновлять представление (REFRESH MATERIALIZED VIEW).
При добавлении атрибута (характеристики) необходимо пересоздавать представление (CREATE MATERIALIZED VIEW).

Эти операции занимают считанные секунды. Эти действия можно автоматизировать и выполнять без привлечения квалифицированного персонала.

Но если у нас миллион пользователей и они постоянно набивают новые позиции каталога (рубрики), а ещё и администрация несколько раз в час меняет список характеристик, то это может быть проблемой.

Таблица

Data Definition Language

-- CREATE TABLE :

CREATE TABLE tea_t
(
  id serial not null constraint tea_pkey primary key,
  is_hidden integer default 0,
  insert_date timestamp with time zone default now(),
  item_id integer REFERENCES item (id),

"snake" TEXT,
"tiger" TEXT,
"beans" TEXT,
"sweater" TEXT,
"pudding" TEXT,
"bumper" TEXT,
"train" TEXT,
"alligator" TEXT,
"trousers" TEXT,
"bowl" DOUBLE PRECISION,
"cabbage" DOUBLE PRECISION,
"ship" DOUBLE PRECISION,
"undershirt" DOUBLE PRECISION,
"simmer" DOUBLE PRECISION,
"squirrel" DOUBLE PRECISION,
"ceiling" DOUBLE PRECISION,
"legs" DOUBLE PRECISION,
"jacket" DOUBLE PRECISION,
"book" DOUBLE PRECISION,
"stomach" DOUBLE PRECISION,
"clock" DOUBLE PRECISION,
"furniture" DOUBLE PRECISION
)
;
/* [2017-12-03 14:28:24] completed in 82ms */
 -- CREATE INDEX FOR TABLE :

CREATE INDEX tea_t_snake ON tea_t ("snake");
CREATE INDEX tea_t_tiger ON tea_t ("tiger");
CREATE INDEX tea_t_beans ON tea_t ("beans");
CREATE INDEX tea_t_sweater ON tea_t ("sweater");
CREATE INDEX tea_t_pudding ON tea_t ("pudding");
CREATE INDEX tea_t_bumper ON tea_t ("bumper");
CREATE INDEX tea_t_train ON tea_t ("train");
CREATE INDEX tea_t_alligator ON tea_t ("alligator");
CREATE INDEX tea_t_trousers ON tea_t ("trousers");
CREATE INDEX tea_t_bowl ON tea_t ("bowl");
CREATE INDEX tea_t_cabbage ON tea_t ("cabbage");
CREATE INDEX tea_t_ship ON tea_t ("ship");
CREATE INDEX tea_t_undershirt ON tea_t ("undershirt");
CREATE INDEX tea_t_simmer ON tea_t ("simmer");
CREATE INDEX tea_t_squirrel ON tea_t ("squirrel");
CREATE INDEX tea_t_ceiling ON tea_t ("ceiling");
CREATE INDEX tea_t_legs ON tea_t ("legs");
CREATE INDEX tea_t_jacket ON tea_t ("jacket");
CREATE INDEX tea_t_book ON tea_t ("book");
CREATE INDEX tea_t_stomach ON tea_t ("stomach");
CREATE INDEX tea_t_clock ON tea_t ("clock");
CREATE INDEX tea_t_furniture ON tea_t ("furniture");

-- FILL TABLE WITH DATA :

INSERT INTO tea_t
(item_id,snake,tiger,beans,sweater,pudding,bumper,train,alligator,trousers,bowl,cabbage,ship,undershirt,simmer,squirrel,ceiling,legs,jacket,book,stomach,clock,furniture)

SELECT
    ri.item_id item_id,

(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'snake'
) "snake"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'tiger'
) "tiger"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'beans'
) "beans"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'sweater'
) "sweater"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'pudding'
) "pudding"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'bumper'
) "bumper"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'train'
) "train"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'alligator'
) "alligator"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'trousers'
) "trousers"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'bowl'
) "bowl"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'cabbage'
) "cabbage"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'ship'
) "ship"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'undershirt'
) "undershirt"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'simmer'
) "simmer"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'squirrel'
) "squirrel"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'ceiling'
) "ceiling"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'legs'
) "legs"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'jacket'
) "jacket"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'book'
) "book"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'stomach'
) "stomach"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'clock'
) "clock"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'furniture'
) "furniture"

  FROM
    rubric_item ri
  WHERE
    ri.rubric_id = (SELECT id FROM rubric WHERE code = 'tea')

;
/*[2017-12-03 14:30:08] 41 rows affected in 105ms*/

Запросы к таблице как и запросы к материализованному представлению работают в разы быстрей прямых запросов, при этом запрос к таблице отработает чуть медленней запроса к материализованному представлению.

У таблиц, при изменении списка атрибутов, проблемы такие же как у материализованного представления, но при изменении значения характеристики, не требуется обновлять все записи таблицы, достаточно обновить только изменённые строки и колонки.

Если для представления невозможно создать внешний ключ, то для таблицы можно и нужно, это позволит связать позицию каталога и запись в нашей таблице характеристик, тем самым повысится консистентность данных. Использование таблицы позволяет пользоваться всеми возможностями таблиц (тригеры, логирование, резервное копирование).

Представление

Data Definition Language

CREATE VIEW tea_v AS
SELECT
    ri.item_id item_id,

(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'snake'
) "snake"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'tiger'
) "tiger"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'beans'
) "beans"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'sweater'
) "sweater"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'pudding'
) "pudding"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'bumper'
) "bumper"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'train'
) "train"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'alligator'
) "alligator"
    ,
(
  SELECT sm.string
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN string_matter sm
      ON sm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'trousers'
) "trousers"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'bowl'
) "bowl"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'cabbage'
) "cabbage"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'ship'
) "ship"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'undershirt'
) "undershirt"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'simmer'
) "simmer"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'squirrel'
) "squirrel"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'ceiling'
) "ceiling"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'legs'
) "legs"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'jacket'
) "jacket"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'book'
) "book"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'stomach'
) "stomach"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'clock'
) "clock"
    ,
(
  SELECT dm.digital
  FROM
    item_content ic
    JOIN content c
      ON ic.content_id = c.id
    JOIN property p
      ON c.property_id = p.id
    JOIN digital_matter dm
      ON dm.content_id = c.id
  WHERE
    ic.item_id = ri.item_id
    AND p.code = 'furniture'
) "furniture"

  FROM
    rubric_item ri
  WHERE
    ri.rubric_id = ( SELECT id FROM rubric WHERE code = 'tea' )
;

Представление работает чуть быстрей чем прямой запрос, кроме того использование представления сокращает количество строк кода. Если запрос может легко раздуться на 1000 и более строк, то обращение к представлению это всего одна строка — меньше данных пересылать между сервером СУБД и сервером приложения, меньше работы парсеру СУБД.

Изменение данных сразу, без дополнительных действия, отражается в представлении.
При изменении списка характеристик (атрибутов), необходимо пересоздавать представление.

Использование EAV модели

Для обычного интернет магазина, который торгует и холодильниками и автозапчастями, использование EAV более чем оправдано, для каждой номенклатуры не будешь создавать новую таблицу или дополнительную колонку в таблице характеристик. Кроме того в каждой рубрике обычно не более сотни наименований и делать для каждой рубрики отдельное материализованное представление это хорошая идея, искать среди сотни другой строк это быстрей чем искать среди миллионов строк атрибутов.

Изменения в значениях характеристик происходит один раз в жизни — в момент добавления товарной позиции. Изменения в списке характеристик происходят с каждым витком моды — не чаще одного раза в полгода. Добавление товарных позиций — каждый завоз — не чаще раза в неделю.

То есть мы имеем данные которые чаще читают и почти ни когда не меняют.
Модель EAV отлично ложиться на такое использование. Оптимизация через материализованные представления даёт скорость чтения не ниже скорости чтения из обычных таблиц.

Бонус

В предыдущей статье я обещал поделиться классами для генерации запросов к EAV. У меня до сих пор не дошли руки их написать, но я могу поделиться репозиторием в котором всё это есть, единственное чего там нет — это документации.

Если кто то возьмётся разбираться с этим кладбищем благих намерений, то обращайтесь, помогу (мои контакты указаны в профиле, лучше писать в ВК).

Если вы хотите повторить эксперимент

Для тех кому мой колхозинг с тестированием оптимизаций показался сомнительным, предлагаю провести тестирование самостоятельно. Все скрипты для этого выложены в репозиторий.
Последовательность действий описана в use_case.sql.

Кроме того, используя связку:

noun.txt
adjective.txt
get_pdo.php
words_input.php
data_generation.php
ddl_generation.php

вы можете создать свой набор данных и провести свои эксперименты.

Кода в скриптах не много (на три php файла 1 000 строк), кроите как хотите.

Код работает под PHP 7.1 и PostgreSQL 10.1, после обработки напильником будет работать и для более ранних версий.

Заключение

Если вам понравилось, подписывайтесь на наш канал, ставьте лайки, пишите коменты.