背景

MySQL在2015年中發布的5.7.8版本中首次引入了JSON數據類型。自此，它成了一種逃離嚴格列定義的方式，可以存儲各種形狀和大小的JSON文檔，例如審計日志、配置信息、第三方數據包、用戶自定義字段等。

雖然MySQL提供了讀寫JSON數據的函數，但你很快會發現一個顯著的缺失：直接給JSON列建立索引的能力。

在其他數據庫中，直接索引JSON列的最佳方法通常是使用一種叫做廣義倒排索引（Generalized Inverted Index，簡稱GIN）的類型。然而，由于MySQL沒有提供GIN索引，我們無法直接對整個存儲的JSON文檔建立索引。不過不必擔心！MySQL確實為我們提供了一種間接索引存儲在JSON文檔中特定部分的方式。

根據所使用的MySQL版本，有兩個選項可以給JSON建立索引：

• 如果使用MySQL 5.7，需要創建一個中間生成列（Generated Column）。
• 從MySQL 8.0.13開始，可以直接創建函數索引（Functional Index）。

接下來，我們以一個示例表為例，該表用于記錄應用程序中的各種操作日志：

CREATE TABLE `activity_log` (`id` int(11) NOT NULL AUTO_INCREMENT,`properties` json NOT NULL,`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)
)

在該表的properties字段中插入如下結構的JSON文檔：

{"uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502","request": {"email": "little.bobby@tables.com","firstName": "Little","formType": "vehicle-inquiry","lastName": "Bobby","message": "Hello, can you tell me what the specs are for this vehicle?","postcode": "75016","townCity": "Dallas"}
}

在本例中，我們將嘗試索引request對象內的email鍵，這可以讓用戶快速找到由特定人員提交的表單。

方法一：通過“生成列”索引JSON

**生成列（Generated Column）**可以視為計算列、派生列或公式列。它的值是某個表達式的運算結果，而不是直接的數據輸入。表達式可以包含常量值、內置函數或對其他列的引用。表達式的結果必須是定量的（Scalar）且具有確定性（Deterministic）。

由于我們試圖索引properties列中的request.email字段，生成列將使用JSON的解引用（Unquoting Extraction）運算符來提取該值。

首先，運行一個SELECT語句來驗證表達式是否正確:

mysql> SELECT properties->>"$.request.email" FROM activity_log;
+--------------------------------+
| properties->>"$.request.email" |
+--------------------------------+
| little.bobby@tables.com        |
+--------------------------------+

符號->>是解引用運算符，它等價于如下的寫法：

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email"))->   FROM activity_log;
+-----------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email")) |
+-----------------------------------------------------------+
| little.bobby@tables.com                                   |
+-----------------------------------------------------------+

上述兩種寫法，具體使用哪種方式可完全取決于個人偏好。

確認表達式的有效性和準確性后，我們使用它創建一個生成列：

ALTER TABLE activity_log ADD COLUMN email VARCHAR(255)GENERATED ALWAYS as (properties->>"$.request.email");

這條ALTER語句的前半部分非常熟悉，添加了一個名為email的列，并將其定義為VARCHAR(255)類型。而后半部分聲明該列為生成列，并定義它始終等于表達式properties->>"$.request.email"的結果。

我們可以像其他列一樣查詢它，確認生成列已被成功添加：

mysql> SELECT id, email FROM activity_log;
+----+-------------------------+
| id | email                   |
+----+-------------------------+
|  1 | little.bobby@tables.com |
+----+-------------------------+

從結果可以看到，MySQL將動態維護這個列。如果我們更新了JSON數據，生成列的值也會隨之改變。

接下來，我們像其他普通列一樣為這生成列添加索引：

ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;

現在已經成功為JSON中request.email鍵建立了索引。可以通過EXPLAIN驗證索引是否會被用于查詢：

mysql> EXPLAIN SELECT * FROM activity_log WHERE email = 'little.bobby@tables.com';

結果顯示MySQL計劃使用email索引來滿足該查詢。

索引生成列與優化器（Optimizer）

MySQL的優化器是一個強大但神秘的組件。當我們給MySQL下達命令時，它理解的是我們想要什么，而不是我們明確指定如何實現。通常，MySQL會稍微改寫我們的查詢，這通常是一件好事。

對于生成列上的索引，優化器能“透過”不同的訪問模式以確保使用索引。例如，在以下查詢中，我們通過JSON提取運算符訪問數據，而不是直接使用生成的email列：

mysql> EXPLAIN SELECT * FROM activity_log->   WHERE properties->>"$.request.email" = 'little.bobby@tables.com';

結果可以看到優化器仍然使用了email索引。哪怕使用長寫的表達式，也可以看到優化器仍然“穿透”表達式并利用了索引，甚至可以通過SHOW WARNINGS查看優化器改寫后的查詢：

mysql> SHOW WARNINGS;

顯示結果表明查詢被改寫為直接參考了索引的列。

方法二：函數索引（Functional Index）

從MySQL 8.0.13開始，可以跳過創建生成列的中間步驟，直接創建表達式索引（Function Index）。例如：

ALTER TABLE activity_logADD INDEX email ((properties->>"$.request.email")) USING BTREE;

然而，當你嘗試運行上述語句時會遇到錯誤：

ERROR: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

這是因為MySQL自動推斷JSON解引用操作返回LONGTEXT類型，而無法對其直接建立索引。可通過CAST將值轉化為MySQL可索引的數據類型：

ALTER TABLE activity_logADD INDEX email ((CAST(properties->>"$.request.email" AS CHAR(255)))) USING BTREE;

此外還需要解決字符集不匹配的問題，需要顯式設置排序規則為utf8mb4_bin：

ALTER TABLE activity_logADD INDEX email ((CAST(properties->>"$.request.email" AS CHAR(255)) COLLATE utf8mb4_bin)) USING BTREE;

運行EXPLAIN后可以確認函數索引已成功被使用。

總結

盡管MySQL無法直接對JSON列建立索引，但通過生成列和函數索引的方式間接索引特定字段能夠滿足絕大多數場景。同時這種方式不僅適用于JSON，還適用于其它復雜或難以索引的模式。