MySQL 查詢優化：系統管理員層面的效能調校措施

核心原則

1. 記憶體存取 ≫ 磁碟 I/O
2. 盡量將熱點資料留在記憶體
3. 索引資訊比資料記錄更關鍵，優先快取索引

關鍵快取參數調整

表快取

• 參數：table_open_cache (舊版 table_cache)
• 監控：SHOW STATUS LIKE 'Opened_tables';
• 若 Opened_tables 快速增長，表示快取太小，頻繁開關表檔
• 建議：根據併發連線數與資料表數量調大，避免檔案描述符耗盡

MyISAM 索引快取

• 參數：key_buffer_size (預設 8MB，極度保守)
• 監控：Key_read_requests vs Key_reads
• 未命中率 = Key_reads / Key_read_requests，目標 < 0.1%
• MySQL 4.1+ 支援多索引快取，可將關鍵表指派專用快取

InnoDB 緩衝池

• 參數：innodb_buffer_pool_size (最關鍵參數，建議實體記憶體 60-80%)
• 日誌緩衝：innodb_log_buffer_size
• 監控：Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads

BDB 快取

• 參數：bdb_cache_size

查詢快取

• 參數：query_cache_size, query_cache_type, query_cache_limit
• 適用「讀多寫少、重複查詢多」場景；頻繁更新表會導致快取頻繁失效，反而增加負擔

調參原則

• **一次只改一個參數**，觀察效果
• **漸進式調大**，避免過度配置導致記憶體交換 (swapping)
• **勿在生產庫直接實驗**，建立獨立測試環境模擬真實負載
• 記錄基準線，量化比較調整前後差異

硬體層面優化

• 足夠實體記憶體 (避免 Swap)
• 高速儲存：NVMe SSD ≫ SATA SSD ≫ HDD，特別利於 Redo Log、Binlog、臨時表
• RAID 10 平衡效能與冗餘
• 獨立磁碟分離：資料、日誌、備份、tmpdir 分不同實體裝置
• CPU 核心數配合 innodb_thread_concurrency 設定

作業系統調校

• 檔案系統：ext4/xfs 掛載 noatime
• I/O 排程器：SSD 用 none/noop，HDD 用 deadline
• 關閉 Transparent Huge Pages (THP)：echo never > /sys/kernel/mm/transparent_hugepage/enabled
• 調大 ulimit -n (開啟檔案數)、vm.max_map_count

總結

DBA 層面優化核心在於「給記憶體、減磁碟、調快取、分 I/O」。參數調整需以監控數據為依據，切忌盲目套用網路範本配置。