MySQL 優化是系統性工程，涵蓋 SQL 語句、索引結構、表架構、服務器參數、硬體資源、快取策略六大面向。本文結合 EXPLAIN 實戰分析、索引失效場景、分區/中間表/冗余欄位架構優化、my.cnf 核心參數詳解、Query Cache 與 Memcached 兩級快取策略，構建完整優化知識體系。
SHOW STATUS LIKE 'Connections'; -- 總連線數
SHOW STATUS LIKE 'Uptime'; -- 運行時間
SHOW STATUS LIKE 'Slow_queries'; -- 慢查詢次數
SHOW STATUS LIKE 'Com_select'; -- 查詢次數
SHOW STATUS LIKE 'Com_insert'; -- 插入次數
SHOW STATUS LIKE 'Com_update'; -- 更新次數
SHOW STATUS LIKE 'Com_delete'; -- 刪除次數持續監控上述指標，建立基線，異常時快速定位。
| 欄位 | 含義 | 優化關注點 |
|---|---|---|
| id | SELECT 識別符 | 多表關聯執行順序 |
| select_type | 查詢類型 | SIMPLE/PRIMARY/UNION/SUBQUERY/DERIVED |
| table | 存取表 | |
| type | 關聯類型（效能由好到壞） | system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL |
| possible_keys | 可能用到的索引 | NULL 表示無索引可用 |
| key | 實際使用的索引 | 可用 FORCE/USE/IGNORE INDEX 干預 |
| key_len | 索引長度（位元組） | 推斷複合索引用到幾個欄位 |
| ref | 與索引比較的欄位/常數 | |
| rows | 預估掃描行數 | 越小越好 |
| Extra | 額外資訊 | Using filesort、Using temporary、Using index、Using where |
| 場景 | SQL 範例 | type | 說明 |
|---|---|---|---|
| LIKE 以 % 開頭 | WHERE name LIKE '%x' | ALL | 全表掃描 |
| LIKE 不以 % 開頭 | WHERE name LIKE 'x%' | index | 索引掃描 |
| 複合索引非前導欄位 | WHERE age=12 (idx_name_age) | ALL | 需符合最左前綴 |
| OR 條件欄位無全索引 | WHERE name='A' OR job='B' | ALL | job 無索引導致全掃 |
| OR 條件欄位全有索引 | WHERE name='A' OR age=12 | ref | 索引合併 |
| 引擎 | 優化手段 | 指令 |
|---|---|---|
| MyISAM | 禁用索引 → 匯入 → 重建索引 | ALTER TABLE t DISABLE KEYS; ... ENABLE KEYS; |
| MyISAM | 禁用唯一檢查 | SET UNIQUE_CHECKS=0; ... =1; |
| MyISAM | 批次 INSERT / LOAD DATA INFILE | INSERT INTO t VALUES (...),(...); |
| InnoDB | 禁用唯一檢查 + 外鍵檢查 | SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; |
| InnoDB | 關閉自動提交 / 顯式事務 | SET AUTOCOMMIT=0; ... COMMIT; |
注意：MyISAM 空表批次匯入不需禁用索引（匯入後才建索引）。
| 指令 | 用途 | 適用引擎 | 鎖行為 |
|---|---|---|---|
ANALYZE TABLE | 更新索引統計資訊（Cardinality） | InnoDB/MyISAM/BDB | 只讀鎖 |
CHECK TABLE | 檢查錯誤、更新統計 | InnoDB/MyISAM/View | 只讀鎖 |
OPTIMIZE TABLE | 整理碎片、回收空間（VARCHAR/BLOB/TEXT） | InnoDB/MyISAM | 只讀鎖 |
REPAIR TABLE | 修復損壞表 | MyISAM/ARCHIVE | 排他鎖 |
CHECKSUM TABLE | 驗證資料一致性 | MyISAM (Live Checksum) | 無鎖 |
頻率建議：OPTIMIZE 週/月級，針對高變動表；ANALYZE 大量寫入後執行。
max_connections=500~800：最大連線數，視主機記憶體調整max_user_connections：單用戶連線上限，共享主機環境需限制net_buffer_length=16K、max_allowed_packet=16M~1G：網路封包緩衝back_log=50~65535：連線等待佇列，瞬時高併發需調大，同步調整 OS somaxconnthread_cache_size：短連接環境設大（≥ 併發數），長連接 50~100。命中率 (Connections-Threads_created)/Connections > 90%table_open_cache = max_connections * N（N=單查詢最大表數），實際視 Open Tables 狀態調整join_buffer_size=1M~2M：Full Join (ALL/index/range) 時每表分配，慎防 OOMsort_buffer_size=2M~4M：ORDER BY/GROUP BY 排序緩衝，每 Thread 獨佔read_buffer_size、read_rnd_buffer_size：全表掃描/排序讀取緩衝innodb_buffer_pool_size：專用記憶體 60~80%，核心快取innodb_log_file_size=256M~1G：Redo Log 檔案大小，減少 Checkpoint 壓力innodb_flush_log_at_trx_commit=2：效能/安全平衡innodb_flush_method=O_DIRECT：避免雙重緩衝innodb_io_capacity：SSD 5000，SAS 200innodb_max_dirty_pages_pct=75：髒頁上限[mysqld]
query_cache_type = 1
query_cache_size = 512Mquery_cache_type=1：啟用，除 SQL_NO_CACHE 外皆快取FLUSH QUERY CACHE：整理碎片風險提示：寫入頻繁、命中率低時 Query Cache 反成瓶頸（全域鎖）。生產環境強烈建議：關閉 Query Cache，改用 Memcached/Redis 應用層快取，精確控制失效策略。
SHOW GLOBAL STATUS、pt-query-digest 分析慢查詢EXPLAIN 驗證索引、改寫子查詢、消除 Using temporary/filesort優化無終點，唯有「測量 → 分析 → 調整 → 驗證」的持續閉環，才能在業務增長中保持資料庫高效穩定運行。
