表结构
CREATE TABLE IF NOT EXISTS `test_load_file` (
`int1` int(11) NOT NULL,
`int2` int(11) NOT NULL,
`int3` int(11) NOT NULL,
`int4` int(11) NOT NULL,
`int5` int(11) NOT NULL,
`int6` int(11) NOT NULL,
`int7` int(11) NOT NULL,
`int8` int(11) NOT NULL,
`int9` int(11) NOT NULL,
`int10` int(11) NOT NULL,
`int11` int(11) NOT NULL,
PRIMARY KEY (`int1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1 LOAD DATA 10W条
LOAD DATA LOCAL INFILE ‘D:\\wamp\\www\\test\\test.csv’ INTO TABLE `test_load_file` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’;2.86秒完成
2 insert into 10W条20秒（每次插入647条）
source D:\\wamp\\www\\test\\test_load_file.sql
3 into 10W条（每次插入1条，我等的花儿也谢了，等了3分12秒，只插入完成2115条，按这速度计算了一下9078秒）
source D:\\wamp\\www\\test\\test.sql
倍数比
3174:453:1
所以可以考虑插入的操作统一处理
上面的未考虑并发插入，今天又专门测了一下并发插入
服务器配置 centos6.0 单核512MB，2G硬盘
1，分别测试100并发和8并发，进行本测试前测试了系统并发的最优数量为8
测试8并发插入10w条数据
mysqlslap –concurrency=8 –iterations=1 –create-schema=’test’ –query=’INSERT INTO test_load_file (`int1`,`int2`,`int3`,`int4`,`int5`,`int6`,`int7`,`int8`,`int9`,`int10`,`int11`) VALUES (null,16399,16399,16399,16399,16399,16399,16399,16399,16399,16399);’ –number-of-queries=100000 –debug-info -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 45.459 seconds
Minimum number of seconds to run all queries: 45.459 seconds
Maximum number of seconds to run all queries: 45.459 seconds
Number of clients running queries: 8
Average number of queries per client: 12500
User time 0.00, System time 1.35
Maximum resident set size 2176, Integral resident set size 0
Non-physical pagefaults 625, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 200044, Involuntary context switches 13
测试100并发插入10w条数据
mysqlslap –concurrency=100 –iterations=1 –create-schema=’test’ –query=’INSERT INTO test_load_file (`int1`,`int2`,`int3`,`int4`,`int5`,`int6`,`int7`,`int8`,`int9`,`int10`,`int11`) VALUES (null,16399,16399,16399,16399,16399,16399,16399,16399,16399,16399);’ –number-of-queries=100000 –debug-info -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 55.633 seconds
Minimum number of seconds to run all queries: 55.633 seconds
Maximum number of seconds to run all queries: 55.633 seconds
Number of clients running queries: 100
Average number of queries per client: 1000
User time 0.79, System time 1.34
Maximum resident set size 4160, Integral resident set size 0
Non-physical pagefaults 1121, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 200511, Involuntary context switches 20
55秒，结果好多了。
2，测试source xxx.sql。同样10w条
时间1.65s，比较惊喜
3，测试load data。10w条
LOAD DATA LOCAL INFILE ‘~/test10w.csv’ INTO TABLE `test_load_file` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’;
Query OK, 100000 rows affected (1.63 sec)
Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0
事件1.63，跟source差不多，下面再把数据量增加到100w。
4，source 100w条数据（load data之后导出sql文件来进行本次测试）
13.86s
5，load data 100w条数据
LOAD DATA LOCAL INFILE ‘~/test100w.csv’ INTO TABLE `test_load_file` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’;
Query OK, 1000000 rows affected (13.88 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0
还是不相上下，需再研究。
转载请注明：小Y » mysql插入数据库三种方法性能测试
