MySQL 可搜尋條件：用產生欄位保住索引

當查詢為了比對電話、代碼或識別字而在資料欄位上套用 REPLACE()、LOWER()、DATE() 等函數，原本建立在基礎欄位上的 B-tree 索引不一定能直接支援該條件。問題不是函數本身「很慢」，而是條件改成計算結果後，索引鍵與查詢要求的值不再是同一個可搜尋邊界。對大表而言，這可能讓點查詢退化成逐列計算；正確處理方式是先用 EXPLAIN 取證，再把穩定、確定性的正規化規則建成有索引的產生欄位，並以相同運算式與型別驗證計畫。

本文聚焦 MySQL sargability、產生欄位索引與執行計畫驗證。本文回答「為何欄位函數讓索引失去作用，以及如何用 generated column 修復」。它不同於既有的 EF Core 查詢轉譯文章，也不同於 INFORMATION_SCHEMA 索引快取與 DDL 驗收文章；這裡的核心決策是查詢條件能否形成索引範圍，而不是 ORM 寫法或索引 metadata 是否即時。

直接答案：先讓條件可搜尋，再談索引

直接答案：不要只看到 key=NULL 就盲目加索引；先確認函數是否套在被搜尋欄位上，再把固定正規化運算式建成有索引的產生欄位，並用相同運算式、相同結果型別與代表性參數重新執行 EXPLAIN。

Sargable 可理解為「條件能被索引搜尋」。例如 phone = '5550100' 能以完整等值鍵查找，但 REPLACE(phone, '.', '') = '5550100' 要求的是運算後的值。只有 phone 索引時，最佳化器沒有一棵直接依 REPLACE(phone, '.', '') 排序的索引樹可查。把函數移到常數端有時可行，例如先在應用程式正規化輸入；但若既有資料本身混有分隔符號，僅處理輸入仍無法讓原始欄位與正規化值相等。

MySQL 8.4 官方文件說明，產生欄位可以建立索引。查詢中的運算式若與產生欄位定義相同，而且結果型別相同，最佳化器可以用產生欄位取代該運算式並評估索引。這個「可以」不是保證必選：成本模型仍可能因資料量、選擇性與統計資訊選擇掃描。因此，驗收標準應是可觀察的計畫與結果一致性，而不是「建立索引就一定更快」。

為何欄位函數會破壞可搜尋邊界

索引保存原始鍵；當查詢改比對函數結果，原始排序無法直接界定候選範圍，因此可能逐列計算。

B-tree 索引保存已排序的索引鍵。等值、範圍與前綴條件若可直接映射到這些鍵，儲存引擎便能從樹上的起點縮小候選範圍。當每列都必須先套用函數，條件描述的是另一個值域；基礎欄位索引的排序不一定等同函數輸出的排序，也未保存函數結果。這就是可搜尋性問題，與「函數執行一次需要幾微秒」是兩件事。

常見例子包括：

• WHERE DATE(created_at) = '2026-10-05'：可優先改為半開區間 created_at >= ... AND created_at < ...，通常不必新增欄位。
• WHERE LOWER(email) = ...：若大小寫規則屬於資料契約，可在寫入邊界保存規範值，或評估有索引的產生欄位；同時必須理解 collation。
• WHERE REPLACE(phone, '.', '') = ...：若歷史資料格式不一致，產生欄位能把正規化規則集中在資料庫 schema，避免每個呼叫端各寫一版。
• WHERE amount + fee = ...：只有在運算式穩定且確實為主要查詢維度時，才值得建立對應索引，否則會增加寫入與儲存成本。

不要把 type=ALL 一律視為故障。MySQL 官方文件指出，小表、低基數條件或涵蓋大部分資料的條件，本來就可能讓掃描比索引回表便宜。測試資料若只有數列，最佳化器可能合理忽略索引。可靠測試要有足夠列數與有選擇性的目標值，而且要同時查看 possible_keys、key、type、估計列數及附加條件。

設計產生欄位前的四個決策

建立產生欄位前，先固定正規化語意、資料型別、替代重寫與寫入成本；任何一項不明確，都不應直接新增索引。

第一，正規化規則必須是資料契約，而不是臨時修補。要先決定點、空白、國碼符號或大小寫如何處理。若今天移除句點、明天又移除空白，欄位定義與查詢運算式不同，舊索引不能代表新規則。正規化也可能產生碰撞：兩個原始值經清理後相同。若要加唯一索引，必須先分析碰撞；本文沙箱只用一般 secondary index，不把正規化結果宣告為唯一。

第二，資料型別與字元規則要一致。官方文件要求查詢運算式不只文字上相同，結果型別也要相同。電話不是數值計算欄位：前導零可能具有意義，應保留字串型別。VARCHAR 長度、character set 與 collation 會影響比較；不要為了看似方便而轉成整數。

