В середине 2015 года, в MySQL 5.7.8 появился тип данных JSON. С тех пор он применяется, чтобы избегать жёстких определений столбцов и сохранять документы JSON всех форм и размеров: логи аудита, параметры конфигурации, сторонние полезные нагрузки, пользовательские поля и др. Подробности — к старту нашего курса по анализу данных.
Хотя в MySQL есть функции чтения и записи данных JSON, вы быстро обнаружите, что явно не хватает прямого индексирования столбцов с JSON.
В других базах данных в качестве способа прямого индексирования столбца JSON обычно применяется обобщённый обратный индекс, или GIN-индекс (Generalized INverted index). В MySQL GIN-индексов нет, поэтому индексировать весь сохранённый документ JSON напрямую нельзя, зато возможно косвенно индексировать части JSON.
В зависимости от версии MySQL есть два варианта. В MySQL 5.7 нужно создавать промежуточный сгенерированный столбец, но с версии MySQL 8.0.13 можно создавать функциональный индекс напрямую.
Начнём с таблицы логирования действий в приложении:
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`)
)
В эту таблицу вставим такой JSON:
{
"uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502",
"request": {
"email": "little.bobby@tables.com",
"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 в столбце свойств, извлекать значения в сгенерированном столбце будем оператором извлечения JSON без кавычек.
Чтобы проверить правильность сформированного выражения, сначала запустим оператор SELECT и посмотрим результаты:
mysql> SELECT properties->>"$.request.email" FROM activity_log;
+--------------------------------+
| properties->>"$.request.email" |
+--------------------------------+
| little.bobby@tables.com |
+--------------------------------+->> — это сокращённый оператор извлечения значений без кавычек, его эквивалент — JSON_UNQUOTE(JSON_EXTRACT(column, path)). Если написать этот оператор SELECT в полной версии, результат будет тот же:
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email"))
-> FROM activity_log;
+-----------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email")) |
+-----------------------------------------------------------+
| little.bobby@tables.com |
+-----------------------------------------------------------+После подтверждения корректности и точности выражения сгенерируем столбец с его помощью:
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 | little.bobby@tables.com |
+----+-------------------------+Видно, что теперь этот столбец поддерживается MySQL. Если обновить значение JSON, обновится и значение сгенерированного столбца.
Теперь добавим сгенерированному столбцу индекс, как у любого другого столбца:
ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;Вот и всё. Ключ request.email в столбце свойств JSON индексирован. Теперь проверим, что MySQL использует этот индекс для ускорения запросов фильтрации по электронной почте:
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
possible_keys: email
key: email
key_len: 768
[...]: [...]MySQL сообщает, что для выполнения этого запроса планирует использовать индекс электронной почты.
Индексы сгенерированных столбцов и оптимизатор
Оптимизатор MySQL — мощная и загадочная сущность. Оператор MySQL сообщает, что нам нужно, но не как это получить. Часто MySQL слегка переписывает запрос, и это хорошо! На то, чтобы сделать эффективный оптимизатор, ушли тысячи часов и десятки лет.
С индексами в сгенерированных столбцах оптимизатор может «видеть» через различные шаблоны доступа, обеспечивая применение основного индекса.
Мы определили индекс email. Это сгенерированный столбец на основе выражения properties->>"$.request.email". И уже доказали, что индекс используется при выполнении запроса в столбце почты. Но вот что ещё интереснее: оптимизатор достаточно умён и выручает нас, когда мы забываем запросить столбец email!
Вместо того чтобы запрашивать сгенерированный столбец по имени, мы используем сокращённый оператор извлечения значений JSON. Ниже некоторые строки в EXPLAIN опущены для краткости:
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
possible_keys: email
key: email
key_len: 768
[...]: [...]
Хотя явного обращения к столбцу по имени не было, оптимизатор понимает, что в сгенерированном на основе того выражения столбце есть индекс, и предпочитает использовать его. Спасибо оптимизатору! Убедиться в этом можно и в полной версии:
mysql> EXPLAIN SELECT * from activity_log WHERE -> JSON_UNQUOTE( -> JSON_EXTRACT(properties, "$.request.email") -> ) = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1 possible_keys: email key: email key_len: 768 [...]: [...]Опять же оптимизатор считывает выражение и использует индекс почты.
Хотите убедиться ещё раз? Взгляните на то, что происходит в оптимизаторе при запуске SHOW WARNINGS после прошлого оператора EXPLAIN, и на переписанный запрос:
mysql> SHOW WARNINGS;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `activity_log`.`id` AS `id`,`activity_log`.`properties` AS `properties`,`activity_log`.`created_at` AS `created_at`,`activity_log`.`email` AS `email` from `activity_log` where (`activity_log`.`email` = 'little.bobby@tables.com')Посмотрите внимательно: оптимизатор переписал запрос и изменил сравнение на равенство для ссылки на индексированный столбец. Это особ��нно полезно, если нет возможности контролировать шаблон доступа, потому что запрос выдаётся из стороннего пакета кодовой базы или вы не можете изменить эту часть кода по какой-то другой причине.
Если основное выражение соответствует не очень близко, индекс в оптимизаторе не применится, поэтому будьте осторожны при создании генерируемого столбца. Подробнее об использовании в оптимизаторе индексов сгенерированных столбцов см. в документации MySQL.
Функциональные индексы
С версии MySQL 8.0.13 промежуточный этап сгенерированного столбца можно пропускать и создавать «функциональные индексы». В документации MySQL они называются функциональными ключевыми частями.
Функциональный индекс — это индекс по выражению, а не столбцу. Он очень похож на сгенерированный столбец, так как реализован с помощью скрытого сгенерированного столбца! Генерируемый столбец создавать больше не нужно, но он создаётся.
В функциональных индексах есть ряд нюансов, особенно при их использовании для JSON.
Неплохо было бы создать индекс JSON вот так:
ALTER TABLE activity_log ADD INDEX email ((properties->>"$.request.email")) USING BTREE;Но здесь вы получите неприятную ошибку:
Query 1 ERROR: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.Что же здесь происходит? В примерах выше мы сами создавали генерируемый столбец и объявляли его как VARCHAR(255), легко индексируемый в MySQL.
А при использовании функционального индекса этот столбец создаётся на основе типа данных, выводимых в MySQL. В JSON_UNQUOTE возвращается неиндексируемое значение LONGTEXT.
К счастью, в сообщении об ошибке указывается правильное направление: нужно привести значение к типу, отличному от LONGTEXT. При приведении к типу с помощью функции CHAR в MySQL даётся указание вывести тип данных VARCHAR:
ALTER TABLE activity_log
ADD INDEX email ((CAST(properties->>"$.request.email" as CHAR(255)))) USING BTREE;Добавив индекс, запустим теперь EXPLAIN:
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: activity_log
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using whereК сожалению, индекс вообще не учитывается, так что проблемы ещё не ушли.
Если не указано иное, приведение значения к строке устанавливает сопоставление на utf8mb4_0900_ai_ci. С другой стороны, функции извлечения JSON возвращают строку с параметром сортировки utf8mb4_bin. В этом и проблема. Параметры сортировки между выражением запроса и сохранённым индексом не совпадают, поэтому новый функциональный индекс не используется.
Финальный этап состоит в том, чтобы явно задать сопоставление приведения к utf8mb4_bin:
ALTER TABLE activity_log
ADD INDEX email ((
CAST(properties->>"$.request.email" as CHAR(255)) COLLATE utf8mb4_bin
)) USING BTREE;Снова запускаем предыдущий EXPLAIN и видим, что наконец можно использовать функциональный индекс:
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
possible_keys: email
key: email
key_len: 1023
[...]: [...]Очевидно, применение функциональных индексов сопряжено с рядом проблем, часть из которых явные и простые в отладке, а для решения других нужно чуть покопаться в документации.
И помните: под капотом у функциональных индексов — скрытые генерируемые столбцы. Хотите управлять генерируемыми столбцами сами (даже в версии MySQL 8.0.13 и новее)? Это вполне разумно.
Хотя прямое индексирование JSON может быть недоступно в MySQL, косвенным индексированием определённых ключей можно охватить большинство случаев использования.
Но не останавливайтесь только на JSON! Использовать генерируемые столбцы и функциональные индексы можно во всех типах распространённых, трудно индексируемых паттернах.
Поможем разобраться с SQL, чтобы вы прокачали карьеру или стали востребованным IT-специалистом:
Чтобы посмотреть все курсы, кликните по баннеру:
- Профессия Data Scientist
- Профессия Data Analyst
- Курс «Математика для Data Science»
- Курс «Математика и Machine Learning для Data Science»
- Курс по Data Engineering
- Курс «Machine Learning и Deep Learning»
- Курс по Machine Learning
Python,
- Профессия
Fullstack-разработчик на Python - Курс «Python для
веб-разработки » - Профессия
Frontend-разработчик - Профессия
Веб-разработчик
Мобильная разработка
Java и C#
- Профессия
Java-разработчик - Профессия
QA-инженер на JAVA - Профессия C#-разработчик
- Профессия Разработчик игр на Unity
От основ — в глубину
А также
