多线程提升MySQL造数据速度

由于最近沉迷小孩子的MySQL 是怎样运行的：从根儿上理解 MySQL，想造一点数据磨练一下自己的所学，所以准备创建两个一百万数据的表，一开始写一个简单的程序使用jdbc往里面插入数据，开始的写的时候我已经意识到了为了提高插入速度，可以使用多线程去插入，所以第一版程序如下：

public class DataInsert {
    public static void main(String[] args) throws Exception {
        ExecutorService fixedThreadPool = Executors.newFixedThreadPool(20);
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://xx:3306/pamphlet", "root", "pwd");
        PreparedStatement statement = connection.prepareStatement("insert into t1 values(?,?)");
        for (int i = 0; i < 1000000; i++) {
            int finalI = i;
            fixedThreadPool.execute(new Runnable() {
                @Override
                public void run() {
                    try {
                        statement.setInt(1, finalI);
                        statement.setString(2, String.valueOf((char) (finalI % 26 + 97)));
                        statement.execute();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            });
        }
    }
}

表结构

create table t1
(
    m1 int  null,
    n1 char null
);

然后启动程序程序感觉速度还可以，大概一秒插十几条数据吧，然后就去吃完饭了，吃完溜达了一会回来后，发现才插入了小几万条，这速度明天上班也插不完啊！所以需要找一下问题。

我打开idea的调试器，发现线程池里面的20条线程都是交替RUNNING状态,所以并没有发挥出多线程的特性！然后很快我也意识到问题出现在了哪里，就是Connection我们始终只是20条线程交替的使用一个Connection在往mysql中插入数据

而且statement的execute方法的实现是被锁包围的，so?虽然我们使用了多线程，但我们还是在串行得执行任务。

问题找到了，那么我们怎么优化呢？我们在每次执行之前都取一个Connection这样就可以并发执行插入了吧？写一下代码

for (int i = 0; i < 1000000; i++) {
    int finalI = i;
    fixedThreadPool.execute(new Runnable() {
        @Override
        public void run() {
            try {
                Connection connection = DriverManager.getConnection("jdbc:mysql://xx:3306/pamphlet", "root", "pwd");
                PreparedStatement statement = connection.prepareStatement("insert into t1 values(?,?)");
                statement.setInt(1, finalI);
                statement.setString(2, String.valueOf((char) (finalI % 26 + 97)));
                statement.execute();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    });
}

像上面这样写应该可以了吧？实话说我没试过，大家可以试一下，效率应该会好很多，但是我觉得还可以再优化，因为线程池中得线程是反复利用的，所以我们让每一条线程拥有一个Connection不就行了？所以ThreadLocal不久可以用上了吗？

@Log4j2
public class DataInsert {
    public static void main(String[] args) throws Exception {
        ExecutorService fixedThreadPool = Executors.newFixedThreadPool(80);
        ThreadLocal<PreparedStatement> threadLocal = new ThreadLocal<>();
        Class.forName("com.mysql.jdbc.Driver");
        for (int i = 0; i < 1000000; i++) {
            int finalI = i;
            fixedThreadPool.execute(new Runnable() {
                @SneakyThrows
                @Override
                public void run() {
                    PreparedStatement statement = threadLocal.get();
                    if (statement == null){
                        Connection connection = DriverManager.getConnection("jdbc:mysql://xx:3306/pamphlet", "root", "pwd");
                        statement = connection.prepareStatement("insert into t1 values(?,?)");
                        threadLocal.set(statement);
                    }
                    try {
                        log.info(finalI);
                        statement.setInt(1, finalI);
                        statement.setString(2, String.valueOf((char) (finalI%26 + 97)));
                        statement.execute();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            });
        }
    }
}

可以看到上面的的代码就优雅多了，既把效率提上来了，也避免了产生大量的Connection对象。而且我们用idea查看线程状态，他们个个都是“活力满满”！

但是问题又来了，我报错了，我后面同时插入t1和t2表，所以最大可能占用160个mysql连接（代码里我给FixedThreadPool的参数是80，所以启动插入t1和t2会有160条线程同时插入），而mysql的最大连接数是151个，所以会有几个线程插入失败，报Too many connections!!

这不要紧啊，我们修改一下mysql配置就行了，给个大的数值2000就行了。

就此这个程序就能比较优雅的运行了，很快也把两个一百万插入到了表中。