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

В середине 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 Analyst (12 месяцев)
• Профессия Data Scientist (24 месяца)

Чтобы посмотреть все курсы, кликните по баннеру: