使用 MySQL 8.0 為 Schemaless 定義 JSON 索引

我們正在使用 MySQL 8.0，需要實現無模式功能，我們允許供應商創建模組（對象名稱）並向其添加列（整數、字元串、貨幣）。供應商可以在定義模組後添加對象。

CREATE TABLE `ObjectName` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(45) DEFAULT NULL,
 `vendor_Id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`),
 KEY `vendor_Id_idx` (`vendor_Id`),
 CONSTRAINT `vendor_Id` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `ObjectNameColumn` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `objectname_id` int(11) DEFAULT NULL,
 `name` varchar(45) DEFAULT NULL,
 `dataType` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `objectnamecolumn_id_objectname_id_idx` (`objectname_id`),
 CONSTRAINT `objectnamecolumn_id_objectname_id` FOREIGN KEY (`objectname_id`) 
REFERENCES `ObjectName` (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci



CREATE TABLE `object_json` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `attributes` json DEFAULT NULL,
 `objectname_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `objectname_id_objectnameidjson` (`objectname_id`),
 CONSTRAINT `objectname_id_objectnameidjson` FOREIGN KEY (`objectname_id`) 
REFERENCES `ObjectName` (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=400002 DEFAULT CHARSET=utf8mb4 
   COLLATE=utf8mb4_0900_ai_ci

對象名：

id | name     | vendor_id
1  | Contact  |   1
2  | Account  |   1
3  | Contact  |   2
4  | Account  |   2

對象名稱列：

id | objectname_id | name              | dataType
1 |      1        | height            |  int
2 |      1        | weight            |  int
3 |      1        | age               |  int
4 |      1        | name              |  string
5 |      1        | mobile_number     |  string
6 |      2        | annual_revenue    |  int
7 |      2        | establised_year   |  int
8 |      2        | numbe_of_employees|  int
9 |      2        | address           |  string
10 |      2        | name              |  string

object_json：

   id | objectname_id | attributes
    1 | 1             |{"age": 3, "name": "jiten1", "height": 1, "weight": 2, "mobile_number": null}
    2 | 2             |{"name": "xyz", "address": null, "annual_revenue": 1, "established_year": 2, "number_of_employees": 3}

---

現在，假設我要查找滿足以下條件的供應商 1 的聯繫人：

height between 200 and 400
weight between 400 and 800
age between 400 and 800

---

SELECT
  object_json.id AS id,
  attributes 
FROM
  object_json 
where
  object_json.objectname_id = 1 
  AND attributes -> " $ .height" between 200 and 400 
  AND attributes -> " $ .weight" between 400 and 800 
  AND attributes -> " $ .age" between 400 and 800

供應商可以使用作為 JSON 欄位的過濾器執行 Select 操作。當我們使用 4 個 ObjectNames 的 100,000 條記錄執行這些查詢時

解釋的輸出：

{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "21938.55"
   },
   "table": {
     "table_name": "object_json",
     "access_type": "ref",
     "possible_keys": [
       "objectname_id_objectnameidjson"
     ],
     "key": "objectname_id_objectnameidjson",
     "used_key_parts": [
       "objectname_id"
     ],
     "key_length": "4",
     "ref": [
       "const"
     ],
     "rows_examined_per_scan": 192228,
     "rows_produced_per_join": 192228,
     "filtered": "100.00",
     "cost_info": {
       "read_cost": "2715.75",
       "eval_cost": "19222.80",
       "prefix_cost": "21938.55",
       "data_read_per_join": "5M"
     },
     "used_columns": [
       "id",
       "attributes",
       "objectname_id"
     ],
     "attached_condition": "((json_extract(`test`.`object_json`.`attributes`,'$.height') between 200 and 400) and (json_extract(`test`.`object_json`.`attributes`,'$.weight') between 400 and 800) and (json_extract(`test`.`object_json`.`attributes`,'$.age') between 400 and 800))"
   }
 }
}

我們希望使用索引來加速查詢。我們發現有一個輔助索引選項，方法是添加虛擬列，然後在 JSON 類型的情況下在虛擬列上定義索引。

1. 由於供應商可以搜尋 ObjectName 的所有屬性 –> 在這種情況下我們如何定義索引
2. 此外，在我們的例子中，由於我們不知道供應商將添加什麼樣的 ObjectName(entities) –> 而且我認為在執行時在表上定義索引不是一個好主意，該方法確實似乎不可擴展。當我們不知道實體的性質時，我們如何解決這個問題。

我建議您收集使用者使用的查詢。定期瀏覽它們以查看最常過濾哪些 json“列”並建構索引（使用虛擬等）。

確保還查找過濾的列對。在這種情況下，請務必先放置=列。在您的範例中，每個過濾器都是一個“範圍”，因此建構“複合”索引沒有任何好處。

我在這裡談了一些。

抱歉，由於評論的長度，不得不將此作為答案發布。

該連結非常有幫助。謝謝瑞克。來自網站“該解決方案對所有 EAV 內容使用一張表。這些列包括可搜尋欄位加上一個 TEXT/BLOB。可搜​​索欄位已適當聲明（INT、TIMESTAMP 等）。BLOB 包含所有額外內容的 JSON 編碼領域。”

因此，如果我理解正確，我們可以將上述實現為：假設每個實體有 100 個列/欄位的限制，並且我們計劃支持文本、整數、小數、日期、日期時間欄位。所以我們的實體表將有：

1. Entity_Id
2. JSON（而不是我們使用 MySQL 8.0 的 blob）

從 Col 3 到 Col 102：所以我們將這些數據類型分為 5 組：所以：

第 3 欄 - 第 22 欄 -詮釋

第 23 列 - 第 42 列 -字元串

第 43 欄 - 第 62 欄 -日期

Col62 - Col81 -日期時間

Col82 - Col102 -十進制

因此，假設我們有 Entity Contactwhere 可搜尋欄位(age, name)- 我們將它們儲存為：

Col 2  |  Col 3    |  Col 23
{"age": 3, "name": "jiten1", "height": 1, "weight": 2, "mobile_number": null}   | 3 |  jiten1

所有列的其餘部分將為空。

Account可搜尋欄位（annual_revenue、established_year、name）的實體將它們儲存為：

Col 2   |  Col 3   |  Col 4   |  Col 23
{"name": "xyz", "address": null, "annual_revenue": 1, "established_year": 2, "number_of_employees": 3}   | 1  | 2   |  xyz

所有列的其餘部分將為空。

表結構將類似於：

Col 1(Entity_Id) |  Col 2(JSON) | Col 3 - Col 22 Int | Col 23 - Col 42 String | Col 43 - Col 62 Date | Col 62 - Col 81 Date Time | Col 82- Col 102 Decimal

同樣對於每個實體，我們將有一個屬性/欄位的映射 –> 列號

在 Entity 的情況下Contact，我們在**(col 3, col 23)上創建索引**

在 Entity 的情況下Account，我們在**(col 3, col4, col 23)上創建索引**

我們的解釋正確嗎？在可搜尋欄位的情況下，我們不會在 JSON 和 Column 中重複該屬性（儘管在我的範例中）

此外，每個表有64 個二級索引的限制。因此，當我們的索引空間不足時，我們創建一個類似的表，然後根據某些條件相應地路由我們的查詢。

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