萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 性能調優新手必讀:從引擎選型到架構優化的 11 大核心法則

前言

MySQL 是 Web 後端最流行的關係型資料庫,但預設配置遠未發揮硬體效能。本文梳理 11 大調優維度,從存儲引擎、記憶體、磁碟、索引、SQL 寫法到架構層面,助你將資料庫效能提升數倍至數百倍。

1. 選對存儲引擎:預設 InnoDB

  • InnoDB:支援事務、行鎖、外鍵、崩潰恢復、緩存數據+索引(innodb_buffer_pool)。
  • MyISAM:僅緩存索引、表鎖、不支援事務,唯有全文檢索(FULLTEXT)略優,但 5.6+ InnoDB 已支援。
  • 建議: 除純只讀/全文檢索場景,全盤 InnoDB。

MyISAM → 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)。

2. 讓數據住在記憶體:Buffer Pool 容量規劃

核心指標


SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

關鍵:Innodb_buffer_pool_pages_free = 0 表示池已滿,需擴容。

規劃原則

  • 專用 DB 伺服器:innodb_buffer_pool_size = 物理記憶體 70-80%。
  • 共用伺服器:預留 OS/其他進程記憶體,確保 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

禁用 Swap

專用 DB:swapoff -a 或 vm.swappiness=1。共用機:鎖定記憶體 memlock unlimited(需 ulimit -l unlimited)。

3. 定期重建表碎片(而非 mysqlcheck -o)

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)。

4. 減少磁碟寫入

參數建議值影響
innodb_log_file_sizeBuffer Pool 25%(如 4GB)減少 Checkpoint 刷盤頻率,崩潰恢復時間稍長
innodb_flush_log_at_trx_commit2(非金融核心)每秒刷盤而非每次提交,效能提升 10 倍+
innodb_flush_methodO_DIRECT繞過 OS Cache,避免雙重緩存

5. 提升磁碟 IOPS:RAID 0 / NVMe / 云盤條帶化

特別是 AWS EBS、阿里雲雲盤等虛擬磁碟,單盤 IOPS 有上限。軟 RAID 0 (mdadm) 條帶化多塊雲盤,線性擴展吞吐與 IOPS。注意:RAID 0 無冗余,務必配合 Binlog 異地備份、主從複製保障數據安全。

6. 索引是查詢加速的唯一槓桿

  • 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 < '...'。

7. 啟用慢查詢與通用日誌(僅診斷期)


[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。

8. 激進方案:記憶體盤(tmpfs)跑 MySQL


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 備份到持久磁碟。適合「讀多寫少、可容忍秒級數據丟失」的緩存/臨時表/分析實例。

9. HandlerSocket:繞過 SQL 層,MySQL 變身 NoSQL

HandlerSocket 直接操作 InnoDB 索引,跳過 SQL 解析、優化器、鎖開銷,單機 QPS 可達 100k+。適用於:鍵值讀寫極高、邏輯簡單(PK/UK 查詢)、可容忍最終一致性的場景。

10. 微優化清單

  • 單條查詢加 LIMIT 1 避免全掃。
  • 大文本/Blob 拆離表,不影響主表緩存命中率。
  • 避免 MySQL 內建函數(NOW(), RAND())導致查詢緩存失效。
  • PHP 短連接快,慎用連接池(易導致 max_connections 耗盡)。
  • 連線字串用 IP 而非域名,省 DNS 解析與故障點。

11. 結語

MySQL 調優是系統工程:硬體(記憶體/磁碟)是地基,配置是骨架,索引與 SQL 是肌肉,架構(主從/分庫分表/緩存)是戰術。優化成本遠低於重構遷移 NoSQL,且 MySQL 8.0+ 在 JSON、GIS、CTE、窗口函數、即時分析等方面持續增強,依然是絕多數業務的最佳選擇。

copyright © 萬盛學電腦網 all rights reserved