MySQL INFORMATION_SCHEMA 索引快取：避免 DDL 驗收誤判

完成 CREATE INDEX 之後，若 INFORMATION_SCHEMA.STATISTICS 的統計值不如預期，不應直接判定 DDL 失敗；如果缺少索引結構列，則應另查連線目標、權限與 DDL 狀態，不能用統計快取解釋。更穩健的判斷是先區分「索引定義是否存在」與「統計值是否新鮮」：前者用 SHOW INDEX 核對索引名稱、欄位順序與可見性；後者才討論 CARDINALITY、ANALYZE TABLE 與 information_schema_stats_expiry。本文以 MySQL 8.4 沙盒建立可重現流程，不引用特定環境位址、資料量或效能數字。

這是一個資訊型、MOFU 的資料庫維運主題。讀者已會建立索引，但需要一套可稽核的驗收方法。核心 KPI 不是「DDL 指令顯示成功」而已，而是索引定義比對通過率、變更後誤報率、驗收查詢一致性，以及異常時能否留下足以重現的證據。

MySQL INFORMATION_SCHEMA 索引快取的直接答案

INFORMATION_SCHEMA.STATISTICS 的部分統計欄位可以使用快取值，預設過期時間為 86400 秒；DDL 後若要確認索引定義，先以 SHOW INDEX 核對結構，不要只靠統計欄位是否立即更新下結論。

MySQL 官方文件把 STATISTICS.CARDINALITY 列為動態表格中繼資料。伺服器通常從資料字典的快取統計取得這些值；沒有快取或快取過期時，才向儲存引擎取得新值。information_schema_stats_expiry 支援全域與工作階段範圍，因此不同連線可能採用不同的過期設定。這個機制是為了降低頻繁蒐集統計的成本，不代表索引定義本身消失。

驗收時必須拆成兩個問題。第一，索引物件是否存在，而且欄位順序、唯一性、可見性與索引方法是否正確？第二，最佳化器使用的基數估計是否需要更新？把兩者混在一起，會把快取統計的時間差誤報為 DDL 失敗，也可能在看見索引名稱後就錯誤認定查詢一定會採用它。

先分清索引定義與統計估計

索引定義描述資料庫中有哪些索引及其結構；統計估計描述索引值分布，供最佳化器評估成本。SHOW INDEX 同時呈現兩類欄位，但 Key_name、Seq_in_index、Column_name 與 Visible 才是結構驗收的主要依據。

以複合索引為例，只有確認 Seq_in_index 的順序，才能知道最左前綴是否符合查詢條件。只檢查索引名稱不夠，因為名稱正確但欄位次序錯誤，仍可能無法服務預期的篩選或排序。Non_unique 則用來核對唯一性契約；Visible 可辨認索引是否對最佳化器可見；運算式索引還需要查看 Expression。

CARDINALITY 不同。官方明確說明它是索引唯一值數量的估計，以整數統計計算，即使小表也不保證精確。因此，驗收腳本不應把某個固定基數當成索引存在的條件，也不應因兩次查詢的估計不同就宣稱資料不一致。結構門檻應比對可決定的欄位；統計門檻則應標註估計性質及更新方法。

建議把證據分成三層：

• DDL 回應：記錄指令是否由伺服器接受，但不把用戶端等待結果等同完整驗收。
• 結構快照：以 SHOW INDEX 保存索引名稱、欄位序、唯一性、可見性及運算式。
• 計畫證據：對代表性查詢執行 EXPLAIN，觀察候選索引與實際選擇，不把「索引存在」誤寫成「索引必然被使用」。

何時調整 information_schema_stats_expiry

需要最新統計時，可在目前工作階段把 information_schema_stats_expiry 設為 0；需要重新蒐集特定資料表的鍵分布時，再評估 ANALYZE TABLE。兩者處理的是統計新鮮度，不是建立遺失的索引。

預設 86400 秒是一項官方記載的預設值，不應硬編碼成所有環境永遠相同。驗收開始時先執行 SELECT @@SESSION.information_schema_stats_expiry，把實際工作階段值寫入證據。若自動化工具開啟多條連線，也要在每條執行驗收查詢的連線設定或讀取該值；在 A 連線調整工作階段變數，不會自動改變 B 連線的設定。

