MySQL提高插入数据的效率（结合JDBC）

0 解决问题最佳途径：直接找官方

先说明的是，有问题直接去找官方文档，而不应该去百度搜索，您很容易体验到，搜索引擎很难快速找到真正对您有价值的解决方案，而官方文档是最快捷的途径。

本篇也是基于官方文档和自己的实践得出的一些经验，适合初学者，本人也是初学者。

测试的插入数据只有353条，但是您依然能够体会到性能的提升。

1 【40s】使用云数据库Mysql的体验

下面是使用JDBC和INSERT INTO语句逐条插入信息。

/**
 * 将订单信息存入表 Performance_First
 *
 * @param SalesOrder 销售提交批量订单
 * @param id         当前登录账户的id
 */
private static void InsertSalesOrder(int id, String name, int[] SalesOrder) throws SQLException {
    Connection connection = null;
    PreparedStatement insertSalesOrder = null;
    String insertSalesOrderSQL = "insert into Performance_First " +
           						 "values (?,?,0,now(),?,0)";

    long startTime = 0;
    try {
        connection = DatabaseOperation.CreateDatabaseConnection();
        assert connection != null;
        insertSalesOrder = connection.prepareStatement(insertSalesOrderSQL);

        startTime = System.currentTimeMillis();// TODO 记录时间

        // 插入订单
        for (int TemporarySalesOrder : SalesOrder) {
            insertSalesOrder.setInt(1, id);
            insertSalesOrder.setString(2, name);
            insertSalesOrder.setInt(3, TemporarySalesOrder);

            insertSalesOrder.execute();
            // 应该加点插入成功的标记
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DatabaseOperation.CloseDatabase(connection, insertSalesOrder);
    }
    long endTime = System.currentTimeMillis();
    System.out.println("程序运行时间：" + (endTime - startTime) + "ms");
}

测试数据是353条，居然需要40s，我的天，难以忍受！

于是使用bing搜索半天，也没有找到什么结果，之后看见了官方文档，很快找到完美的解决方案。

首先，进入Mysql中文文档，找到第7章 优化（可惜没有搜索功能……）。

我们找到INSERT语句的速度。

入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：
连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1x记录大小）
插入索引：（1x索引）
关闭：(1)
这不考虑打开表的初始开销，每个并发运行的查询打开。

好了，现在明白了，使用云数据库，连接和发送就占用了大部分时间，所以，使用本地数据库试一试?

2 【2s】提高性能：使用本地数据库

这部分依然使用INSERT INTO语句，速度果然提高了很多，比较不需要联网发送数据了。

不过，我们还想再快一点，此时，你需要知道，你不知道你不知道的知识，如果官方文档找不到蛛丝马迹，又没有搜索，那就只能先去搜索引擎搜一下，看看我们需要解决那个位置的问题。

不过，幸运的是，官方文档还真有。

当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见LOAD DATA INFILE语法。

3 【20ms】进一步提高性能：使用LOAD DATA INFILE而不是INSERT INTO

我们获取了LOAD DATA INFILE的语法原型，然后使用我们需要的部分就可以了。

3.1 使用什么选项

我们的需求是

1. 将txt文件导入到数据库表的一部分columns
2. 将其他的columns设置为指定值

根据语法原型，我们需要的语法格式是

LOAD DATA INFILE [file_name]
INTO TABLE [table_name](column1,column2,……)
SET column5=expr,column6=expr……

JDBC是这样的（关键代码）

String sql = "LOAD DATA INFILE ? " +
     	     "INTO TABLE Performance_First(SalesOrder) " +
             "SET ID=?,Name=?,QueryStatus=0,SubmitTime=now(),CalculateStatus=0";
try (
        Connection connection = DatabaseOperation.CreateDatabaseConnection(); // 连接数据库
) {
    assert connection != null;
    startTime = System.currentTimeMillis();
    try (PreparedStatement p = connection.prepareStatement(sql);
    ) {
        p.setString(1, filePath);
        p.setInt(2, id);
        p.setString(3, name);
        p.execute();
    }
}

3.2 文件路径：客户端与服务器

如果指定了LOCAL，则被认为与连接的客户端有关：
· 如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。

• 如果LOCAL没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。
  当在服务器主机上为文件定位时，服务器使用以下规则：
• 如果给定了一个绝对的路径名称，则服务器使用此路径名称。
• 如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。
• 如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。
  注意，这些规则意味着名为./myfile.txt的文件会从服务器数据目录中被读取，而名为myfile.txt的同样的文件会从默认数据库的数据库目录中读取。例如，下面的LOAD DATA语句会从db1数据库目录中读取文件data.txt，因为db1是当前数据库。即使语句明确把文件载入到db2数据库中的表里，也会从db1目录中读取。

mysql> USE db1;
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

注意，使用正斜杠指定Windows路径名称，而不是使用反斜杠。如果您使用反斜杠，您必须使用两个。
出于安全原因，当读取位于服务器中的文本文件时，文件必须位于数据库目录中，或者是全体可读的。另外，要对服务器文件使用LOAD DATA INFILE，您必须拥有FILE权限。

我们选择直接使用服务器本地的文件，而不是客户端远程上传，因此不需要加LOCAL。

此外，数据库的文件会有默认的DATA文件夹，如果使用./则会认为是此文件夹，使用JDBC导入文件夹的时候，需要

1. 获取java工程的路径
2. 文件路径 = Java工程路径 + 文件名

注意加上后缀，并且，不要使用中文。

3.3 注意

1. 不能包含中文：文件的路径和文件名都不能包含中文
2. 您可能会遇到一些报错，这是因为权限不足，默认情况下，只有指定文件夹中的文件可以被导入，这个问题请如果您遇到，请自行查阅资料解决。