MySQL 性能優化實戰清單：從參數調優到索引重構的 12 條核心建議

MySQL 性能優化實戰清單：從參數調優到索引重構的 12 條核心建議

針對 8GB 記憶體典型生產主機，匯總 12 條經驗證有效的 MySQL 優化建議，分為「SQL 層實用優化」「InnoDB 核心參數」「Query Cache」「連接與網絡」「維護運維」五大維度，附帶 my.cnf 完整配置範例與監控指標腳本。

一、SQL 層實用優化

1. 關閉 autocommit 批量導入：

   SET autocommit=0;
   -- 大量 INSERT
   COMMIT;

2. 高頻查詢列加索引：權衡寫入性能，EXPLAIN 驗證 type=ref/range。
3. 單行查詢用 LIMIT 1：

   SELECT 1 FROM tbl WHERE cond LIMIT 1;

   SELECT 1 比 SELECT * 省去字典查詢，效率 1 > 列名 > *。
4. 每表必備無業務含義主鍵：BIGINT UNSIGNED AUTO_INCREMENT，避免業務鍵變更導致聚簇索引重構。
5. 批量插入多值語法：

   INSERT INTO t(a,b) VALUES (1,2),(3,4),(5,6);

   減少解析/網絡往返。

二、InnoDB 核心參數（8GB 記憶體基準）

[mysqld]
# 緩衝池 50-80% 物理內存
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_additional_mem_pool_size = 256M

# Redo Log 25% Buffer Pool，單文件 1G，組 2-4 個
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 256M

# 刷盤策略：折衷性能與安全
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 並發控制
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8

安全變更 innodb_log_file_size：

mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown=0;"
systemctl stop mysqld
rm -f /var/lib/mysql/ib_logfile*
systemctl start mysqld

三、Query Cache（5.7 已棄用，8.0 移除）

max_allowed_packet = 64M
max_connections = 2000
thread_cache_size = 100
open_files_limit = 65535
table_open_cache = 4000

五、監控指標腳本

[mysqld]
user = mysql
default-storage-engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# 基礎
datadir = /data/mysql
port = 3306
bind-address = 0.0.0.0
max_connections = 2000
max_allowed_packet = 64M
thread_cache_size = 100
open_files_limit = 65535
table_open_cache = 4000

# InnoDB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 二進制日誌
log_bin = /var/log/mysql/binlog
expire_logs_days = 7
sync_binlog = 1
binlog_format = ROW

# 其它
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION