Раскрытие возможностей JSON в MySQL: советы и подсказки
programming mysql json json-schema indexes

Раскрытие возможностей JSON в MySQL: советы и подсказки

12 сентября 2023 г.

Тип данных JSON впервые был представлен в MySQL 5.7.8 в августе 2015 года. Это было довольно давно, но я до сих пор вижу, что многие люди используют тип данных TEXT для хранения данных JSON. В этой статье я объясню, как и почему следует использовать тип данных JSON вместо TEXT, покажу, как перейти с TEXT на JSON, а также продемонстрирую несколько советов и рекомендаций по использованию типа данных JSON.

Зачем использовать тип данных JSON вместо TEXT

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

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

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

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

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

Строго говоря, функции JSON можно использовать с текстовыми данными. Но это не рекомендуется. Давайте сравним скорость функций JSON для типов данных TEXT и JSON и воспользуемся примерами из предыдущего статья. Но вместо сравнения старых и текущих столбцов JSON мы сравниваем JSON и TEXT.

Начнем с ТЕКСТА.

CREATE TABLE t_with_text
(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    json_col LONGTEXT,
    name     VARCHAR(100) AS (json_col ->> '$.name'),
    age      INT AS (json_col -> '$.age')
);

INSERT INTO t_with_text(json_col)
VALUES (JSON_OBJECT('name', 'Joe', 'age', 24,
                    'data', REPEAT('x', 10 * 1000 * 1000))),
       (JSON_OBJECT('name', 'Sue', 'age', 32,
                    'data', REPEAT('y', 10 * 1000 * 1000))),
       (JSON_OBJECT('name', 'Pete', 'age', 40,
                    'data', REPEAT('z', 10 * 1000 * 1000))),
       (JSON_OBJECT('name', 'Jenny', 'age', 27,
                    'data', REPEAT('w', 10 * 1000 * 1000)));

INSERT INTO t_with_text(json_col)
SELECT json_col
FROM t_with_text;
INSERT INTO t_with_text(json_col)
SELECT json_col
FROM t_with_text;

UPDATE t_with_text
SET json_col = JSON_SET(json_col, '$.age', age + 1); -- 16 rows affected in 4 s 418 ms

А теперь JSON.

CREATE TABLE t_with_json
(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    json_col LONGTEXT,
    name     VARCHAR(100) AS (json_col ->> '$.name'),
    age      INT AS (json_col -> '$.age')
);

INSERT INTO t_with_json(json_col)
VALUES (JSON_OBJECT('name', 'Joe', 'age', 24,
                    'data', REPEAT('x', 10 * 1000 * 1000))),
       (JSON_OBJECT('name', 'Sue', 'age', 32,
                    'data', REPEAT('y', 10 * 1000 * 1000))),
       (JSON_OBJECT('name', 'Pete', 'age', 40,
                    'data', REPEAT('z', 10 * 1000 * 1000))),
       (JSON_OBJECT('name', 'Jenny', 'age', 27,
                    'data', REPEAT('w', 10 * 1000 * 1000)));

INSERT INTO t_with_json(json_col)
SELECT json_col
FROM t_with_json;
INSERT INTO t_with_json(json_col)
SELECT json_col
FROM t_with_json;

UPDATE t_with_json
SET json_col = JSON_SET(json_col, '$.age', age + 1); -- 16 rows affected in 1 s 59 ms

Как видите, столбец JSON работает намного быстрее, чем TEXT.

Как перейти с TEXT на JSON

Чтобы перейти с TEXT на JSON, вам необходимо создать новый столбец с типом данных JSON, скопировать данные из столбца TEXT в столбец JSON и удалить столбец TEXT.

ALTER TABLE `users`
    ADD COLUMN `data_json` JSON AFTER `data`;

UPDATE `users`
SET `data_json` = `data`;

ALTER TABLE `users`
    DROP COLUMN `data`;

Это выглядит довольно просто, но есть несколько моментов, на которые следует обратить внимание.

Во-первых. Столбец TEXT может содержать недопустимые данные JSON. В этом случае вам необходимо исправить неверные данные JSON перед переходом на тип данных JSON.

Чтобы выбрать недопустимые данные JSON, вы можете использовать следующий запрос:

SELECT *
FROM `users`
WHERE JSON_VALID(`data`) = 0;

Этот запрос вернет все строки, в которых столбец data содержит недопустимые данные JSON.

Во-вторых, объем данных, хранящихся в столбцах JSON, может быть огромным. В этом случае вам нужно использовать такой инструмент, как pt-online-schema-change, чтобы избежать блокировки таблицы.

После перехода на тип данных JSON можно проверить данные на соответствие схеме JSON. Для этого вам необходимо создать схему JSON и использовать функцию JSON_SCHEMA_VALID.

