mysql - 批量插入操作速度太慢

Question

这是我的“venprices”表的结构。

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| conid | int(10)     | NO   | PRI | NULL    |       |
| rate  | double      | YES  |     | NULL    |       |
| venid | varchar(50) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+

JAVA代码:

new Thread(){
            public void run(){
                XSSFWorkbook myWorkBook;
                XSSFSheet mySheet = null;
                Iterator<Row> rowIterator = null;
                String venid = null, errorlog = null;
                int code;
                double rate;
                int rows, maxcount;
                PreparedStatement ps = null;
                Connection con;
                ProgressMonitor pm;
                try {
                    myWorkBook = new XSSFWorkbook(new FileInputStream(new File(jTextField1.getText())));
                    mySheet = myWorkBook.getSheetAt(0);
                    rowIterator = mySheet.iterator();
                    rowIterator.next();
                    venid = jComboBox1.getItemAt(jComboBox1.getSelectedIndex());
                    con = Mycon.getConnection();
                    ps = con.prepareStatement("DELETE FROM venprices WHERE venid = ?");
                    ps.setString(1, venid);
                    ps.executeUpdate();
                    ps.clearBatch();    
                    ps = con.prepareStatement("insert into venprices values (?,?,?)");
                } catch(Exception ioe) {
                    JOptionPane.showMessageDialog(null, ioe.getMessage());
                }
                    rows = 1;maxcount = mySheet.getLastRowNum();
                    // Traversing over each row of XLSX file
                    while (rowIterator.hasNext())
                    {
                        try{
                            Row row = rowIterator.next();
                            Iterator<Cell> cellIterator = row.cellIterator();
                            Cell cell = cellIterator.next();
                            code = (int) cell.getNumericCellValue();
                            cell = cellIterator.next();
                            rate = cell.getNumericCellValue();
                            ps.setInt(1,code);
                            ps.setDouble(2,rate);
                            ps.setString(3, venid);
                            ps.addBatch();
                            rows++;
                        }catch(Exception e){errorlog = errorlog + "\n" +rows+ e.getMessage();}
                    }
                    try{
                        System.gc();
                        ps.executeBatch();
                    }catch(Exception e){e.printStackTrace();}
                    if(errorlog == null)
                        JOptionPane.showMessageDialog(null, "Import Successful. " + rows + " Records Imported.");
                    else
                        JOptionPane.showMessageDialog(null, "Error Log :\n"+errorlog);
            }
        }.start();

用户预计使用 Excel 文件一次插入大约 50,000 条记录。但查询大约需要 6-7 分钟。谁能帮助我减少插入操作时间或告诉我插入查询中的一些调整？提前致谢!

编辑1:根据要求，这是 show create table venprices

的结果

mysql> show create table venprices;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| venprices | CREATE TABLE `venprices` (
  `conid` int(10) NOT NULL,
  `rate` double DEFAULT NULL,
  `venid` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`conid`,`venid`),
  KEY `vepr` (`conid`,`rate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

不懂Java语法，但是加个MySQL START TRANSACTION ( BEGIN ) 在代码的开头。并添加COMMIT到最后。

为什么？

在 6-7 分钟内插入 50K 行，听起来非常像在自己的事务中插入每一行(如 autocommit=ON )。由于磁盘上的所有事务事件，这很慢。

通过将 50K 事务转化为 1 个事务，您将拥有更少的 I/O，因此运行速度会更快。

其次...通过转动 50K 1 行 INSERTs分成 50 1000 行 INSERTs ，您可以获得另外 10 倍的加速。这是因为避免了客户端和服务器之间的大部分往返时间以及大部分解析时间。 (同样，我不知道 Java 是否有一个特殊的类来促进这种目的；可能是。)

这两项更改也许与 Drew 的 LOAD_DATA INFILE 具有竞争力。方法。