MySQL 效能優化總結（一）：SQL_MODE、引擎選型、大表設計、優化參數模板

MySQL 效能優化總結（一）：SQL_MODE、引擎選型、大表設計、優化參數模板

系統性優化需從 SQL 模式、儲存引擎、大表架構、核心參數四維切入。本文匯總實戰精華與生產級配置模板。

1. SQL_MODE：嚴格模式防患未然

# 查看當前模式
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

# 生產建議嚴格模式（8.0 預設已含大部分）
STRICT_TRANS_TABLES,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_AUTO_VALUE_ON_ZERO,
NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,
NO_ZERO_IN_DATE,
ONLY_FULL_GROUP_BY

作用：拒絕非法日期、除零、隱式類型截斷、非聚合欄位亂用 GROUP BY，將資料品質問題攔截在寫入端。

2. MyISAM vs InnoDB 六大差異決策表

選型原則：寫多讀少、需事務/外鍵/高可用 → InnoDB；純讀、靜態報表、允許偶發不一致 → MyISAM（或 ClickHouse/Apache Doris 更佳）。

3. 千萬級大表設計十問

1. 未來 1-3 年資料量、行大小？
2. 是否有大欄位（TEXT/BLOB），更新頻率？
3. WHERE/GROUP BY/ORDER BY 高頻欄位？
4. UPDATE/DELETE 高頻條件欄位？
5. SELECT : UPDATE+DELETE : INSERT 比例？
6. 日均 SQL 執行量級？
7. 讀多寫少或寫多讀少？
8. 物理架構：單機/主從/分庫分表？
9. 併發峰值 QPS？
10. 引擎選 InnoDB 還是 MyISAM？

回答清晰，表結構、索引、分區、中間件選型自然清晰。

4. IN vs EXISTS 優化鐵律

• 小表驅動大表：IN 優（子查詢結果集小）。
• 大表驅動小表：EXISTS 優（可利用外部表索引）。
• 核心：**小結果集驅動大結果集**。

5. ORDER BY 索引優化模式

-- 1. 純排序
ORDER BY col1, col2          → 索引 (col1, col2)
-- 2. WHERE + 排序
WHERE colX = ? ORDER BY sort → 索引 (colX, sort)
-- 3. 多欄位排序
WHERE uid=? ORDER BY x,y     → 索引 (uid, x, y) 遠優於 (x, y, uid)
-- 4. 失效場景
ORDER BY col1 ASC, col2 DESC  -- 混合排序
WHERE col2=? ORDER BY col1    -- WHERE 與 ORDER BY 索引列不符
ORDER BY col1, col2           -- 無 WHERE，但未用最左前綴

6. InnoDB 核心參數生產模板（64GB RAM、RAID 10、SSD、主從、全球化業務）

[mysqld]
# InnoDB
innodb_buffer_pool_size = 50G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 6000
innodb_lru_scan_depth = 2000

# Binlog / Replication
log-bin
sync_binlog = 1
sync_relay_log = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE
expire_logs_days = 10
binlog_format = ROW
transaction_isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2

# Misc
timezone = GMT
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY"
skip_name_resolve = 1
max_connect_errors = 100000
max_connections = 500

# Unique per server
server_id = 123

總結

優化是系統工程：SQL_MODE 守住資料品質、引擎選型匹配業務模式、大表設計預判增長、參數模板落地硬體紅利。建立基準測試、持續監控、定期複盤，才能持續演進。