背景

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

雖然MySQL提供了讀寫JSON數(shù)據(jù)的函數(shù)，但你很快會發(fā)現(xiàn)一個顯著的缺失：直接給JSON列建立索引的能力。

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

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

• 如果使用MySQL 5.7，需要創(chuàng)建一個中間生成列（Generated Column） 。
• 從MySQL 8.0.13開始，可以直接創(chuàng)建函數(shù)索引（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對象內(nèi)的email鍵，這可以讓用戶快速找到由特定人員提交的表單。

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

生成列（Generated Column） 可以視為計算列、派生列或公式列。它的值是某個表達式的運算結果，而不是直接的數(shù)據(jù)輸入。表達式可以包含常量值、內(nèi)置函數(shù)或?qū)ζ渌械囊?。表達式的結果必須是定量的（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                                   |
+-----------------------------------------------------------+

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

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

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將動態(tài)維護這個列。如果我們更新了JSON數(shù)據(jù)，生成列的值也會隨之改變。

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

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

現(xiàn)在已經(jīng)成功為JSON中request.email鍵建立了索引?？梢酝ㄟ^EXPLAIN驗證索引是否會被用于查詢：

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

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

索引生成列與優(yōu)化器（Optimizer）

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

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

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

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

mysql> SHOW WARNINGS;

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

方法二：函數(shù)索引（Functional Index）

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

ALTER TABLE activity_log
  ADD 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將值轉(zhuǎn)化為MySQL可索引的數(shù)據(jù)類型：

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

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

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

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

總結

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

到此這篇關于為MySQL中的JSON字段設置索引的兩種方法的文章就介紹到這了,更多相關MySQL JSON設置索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論