第三，優先選最簡單的重寫。日期函數常能改成範圍；輸入格式可在服務邊界正規化時，直接查原始規範欄位可能更清楚。產生欄位適合「讀取頻繁、規則穩定、舊資料不能一次改完、需要由 schema 維持一致」的情境。它不是每個函數條件的預設答案。

第四，計算讀取收益，也計算寫入成本。每個 secondary index 都要在新增、更新與刪除時維護。產生欄位的表達式也有計算成本。應以實際查詢比例、寫入量與容量預算判斷，而不是引用別人的毫秒數。來源筆記包含特定環境的列數與耗時，但這些結果不應外推，因此本文不把它們當成讀者可期待的效能承諾。

可重現測試：比較基礎索引與產生欄位索引

這個測試在固定的 MySQL 8.4.0 本機容器建立一次性資料表，先區分基礎索引的完整掃描與等值定位，再加入有索引的虛擬產生欄位，驗證同一正規化語意可取得索引計畫與相同結果。排程環境未安裝 Docker 或 MySQL client，因此以下是讀者可重現步驟，排程環境未執行。

1. 啟動隔離容器並確認版本。使用 mysql:8.4.0，不要連線任何既有資料庫。前置條件是已由操作者建立並隔離網路的空白 MySQL 8.4.0 沙箱，且已用 MySQL client 連入。下方是完整 SQL 測試，不包含容器建置或初始化程序。先單獨執行 SELECT VERSION()，確認為 8.4.0 才繼續；版本不符或資料庫名稱已存在即停止，不清除既有資料。
2. 建立 contacts_lab、加入基礎欄位索引並產生 10,000 筆 fixture。先對 REPLACE(phone_raw, '.', '') 執行 EXPLAIN FORMAT=JSON，保存 key 與 access_type。
3. 新增 phone_normalized 虛擬產生欄位與 secondary index，分別以欄位名稱及完全相同的運算式查詢，再比較計畫與 COUNT(*)。測試完成後只移除該一次性容器。

