Индексирование JSON в MySQL
MySQL предоставил нам Для просмотра ссылки Войди
Хотя MySQL предоставляет нам функции для чтения и записи данных JSON, вы быстро обнаружите то, чего явно не хватает: возможность прямого индексирования ваших столбцов JSON.
В других базах данных лучшим способом прямого индексирования столбца JSON обычно является тип индекса, известный как обобщенный инвертированный индекс, или сокращенно GIN. Поскольку MySQL не предлагает индексы GIN, мы не можем напрямую индексировать весь сохраненный документ JSON. Однако не все потеряно, потому что MySQL дает нам возможность косвенно индексировать части наших сохраненных документов JSON.
В зависимости от версии MySQL, которую вы используете, у вас есть два варианта индексации JSON. В MySQL 5.7 вам пришлось бы создать промежуточный Для просмотра ссылки Войди
Давайте начнем с примера таблицы, используемой для регистрации различных действий, выполняемых в приложении.
Код:
CREATE TABLE `activity_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`properties` json NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
Код:
{
"uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502",
"request": {
"email": "[email protected]",
"firstName": "Little",
"formType": "vehicle-inquiry",
"lastName": "Bobby",
"message": "Hello, can you tell me what the specs are for this vehicle?",
"postcode": "75016",
"townCity": "Dallas"
}
}Давайте рассмотрим наш первый вариант индексации: сгенерированные столбцы.
Индексирование JSON через сгенерированный столбец #
Сгенерированный столбец можно рассматривать как вычисляемый, вычисляемый или производный столбец. Это столбец, значение которого является результатом выражения, а не прямого ввода данных. Выражение может содержать литеральные значения, встроенные функции или ссылки на другие столбцы. Результат выражения должен быть скалярным и детерминированным.Поскольку мы пытаемся проиндексировать request.emailполе в propertiesстолбце, наш сгенерированный столбец будет использовать Для просмотра ссылки Войди
Чтобы убедиться, что мы правильно сформировали наше выражение, мы сначала выполним SELECTинструкцию и проверим результаты.
->>Оператор является сокращенным оператором извлечения без кавычек, что делает его эквивалентным JSON_UNQUOTE(JSON_EXTRACT(column, path)). Мы могли бы написать предыдущее SELECTутверждение, используя longhand, и получить тот же результат.
Какой метод вы выберете, зависит от личных предпочтений!
Теперь, когда мы убедились, что наше выражение корректно и точно, давайте используем его для создания сгенерированного столбца.
Теперь, когда мы убедились, что наше выражение корректно и точно, давайте используем его для создания сгенерированного столбца.
Код:
ALTER TABLE activity_log ADD COLUMN email VARCHAR(255)
GENERATED ALWAYS as (properties->>"$.request.email");Первая часть ALTERинструкции должна выглядеть очень знакомо, мы добавляем столбец с именем emailи определяем его как VARCHAR(255). Во второй половине инструкции мы объявляем, что столбец сгенерирован и что он всегда должен быть равен результату выражения properties->>"$.request.email".
Мы можем подтвердить, что наш столбец был добавлен, выбрав его, как и любой другой столбец.
Код:
mysql> SELECT id, email FROM activity_log;
+----+-------------------------+
| id | email |
+----+-------------------------+
| 1 | [email protected] |
+----+-------------------------+Вы увидите, что MySQL теперь поддерживает этот столбец для нас. Если бы мы обновили значение JSON, сгенерированное значение столбца также изменилось бы.
Теперь, когда у нас есть сгенерированный столбец, мы можем добавить к нему индекс, как к любому другому столбцу.
Код:
ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;Вот именно! Теперь вы проиндексировали request.email ключ в своем properties столбце JSON. Давайте проверим, что MySQL будет использовать индекс для ускорения запросов, которые фильтруются по электронной почте.
Код:
mysql> EXPLAIN SELECT * FROM activity_log WHERE email = '[email protected]';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: activity_log
partitions: NULL
type: ref
possible_keys: email
key: email
key_len: 768
ref: const
rows: 1
filtered: 100.00
Extra: NULL