MySQL 索引深度解析：原理、類型、建立與優化實戰

前言：索引是查詢效能的基石

索引是資料庫加速查詢的核心手段，尤其在多表聯查、大數據量過濾、排序分組場景下效果顯著。本文系統梳理 MySQL 索引特性、分類、建立/刪除語法、前綴索引策略，並對比 ISAM/MyISAM/InnoDB 差異，助你構建高效索引架構。

一、索引核心特性與限制

• 所有列類型皆可索引，但 BLOB/TEXT 必須指定前綴長度。
• 單表最多 16 個索引（MyISAM 32 個），單索引最多 16 列（MyISAM 16 列），最大索引行長度 256 位元組（MyISAM 500，InnoDB 3072/768 視版本）。
• CHAR/VARCHAR 可索引前綴，大幅節省空間並提升速度。
• NULL 值限制：早期 ISAM 不允許；MyISAM/InnoDB 允許（除 PRIMARY KEY）。

二、三大核心索引類型對比

本質：PRIMARY KEY 只是名為 PRIMARY 的 UNIQUE 索引，且隱含 NOT NULL。

三、建立與刪除索引的三種方式

1. ALTER TABLE（最通用，支援所有類型）

-- 單列普通索引
ALTER TABLE tbl ADD INDEX idx_name (col);
-- 多列複合索引
ALTER TABLE tbl ADD INDEX idx_multi (col1, col2, col3);
-- 唯一索引
ALTER TABLE tbl ADD UNIQUE uk_email (email);
-- 主鍵
ALTER TABLE tbl ADD PRIMARY KEY (id);

-- 刪除
ALTER TABLE tbl DROP INDEX idx_name;
ALTER TABLE tbl DROP PRIMARY KEY;

2. CREATE / DROP INDEX（3.23+ 引入，語法更直觀）

CREATE INDEX idx_name ON tbl (col);
CREATE UNIQUE INDEX uk_email ON tbl (email);
DROP INDEX idx_name ON tbl;

限制：無法建立 PRIMARY KEY；索引名不可省略。

3. CREATE TABLE 時同步定義（效能最佳，避免鎖表重建）

CREATE TABLE tbl (
  id INT NOT NULL,
  name CHAR(30),
  email VARCHAR(100),
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email),
  INDEX idx_name (name(10)),      -- 前綴索引：僅索引前 10 字元
  INDEX idx_multi (name, email)   -- 複合索引
);

四、前綴索引：空間與效能的平衡術

• 適用場景：長 CHAR/VARCHAR、BLOB/TEXT 必須使用前綴。
• 語法：col_name(prefix_length)，如 name(10)。
• 選擇原則：COUNT(DISTINCT LEFT(col, n)) / COUNT(*) ≈ 1 時，n 為最佳長度。
• 副作用：無法在不重建索引下縮短欄位長度低於前綴長度。

五、查看索引資訊

SHOW INDEX FROM tbl\G
-- 關鍵欄位：Non_unique(0=唯一), Key_name, Seq_in_index(複合索引順序), Column_name, Cardinality(基數)

六、存儲引擎差異對索引的影響

InnoDB 核心啟示：主鍵宜短（如自增整數），避免 UUID/長字串導致二級索引膨脹。

七、實戰建議總結

1. WHERE/JOIN/ORDER BY/GROUP BY 涉及的欄位優先建索引。
2. 複合索引遵循「最左前綴原則」，高選擇性欄位前置。
3. 避免過度索引：寫入效能下降、佔用空間、優化器誤判風險。
4. 定期 ANALYZE TABLE 更新統計資訊，配合 EXPLAIN 驗證執行計畫。
5. 覆蓋索引優化：SELECT 欄位全在索引中，避免回表。