將值設為 0 會讓相關 INFORMATION_SCHEMA 查詢直接向儲存引擎取得最新統計，代價通常高於讀取快取。因此不宜把它當成全域、永久的萬用修正。更合理的方式是把設定限制在短生命週期的驗收工作階段，完成證據蒐集後關閉連線，讓其他工作階段保留各自策略。

ANALYZE TABLE 用於更新鍵分布，而不是「重新建立索引」。它可能需要資源，也可能受唯讀設定影響。若任務只要求確認索引欄位與順序，先用 SHOW INDEX 即可；若後續 EXPLAIN 顯示估計明顯需要更新，再於隔離沙盒或經核准的維護流程評估分析。這個順序能避免為了讀取結構而不必要地改動統計狀態。

建立可稽核的 DDL 驗收契約

一份可靠的驗收契約應把「預期結構」寫成資料，而不是依賴人眼掃描畫面。至少固定資料庫版本、資料表、索引名稱、欄位順序、唯一性與可見性，並為每個不符合條件定義清楚的失敗訊息。

假設沙盒表 orders_lab 預期有 ix_orders_status_created，依序包含 status_code 與 created_at。驗收不能只寫「查得到一列」；複合索引會回傳多列，應依 Seq_in_index 排序後比對完整序列。若索引存在但少一欄、多一欄、順序顛倒或不可見，都要判定失敗。

自動化流程可以保存以下欄位：伺服器版本、工作階段過期值、DDL 結束狀態、SHOW INDEX 結果、INFORMATION_SCHEMA.STATISTICS 結果及 EXPLAIN。保存證據不等於公開真實資料；公開範例只使用合成名稱，內部記錄也應依資料治理規範限制存取與保存期限。

推薦的閘門順序如下：

1. 確認執行目標是隔離 MySQL 8.4.0 沙盒，建立只有合成欄位的測試表。
2. 執行 CREATE INDEX，再用 SHOW INDEX 比對名稱、兩個欄位的順序與可見性。
3. 讀取工作階段的 information_schema_stats_expiry，分別在預設值與 0 下查詢 STATISTICS。
4. 執行代表性 EXPLAIN，只記錄最佳化器當下選擇，不把結果宣稱為所有資料分布的保證。
5. 任一結構欄位不符即關閉發布閘門；只有統計估計不同時，標示為統計證據待判讀，不回頭重複建立同名索引。

驗證與重現：MySQL 8.4 沙盒

以下步驟只適用於本機或容器內的空白沙盒，固定 MySQL 8.4.0，且不得指向正式資料。排程環境沒有 Docker、Podman 或 MySQL 用戶端，因此這是讀者可重現步驟，排程環境未執行；本文不提供虛構輸出。

1. 啟動隔離的 MySQL 8.4.0 沙盒，以命令列進入後確認 SELECT VERSION() 回傳 8.4.0；版本不同即停止，避免把其他版本行為混入證據。
2. 逐段執行下列 SQL。建立合成資料表與複合索引後，確認 SHOW INDEX 對目標索引回傳兩列，欄位順序為 status_code、created_at。
3. 確認預設工作階段值；再設為 0 並重查 INFORMATION_SCHEMA.STATISTICS。通過條件是變數值變為 0，且索引結構仍完整存在。
4. 執行 EXPLAIN。命令失敗或結構欄位與契約不符時，測試失敗；保存 possible_keys、key 與 Extra，空表可能使最佳化器省略一般存取路徑，因此候選索引為 NULL 本身不代表 DDL 失敗。

SELECT VERSION();
CREATE DATABASE IF NOT EXISTS index_verification_lab;
USE index_verification_lab;

