mysql合并插入提升吞吐
本次实测了一下Mysql批量插入的性能优化方案,项目地址:https://github.com/owenliang/mysql-bulk-insert 。
实测情况
1234567891011121314151617181920212223242526272829303132333435363738 [root@10-9-136-206 mysql-bulk-insert]# php app/webroot/index.php /mysql/bulk 1 100000 1000042806.0091443543227.9029596743272.17366214943692.72614151944000.41122820944212.53578041244151.1128407944052.56884454543966.587535806总耗时:2.2602601051331[root@10-9-136-206 mysql-bulk-insert]# php app/webroot/index.php /mysql/single 1 100000 100002491.93980660762508.96235516232507.84896644532472.55954058062472.80264577542470.83336364712474.21253536282472.26275743252482.6912969641总耗时:40.278048992157[root@10-9-136-206 mysql-bulk-insert]# php app/webroot/index.php /mysql/tran 1 100000 100004836.60739695914657.22804311944636.07069499074642.26530997424693.9488506784719.1645231414763.71338276374819.85053024934849.5904714522总耗时:20.423968076706
结论
无优化:逐条插入(或者说1个事务插入1条)
INSERT INTO user (id, name, age) VALUES (1, ‘Bob’, 20);
翻倍提升:多条事务提交
BEGIN
INSERT INTO user (id, name, age) VALUES (1, ‘Bob’, 20);
INSERT INTO user (id, name, age) VALUES (2, ‘John’, 22);
INSERT INTO user (id, name, age) VALUES (3, ‘Alice’, 24);COMMIT
数量级提升:合并插入
INSERT INTO user (id, name age) VALUES (1, ‘Bob’, 20), (2, ‘John’, 22), (3, ‘Alice’, 24);
其他
为了避免插入时部分记录有唯一键的冲突,可以使用INSERT IGNORE INTO。
删除同样可以通过如下的方法合并,达到吞吐优化效果:
DELETE FROM user WHERE (id = 1) OR (id = 2) OR (id = 3);
如果文章帮助您解决了工作难题,您可以帮我点击屏幕上的任意广告,或者赞助少量费用来支持我的持续创作,谢谢~
