JSON-колонки в MySQL: функции, индексы и генерируемые колонки

Настоящий тип JSON есть в MySQL с версии 5.7, и в 8.4 это вполне хорошее место для данных переменной формы: атрибутов товаров, блоков настроек, payload вебхуков, feature-флагов. Чем он не является, так это индульгенцией на проектирование схемы. JSON-колонку нельзя проиндексировать напрямую, запрос с фильтром по полю внутри неё сканирует всю таблицу, а каждое значение возвращается с типом, о котором приходится думать. Ответ на все три проблемы - одна и та же возможность: генерируемая колонка, которая вытаскивает одно поле из документа, с обычным индексом на ней. Это сочетание - гибкий документ плюс проиндексированные поля, по которым вы действительно делаете запросы, - и есть способ использовать JSON в MySQL, не пожалев об этом.

Что на самом деле хранит тип JSON#

Колонка JSON - это не текст с прикрученной проверкой. При вставке MySQL разбирает документ, отклоняет его, если он невалиден, и сохраняет в бинарном формате, который позволяет прочитать один элемент, не разбирая весь документ.

CREATE TABLE products (  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  sku        VARCHAR(40) NOT NULL UNIQUE,  attributes JSON NOT NULL DEFAULT (JSON_OBJECT()),  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO products (sku, attributes) VALUES  ('MUG-01', '{"colour": "blue", "capacity_ml": 350, "tags": ["kitchen", "gift"]}'),  ('TEE-02', '{"colour": "black", "sizes": ["S", "M", "L"], "price": 19.5}');

Некоторые следствия того, что документ хранится в разобранном виде:

• Невалидный JSON - это ошибка, а не предупреждение. INSERT ... VALUES ('{colour: blue}') падает с ошибкой 3140, Invalid JSON text. Это полезное свойство: колонка TEXT сохранила бы мусор.
• Документ нормализуется. Пробелы отбрасываются, ключи объектов сортируются, а если ключ встречается дважды, побеждает последнее значение. Исходный текст байт в байт вы не получите, и это важно, если вы храните подписанные payload, - держите такие данные в колонке TEXT или BLOB.
• Размер ограничен `max_allowed_packet`, в 8.4 по умолчанию это 64 MB. На практике документы больше нескольких сотен килобайт становятся проблемой проектирования задолго до того, как упрутся в этот лимит.
• У JSON-колонки не может быть литерального значения по умолчанию. Выражение по умолчанию DEFAULT (JSON_OBJECT()) из примера выше, со скобками, разрешено начиная с 8.0.13.

JSON_STORAGE_SIZE(attributes) показывает, сколько байт значение занимает на диске, и это быстрый способ найти строки, в которые кто-то что-то напихал.

Чтение значений: пути, -> и ->>#

К полям обращаются через выражение пути. $ - весь документ, $.colour - элемент объекта, $.tags[0] - элемент массива, $.tags[last] - последний элемент, $.tags[*] - все элементы, а $**.price - любой price на любой глубине. Имя элемента с пробелами или дефисами берётся в кавычки: $."list-price".

Большую часть чтения покрывают два оператора:

SELECT sku,       attributes->>'$.colour'      AS colour,       attributes->'$.capacity_ml'  AS capacity,       JSON_LENGTH(attributes, '$.tags') AS tag_countFROM productsWHERE attributes->>'$.colour' = 'blue';

Какой оператор использовать - не дело вкуса. -> возвращает значение JSON, поэтому сравнивает как JSON: числа как числа, строки как строки. ->> возвращает текст, поэтому attributes->>'$.price' > 9 сравнивает строки, если MySQL не выполнит преобразование, а как текст '10' < '9'. Используйте ->> для строк, которые вы показываете или сравниваете со строкой; используйте -> (или явный CAST) для чисел.

Поиск внутри массивов и объектов#

Фильтрация по вхождению в массив - это то, ради чего JSON стоит использовать, потому что альтернатива - промежуточная таблица.

-- Products tagged 'gift'SELECT sku FROM products WHERE 'gift' MEMBER OF (attributes->'$.tags');-- Contains all of these valuesSELECT sku FROM productsWHERE JSON_CONTAINS(attributes->'$.sizes', '["M", "L"]');-- Contains any of these valuesSELECT sku FROM productsWHERE JSON_OVERLAPS(attributes->'$.tags', '["gift", "sale"]');-- Find the path to a valueSELECT JSON_SEARCH(attributes, 'one', 'kitchen') FROM products;  -- "$.tags[0]"

MEMBER OF и JSON_OVERLAPS появились в 8.0.17, и именно их стоит запомнить, потому что их может обслуживать многозначный индекс (о нём ниже). JSON_CONTAINS тоже использует этот индекс.

Когда массив JSON нужно обработать как строки - сделать по нему join, сгруппировать по его элементам или выгрузить, - JSON_TABLE превращает документ в производную таблицу:

SELECT p.sku, t.tagFROM products p,     JSON_TABLE(p.attributes, '$.tags[*]'       COLUMNS (tag VARCHAR(40) PATH '$')) AS t;

Обратное направление, из строк в JSON, - это JSON_OBJECT, JSON_ARRAY и агрегатные функции JSON_ARRAYAGG и JSON_OBJECTAGG, самый чистый способ собрать ответ API одним запросом: SELECT JSON_ARRAYAGG(JSON_OBJECT('sku', sku, 'colour', attributes->>'$.colour')) FROM products.

Обновление части документа#

Не обязательно читать, изменять и записывать обратно весь документ в коде приложения. В MySQL есть функции, которые возвращают изменённую копию:

UPDATE productsSET attributes = JSON_SET(attributes, '$.price', 21.0, '$.on_sale', true)WHERE sku = 'TEE-02';UPDATE productsSET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'sale')WHERE sku = 'MUG-01';

