mysql快速保存插入大量數據一些方法總結

在mysql中如果上百萬級的數據我們要插入到數據表中是不可以使用普通insert into來操作的，一般推薦使用load file或存儲過程來導入數據，下面我總結了一些方法與各位分享一下。

說明：

這幾天嘗試了使用不同的存儲引擎大量插入MySQL表數據，主要試驗了MyISAM存儲引擎和InnoDB。下面是實驗過程：

實現：
一、InnoDB存儲引擎。
創建數據庫和表

 代碼如下 復制代碼 > CREATE DATABASE ecommerce;
> CREATE TABLE employees (    
    id INT NOT NULL,    
    fname VARCHAR(30),    
    lname VARCHAR(30), 
    birth TIMESTAMP,   
    hired DATE NOT NULL DEFAULT '1970-01-01',    
    separated DATE NOT NULL DEFAULT '9999-12-31',    
    job_code INT NOT NULL,    
    store_id INT NOT NULL   
)     
partition BY RANGE (store_id) (    
    partition p0 VALUES LESS THAN (10000),    
    partition p1 VALUES LESS THAN (50000),    
    partition p2 VALUES LESS THAN (100000),    
    partition p3 VALUES LESS THAN (150000),
    Partition p4 VALUES LESS THAN MAXVALUE    
);

創建存儲過程

 代碼如下 復制代碼 > use ecommerce;
> delimiter //   delimiter命令來把語句定界符從;變為//，不然後面的存儲過程會出錯。到declare var int;mysql就停止
> CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)
BEGIN
 DECLARE Var INT;
 DECLARE ID INT;
 SET Var = 0;
 SET ID = init;
      WHILE Var < loop_time DO
      insert into employees(id,fname,lname,birth,hired,separated,job_code,store_id) values(ID,CONCAT('chen',ID),CONCAT('haixiang',ID),Now(),Now(),Now(),1,ID);
      SET ID = ID + 1;
      SET Var = Var + 1;
      END WHILE;
END;
//
> delimiter ; 

  把定界符變回;
調用存儲過程插入數據

 代碼如下 復制代碼 > CALL BatchInsert(30036,200000);

用時：3h 37min 8sec
二、MyISAM存儲引擎
創建表

 代碼如下 復制代碼 > use ecommerce;
> CREATE TABLE ecommerce.customer (
   id INT NOT NULL,
   email VARCHAR(64) NOT NULL,
   name VARCHAR(32) NOT NULL,
   password VARCHAR(32) NOT NULL,
   phone VARCHAR(13),
   birth DATE,
   sex INT(1),
   avatar BLOB,
   address VARCHAR(64),
   regtime DATETIME,
   lastip VARCHAR(15),
   modifytime TIMESTAMP NOT NULL,
  PRIMARY KEY (id)
)ENGINE = MyISAM ROW_FORMAT = DEFAULT
partition BY RANGE (id) (    
    partition p0 VALUES LESS THAN (100000),    
    partition p1 VALUES LESS THAN (500000),    
    partition p2 VALUES LESS THAN (1000000),    
    partition p3 VALUES LESS THAN (1500000),
    partition p4 VALUES LESS THAN (2000000),
    Partition p5 VALUES LESS THAN MAXVALUE    
);

創建存儲過程

 代碼如下 復制代碼 > use ecommerce;
> DROP PROCEDURE IF EXISTS ecommerce.BatchInsertCustomer;
> delimiter //
> CREATE PROCEDURE BatchInsertCustomer(IN start INT,IN loop_time INT)
BEGIN
 DECLARE Var INT;
 DECLARE ID INT;
 SET Var = 0;
 SET ID= start;
      WHILE Var < loop_time
        DO
      insert into customer(ID,email,name,password,phone,birth,sex,avatar,address,regtime,lastip,modifytime)
  values(ID,CONCAT(ID,'@sina.com'),CONCAT('name_',rand(ID)*10000 mod 200),123456,13800000000,adddate('1995-01-01',(rand(ID)*36520) mod 3652),Var%2,'http:///it/u=2267714161,58787848&fm=52&gp=0.jpg','北京市海澱區',adddate('1995-01-01',(rand(ID)*36520) mod 3652),'8.8.8.8',adddate('1995-01-01',(rand(ID)*36520) mod 3652));
      SET Var = Var + 1;
      SET ID= ID + 1;
      END WHILE;
