MySQL my.ini 配置文件核心參數詳解

MySQL 的配置文件 my.ini（Windows）或 my.cnf（Linux）掌控著資料庫的效能、記憶體使用、連線處理等核心行為。預設配置多為英文，參數眾多，初學者往往只知皮毛。本文將常見的 [mysqld]、[client]、[mysqldump] 等區塊的關鍵參數進行分類解說，並附上建議調整方向，幫助你根據實際硬體資源與業務場景進行優化。

基礎路徑與字元集設定

[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = F:/phpecorg/MySql Server 51
datadir = F:/phpecorg/MySql Server 51/data
default-character-set = utf8

• basedir：MySQL 安裝根目錄。
• datadir：資料檔案實際存放目錄（必須指向 data 目錄）。
• default-character-set：伺服器預設字元集，建議統一使用 utf8mb4 以支援完整 Unicode（含 Emoji）。

連線與執行緒管理

back_log = 50
max_connections = 100
thread_cache_size = 8
thread_concurrency = 16

• back_log：作業系統監聽佇列長度，若頻繁出現「拒絕連線」可適當增大。
• max_connections：最大同時連線數。高併發場景需調大，但受限於作業系統開啟檔案數上限。
• thread_cache_size：緩存可重用的執行緒數，減少頻繁建立/銷毀執行緒的開銷。
• thread_concurrency：建議設為 CPU 核心數 × 2（或 × 0.7~0.8），協助作業系統調度。

緩衝區與快取參數（效能關鍵）

table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 32M
query_cache_size = 64M
query_cache_limit = 2M
tmp_table_size = 64M

• table_open_cache：所有執行緒共享的開啟資料表快取數，避免頻繁開關 .frm 檔案。
• max_allowed_packet：單次查詢/回應的最大封包大小，處理大型 BLOB/TEXT 或批次插入時必須調大。
• sort_buffer_size / join_buffer_size：每個執行緒獨享，用於 ORDER BY、GROUP BY 及無索引 JOIN。設太大會導致記憶體耗盡，建議 2~8MB 視記憶體而定。
• query_cache_size：查詢快取總大小。MySQL 5.7+ 已棄用，8.0 移除；若使用舊版本且讀多寫少可開啟，否則設為 0。
• tmp_table_size / max_heap_table_size：記憶體臨時表上限，超過則轉為磁碟 MyISAM 表，拖慢效能。

InnoDB 核心參數（重中之重）

default-storage-engine = InnoDB
innodb_buffer_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1

• innodb_buffer_pool_size：最關鍵參數。專用機建議設為實體記憶體 60%~75%。它快取資料、索引、鎖、插入緩衝等，直接決定讀寫速度。
• innodb_log_file_size：Redo Log 大小。較大可減少 checkpoint 頻率，提升寫入效能；修改需先停庫並刪除舊 ib_logfile*。
• innodb_flush_log_at_trx_commit：1 最安全（每次 commit 刷盤），0/2 損耗較小但可能丟失 1 秒資料。
• innodb_file_per_table：獨立表空間（每表一個 .ibd），便於單表備份、回收空間、攜帶表移動。

MyISAM 相關（漸少使用，但仍需基礎配置）

key_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 24M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M

即使主力用 InnoDB，系統表、臨時表仍可能用到 MyISAM，key_buffer_size 不宜設為 0。

慢查詢與二進制日誌

slow_query_log = 1
long_query_time = 1
log-slow-queries = "d:/logs/slow_query.log"
log-queries-not-using-indexes = 1
log-bin = mysql-bin
binlog_format = mixed
server-id = 1

• 開啟慢查詢日誌，設定閾值（如 1 秒），並記錄未用索引的查詢，是 SQL 優化的起點。
• binlog_format = mixed 兼顧 Statement 與 Row 模式優點，主從複制更安全。
• server-id 在複制拓撲中必須唯一。

用戶端與工具區塊

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

quick 讓 mysqldump 逐行讀取而非全載入記憶體，導出大表必備。

常用額外參數速查表

參數	說明
lower_case_table_names	1=表名不分大小寫（Windows 預設），0=區分大小寫（Linux 預設）
character-set-server	伺服器級預設字元集（優先於 default-character-set）
collation-server	預設排序規則
skip-name-resolve	禁用 DNS 反解，避免連線延遲，僅用 IP 做權限匹配
sql_mode	嚴格模式建議：STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
max_allowed_packet	客戶端/伺服器通訊緩衝區上限，必須大於最大 BLOB

調優建議總結

1. 先調 innodb_buffer_pool_size、innodb_log_file_size、max_connections 三大項。
2. 監控 Threads_created、Opened_tables、Innodb_buffer_pool_wait_free 等狀態值，動態微調。
3. 修改配置後必須 重啟 MySQL 服務 才生效（部分參數可用 SET GLOBAL 即時生效，但重啟後失效）。
4. 生產環境變更前務必備份配置檔，並在測試環境驗證。