MySQL 效能優化 19 大核心要點：從查詢緩存到架構選型

MySQL 效能優化 19 大核心要點：從查詢緩存到架構選型

系統整理 19 項 MySQL 效能優化核心原則，涵蓋 SQL 改寫、索引設計、架構配置、監控排查，附帶實測對比與避坑指南。

1. 善用查詢緩存 (Query Cache) —— 5.7 已棄用，8.0 移除

原理：相同 SELECT 結果快取在記憶體，後續直接返回。

-- 失效場景：非確定性函數
SELECT * FROM user WHERE signup_date >= CURDATE();  -- 不快取

-- 優化：應用層計算日期
$today = date('Y-m-d');
SELECT * FROM user WHERE signup_date >= '$today';  -- 可快取

現狀：MySQL 5.7 棄用，8.0 移除。建議用 Redis/應用層快取替代。

2. EXPLAIN 分析執行計畫

EXPLAIN SELECT u.username, g.group_name 
FROM users u JOIN groups g ON u.group_id = g.id;
-- 關注：type=ref/eq_ref、key=idx_group_id、rows=預估掃描行

關鍵欄位：`type` (系統→const→eq_ref→ref→range→index→ALL)、`key` (實際用索引)、`rows` (預估掃描)、`Extra` (Using index/where/filesort/temporary)。

3. 單行結果加 LIMIT 1

-- 低效
SELECT * FROM user WHERE country = 'China';

-- 高效 (找到 1 筆即停止)
SELECT 1 FROM user WHERE country = 'China' LIMIT 1;

4. 搜尋欄位建立索引

WHERE、JOIN、ORDER BY、GROUP BY 涉及欄位評估建索引。避免過度索引 (寫入放大)。

5. JOIN 欄位類型一致 + 索引

• 關聯欄位資料型別、長度、字元集、排序規則完全一致
• 雙方都建立索引
• 小表驅動大表 (優化器通常自動處理)

6. 禁用 ORDER BY RAND()

-- 極慢：全表掃描 + 臨時表 + 檔案排序
SELECT * FROM table ORDER BY RAND() LIMIT 10;

-- 優化：應用層隨機 ID 或兩步查詢
SELECT MAX(id) FROM table;  -- 應用層隨機生成 ID
SELECT * FROM table WHERE id >= $rand_id LIMIT 10;

7. 避免 SELECT *

• 增加網路傳輸、磁碟 IO、記憶體佔用
• 覆蓋索引失效
• Schema 變更影響面大

8. 每表設定單一自增整數主鍵

• InnoDB 聚簇索引 = 主鍵，主鍵越小二級索引越小
• 業務鍵 (email/手機) 改用唯一索引
• UUID/雪花算法 ID 導致頁面分裂，慎用

9. ENUM 替代固定值 VARCHAR

儲存為 TINYINT，壓縮空間、比較快。適用：性別、狀態、類型、部門等固定枚舉。

10. PROCEDURE ANALYSE() 獲取結構建議

SELECT * FROM table PROCEDURE ANALYSE();
-- 建議：INT→MEDIUMINT、VARCHAR→ENUM 等 (需有實際資料才準確)

11. 儘量用 NOT NULL

• NULL 需額外位元組標記 (MyISAM 每欄 1 bit)
• 比較邏輯複雜 (NULL != NULL、NULL > 1 皆為 NULL)
• 索引統計準確度較高

12. IP 地址存 UNSIGNED INT

-- 存入
INSERT INTO ip_log (ip) VALUES (INET_ATON('192.168.1.1'));
-- 查詢範圍
SELECT * FROM ip_log WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');
-- 顯示
SELECT INET_NTOA(ip) FROM ip_log;

13. 固定長度表 (Fixed-row Format) 更快

• 無 VARCHAR/TEXT/BLOB → 固定行長 → 偏移量計算 O(1)
• 易於快取、重建
• 代價：空間浪費 (CHAR 按定長儲存)

14. 垂直分割 (Vertical Partitioning)

• 大表拆分：常用欄位放主表，大欄位/低頻欄位放副表
• 主表瘦身 → 更多行放入一頁 → 掃描更快
• 避免頻繁 JOIN 回副表

15. 拆分大量 DELETE/INSERT

-- 避免長鎖表阻塞
WHILE 1 DO
    DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000;
    IF ROW_COUNT() = 0 THEN LEAVE; END IF;
    DO SLEEP(0.05);  -- 釋放鎖讓其他查詢進入
END WHILE;

16. 越小的型別越快

• TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT
• DATE < DATETIME < TIMESTAMP
• 預留擴展空間 (ALTER TABLE 大表極慢)

17. 選擇正確儲存引擎

18. 謹慎使用持久連線 (Persistent Connections)

PHP `mysql_pconnect()`、Java 連線池預設持久。問題：連線洩漏、佔用 max_connections、閒置連線被防火牆斷開。建議：短連線 + 連線池，配合 `validationQuery`。

19. 子查詢改寫為 JOIN (經典優化)

-- 慢 (18.87s)：依賴子查詢執行計畫差
SELECT * FROM guang_deal_outs 
WHERE deal_id IN (SELECT id FROM guang_deals WHERE id = 100017151);

-- 快 (0.01s)：JOIN 使優化器選擇最佳路徑
SELECT a.* FROM guang_deal_outs a
INNER JOIN guang_deals b ON a.deal_id = b.id
WHERE b.id = 100017151;

-- EXPLAIN 對比關鍵差異：
-- 子查詢: select_type=DEPENDENT SUBQUERY, type=ALL (全表掃描外表)
-- JOIN:    select_type=SIMPLE, type=const/ref (索引查找)

總結：優化優先級矩陣