萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 索引最佳實踐:從策略制定到工具驅動維護的完整體系

索引是性能倍增器,也是寫入負擔——科學用索引的 7 條鐵律

索引加速讀取但拖慢寫入。本文基於 MySQL InnoDB 架構,給出生產環境驗證的索引設計、使用、維護全流程規範。

七大索引策略鐵律

  1. 外鍵/關聯欄位必建索引:JOIN `ON` 條件、`WHERE` 關聯欄位,無索引將導致全表掃描或 Nested Loop 災難。外鍵約束自動建索引,但業務關聯欄位需手動建立。
  2. 高頻排序/分組欄位建索引:`ORDER BY`、`GROUP BY` 欄位納入索引尾部,實現「索引下推 + 避免 Filesort」。
  3. 高選擇性欄位優先建索引:選擇性 = `COUNT(DISTINCT col) / COUNT(*)`。> 0.1 甚至 > 0.3 才值得建索引。**性別/狀態/布爾值等低選擇性欄位嚴禁建單列索引**,不但無效還拖慢寫入。
  4. 組合索引遵循「最左前綴 + 選擇性遞減」:`INDEX (a, b, c)` 支援 `a` / `a,b` / `a,b,c` 查詢。高選擇性欄位盡量前置,但須兼顧查詢模式 (篩選在前,排序在後)。
  5. 覆蓋索引消除回表:查詢只需索引欄位即可滿足 (`Using index`),避免聚簇索引回表隨機 I/O。`EXPLAIN` 出現 `Using index` 即命中。
  6. 定期檢查索引健康度:`ANALYZE TABLE` 更新統計信息;`OPTIMIZE TABLE` 或 `ALTER TABLE ... ENGINE=InnoDB` 重建碎片索引。大表線上重建用 `pt-online-schema-change` 或 MySQL 8.0+ 即時 DDL。
  7. 刪除無用/重複索引:`pt-duplicate-key-checker` 掃描重複/冗餘索引。無用索引佔空間、拖寫入、誤導優化器。

系統工具鏈

  • Percona Toolkit:`pt-index-usage` (分析慢查詢日誌找未用索引)、`pt-duplicate-key-checker`、`pt-online-schema-change`
  • sys schema:`sys.schema_unused_indexes`、`sys.schema_redundant_indexes` 視圖直接查詢
  • Information Schema:`STATISTICS` 表手工分析基數、前綴長度

大批量寫入後的索引重建策略

-- 1. 刪除非唯一索引
ALTER TABLE huge_table DROP INDEX idx_a, DROP INDEX idx_b;

-- 2. 大量載入 (LOAD DATA / 批次 INSERT)

-- 3. 重建索引
ALTER TABLE huge_table ADD INDEX idx_a (...), ADD INDEX idx_b (...);

避免邊寫入邊維護 B+ 樹,單次建索引掃表排序建樹,效率高數數量級。

避坑清單

  • ❌ 為每個欄位建單列索引 (優化器只能用一個,其餘累贅)
  • ❌ 組合索引順序顛倒 (WHERE 用 b, ORDER BY a → 索引應為 (b, a))
  • ❌ 過長前綴索引 (`VARCHAR(255)` 只索引前 10 字元?選擇性極低)
  • ❌ 忽略外鍵隱式索引 (InnoDB 自動建,但需確認是否滿足查詢模式)
copyright © 萬盛學電腦網 all rights reserved