MySQL JSON 數據類型是否不利於數據檢索的性能？

假設我有一個custom_properties用於媒體表的 MySQL JSON 數據類型：

儲存在列中的 json 數據的範例custom_properties可能是：

{
"company_id": 1, 
"uploaded_by": "Name", 
"document_type": "Policy", 
"policy_signed_date": "04/04/2018"
}

在我的 PHP Laravel 應用程序中，我會執行以下操作：

$media = Media::where('custom_properties->company_id', Auth::user()->company_id)->orderBy('created_at', 'DESC')->get();

這將獲取屬於公司 1 的所有媒體項目。

我的問題是，假設我們有 100 萬條媒體記錄，就性能而言，這會是獲取記錄的壞方法嗎？誰能解釋一下 MySQL 如何索引 JSON 數據類型？

來自MySQL 官方文件：

儲存在 JSON 列中的 JSON 文件被轉換為允許對文件元素進行快速讀取訪問的內部格式。當伺服器稍後必須讀取以這種二進制格式儲存的 JSON 值時，不需要從文本表示中解析該值。二進制格式的結構使伺服器能夠直接通過鍵或數組索引查找子對像或嵌套值，而無需讀取文件中它們之前或之後的所有值。

我認為我們可以放心地假設性能會比使用正常列更差。

索引 JSON 屬性的方法是為您想要的特定屬性 (company_id) 創建一個虛擬生成列，然后索引虛擬列，並使用該列過濾您的查詢。

所以我用大數據做了一些測試；我創建了這兩個表：

CREATE TABLE `json` (
 `data` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `users` (
 `name` varchar(255) DEFAULT NULL,
 `relation_id` int DEFAULT NULL,
 `description` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

第一個表將其數據儲存在 JSON 欄位中，另一個使用列。

我用1,000,000 條記錄填滿了每個表。

我執行了這兩個查詢：

select * from users where relation_id > 100 order by relation_id limit 100


select json_extract(data, '$.name') as name,
      json_extract(data, '$.relation_id') as relation_id
      from json
       where json_extract(data, '$.relation_id') > 100 order by relation_id
       limit 100

第一個查詢，它查詢不使用 JSON 的表，結果是

[2022-03-02 00:37:46] 100 rows retrieved starting from 1 in 854 ms (execution: 838 ms, fetching: 16 ms)

使用 JSON的第二個查詢導致：

[2022-03-02 00:42:52] 100 rows retrieved starting from 1 in 1 s 10 ms (execution: 994 ms, fetching: 16 ms)

所以很明顯，在簡單的情況下並沒有太大的不同。

現在嘗試執行聚合函式，這兩個查詢：

select name, relation_id, description , max(relation_id) as max_relation
from users
where relation_id > 100
group by name, relation_id, description
order by relation_id
limit 100; # executed in 5 s 3 ms

select data -> '$.name'             as name,
      data -> '$.relation_id'      as relation_id,
      data -> '$.description'      as description,
      max(data -> '$.relation_id') as max_relation
from json
where json_extract(data, '$.relation_id') > 100
group by data -> '$.name', data -> '$.relation_id', data -> '$.description'
order by relation_id
limit 100 # executed in 6 s 238 ms

第一個查詢結果：

[2022-03-02 15:23:13] 100 rows retrieved starting from 1 in 5 s 21 ms (execution: 5 s 3 ms, fetching: 18 ms)

第二個查詢結果：

[2022-03-02 15:23:25] 100 rows retrieved starting from 1 in 6 s 238 ms (execution: 6 s 218 ms, fetching: 20 ms)

所以看起來在使用聚合函式時存在明顯的性能差異。

引用自：https://dba.stackexchange.com/questions/203037