MySQL 索引學習與優化：類型、語法、原則、全文索引實戰

MySQL 索引學習與優化：類型、語法、原則、全文索引實戰

索引是查詢加速器，也是寫入負擔。本文系統梳理索引定義、五大類型、創建刪除語法、使用原則、全文索引中文難點與最佳實踐。

一、索引本質與代價

• 本質：數據的「目錄」，B+ 樹結構，將無序數據變為有序查找
• 收益：查詢 O(N) → O(log N)
• 代價：
  — 寫入 (INSERT/UPDATE/DELETE) 需維護索引樹，變慢
  — 存儲空間：索引文件甚至超過數據文件（MyISAM .MYI / InnoDB .ibd）

案例：15 列表、10 個索引、500 萬行 → 大量導入時先刪索引、導數據、再建索引。

二、索引算法複雜度對比

三、五大索引類型與語法

建表時指定索引

CREATE TABLE t (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(30),
  tel CHAR(11),
  intro TEXT,
  INDEX idx_tel (tel),
  UNIQUE INDEX uk_email (email),
  FULLTEXT INDEX ft_intro (intro)
) ENGINE=MyISAM CHARSET=utf8;

刪除索引

-- 普通/唯一/全文
ALTER TABLE t DROP INDEX idx_tel;

-- 主鍵（若為 AUTO_INCREMENT 需先移除屬性）
ALTER TABLE t MODIFY id INT;  -- 去掉 AUTO_INCREMENT
ALTER TABLE t DROP PRIMARY KEY;

四、索引使用四大原則

1. 不濫用：只為高頻 WHERE/JOIN/ORDER BY/GROUP BY 列建索引
2. 高區分度優先：COUNT(DISTINCT col)/COUNT(*) > 0.1（JOIN 列）
3. 避開低區分度：性別、狀態、布爾值等「基數極小」列不建索引
4. 最左前綴：組合索引 (a,b,c)，查詢必須從 a 開始連續匹配

五、全文索引中文難點與替代方案

• MySQL 內置全文索引基於「空格/標點分詞」，英文天然分詞，中文無分隔符 → 無法識別中文詞邊界
• 5.7+ 支援 ngram 解析器（WITH PARSER ngram），按 bigram 切分，勉強可用
• 生產建議：接入 Elasticsearch / Solr / OpenSearch 等專業搜索引擎，支援中文分詞器、同義詞、評分排序、高亮

六、查看索引結構

SHOW INDEX FROM t;
-- 關鍵列：Key_name, Column_name, Non_unique, Seq_in_index, Cardinality (區分度估值)

七、文件級視角

總結

索引設計 = 業務查詢模式分析 + 區分度評估 + 寫入性能權衡。定期 ANALYZE TABLE 更新統計信息，配合 EXPLAIN 驗證執行計劃，是索引優化的常態化動作。