萬盛學電腦網

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

MySQL 效能優化全方位指南:編譯、配置、表類型與工具

一、編譯期優化(源碼安裝時)

1.1 選用優化編譯器

  • pgcc (Pentium GCC):針對奔騰處理器優化,效能提升 ~10%
  • 非奔騰平台勿用

1.2 僅編譯所需字符集

./configure --with-extra-charsets=none  # 僅保留 latin1
# 或指定需要的:--with-charset=utf8mb4 --with-collation=utf8mb4_general_ci

減少二進制體積、載入時間與記憶體佔用。

1.3 靜態編譯 mysqld

./configure --with-mysqld-ldflags=-all-static --disable-shared

省去動態連結庫載入開銷,提升 5-10% 效能。

1.4 經典高效能編譯參數組合

CFLAGS="-O3 -march=native -fomit-frame-pointer" \
CXX=gcc CXXFLAGS="-O3 -march=native -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/usr/local/mysql \
  --enable-assembler \
  --with-mysqld-ldflags=-all-static \
  --disable-shared \
  --with-charset=utf8mb4 \
  --with-collation=utf8mb4_general_ci

二、運行期配置優化 (my.cnf)

配置檔案層級

  • Global:/etc/my.cnf 影響所有實例
  • Instance:$MYSQL_BASE/var/my.cnf 單實例
  • User:~/.my.cnf 僅限當前用戶

關鍵變數調整範例

[mysqld]
# 連接設定
max_connections = 500
max_connect_errors = 100000

# 緩衝區與快取
innodb_buffer_pool_size = 4G        # 專用機建議 70-80% 實體記憶體
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
query_cache_type = 0                # MySQL 5.7+ 建議關閉
query_cache_size = 0

# 暫存表與排序
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M

# InnoDB 核心
innodb_flush_log_at_trx_commit = 1  # ACID 合規;可設 2 提升寫入效能
innodb_flush_method = O_DIRECT      # 避免雙重緩衝
innodb_file_per_table = ON          # 獨立表空間,利於管理

# 慢查詢日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

三、表類型選擇策略

類型事務支援特點適用場景
InnoDB✅行級鎖、MVCC、外鍵、崩潰恢復、預設引擎**絕大多數 OLTP 應用**
MyISAM❌表級鎖、全文索引、壓縮表、讀多寫少唯讀分析、日誌、緩存表
MEMORY (HEAP)❌資料在記憶體、Hash 索引、重啟消失臨時查找表、Session 快取
CSV❌CSV 文字檔、可外部工具處理資料交換、匯入匯出
ARCHIVE❌高壓縮、僅支援 INSERT/SELECT歷史日誌、審計追蹤
BLACKHOLE❌寫入即丟棄、用於複寫轉發多源複寫中繼

指定表類型

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=MEMORY;

四、內建優化工具

SHOW STATUS

SHOW GLOBAL STATUS;
-- 關鍵指標:Threads_connected, Queries, Slow_queries, Innodb_buffer_pool_reads, ...

EXPLAIN 分析查詢計畫

EXPLAIN SELECT * FROM orders JOIN customers ON orders.cid = customers.id WHERE orders.date > '2023-01-01';
/* 重點欄位:type (ALL/index/range/ref/eq_ref/const), key, rows, Extra */

OPTIMIZE TABLE

OPTIMIZE TABLE table_name;
-- 適用 MyISAM/InnoDB:回收碎片、重建索引、更新統計資訊

ANALYZE TABLE

ANALYZE TABLE table_name;
-- 更新索引統計資訊,幫助優化器產生更好計畫

五、監控與持續優化

  • Prometheus + Grafana + mysqld_exporter 監控核心指標
  • pt-query-digest 分析慢查詢日誌
  • Sysbench 壓測驗證配置調整效果
  • 定期 ANALYZE、OPTIMIZE、檢查索引使用率(sys.schema_unused_indexes)
copyright © 萬盛學電腦網 all rights reserved