開源數據庫 MySQL 優化秘籍：從系統瓶頸到查詢重構的系統性指南

優化核心認知

數據庫優化非單點調參，而是「系統理解 → 瓶頸定位 → 針對性優化」的系統工程。最關鍵因素是基礎架構設計（表結構、索引、分區、分庫分表），其次才是參數調優。

一、系統級瓶頸四大類別

二、MySQL 架構固有限制與應對

1. MyISAM 表鎖並發缺陷

MyISAM 採用表級鎖：多讀單寫，長時間 SELECT 會阻塞 INSERT/UPDATE/DELETE。
對策：核心業務表遷移至 InnoDB（行鎖、MVCC）；唯讀/歸檔表可保留 MyISAM/Archive。

2. 非事務表行為差異（嚴格模式關鍵）

• 默認模式下：非法值被「靜默截斷/轉換」（數字溢出→0/邊界值，字符串過長→截斷，除零→NULL），不報錯。
• 啟用 sql_mode=STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE 強制報錯，將數據質量前置到寫入環節。
• 應用層必須做輸入驗證，不可依賴數據庫做業務合法性檢查。

三、應用設計可移植性與性能平衡

1. 標準 SQL 優先：避免 MySQL 專有語法（REPLACE INTO、INSERT IGNORE、LIMIT n,m 分頁），使用 ANSI SQL 92/99 標準寫法。
2. /*! ... */ 條件註釋：MySQL 執行，其他庫視為註釋忽略，可用於特定優化 Hint（如 /*! MAX_EXECUTION_TIME(1000) */）。
3. 應用層緩存策略：熱點數據 Redis/Memcached 緩存，TTL+主動失效雙保險；MySQL 8.0+ Query Cache 已移除，不再依賴內置查詢緩存。
4. 緩存預熱與降級：啟動腳本預熱核心熱點，緩存失效時熔斷降級讀庫，防止緩存雪崩。

四、SELECT 查詢優化實戰體系

1. 權限系統開銷最小化

複雜 GRANT（表級、列級權限、資源限制）會增加每次查詢的權限檢查開銷。
建議：生產環境僅使用庫級/全局權限，避免 tables_priv、columns_priv、user_resources 表被讀取。

2. BENCHMARK() 微基準測試

SELECT BENCHMARK(1000000, MD5('test_string'));
-- 測試 MD5 百萬次調用耗時，用於函數/表達式性能對比

適用於函數選型（如 UUID() vs MD5(RAND())）、表達式優化驗證。

3. 核心優化清單（生產環境必查）

1. EXPLAIN 分析執行計劃：關注 type（目標 ref/range/eq_ref）、key（實際用到的索引）、rows（預估掃描行）、Extra（Using index/Using where/Using temporary/Using filesort）。
2. 覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表。
3. 索引下推 (ICP)：5.6+ 在存儲引擎層過濾 WHERE 條件，減少回表次數。
4. 避免隱式轉換：WHERE varchar_col = 123 導致全表掃描，統一類型或顯式 CAST。
5. 分頁優化：深分頁 LIMIT 100000,20 改為延遲關聯 SELECT * FROM t INNER JOIN (SELECT id FROM t WHERE cond LIMIT 100000,20) tmp USING(id)。
6. COUNT(*) 優化：MyISAM 直接讀表元數據 O(1)；InnoDB 需掃描，可維護計數表或用 EXPLAIN 預估行數近似值。
7. 子查詢優化：5.6+ 半連接/物化優化，但複雜子查詢仍建議改寫為 JOIN 或 CTE（8.0+ 支持）。

五、配置參數調優優先級（InnoDB 為例）

六、監控與持續優化閉環

1. 採集指標：QPS、TPS、慢查詢數、鎖等待、緩存命中率、磁盤 IOPS/延遲、主從延遲。
2. 慢查詢日誌 long_query_time=0.5（微秒級），配合 pt-query-digest 定期分析 TOP 慢查詢。
3. 定期 ANALYZE TABLE 更新統計信息（5.6+ 自動統計觸發機制已改進，但大表變更後建議手動觸發）。
4. Schema 變更走 gh-ost/pt-online-schema-change 在線 DDL，避免鎖表阻塞業務。