单批次插入和多批次插入的效率问题

在生产调优的时候发现有些客户插入数据是使用INSERT INTO VALUE();进行单行插入，此时效率非常地下。采用INSERT INTO VALUES(),()进行多行插入时效率有巨大提升，下面为测试示例。

创建两个sql文件，

SQL1中插入50行数据

TRUNCATE TABLE tab;
INSERT INTO tab VALUES(1,'A',NOW());
INSERT INTO tab VALUES(2,'B',NOW());
INSERT INTO tab VALUES(3,'C',NOW());
INSERT INTO tab VALUES(4,'D',NOW());
INSERT INTO tab VALUES(5,'E',NOW());
INSERT INTO tab VALUES(6,'F',NOW());
INSERT INTO tab VALUES(7,'G',NOW());
INSERT INTO tab VALUES(8,'H',NOW());
INSERT INTO tab VALUES(9,'I',NOW());
INSERT INTO tab VALUES(0,'J',NOW());
INSERT INTO tab VALUES(1,'A',NOW());
INSERT INTO tab VALUES(2,'B',NOW());
INSERT INTO tab VALUES(3,'C',NOW());
INSERT INTO tab VALUES(4,'D',NOW());
INSERT INTO tab VALUES(5,'E',NOW());
INSERT INTO tab VALUES(6,'F',NOW());
INSERT INTO tab VALUES(7,'G',NOW());
INSERT INTO tab VALUES(8,'H',NOW());
INSERT INTO tab VALUES(9,'I',NOW());
INSERT INTO tab VALUES(0,'J',NOW());
INSERT INTO tab VALUES(1,'A',NOW());
INSERT INTO tab VALUES(2,'B',NOW());
INSERT INTO tab VALUES(3,'C',NOW());
INSERT INTO tab VALUES(4,'D',NOW());
INSERT INTO tab VALUES(5,'E',NOW());
INSERT INTO tab VALUES(6,'F',NOW());
INSERT INTO tab VALUES(7,'G',NOW());
INSERT INTO tab VALUES(8,'H',NOW());
INSERT INTO tab VALUES(9,'I',NOW());
INSERT INTO tab VALUES(0,'J',NOW());
INSERT INTO tab VALUES(1,'A',NOW());
INSERT INTO tab VALUES(2,'B',NOW());
INSERT INTO tab VALUES(3,'C',NOW());
INSERT INTO tab VALUES(4,'D',NOW());
INSERT INTO tab VALUES(5,'E',NOW());
INSERT INTO tab VALUES(6,'F',NOW());
INSERT INTO tab VALUES(7,'G',NOW());
INSERT INTO tab VALUES(8,'H',NOW());
INSERT INTO tab VALUES(9,'I',NOW());
INSERT INTO tab VALUES(0,'J',NOW());
INSERT INTO tab VALUES(1,'A',NOW());
INSERT INTO tab VALUES(2,'B',NOW());
INSERT INTO tab VALUES(3,'C',NOW());
INSERT INTO tab VALUES(4,'D',NOW());
INSERT INTO tab VALUES(5,'E',NOW());
INSERT INTO tab VALUES(6,'F',NOW());
INSERT INTO tab VALUES(7,'G',NOW());
INSERT INTO tab VALUES(8,'H',NOW());
INSERT INTO tab VALUES(9,'I',NOW());
INSERT INTO tab VALUES(0,'J',NOW());

SQL2中插入1行数据

TRUNCATE TABLE tab;
INSERT INTO tab VALUES(1,'A',NOW()),(2,'B',NOW()),(3,'C',NOW()),(4,'D',NOW()),(5,'E',NOW()),(6,'F',NOW()),(7,'G',NOW()),(8,'H',NOW()),(9,'I',NOW()),(0,'J',NOW()),(1,'A',NOW()),(2,'B',NOW()),(3,'C',NOW()),(4,'D',NOW()),(5,'E',NOW()),(6,'F',NOW()),(7,'G',NOW()),(8,'H',NOW()),(9,'I',NOW()),(0,'J',NOW()),(1,'A',NOW()),(2,'B',NOW()),(3,'C',NOW()),(4,'D',NOW()),(5,'E',NOW()),(6,'F',NOW()),(7,'G',NOW()),(8,'H',NOW()),(9,'I',NOW()),(0,'J',NOW()),(1,'A',NOW()),(2,'B',NOW()),(3,'C',NOW()),(4,'D',NOW()),(5,'E',NOW()),(6,'F',NOW()),(7,'G',NOW()),(8,'H',NOW()),(9,'I',NOW()),(0,'J',NOW()),(1,'A',NOW()),(2,'B',NOW()),(3,'C',NOW()),(4,'D',NOW()),(5,'E',NOW()),(6,'F',NOW()),(7,'G',NOW()),(8,'H',NOW()),(9,'I',NOW()),(0,'J',NOW());

分别执行SQL1,SQL2

[gbase@localhost ~]$ date;gccli -ugbase -p123456 -Dvcname000001.mydb < 1.sql;date
Mon Dec 30 19:39:04 CST 2024
Mon Dec 30 19:39:06 CST 2024

[gbase@localhost ~]$ date;gccli -ugbase -p123456 -Dvcname000001.mydb < 2.sql;date
Mon Dec 30 19:39:24 CST 2024
Mon Dec 30 19:39:24 CST 2024

SQL1用了2秒而SQL2瞬间完成，因此建议采用批量插入的方式提高效率