MySQL 性能調優 10 大實戰方法（含配置參數、索引、架構）

MySQL 性能調優 10 大實戰方法（含配置參數、索引、架構）

本文系統梳理從引擎選型、內存佈局、磁盤 I/O、索引策略、日誌分析、內存磁盤、HandlerSocket 等 10 大調優方向，附具體配置參數、腳本、驗證指標，可直接落地生產。

方法 1：默認選 InnoDB，MyISAM 退役

• InnoDB 支援事務、行鎖、崩潰恢復、外鍵，5.5+ 默認引擎。
• 讀性能對齊：innodb_flush_log_at_trx_commit = 2 可達 MyISAM 讀速 99%+，且保留 ACID。
• 批量轉換腳本見《MySQL 效能調優 10 大實戰招式》。
• 必開獨立表空間：innodb_file_per_table = 1。

方法 2：數據 100% 內存駐留

2.1 Buffer Pool 容量規劃

專用 DB：物理內存 70~80%。
共用機：總數據量 + 20%，不超過物理內存 50%。

2.2 驗證是否足夠

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%';
-- Innodb_buffer_pool_pages_free = 0  → 需擴容

輔助：iostat -d -x -k 1 觀察 %util / await。

2.3 預熱腳本（重啟/備份前必跑）

# 生成掃描所有索引的 SELECT
mysql -uroot -AN < MakeSelectQueriesToLoad.sql > SelectQueriesToLoad.sql
# 執行預熱
mysql -uroot < SelectQueriesToLoad.sql > /dev/null 2>&1

2.4 禁止 Swap / 鎖內存

swapoff -a 或 memlock unlimited。

方法 3：定期重建表消除碎片

CREATE TABLE db.tbl_new LIKE db.tbl;
INSERT INTO db.tbl_new SELECT * FROM db.tbl;
RENAME TABLE db.tbl TO db.tbl_old, db.tbl_new TO db.tbl;
DROP TABLE db.tbl_old;

或用 pt-online-schema-change 零停機。

方法 4：減少磁盤寫入

參數	建議值	說明
innodb_log_file_size	0.25 * Buffer Pool	過大導致崩潰恢復慢
innodb_flush_log_at_trx_commit	2	每秒 fsync，高併發首選
innodb_flush_method	O_DIRECT	繞過 OS cache，避免雙重拷貝
innodb_log_buffer_size	16M~64M	大事務場景適當增大

方法 5：提升磁盤 I/O 能力

• RAID 0 / RAID 10 條帶化，雲盤必做軟 RAID。
• SSD/NVMe 替代 SAS，隨機 IOPS 提升 10~50 倍。
• 分離 data / log / backup 掛載點。

方法 6：索引是唯一槓桿

• SHOW CREATE TABLE db.tbl\G 先看結構。
• 依慢查詢 / EXPLAIN 補索引，遵循最左前綴、高區分度靠左。
• 啟動預熱核心索引：init-file=/var/lib/mysql/upcache.sql。
• 框架自動索引（Rails/Drupal）降低初期設計風險。

方法 7：開啟並分析慢查詢日誌

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

分析：pt-query-digest /var/log/mysql/slow.log（專業）、mysqldumpslow -t 10（簡易）。

方法 8：激進派——內存磁盤

mkdir -p /mnt/ramdisk
mount -t tmpfs -o size=4G tmpfs /mnt/ramdisk/
mv /var/lib/mysql /mnt/ramdisk/
ln -s /mnt/ramdisk/mysql /var/lib/mysql
chown mysql:mysql /mnt/ramdisk/mysql

定期 xtrabackup 落盤。**風險**：斷電丟數據，需業務容忍。

方法 9：HandlerSocket 繞過 SQL 層

Daemon Plugin 直連 InnoDB，單機 75 萬+ QPS。詳見《MySQL 當 NoSQL 用：HandlerSocket 實現 75 萬 QPS 實戰解析》。

方法 10：碎片化優化清單

• 單查詢 LIMIT 1 防全掃。
• 大字段垂直拆分獨立表。
• WHERE 避免函數（DATE(created_at) → 範圍查詢）。
• PHP 短連接快，通常無需連接池；用池注意 max_connections。
• 連接串用 IP 避免 DNS 抖動。

總結

調優路徑：引擎對 → 內存夠 → 磁盤快 → 索引準 → 日誌勤 → 架構分 → 極致用 HandlerSocket。每步落地必須用 QPS、延遲、CPU、IOPS 數據驗證。