Делать это в SQL не просто короче - это безопаснее при конкурентном доступе. Два запроса, каждый из которых читает документ, меняет своё поле и записывает его обратно, потеряют одно из изменений. Два оператора UPDATE ... JSON_SET для одной строки выполняются последовательно благодаря блокировке строки, и оба изменения сохраняются. Общая версия этой проблемы разобрана в статье транзакции, блокировки и deadlock в MySQL.

Когда JSON_SET, JSON_REPLACE или JSON_REMOVE обновляют колонку на месте (SET col = JSON_SET(col, ...)) и новое значение не больше старого, InnoDB может выполнить частичное обновление вместо перезаписи всего значения. С binlog_row_value_options = PARTIAL_JSON бинарный журнал тоже записывает только изменение. Эту оптимизацию вы получаете бесплатно, обновляя данные в SQL, и не получаете вовсе, перезаписывая документ из приложения.

JSON_MERGE_PRESERVE (и его устаревший псевдоним JSON_MERGE) склеивает массивы и объединяет повторяющиеся ключи в массивы; JSON_MERGE_PATCH заменяет значения, а это почти всегда то, что подразумевает «patch» в API.

Индексирование JSON через генерируемые колонки#

JSON-колонка не может входить в индекс. WHERE attributes->>'$.colour' = 'blue' на миллионе строк читает миллион документов. Решение - генерируемая колонка: колонка, значение которой вычисляется выражением от других колонок той же строки.

ALTER TABLE products  ADD COLUMN colour VARCHAR(30)    GENERATED ALWAYS AS (attributes->>'$.colour') VIRTUAL,  ADD INDEX idx_colour (colour);

Колонка VIRTUAL (по умолчанию) вычисляется при чтении строки и не занимает места в таблице; при этом InnoDB может включить её во вторичный индекс, где вычисленное значение хранится. Колонка STORED вычисляется при записи и хранится в строке, что стоит места, зато её можно использовать в первичном ключе. Для индексирования полей JSON обычный выбор - виртуальная колонка.

EXPLAIN SELECT sku FROM products WHERE colour = 'blue';-- type: ref, key: idx_colour

Обращайтесь к генерируемой колонке по имени. Оптимизатор умеет распознать и исходное выражение и использовать индекс для WHERE attributes->>'$.colour' = 'blue', но только когда выражение, его тип и collation в точности совпадают с определением колонки; имя колонки никогда не бывает двусмысленным. Как читать план, описано в статье индексы и EXPLAIN в MySQL.

На чём попадаются:

