高效TDDL批量插入

深碍√TFBOYSˉ_ 2022-08-22 01:25 62阅读 0赞

## 高效TDDL批量插入 ##

使用TDDL客户端，向MySQL数据库插入1000条数据，插入时间10秒内。

常规方法

单条插入，代码如下：

<table style="width:620px; border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; background:none!important"> 
 <tbody style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; background:none!important"> 
  <tr style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; background:none!important"> 
   <td style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; color:rgb(120,120,120)!important; background:none!important"> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      1 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      2 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      3 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      4 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      5 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      6 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      7 
    </div> 
    <div style="border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(212,208,200)!important; margin:0px!important; padding:0px 0.5em 0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important">
      8 
    </div> </td> 
   <td style="width:590px; border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; background:none!important"> 
    <div style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:relative!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; background:none!important"> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:gray!important; background:none!important">@Test</code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:gray!important; background:none!important">@Transactional</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">(TransactionMode.ROLLBACK)</code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:bold!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:rgb(127,0,85)!important; background:none!important">public</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:bold!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:rgb(127,0,85)!important; background:none!important">void</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important"> testInsters2() { </code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:bold!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:rgb(127,0,85)!important; background:none!important">for</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">(</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:bold!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:rgb(127,0,85)!important; background:none!important">int</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">i = </code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:rgb(0,153,0)!important; background:none!important">0</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">; i &lt; </code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; color:rgb(0,153,0)!important; background:none!important">10000</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">; i++) { </code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">CheckItemDO checkItemDO = createCheckItem2(i);</code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">checkItemDao.insertCheckItem(checkItemDO);</code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">}</code> 
     </div> 
     <div style="border:0px!important; margin:0px!important; padding:0px 1em!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-size:1em!important; direction:ltr!important; white-space:pre!important"> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="border:0px!important; margin:0px!important; padding:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-size:1em!important; direction:ltr!important; display:inline!important; background:none!important">}</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

消费时间：  
[![1][]][1 1]  
居然花了144秒，悲剧啊！

批量插入  
第一种批量插入，ibatis批量插入数据-iterate

TDDL不支持SQL多条插入，INSERT INTO tableName  (col\_name,…)  VALUES (expression,…),(…)；所以要添加备注，绕开语法校验，

