本文將介紹如何在 Ubuntu 22.04 LTS 上對 MySQL 8.0 進行效能調校，以提升資料庫的運行效率和回應速度。
效能調校概述
MySQL 效能調校是一項持續性的工作，涵蓋多個層面：
- 硬體資源配置：CPU、記憶體、磁碟 I/O
- MySQL 設定參數調整：針對工作負載優化
- 查詢與索引優化：減少不必要的資源消耗
- 監控與分析：持續追蹤效能指標
在開始調校之前，請先了解您的系統資源：
1
2
3
4
5
6
7
8
| # 查看系統記憶體
free -h
# 查看 CPU 資訊
lscpu
# 查看磁碟空間
df -h
|
InnoDB Buffer Pool 設定
InnoDB Buffer Pool 是 MySQL 效能調校中最重要的參數之一，它用於快取資料和索引，減少磁碟 I/O。
計算建議值
一般建議將 Buffer Pool 設定為系統可用記憶體的 50-80%：
1
2
| # 查看目前的 Buffer Pool 大小
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
|
修改設定
編輯 MySQL 設定檔：
1
| sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
|
在 [mysqld] 區段下加入以下設定（假設系統有 8GB 記憶體）：
1
2
3
4
5
6
7
8
| [mysqld]
# InnoDB Buffer Pool 設定
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
# Buffer Pool 預熱設定
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
|
監控 Buffer Pool 使用率
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| -- 查看 Buffer Pool 狀態
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 計算 Buffer Pool 命中率
SELECT
(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
AS buffer_pool_hit_rate
FROM (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) AS reads,
(
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) AS requests;
|
命中率應維持在 99% 以上，若低於此值，考慮增加 Buffer Pool 大小。
查詢快取（MySQL 8.0 移除說明）
在 MySQL 5.7 及之前版本，Query Cache 是常用的效能優化功能。然而，MySQL 8.0 已完全移除查詢快取功能。
移除原因
- 在高併發環境下，Query Cache 的鎖定機制造成效能瓶頸
- 現代應用程式多使用應用層快取（如 Redis、Memcached）
- Query Cache 的維護成本過高
替代方案
建議使用以下替代方案：
1
2
3
4
5
6
| # 安裝 Redis 作為應用層快取
sudo apt install redis-server -y
# 啟動 Redis 服務
sudo systemctl enable redis-server
sudo systemctl start redis-server
|
或在應用程式中實作查詢結果快取邏輯。
連線與執行緒設定
最大連線數設定
1
2
3
4
5
6
7
8
9
| [mysqld]
# 最大連線數（根據應用需求調整）
max_connections = 200
# 每個連線使用的緩衝區大小
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
|
監控連線使用情況
1
2
3
4
5
6
7
8
| -- 查看目前連線數
SHOW STATUS LIKE 'Threads_connected';
-- 查看歷史最大連線數
SHOW STATUS LIKE 'Max_used_connections';
-- 查看連線相關設定
SHOW VARIABLES LIKE 'max_connections';
|
執行緒快取設定
1
2
3
| [mysqld]
# 執行緒快取大小
thread_cache_size = 16
|
日誌設定優化
InnoDB Redo Log 設定
1
2
3
4
5
6
7
8
9
10
11
| [mysqld]
# Redo Log 檔案大小（MySQL 8.0.30+）
innodb_redo_log_capacity = 1G
# 日誌緩衝區大小
innodb_log_buffer_size = 64M
# 日誌刷新策略
# 1 = 每次交易都刷新（最安全）
# 2 = 每秒刷新（較高效能）
innodb_flush_log_at_trx_commit = 1
|
Binary Log 設定
如果不需要複製或時間點還原，可考慮停用：
1
2
3
4
5
6
7
| [mysqld]
# 啟用 Binary Log（建議保留）
log_bin = mysql-bin
binlog_expire_logs_seconds = 604800 # 7 天
# Binary Log 格式
binlog_format = ROW
|
索引優化基礎
查看資料表索引
1
2
3
4
5
| -- 查看資料表的索引
SHOW INDEX FROM table_name;
-- 使用 EXPLAIN 分析查詢
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
|
常見索引優化建議
1
2
3
4
5
6
7
8
9
10
11
| -- 為經常查詢的欄位建立索引
CREATE INDEX idx_email ON users(email);
-- 建立複合索引（注意欄位順序）
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 查看重複的索引
SELECT * FROM sys.schema_redundant_indexes;
|
索引使用原則
- 在 WHERE、JOIN、ORDER BY 常用的欄位建立索引
- 避免在低基數（cardinality）欄位建立索引
- 定期使用 ANALYZE TABLE 更新統計資訊
1
| ANALYZE TABLE table_name;
|
慢查詢日誌
啟用慢查詢日誌
1
2
3
4
5
6
7
8
9
10
| [mysqld]
# 啟用慢查詢日誌
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 定義慢查詢閾值（秒）
long_query_time = 2
# 記錄未使用索引的查詢
log_queries_not_using_indexes = ON
|
分析慢查詢日誌
使用 mysqldumpslow 工具：
1
2
3
4
5
| # 查看最慢的 10 個查詢
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# 查看執行次數最多的慢查詢
sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
|
使用 pt-query-digest（需安裝 Percona Toolkit）：
1
2
3
4
5
| # 安裝 Percona Toolkit
sudo apt install percona-toolkit -y
# 分析慢查詢日誌
pt-query-digest /var/log/mysql/mysql-slow.log
|
MySQLTuner 工具
MySQLTuner 是一個自動分析 MySQL 效能並提供建議的腳本。
安裝與使用
1
2
3
4
5
6
7
8
| # 下載 MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# 設定執行權限
chmod +x mysqltuner.pl
# 執行分析
sudo ./mysqltuner.pl --user root --pass 'your_password'
|
輸出說明
MySQLTuner 會提供以下資訊：
- 系統資源使用情況
- MySQL 設定建議
- 效能指標分析
- 安全性建議
根據輸出的建議調整設定，但請注意：
- 不要盲目套用所有建議
- 在測試環境驗證變更
- 每次只調整少量參數
監控效能指標
1
2
3
4
5
6
7
8
9
10
11
12
| -- 啟用 Performance Schema（預設已啟用）
SHOW VARIABLES LIKE 'performance_schema';
-- 查看執行時間最長的查詢
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
AVG_TIMER_WAIT/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
|
使用 sys Schema
1
2
3
4
5
6
7
8
9
10
11
| -- 查看執行時間最長的語句
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
-- 查看等待事件
SELECT * FROM sys.waits_by_host_by_latency;
-- 查看 I/O 統計
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;
-- 查看記憶體使用情況
SELECT * FROM sys.memory_global_by_current_bytes;
|
常用監控指令
1
2
3
4
5
6
7
8
| -- 查看即時查詢
SHOW PROCESSLIST;
-- 查看 InnoDB 引擎狀態
SHOW ENGINE INNODB STATUS\G
-- 查看全域狀態變數
SHOW GLOBAL STATUS;
|
綜合設定範例
以下是一個適用於 8GB 記憶體伺服器的設定範例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| [mysqld]
# 基本設定
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
# InnoDB 設定
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_buffer_size = 64M
innodb_redo_log_capacity = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
# 連線設定
max_connections = 200
thread_cache_size = 16
# 緩衝區設定
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
tmp_table_size = 64M
max_heap_table_size = 64M
# 慢查詢日誌
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
# Binary Log
log_bin = mysql-bin
binlog_expire_logs_seconds = 604800
binlog_format = ROW
|
修改設定後，重新啟動 MySQL：
1
| sudo systemctl restart mysql
|
結論
MySQL 效能調校需要綜合考量硬體資源、工作負載特性和應用需求。本文介紹的調校方向包括：
- InnoDB Buffer Pool 配置
- 連線與執行緒管理
- 日誌設定優化
- 索引建立與維護
- 慢查詢分析
- 使用工具輔助調校
建議採用漸進式調整策略，每次修改後觀察效能變化，並建立完整的監控機制以持續追蹤系統表現。
參考資料