• Тип имеет значение. VARCHAR(30) AS (attributes->>'$.colour') хранит текст. Для числа нужно приведение: price DECIMAL(10,2) AS (CAST(attributes->>'$.price' AS DECIMAL(10,2))). Тогда документ, в котором price не числовой, упадёт при вставке, и часто это именно та валидация, которая была нужна.
• Значение, слишком длинное для колонки, - это ошибка в строгом режиме, а он в 8.4 включён по умолчанию. Подбирайте размер генерируемой колонки под данные, иначе вставка упадёт на той единственной строке с длинным значением.
• Разрешены только детерминированные выражения - никаких NOW(), RAND(), пользовательских переменных и подзапросов.
• Добавить виртуальную колонку дёшево, построить её индекс - нет. Колонка - это изменение метаданных, а индексу нужно прочитать каждую строку. InnoDB строит его онлайн, разрешая тем временем чтение и запись, но на большой таблице это всё равно занимает время и I/O. Делайте это в тихий час.

Функциональные и многозначные индексы#

Начиная с 8.0.13 можно обойтись без именованной колонки и индексировать выражение напрямую. MySQL сам создаёт для вас скрытую виртуальную колонку. В случае строк здесь есть ловушка с collation: CAST(... AS CHAR) даёт collation по умолчанию, а ->> даёт utf8mb4_bin, и индекс, построенный на одном, не используется для сравнения с другим. Решение из самого руководства MySQL - указать collation в индексе:

ALTER TABLE products  ADD INDEX idx_material ((CAST(attributes->>'$.material' AS CHAR(30))                           COLLATE utf8mb4_bin));SELECT sku FROM products WHERE attributes->>'$.material' = 'steel';

Это работает, но именованную генерируемую колонку проще читать, проще использовать в запросах, и её видит каждый, кто открывает схему. Предпочитайте её, если нет причин поступить иначе.

Массивам нужен другой вид индекса. Начиная с 8.0.17 многозначный индекс хранит по одной записи индекса на каждый элемент массива, поэтому его могут использовать MEMBER OF, JSON_CONTAINS и JSON_OVERLAPS:

CREATE TABLE customers (  id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  name     VARCHAR(100) NOT NULL,  profile  JSON NOT NULL,  INDEX idx_zips ((CAST(profile->'$.zipcodes' AS UNSIGNED ARRAY))));SELECT name FROM customers WHERE 94507 MEMBER OF (profile->'$.zipcodes');

Тип приведения - часть индекса: UNSIGNED ARRAY, SIGNED ARRAY, CHAR(n) ARRAY, DATE ARRAY и ещё несколько, - и значения в документах должны в него преобразовываться. У многозначного индекса может быть только одна часть ключа с массивом, он не может быть первичным или покрывающим и используется для фильтрации, а не для сортировки.

Перевод существующей колонки TEXT в JSON#

На практике обычно начинают не с новой таблицы, а со старой, где кто-то много лет назад хранил JSON в колонке TEXT или LONGTEXT. Перевод даёт вам валидацию, бинарный формат и все функции, описанные выше. Преобразование падает на первой невалидной строке, поэтому сначала найдите такие строки:

SELECT id, LEFT(settings, 80) AS previewFROM accountsWHERE settings IS NOT NULL AND JSON_VALID(settings) = 0;

Ожидайте три вида результатов. Пустые строки, которые не являются валидным JSON, - решите, означают они NULL или {}, и обновите их. Документы с одинарными кавычками или висячими запятыми, написанные вручную или сериализатором, который на самом деле выдавал не JSON, - исправьте их в приложении, которое их записывало, а затем в данных. И вывод PHP serialize() или что-то похожее - это значит, что колонка никогда не была JSON и нужен скрипт, а не SQL.

Когда запрос перестанет что-либо возвращать, выполните преобразование одним оператором:

UPDATE accounts SET settings = '{}' WHERE settings = '';ALTER TABLE accounts MODIFY settings JSON NULL;

Изменение типа колонки перестраивает таблицу: InnoDB копирует каждую строку, и всё это время конкурентная запись заблокирована (ALGORITHM=COPY). Для таблицы в несколько сотен мегабайт это от секунд до минут; для чего-то большего запланируйте окно обслуживания или добавьте новую колонку JSON, заполните её пакетами и переключите приложение, прежде чем удалять старую. В любом случае сначала сделайте дамп - как сделать его без блокировки таблицы, описано в статье бэкап и восстановление через mysqldump.

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

Сортировка и группировка по полям JSON#

