摘要：在 MySQL 中，寫一條帶函數(shù)的 WHERE 條件導致全表掃描，是開發(fā)中極為常見的性能陷阱。本文將從索引失效的根因出發(fā)，深入剖析函數(shù)索引的實現(xiàn)機制，對比顯式虛擬列與隱式虛擬列兩種技術(shù)路徑，并結(jié)合 JSON 業(yè)務(wù)場景，給出可落地的索引優(yōu)化方案。讀完本文，你將徹底掌握函數(shù)索引的底層邏輯與最佳實踐。

一、普通索引為何擋不住函數(shù)條件？

普通索引基于原始列值構(gòu)建有序結(jié)構(gòu)。例如，對 register_date 列建立的索引，內(nèi)部存儲的是原始日期值，并按日期順序排列：
2021-01-01 → 2021-01-02 → … → 2021-02-01。

當查詢條件寫成：

SELECT * FROM User
WHERE DATE_FORMAT(register_date, '%Y-%m') = '2021-01';

DATE_FORMAT() 函數(shù)將寄存器日期轉(zhuǎn)換為 2021-01 格式的字符串。然而，普通索引存儲和排序的依據(jù)是原始日期，而不是格式化后的結(jié)果。優(yōu)化器無法直接利用該索引快速定位，只能逐行讀取數(shù)據(jù)、計算后再進行比較，即“索引失效，全表掃描”。

有可能會認為在 register_date 創(chuàng)建了索引，所以所有的 SQL 都可以使用該索引。但索引的本質(zhì)是排序， 索引 idx_register_date 只對 register_date 的數(shù)據(jù)排序，又沒有對DATE_FORMAT(register_date) 排序，因此上述 SQL 無法使用二級索引idx_register_date。

數(shù)據(jù)庫規(guī)范要求“函數(shù)寫在等式右邊”，正是為了避免這種情況?？蓪?SQL 改寫為：

SELECT * FROM User
WHERE register_date BETWEEN '2021-01-01' AND '2021-01-31';

此時 register_date 列保持原始值，能夠命中索引，從而實現(xiàn)高效查詢。

二、函數(shù)索引：從根源解決“函數(shù)導致索引失效”

函數(shù)索引的核心理念十分樸素：既然查詢條件使用了函數(shù)表達式，那就直接為表達式的計算結(jié)果建立索引，并按該結(jié)果排序。從 MySQL 8.0.13 開始，可以使用簡潔語法直接創(chuàng)建函數(shù)索引：

CREATE INDEX idx_date_format ON User (DATE_FORMAT(register_date, '%Y-%m'));

該索引內(nèi)部存儲的是格式化后的值（如 2021-01），并按此順序排列。當再次執(zhí)行 WHERE DATE_FORMAT(register_date, '%Y-%m') = '2021-01' 時，優(yōu)化器能夠直接匹配到索引，無需全表掃描。

從底層來看，MySQL 會自動創(chuàng)建一個隱藏的生成列（隱式虛擬列）來存放表達式結(jié)果，再對該隱藏列建立索引。用戶無需手動維護，表結(jié)構(gòu)對上層應(yīng)用完全透明。

三、顯式虛擬列：手動實現(xiàn)函數(shù)索引（5.7 兼容方案）

在 MySQL 5.7 中，雖然不支持直接創(chuàng)建函數(shù)索引，但可以通過虛擬列（Generated Column）+ 普通索引的組合實現(xiàn)同等效果。虛擬列的值由表達式自動生成，無需手動維護，且默認不占用磁盤空間（VIRTUAL 類型）。

1. 創(chuàng)建虛擬列并建立索引

ALTER TABLE User
ADD COLUMN reg_month VARCHAR(7)
GENERATED ALWAYS AS (DATE_FORMAT(register_date, '%Y-%m')) VIRTUAL;
CREATE INDEX idx_reg_month ON User(reg_month);

此時 reg_month 是一個真實存在的列，可在查詢中直接使用：
SELECT * FROM User WHERE reg_month = '2021-01';

2. 虛擬列類型詳解與語法簡化

MySQL 提供了兩種虛擬列類型，適用于不同場景：

• VIRTUAL（默認）：不占用磁盤空間，僅存儲計算規(guī)則；每次查詢時根據(jù)表達式實時計算值。適用于計算成本低、查詢頻率不高的場景。本文中的 cellphone 列即為此類型，因此可以說“不占用任何存儲空間”。
• STORED：占用磁盤空間，將表達式結(jié)果物理存儲到表中；當原始數(shù)據(jù)發(fā)生修改時自動更新。適用于計算成本高、頻繁查詢的場景，可避免每次重復計算。

在實際編寫 DDL 時，許多關(guān)鍵字可以省略，使語句更加簡潔。以下三種寫法完全等價：

-- 完整寫法（關(guān)鍵字齊全）
reg_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(register_date, '%Y-%m')) VIRTUAL;
-- 省略 GENERATED ALWAYS
reg_month VARCHAR(7) AS (DATE_FORMAT(register_date, '%Y-%m')) VIRTUAL;
-- 最簡寫法（連 VIRTUAL 也省略，因為它是默認值）
reg_month VARCHAR(7) AS (DATE_FORMAT(register_date, '%Y-%m'));

