萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 效能調優進階:INSERT 加速、LOAD DATA INFILE 大量導入與鎖表策略

INSERT 單筆耗時拆解

  • 連線建立:~3
  • 發送查詢:~2
  • 解析查詢:~2
  • 寫入記錄:~1 × 記錄大小
  • 更新索引:~1 × 索引數
  • 關閉:~1

(單位為相對時間單位)表大小隨 N log N(B-Tree)影響索引插入速度。

加速 INSERT 實戰技巧

1. 批次多值 INSERT(單連線多行)

INSERT INTO t (a,b) VALUES (1,2), (3,4), (5,6), ...;

比循環單條 INSERT 快數倍(減少網路往返、解析、鎖開銷)。

2. 異步插入 INSERT DELAYED(MyISAM/MEMORY)

客戶端立即返回,伺服器排隊寫入。適合日誌、統計類非即時業務。**InnoDB 不支援**(會轉為普通 INSERT)。

3. MyISAM 並發插入

表無刪除產生的「洞」時,SELECT 與 INSERT 可並發(尾部追加)。

大量導入終極武器:LOAD DATA INFILE

官方測試比多條 INSERT **快 20 倍**。

極速導入流程(適用 MyISAM/InnoDB)

  1. 建表(不建索引):CREATE TABLE ...
  2. FLUSH TABLES; 或 mysqladmin flush-tables
  3. 禁用索引:myisamchk --keys-used=0 -rq /path/to/db/tbl(MyISAM)
    或 ALTER TABLE tbl DISABLE KEYS;(MyISAM 專用)
  4. 導入資料:LOAD DATA INFILE 'file.txt' INTO TABLE tbl ...(不更新索引,極快)
  5. (可選)壓縮唯讀表:myisampack /path/to/db/tbl
  6. 重建索引:myisamchk -r -q /path/to/db/tbl(記憶體中建樹,一次寫盤,索引完美平衡)
  7. FLUSH TABLES;

鎖表批次插入(InnoDB 同樣受益)

LOCK TABLES t WRITE;
INSERT INTO t VALUES (...), (...), ...;  -- 批次 1000 行
INSERT INTO t VALUES (...), (...), ...;
UNLOCK TABLES;

優勢:索引緩衝區僅在 UNLOCK 時刷盤一次,而非每條 INSERT 刷盤。
注意:鎖表會阻塞其他寫入/讀取(視隔離級別),適合離線維護窗口或低併發時段。

總結

  • 日常業務:批次 INSERT ... VALUES (),(),()。
  • 大量初始化/遷移:LOAD DATA INFILE + 禁用/重建索引。
  • 高併發寫入優化:調整 innodb_flush_log_at_trx_commit、innodb_buffer_pool_size、使用預備語句。
copyright © 萬盛學電腦網 all rights reserved