MySQL 常見優化點歸納總結：索引、分頁、JOIN、資料型別四大維度

MySQL 常見優化點歸納總結：索引、分頁、JOIN、資料型別四大維度

一、索引相關

1. 查詢未用到索引

EXPLAIN 關注 type=ALL、key=NULL。

2. 索引欄位施加函數

-- 錯誤
WHERE TO_CHAR(gmt_created, 'mmdd') = '0101'
-- 正確
WHERE gmt_created BETWEEN TO_DATE('20090101','yyyymmdd') AND TO_DATE('20090102','yyyymmdd')

3. 索引欄位全模糊查詢

-- 無法用索引
WHERE member_id LIKE '%alibab%'
-- 可用索引
WHERE member_id LIKE 'alibab%'

任何 LIKE '%xxx%' 皆為不規範寫法，需靜態檢查攔截。

4. 多欄索引未用前導欄

索引 (member_id, group_id)，查詢 WHERE group_id=9234 **無法用到索引**。
原理：B+ 樹鍵值為欄位拼接字串，無前導欄無法定位。
解決：建立 (group_id, member_id)。

5. 訪問索引外欄位（回表）

索引 (member_id, subject)：

-- 僅掃索引
SELECT subject FROM offer WHERE member_id=234
-- 需回表
SELECT subject, gmt_created FROM offer WHERE member_id=234

高頻 SQL 若回表成本高 → 建立**覆蓋索引**。

6. COUNT(id) vs COUNT(*)

COUNT(id) 等價 COUNT(1) WHERE id IS NOT NULL
-- 無 id 索引時全表掃描
COUNT(*) 會選最優索引做快速全掃描
-- 統一使用 COUNT(*)

7. 善用 LIMIT 1 早期終止

-- 判斷是否存在
SELECT COUNT(*) FROM offer WHERE member_id=234 LIMIT 1
-- 優於無 LIMIT 版本（找到第一筆即停止）

二、高效分頁

1. 延遲關聯優化

-- 原始
SELECT * FROM blog_article WHERE domain_id=1 AND draft=0 ORDER BY gmt_created DESC LIMIT 2,3

-- 優化：先在索引取 ROWID，再 JOIN 回表
SELECT blog_article.* FROM (
  SELECT rowid, rownum AS rn FROM (
    SELECT rowid FROM blog_article WHERE domain_id=1 AND draft=0 ORDER BY gmt_created DESC
  ) t WHERE rownum >= 2
) a, blog_article WHERE a.rn <= 3 AND a.rowid = blog_article.rowid

2. ORDER BY 用不到索引的常見原因

3. 隨機查詢優化

-- 錯誤：全表掃描 + 臨時表
SELECT * FROM title WHERE kind_id=1 ORDER BY RAND() LIMIT 1;

-- 改進：偏移量法
SELECT ROUND(RAND() * COUNT(*)) FROM title WHERE kind_id=1;
SELECT * FROM title WHERE kind_id=1 LIMIT 1 OFFSET $random;

-- 最優：主鍵範圍查詢 (PK 需連續自增)
SELECT ROUND(RAND() * COUNT(*)) FROM title WHERE kind_id=1;
SELECT * FROM title WHERE kind_id=1 AND id > $random LIMIT 1;

三、高效 JOIN

1. 小表驅動大表
2. 避免子查詢，改寫為 JOIN

四、資料型別

1. 避免隱式轉換

CREATE TABLE `user` (
  `account` CHAR(11) NOT NULL,  -- 字串型
  UNIQUE KEY `username` (`account`)
) ENGINE=InnoDB;

-- 錯誤：數字參數導致隱式轉換，索引失效
EXPLAIN SELECT * FROM user WHERE account=123;  -- type=ALL

-- 正確：字串參數
EXPLAIN SELECT * FROM user WHERE account='123'; -- type=const, key=username

極常見錯誤：應用層傳入數字型別，資料庫欄位為字串。

2. 主鍵非自增列的缺點

• 插入效能差（隨機 I/O、頁分裂）
• 頁碎片多
• 二級索引儲存主鍵值，主鍵大 → 二級索引大 → 記憶體壓力

**強烈建議：InnoDB 表使用自增整數主鍵**。

💡 這份清單涵蓋 80% 線上慢 SQL 成因，建議納入 Code Review 清單與自動化靜態檢查規則。