需要特別注意：AS (表達式) 是虛擬列的核心標識，絕不可省略。GENERATED ALWAYS 僅起語義裝飾作用，不留亦可。日常開發(fā)中推薦使用最簡寫法，保持 DDL 清晰易讀。

3. 顯式虛擬列與函數(shù)索引本質(zhì)一致

二者都是將表達式的計算結(jié)果固化下來，并以此為基礎(chǔ)構(gòu)建有序索引，從而讓函數(shù)查詢能夠利用索引執(zhí)行。區(qū)別僅在于：

• 顯式虛擬列：列對用戶可見，可被直接引用，適合需要反復使用或作為查詢條件的表達式。
• 函數(shù)索引：列對用戶隱藏，使用更簡潔，但無法在 SELECT 中直接引用該虛擬列。

四、隱式虛擬列：函數(shù)索引背后的隱藏列

當執(zhí)行 CREATE INDEX idx ON User (loginInfo->>"$.cellphone") 這類函數(shù)索引語句時，MySQL 會自動在底層創(chuàng)建一個對用戶不可見的虛擬列，稱為隱式虛擬列。

其特性如下：

• 完全隱藏：無法通過 DESC 或 SELECT * 看到，只為索引服務(wù)。
• 自動維護：寫入數(shù)據(jù)時，表達式結(jié)果自動計算并存儲到隱藏列，無需任何額外操作。
• 等價于顯式虛擬列索引：查詢優(yōu)化器能夠識別帶有相同表達式的 WHERE 條件，直接使用該索引。

可以用一個比喻來理解：顯式虛擬列相當于自己給數(shù)據(jù)貼上一個可見的標簽，而隱式虛擬列則是系統(tǒng)悄悄貼上的隱形標簽，只有系統(tǒng)自己需要時才會用到。

五、實戰(zhàn)場景：為 JSON 字段建立高效的查詢路徑

在爬蟲數(shù)據(jù)、訂單快照等以 JSON 存儲半結(jié)構(gòu)化數(shù)據(jù)的場景中，虛擬列/函數(shù)索引的價值尤為突出。

痛點：查詢JSON內(nèi)部字段只能全表掃描

假設(shè) UserLogin 表存儲 JSON 格式的登錄信息：

CREATE TABLE UserLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_cellphone(cellphone)
);

若沒有虛擬列，直接查詢 JSON 內(nèi)部字段只能寫為：

SELECT * FROM UserLogin
WHERE loginInfo->>"$.cellphone" = '13918888888';

該寫法每次需要解析 JSON、提取字段值，且無法利用索引，數(shù)據(jù)量稍大便會導致嚴重性能問題。

方案1：顯式虛擬列 + 索引

ALTER TABLE UserLogin
ADD COLUMN cellphone VARCHAR(255)
GENERATED ALWAYS AS (loginInfo->>"$.cellphone") VIRTUAL;
CREATE UNIQUE INDEX idx_cellphone ON UserLogin(cellphone);

之后便可以直接查詢 cellphone 列并命中索引：

SELECT * FROM UserLogin WHERE cellphone = '13918888888';

方案2：直接函數(shù)索引（8.0.13+）

CREATE INDEX idx_cellphone ON UserLogin ( (CAST(loginInfo->>"$.cellphone" AS CHAR(255))) );

兩種方案都能讓 JSON 字段查詢享受到與傳統(tǒng)列相同的索引性能，同時避免全表掃描帶來的資源浪費。

六、總結(jié)

• 普通索引失效的根因：索引基于原始列值排序，無法匹配函數(shù)處理后的結(jié)果，優(yōu)化器只能放棄索引。
• 函數(shù)索引：對表達式計算結(jié)果建立索引，直接解決函數(shù)條件無法使用索引的問題。MySQL 8.0.13+ 支持簡潔語法，底層自動創(chuàng)建隱式虛擬列。
• 顯式虛擬列 + 索引：MySQL 5.7 時期的替代方案，但今天依然流行，因為虛擬列對用戶可見，可讀性及復用性更佳。
• 隱式與顯式本質(zhì)相同：都是將表達式值物化并建立有序結(jié)構(gòu)，區(qū)別僅在于用戶是否能看到這一中間列。
• JSON 高性能查詢：虛擬列/函數(shù)索引是處理 JSON 字段查詢優(yōu)化的利器，能有效避免全表掃描，是各類半結(jié)構(gòu)化數(shù)據(jù)存儲方案的關(guān)鍵優(yōu)化手段。

掌握函數(shù)索引與虛擬列的原理與用法，能夠幫助開發(fā)者在面對復雜表達式查詢時，自如地做出最優(yōu)的索引設(shè)計，顯著提升數(shù)據(jù)庫讀寫性能。

到此這篇關(guān)于MySQL 函數(shù)索引與虛擬列最佳實踐的文章就介紹到這了,更多相關(guān)mysql函數(shù)索引與虛擬列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論