MySQL 效能優化實戰：從表維護、大量匯入到 SQL 語句調優完全指南

一、定期表維護三大核心指令

1. ANALYZE TABLE — 更新統計資訊，優化執行計畫

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...;

• 分析並儲存索引關鍵字分布，讓優化器產生精準執行計畫。
• 適用於 MyISAM、InnoDB、BDB。
• 執行期間施加讀取鎖定，建議離峰時段執行。
• 適用情境：查詢執行計畫不如預期、大量資料異動後。

2. CHECK TABLE — 檢測錯誤與更新統計

CHECK TABLE tbl_name [, tbl_name] ... [option] ...;
-- option: QUICK | FAST | MEDIUM | EXTENDED | CHANGED

• 檢查表是否損壞，MyISAM 會同步更新關鍵字統計。
• 也可檢視 View 定義引用的表是否存在。
• EXTENDED 最完整但最慢，QUICK 僅檢查索引樹。

3. OPTIMIZE TABLE — 整理碎片、回收空間

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...;

• 適用情境：大量刪除資料、更新可變長度欄位（VARCHAR、BLOB、TEXT）。
• 合併空間碎片，消除刪除/更新造成的空洞。
• 僅支援 MyISAM、BDB、InnoDB。
• 執行期間鎖表，大表操作耗時極長，務必排程在維護窗口。

二、大量資料匯入效能優化策略

MyISAM 引擎：停用/啟用非唯一索引

ALTER TABLE tbl_name DISABLE KEYS;
-- 執行大量 INSERT / LOAD DATA INFILE
ALTER TABLE tbl_name ENABLE KEYS;

• DISABLE KEYS 暫停非唯一索引更新，資料載入完畢後 ENABLE KEYS 重建索引，大幅提升速度。
• 空表匯入預設即為「先載資料、後建索引」，無需額外設定。
• LOAD DATA INFILE 比多條 INSERT 快約 20 倍。

InnoDB 引擎：四大優化關鍵

1. 主鍵順序排序：InnoDB 按主鍵叢集儲存，預先按主鍵排序匯入資料可避免頁分裂，大幅提升效能。
2. 關閉唯一性檢查：

   SET UNIQUE_CHECKS = 0; -- 匯入前
   SET UNIQUE_CHECKS = 1; -- 匯入後
   

3. 關閉自動提交：

   SET AUTOCOMMIT = 0; -- 匯入前
   SET AUTOCOMMIT = 1; -- 匯入後
   

4. 調大 InnoDB Log Buffer：臨時調大 innodb_log_buffer_size 減少磁碟同步頻率。

三、INSERT 語句優化五大技巧

1. 多值單一 INSERT：INSERT INTO t VALUES (1,2), (3,4), (5,6); 減少客戶端往返開銷。
2. INSERT DELAYED：不同客戶端並發寫入時，資料先入記憶體佇列再非同步寫入磁碟，提升吞吐（MyISAM 支援）。
3. 索引與資料分離存放：將索引檔與資料檔置於不同磁碟（透過符號連結或 DATA DIRECTORY/INDEX DIRECTORY）。
4. 調大 bulk_insert_buffer_size：僅對 MyISAM 生效，批次插入時加速。
5. 優先使用 LOAD DATA INFILE：大量文字檔匯入的最佳選擇。

四、常用 SQL 優化原則

• 避免 SELECT *：只查詢所需欄位，減少網路傳輸與 I/O。
• 善用覆蓋索引：查詢欄位全包含於索引中，避免回表。
• LIMIT 分頁優化：大偏移量時改用 WHERE id > last_id LIMIT n 方式（詳見後續文章）。
• 避免在 WHERE 條件欄位上使用函數：導致索引失效。
• EXPLAIN 分析執行計畫：重點關注 type、key、rows、Extra 欄位。

五、維護操作的黃金法則

ANALYZE、CHECK、OPTIMIZE 執行期間皆會鎖表，務必在資料庫低負載時段（如深夜）執行，並監控執行時間避免影響線上服務。