mysql表插入y_mysql插入数据库三种方法性能测试

表结构

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

还是不相上下，需再研究。