資料庫優化涵蓋架構、SQL、參數、硬體多層面。本文聚焦 DBA 可直接操作的實戰技巧：定期表維護、大批量導入加速、常用 SQL 改寫模式與核心參數調優清單。
| 操作 | 語法 | 適用引擎 | 作用 | 鎖行為 |
|---|---|---|---|---|
| ANALYZE TABLE | ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE t1, t2... | MyISAM/InnoDB/BDB | 更新索引統計資訊，優化器生成正確執行計畫 | 讀鎖 |
| CHECK TABLE | CHECK TABLE t1 [QUICK|FAST|MEDIUM|EXTENDED|CHANGED] | MyISAM/InnoDB | 檢測錯誤、更新關鍵字統計、驗證 View 定義 | 讀鎖 |
| OPTIMIZE TABLE | OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE t1... | MyISAM/BDB/InnoDB | 碎片整理、回收刪除/更新空間、重排索引 | 寫鎖 |
注意：維護期間鎖表，請在業務低峰期執行。
ALTER TABLE tbl DISABLE KEYS; -- 關閉非唯一索引更新
LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
ALTER TABLE tbl ENABLE KEYS; -- 重建索引（一次排序更快）空表導入預設即為「先導資料後建索引」，無需手動控制。
SET UNIQUE_CHECKS=0; 關閉唯一檢查 → 導入 → SET UNIQUE_CHECKS=1;SET AUTOCOMMIT=0; 關閉自動提交 → 導入 → SET AUTOCOMMIT=1;（單一事務提交）| 場景 | 優化寫法 | 效能增益 |
|---|---|---|
| 單客戶端多行 | 多值 INSERT：INSERT INTO t VALUES (1,2),(3,4),(5,6); | 減少連線/解析開銷，比單條快數倍 |
| 多客戶端並發寫入 | INSERT DELAYED 寫入記憶體佇列非同步落盤 | 極大降低阻塞，但不保證即時可見 |
| 索引/資料分盤 | 建表指定 DATA DIRECTORY/INDEX DIRECTORY | 分散 I/O 壓力 |
| 批次導入 | 調大 bulk_insert_buffer_size（僅 MyISAM） | 減少臨時檔 I/O |
| 文本檔導入 | LOAD DATA INFILE 比多條 INSERT 快 20 倍 | 極致導入速度 |
YEAR(date) < 2007 → date < '2007-01-01'（索引生效）NOT IN / <> → NOT EXISTS / id>3 OR id<3LIKE '%abc%' → 全文索引或應用層搜尋引擎SELECT *，明確列出欄位（覆蓋索引機會）LIMIT 100000, 20 → 子查詢主鍵定位 + JOIN| 參數 | 建議值（2GB RAM 例） | 說明 |
|---|---|---|
| key_buffer_size | 400MB | MyISAM 索引緩衝 |
| innodb_buffer_pool_size | 1.2GB | InnoDB 資料+索引緩衝（核心） |
| max_connections | 1024 | 避免 Too many connections |
| thread_cache_size | 80 | 線程複用，降低建立開銷 |
| table_cache | 512 | 表描述符快取 |
| sort_buffer_size | 16MB | ORDER BY/GROUP BY 排序緩衝 |
| read_buffer_size | 16MB | 全表掃描順序讀緩衝 |
| wait_timeout / interactive_timeout | 7200 | 釋放閒置連線 |
| back_log | 500 | 連線爆發時排隊緩衝 |
修改 /etc/my.cnf 後重啟生效；參數調優需配合 SHOW GLOBAL STATUS 觀察實際命中率與等待狀態迭代。
