MySQL 效能優化 12 條實戰技巧：從配置到 SQL 改寫

1. 絕不手動編輯 mysqldump 輸出檔

轉儲檔看似純文字，實則結構嚴謹。手動修改極易導致語法錯誤、編碼損壞、還原失敗。需修改時，請重新匯出或用 sed/awk 等工具程式化處理。

2. MyISAM 區塊大小調優

[mysqld]
myisam_block_size = 4K  # 預設 1K，現代檔案系統 4K 對齊更佳

大區塊減少磁碟尋找，提升大表掃描吞吐。需權衡記憶體浪費（小表）。

3. 開啟 delay_key_write（非關鍵資料表）

[mysqld]
delay_key_write = ON

• 延遲索引寫入磁碟，大幅減少 I/O
• 風險：崩潰時索引損壞，需 CHECK TABLE 或 myisam_recover_options=FORCE,BACKUP
• 僅用於可重建的非關鍵表（日誌、快取、暫存）

4. JOIN 優化鐵律

1. 關聯欄位型別完全一致：INT 對 INT，VARCHAR(20) 對 VARCHAR(20)，字元集排序規則一致
2. 關聯欄位建立索引：驅動表與被驅動表雙邊皆需索引
3. 小表驅動大表：EXPLAIN 確認 type=ref 而非 ALL
4. 避免 SELECT *：只查必要欄位，利用覆蓋索引

5. WHERE 條件優化：LIMIT 1

-- 僅需存在性檢查
SELECT 1 FROM users WHERE email = 'test@example.com' LIMIT 1;

-- 僅需單筆記錄
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 1;

避免全表掃描，特別是無索引欄位條件。

6. 善用 EXPLAIN 分析執行計畫

EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 1;

重點關注：type（ALL/index/range/ref/eq_ref/const）、key（實際用到的索引）、rows（預估掃描行數）、Extra（Using where/Using index/Using temporary/Using filesort）。

7. 啟用查詢快取（5.7 及之前版本）

[mysqld]
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

• 適用「讀多寫少、重複查詢多」場景
• 寫入會使相關快取失效，寫入頻繁時反而拖慢效能
• MySQL 8.0 已移除 Query Cache，改用 ProxySQL、Redis 等外部快取

8. 堆疊追蹤定位 Bug

[mysqld]
log_error = /var/log/mysql/error.log
log_error_verbosity = 3

分析 stack_trace 定位死鎖、段錯誤、異常退出根因。配合 gdb、perf 進階診斷。

9. 設定 SQL_MODE 嚴格模式

[mysqld]
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• 防止隱性資料截斷、無效日期、除零錯誤靜默通過
• 開發階段建議加上 TRADITIONAL（等同最嚴格組合）
• 線上變更需測試相容性，避免應用報錯

10. 定期修改 Root 密碼與權限審計

# 修改密碼
mysqladmin -u root -p password 'NewStrongPassw0rd!'

# 審計權限
mysql -e "SELECT User, Host FROM mysql.user WHERE User != '';"
mysql -e "SHOW GRANTS FOR 'app'@'%';"

11. mysqldump 備份自動化與驗證

#!/bin/bash
# backup.sh
mysqldump --single-transaction --routines --events --master-data=2 \
    --flush-logs --hex-blob -u backup_user -p'pass' --databases db1 db2 \
    | gzip > /backup/mysql/full_$(date +%F_%H%M).sql.gz

# 校驗備份可還原
gunzip -c /backup/mysql/full_latest.sql.gz | head -20 | grep -q "CREATE DATABASE" && echo "OK" || echo "CORRUPT"

12. 使用 MySQLTuner / Percona Toolkit 定期體檢

# MySQLTuner
wget https://mysqltuner.pl -O mysqltuner.pl
perl mysqltuner.pl --user root --pass password

# Percona Toolkit 核心工具
pt-query-digest /var/log/mysql/slow.log          # 慢查詢分析
pt-duplicate-key-checker --user=root -p          # 重複索引檢測
pt-index-usage --user=root -p /var/log/mysql/slow.log  # 索引使用率
pt-online-schema-change --alter "ADD INDEX..." D=db,t=tbl --execute  # 線上 DDL

總結：優化優先級矩陣