透過 JSON_CONTAINS 查詢資料,是否能使用 INDEX 加速查詢?
2024-10-03 17:19:09
上次分享了使用 MySQL/ MariaDB 內建的 JSON_CONTAINS 方法來查詢 JSON 欄位值。
[前情提要 : MySQL/ MariaDB 之 JSON 欄位值查詢](https://medium.com/@HeronCheng/mysql-mariadb-%E4%B9%8B-json-%E6%AC%84%E4%BD%8D%E5%80%BC%E6%9F%A5%E8%A9%A2-f098b9768490)
文章出來之後有幸收到彭彭老師的留言指點,開始研究使用 JSON_CONTAINS 做 JSON 格式資料內部的查詢,是否能使用 INDEX 加速問題。
---
我們先來看一下 MySQL 的 [官方文件](https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-contains) 裡有關 JSON_CONTAINS 的段落。
內文有提到 ,我們可以透過建立多值索引(multi-valued indexes)的方式來優化 `JSON_CONTAINS()` 的查詢。
多值索引是一種次要索引,可以用在索引 JSON 格式的陣列資料,像下面例子中的 zipcode 就可以建立這種索引。
```
{
"name":"Bob",
"height":173.17,
"age":49,
"zipcode":[94477,94536]
}
```
多值索引和一般索引不一樣的地方是:
* 一般索引通常是一筆索引對應一筆數據。
* 多值索引指的是多筆索引對應到同一筆數據。以剛剛的例子來說,一個定義在 zipcode 欄位上的多值索引會對 zipcode 陣列中的每一個值都產生一筆索引。
---
重點來了,我想大家一定跟我一樣好奇,加索引之前和之後,查詢的速度會差多少?
首先,先建立一張測試用的表格。
```sql
create table student(
id int AUTO_INCREMENT PRIMARY KEY,
detail json
);
```
接著新增資料到表內。
```sql
insert into student(detail)
values
('{"name":"Amy","height":103.17,"age":99,"zipcode":[50336,88782]}');
```
為了怕資料量不足,造成建立 INDEX 前後的資料查詢速度落差不大,因此為了快速新增測試資料,我請 chatgpt 給我生成隨機資料的程式碼,生成資料的 [範例程式碼](https://github.com/HeronCheng/example_code/blob/main/generate_random_user_data.js) 請參考連結,有興趣的話大家也可以自行嘗試看看,最後我總共新增了約 30 萬筆資料到表內。
接下來,先來試看看加索引之前需要花費多少時間查詢,用以下語句查詢實測,需要花費大約 0.6 – 0.8 秒左右的時間。
```sql
select * from student
where JSON_CONTAINS(detail->'$.zipCode[*]', '50854');
```
然後透過下面語法建立索引。
```sql
-- 寫法一
create INDEX zips ON student((CAST(detail->'$.zipCode[*]' AS unsigned ARRAY)));
-- 寫法二
ALTER TABLE student
ADD INDEX zips ((CAST(detail->'$.zipCode[*]' AS UNSIGNED ARRAY)));
```
建立完索引後,再重複上面的查詢語句,只要花費不到 0.001 秒的時間就完成查詢,結論我們確實可以透過建立 INDEX 來加速 JSON_CONTAINS 的查詢速度。
---
前面有提到說,可以透過建立多值索引來優化JSON_CONTAINS()的查詢,但是多值索引只可以用在索引 JSON 格式的陣列資料。
那如果我們是想針對 JSON 格式的物件資料進行索引呢?
例如想要針對 detail 裡面的身高或年齡建立索引。
```
| id | detail |
| -- | ----------------------------------------------------------------------- |
| 1 | {"name":"Amy","height":103.17,"age":99,"zipcode":[50336,88782]} |
| 2 | {"name":"Kevin","height":103,"age":99,"zipCode":[43067]} |
| 3 | {"name":"Frank","height":141.58,"age":62,"zipCode":[7895,89035,61591]} |
```
先講結論,一樣可以透過建立 INDEX 的方式加速查詢,但實測後發現要改用 `JSON_EXTRACT()` 查詢速度才有變快。
註: `JSON_CONTAINS()` 會依照是否找到資料返回 1 或 0,而 `JSON_EXTRACT()` 則是返回找到的資料。
```sql
select * from student
where JSON_EXTRACT(detail, '$.height') = 183.5;
```
上述查詢語句約花費 0.2 – 0.3 秒之間。
```sql
-- 寫法一
CREATE INDEX idx_height ON student ((CAST(detail->'$.height' AS DECIMAL(10, 2))));
-- 寫法二
ALTER TABLE student
ADD INDEX idx_height ((CAST(detail->'$.height' AS DECIMAL(10, 2))));
```
建立完索引後,再進行一次查詢,一樣只要花費不到 0.001秒的時間。
---
補充說明一
```sql
select * from student
where JSON_EXTRACT(detail, '$.height') = 183;
```
如果要查詢的資料是整數格式,則用上面建立索引的寫法,查詢速度並不會加快,要改成下面寫法才行。
```sql
-- 寫法一
CREATE INDEX idx_height ON student ((CAST(detail->'$.height' AS UNSIGNED)));
-- 寫法二
ALTER TABLE student
ADD INDEX idx_height ((CAST(detail->'$.height' AS UNSIGNED)));
```
---
補充說明二
其實以下兩種寫法作用是相等的,用寫法一建立索引,然後用寫法二去查詢資料,一樣會有 INDEX 加速的效果。
```sql
-- 寫法一
JSON_EXTRACT(detail, "$.height")
-- 寫法二
detail->'$.height'
```
---
補充說明三
要確認使用的查詢語句是否有使用 INDEX 加速,可以在查詢語句前面加上 EXPLAIN。
```sql
EXPLAIN select * from student
where JSON_EXTRACT(detail, '$.height') = 183;
-- 沒有使用 INDEX 加速
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 298283 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
-- 有使用 INDEX 加速
+----+-------------+----------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_height | idx_height | 9 | cosnt | 1960 | 100.00 | |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
```
以上就是本次的分享,如果有錯誤的地方歡迎留言告訴我~
最後,感謝跟我一起研究這個問題的同事 T 先生。
[文章也同步分享在 Medium。](https://medium.com/@HeronCheng/mysql-mariadb-%E4%B9%8B-json-%E6%AC%84%E4%BD%8D%E5%80%BC%E6%9F%A5%E8%A9%A2-f098b9768490)
點擊複製文章連結
X