萬盛學電腦網

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

MySQL 大記憶體高效能調優全攻略:參數、硬體、SQL、架構四維優化

硬體層面:效能基石

資源建議說明
磁碟 I/ORAID 10(SSD/NVMe 最佳)避開 RAID 5 寫入懲罰;binlog/redo log/數據檔分盤
CPU多核高主頻(如 2× Intel Xeon Gold 3.0GHz+)MySQL 單查詢單線程,高主頻利於複雜查詢
記憶體≥ 64GB(專用 DB 建議佔總記憶體 70-80%)InnoDB Buffer Pool 越大命中率越高
網路萬兆/雙網卡 bonding主從同步、備份傳輸不佔用業務頻寬

MySQL 8.0 關鍵參數範本(64GB RAM 專用機)

[mysqld]
# 基礎
server-id=1
port=3306
socket=/tmp/mysql.sock
datadir=/data/mysql
pid-file=/data/mysql/mysqld.pid

# 連線
max_connections=5000
max_connect_errors=1000000
back_log=1024
wait_timeout=600
interactive_timeout=600
skip-name-resolve

# InnoDB 核心(佔記憶體 70%)
innodb_buffer_pool_size=44G
innodb_buffer_pool_instances=16
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_log_buffer_size=256M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=20000
innodb_io_capacity_max=40000
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_purge_threads=4
innodb_page_cleaners=4
innodb_thread_concurrency=0  # 0=自動

# 查詢快取(8.0 已移除,改用 ProxySQL/Redis 外部快取)
# query_cache_type=0
# query_cache_size=0

# 臨時表與排序
tmp_table_size=1G
max_heap_table_size=1G
sort_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=4M

# 表快取
table_open_cache=8000
table_open_cache_instances=16
table_definition_cache=4000

# 執行緒
thread_cache_size=200
thread_stack=512K

# Binlog
log-bin=mysql-bin
binlog_format=ROW
binlog_row_image=MINIMAL
expire_logs_days=7
max_binlog_size=1G
sync_binlog=1
gtid_mode=ON
enforce_gtid_consistency=ON

# 安全與審計
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
log_error=/data/mysql/error.log
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

參數調優核心邏輯

  1. Buffer Pool:設為物理記憶體 70%,innodb_buffer_pool_instances = min(16, Buffer Pool GB 數),減少互斥鎖爭用。
  2. Redo Log:大檔案(1-4G)減少 checkpoint 衝刷頻率,innodb_flush_log_at_trx_commit=1 保證 ACID。
  3. IO Capacity:根據 SSD IOPS 設定,NVMe 可設 20000+。
  4. 連線相關:max_connections 配合連線池,thread_cache_size 避免頻繁建銷線程。
  5. 排序/Join Buffer:每連線獨佔,設太大易 OOM,4-8MB 適中;大查詢靠臨時表落盤。

SQL 與索引優化 18 條鐵律

  1. 查詢快取友善:避免 NOW()、RAND()、UUID() 等非確定性函數;用應用層變量替代。
  2. EXPLAIN 必看:關注 type(避免 ALL/index)、key、rows、Extra(Using temporary/filesort)。
  3. 存在性查詢用 LIMIT 1:SELECT 1 FROM t WHERE cond LIMIT 1。
  4. 搜尋欄位建索引:WHERE col LIKE 'abc%' 走索引,%abc% 需全文索引或外部搜尋引擎。
  5. JOIN 欄位同類型、同字元集、有索引:隱式轉換導致索引失效。
  6. 禁用 ORDER BY RAND():改用 SELECT * FROM t WHERE id >= (SELECT FLOOR(RAND()*MAX(id)) FROM t) LIMIT 1。
  7. 拒絕 SELECT *:只取需要欄位,減少網路/IO/記憶體,覆蓋索引更有機會生效。
  8. 主鍵用 BIGINT UNSIGNED AUTO_INCREMENT:單調遞增利於聚簇索引插入,避免頁分裂。
  9. 枚舉用 ENUM:內部存 TINYINT,體積小、比較快。
  10. PROCEDURE ANALYSE() 參考:根據實際數據分布建議欄位類型優化。
  11. 預設 NOT NULL:NULL 需額外位圖、複雜化比較邏輯。
  12. Prepared Statement:防注入、解析一次多次執行、二進制協議傳輸高效。
  13. 大結果集用無緩衝查詢:mysql_unbuffered_query / PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=false,流式處理。
  14. IP 存 UNSIGNED INT:INET_ATON()/INET_NTOA(),範圍查詢極快。
  15. 定長表更快:避免 VARCHAR/TEXT/BLOB,或垂直拆分熱/冷欄位。
  16. 垂直拆分:大字段(詳情、日誌)移至獨立表,主表保持窄而快。
  17. 批量 DML 分批提交:DELETE ... LIMIT 1000 循環 + usleep,避免長鎖表阻塞業務。
  18. 欄位類型最小化:TINYINT/SMALLINT/MEDIUMINT 代替 INT,DATE 代替 DATETIME,節省空間提升快取命中。

儲存引擎選型

  • InnoDB:預設、支援交易、行鎖、MVCC、外鍵、崩潰恢復——99% 場景首選。
  • MyISAM:唯讀/讀多寫少、全文索引(5.6+ InnoDB 也支援)、COUNT(*) 極快——僅用於日誌/報表只讀表。

架構層面擴展

  • 讀寫分離:一主多從,ProxySQL/MaxScale 做路由與快取。
  • 分庫分表:ShardingSphere/Vitess/MyCAT,按 tenant_id/user_id hash 分片。
  • 緩存層:Redis 熱點資料、查詢結果快取,降低 DB QPS 壓力。
  • 異步處理:Kafka/RocketMQ 削峰填谷,寫入異步化。

監控與持續優化

部署 Prometheus + Grafana + node_exporter + mysqld_exporter,重點儀表板:

  • QPS/TPS、併發執行緒、緩衝池命中率(目標 >99%)、InnoDB 行鎖等待、慢查詢 TOP 10、磁碟 IOPS/延遲、主從延遲秒數。
  • 定期 pt-query-digest 分析慢查詢日誌,產出優化工單。
  • 每季度執行 ANALYZE TABLE 更新統計資訊,避免執行計劃退化。
copyright © 萬盛學電腦網 all rights reserved