SELECT VERSION();
CREATE DATABASE sargability_lab;
USE sargability_lab;
CREATE TABLE contacts_lab (
  id BIGINT NOT NULL PRIMARY KEY,
  phone_raw VARCHAR(32) NOT NULL,
  INDEX ix_phone_raw (phone_raw)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO contacts_lab (id, phone_raw)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n, CONCAT('555.', LPAD(n, 7, '0')) FROM seq;

EXPLAIN FORMAT=JSON
SELECT id FROM contacts_lab
WHERE REPLACE(phone_raw, '.', '') = '5550004321';

ALTER TABLE contacts_lab
  ADD COLUMN phone_normalized VARCHAR(32)
    GENERATED ALWAYS AS (REPLACE(phone_raw, '.', '')) VIRTUAL,
  ADD INDEX ix_phone_normalized (phone_normalized);

EXPLAIN FORMAT=JSON
SELECT id FROM contacts_lab
WHERE phone_normalized = '5550004321';

EXPLAIN FORMAT=JSON
SELECT id FROM contacts_lab
WHERE REPLACE(phone_raw, '.', '') = '5550004321';

SELECT
  (SELECT COUNT(*) FROM contacts_lab
   WHERE REPLACE(phone_raw, '.', '') = '5550004321') AS function_count,
  (SELECT COUNT(*) FROM contacts_lab
   WHERE phone_normalized = '5550004321') AS generated_count;

通過條件不要寫成某個固定毫秒數。第一個計畫可能顯示 access_type=index 且 key=ix_phone_raw，因為查詢只需要索引已涵蓋的欄位；這是完整索引掃描，不是正規化等值定位。access_type=ALL 也是可能的基準計畫。不要把非空 key 當成可搜尋性的證明；以 phone_normalized 查詢時，key 應為 ix_phone_normalized，且 access type 不應為 ALL。直接使用相同 REPLACE() 運算式時，MySQL 可能將它比對到產生欄位索引；若未採用，先用 SHOW WARNINGS 檢查重寫，再核對運算式與結果型別。兩種語意的計數都必須為 1。任何結果數不同都代表修法改變了資料契約，即使計畫看起來更漂亮也不能接受。

若 fixture 太小或目標值不具選擇性，成本模型可能仍選擇掃描。此時不要立刻使用 FORCE INDEX 美化測試；先增加具代表性的安全 fixture、執行 ANALYZE TABLE contacts_lab，再觀察計畫。索引提示適合診斷，不應掩蓋統計資訊或資料分布問題。

EXPLAIN 與 EXPLAIN ANALYZE 的安全使用方式

EXPLAIN 顯示最佳化器預計如何處理查詢，本身適合先做低風險計畫檢查。EXPLAIN ANALYZE 則會真的執行敘述並回報 iterator 的實際時間、列數與迴圈；它提供更強證據，也帶來真實查詢成本。對未知的大表查詢，不應把 EXPLAIN ANALYZE 當成零成本檢視器。先在隔離資料、唯讀副本或受控測試資料集執行，並設定合理的資源界線。

判讀時至少回答五個問題：

1. possible_keys 是否出現預期索引？沒有時通常代表條件或型別無法匹配。
2. key 最終選了哪個索引？出現候選不等於實際採用。
3. type 或 JSON access_type 是 ALL、ref、range 還是其他方式？
4. 估計列數與實際列數是否差距過大？差距可能指向過時統計或資料偏斜。
5. 修正前後的結果集合是否完全相同？效能改善不能交換正確性。

計畫只是一個時間點的決策。資料分布、統計資訊、MySQL 版本、參數型別或 collation 改變後，計畫也可能改變。應把代表性 EXPLAIN FORMAT=JSON 快照與結果契約納入回歸檢查，但不要硬性比對所有成本數字；成本估計可因版本更新而合理變動。較穩定的斷言是指定查詢不得為 ALL、必須選到預期索引，以及結果內容一致。

上線前風險清單與回復邊界

新增產生欄位與索引屬於 schema 變更。即使版本文件列出 online DDL 能力，也不能把「允許並行讀寫」誤解成「沒有 metadata lock、沒有 I/O 壓力、一定瞬間完成」。資料量、欄位型別、既有長交易、儲存空間與複寫拓樸都會改變風險。本文刻意不宣稱特定演算法在所有表結構上都不阻塞；部署前應針對實際 MySQL 小版本與表定義執行 ALTER TABLE ... ALGORITHM=... 的失敗式能力檢查。

檢查清單如下：

• 語意：列出正規化前後碰撞、空字串、NULL、前導零與非預期字元。
• 型別：固定參數與產生欄位的字串型別、長度、character set、collation。
• 計畫：保存修正前後 EXPLAIN FORMAT=JSON，同時驗證候選索引與實際索引。
• 正確性：針對正常值、含分隔符號、無匹配及碰撞案例比較結果集合。
• 寫入：量測 sandbox 中新增與更新成本，不引用其他環境的改善百分比。
• DDL：在同版本、相近 schema 的測試副本確認支援的演算法、鎖定行為與空間需求。
• 回復：先讓應用程式可切回原查詢，再移除索引或欄位；不要把不可逆資料清理綁在同一變更。
• 監控：觀察查詢延遲分布、掃描列數、錯誤率與資料庫資源，而不是只看單次最快值。

若規則仍在變動，最穩健的決策可能是暫不加索引，先把資料契約定義清楚。若規則已穩定且讀取價值足夠，有索引的產生欄位能把「每次查詢臨時計算」轉成可治理的 schema 能力。真正的完成條件不是 DDL 成功，而是計畫可驗證、結果不變、風險有界且可回復。

結論：用證據證明索引可用，而非相信索引存在

MySQL 查詢最佳化的核心不是索引數量，而是查詢條件能否映射到索引鍵。函數套在欄位上時，先找可重寫的範圍或輸入正規化；需要跨歷史資料維持一致運算時，再考慮有索引的產生欄位。最後以 EXPLAIN、結果集合與隔離環境測試共同驗收。

這套方法避免兩種常見誤判：第一，把所有全表掃描都當錯；第二，看到索引已建立就假設查詢一定會用。把運算式一致性、型別、選擇性、寫入成本與回復策略放進同一張檢查表，才是可維護的 MySQL sargability 改善。

預期結果與官方文件

以下是驗收條件，不是本次實測輸出。COUNT(*) 的欄位值必須等於 1；不是只確認查詢回傳一列。基準計畫若出現 ref、range 或其他定位路徑，先保留計畫並檢查測試前置條件，不以 key 是否空白單獨判定。

Server version: 8.4.0
Baseline: access_type=index or ALL; key may be ix_phone_raw
Direct generated-column lookup: key=ix_phone_normalized; access_type=ref
function_count: 1
generated_count: 1
Failure: SQL error, wrong version, count mismatch, or direct lookup lacks ref access

確認資料與計畫後，可自行停止並移除本次建立的容器；先核對名稱與建立時間，勿移除先前存在的容器。本文僅示範隔離測試，不以此設定部署資料庫。

• 產生欄位索引與運算式匹配
• EXPLAIN 與 EXPLAIN ANALYZE
• 全表掃描與成本選擇