Хранение массивов в MySQL: JSON, сериализация и нормализация - как выбрать надёжный способ

Почему хранение массивов в MySQL - это компромисс

У MySQL нет типа «массив». Колонка хранит одно значение, поэтому список приходится упаковывать: в JSON-документ, в строку с разделителями или в отдельные строки связной таблицы. Каждый способ что-то упрощает и что-то усложняет, и универсального варианта здесь нет.

Короткий ответ, к которому сводится вся статья. Если по элементам нужно искать, соединять таблицы и считать агрегаты, берите отдельную таблицу-связку. Если у вас MySQL 8.0.17 или новее и поиск ограничен операциями MEMBER OF, JSON_CONTAINS и JSON_OVERLAPS, работайте с нативным JSON и multi-valued индексом. Если массив всегда читается целиком и никогда не попадает в WHERE, хватит TEXT с сериализацией. Генеративные колонки закрывают узкий случай: фильтры идут по фиксированным позициям, например по первому тегу.

Решение определяют три фактора. Первый: какие выборки нужны. Поиск по элементу, сортировка по элементу, агрегат «сколько статей с тегом mysql», JOIN с другими таблицами. Второй: профиль нагрузки. Пять тысяч вставок в секунду и пять вставок в секунду дают разные ответы: JSON-документ при росте перезаписывается целиком, а вставка строки в таблицу-связку дешева. Третий: требования к целостности и валидации. Свободный TEXT принимает что угодно, включая обрезанные строки и мусор.

Типовые задачи выглядят так: теги статьи, список IP-адресов у хоста, история смены статусов заказа, набор ролей у пользователя, позиции заказа. Для тегов почти всегда нужен поиск по элементу, для истории статусов - чтение целиком с сортировкой по времени, для IP-адресов - проверка вхождения. Разные требования дают разные схемы. Общий разбор того, как выбирать СУБД под учётные данные, логи и кэш, есть в материале про выбор СУБД и организацию хранения данных.

Обзор четырёх подходов к хранению массивов в MySQL

Нативный тип JSON. Доступен с MySQL 5.7.8. Значение хранится в бинарном формате, проверяется при записи, поддерживает пути вида $.tags[0] и функции JSON_EXTRACT, JSON_CONTAINS, JSON_ARRAY, JSON_LENGTH.
TEXT с сериализацией. Массив упаковывается в строку: JSON-текст, CSV или результат serialize() из PHP. Работает на любой версии MySQL, индекс по отдельным элементам создать нельзя.
Таблица-связка. Классическая нормализация: одна строка на элемент, внешний ключ на основную таблицу, обычные B-tree индексы.
Генеративные колонки. Значение извлекается из JSON выражением и индексируется как обычный столбец, VIRTUAL или STORED. Доступны с MySQL 5.7.6.

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

Нативный тип JSON в MySQL: возможности и ограничения

Тип JSON с MySQL 5.7.8 хранит документ в бинарном формате: ключи объектов дедублицируются, дубликаты отбрасываются, значение проверяется на валидность в момент записи. Прикладной код передаёт обычную строку, сервер разбирает её сам.

CREATE TABLE articles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  doc JSON NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO articles (title, doc) VALUES
  ('Индексы InnoDB', JSON_OBJECT('tags', JSON_ARRAY('mysql', 'innodb', 'index'), 'priority', 2)),
  ('Настройка репликации', JSON_OBJECT('tags', JSON_ARRAY('mysql', 'replication'), 'priority', 1));

Массив лежит по пути $.tags. Чтение и проверка вхождения элемента выглядят так:

SELECT id, title FROM articles WHERE JSON_CONTAINS(doc->'$.tags', JSON_QUOTE('innodb'));
SELECT id, doc->>'$.tags[0]' AS first_tag, JSON_LENGTH(doc, '$.tags') AS tag_count FROM articles;
SELECT id, title FROM articles WHERE 'innodb' MEMBER OF(doc->'$.tags');

Оператор -> возвращает JSON-значение, ->> возвращает текст без кавычек. Для поиска по элементам массива есть три инструмента: JSON_CONTAINS (5.7+), MEMBER OF (8.0.17+) и JSON_OVERLAPS для пересечения двух массивов (8.0.17+). Без индекса все три читают каждую строку таблицы, поэтому на миллионе записей запрос займёт секунды, а не миллисекунды.

Ограничения типа JSON, о которых узнают уже в продакшене. Обычный индекс на JSON-колонку создать нельзя, а первичный ключ по ней невозможен. ORDER BY по JSON-колонке сортирует по внутреннему бинарному представлению и практической пользы не приносит, нужен CAST к числу или строке. Размер документа ограничен параметром max_allowed_packet: по умолчанию 4 МБ в MySQL 5.7 и 64 МБ в 8.0. Бинарный формат добавляет накладные расходы на каждый короткий ключ, поэтому документ из множества мелких полей занимает больше места, чем та же строка в TEXT. Обновление документа перезаписывает значение целиком; частичное обновление на месте в MySQL 8.0 работает для JSON_SET, JSON_REPLACE и JSON_REMOVE только тогда, когда новый фрагмент не длиннее прежнего. Значение по умолчанию для JSON-колонки разрешено с 8.0.13 и задаётся выражением.