ORDER BY attributes->'$.price' работает, но сортирует значения JSON, а у сравнения JSON свои правила: числа сортируются как числа, строки как строки, а значения разных типов JSON упорядочиваются сначала по типу, потом по значению. Если в одних документах цена хранится как 19.5, а в других как "19.50", они не перемешаются так, как вы ожидаете. Когда порядок важен, приводите тип явно - ORDER BY CAST(attributes->>'$.price' AS DECIMAL(10,2)), - а ещё лучше сортируйте по генерируемой колонке с таким приведением: тогда порядок может обеспечить индекс, а не filesort.

С группировкой то же самое: GROUP BY attributes->>'$.colour' группирует по тексту, поэтому "Blue" и "blue" оказываются в разных группах из-за бинарного collation, который возвращает ->>. Генерируемая колонка VARCHAR с регистронезависимым collation по умолчанию объединит их. Такие мелкие различия - хороший аргумент за то, чтобы пропускать каждое поле, по которому вы строите отчёты, через генерируемую колонку с объявленным типом и collation.

Валидация документов и когда JSON не нужен#

JSON-колонка принимает любой валидный документ. Если ваше приложение предполагает, что price всегда число, обеспечьте это, потому что та единственная строка, где это строка, всплывёт в отчёте через полгода. JSON_SCHEMA_VALID (8.0.17) проверяет документ по JSON Schema, а ограничение CHECK заставляет MySQL применять проверку при каждой записи:

ALTER TABLE products ADD CONSTRAINT attributes_shape CHECK (  JSON_SCHEMA_VALID('{    "type": "object",    "properties": {      "colour": {"type": "string"},      "price":  {"type": "number", "minimum": 0},      "tags":   {"type": "array", "items": {"type": "string"}}    }  }', attributes));

Нарушение проявляется как ошибка 3819, Check constraint 'attributes_shape' is violated. Генерируемые колонки с приведением типов - облегчённая версия той же идеи.

Теперь честная часть. JSON - неверный выбор, когда:

• Вы делаете по нему join. Внешние ключи не могут указывать внутрь документа, поэтому ссылочной целостности больше нет. user_id внутри JSON - это баг, который ждёт удалённого пользователя.
• У каждой строки одинаковые поля. Тогда это колонки. Колонки меньше, типизированы, индексируются без лишних церемоний и видны в схеме.
• Вы много раз в секунду обновляете один счётчик внутри большого документа. Каждое обновление блокирует строку и, если значение растёт, перезаписывает документ.
• Вы постоянно агрегируете по нему. SUM(CAST(attributes->>'$.price' AS DECIMAL)) по всей таблице - это полное сканирование, как ни индексируй.

Разумное правило: фиксированные поля, по которым делают запросы и join, - это колонки; необязательные, разреженные или задаваемые клиентом поля идут в одну JSON-колонку; любое поле из JSON, которое стало достаточно важным, чтобы по нему фильтровать, повышается до генерируемой колонки с индексом. Если вы выбираете между JSON в MySQL и документной базой данных, сравнение jsonb и GIN-индексов PostgreSQL есть в статье MySQL или PostgreSQL, а сторона документных хранилищ - в статье Postgres или MongoDB.

FAQ#

JSON-колонка медленнее обычных колонок?

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

Можно ли повесить внешний ключ на значение внутри JSON?

Не напрямую. Можно добавить генерируемую колонку STORED, извлекающую значение, и повесить внешний ключ на неё - это работает для одного скалярного значения. Если вы ловите себя на таком, значению, скорее всего, место в обычной колонке.

Почему мой запрос возвращает "blue" в кавычках?

Вы использовали -> или JSON_EXTRACT, которые возвращают значения JSON, а строка JSON включает свои кавычки. Используйте ->> или оберните выражение в JSON_UNQUOTE, чтобы получить обычный текст.

Работает ли JSON в MySQL с ORM?

Да. Eloquent в Laravel приводит JSON-колонки к массивам и поддерживает where('attributes->colour', 'blue'), JSONField в Django поддерживает MySQL, а в SQLAlchemy есть тип JSON. Чего ORM за вас не сделает, так это не создаст генерируемую колонку и индекс - добавьте их в миграции.

Как найти документы, в которых нет поля?

WHERE NOT JSON_CONTAINS_PATH(attributes, 'one', '$.price'). Учтите, что attributes->>'$.price' IS NULL тоже находит документы, где поле отсутствует, но не те, где оно явно равно JSON null, - они возвращают строку null.