/*+TDDL({type:executeByCondition,parameters:["seller_id=#sellerId#;l"],virtualTableName:jxc_check_item})*/
    
       
        
        
         1 2 3 4 5 6 7 8 9 10 11 12 13 14       < insert id = "insertCheckItems" parameterClass = "java.util.Map" >          /*+TDDL({type:executeByCondition,parameters:["seller_id=#sellerId#;l"],virtualTableName:jxc_check_item})*/          insert into          jxc_check_item (id, check_item_id, check_id, from_site, seller_id, warehouse_id, product_id, before_check_num, after_check_num, creator, modifier, gmt_create, gmt_check, gmt_modified)          values           < iterate conjunction = "," property = "checkItems" >          <![CDATA[          (              #checkItems[].id#, #checkItems[].checkItemId#, #checkItems[].checkId#, #checkItems[].fromSite#, #checkItems[].sellerId#, #checkItems[].warehouseId#, #checkItems[].productId#, #checkItems[].beforeCheckNum#, #checkItems[].afterCheckNum#, #checkItems[].creator#, #checkItems[].modifier#, now(), now(), now()          )          ]]>          </ iterate >      </ insert > 
       
        
     说白了这种方式就是拼接SQL，但是MySQL长度是有限制的，默认限制是1M，所以，如果一次性插入10000调数据，程序会出现假死状态。所有，要分批插入，一批插入1000调，插10次。Java代码如下：
    
       
        
        
         1 2 3 4 5 6 7 8 9 10 11 12 13 14 @Test      @Transactional (TransactionMode.ROLLBACK)      public void testInsters() {          List<CheckItemDO> checkItemDOsTemp = new ArrayList<CheckItemDO>();          for ( int i = 0 ; i < 10000 ; i++) {              CheckItemDO checkItemDO = createCheckItem2(i);              checkItemDOsTemp.add(checkItemDO);                if (i % 1000 == 0 ) {                  checkItemDao.insertCheckItems(sellerId, checkItemDOsTemp);                  checkItemDOsTemp.clear();              }          }      } 
       
        
     
     消费时间：  只消耗了40秒不到，性能提升了3.5倍。
     虽然时候性能已经提高了不少，但是，还有没有更给力的方法呢？答案是：Yes。
     第二种批量插入，jdbc Rewrite Batched Statements批量插入
     java代码如下：
    
       
        
        
         1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 String url = "jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true" ;          String userName = "root" ;          String password = "1234" ;          Connection conn = null ;          try {              Class.forName( "com.mysql.jdbc.Driver" );              conn = DriverManager.getConnection(url, userName, password);              conn.setAutoCommit( false );              String sql = "insert into t_user(id,uname) values(?,?)" ;              PreparedStatement prest = conn.prepareStatement(sql);              long a = System.currentTimeMillis();              for ( int x = 0 ; x < 100000 ; x++) {                  prest.setInt( 1 , x);                  prest.setString( 2 , "张三" );                  prest.addBatch();              }              prest.executeBatch();              conn.commit();              long b = System.currentTimeMillis();              System.out.println( "MySql批量插入10万条记录用时" + (b - a) + " ms" );          } catch (Exception ex) {              ex.printStackTrace();          } finally {              try {                  if (conn != null ) conn.close();              } catch (SQLException e) {                  e.printStackTrace();              }          } 
       
        
     这里，要求mysql url必须带rewriteBatchedStatements=true。此处是jdbc的代码，而本人的开发环境是基于ibatis的，下面为ibatic批量插入的实现代码：
    
       
        
        
         1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 @Test      @Transactional (TransactionMode.DISABLED)      public void testBatch2() {          List<CheckItemDO> checkItemDOsTemp = new ArrayList<CheckItemDO>();            for ( int i = 0 ; i < 10000 ; i++) {              CheckItemDO checkItemDO = createCheckItem2(i);              checkItemDOsTemp.add(checkItemDO);          }            final List<CheckItemDO> checkItemDOs = new ArrayList<CheckItemDO>(checkItemDOsTemp);            CheckItemDaoImpl checkItemDaoImpl = (CheckItemDaoImpl) checkItemDao;          SqlBaseExecutorImp sqlBaseExecutorImp = (SqlBaseExecutorImp)checkItemDaoImpl.getSqlBaseExecutor();          sqlBaseExecutorImp.getSqlMapClientTemplate().execute( new SqlMapClientCallback() {              public Object doInSqlMapClient(SqlMapExecutor executor) {                  try {                      executor.startBatch();                      for (CheckItemDO checkItemDO : checkItemDOs) {                          executor.insert(CheckItemDao.SQL_MAPPING_NAME_SPACE + ".insertCheckItem" , checkItemDO);                      }                        executor.executeBatch();                  } catch (SQLException e) {                      e.printStackTrace();                      return false ;                  }                  return true ;              };          });      } 
       
        
     
     消费时间：质的飞跃，3.384秒，性能提升了40倍，Perfect。
     注意事项
     第一，MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
     Mysql JDBC驱动，各个版本测试结果：
    
       
        
         MySql JDBC 驱动版本号 插入10万条数据用时 5.0.8 加了rewriteBatchedStatements参数，没有提高还是17.4秒 5.1.7 加了rewriteBatchedStatements参数，没有提高还是17.4秒 5.1.13 加了rewriteBatchedStatements参数，插入速度提高到1.6秒 
        
     第二，保证表插入的顺序，如果要批量插入A和B两个表的数据，要保证先批量插入表A，再批量插入表B。原因在于磁盘IO，应为MySQL会保证SQL的顺序性，不断在A,B表之间切换，相当与磁盘寻址要不断改变，相当损耗性能，这无异于单条插入。
     第三，小心事务嵌套，jdbc Rewrite Batched Statements批量插入要求在一个事务中进行批量插入，所以，外面不应该再嵌套事务，在使用TestNG事务的时候，要声明为无事务，即：@Transactional(TransactionMode.DISABLED)。

[1]: /images/20220724/194ca66a44894ceda3bf6a5f8815390f.png
[1 1]: http://www.xiaoyaochong.net/wordpress/?attachment_id=629