Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье я собираюсь объяснить, как мы можем индексировать столбцы JSON при использовании MySQL.
В то время как другие системы реляционных баз данных предоставляют индексы GIN (Обобщенный инвертированный индекс), MySQL позволяет вам индексировать виртуальный столбец, который отражает выражение пути JSON, которое вы хотите индексировать.
Таблица базы данных
Давайте предположим, что у нас есть следующая база данных книга таблица:
| id | isbn | properties |
|----|----------------|----------------------------------------------------------------|
| 1 | 978-9730228236 |{ |
| | | "price":44.99, |
| | | "title":"High-Performance Java Persistence", |
| | | "author":"Vlad Mihalcea", |
| | | "reviews":[ |
| | | { |
| | | "date":"2017-11-14", |
| | | "rating":5, |
| | | "review":"Excellent book to understand Java Persistence", |
| | | "reviewer":"Cristiano" |
| | | }, |
| | | { |
| | | "date":"2019-01-27", |
| | | "rating":5, |
| | | "review":"The best JPA ORM book out there", |
| | | "reviewer":"T.W" |
| | | }, |
| | | { |
| | | "date":"2016-12-24", |
| | | "rating":4, |
| | | "review":"The most informative book", |
| | | "reviewer":"Shaikh" |
| | | } |
| | | ], |
| | | "publisher":"Amazon" |
| | |} |
|----|----------------|----------------------------------------------------------------|
Тип столбца свойства – json , поэтому мы можем хранить объекты JSON как свойства книги.
Запрос столбцов MySQL JSON без индекса
Если мы попытаемся отфильтровать одну запись по ее связанному заголовку атрибуту, расположенному внутри свойств JSONObject:
SELECT isbn FROM book WHERE properties ->> "$.title" = 'High-Performance Java Persistence'
Сканирование всей таблицы будет использоваться для фильтрации всех записей, найденных в таблице книга :
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "50.25"
},
"table": {
"table_name": "book",
"access_type": "ALL",
"rows_examined_per_scan": 500,
"rows_produced_per_join": 500,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "50.00",
"prefix_cost": "50.25",
"data_read_per_join": "140K"
},
"used_columns": [
"isbn",
"properties"
],
"attached_condition": "(
json_unquote(
json_extract(
`high_performance_sql`.`book`.`properties`,'$.title'
)
) = 'High-Performance Java Persistence'
)"
}
}
}
Тип доступа ВСЕ означает, что все страницы были отсканированы, что также подтверждается атрибутом rows_examined_per_scan , поскольку в таблице книга у нас всего 500 записей.
Добавьте виртуальный столбец MySQL для индексирования выражения пути JSON
В MySQL единственный способ индексировать выражение JSONPath-это добавить виртуальный столбец, который отражает рассматриваемое выражение пути, и создать индекс для виртуального столбца.
По этой причине мы собираемся добавить следующий заголовок виртуальный столбец в нашу книгу таблицу:
ALTER TABLE book ADD COLUMN title VARCHAR(50) GENERATED ALWAYS AS ( properties ->> \"$.title\" )
Как вы можете видеть, столбец title сопоставляется с выражением $.title пути в столбце свойства JSON.
Далее мы добавим индекс в столбец title , например:
CREATE INDEX book_title_idx ON book (title)
И при повторном выполнении предыдущего SQL-запроса мы теперь получаем следующий план выполнения:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "book",
"access_type": "ref",
"possible_keys": [
"book_title_idx"
],
"key": "book_title_idx",
"used_key_parts": [
"title"
],
"key_length": "203",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "288"
},
"used_columns": [
"isbn",
"properties",
"title"
]
}
}
}
Не то, чтобы атрибут ключ ссылался на только что созданный индекс book_title_idx , что означает, что SQL-запрос не использует индекс для фильтрации записей book , что также подтверждается атрибутом rows_examined_per_scan , поскольку была отсканирована одна строка индекса.
Обновление индексированного атрибута JSON
Если вы обновите индексированный атрибут JSON:
UPDATE
book
SET
properties = JSON_SET(
properties,
'$.title',
'High-Performance Java Persistence, 2nd edition'
)
WHERE
isbn = '978-9730228236'
Вы увидите, что виртуальный столбец title соответствующим образом обновлен:
| id | isbn | properties | title |
|----|----------------|----------------------------------------------------------------|------------------------------------------------|
| 1 | 978-9730228236 |{ | High-Performance Java Persistence, 2nd edition |
| | | "price":44.99, | |
| | | "title":"High-Performance Java Persistence, 2nd edition", | |
| | | "author":"Vlad Mihalcea", | |
| | | "reviews":[ | |
| | | { | |
| | | "date":"2017-11-14", | |
| | | "rating":5, | |
| | | "review":"Excellent book to understand Java Persistence", | |
| | | "reviewer":"Cristiano" | |
| | | }, | |
| | | { | |
| | | "date":"2019-01-27", | |
| | | "rating":5, | |
| | | "review":"The best JPA ORM book out there", | |
| | | "reviewer":"T.W" | |
| | | }, | |
| | | { | |
| | | "date":"2016-12-24", | |
| | | "rating":4, | |
| | | "review":"The most informative book", | |
| | | "reviewer":"Shaikh" | |
| | | } | |
| | | ], | |
| | | "publisher":"Amazon" | |
| | |} | |
|----|----------------|----------------------------------------------------------------|------------------------------------------------|
Однако, хотя вы можете изменить индексированный атрибут JSON, вам не разрешается изменять виртуальный столбец напрямую. Если вы попытаетесь это сделать, MySQL выдаст исключение.
Вывод
Типы столбцов JSON могут быть очень полезны, особенно если вы внедряете журнал аудита .
Хотя добавление индекса GIN в столбцы JSON в MySQL запрещено, вы можете определить виртуальный столбец и добавить к нему индекс.
Однако вам необходимо убедиться, что атрибут JSON, который вы используете для индексирования, является высокоселективным, так как в противном случае компонент database engine может не использовать индекс при создании плана выполнения SQL.