您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL SQL 優化核心原則與實戰技巧：索引、EXPLAIN、避坑指南

編輯：MySQL 綜合教學

前言

SQL 優化是資料庫效能提升的最高 ROI 活動。本文從索引設計、查詢改寫、EXPLAIN 分析、常見反模式等維度，系統總結 MySQL SQL 優化核心原則與實戰技巧。

一、索引設計核心原則

1. 索引類型選擇

B-Tree 索引：預設、支援 =、>、<、>=、<=、BETWEEN、LIKE 'prefix%'
Hash 索引：Memory 引擎預設、僅支援 =、IN、不支援範圍/排序
Fulltext 索引：全文搜尋專用
Spatial 索引：空間資料專用

2. 建立索引決策

場景	建議
WHERE/JOIN/ORDER BY/GROUP BY 欄位	優先建立索引
高選擇性 (基數大、重複少)	單欄索引效果佳
多欄位常一起查詢	組合索引，遵循最左前綴原則
低選擇性 (性別、狀態、類型)	通常不建索引，或組合索引次序靠後
長字串欄位	前綴索引 (name(10))
頻繁 UPDATE/INSERT、少 SELECT	慎重建索引，維護成本高

3. 組合索引最左前綴原則

CREATE INDEX idx_a_b_c ON t(a, b, c);

-- 可用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 ORDER BY b
WHERE a = 1 AND b = 2 ORDER BY c

-- 無法用到索引 (或部分使用)
WHERE b = 2                  -- 缺少最左列 a
WHERE a = 1 AND c = 3        -- 中間缺 b
WHERE b = 2 AND c = 3        -- 缺少最左列 a
ORDER BY b                   -- 缺少最左列 a

4. 覆蓋索引

查詢所需欄位全在索引中，無需回表：

-- 索引: (a, b, c)
SELECT a, b, c FROM t WHERE a = 1;  -- 覆蓋索引，Extra: Using index

5. 索引下推 (ICP, MySQL 5.6+)

儲存引擎層面過濾，減少回表：

-- 索引: (a, b)
SELECT * FROM t WHERE a = 1 AND b LIKE 'abc%';
-- 5.6+ 在索引遍歷時直接過濾 b，不滿足不回表

二、查詢改寫優化技巧

1. 避免在索引欄位上使用函數/運算

-- ❌ 索引失效
WHERE YEAR(created_at) = 2024
WHERE amount / 7 < 24
WHERE UPPER(name) = 'JOHN'

-- ✅ 改寫為範圍查詢
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE amount < 24 * 7
WHERE name = 'JOHN'  -- 配合 collate 或功能性索引 (8.0+)

2. LIKE 查詢優化

-- ✅ 可用索引
WHERE name LIKE 'abc%'

-- ❌ 無法用索引 (全表掃描)
WHERE name LIKE '%abc'
WHERE name LIKE '%abc%'

-- 解決方案：全文索引、反轉欄位、搜尋引擎

3. OR 條件優化

-- ❌ 通常導致全表掃描
WHERE a = 1 OR b = 2

-- ✅ 改寫為 UNION (需各條件有索引)
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

-- 或使用 UNION ALL + 去重 (效能更好，若確定無重複)

4. IN / NOT IN 優化

IN (常數列表)：MySQL 優化為二分查找，效能好
IN (子查詢)：5.6+ 優化為 Semi-Join，但複雜子查詢仍建議改寫 JOIN
NOT IN (含 NULL)：永遠回傳空集，改用 NOT EXISTS 或 LEFT JOIN ... IS NULL

5. 分頁優化 (大偏移量)

-- ❌ 偏移量大時極慢 (掃描 100萬行再丟棄)
SELECT * FROM t ORDER BY id LIMIT 1000000, 20;

-- ✅ 遞延關聯 / 書籤分頁
SELECT * FROM t 
WHERE id > (SELECT id FROM t ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 20;

-- ✅ 記住上一頁最大 id (游標分頁)
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 20;

6. COUNT(*) 優化

MyISAM：O(1) 直接讀取表行數
InnoDB：需全表掃描 (MVCC 可見性檢查)
近似值：SHOW TABLE STATUS 或 EXPLAIN SELECT COUNT(*)
精確高頻：維護計數表 / Redis 快取

三、EXPLAIN 實戰分析

關鍵欄位解讀

EXPLAIN SELECT * FROM users WHERE name = 'John'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref          -- 關鍵：system > const > eq_ref > ref > range > index > ALL
possible_keys: idx_name
          key: idx_name    -- 實際使用索引
      key_len: 152
          ref: const
         rows: 1          -- 預估掃描行數
     filtered: 100.00
        Extra: Using index -- 覆蓋索引

type 值效能排序 (好→壞)

system/const：常數查找、主鍵/唯一鍵等值
eq_ref：JOIN 關聯主鍵/唯一鍵
ref：普通索引等值查找
range：範圍查詢 (>, <, BETWEEN, IN)
index：全索引掃描
ALL：全表掃描 ❌

Extra 關鍵提示

Using index：覆蓋索引 ✅
Using where：Server 層過濾
Using temporary：建立臨時表 ⚠️
Using filesort：外部排序 ⚠️ (大數據極慢)
Using join buffer：Join Buffer 使用

四、資料型別選擇影響效能

原則	說明
越小越好	TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT
整數優於字串	比較快、索引小、Join 快
固定長度優於可變	CHAR vs VARCHAR (但 CHAR 浪費空間)
避免 NULL	NOT NULL 預設值，NULL 需額外位元組、索引複雜
ENUM 替代短字串	內部存整數、極省空間
TIMESTAMP vs DATETIME	TIMESTAMP 4 bytes、自動時區、範圍較小