END;
//
> delimiter ;

調用存儲過程插入數據

 代碼如下 復制代碼 > ALTER  TABLE  customer  DISABLE  KEYS;
> CALL BatchInsertCustomer(1,2000000);
> ALTER  TABLE  customer  ENABLE  KEYS;

用時：8min 50sec
通過以上對比發現對於插入大量數據時可以使用MyISAM存儲引擎，如果再需要修改MySQL存儲引擎可以使用命令：
ALTER TABLE t ENGINE = MYISAM;

另一文件

很久很久以前，為了寫某個程序，必須在MySQL數據庫中插入大量的數據，一共有85766121條。近一億條的數據，怎麼才能快速插入到MySQL裡呢？

當時的做法是用INSERT INTO一條一條地插入，Navicat估算需要十幾個小時的時間才能完成，就放棄了。最近幾天學習了一下MySQL，提高數據插入效率的基本原則如下：

» 批量插入數據的效率比單數據行插入的效率高
» 插入無索引的數據表比插入有索引的數據表快一些
» 較短的SQL語句的數據插入比較長的語句快
這些因素有些看上去是微不足道的，但是如果插入大量的數據，即使很小的影響效率的因素也會形成不同的結果。根據上面討論的規則，我們可以就如何快速地加載數據得出幾個實用的結論。

» 使用LOAD DATA語句要比INSERT語句效率高，因為它批量插入數據行。服務器只需要對一個語句（而不是多個語句）進行語法分析和解釋。索引只有在所有數據行處理完之後才需要刷新，而不是每處理一行都刷新。
» 如果你只能使用INSERT語句，那就要使用將多個數據行在一個語句中給出的格式：

INSERT INTO table_name VALUES(...),(...),...這將會減少你需要的語句總數，最大程度地減少了索引刷新的次數。

根據上面的結論，今天又對相同的數據和數據表進行了測試，發現用LOAD DATA速度快了不只是一點點，竟然只用了十多分鐘！所以在MySQL需要快速插入大量數據時，LOAD DATA是你不二的選擇。

順便說一下，在默認情況下，LOAD DATA語句將假設各數據列的值以制表符（t）分閣，各數據行以換行符（n）分隔，數據值的排列順序與各數據列在數據表裡的先後順序一致。但你完全可以用它來讀取其他格式的數據文件或者按其他順序來讀取各數據列的值，有關細節請參照MySQL文檔。

總結

1. 對於Myisam類型的表，可以通過以下方式快速的導入大量的數據。

ALTER  TABLE  tblname  DISABLE  KEYS;

loading  the  data

 ALTER  TABLE  tblname  ENABLE  KEYS;

這兩個命令用來打開或者關閉Myisam表非唯一索引的更新。在導入大量的數據到一 個非空的Myisam表時，通過設置這兩個命令，可以提高導入的效率。對於導入大量 數據到一個空的Myisam表，默認就是先導入數據然後才創建索引的，所以不用進行 設置。

2. 而對於Innodb類型的表，這種方式並不能提高導入數據的效率。對於Innodb類型 的表，我們有以下幾種方式可以提高導入的效率：

a. 因為Innodb類型的表是按照主鍵的順序保存的，所以將導入的數據按照主鍵的順 序排列，可以有效的提高導入數據的效率。如果Innodb表沒有主鍵，那麼系統會默認創建一個內部列作為主鍵，所以如果可以給表創建一個主鍵，將可以利用這個優勢提高 導入數據的效率。

b. 在導入數據前執行SET  UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束後執行SET  UNIQUE_CHECKS=1，恢復唯一性校驗，可以提高導入的效率。
c. 如果應用使用自動提交的方式，建議在導入前執行SET  AUTOCOMMIT=0，關閉自動 提交，導入結束後再執行