V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
zw1one
V2EX  ›  Java

for 循环中执行 sql 语句,为什么效率低下?

  •  
  •   zw1one · 2020-10-28 16:05:49 +08:00 · 5705 次点击
    这是一个创建于 1248 天前的主题,其中的信息可能已经有所发展或是发生改变。

    代码错误示例( java ):

    for(PO po : poList){
      dao.insert(po);
    }
    

    已经用批量提交 /拼接 insert 语句的方式去除了 for 循环,效率也正常了。但是想问下,大家都知道这种操作它效率低,可是它具体低在哪呢?

    我大概猜的几个点:

    • 多次建立数据库链接耗时(但是有连接池管理效率应该不会低?)
    • 多次开启关闭事务耗时( service 方法声明了只有一个事务)
    • 数据库多次解析同一条 sql 语句耗时

    各位大佬,哪里有参考资料之类的可以看看吗 : (

    26 条回复    2020-10-29 17:02:10 +08:00
    biepin
        1
    biepin  
       2020-10-28 16:11:13 +08:00
    每次都进行 insert 操作,每次 insert 操作都是需要花时间的
    misaka19000
        2
    misaka19000  
       2020-10-28 16:13:27 +08:00
    网络开销也要考虑
    mazhan465
        3
    mazhan465  
       2020-10-28 16:14:10 +08:00   ❤️ 2
    每条 insert,DB 都要重新走一遍整个解析,数据检查,写内存,刷盘,一条 insert 插多条数据可以一次将数据写到内存,然后等待刷盘
    a719031256
        4
    a719031256  
       2020-10-28 16:20:07 +08:00
    主要应该是网络开销把,数据库跟代码在同一服务器上效率跟批量插入不相上下
    chendy
        5
    chendy  
       2020-10-28 16:25:36 +08:00
    效率低下是相比于批量操作效率低下,循环要访问 n 次数据库,批量就 1 次
    zw1one
        6
    zw1one  
    OP
       2020-10-28 16:28:10 +08:00
    @a719031256 网络开销指的是:传输这些插入的数据吗?还是与数据库建立链接也有网络开销?因为要插入的数据总量是不变的,两种做法的网络开销是不是差不多呢?
    JaguarJack
        7
    JaguarJack  
       2020-10-28 16:38:17 +08:00
    应该是 insert 这个工作重复做吧
    a719031256
        8
    a719031256  
       2020-10-28 16:38:40 +08:00
    @zw1one 传输数据的网络开销
    HanMeiM
        9
    HanMeiM  
       2020-10-28 17:48:16 +08:00
    还有表里有索引的话,每次 insert 是会重建索引树的,这个还是挺麻烦的。
    用 foreach 最好开个事务,一次性提上去
    AngryPanda
        10
    AngryPanda  
       2020-10-28 17:52:45 +08:00
    TCP 连接就一次,并没有多次。
    QBugHunter
        11
    QBugHunter  
       2020-10-28 17:54:23 +08:00
    数据库本质上来说是一种读写本地文件,然后,现在有 10 个字符需要写入本地文件
    方案 A:打开文件,写入一个字符,关闭文件。然后把该过程重复 10 此
    方案 B:打开文件,写入 10 个字符,关闭文件

    基本上就是这个样子
    chenluo0429
        12
    chenluo0429  
       2020-10-28 18:42:10 +08:00 via Android
    之前短暂维护过一座屎山,平板离线录入数据,然后将数据打包成文件上传到服务器,服务器解析本机的文件,写入本机数据库,然后请求返回成功。因为在 for 循环里面每次只写入一条数据,单次 insert 大概在 10-20ms 。很久没上传的平板数据量巨大,大概需要上传三天,所以总是超时无法上传成功。
    l00t
        13
    l00t  
       2020-10-28 19:01:59 +08:00
    这得看你程序本身和数据库交互的部分是怎么写的。for 不是关键。
    yeqizhang
        14
    yeqizhang  
       2020-10-28 19:24:23 +08:00 via Android
    只能说网络开销,你不可能 for 里面每次 insert 前 connect 然后 close 吧
    Bromine0x23
        15
    Bromine0x23  
       2020-10-28 20:46:44 +08:00
    条数多的话就主要是等待响应的传播时延了
    非批量模式下,要等待前一条执行的结果响应传输回来才能执行下一条,那就多了 (N-1) 个传播时延
    Cuo
        16
    Cuo  
       2020-10-29 00:42:51 +08:00 via iPhone
    N+1 问题?
    chanyan
        17
    chanyan  
       2020-10-29 08:44:45 +08:00
    实测在 pg 里面,普通业务 10 万数据拼接与同一个事务中进行 10w insert 效率没什么差别。用同一个事务即可,拼接代码的可读性不好
    857681664
        18
    857681664  
       2020-10-29 09:11:19 +08:00 via Android
    如果一堆单条 insert 在一个事务里,跟批量 insert 差距忽略不计,如果单条 insert 是每次单开一个事务,那差距大概是几十倍,前几天刚好遇到过这个问题,用 spring 的 scriptUtil.execScript,给的 sql 是大量单条 insert,1000 条插入耗时 2s 多,换成批量插入 100ms 。
    xdsty
        19
    xdsty  
       2020-10-29 09:32:15 +08:00
    极客时间 mysql 实战挺不错
    xdsty
        20
    xdsty  
       2020-10-29 09:34:47 +08:00
    每次 insert 都要刷 redo log 和 binlog,这都是需要写入硬盘的,所以较慢。
    合并为批量 sql,只需要刷一次 redo log 和 binlog,会快一些
    passerbytiny
        21
    passerbytiny  
       2020-10-29 09:41:42 +08:00 via Android
    一、只要是 for 循环中的 sql,不管是插入还是查询,都是可以使用批量的方式替代的。
    二、一次扛两桶水,相比每次扛一桶水扛两次,有效付出是一样的,但通常前者的无效付出更少(一种例外情况是,没有那能力还硬扛导致路上水撒了最终变成先扛一桶半再扛一桶)。简单说就是通常情况下批量比循环好。

    所以,并不是 for 循环中用 sql 效率底下,而是改成批量模式会更好。
    ytymf
        22
    ytymf  
       2020-10-29 09:46:18 +08:00
    楼上说的不错,日志开销也是很大一部分
    1194129822
        23
    1194129822  
       2020-10-29 12:27:54 +08:00
    不要以为 JDBC addBatch 和 mybatis BatchExecutor 就是真正的批量提交!!!这个功能要先开启才能用,在 JDBC url 后面加 rewriteBatchedStatements=true,不然还是走的循环插入。
    kingofzihua
        24
    kingofzihua  
       2020-10-29 14:57:38 +08:00
    你下楼买 10 瓶水
    方案 1:每次买一瓶,跑 10 次 => for 循环
    方案 2:一次买 10 瓶 => 不用 for 循环

    所以不用 for 循环进行执行 sql

    但是如果你 1 次拿不了,可以 每次取 3 条 for
    constructor
        25
    constructor  
       2020-10-29 16:30:58 +08:00
    插入 10000 条数据实测结果:
    一次性批量插入 1 万条: 118.087ms
    开启事务循环插入 1 万条: 16.562s
    不开启事务循环插入 1 万条: 42.204s

    js 代码: https://gist.github.com/xuxucode/e16d9df4476e2e10e3858287c442a778
    aragakiyuii
        26
    aragakiyuii  
       2020-10-29 17:02:10 +08:00
    我觉得在忽略数据库本身性能的情况下,无论用什么 orm,只要能保证这些 insert 在一个事务里提交执行就 ok
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5264 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 09:23 · PVG 17:23 · LAX 02:23 · JFK 05:23
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.