MySQL 資料庫優化全攻略：定期維護、大批量插入、INSERT 優化實戰

一、定期資料表維護

1. 分析表

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

分析關鍵字分布，更新統計資訊，讓優化器產生正確執行計畫。執行期間以讀取鎖鎖定表。適用 MyISAM、BDB、InnoDB。

ANALYZE TABLE table_name;

2. 檢查表

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

檢查錯誤，MyISAM 會更新關鍵字統計。也可檢視 View 定義引用的表是否存在。

3. 優化表

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

適用場景：大量刪除、含可變長欄位（VARCHAR/BLOB/TEXT）頻繁更新。功能：合併空間碎片、消除刪除/更新造成的空間浪費。僅支援 MyISAM、BDB、InnoDB。

OPTIMIZE TABLE table_name;

注意：上述操作皆會鎖表，務必在業務低峰期執行。

二、常用 SQL 優化

1. 大批量插入資料優化

MyISAM 表

ALTER TABLE tbl_name DISABLE KEYS;
-- 載入資料
ALTER TABLE tbl_name ENABLE KEYS;

關閉/開啟非唯一索引更新，大幅提升導入效率。空表導入預設即先導資料後建索引，無需設定。

InnoDB 表

• 資料按主鍵順序排序後導入
• 導入前 SET UNIQUE_CHECKS=0，結束後 SET UNIQUE_CHECKS=1
• 關閉自動提交：SET AUTOCOMMIT=0 → 導入 → SET AUTOCOMMIT=1

2. INSERT 語句優化

1. 多值單語句：INSERT INTO test VALUES (1,2),(3,4),(5,6)... 減少連線開銷
2. INSERT DELAYED：不同客戶端並發插入時，資料先入記憶體佇列，非同步寫入磁碟，速度更快
3. 索引與資料檔分盤：減少 IO 爭用
4. 調大 bulk_insert_buffer_size：僅對 MyISAM 有效
5. LOAD DATA INFILE：從文字檔載入，比多條 INSERT 快 ~20 倍

三、總結

結合定期維護（分析/檢查/優化）與針對性 SQL 優化（批量插入策略、INSERT 寫法、LOAD DATA），可充分釋放 MySQL 效能。