MySQL 5.7 虛擬列實現函數索引：從全表掃描到索引範圍查詢的性能優化實戰

問題背景：函數導致索引失效

表 t1(id, rank, log_time DATETIME, nickname)，log_time 有索引 idx_log_time。查詢「2015-04-09 的數據」：

SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09';

EXPLAIN 結果：type=ALL，rows=5000，**全表掃描**。索引 idx_log_time 因函數 DATE() 而失效。

傳統繞過方案：範圍查詢改寫

SELECT * FROM t1
WHERE log_time >= '2015-04-09 00:00:00'
  AND log_time <  '2015-04-10 00:00:00';

EXPLAIN 結果：type=range，key=idx_log_time，rows=2，**索引生效**。但 SQL 變長、業務邏輯分散、難維護。

MySQL 5.7 解決方案：虛擬列 + 索引

語法

ALTER TABLE t1
ADD COLUMN log_date DATE AS (DATE(log_time)) STORED,
ADD KEY idx_log_date (log_date);

• AS (expr)：虛擬列表達式，**不可寫入**，讀取時實時計算 (VIRTUAL) 或寫入時計算存儲 (STORED)。
• STORED：佔用磁盤空間，但查詢不再計算，**可建索引**。
• VIRTUAL (預設)：不佔磁盤，**不可建索引** (5.7 限制，8.0 支援函數索引直接建立)。

驗證效果

SELECT * FROM t1 WHERE log_date = '2015-04-09';

EXPLAIN 結果：type=ref，key=idx_log_date，rows=2，**精準索引命中**。

虛擬列完整語法與約束

column_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'comment']

• 表達式限制：無副作用、確定性函數、不可引用自增列、不可引用其他虛擬列 (8.0 放寬)、不可含子查詢。
• STORED 更新開銷：INSERT/UPDATE 基列時需計算並寫入虛擬列，批量導入略增耗時。
• 索引支援：STORED 可建立單列/聯合索引，VIRTUAL 僅 8.0+ 可建函數索引。
• 元數據：SHOW COLUMNS 顯示 GENERATED ALWAYS，INFORMATION_SCHEMA.COLUMNS 有 GENERATION_EXPRESSION。

適用場景與避坑指南

✅ 適合場景

1. 函數/表達式頻繁查詢：DATE()、YEAR()、SUBSTRING()、JSON_EXTRACT()、CAST() 等。
2. JSON 字段索引：ADD COLUMN v_name VARCHAR(50) AS (JSON_EXTRACT(data, '$.name')) STORED, ADD INDEX idx_v_name (v_name)。
3. 複雜計算結果複用：total_amount AS (price * quantity * (1 - discount)) STORED。

❌ 避坑指南

1. 非必要勿用：函數索引屬於「不得已而為之」，設計階段應優先考慮直接存儲計算結果列 (應用層寫入)。
2. STORED 增加寫入開銷：高頻寫入表需評估 CPU/IO 影響。
3. 8.0+ 首選函數索引：ALTER TABLE t1 ADD INDEX idx_func ((DATE(log_time)))，無需虛擬列，更簡潔。
4. 分區表限制：虛擬列不可作分區鍵。
5. 外鍵限制：虛擬列不可被外鍵引用，也不可引用外鍵列。

總結

MySQL 5.7 虛擬列 (STORED) 完美解決了函數導致索引失效的歷史痛點，將「寫時計算、讀時直接用」的思想落地。但本質上是**設計妥協的補救手段**，新架構應優先在應用層落地計算結果，或升級 MySQL 8.0 使用原生函數索引。