这个值只对 MyISAM 类型数据库表有用, 对 innoDB 没有什么用. 最大设备 32M 就行了吧。 bulk_insert_buffer_sizeCommand-Line Format --bulk_insert_buffer_size=# Option-File Format bulk_insert_buffer_size System Variable Name bulk_insert_buffer_size Variable Scope Global, Session Dynamic Variable Yes Permitted Values Platform Bit Size 32 Type numeric Default 8388608 Min Value 0 Max Value 4294967295 Permitted Values Platform Bit Size 64 Type numeric Default 8388608 Min Value 0 Max Value 18446744073709547520 MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB你的不是Myisam存储引擎？
这个值只对 MyISAM 类型数据库表有用, 对 innoDB 没有什么用. 最大设备 32M 就行了吧。 bulk_insert_buffer_sizeCommand-Line Format --bulk_insert_buffer_size=# Option-File Format bulk_insert_buffer_size System Variable Name bulk_insert_buffer_size Variable Scope Global, Session Dynamic Variable Yes Permitted Values Platform Bit Size 32 Type numeric Default 8388608 Min Value 0 Max Value 4294967295 Permitted Values Platform Bit Size 64 Type numeric Default 8388608 Min Value 0 Max Value 18446744073709547520 MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB你的不是Myisam存储引擎？ 我用的是 innoDB 存储引擎, 我现在换了一个最新版本 5.7 mysql 哎, 原来 load data infile 的代码居然执行成功但没有插入数据库, 果然 orecle 就是出了一个坑。。各种语法在 MYSQL 5.7 版本前的都能用, 唯独在 5.7 上用就出问题.
bulk_insert_buffer_sizeCommand-Line Format --bulk_insert_buffer_size=#
Option-File Format bulk_insert_buffer_size
System Variable Name bulk_insert_buffer_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values
Platform Bit Size 32
Type numeric
Default 8388608
Min Value 0
Max Value 4294967295
Permitted Values
Platform Bit Size 64
Type numeric
Default 8388608
Min Value 0
Max Value 18446744073709547520
MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB你的不是Myisam存储引擎？
bulk_insert_buffer_sizeCommand-Line Format --bulk_insert_buffer_size=#
Option-File Format bulk_insert_buffer_size
System Variable Name bulk_insert_buffer_size
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values
Platform Bit Size 32
Type numeric
Default 8388608
Min Value 0
Max Value 4294967295
Permitted Values
Platform Bit Size 64
Type numeric
Default 8388608
Min Value 0
Max Value 18446744073709547520
MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB你的不是Myisam存储引擎？
我用的是 innoDB 存储引擎, 我现在换了一个最新版本 5.7 mysql 哎, 原来 load data infile 的代码居然执行成功但没有插入数据库, 果然 orecle 就是出了一个坑。。各种语法在 MYSQL 5.7 版本前的都能用, 唯独在 5.7 上用就出问题.
2. 表的主键是随机数吗，InnoDB在处理随机数主键上性能不佳（http://stackoverflow.com/questions/9819271/why-is-mysql-innodb-insert-so-slow）
3. 如果对ACID要求不那么严格，试着该一下这个看看innodb_flush_method = O_DIRECT
4. 据说MySQL的LOAD DATA INFILE性能不错，可以考虑用程序把数据处理成兼容的数据文件然后导入（http://kvz.io/blog/2009/03/31/improve-mysql-insert-performance/）
试过，一个单独的线程Load data inFile的效率吗？这种bulk insert方式，并发多线程不一定效率就高．
最好对比一下，将线程数从１-20，挑几个测一下．应该有一个最佳值．