《面試官別再問》MySQL 大量寫入的加速技巧

在MySQL數據庫中，如果要插入上百萬級的記錄，用普通的來操作非常不現實，速度慢人力成本高，推薦使用或存儲過程來導入數據，我總結了一些方法分享如下，主要基於MyISAM和InnoDB引擎。insert intoLoad Data

1 InnoDB存儲引擎
首先建立資料庫（可選）：

> CREATE DATABASE ecommerce;
> USE  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
  );

然後建立存儲過程，其中，delimiter命令用來把語句定界符從;變為//，不然到遇上第一個分號MySQL就錯誤停止：declare var int;

> use  ecommerce ;
> DROP PROCEDURE BatchInser IF EXISTS;
> delimiter // --把界定符改成雙斜杠
> CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT) -- 第一個參數為初始ID號（可自定義），第二個位生成MySQL記錄個數
  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 ); --調用存儲過程插入函數

也可以把上面的內容（除了語句之前的>號）複製到MySQL查詢框中執行。



2 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 ecommerce.BatchInsertCustomer IF EXISTS;
> 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;

通過以上對比發現對於插入大量數據時可以使用MyISAM存儲引擎，如果再需要修改MySQL存儲引擎可以使用命令：

 ALTER  TABLE ecommerce ENGINE = MYISAM;

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

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

1. 批量插入數據的效率比單數據行插入的效率高
2. 插入無索引的數據表比插入有索引的數據表快一些
3. 較短的SQL語句的數據插入比較長的語句快

這些因素有些看上去是微不足道的，但是如果插入大量的資料，即使很小的影響效率的因素也會形成不同的結果。根據上面討論的規則，我們可以就如何快速地加載資料得出幾個實用的結論。

1. 使用語句要比語句效率高，因為它批量插入數據行。服務器只需要對一個語句（而不是多個語句）進行語法分析和解釋。索引只有在所有數據行處理完之後才需要刷新，而不是每處理一行都刷新。LOAD DATAINSERT
2. 如果你只能使用INSERT語句，那就要使用將多個數據行在一個語句中給出的格式：，這將會減少你需要的語句總數，最大程度地減少了索引刷新的次數。INSERT INTO table_name VALUES(...),(...),...

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

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

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

ALTER  TABLE tblname DISABLE  KEYS ;
loading the data
ALTER  TABLE tblname ENABLE  KEYS ;

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

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

因為Innodb類型的表是按照主鍵的順序保存的，所以將導入的數據按照主鍵的順序排列，可以有效的提高導入數據的效率。如果Innodb表沒有主鍵，那麼系統會默認創建一個內部列作為主鍵，所以如果可以給表創建一個主鍵，將可以利用這個優勢提高導入數據的效率。
在導入數據前執行，關閉唯一性校驗，在導入結束後執行，恢復唯一性校驗，可以提高導入的效率。SET  UNIQUE_CHECKS=0SET  UNIQUE_CHECKS=1
如果應用使用自動提交的方式，建議在導入前執行，關閉自動提交，導入結束後再執行SET  AUTOCOMMIT=0


參考資料：http://www.111cn.net/database/mysql/53274.htm

LOAD DATA基本語法：

load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'' ]]
[lines terminated by'n']
[ignore number lines]
[(co​​l_name, )]

load data infile語句從一個文本文件中以很高的速度讀入一個表中。使用這個命令之前，mysqld進程（服務）必須已經在運行。為了安全原因，當讀取位於服務器上的文本文件時，文件必須處於數據庫目錄或可被所有人讀取。另外，為了對服務器上文件使用load data infile，在服務器主機上你必須有file的權限。
1 如果你指定關鍵詞low_priority，那麼MySQL將會等到沒有其他人讀這個表的時候，才把插入數據。可以使用如下的命令：
load data low_priority infile "/home/mark/data sql" into table Orders;

2 如果指定local關鍵詞，則表明從客戶主機讀文件。如果local沒指定，文件必須位於服務器上。

3 replace和ignore關鍵詞控制對現有的唯一鍵記錄的重複的處理。如果你指定replace，新行將代替有相同的唯一鍵值的現有行。如果你指定ignore，跳過有唯一鍵的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複鍵時，出現一個錯誤，並且文本文件的餘下部分被忽略。例如：
load data low_priority infile "/home/mark/data sql" replace into table Orders;

4 分隔符
（1） fields關鍵字指定了文件記段的分割格式，如果用到這個關鍵字，MySQL剖析器希望看到至少有下面的一個選項：
terminated by分隔符：意思是以什麼字符作為分隔符
enclosed by字段括起字符
escaped by轉義字符
terminated by描述字段的分隔符，默認情況下是tab字符（t）
enclosed by描述的是字段的括起字符。
escaped by描述的轉義字符。默認的是反斜杠（backslash： ） 
例如：load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';
（2）lines 關鍵字指定了每條記錄的分隔符默認為'n'即為換行符
如果兩個字段都指定了那fields必須在lines之前。如果不指定fields關鍵字缺省值與如果你這樣寫的相同： fields terminated by't' enclosed by ' '' ' escaped by'\'
如果你不指定一個lines子句，缺省值與如果你這樣寫的相同： lines terminated by'n'
例如：load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';
5 load data infile 可以按指定的列把文件導入到數據庫中。當我們要把數據的一部分內容導入的時候，，需要加入一些欄目（列/字段/field）到MySQL數據庫中，以適應一些額外的需要。比方說，我們要從Access數據庫升級到MySQL數據庫的時候
下面的例子顯示瞭如何向指定的欄目(field)中導入數據：
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID);
6 當在服務器主機上尋找文件時，服務器使用下列規則：
（1）如果給出一個絕對路徑名，服務器使用該路徑名。
（2）如果給出一個有一個或多個前置部件的相對路徑名，服務器相對服務器的數據目錄搜索文件。
（3）如果給出一個沒有前置部件的一個文件名，服務器在當前數據庫的數據庫目錄尋找文件。
例如： /myfile txt”給出的文件是從服務器的數據目錄讀取，而作為“myfile txt”給出的一個文件是從當前數據庫的數據庫目錄下讀取。