MySQL 性能優化是一項系統工程，涵蓋查詢優化、索引策略、架構設計、參數配置及硬體資源規劃等多個維度。以下整理自實戰經驗的核心優化要點，供開發與運維人員參考。
? contents 快速查閱官方文件。SHOW STATUS LIKE 'Com_%' 觀察 Com_select、Com_insert 等計數器，判斷讀寫比例；SHOW STATUS LIKE 'Handler_read%' 分析 Handler_read_key（索引讀取次數）與 Handler_read_rnd_next（全表掃描次數），前者偏低、後者偏高提示索引缺失或失效。ANALYZE TABLE 更新統計資訊、CHECK TABLE 檢查錯誤、OPTIMIZE TABLE 整理碎片、CHECKSUM TABLE 驗證資料完整性。MyISAM：ALTER TABLE tbl DISABLE KEYS → 導入資料 → ALTER TABLE tbl ENABLE KEYS，暫停索引維護大幅提升速度。
InnoDB：SET UNIQUE_CHECKS=0; SET AUTOCOMMIT=0; 導入後再恢復，減少唯一性檢查與事務提交開銷。
通用建議：優先使用 LOAD DATA INFILE，配合 REPLACE 或 IGNORE 處理衝突；單條 INSERT 改為多值語法 INSERT INTO t VALUES (),(),()。
| 參數 | 適用引擎 | 核心作用 | 建議方向 |
|---|---|---|---|
| key_buffer_size | MyISAM | 索引塊緩衝區，所有執行緒共享 | 視記憶體大小設為 128M~400M |
| table_cache | 通用 | 開啟表的快取數量，關聯 max_connections | ≥ max_connections × 最大 JOIN 表數 |
| innodb_buffer_pool_size | InnoDB | 資料與索引的記憶體緩衝區，核心參數 | 實體記憶體 60%~80%，越大磁碟 I/O 越少 |
| innodb_flush_log_at_trx_commit | InnoDB | 事務日誌刷盤策略：0/1/2 | 1 最安全，2 折中，0 性能最好但可能丟失 1 秒資料 |
| innodb_support_xa | InnoDB | 是否支援分散式事務（XA） | 不需要分散式事務時設 OFF 減少 fsync |
| innodb_log_buffer_size / innodb_log_file_size | InnoDB | 重做日誌緩衝與檔案大小 | 視寫入量調大，減少檢查點頻率 |
MEDIUMINT 別用 INT，無變長欄位優先 CHAR 而非 VARCHAR（固定長度查詢更快）。NOT NULL，索引欄位尤甚。MERGE（MyISAM）或 PARTITION（通用）拆分為靜態歷史表與動態熱表。UNSIGNED INT (Unix timestamp)，比 DATETIME 運算更快。EXPLAIN 分析複雜查詢，確認 type 至少達到 ref 級別，避免 ALL 全表掃描。LIMIT 搭配 ORDER BY 或 DISTINCT，防止無序掃描。TRUNCATE TABLE（DDL 級別，秒級完成），不使用 DELETE（逐行刪除，需回滾日誌）。INSERT 使用多值語法或 LOAD DATA。GROUP BY 預設排序，若不需排序加 ORDER BY NULL 關閉。| 瓶頸類型 | 表徵 | 對策 |
|---|---|---|
| 磁碟搜尋 (Seek) | 隨機 I/O 延遲高 | 資料分散至多磁碟/RAID/SSD，縮短尋道時間 |
| 磁碟讀寫 (Throughput) | 連續吞吐不足 | 多磁碟並行讀取、啟用壓縮、增加快取 |
| CPU 週期 | 計算密集型查詢佔用高 | 優化 SQL、增加 CPU 核心、調整並行度 |
| 記憶體頻寬 | 大量資料在 CPU 快取間搬運 | 優化索引減少掃描行數、使用覆蓋索引 |
深分頁優化：LIMIT 100000, 20 會掃描 10 萬行再丟棄。改用覆蓋索引子查詢：
SELECT * FROM t INNER JOIN (SELECT id FROM t LIMIT 100000, 20) AS tmp USING(id);
子查詢僅掃描索引 (Using index)，外層按主鍵精準回表，行數從千萬級降至 20 行。
COUNT(*) 加速：InnoDB 聚簇索引包含完整行資料，掃描主鍵即掃描全表。利用較窄的二級索引 SELECT COUNT(*) FROM t WHERE secondary_col >= 0，僅掃描索引樹，速度提升數倍。
