MySQL資料庫維護與優化實戰：定期分析、檢查、優化表與大批量插入技巧

資料庫長期運行後會產生碎片、統計資訊過時、索引效能下降等問題。本文系統介紹MySQL表維護三大核心命令（ANALYZE、CHECK、OPTIMIZE）的使用時機與語法，並深度解析大批量插入與INSERT優化的多種實戰技巧。

表維護三大核心命令

1. ANALYZE TABLE — 更新統計資訊

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

• 分析並儲存關鍵字分布，使優化器生成正確執行計劃
• 執行期間加READ LOCK，適用MyISAM/InnoDB/BDB
• 適用場景：實際執行計劃偏離預期、大量資料變更後

ANALYZE TABLE table_name;

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

CHECK TABLE tbl_name [, tbl_name] ... [option]
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

• 檢查表結構完整性，MyISAM/InnoDB支援
• MyISAM同時更新關鍵字統計
• 可檢測View定義引用表不存在等問題

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

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

• 合併空間碎片，消除刪除/更新造成的空洞
• 適用：大量刪除、含可變長欄位（VARCHAR/BLOB/TEXT）頻繁更新的表
• 僅支援MyISAM/BDB/InnoDB
• 執行期間鎖表，必須在業務低峰期執行

OPTIMIZE TABLE table_name;

大批量資料導入優化策略

MyISAM表：關閉/開啟非唯一索引更新

ALTER TABLE tbl_name DISABLE KEYS;
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name;
ALTER TABLE tbl_name 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;

INSERT語句優化五大技巧

1. 多值單語句：INSERT INTO t VALUES (1,2),(3,4),(5,6)減少客戶端往返開銷
2. INSERT DELAYED：異步寫入記憶體隊列，適用多客戶端並發插入（僅MyISAM）
3. 索引/數據分離存放：不同磁碟存放.MYI與.MYD提升I/O並行
4. 調大bulk_insert_buffer_size：批量插入緩衝區，僅MyISAM生效
5. 優先使用LOAD DATA INFILE：文本導入效能最優

維護操作執行建議

• 建立定時任務（cron），業務低峰期（如凌晨2-4點）執行
• 大表OPTIMIZE耗時長，建議配合pt-online-schema-change類工具線上執行
• 監控Handler_read_rnd_next狀態值，持續偏高說明索引失效或全表掃描頻發

系統性的維護機制配合針對性的導入優化，是維持MySQL長期高效運行的基石。