MySQL SQL 優化十大核心原則：從定位瓶頸到索引重構的系統化方法論

優化思路四部曲（心法）

1. 優化最需要優化的 Query：高併發低消耗 > 低併發高消耗。單次省 2 IO × 10,000 QPS = 省 20,000 IO/時，遠大於單次省 2,000 IO × 10 QPS。
2. 定位性能瓶頸：IO bound？CPU bound？鎖等待？網路？用 PROFILING（5.0/5.1.30+）或 EXPLAIN、PERFORMANCE_SCHEMA 精準定位。
3. 明確優化目標：降低延遲？提高吞吐？減少鎖衝突？無目標不優化。
4. 從 EXPLAIN 入手：看 type、key、rows、Extra（Using index、Using where、Using temporary、Using filesort）。

優化原則六大鐵律（招式）

實戰案例：高併發 Query 微調效果放大

-- 原始：全表掃描 + filesort
SELECT * FROM orders WHERE user_id=123 ORDER BY created_at DESC LIMIT 20;

-- 優化：複合索引覆蓋
ALTER TABLE orders ADD INDEX idx_uid_created (user_id, created_at DESC);
-- EXPLAIN 顯示 type=ref, key=idx_uid_created, Extra=Using index

優化前：rows=500000, Using where; Using filesort
優化後：rows=20, Using index
併發 500 QPS 時，CPU 從 85% 降至 12%，延遲從 800ms 降至 3ms。

工具箱速查

• EXPLAIN FORMAT=JSON（5.6+）：輸出成本估算與優化器決策細節。
• OPTIMIZER_TRACE：SET optimizer_trace="enabled=on"; 追蹤為何不走索引。
• sys.schema_table_statistics / sys.schema_index_statistics：熱點表/索引一覽。
• pt-query-digest（Percona Toolkit）：慢查詢日誌聚合分析，找出 Top N 消耗資源語句。

避坑指南

• ❌ 盲目加索引 → 寫入放大、優化器選錯索引。
• ❌ 生產環境直接 ALTER TABLE 大表 → 鎖表停服務（用 pt-online-schema-change 或 8.0 Instant DDL）。
• ❌ 忽略統計資訊過期 → ANALYZE TABLE 定期執行。
• ✅ 建立變更管理流程：SQL 審核 → 測試環境驗證 → 灰度發布 → 全量釋出。

優化是「減法藝術」：減少掃描行、減少回表、減少排序、減少鎖、減少網路往返。每一次 EXPLAIN 都是與優化器的對話，聽懂它的語言，才能寫出極致 SQL。