Mysql中Innodb大量插入数据时SQL语句的优化

时间：2022-08-11 09:52:14

innodb优化后，29小时入库1300万条数据

参考：http://blog.51yip.com/mysql/1369.html

对于Myisam类型的表，可以通过以下方式快速的导入大量的数据：

ALTER TABLE tblname DISABLE KEYS;
    loading the data
    ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。

加载大量数据时，关闭非唯一索引，取消唯一性检查，以及取消自动提交以提高插入速度

set unique_checks=0

alter table stu disable keys

set autocommit=0

load load infile........

alter table stu enable keys

set unique_checks=1

set autocommit =1

没有使用打开或关闭MyISAM表非唯一索引：
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2 fields
terminated by “,”;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0

使用打开或关闭MyISAM表非唯一索引：
mysql> alter table film_test2 disable
keys;
Query OK,0 rows affected (0.0
sec)
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2;
Query OK,529056 rows affected
(6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2 enable
keys;
Query OK,0 rows affected (12.25
sec)
以上对MyISAM表的数据导入，但对于InnoDB表并不能提高导入数据的效率

对于Innodb类型的表，我们有以下几种方式可以提高导入的效率：

  • 因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
  • 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
  • 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。

使用test3.txt文本是按表film_test4主键存储顺序保存的
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
使用test3.txt没有任何顺序的文本（效率慢了1.12倍）
mysql> load data infile ‘/home/mysql/film_test4.txt’into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

关闭唯一性效验可以提高导入效率

在导入数据前先执行set unique_checks=0，关闭唯一性效验，在导入结束后执行set unique_checks=1，恢复唯一性效验，可以提高导入效率。

当unique_checks=1时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
当unique_checks=0时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

关闭自动提交可以提高导入效率

在导入数据前先执行set autocommit=0，关闭自动提交事务，在导入结束后执行set autocommit=1，恢复自动提交，可以提高导入效率。

当autocommit=1时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
当autocommit=0时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

知识点的补充：20160318

这里主要是针对innodb的优化

    1. init_connect='SET autocommit=0'    //关闭自动提交，这个对于innodb来说，很重要
    2. innodb-file-per-table=1            //使用独立表空间
    3. innodb-open-file=500               //打开最大文件数据是500，默认是300
    4. innodb_log_file_size=512M          //log文件大小
    5. innodb_log_buffer_size=8M          //缓冲日志数据的缓冲区的大小
    6. innodb_flush_log_at_trx_commit=0   //提交数据等级0是最快，但是有可能会丢数据
    7. innodb_buffer_pool_size=5G         //缓冲池大小，我把它设置内在的65%
    8. innodb_log_files_in_group=3        //日志文件总数
    9. innodb_file_io_threads=8           //根cpu核数是一样，读写文件进程数
    10. event_scheduler=1                  //开启动mysql event
    11. //添加一个mysql event
    12. CREATE EVENT `commit_event` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-01-04 19:06:26' ON
    13. COMPLETION NOT PRESERVE ENABLE DO COMMit

1,自动提交对innodb的影响非常大的，这个我做过测试，请参考,mysql autocommit对myisam,innodb的性能影响.

2,innodb_flush_log_at_trx_commit我把它设置成0，我只要求速度最快，最是统计推广的弹窗，

这些数据只是为了我们后期统计和分析用的，没有太大的价值。如果数据很重要就不要设置成0了。

0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
1InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上
2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.

3,还有一点就是我用了mysql event功能，根linux的crontab差不多。

这里对：mysql event的补充   --- http://www.cnblogs.com/end/archive/2011/04/21/2023725.html

相关文章