CREATE TABLE IF NOT EXISTS orders_lab (
  id BIGINT NOT NULL PRIMARY KEY,
  status_code VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE = InnoDB;

CREATE INDEX ix_orders_status_created
  ON orders_lab (status_code, created_at);

SHOW INDEX FROM orders_lab
  WHERE Key_name = 'ix_orders_status_created';

SELECT @@SESSION.information_schema_stats_expiry;
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'index_verification_lab'
  AND TABLE_NAME = 'orders_lab'
  AND INDEX_NAME = 'ix_orders_status_created'
ORDER BY SEQ_IN_INDEX;

SET SESSION information_schema_stats_expiry = 0;
SELECT @@SESSION.information_schema_stats_expiry;
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'index_verification_lab'
  AND TABLE_NAME = 'orders_lab'
  AND INDEX_NAME = 'ix_orders_status_created'
ORDER BY SEQ_IN_INDEX;

EXPLAIN FORMAT=TRADITIONAL SELECT id
FROM orders_lab
WHERE status_code = 'ready'
ORDER BY created_at;

這個重現刻意不加入真實資料，也不聲稱一定出現某個 key。空表或資料分布不同時，最佳化器可以合法選擇不同計畫。可穩定斷言的是：索引定義應回傳兩個正確排序的欄位，工作階段值可被設為 0，而 CARDINALITY 是估計，不是逐列精確計數。

常見誤判與安全邊界

最常見的誤判，是把用戶端等待逾時、統計快取、索引結構與查詢計畫混成單一布林值。安全流程應逐層取證；任何一層未知，都應標示未知，而不是重送可能已完成的 DDL。

第一個誤判是「工具畫面沒更新，所以索引不存在」。先開新查詢並執行 SHOW INDEX，因為圖形介面也可能快取物件樹。第二個誤判是「看見索引，所以查詢一定會走它」。最佳化器仍會根據成本、資料分布、可見性與查詢形狀選擇計畫。第三個誤判是「CARDINALITY 不等於真實列數，所以索引壞了」；官方定義本來就是估計。

第四個誤判是把 information_schema_stats_expiry=0 當成全域常態。這可能提高中繼資料查詢成本，也掩蓋監控工具過度輪詢的問題。第五個誤判是為了得到漂亮數字而頻繁執行 ANALYZE TABLE。應先定義需要解決的決策：若只是驗收結構，根本不需要刷新統計；若要診斷計畫，才需要把統計新鮮度納入控制變數。

安全邊界包括：只在空白沙盒重現；不連接真實帳號、付款、交易或客戶資料；不在未知狀態下重送 DDL；不把特定環境的耗時或資料量複製成通用效能承諾。正式變更應另有審批、備份、回復與監控流程，本文不取代這些治理。

結論與檢查清單

MySQL 索引驗收的核心，是先證明結構，再判讀統計，最後觀察計畫。SHOW INDEX、工作階段過期值、STATISTICS 與 EXPLAIN 各自回答不同問題，不能互相替代。

發布或交付 DDL 前，可快速檢查：

• 已固定 MySQL 版本與隔離沙盒前置條件。
• 已用 SHOW INDEX 核對名稱、欄位序、唯一性及可見性。
• 已記錄 @@SESSION.information_schema_stats_expiry，沒有假設所有連線相同。
• 已把 CARDINALITY 當作估計，而非索引存在證明或精確列數。
• 只有在需要新統計時才評估 ANALYZE TABLE 或工作階段值 0。
• 已用 EXPLAIN 驗證代表性查詢，但沒有承諾所有資料分布都採同一計畫。
• 排程環境未執行的步驟已明確標示，沒有編造終端輸出或效能數字。

這套方法的價值不在增加更多 SQL，而在讓每個結論都有對應證據。當結構、統計與計畫被拆開，團隊就能判斷究竟是 DDL 未完成、統計尚未刷新，還是最佳化器基於成本做了不同選擇，避免因錯誤診斷造成重複變更。

官方依據與預期結果

以下是驗收契約，並非實測輸出：

SHOW INDEX: 2 rows; sequence 1=status_code, 2=created_at
Non_unique: 1; Visible: YES
Session expiry after SET: 0
STATISTICS after SET: same two ordered index columns
EXPLAIN: successful statement; retain possible_keys, key and Extra

統計快取不等於索引清單快取；SHOW INDEX 與 STATISTICS 均可提供結構證據。來源筆記的概括說法已依 MySQL 8.4 文件修正。

• STATISTICS and statistics expiry
• SHOW INDEX fields
• EXPLAIN output