MySQL 關鍵配置與優化參數深度解析

掌握核心配置參數，能從連線處理、記憶體分配、查詢優化、日誌審計等面向全方位提升 MySQL 效能。

1. 查看目前配置

# 客戶端
mysqladmin -u root -p variables

# 或登入 mysql
SHOW VARIABLES;
SHOW VARIABLES LIKE 'table_type';
SHOW STATUS;

2. 連線負載管理

• back_log：主執行緒忙碌時允許排隊的連線數，預設 50。過高無益，受 OS/網路裝置限制。
• max_connections：最大並發連線數，預設 100。觀察 SHOW STATUS LIKE 'Max_used_connections'，若接近上限再調高。每連線消耗執行緒堆疊與緩衝區記憶體。

3. 資料目錄與儲存引擎

• datadir：指定資料目錄路徑（如掛載獨立磁碟 /data/mysql）。移動時需同步權限與 mysql 系統表。
• default-storage-engine / default-table-type：設定預設引擎（如 InnoDB、MEMORY）。

4. 啟動自動執行 SQL

• init_file：指定啟動時執行的 SQL 檔（如清理 Session 表）。
  mysqld_safe --init-file=/path/to/init.sql &

5. 慢查詢與無索引查詢審計

分析工具：mysqldumpslow -s c -t 10 /var/log/mysql/slow.log（按次數 Top 10）。

6. 網路與安全

• port：預設 3306，可改為非標準埠。
• skip-name-resolve：禁用 DNS 反解，授權表 Host 欄位只能用 IP/localhost，提升連線速度。
• skip-networking：完全禁用 TCP/IP，僅允許本機 Socket 連線（極高安全需求）。
• --user=mysql：以非 root OS 使用者啟動，降低被入侵後破壞範圍。

7. 記憶體相關關鍵參數（概覽）

• 全域共享：key_buffer_size（MyISAM 索引）、query_cache_size、innodb_buffer_pool_size（InnoDB 核心）。
• 每連線獨佔（按需分配）：sort_buffer_size、read_buffer_size、join_buffer_size、tmp_table_size。

優化原則

1. 先測量（SHOW STATUS、mysqldumpslow、EXPLAIN），再調參。
2. 一次只調一個參數，觀察效果。
3. 記憶體總用量 = 全域共享 + (每連線 × max_connections) < 物理記憶體 70-80%。
4. 優先優化 SQL 與索引，配置調校為輔助。