SET @schema = '{
  "type": "object",
  "required": [
    "firstName",
    "lastName",
    "age"
  ],
  "properties": {
    "age": {
      "type": "integer",
      "minimum": 0
    },
    "firstName": {
      "type": "string",
      "minLength": 1,
      "maxLength": 120
    },
    "lastName": {
      "type": "string",
      "minLength": 1,
      "maxLength": 120
    }
  }
}
';

SELECT *
FROM `users`
WHERE JSON_SCHEMA_VALID(@schema, `data_json`) = 0;

JSON_SCHEMA_VALID работает только с действительными документами JSON. Если у вас есть недействительные документы JSON, сначала их необходимо исправить.

Индексирование данных JSON

MySQL позволяет создавать индексы для данных JSON. Чтобы создать индекс данных JSON, вам необходимо указать путь к значению JSON.

CREATE INDEX `idx_users_data_json_firstName`
    ON `users` ((CAST(`data_json` ->> '$.firstName' AS CHAR(120))));

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

ALTER TABLE `users`
    ADD COLUMN `firstName` VARCHAR(120) AS (`data_json` ->> '$.firstName') VIRTUAL;

CREATE INDEX `idx_users_firstName`
    ON `users` (`firstName`);

Многозначные индексы

В MySQL 8.0.17 появились многозначные индексы. Многозначные индексы позволяют создавать индексы для массивов JSON.

CREATE INDEX `idx_users_data_json_tags`
    ON `users` ((CAST(`data_json` ->> '$.tags' AS CHAR(255) array)));

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

CREATE TABLE explain_bug
(
    project_id INT  NULL,
    json_data  JSON NULL,
    INDEX project_id_json_data (project_id, (CAST(json_data -> '$' AS CHAR(255) ARRAY)))
) COLLATE = utf8mb4_unicode_ci;

INSERT INTO explain_bug (project_id, json_data)
VALUES (42, '[]');

SELECT *
FROM explain_bug
WHERE project_id = 42; -- nothing is returned

SELECT *
FROM explain_bug IGNORE INDEX (project_id_json_data)
WHERE project_id = 42; -- expected result is returned

Вот еще пара связанных ошибок: https://bugs.mysql.com/bug.php?id=108659 https://bugs.mysql.com/bug.php?id=109333

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

Функции JSON

MySQL предоставляет множество функций для работы с данными JSON. Полный список функций JSON можно найти здесь. Давайте проверим несколько из них, которые я использую чаще всего.

JSON_EXTRACT

Функция

JSON_EXTRACT позволяет извлечь значение из документа JSON. Требуется как минимум два аргумента: документ JSON и путь к значению.

SELECT JSON_EXTRACT('{"id": 42, "name": "Joe"}', '$.name'); -- "Joe"

Существует сокращение для функции JSON_EXTRACT. Вы можете использовать оператор -> вместо функции JSON_EXTRACT.

SELECT '{"id": 42, "name": "Joe"}' -> '$.name'; -- "Joe"

Также возможно извлечь несколько значений из документа JSON.

SELECT JSON_EXTRACT('{"id": 42, "name": "Joe"}', '$.name', '$.id'); -- ["Joe", 42]

JSON_SET

Функция

JSON_SET позволяет вам установить значение в документе JSON. Он принимает как минимум три аргумента: документ JSON, путь к значению и новое значение.

SELECT JSON_SET('{"id": 42, "name": "Joe"}', '$.name', 'Sue'); -- {"id": 42, "name": "Sue"}

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

SELECT JSON_SET('{"id": 42, "name": "Joe"}', '$.name', 'Sue', '$.id', 43); -- {"id": 43, "name": "Sue"}

JSON_REMOVE

Функция

JSON_REMOVE позволяет удалить значение из документа JSON. Требуется как минимум два аргумента: документ JSON и путь к значению.

SELECT JSON_REMOVE('{"id": 42, "name": "Joe"}', '$.name'); -- {"id": 42}

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

SELECT JSON_REMOVE('{"id": 42, "name": "Joe"}', '$.name', '$.id'); -- {}

JSON_MERGE_PRESERVE

Функция

JSON_MERGE_PRESERVE позволяет объединить несколько документов JSON в один. В качестве аргументов требуется как минимум два документа JSON:

SELECT JSON_MERGE_PRESERVE('{"id": 42, "name": "Joe"}',
                           '{"id": 43, "name": "Sue"}'); -- {"id": [42, 43], "name": ["Joe", "Sue"]}

Заключение

Тип данных JSON — отличный способ хранения данных JSON в MySQL. Он обеспечивает проверку, позволяет создавать индексы и использовать функции JSON для управления данными JSON. Это отличная альтернатива типу данных TEXT.

Оригинал

Recent Post

Categories

PREVIOUS ARTICLE
NEXT ARTICLE