PHP 開發必讀：MySQL 效能優化全方位策略綜述

PHP 開發必讀：MySQL 效能優化全方位策略綜述

PHP 專案瓶頸多半在資料庫層。本文從 SQL 寫法、架構設計、索引策略、查詢分析、主從架構到分庫分表，系統梳理 MySQL 優化完整知識體系，助你精準定位並解決效能問題。

一、SQL 撰寫微優化

1. 關鍵字大寫：MySQL 內部會轉大寫，手寫省解析開銷（雖微小但養成好習慣）。
2. 重置自增 ID：ALTER TABLE t AUTO_INCREMENT = N 避免 ID 耗盡。
3. ZEROFILL 補零：INT(5) ZEROFILL → 00001，前導零顯示需求。
4. 大量匯入技巧：先 DROP INDEX → 載入資料 → ADD INDEX，避免逐行維護索引。
5. SQL 腳本管理：.sql 檔案版控，災難恢復用 mysql -u user -ppass db < file.sql（-p 緊接密碼無空格）。

二、資料庫設計層優化

1. 第三範式 (3NF) 為基準：適度反正規化（冗余）換查詢速度，如常用關聯欄位冗存。
2. 資料型別選擇優先級：INT > DATE/TIME > ENUM/CHAR > VARCHAR > BLOB/TEXT。
   例：IP 用 INET_ATON() 存 UNSIGNED INT，比 CHAR(15) 省空間且利於範圍查詢。
3. CHAR(n) 取最小 n：固定長度佔滿空間。
4. 表分區：PARTITION BY RANGE/LIST/HASH/KEY，RANGE 最常用（如按時間分區），實現分區裁剪。
5. 引擎選型：InnoDB vs MyISAM

結論：除純讀取、無事務、全文搜尋（需 Sphinx/ES）場景，預設選 InnoDB。

三、索引優化核心原則

1. InnoDB 聚簇索引機制：主鍵即數據順序。
   → 主鍵用有序自增 INT/BIGINT，避免 UUID/隨機字串導致頁分裂。
   → 次級索引存主鍵值，查詢需「回表」；覆蓋索引可避免回表。
2. MyISAM 非聚簇索引：所有索引指向物理行指標，插入較輕量但主鍵查詢無優勢。
3. 多條件查詢建聯合索引：單列索引優化器只能用一個。聯合索引遵循最左前綴原則（B+樹先按第一列排序，再第二列...）。
4. 索引長度權衡：ALTER TABLE t ADD INDEX idx_col(col(n)) 只索引前 n 字元。
   用 SELECT COUNT(DISTINCT LEFT(col, n))/COUNT(*) FROM t 測覆蓋率，選接近飽和的 n。極端相同前綴可反轉字串存儲再建索引。
5. 索引碎片維護：OPTIMIZE TABLE t 或 ALTER TABLE t ENGINE=InnoDB 重建。

四、查詢層優化與分析工具鏈

1. 業務邏輯下沉 PHP：資料庫只做存取，複雜運算在應用層，降低 DB CPU 壓力。
2. 慢查詢日誌：

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

   分析工具：mysqldumpslow -s t -t 10 slow.log（按時間排序前 10）。
3. EXPLAIN 執行計劃：關鍵欄位解讀：

4. PROFILE 剖析資源消耗：

   SET profiling=1;
   -- 執行查詢
   SHOW PROFILES;
   SHOW PROFILE CPU, BLOCK IO FOR QUERY ;
   

五、大型存儲架構優化

1. 主從複寫與讀寫分離

• Master 寫 Binlog → Slave IO 線程拉取寫 Relay Log → SQL 線程重放。
• 配置核心：server-id 唯一、log-bin 啟用、GRANT REPLICATION SLAVE、CHANGE MASTER TO ...、START SLAVE。
• 讀寫分離需中介軟體：mysql-proxy、Atlas、ProxySQL 等，將 SELECT 路由至 Slave。

2. 分庫分表

• 垂直切分：業務模組拆分到不同實例（如用戶、訂單、商品分庫）。
• 水平切分：單表過大，按 Hash/Range 拆分為多表/多庫（如 user_id % 10）。
• 聯合切分：垂直+水平矩陣式分佈。
• 區別於分區：分表跨實例/磁碟，分區單實例內邏輯劃分。

六、總結：優化路徑圖譜

1. 慢查詢日誌 → 定位問題 SQL
2. EXPLAIN → 分析執行計劃、索引使用
3. PROFILE → 細粒度資源分析
4. 索引調優（建立/修改/覆蓋/長度）
5. SQL 改寫（關聯/子查詢/分頁/條件下推）
6. 架構擴展（讀寫分離/分庫分表/快取層 Redis/Memcached）

每個優化點都可深入展開，建議建立團隊優化檢查清單，持續迭代。