MySQL 數據庫優化五步走：從硬件到參數調優的系統化指南

前言

MySQL 優化是系統工程，而非單點調參。本文遵循「硬件 → 架構 → 參數 → SQL → 維護」五步法，從根源解決效能瓶頸。

第一步：硬件層面瓶頸識別與規避

第二步：架構層面（以學校 Linux 環境為例）

1. 讀寫分離

主庫寫、從庫讀，利用 Proxy（ProxySQL/MaxScale）或代碼層路由。

2. 分庫分表

• 垂直拆分：業務模塊解耦（用戶庫、訂單庫、日誌庫）。
• 水平拆分：大表按 tenant_id、date_range、hash(id) 路由。

3. 緩存層

Redis 緩存熱點數據（用戶會話、商品詳情、計數器），降低 DB QPS 90%+。

第三步：關鍵服務器參數調優 (mysqld --help 查看所有)

[mysqld]
# 連接層
max_connections = 3000
back_log = 500
max_connect_errors = 100000

# 內存核心 (InnoDB)
innodb_buffer_pool_size = 12G      # 物理內存 60-70%
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G          # 大紅日志減少 checkpoint 壓力
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1 # 1=最安全, 2=高性能(秒級丟失)
innodb_flush_method = O_DIRECT     # 繞過 OS cache

# 查詢緩存 (8.0 已移除，改用 ProxySQL/Redis)
# query_cache_type = 0

# 臨時表、排序
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
read_rnd_buffer_size = 8M

# 連接、超時
interactive_timeout = 300
wait_timeout = 300
net_read_timeout = 30
net_write_timeout = 60

# 其它
open_files_limit = 65535
table_open_cache = 4000
table_definition_cache = 2000
max_allowed_packet = 64M
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

第四步：SQL 與索引優化（最高 ROI）

1. 慢查詢日志：slow_query_log=1, long_query_time=1，配合 pt-query-digest 分析。
2. EXPLAIN 計畫：關注 type=ALL/index、rows、Extra=Using temporary/filesort。
3. 索引策略：最左前綴、覆蓋索引、避免選擇性低欄位（性別、狀態）建單列索引。
4. 改寫技巧：IN→EXISTS、OR→UNION ALL、子查詢→JOIN、分頁優化（延遲關聯/游標式）。

第五步：日常維護與監控

• 定期 ANALYZE TABLE 更新統計信息。
• 在線 DDL (pt-online-schema-change) 避免鎖表。
• 監控指標：QPS、TPS、慢查詢數、連接數、Buffer Pool 命中率、主從延遲、磁盤 IOPS/利用率。
• 備份驗證、Binlog 清理策略、錯誤日志告警。

總結

優化順序決定成敗：硬件兜底 → 架構分流 → 參數匹配 → SQL 重構 → 維護保障。切勿上來就改參數、加索引，而忽略了架構層面的讀寫分離、分庫分表、緩存策略。系統化優化，方能長治久安。