背景：
MySQL在2015年中發(fā)布的5.7.8版本中首次引入了JSON數(shù)據(jù)類型。自此，它成了一種逃離嚴(yán)格列定義的方式，可以存儲(chǔ)各種形狀和大小的JSON文檔，例如審計(jì)日志、配置信息、第三方數(shù)據(jù)包、用戶自定義字段等。
雖然MySQL提供了讀寫JSON數(shù)據(jù)的函數(shù)，但你很快會(huì)發(fā)現(xiàn)一個(gè)顯著的缺失：直接給JSON列建立索引的能力。
在其他數(shù)據(jù)庫(kù)中，直接索引JSON列的最佳方法通常是使用一種叫做廣義倒排索引（Generalized Inverted Index，簡(jiǎn)稱GIN）的類型。
然而，由于MySQL沒有提供GIN索引，我們無(wú)法直接對(duì)整個(gè)存儲(chǔ)的JSON文檔建立索引。不過不必?fù)?dān)心！MySQL確實(shí)為我們提供了一種間接索引存儲(chǔ)在JSON文檔中特定部分的方式。
根據(jù)所使用的MySQL版本，有兩個(gè)選項(xiàng)可以給JSON建立索引：
接下來(lái)，我們以一個(gè)示例表為例，該表用于記錄應(yīng)用程序中的各種操作日志：
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字段中插入如下結(jié)構(gòu)的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對(duì)象內(nèi)的email鍵，這可以讓用戶快速找到由特定人員提交的表單。
方法一：通過“生成列”索引JSON
生成列（Generated Column）可以視為計(jì)算列、派生列或公式列。它的值是某個(gè)表達(dá)式的運(yùn)算結(jié)果，而不是直接的數(shù)據(jù)輸入。
表達(dá)式可以包含常量值、內(nèi)置函數(shù)或?qū)ζ渌械囊谩１磉_(dá)式的結(jié)果必須是定量的（Scalar）且具有確定性（Deterministic）。
由于我們?cè)噲D索引properties列中的request.email字段，生成列將使用JSON的解引用（Unquoting Extraction）運(yùn)算符來(lái)提取該值。
首先，運(yùn)行一個(gè)SELECT語(yǔ)句來(lái)驗(yàn)證表達(dá)式是否正確:
mysql> SELECT properties->>"$.request.email" FROM activity_log;
+--------------------------------+
| properties->>"$.request.email" |
+--------------------------------+
| little.bobby@tables.com |
+--------------------------------+
符號(hào)->>是解引用運(yùn)算符，它等價(jià)于如下的寫法：
?mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email"))
-> FROM activity_log;
+-----------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email")) |
+-----------------------------------------------------------+
| little.bobby@tables.com |
+-----------------------------------------------------------+
上述兩種寫法，具體使用哪種方式可完全取決于個(gè)人偏好。
確認(rèn)表達(dá)式的有效性和準(zhǔn)確性后，我們使用它創(chuàng)建一個(gè)生成列：
ALTER TABLE activity_log ADD COLUMN email VARCHAR(255)
GENERATED ALWAYS as (properties->>"$.request.email");
這條ALTER語(yǔ)句的前半部分非常熟悉，添加了一個(gè)名為email的列，并將其定義為VARCHAR(255)類型。
而后半部分聲明該列為生成列，并定義它始終等于表達(dá)式properties->>"$.request.email"的結(jié)果。
我們可以像其他列一樣查詢它，確認(rèn)生成列已被成功添加：
mysql> SELECT id, email FROM activity_log;
+----+-------------------------+
| id | email |
+----+-------------------------+
| 1 | little.bobby@tables.com |
+----+-------------------------+
從結(jié)果可以看到，MySQL將動(dòng)態(tài)維護(hù)這個(gè)列。如果我們更新了JSON數(shù)據(jù)，生成列的值也會(huì)隨之改變。
接下來(lái)，我們像其他普通列一樣為這生成列添加索引：
ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;
現(xiàn)在已經(jīng)成功為JSON中request.email鍵建立了索引?？梢酝ㄟ^EXPLAIN驗(yàn)證索引是否會(huì)被用于查詢：
mysql> EXPLAIN SELECT * FROM activity_log WHERE email = 'little.bobby@tables.com';
結(jié)果顯示MySQL計(jì)劃使用email索引來(lái)滿足該查詢。
索引生成列與優(yōu)化器（Optimizer）
MySQL的優(yōu)化器是一個(gè)強(qiáng)大但神秘的組件。當(dāng)我們給MySQL下達(dá)命令時(shí)，它理解的是我們想要什么，而不是我們明確指定如何實(shí)現(xiàn)。
通常，MySQL會(huì)稍微改寫我們的查詢，這通常是一件好事。
對(duì)于生成列上的索引，優(yōu)化器能“透過”不同的訪問模式以確保使用索引。
例如，在以下查詢中，我們通過JSON提取運(yùn)算符訪問數(shù)據(jù)，而不是直接使用生成的email列：
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
結(jié)果可以看到優(yōu)化器仍然使用了email索引。哪怕使用長(zhǎng)寫的表達(dá)式，也可以看到優(yōu)化器仍然“穿透”表達(dá)式并利用了索引，甚至可以通過SHOW WARNINGS查看優(yōu)化器改寫后的查詢：
顯示結(jié)果表明查詢被改寫為直接參考了索引的列。
![]()
方法二：函數(shù)索引（Functional Index）
從MySQL 8.0.13開始，可以跳過創(chuàng)建生成列的中間步驟，直接創(chuàng)建表達(dá)式索引（Function Index）。例如：
ALTER TABLE activity_log
ADD INDEX email ((properties->>"$.request.email")) USING BTREE;
然而，當(dāng)你嘗試運(yùn)行上述語(yǔ)句時(shí)會(huì)遇到錯(cuò)誤：
ERROR: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.
這是因?yàn)镸ySQL自動(dòng)推斷JSON解引用操作返回LONGTEXT類型，而無(wú)法對(duì)其直接建立索引。可通過CAST將值轉(zhuǎn)化為MySQL可索引的數(shù)據(jù)類型：
ALTER TABLE activity_log
ADD INDEX email ((CAST(properties->>"$.request.email" AS CHAR(255)))) USING BTREE;
此外還需要解決字符集不匹配的問題，需要顯式設(shè)置排序規(guī)則為utf8mb4_bin：
ALTER TABLE activity_log
ADD INDEX email ((
CAST(properties->>"$.request.email" AS CHAR(255)) COLLATE utf8mb4_bin
)) USING BTREE;
運(yùn)行EXPLAIN后可以確認(rèn)函數(shù)索引已成功被使用。
總結(jié)：
盡管MySQL無(wú)法直接對(duì)JSON列建立索引，但通過生成列和函數(shù)索引的方式間接索引特定字段能夠滿足絕大多數(shù)場(chǎng)景。
同時(shí)這種方式不僅適用于JSON，還適用于其它復(fù)雜或難以索引的模式。
參考文章：原文鏈接?
該文章在 2025/10/20 16:00:15 編輯過
400 186 1886
