如何提高mysql插入数据的速度

提高mysql插入数据的速度

在myisam engine下:

1. 尽量使用insert into table_name values (…), (…),(…)这样的形式插入数据，避免使用inset into table_name values (); inset into table_name values (); inset into table_name values ();
2. 增加bulk_insert_buffer_size(默认8M)
3. 如果是非空表，使用alter table table_name disable keys，然后load data infile,导入完数据在执行：alter table table_name enable keys. 如果是空表，就不需要这个操作，因为myisam表在空表中导入数据时，是先导入数据然后建立indexs。
4. 在插入数据时考虑使用：insert delayed…这样操作实际mysql把insert操作放到队列里面，进行相对集中的插入，速度更快。
5. 使用load data infile 比使用insert 操作快近20倍，尽量使用此操作。

在innodb engine下

1. 导入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1
2. 导入数据之前执行set foreign_key_checks=0来禁止对外键的检查，数据导入完成之后再执行set foreign_key_checks=1
3. 导入数据之前执行set autocommit=0禁止自动事务的自动提交，数据导入完成之后，执行set autocommit=1 恢复自动提交操作
4. 使用innodb engine的表，物理存储都是按PK的顺序存的。不能使用类似于myisam一样disable keys.硬件上提高磁盘的I/0对插入速度很有好处（所以如果进行大数据量的导入导出工作，尽量在比较NB的硬件上进行，能缩减完成的时间，已经防止出现问题）。

其他资料：

1. 如果mysql的data数据很少，内存足够大，可以把data防止到内存盘中。linux如下设置内存盘：mount -t ramfs none /ram默认使用内存一半如果内存不够大，系统有多个硬盘，则把mysql应用程序和data目录分开到不同硬盘上。

2. mysql的表设置为myiasm，比同等条件下的innodb能快20倍以上

3. 导入完成以后才创建数据库索引

4. 导入完成以后根据需要转换为其他engine，比如innodb

5. 大文件sql文件可以用split分成多份再导

6. 同等条件下，redhat比ubuntu强很多（几乎肯定）

7. 性能调整技巧(Performance tuning tips):
   如果 Unix top 或 Windows 任务管理器(Task Manager) 显示服务的 CPU 占用率小于 70%，(shows that the CPU usage percentage with your workload is less than 70 %,）你的系统瓶颈可能在磁盘读写上。或许你提交了大量的事务，或者是缓冲池(buffer pool)太小了。将缓冲池设大点会有所帮助，但一定要注意不能大于物理内存的 80%。

8. 在一个事务中包含几个修改。如果事务对数据库进行了修改，那么在这个事务提交时 InnoDB 必须刷新日志到磁盘上。因为硬盘的旋转速度通常至多为 167 转/秒，那么只要磁盘不欺骗操作系统，提交的事务数目限止也同样为 167 次/秒·用户。

9. 如果掉失最近的几个事务无所谓的话，可以在 my.cnf 文件中将参数 innodb_flush_log_at_trx_commit 设置为 0。InnoDB 无论如何总是尝试一秒刷新(flush)一次日志，尽管刷新并不能得到保证。

10. 将日志文件(log files)设大一点，使日志文件的总和正好与缓冲池(buffer pool)一样大。当 InnoDB 用光日志文件的空间时，它不得不在一个时间点上将缓冲池内修改过的内容写到磁盘上。 小的日志文件可能引起不必要的磁盘写操作。但是大的日志文件的缺点就是在数据恢复时将占用较长的时间。

11. 同样 log buffer 尽量设大点，比如说 8 MB。

12. 如果要存储变长的字符串或字段可能会包含大量的 NULLs，请使用 VARCHAR 型字段代替 CHAR 。一个 CHAR(n) 字段总是使用 n bytes 来存储数据，即使这个字符串很短或是一个 NULL 值。较小的表更加适合缓冲池同时能够减少磁盘 I/O 。

13. (适合从 3.23.41 以上版本) 在某些版本的 Linux 和 Unixes 中，使用 Unix fsync 或其它类似的方法将文件刷新到磁盘是异常地慢的。InnoDB 默认的方法就是 fsync 。如果你对数据库系统的磁盘写性能不能感到满意，你可以尝试在 my.cnf 中将 innodb_flush_method 设置为 O_DSYNC，尽管 O_DSYNC 选项在多数的系统上看起来比较慢。

14. 在向 InnoDB 导入数据时，请确认 MySQL 没有打开 autocommit=1 。否则每个插入语句都要将 log 刷新到磁盘。在你的 SQL 导入文件的第一行加入set autocommit=0;并在最后一行加入
    commit;如果使用 mysqldump 选项 –opt，你将会得到一个快速导入 InnoDB 表的转储(dump)文件，甚至可以不再使用上面所提的 set autocommit=0; … commit; 。

15. 小心 insert 集全的大回滚(roolback)：在插入时 InnoDB 使用插入缓冲来减少磁盘 I/O，但在相应的回滚中却没有使用这样的机制。一个 disk-bound rollback 可能会花费相应插入时间的 30 倍。

16. 同样也要小心一个大的 disk-bound 的操作。使用 Drop TABLE 或 TRUNCATE (从 MySQL-4.0 以上) 来清空一个表，而不要使用 Delete FROM yourtable。

17. 如果需要插入大量记录行可以使用多行(multi-line)的 Insert 来减少客户端与服务器端的通信开销：Insert INTO yourtable VALUES (1, 2), (5, 5);

18. 如果在辅键上有 UNIQUE 约束，从 3.23.52 和 4.0.3 开始，可以通过在一个导入会话中将唯一键检查(uniqueness check)关闭来提高数据导入速度：SET UNIQUE_CHECKS=0;
    一个大的表导入这将减少大量的磁盘 I/O，因为这时 InnoDB 可能使用自身的插入缓冲来分批地记录辅助索引。

19. 如果在表中有一个子 FOREIGN KEY 约束，从 3.23.52 和 4.0.3 开始，可以通过在一个导入会话中将外键检查(foreign key check)关闭来提高数据导入速度：SET FOREIGN_KEY_CHECKS=0;对一个大的表导入这将减少大量的磁盘 I/O。