MYSQL教程:查詢優化之有效加載數據

有效地加載數據

有時我們需大量地把數據加載到數據表，采用批量加載的方式比一個一個記錄加載效率高，因為MySQL不用每加載一條記錄就刷新一次索引。下面介紹幾個有助於加快數據加載的操作：

• 使用LOAD DATA語句要比INSERT語句的加載速度快。

• LOAD DATA比LOAD DATA LOCAL語句的效率高。前者可由服務器直接從本地磁盤讀取加載數據，後者需由客戶程序去讀取文件並通過網絡傳送到服務器。

• 如果一定要用INSERT語句，應盡量在一條語句中插入多個數據行。

• 如果必須使用多條INSERT語句，則應盡量把它們集中在一起放到一個事務中進行處理，而不是在自動提交模式下執行它們：如：

  BEGIN;
  INSERT INTO table_name values (...);
  INSERT INTO table_name values (...);
  INSERT INTO table_name values (...);
  ...
  COMMIT;
  

  對於不支持事務的表，應對表進行寫鎖定，然後在表鎖定期間對表進行INSERT操作，如：

  LOCK TABLES table_name WRITE;
  INSERT INTO table_name ...;
  INSERT INTO table_name ...;
  INSERT INTO table_name ...;
  ...
  UNLOCK TABLES;
  

• 利用客戶/服務器通信協議中的壓縮功能以減少網絡傳輸的數據量。但該壓縮會消耗大量的系統資源，所以小心使用。

• 盡量讓MySQL插入默認值。不要在INSERT中寫太多值，以減少網絡傳輸量和服務器端的語法分析時間。

• 對於MyISAM和ISAM數據表，如果需加載大量數據，應先建立一個沒索引的表，加載數據後再創建索引。該方法不適用於InnoDB或BDB數據表。

禁用和重新激活索引的方法有兩種：

• 使用ALTER TABLE語句的DISABLE KEYS和ENABLE KEYS命令，如：

  ALTER TABLE table_name DISABLE KEYS;
  ALTER TABLE table_name ENABLE KEYS;
  

• 使用myisamchk或isamchk工具。如：

  $ myisamchk --keys-used=0 table_name                   #禁止
  $ myisamchk --recover --quick --key-used=n table_name  #激活
  n是用來表明需要激活索引的位掩碼，第0位對應第一個索引，如果有三個索引，n值就是7(二進制111)。索引編號可以下命令確定：
  $ myisamchk --description table_name