Реализация Entity-Attribute-Value

Требования

• Открытый набор «атрибутов» (ключ=значение) для каждой «сущности». То есть, список атрибутов неизвестен на этапе разработки и будет расти в будущем. (Это делает одну колонку на атрибут непрактичным.)
• «Специальные» запросы для проверки атрибутов.
• Значения атрибутов имеют разные типы (числа, строки, даты и т. д.)
• Масштабируемость для большого количества сущностей при сохранении производительности.

Это известно под разными названиями

• EAV — Сущность-Атрибут-Значение
• Ключ-значение
• RDF — Это разновидность EAV
• MariaDB имеет динамические столбцы, которые чем-то похожи на приведенное ниже решение, с дополнительным преимуществом индексирования скрытых в BLOB столбцов. (Есть свои особенности.)
• MySQL 5.7 имеет тип данных JSON, а также функции для доступа к частям
• MongoDB, CouchDB и другие — не базирующиеся на SQL.

Плохое решение

• Таблица с 3 колонками: entity_id, key, value
• «Значение» — строка, или, возможно, несколько столбцов в зависимости от типа данных или других ухищрений.
• a JOIN b ON a.entity=b.entity AND b.key='x' JOIN c ON ... WHERE a.value=... AND b.value=...

Проблемы

• SELECT-запросы становятся сложными — много JOIN-ов
• Проблемы с типами данных — неудобно помещать числа в строки
• Числа, хранящиеся в VARCHAR, не сравниваются «корректно», особенно при проверке диапазонов.
• Большой объем данных.
• Сложное удаление дубликатов значений.

Решение

Определите, какие столбцы нужно искать/сортировать с помощью SQL-запросов. Нет, не все столбцы должны быть доступны для поиска или сортировки. Некоторые столбцы часто используются для выбора; определите их. Возможно, не все они будут использоваться во всех запросах, но некоторые будут использоваться в каждом запросе.

Решение использует одну таблицу для всех данных EAV. Столбцы включают поля, доступные для поиска, плюс один BLOB. Поля, доступные для поиска, объявляются соответствующим образом (INT, TIMESTAMP и т. д.). BLOB содержит JSON-кодирование всех дополнительных полей.

Таблица должна быть InnoDB, следовательно, должна иметь первичный ключ. entity_id является «естественным» первичным ключом. Добавьте небольшое количество других индексов (часто составных) по полям, доступным для поиска. Разбиение вряд ли будет полезным, если сущности должны удаляться через некоторое время. (Пример: статьи новостей)

Но что насчет специальных запросов?

Вы включили самые важные поля для поиска — дату, категорию и т. д. Эти поля должны существенно сужать данные. Когда также требуется фильтрация по чему-то более специфическому, это обрабатывается по-другому. Код приложения будет обращаться к BLOB; подробности позже.

Почему это работает

• Вы вряд ли будете искать по большему количеству полей.
• Объем данных на диске меньше; меньше —> более кешируемо —> быстрее
• Нет необходимости в JOIN-ах
• Индексы полезны
• Одна таблица содержит одну строку на сущность и может расти по мере необходимости. (EAV требует многих строк на сущность.)
• Производительность зависит от индексов на полях, доступных для поиска.
• По желанию, можно дублировать индексированные поля в BLOB.
• Значения, отсутствующие в полях, доступных для поиска, должны быть NULL (или аналогичным), и код должен обрабатывать это.

Подробности о BLOB/JSON

• Создайте дополнительные (или все) пары ключ-значение в хеше (ассоциативном массиве) в приложении. Кодируйте его. Сжимайте. Вставьте эту строку в BLOB.
• JSON рекомендуется, но не обязательно; он проще, чем XML. Можно использовать другие форматы сериализации (например, YAML).
• Сжимайте JSON и помещайте его в BLOB (или MEDIUMBLOB), а не в поле TEXT. Сжатие уменьшает размер примерно в 3 раза.
• При SELECT-запросах разархивируйте BLOB. Раскодируйте строку в хеш. Теперь вы готовы получить/отобразить любое из дополнительных полей.
• Если вы решите использовать функции JSON MariaDB или 5.7, вам придется отказаться от описанной функции сжатия.
• Встроенный тип данных JSON MySQL 5.7.8 использует двоичный формат для более эффективного доступа.

Выводы

• Схема относительно компактна (сжатие, реальные типы данных, меньшая избыточность и т. д., чем EAV).
• Запросы быстрые (поскольку выбраны «хорошие» индексы).
• Расширяемая (JSON рад новым полям).
• Совместимая (Без сторонних продуктов, только поддерживаемые продукты).
• Проверка диапазонов работает (в отличие от хранения INT в VARCHAR).
• (Недостаток) Нельзя использовать атрибуты без индексов в WHERE или ORDER BY, с этим нужно работать в приложении. (MySQL 5.7 частично устраняет этот недостаток).

Дата публикации

Опубликовано в январе 2014 г.; обновлено в феврале 2016 г.

• Динамические столбцы MariaDB Динамические столбцы
• JSON MySQL 5.7

Это очень многообещающе; мне нужно провести дополнительное исследование, чтобы понять, насколько актуальна эта статья в свете: Использование MySQL в качестве хранилища документов в версии 5.7, Дополнительные обсуждения хранилища документов

Если вы настаиваете на использовании EAV, установите optimizer_search_depth=1.

См. также

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/eav