MySQL 索引設計三大原則：從原理到實戰的效能調優指南

一、索引重要性：字典查字的啟示

無索引 = 全表掃描（從第 1 頁翻到最後），有索引 = 目錄直達（翻到 Z 開頭頁碼）。表越大、查詢越頻，索引價值越高。但索引非越多越好：維護成本（寫入放大）、佔用空間、優化器選擇困擾都會隨索引數增加而上升。

二、實驗環境建立

CREATE TABLE test_t (
  id INT PRIMARY KEY AUTO_INCREMENT,
  num INT NOT NULL DEFAULT 0,
  d_num VARCHAR(30) NOT NULL DEFAULT '0'
) ENGINE=MyISAM;

CREATE TABLE test_test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  num INT NOT NULL DEFAULT 0
) ENGINE=MyISAM;

-- 建立存儲過程快速生成測試資料
DELIMITER $$
CREATE PROCEDURE i_test(IN pa INT, IN tab VARCHAR(30))
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < pa DO
    IF tab != 'test_test' THEN
      INSERT INTO test_t (num, d_num) VALUES (FLOOR(RAND()*100), CONCAT(FLOOR(RAND()*100), FLOOR(RAND()*100)));
    ELSE
      INSERT INTO test_test (num) VALUES (FLOOR(RAND()*100));
    END IF;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

SET profiling = 1; -- 開啟剖析

三、三大核心原則與實證

原則 1：小表不加索引，反而拖慢查詢

CALL i_test(10, 'test_t'); -- 僅 10 筆
SELECT num FROM test_t WHERE num != 0; -- 0.00026s
CREATE INDEX num_2 ON test_t(num);
SELECT num FROM test_t WHERE num != 0; -- 0.00127s (慢 5 倍!)

解釋：10 行資料全表掃描在記憶體瞬間完成，B+ 樹遍歷、磁盤 IO 開銷反而更大。經驗值：< 1000 行或查詢回傳 > 30% 行數時，全表掃描更優。

原則 2：WHERE、JOIN、ORDER BY、GROUP BY 後的欄位優先建索引

實驗：test_t 10 萬筆，num 加索引前後：

• 無索引：type=ALL, rows=100000
• 有索引：type=ref, rows=1 (唯一值) 或 range

索引類型選擇：

• 唯一業務鍵 → UNIQUE INDEX（語義約束 + 效能）
• 主鍵 → PRIMARY KEY（聚簇索引，InnoDB 必須）
• 普通查詢欄位 → INDEX
• 複合查詢 → 複合索引，遵循「最左前綴」與「區分度高在前」。

原則 3：多表關聯/子查詢的關聯欄位雙向建索引

EXPLAIN SELECT a.num, b.num 
FROM test_t a LEFT JOIN test_test b ON a.num = b.num;

結果：驅動表 a 走 index (覆蓋索引)，被驅動表 b 走 ref 存取 num_1 索引。無索引時被驅動表會全表掃描，Nested Loop Join 複雜度爆炸。

注意：超大表關聯即使有索引仍可能慢，建議應用層拆分查詢或用 Redis 快取聚合結果。

四、EXPLAIN 關鍵欄位速查

五、總結

1. 索引是雙面刃，小表不用、大表精準、寫多讀少慎加。
2. 以 EXPLAIN 為準，觀察 type、rows、Extra 驗證索引生效。
3. 複合索引遵循「最左前綴」，WHERE a=1 AND b=2 建 (a,b) 而非 (b,a)。
4. 定期 ANALYZE TABLE 更新統計資訊，避免優化器誤判。