mysql快速保存插入大量数据一些方法总结

在mysql中如果上百万级的数据我们要插入到数据表中是不可以使用普通insert into来操作的,一般推荐使用load file或存储过程来导入数据,下面我总结了一些方法与各位分享一下.

说明:这几天尝试了使用不同的存储引擎大量插入MySQL表数据,主要试验了MyISAM存储引擎和InnoDB,下面是实验过程.

实现:一、InnoDB存储引擎.

创建数据库和表,代码如下:

1. > CREATE DATABASE ecommerce; 
2. > CREATE TABLE employees (      
3.     id INT NOT NULL,      
4.     fname VARCHAR(30),      
5.     lname VARCHAR(30),   
6.     birth TIMESTAMP,     
7.     hired DATE NOT NULL DEFAULT '1970-01-01',      
8.     separated DATE NOT NULL DEFAULT '9999-12-31',      
9.     job_code INT NOT NULL,      
10.     store_id INT NOT NULL     
11. )       
12. partition BY RANGE (store_id) (      
13.     partition p0 VALUES LESS THAN (10000),      
14.     partition p1 VALUES LESS THAN (50000),      
15.     partition p2 VALUES LESS THAN (100000),      
16.     partition p3 VALUES LESS THAN (150000), 
17.     Partition p4 VALUES LESS THAN MAXVALUE      
18. );  

创建存储过程,代码如下:

1. > use ecommerce; 
2. > delimiter //   delimiter命令来把语句定界符从;变为//，不然后面的存储过程会出错。到declare var int;mysql就停止 
3. > CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT) 
4. BEGIN 
5.  DECLARE Var INT; 
6.  DECLARE ID INT; 
7.  SET Var = 0; 
8.  SET ID = init; 
9.       WHILE Var < loop_time DO 
10.       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); 
11.       SET ID = ID + 1; 
12.       SET Var = Var + 1; 
13.       END WHILE; 
14. END; 
15. // 
16. > delimiter ; 

把定界符变回,调用存储过程插入数据,代码如下:

> CALL BatchInsert(30036,200000);

用时:3h 37min 8sec 

二、MyISAM存储引擎

创建表,代码如下:

1. > use ecommerce; 
2. > CREATE TABLE ecommerce.customer ( 
3.    id INT NOT NULL, 
4.    email VARCHAR(64) NOT NULL, 
5.    name VARCHAR(32) NOT NULL, 
6.    password VARCHAR(32) NOT NULL, 
7.    phone VARCHAR(13), 
8.    birth DATE, 
9.    sex INT(1), 
10.    avatar BLOB, 
11.    address VARCHAR(64), 
12.    regtime DATETIME, 
13.    lastip VARCHAR(15), 
14.    modifytime TIMESTAMP NOT NULL, 
15.   PRIMARY KEY (id) 
16. )ENGINE = MyISAM ROW_FORMAT = DEFAULT 
17. partition BY RANGE (id) (      
18.     partition p0 VALUES LESS THAN (100000),      
19.     partition p1 VALUES LESS THAN (500000),      
20.     partition p2 VALUES LESS THAN (1000000),      
21.     partition p3 VALUES LESS THAN (1500000), 
22.     partition p4 VALUES LESS THAN (2000000), 
23.     Partition p5 VALUES LESS THAN MAXVALUE      
24. );  

创建存储过程,代码如下:

1. > use ecommerce; 
2. > DROP PROCEDURE IF EXISTS ecommerce.BatchInsertCustomer; 
3. > delimiter // 
4. > CREATE PROCEDURE BatchInsertCustomer(IN start INT,IN loop_time INT) 
5. BEGIN 
6.  DECLARE Var INT; 
7.  DECLARE ID INT; 
8.  SET Var = 0; 
9.  SET ID= start; 
10.       WHILE Var < loop_time  
11.         DO 
12.       insert into customer(ID,email,name,password,phone,birth,sex,avatar,address,regtime,lastip,modifytime)  
13.   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)); 
14.       SET Var = Var + 1;  //Vevb.com 
15.       SET ID= ID + 1; 
16.       END WHILE; 
17. END; 
18. // 
19. > delimiter ;  

调用存储过程插入数据,代码如下:

1. > ALTER  TABLE  customer  DISABLE  KEYS;  
2. > CALL BatchInsertCustomer(1,2000000); 
3. > 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类型的表,可以通过以下方式快速的导入大量的数据,代码如下:

1. ALTER  TABLE  tblname  DISABLE  KEYS; 
2.  
3. loading  the  data 
4.  
5.  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,关闭自动提交,导入结束后再执行.