MySQL 效能優化十大實戰技巧：從架構規劃到 SQL 重寫與參數調校

MySQL 效能優化核心清單 (開發/架構/運維通用)

優化是系統工程，由大到小：架構 → 索引 → SQL → 參數 → 硬體。以下十大技巧覆蓋全鏈路。

1. 架構先行：讀寫分離、分庫分表、快取層

• 早期規劃：預估 QPS、資料增長、可用性需求。
• 主寫從讀：ProxySQL/MaxScale/中間件路由，減輕主庫壓力。
• 分片：按 tenant_id / user_id / 日期範圍水平拆分，避免單表超 2000 萬行。
• 多級快取：Redis (熱點資料) → 本地快取 (Guava/Caffeine) → Query Cache (已棄用)。
• 效能 ≠ 可擴展性：單機優化有上限，架構決定上限。

2. 善用 EXPLAIN：優化器的 X 光片

• EXPLAIN SELECT ... 檢視 type (目標 ≥ ref)、key (實際用索引)、rows (預估掃描行)、Extra (Using filesort/temporary/index)。
• 8.0+ 用 EXPLAIN ANALYZE 看實際耗時與行數。
• 每次上線複雜 SQL 必跑 EXPLAIN，納入 CI/CD 檢查。

3. 選對資料型別：越小越快

• 整數：TINYINT (1B) < SMALLINT (2B) < MEDIUMINT (3B) < INT (4B) < BIGINT (8B)。
• 字串：固定長用 CHAR，變長用 VARCHAR，避免 TEXT/BLOB 存短字串。
• 時間：TIMESTAMP (4B, 1970-2038) vs DATETIME (8B, 1000-9999) vs INT Unix stamp (4B)。
• 金額：DECIMAL(19,4) 定點數，嚴禁 FLOAT/DOUBLE。
• IPv4：INT UNSIGNED 存 INET_ATON() 結果。

4. 持久連線：減少握手開銷

• PHP mysqli::persistent / Java HikariCP / Go db.SetMaxOpenConns 連線池複用。
• 注意 max_connections、wait_timeout、interactive_timeout 配合調整，避免連線洩漏打滿上限。
• 跨機房/雲資料庫延遲高時，持久連線收益更明顯。

5. 理解 Query Cache：雙刃劍 (5.7 棄用、8.0 移除)

• 命中機制：SQL 文本完全匹配 (大小寫/空格/註解敏感)。
• 寫入即失效：表級鎖，高寫入場景鎖爭用嚴重。
• 生產建議：query_cache_type=0 關閉，或 =2 DEMAND + SQL_CACHE 精準控制。
• 替代方案：Redis 快取熱點查詢結果，應用層控制失效策略。

6. 別在索引欄上用函數：讓索引生效

-- ❌ 殺索引：函數作用於欄位
WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) <= 7

-- ✅ 索引友善：運算移至常數側
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
-- 或應用層計算好常數
WHERE create_time >= '2024-01-01 00:00:00'

• 隱式轉型也會殺索引：WHERE varchar_col = 123 → WHERE varchar_col = '123'。
• LIKE '%abc%' 導致全掃，前綴 LIKE 'abc%' 可用索引。

7. 精通 SQL 重寫：化繁為簡

• 子查詢 → JOIN (Derived Table 物化開銷大)。
• IN (SELECT ...) → EXISTS (SELECT 1 ...) / JOIN。
• OR 拆分為 UNION ALL (各支路用各自索引)。
• 游標/迴圈 → 集合操作 (單條 SQL 批次處理)。
• SELECT * → 明確列出欄位，減少網路/IO/記憶體，利於覆蓋索引。

8. 善用 ON DUPLICATE KEY UPDATE：單條 SQL 完成 UPSERT

INSERT INTO wordcount (word, cnt) VALUES ('mysql', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
-- 省去 SELECT 判斷存在性 + INSERT/UPDATE 兩條語句 + 事務開銷

• 唯一鍵衝突時自動轉更新，原子性、無競態。
• 配合 LAST_INSERT_ID(expr) 可獲取自增 ID 或自定義值。

9. 監控慢查詢：持續優化的數據來源

• 開啟：slow_query_log=ON, long_query_time=1 (秒), log_queries_not_using_indexes=ON。
• 分析工具：pt-query-digest (Percona Toolkit) 聚合分析 Top N 慢查詢。
• 定期審視：每週跑一次報告，針對性加索引/改 SQL/調參數。

10. 關鍵參數基線配置 (InnoDB、8GB 記憶體範例)