Многофункциональные индексы по JSON-полям

MySQL 8.0.13 добавила функциональные индексы по выражениям, а 8.0.17 - multi-valued индексы, которые индексируют каждый элемент JSON-массива отдельной записью. Для массива строк объявление такое:

ALTER TABLE articles
  ADD INDEX idx_tags ((CAST(doc->'$.tags' AS CHAR(64) ARRAY)));

SELECT id, title FROM articles WHERE 'innodb' MEMBER OF(doc->'$.tags');

Для числового массива используется приведение к UNSIGNED ARRAY, что даёт индекс без строковых сравнений:

CREATE TABLE hosts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  ports JSON NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_ports ((CAST(ports AS UNSIGNED ARRAY)))
) ENGINE=InnoDB;

SELECT id, name FROM hosts WHERE 443 MEMBER OF(ports);

Правила работы multi-valued индекса стоит держать перед глазами. Он строится только по JSON-массиву, и в одном индексе допускается ровно один такой ключ. Оптимизатор применяет его к MEMBER OF, JSON_CONTAINS и JSON_OVERLAPS, если выражение в запросе совпадает с индексным. Сортировку и выборку по позиции он не ускоряет. Каждый элемент массива даёт отдельную индексную запись, поэтому массив из 200 тегов превращается в 200 записей на строку, а вместе с ними растут время вставки и размер индекса. MySQL 5.7 такие индексы не поддерживает, там остаются только генеративные колонки.

Скалярные поля внутри JSON индексируются обычным функциональным индексом или генеративной колонкой:

ALTER TABLE articles
  ADD COLUMN priority INT UNSIGNED
    GENERATED ALWAYS AS (CAST(doc->'$.priority' AS UNSIGNED)) VIRTUAL,
  ADD INDEX idx_priority (priority);

Аккуратнее с пустыми значениями: если элемент содержит JSON null, оператор -> вместе с CAST даст 0, а ->> вернёт SQL NULL. Отсутствие ключа и явный null при таком подходе склеиваются в одно значение, поэтому проверьте данные до того, как строить фильтры и отчёты. Как читать EXPLAIN и собирать покрывающие индексы под высокую нагрузку, разобрано в руководстве по настройке InnoDB и анализу запросов.

TEXT с сериализацией: простота против производительности

Сериализация означает, что массив лежит в текстовой колонке: CSV через запятую, JSON-строка или результат serialize() из PHP. Схема простая, миграций не требует, работает даже на MySQL 4.x.

