MySQL 是 Web 後端最流行的關係型資料庫,但預設配置遠未發揮硬體效能。本文梳理 11 大調優維度,從存儲引擎、記憶體、磁碟、索引、SQL 寫法到架構層面,助你將資料庫效能提升數倍至數百倍。
innodb_buffer_pool)。FULLTEXT)略優,但 5.6+ InnoDB 已支援。
mysql -u user -p -e "SHOW TABLES IN dbname;" | tail -n +2 \
| xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter.sql
# 若有全文索引表需保留 MyISAM,手工修正 alter.sql
mysql -u user -p dbname < alter.sql
[mysqld]
innodb_file_per_table=1
避免 ibdata1 無限增大,便於單表回收空間(OPTIMIZE TABLE)。
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
關鍵:Innodb_buffer_pool_pages_free = 0 表示池已滿,需擴容。
innodb_buffer_pool_size = 物理記憶體 70-80%。Free > 0。innodb_additional_mem_pool_size = BufferPool/200、innodb_max_dirty_pages_pct = 80。生成掃描所有索引的 SELECT 腳本,啟動後執行將數據載入 Buffer Pool:
# 1. 生成腳本
mysql -uroot -AN < MakeSelectQueriesToLoad.sql > SelectQueriesToLoad.sql
# 2. 啟動/備份前執行
mysql -uroot < SelectQueriesToLoad.sql > /dev/null 2>&1
專用 DB:swapoff -a 或 vm.swappiness=1。共用機:鎖定記憶體 memlock unlimited(需 ulimit -l unlimited)。
mysqlcheck -o 只會讓 ibdata1 膨脹。真正釋放空間、整理索引:
CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;
或 5.6+ OPTIMIZE TABLE(線上 DDL)。
| 參數 | 建議值 | 影響 |
|---|---|---|
innodb_log_file_size | Buffer Pool 25%(如 4GB) | 減少 Checkpoint 刷盤頻率,崩潰恢復時間稍長 |
innodb_flush_log_at_trx_commit | 2(非金融核心) | 每秒刷盤而非每次提交,效能提升 10 倍+ |
innodb_flush_method | O_DIRECT | 繞過 OS Cache,避免雙重緩存 |
特別是 AWS EBS、阿里雲雲盤等虛擬磁碟,單盤 IOPS 有上限。軟 RAID 0 (mdadm) 條帶化多塊雲盤,線性擴展吞吐與 IOPS。注意:RAID 0 無冗余,務必配合 Binlog 異地備份、主從複製保障數據安全。
SHOW CREATE TABLE、EXPLAIN、慢查詢日誌分析缺失索引。INDEX (a,b,c) 滿足 SELECT a,b,c FROM t WHERE a=1,避免回表。INDEX (col(191))(utf8mb4 限制 767/3072 bytes)。WHERE date(created_at) = '...' → WHERE created_at >= '...' AND created_at < '...'。
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=1
# 通用日誌僅臨時開啟,極佔磁碟
# general_log=1
# general_log_file=/var/log/mysql/general.log
分析工具:pt-query-digest、mysqldumpslow、mysqlsla。
mkdir -p /mnt/ramdisk
mount -t tmpfs -o size=40G tmpfs /mnt/ramdisk
mv /var/lib/mysql /mnt/ramdisk/
ln -s /mnt/ramdisk/mysql /var/lib/mysql
chown -R mysql:mysql /mnt/ramdisk/mysql
配合 cron 定期 rsync 或 xtrabackup 備份到持久磁碟。適合「讀多寫少、可容忍秒級數據丟失」的緩存/臨時表/分析實例。
HandlerSocket 直接操作 InnoDB 索引,跳過 SQL 解析、優化器、鎖開銷,單機 QPS 可達 100k+。適用於:鍵值讀寫極高、邏輯簡單(PK/UK 查詢)、可容忍最終一致性的場景。
LIMIT 1 避免全掃。NOW(), RAND())導致查詢緩存失效。max_connections 耗盡)。MySQL 調優是系統工程:硬體(記憶體/磁碟)是地基,配置是骨架,索引與 SQL 是肌肉,架構(主從/分庫分表/緩存)是戰術。優化成本遠低於重構遷移 NoSQL,且 MySQL 8.0+ 在 JSON、GIS、CTE、窗口函數、即時分析等方面持續增強,依然是絕多數業務的最佳選擇。