MySQL 常用優化參數與日常管理實戰指南

MySQL 常用優化參數與日常管理實戰指南

在 MySQL 的日常運維中，合理調整優化參數、管理二進制日誌、控制連接數等操作對資料庫性能與穩定性至關重要。本文將系統梳理常用優化參數設定、唯一性檢查控制、最大連接數調整，以及二進制日誌清理與自動過期策略的實戰操作。

一、 插入性能優化：concurrent_insert 與 UNIQUE_CHECKS

1. concurrent_insert 參數

concurrent_insert 控制 MyISAM 表在插入時是否允許並發。預設值為 AUTO，若需最大化插入吞吐量，可設為 2（始終允許在表尾並發插入）。

SHOW VARIABLES LIKE 'concurrent_insert';
SET GLOBAL concurrent_insert = 2;

2. 禁用唯一性檢查加速大量導入

大量導入數據時，唯一性檢查會顯著降低速度。可在導入前暫時關閉，導入完成後再開啟：

-- 禁用唯一性檢查
SET UNIQUE_CHECKS = 0;
-- 執行大量 INSERT 操作...
-- 重新啟用唯一性檢查
SET UNIQUE_CHECKS = 1;

可透過 SHOW VARIABLES LIKE 'unique_checks'; 確認當前狀態。

二、 連接數管理：max_connections

根據業務並發需求調整最大連接數，避免「Too many connections」錯誤：

SET GLOBAL max_connections = 1000;

建議配合 max_user_connections 限制單用戶連接數，防止單一應用佔用過多資源。

三、 二進制日誌清理實戰

1. 現狀排查

主從架構下，mysql-bin.* 檔案可能佔用大量磁碟空間。首先進入數據目錄查看佔用情況：

# 查看目錄大小
du -h -d 1
# 統計二進制日誌總大小
du -c -h mysql-bin*

2. 確認主從同步位置

在 Master 執行 SHOW MASTER LOGS; 與 SHOW MASTER STATUS;，在 Slave 執行 SHOW SLAVE STATUS\G，重點關注 Master_Log_File、Read_Master_Log_Pos、Slave_IO_Running、Slave_SQL_Running 等欄位，確保 Slave 已同步至最新日誌。

3. 安全刪除策略

⚠️ 切勿直接用 rm 刪除檔案，需使用 MySQL 原生命令：

• 按日誌編號刪除：PURGE MASTER LOGS TO 'mysql-bin.000215';（刪除該檔案之前的所有日誌）
• 按時間刪除：PURGE MASTER LOGS BEFORE '2024-01-15 00:00:00';
• 重置所有日誌（慎用）：RESET MASTER;（編號歸零，僅適用於初始化環境）

4. 自動過期配置

在 my.cnf 的 [mysqld] 區段添加：

expire_logs_days = 3

重啟服務或執行 SET GLOBAL expire_logs_days = 3; 生效。執行 SHOW VARIABLES LIKE 'expire%'; 驗證。

四、 關鍵路徑變數查詢

-- 數據目錄
SHOW VARIABLES LIKE 'datadir';
-- 二進制日誌相關路徑
SHOW VARIABLES LIKE 'log_bin%';

五、 總結建議

1. 大量導入前關閉 UNIQUE_CHECKS、FOREIGN_KEY_CHECKS，導入後立即開啟。
2. 定期監控 mysql-bin 佔用空間，配合 expire_logs_days 自動清理。
3. 清理前務必確認 Slave 同步位置，避免主從中斷。
4. 生產環境建議將二進制日誌與數據文件放在不同磁碟分區。