前言
PHP + MySQL 是 Web 開發的經典組合，但隨著流量增長，資料庫往往成為系統瓶頸。本文將優化手段分為 MySQL 層 (SQL、索引、架構、引擎) 與 PHP 層 (快取、編譯、輸出) 兩大維度，提供 30+ 具體可執行的優化項目，並標註優先級與適用場景，作為效能調校的系統化清單。
第一部分：MySQL 效能優化
■ 核心觀測與診斷 (優先級：P0 - 必做)
| 項目 | 工具/指令 | 關鍵指標 | 行動閾值 |
|---|
| 慢查詢日誌 | long_query_time=1, log_queries_not_using_indexes=ON | 查詢時間、掃描行數、未用索引 | > 1s 或未命中索引即記錄 |
| 執行計劃分析 | EXPLAIN / EXPLAIN ANALYZE (8.0.18+) | type、key、rows、Extra (Using filesort/temporary) | 非 const/ref/range、出現 filesort/temporary |
| 狀態變數監控 | SHOW GLOBAL STATUS | Handler_read_rnd_next (全掃)、Created_tmp_disk_tables、Slow_queries | 持續增長或佔比高 |
| InnoDB 監控 | SHOW ENGINE INNODB STATUS | Buffer Pool 命中率、鎖等待、死鎖、歷史清理 | 命中率 < 99%、鎖等待 > 10ms |
■ SQL 與索引優化 (優先級：P0 - 高收益)
- 避免 SELECT *：明確列出欄位，減少網路傳輸、覆蓋索引命中機率、避免未來 Schema 變更衝擊。
- WHERE 條件用索引欄位：
EXPLAIN 確認 type=ref/range、key 非 NULL。
- 複合索引遵循最左前綴：等值條件在前、範圍條件在後、排序欄位最後。
範例：WHERE a=1 AND b>2 ORDER BY c → 索引 (a, b, c) 或 (a, c) (視基數而定)。
- 避免隱式轉型導致索引失效：
WHERE varchar_col = 123 → 全表掃描。修正：WHERE varchar_col = '123'。
- LIKE 模糊查詢優化：
LIKE 'abc%' 可用索引；LIKE '%abc' / LIKE '%abc%' 無法用索引 → 改用全文索引或 Elasticsearch。
- OR 條件優化：
WHERE a=1 OR b=2 通常走全表掃描 → 改用 UNION ALL 分開查詢合併。
- 分頁優化 (深分頁問題)：
LIMIT 100000, 20 掃描 10 萬行 → 改用 延遲關聯 或 游標分頁 (WHERE id > last_id LIMIT 20)。
- 覆蓋索引：查詢欄位全部包含於索引中 (
Using index)，避免回表。
範例：SELECT id, name FROM users WHERE status=1 → 索引 (status, id, name)。
- 定期 OPTIMIZE / ANALYZE：見 維護語句指南。
- 避免在索引欄上使用函數/運算：
WHERE DATE(created_at) = '2024-01-01' → WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
■ 架構與引擎選擇 (優先級：P1 - 架構層面)
| 場景 | 推薦引擎 | 關鍵配置 |
|---|
| 核心交易、強一致性、行級鎖、事務 | InnoDB (預設) | innodb_buffer_pool_size = 物理記憶體 70-80%、innodb_flush_log_at_trx_commit=1、innodb_file_per_table=ON |
| 唯讀/極少寫入、全文搜尋、空間索引 | MyISAM | key_buffer_size、myisam_sort_buffer_size |
| 暫存資料、極高速讀寫、可接受重啟丟失 | MEMORY | max_heap_table_size、tmp_table_size |
| 日誌、歸檔、壓縮、僅追加寫入 | Archive | 無特殊調校 |
InnoDB 關鍵參數速查 (8.0, 32GB 記憶體範例)
[mysqld]
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 4G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_page_cleaners = 4
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
■ 分庫分表與讀寫分離 (優先級：P2 - 流量爆發時)
- 垂直拆分：業務模組解耦，不同庫部署不同實例 (用戶庫、訂單庫、日誌庫)。
- 水平分表：單表 > 2000 萬行或 > 20GB 時考慮。分片鍵選擇：用戶 ID、商家 ID、時間範圍。中間層推薦 ShardingSphere、MyCat、Vitess。
- 讀寫分離：一主多從，Proxy 層 (ProxySQL、MaxScale) 自動路由 SELECT 至從庫。注意：從庫延遲導致讀舊資料，關鍵業務強制走主庫。
■ 查詢快取與緩衝區 (優先級：P1 - 8.0 已移除 Query Cache)
- MySQL 5.7：
query_cache_type=DEMAND、query_cache_size=256M，搭配 SQL_CACHE/SQL_NO_CACHE 精準控制。
- MySQL 8.0：Query Cache 已移除，改用應用層快取或 ProxySQL Query Cache。
- InnoDB Buffer Pool 才是核心快取，務必配置足夠大小並監控命中率。
第二部分：欄位與資料型態優化 (優先級：P1 - 設計階段決定)
- 用最小足夠的型態：
TINYINT 代替 INT 存狀態、MEDIUMINT 代替 INT 存中等 ID (省 25%)。
- 優先 NOT NULL：每欄省 1 bit，優化器統計更準，避免
IS NULL 判斷開銷。
- 定長優於變長 (特定場景)：無 VARCHAR/TEXT/BLOB 時，
ROW_FORMAT=FIXED 加速定位；但會浪費空間，權衡後決定。
- 非多語系欄位用 latin1/ascii：Email、URL、IP、MD5、UUID 等純 ASCII 欄位，
CHARSET=latin1 節省 50-75% 空間 (vs utf8mb4)。
- IP 存無號整數：
INET_ATON()/INET_NTOA()，INT UNSIGNED 4 位元組 vs VARCHAR(45) 45 位元組，支援範圍查詢 (BETWEEN)。
- 金額用 DECIMAL(19,4) 或 BIGINT 分為單位：嚴禁 FLOAT/DOUBLE 存金額。
- Enum/Set 慎用：修改枚舉值需 DDL 鎖表，新增值需 ALTER TABLE；建議用 TINYINT + 應用層字典映射。
第三部分：PHP 層效能優化
■ 快取策略 (優先級：P0 - 立竿見影)
| 層級 | 技術 | 適用資料 | TTL 建議 | 關鍵指標 |
|---|
| Opcode 快取 | OPcache (PHP 內建) | 編譯後位元組碼 | 永久 (部署時清除) | 命中率 > 99% |
| 資料物件快取 | Redis / Memcached | 熱點查詢結果、Session、Token、計數器 | 秒~天 (業務決策) | 命中率 > 95%、P99 < 5ms |
| HTTP 快取 | Varnish / Nginx fastcgi_cache / CDN | 靜態/半靜態頁面、API 回應 | 分鐘~小時 | 命中率 > 90% |
| 本地快取 | APCu / PHP 陣列靜態變數 | 極熱配置、字典表、單機去重 | 請求生命週期~分鐘 | 無網路延遲 |
OPcache 生產推薦配置
opcache.enable=1
opcache.enable_cli=1
opcache.memory_consumption=512
opcache.interned_strings_buffer=64
opcache.max_accelerated_files=20000
opcache.revalidate_freq=0 ; 生產 0 需部署時重啟 PHP-FPM
opcache.validate_timestamps=0
opcache.save_comments=1
opcache.fast_shutdown=1
■ 輸出與傳輸優化
- 啟用 Gzip/ Brotli 壓縮：Nginx
gzip on; gzip_types text/css application/json application/javascript; 或 brotli on;。
- 靜態資源 CDN + 長期快取：
Cache-Control: public, max-age=31536000, immutable，檔名帶 Hash 版本號。
- 頁面靜態化：商品詳情、文章頁、列表首頁定時生成靜態 HTML，Nginx 直接服務，繞過 PHP-FPM。
- 減少 HTTP 請求：CSS/JS 合併、Sprite 圖、內聯關鍵 CSS。
■ 程式碼層面優化
- 避免 N+1 查詢：批次載入關聯資料 (
WHERE id IN (...)) 或 Eager Loading (Laravel with(), Doctrine join)。
- 資料庫連線池：Swoole/RoadRunner 長連線模式，或 PHP-FPM
mysqli::persistent / PDO ATTR_PERSISTENT (需配合 wait_timeout 調整)。
- 大量寫入用批次 INSERT：
INSERT INTO t (a,b) VALUES (1,2),(3,4)... 單次 1000-5000 行，配合 LOAD DATA INFILE 更快。
- 異步處理耗時任務：發送郵件、推播、報表生成 → 推入 Queue (Redis/RabbitMQ) → Worker 消費。
第四部分：優化執行清單 (Checklist)
| 階段 | 動作項目 | 驗收標準 | 負責角色 |
|---|
| 上線前 | 所有核心 SQL EXPLAIN 驗收 | 無 ALL、無 filesort、無 temporary、索引命中 | DBA + RD |
| 上線前 | 慢查詢閾值設為 1s 並跑壓測 | 慢查詢數 = 0 | DBA |
| 上線後 Day 1 | 啟用慢查詢日誌、監控儀表板 | Grafana 儀表板上線 | DBA |
| 每週 | 檢視 Top 10 慢查詢、索引使用率 | 優化或標記風險 | DBA |
| 每月 | 大表碎片評估、OPTIMIZE 排程 | Data_free < 20% | DBA |
| 每季 | 全量備份恢復演練、參數複盤 | RTO < 30min、RPO = 0 | DBA + Ops |
結語
效能優化是系統工程，而非單點技巧。建立 觀測 → 診斷 → 優化 → 驗證 → 固化 的閉環流程，將優化項目納入開發規範與上線清單，才能從根本上解決 PHP + MySQL 架構的擴展性挑戰。本文清單可作為團隊內部優化標準的基準版本，請根據業務實況裁剪調整。
