PHP 開發必讀：MySQL 效能優化全方位策略綜述（完整版）

PHP 開發必讀：MySQL 效能優化全方位策略綜述（完整版）

本文為完整深度版，精簡版請參考文章 172791。內容涵蓋 SQL 微優化、表設計、索引原理、查詢分析工具鏈、主從讀寫分離、分庫分表架構演進。

一、SQL 撰寫微優化（好習慣積少成多）

1. 關鍵字大寫：MySQL 解析器內部轉大寫，手寫省微小開銷，更利閱讀。
2. 重置自增 ID：ALTER TABLE t AUTO_INCREMENT = N，避免 BIGINT 耗盡或業務需求。
3. ZEROFILL 補零：INT(5) ZEROFILL 顯示 00001，僅影響顯示，存儲不變。
4. 大量導入三部曲：DROP INDEX → LOAD DATA → ADD INDEX，避免逐行 B+樹維護，速度提升 10 倍+。
5. SQL 腳本版控：.sql 檔納入 Git，災難恢復：mysql -u user -ppass db < backup.sql（-p 緊接密碼）。

二、資料庫設計層優化

1. 範式與反範式平衡

• 遵循 3NF 消除冗余異常。
• 高頻 JOIN 欄位（如訂單表冗存 user_name）、統計計數器（如 article.view_count）適度冗余，用觸發器/應用層同步。

2. 資料型選擇鐵律

INT > DATE/TIME > ENUM/CHAR > VARCHAR > BLOB/TEXT

• IPv4：INT UNSIGNED 存 INET_ATON(ip)，4 字節 vs 15 字節，範圍查詢極快。
• 狀態碼：TINYINT 或 ENUM，避免 VARCHAR。
• 金額：DECIMAL(19,4) 或 BIGINT 存分（乘 100），嚴禁 FLOAT/DOUBLE。
• CHAR(n) 固定長度，n 取最小；VARCHAR 變長但有 1-2 字節長度前綴。

3. 表分區策略

• PARTITION BY RANGE (YEAR(created_at))：歷史數據歸檔、分區裁剪。
• PARTITION BY HASH (user_id)：均勻分佈熱點。
• 分區數建議 ≤ 100，過多元數據字典開銷大。

4. 引擎選型決策表

三、索引優化核心原則（InnoDB 為主）

1. 聚簇索引機制

• 主鍵即數據順序（B+樹葉子節點存完整行）。
• 主鍵選型：自增 BIGINT > 有序 UUID > 無序 UUID/字串。無序主鍵導致頁分裂、碎片、寫入放大。
• 次級索引葉子存主鍵值 → 查詢需「回表」。覆蓋索引（索引包含所有查詢列）可避免回表。
• 大字段（TEXT/BLOB）溢出頁存儲，主鍵查詢需跳過溢出頁，影響範圍掃描效能。

2. 聯合索引與最左前綴

• 多條件查詢必須建聯合索引，單列索引優化器只能選一個（index merge 例外但效率低）。
• B+樹按索引列順序排序：INDEX idx_a_b_c (a, b, c) 先按 a，再 b，再 c。
• 查詢條件必須包含最左列才能用到索引；中間列缺失後續列失效。
• 範圍查詢（>, <, >=, <=, LIKE '%...'）後的列無法使用索引。

3. 索引長度與選擇性

• 字串列建前綴索引：INDEX idx_name (name(10))。
• 選擇性測試：SELECT COUNT(DISTINCT LEFT(name, 10))/COUNT(*) FROM t，目標 > 0.8。
• 極端相同前綴（如 URL、身份證）可反轉字串存儲（REVERSE(url)）再建索引。

4. 索引維護

• OPTIMIZE TABLE t 或 ALTER TABLE t ENGINE=InnoDB 重建，消除碎片、更新統計信息。
• 線上 DDL (5.6+)：ALGORITHM=INPLACE, LOCK=NONE 減少鎖定時間。

四、查詢分析工具鏈：定位 → 分析 → 優化

1. 慢查詢日誌

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

分析：mysqldumpslow -s t -t 20 /var/log/mysql/slow.log（按耗時 Top 20）。

2. EXPLAIN 核心欄位深度解讀

3. PROFILE 微觀剖析

SET profiling = 1;
-- 執行目標查詢
SHOW PROFILES;
-- 找到 Query_ID
SHOW PROFILE CPU, BLOCK IO, MEMORY FOR QUERY ;

定位 CPU、IO、內存熱點，指導索引/結構調整。

五、大型存儲架構演進

1. 主從複寫與讀寫分離

• Master Binlog (ROW/MIXED) → Slave Relay Log → SQL Thread Replay。
• GTID (5.6+) 簡化故障切換：CHANGE MASTER TO MASTER_AUTO_POSITION=1。
• 半同步複寫 (5.5+)：rpl_semi_sync_master=1，主庫等待至少一個 Slave 收到 Binlog 再提交，強一致性。
• 讀寫分離中介：ProxySQL（推薦，支援查詢路由、熔限、緩存）、MaxScale、Atlas。

2. 分庫分表

• 全局 ID：Snowflake 演算法、數據庫號段模式、UUID。
• 遷移雙寫方案：舊庫寫入同步新庫 → 校驗一致 → 切流量 → 下線舊庫。

六、快取層協同

• Redis：熱點數據、Session、排行榜、分散式鎖、延遲隊列。
• Memcached：純 KV 緩存、多線程、LRU 淘汰。
• 策略：Cache-Aside（旁路緩存）、Read/Write Through、Write Behind。緩存穿透/擊穿/雪崩防禦：布隆過濾器、互斥鎖重建、隨機過期時間。

七、總結：優化閉環

1. 監控發現/慢查詢日誌 → 定位 Top SQL
2. EXPLAIN + PROFILE → 根因分析（索引缺失、統計信息過期、SQL 寫法、架構瓶頸）
3. 方案實施：索引調整 / SQL 改寫 / 表結構變更 / 架構擴展 / 快取介入
4. 驗證效果：同環境壓測、線上灰度、監控指標對比
5. 知識沉澱：優化案例庫、團隊檢查清單、自動化巡檢腳本

每個優化點均可深入展開為專題，建議建立團隊《MySQL 優化實戰手冊》，持續迭代。