WeHelp
透過 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)