MySQL MyISAM 與 InnoDB 深度優化指南：參數、架構選型與實戰技巧

儲存引擎選型決策樹

MyISAM 核心優化參數

[mysqld]
# 索引快取：建議設為實體記憶體 25~30%，專供 MyISAM 索引
key_buffer_size = 512M

# 查詢快取（MySQL 8.0 已移除，5.7 仍可用）
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# 慢查詢門檻
long_query_time = 0.5
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

# 禁止外部鎖定，防死鎖
external_locking = FALSE

# 連線佇列
back_log = 200

# 表快取
table_open_cache = 4000

# 執行緒快取
thread_cache_size = 100

# 排序/連接/讀取緩衝（每執行緒）
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# 禁用不必要引擎
skip-bdb
skip-innodb   # 純 MyISAM 環境可關閉 InnoDB 節省記憶體

InnoDB 核心優化參數（2GB 記憶體範例）

[mysqld]
# 核心：資料+索引快取池，建議 60~70% 物理記憶體
innodb_buffer_pool_size = 1G
# 實例數（1GB 以上建議多實例減少鎖競爭）
innodb_buffer_pool_instances = 8

# 額外記憶體池（資料字典、鎖資訊），5.6.3 後合併入 buffer_pool，可忽略
innodb_additional_mem_pool_size = 16M

# Redo Log 緩衝
innodb_log_buffer_size = 32M

# Redo Log 檔案大小與組數：總大小約 buffer_pool 1/4 ~ 1/2
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

# 關鍵：刷盤策略
# 0 = 每秒刷盤（效能最好，崩潰丟 1 秒資料）
# 1 = 每次 commit 刷盤（ACID 最強，預設）
# 2 = 寫入 OS 快取不強制 fsync（折中）
innodb_flush_log_at_trx_commit = 2

# 雙重寫緩衝區（防止半寫頁），SSD 可關閉
innodb_doublewrite = 1

# 並發執行緒數（0=自動，建議 2 * CPU 核心）
innodb_thread_concurrency = 16

# 檔案 I/O 線程
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 開啟獨立表空間（必開，便於管理與回收空間）
innodb_file_per_table = 1

# 統計資訊持久化（避免重啟後計劃變差）
innodb_stats_persistent = 1
innodb_stats_auto_recalc = 1

效能關鍵差異：innodb_flush_log_at_trx_commit

建議： 非核心金融業務可設 2，兼顧效能與安全；搭配 sync_binlog=1 確保 Binlog 不丟，可做時間點恢復（PITR）。

實戰 SQL 優化技巧（引擎通用）

1. 避免 SELECT COUNT(*)： InnoDB 無快取，全表掃描；改用近似值 SHOW TABLE STATUS 或維護計數表。
2. 善用 EXPLAIN： 重點看 type（ref/range/const）、key、rows、Extra。
3. JOIN 欄位型別長度一致： 含 CHAR/VARCHAR 長度、COLLATE、UNSIGNED 皆需一致，否則隱式轉換導致索引失效。
4. 分頁優化： 深分頁 LIMIT 100000, 10 → 子查詢先取 ID 再 JOIN（見文章 88749）。
5. LIKE 左邊不加 %： name LIKE 'abc%' 走索引，'%abc%' 全掃。
6. 拆解複雜查詢： 大 UNION、多層子查詢拆成多步驟，應用層組裝。
7. 定期 OPTIMIZE/ANALYZE： 碎片整理與統計更新，避免執行計劃退化。

架構層面建議

• 讀寫分離： 主庫寫、從庫讀，減輕主庫壓力。
• 分庫分表： 單表 > 2000 萬行或 > 5GB 考慮分表。
• 快取層： Redis 緩存熱點資料，降低 DB QPS。
• 異步處理： 非即時寫入（日誌、統計）進 MQ 再批次入庫。

總結

MyISAM 適合「讀多、無事務、全文」；InnoDB 是「高併發、事務、高可用」的首選。InnoDB 調優核心在於 innodb_buffer_pool_size、innodb_flush_log_at_trx_commit、Redo Log 大小、並發執行緒。配合 SQL 寫法規範、架構分層，才能榨乾硬體效能。