CREATE TABLE articles_text (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  tags TEXT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO articles_text (title, tags) VALUES ('Индексы InnoDB', 'mysql,innodb,index');

SELECT id, title FROM articles_text WHERE tags LIKE '%innodb%';
SELECT id, title FROM articles_text WHERE FIND_IN_SET('innodb', tags);

Оба запроса выполняют полный перебор таблицы. LIKE с ведущим шаблоном вдобавок даёт ложные срабатывания: подстрока innodb найдётся в innodb_plugin и в произвольном тексте. FIND_IN_SET сравнивает целые элементы с учётом разделителя, поэтому ложных совпадений нет, но индекс всё равно не используется и запрос деградирует линейно с ростом таблицы.

Другие издержки. Лимиты типов: TEXT хранит 65 535 байт, MEDIUMTEXT около 16 МБ, LONGTEXT около 4 ГБ. Значения с запятыми внутри ломают CSV, если не вводить экранирование, а пустое значение и отсутствие элемента выглядят одинаково. Сравнение зависит от collation: в utf8mb4_0900_ai_ci LIKE игнорирует регистр, и запрос '%MYSQL%' найдёт mysql. Формат serialize() из PHP привязывает данные к одному языку, а десериализация недоверенной строки открывает путь к подстановке объектов, поэтому хранить в этом формате пользовательский ввод нельзя.

Подход оправдан там, где массив читается целиком и не участвует в WHERE: конфигурация плагина, снимок параметров, набор полей ответа внешнего API. Для выборок по элементам TEXT не подходит.

Нормализация: отдельная таблица-связка для массива

Нормализация выносит элементы в отдельные строки. Поиск по элементу превращается в обычный index seek, целостность обеспечивают внешние ключи, а агрегаты считаются группировкой.

CREATE TABLE articles_norm (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE article_tags (
  article_id INT UNSIGNED NOT NULL,
  tag VARCHAR(64) NOT NULL,
  PRIMARY KEY (article_id, tag),
  INDEX idx_tag_article (tag, article_id),
  CONSTRAINT fk_article_tags FOREIGN KEY (article_id) REFERENCES articles_norm (id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO article_tags (article_id, tag) VALUES (1, 'mysql'), (1, 'innodb'), (1, 'index');

SELECT a.id, a.title
FROM articles_norm AS a
JOIN article_tags AS t ON t.article_id = a.id
WHERE t.tag = 'innodb';

Составной индекс (tag, article_id) закрывает запрос полностью: сервер находит нужные элементы и берёт article_id прямо из индекса. Обратная задача, собрать массив для статьи, решается JOIN или агрегатом:

SELECT a.id, JSON_ARRAYAGG(t.tag) AS tags
FROM articles_norm AS a
JOIN article_tags AS t ON t.article_id = a.id
GROUP BY a.id;

В MySQL 5.7 JSON_ARRAYAGG отсутствует, там используется GROUP_CONCAT с явным SEPARATOR. Цена решения: на статью с пятью тегами пишутся пять строк вместо одной, схема обрастает JOIN, а пагинация по статьям требует DISTINCT или подзапроса. Частотный анализ вида SELECT tag, COUNT(*) FROM article_tags GROUP BY tag выполняется быстро, но на таблице в сотни миллионов строк лучше держать отдельный справочник тегов со счётчиками.

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

Генеративные колонки: гибрид JSON и индексации

Генеративная колонка вычисляется выражением над JSON и индексируется как обычный столбец. Это компромисс: документ остаётся гибким, а конкретное поле получает полноценный индекс.

ALTER TABLE articles
  ADD COLUMN tag_1 VARCHAR(64)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.tags[0]'))) STORED,
  ADD INDEX idx_tag_1 (tag_1);

SELECT id, title FROM articles WHERE tag_1 = 'mysql';

VIRTUAL-колонка не хранится на диске и вычисляется при чтении, значение индекса при этом материализуется. STORED-колонка пишется в строку таблицы, её можно поставить в первичный ключ, но место на диске и стоимость записи растут. Выражение допускает только детерминированные функции и не может содержать подзапросы.

Главное ограничение: генеративная колонка привязана к позиции. Она ускоряет фильтры по первому, второму или конкретному элементу, но не отвечает на вопрос «есть ли mysql где угодно в массиве». Для произвольного поиска по всем элементам нужен multi-valued индекс (MySQL 8.0.17+) или таблица-связка. Добавление STORED-колонки на большой таблице перестраивает её, поэтому на продакшене используйте pt-online-schema-change или gh-ost и проверяйте на тестовом стенде, что инструмент корректно переносит генеративные колонки.

Сравнение подходов: таблица решений

Критерий	JSON без индекса	JSON + multi-valued index	TEXT с сериализацией	Таблица-связка	Генеративная колонка
Минимальная версия MySQL	5.7.8	8.0.17	любая	любая	5.7.6
Поиск по любому элементу	полный перебор	index seek	полный перебор	index seek	нет
Поиск по фиксированной позиции	полный перебор	нет	полный перебор	через ORDER BY позиции	index seek
Агрегация по элементам	медленно, JSON_TABLE	частично	невозможна	GROUP BY, быстро	нет
Валидация данных	тип + JSON_SCHEMA_VALID	тип + JSON_SCHEMA_VALID	нет	типы колонок и FK	тип JSON
Стоимость записи	перезапись документа	по записи на элемент	низкая	N строк на объект	пересчёт колонки
Размер на диске	средний	средний + индекс	минимальный	высокий	средний + индекс
Чтение массива целиком	одна колонка	одна колонка	одна колонка	JOIN или агрегат	одна колонка

Практические выводы по таблице. Для тегов и ролей с поиском на MySQL 8.0.17+ выбирайте JSON с multi-valued индексом, когда массив небольшой (до 30-50 элементов) и целостность обеспечивает валидация схемы. Для массивов на сотни элементов, отчётов и JOIN берите таблицу-связку. Для конфигураций и снимков без фильтрации оставляйте TEXT. Генеративные колонки берите, когда позиция элемента стабильна и поиск идёт по ней. Похожая логика индексации массивов есть в других СУБД: массивы и multikey-индексы в MongoDB устроены так же: один элемент документа порождает одну запись в индексе.

Миграция между подходами: с сериализации на JSON и обратно

Переход с TEXT на JSON выполняется в четыре шага, и каждый шаг лучше проверять на копии данных. Шаг первый: добавить JSON-колонку, не трогая старую.

ALTER TABLE articles_text ADD COLUMN doc JSON NULL;

SELECT id, tags FROM articles_text
WHERE JSON_VALID(CONCAT('["', REPLACE(TRIM(tags), ',', '","'), '"]')) = 0;

Второй запрос ищет строки, которые не превратятся в корректный JSON: в них есть кавычки, обратные слэши или неэкранированные разделители. Такие записи чинятся руками, иначе UPDATE молча упадёт с ошибкой валидации. Шаг второй: заполнить документ, приведя массив к объекту с ключом tags.

UPDATE articles_text
SET doc = CONCAT('{"tags":["', REPLACE(TRIM(tags), ',', '","'), '"]}')
WHERE tags <> '' AND doc IS NULL;

Строка при записи в JSON-колонку разбирается и валидируется автоматически, отдельная функция приведения не нужна. Шаг третий: сверить результат.

SELECT id FROM articles_text
WHERE JSON_LENGTH(doc, '$.tags') <> 1 + LENGTH(tags) - LENGTH(REPLACE(tags, ',', ''));

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

ALTER TABLE articles_text RENAME COLUMN doc TO tags_json;
ALTER TABLE articles_text ADD INDEX idx_tags ((CAST(tags_json->'$.tags' AS CHAR(64) ARRAY)));

Обратная миграция из JSON в TEXT или в таблицу-связку опирается на JSON_TABLE, доступную с MySQL 8.0.4:

SELECT a.id, GROUP_CONCAT(jt.tag ORDER BY jt.tag SEPARATOR ',') AS tags_csv
FROM articles_text AS a
JOIN JSON_TABLE(a.tags_json, '$.tags[*]' COLUMNS (tag VARCHAR(64) PATH '$')) AS jt
GROUP BY a.id;

INSERT IGNORE INTO article_tags (article_id, tag)
SELECT a.id, jt.tag
FROM articles_text AS a
JOIN JSON_TABLE(a.tags_json, '$.tags[*]' COLUMNS (tag VARCHAR(64) PATH '$')) AS jt;

У GROUP_CONCAT есть параметр group_concat_max_len со значением по умолчанию 1024 байта, для длинных массивов его поднимают до 65535 и выше, иначе строка обрежется без предупреждения. На MySQL 5.7 JSON_TABLE отсутствует, поэтому разбор массива идёт через таблицу чисел и JSON_EXTRACT(doc, CONCAT('$.tags[', n, ']')) с условием jt.n < JSON_LENGTH(doc, '$.tags'). Для MariaDB помните, что тип JSON там синоним LONGTEXT с проверкой json_valid, поэтому multi-valued индексов нет: остаются только генеративные колонки. Перед любой миграцией проверьте, что целевая версия сервера поддерживает нужные функции: 5.7 не умеет ни JSON_TABLE, ни JSON_OVERLAPS, ни multi-valued индексы.

Практические рекомендации и типичные ошибки

Алгоритм выбора из пяти шагов. Первый: выясните, нужен ли поиск по отдельным элементам и по каким именно. Второй: проверьте версию MySQL и наличие требуемых функций, включая жёсткую границу 8.0.17 для multi-valued индексов. Третий: оцените размер массива и частоту записи, чтобы понять стоимость индексации каждого элемента. Четвёртый: определите требования к целостности и валидации: JSON_SCHEMA_VALID доступна с 8.0.17 и проверяет документ по схеме. Пятый: выберите схему и обоснуйте её планом запроса, а не привычкой.

Частые ошибки, которые встречаются в рабочих базах. Поиск по JSON через LIKE по текстовому представлению вместо JSON_CONTAINS: результат зависит от порядка ключей и пробелов. Отсутствие индекса при регулярном поиске по тегам: полный перебор на таблице в миллион строк занимает секунды и упирается в I/O. Хранение массивов на тысячи элементов в JSON там, где выборка идёт по элементам. Игнорирование multi-valued индексов на MySQL 8.0.17+, когда задача решается одной строкой DDL. Генеративная колонка для «поиска вообще по массиву», хотя она видит только фиксированную позицию. Обрезка GROUP_CONCAT из-за значения group_concat_max_len по умолчанию. Хранение PHP-сериализованных строк, которые после обновления кода перестают читаться.

Отдельно про проверку изменений: любой ALTER TABLE на боевой таблице в сотни гигабайт выполняйте через pt-online-schema-change или gh-ost, а стенд для отработки миграции поднимайте на копии реальных данных. Быстрый способ получить чистую копию MySQL и обкатать сценарий миграции без риска для продакшена: развернуть инстанс базы в облаке, например в Timeweb Cloud, где доступны управляемые СУБД и VDS с почасовой оплатой. Проверьте на копии три вещи: план запроса после миграции, прирост размера индексов и время выполнения UPDATE на полном объёме строк. Если поиск по элементам массива нужен постоянно и массив растёт, проектируйте таблицу-связку сразу: переделка схемы на живой базе обойдётся дороже, чем нормализация на старте.