MySQL插入大量数据调优

1. SQL语句级别调优：

> 使用存储过程：(一百万条数据分25万次循环，每次循环插入4条数据）

 如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。

DELIMITER $$

CREATE  PROCEDURE Pro_InsertMillionRecords()

BEGIN

   DECLARE _End int default 0;

   WHILE(_End < 250000)

   DO

       INSERT customer(cname) SELECT name FROM names ORDER BY RAND() LIMIT 1;

       INSERT customer(cname) SELECT name FROM names ORDER BY RAND() LIMIT 1;

       INSERT customer(cname) SELECT name FROM names ORDER BY RAND() LIMIT 1;

       INSERT customer(cname) SELECT name FROM names ORDER BY RAND() LIMIT 1;

       SET _End = _End +1;

   END WHILE;

END $$

DELIMITER ;

 

> MySQL只有InnoDB存储引擎支持事务，事务默认为自动提交模式，即没插入一条数据就提交一次。这样显然效率低下。可以在插入数据前关闭自动提交模式。完成插入后再开启自动提交模式。

SET AUTOCOMMIT=0;

----

COMMIT:

SET AUTOCOMMIT=1;

> 锁定表：LOCK TABLE t1 locktype, t2 locktype

如果locktype为READ，即表示锁定该表，该表不能被写，只提供读，除非获取了该表的读锁。

如果locktype为WRITE，即表示锁定该表，该表不能被读，只能写，除非获取了该表的写锁。

LOCK TABLE customer WRITE, names READ;

插入数据完成后，再解除表锁定：

UNLOCK TABLES;

 

通过以上的措施，我在往表中插入一百万条数据，由原来的平均2分9秒变为现在的1分36秒，足足提速33秒。

另外其实那条插入数据的SQL语句

INSERT customer(cname) SELECT name FROM names ORDER BY RAND() LIMIT 1;

也是性能瓶颈所在，如果我把它换成单纯的插入数据：

INSERT INTO customer (cname) VALUES ('John');

则不需要经过上面的3个调优措施也只花了49秒插入一百万条数据。

只不过我不知道如何替换这条语句，只好作罢。

 

> 为某些字段建立索引，我尝试的例子用不上这个.MySQL默认为主键创建索引。

 

2. 调节服务器参数：http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#server-parameters

   通过下面的语句看到运行服务器的统计和状态指标：

mysql>SHOW STATUS;  MySQL使用完全可以升级的算法，因此通常运行时可以用很少的内存。然而，通常情况若给MySQL更多的内存性能会更好。

当调节MySQL服务器时，要配置的两个最重要的变量是key_buffer_size和table_cache。在试图更改其它变量前你应先确信已经适当地配置了这些变量。

 

    下面内容参考自：http://www.douban.com/group/topic/6097647/

    批量插入暂存使用内存(bulk_insert_buffer_size)：当我们使用如 insert … values(…),(…),(…)… 的方式进行批量插入的时候，MySQL 会先将提交的数据放如一个缓存空间中，当该缓存空间被写满或者提交完所有数据之后，MySQL 才会一次性将该缓存空间中的数据写入数据库并清空缓存。

    临时表使用内存(tmp_table_size)：当我们进行一些特殊操作如需要使用临时表才能完成的 Order By，Group By 等等，MySQL 可能需要使用到临时表。当我们的临时表较小（小于 tmp_table_size 参数所设置的大小）的时候，MySQL 会将临时表创建成内存临时表，只有当 tmp_table_size 所设置的大小无法装下整个临时表的时候，MySQL 才会将该表创建成 MyISAM 存储引擎的表存放在磁盘上。