你向 MySQL 插入 100万条数据用了多久？

快来打我* 2022-12-21 10:48 255阅读 0赞

点击上方“java大数据修炼之道”,选择“设为星标”

# 优质文章和精品资源, 第一时间送达 #

![format_png][]

关注公众号后台回复**资源**获取实战教学资料+视频

## **目录** ##

**1、**多线程插入(单表)  
**2、**多线程插入(多表)  
**3、**预处理SQL  
**4、**多值插入SQL  
**5、**事务(N条提交一次)

### **多线程插入(单表)** ###

**问：****为何对同一个表的插入多线程会比单线程快？****同一时间对一个表的写操作不应该是独占的吗？**

答：在数据里做插入操作的时候，整体时间的分配是这样的：

**1、**多链接耗时 （30%）

**2、**多发送query到服务器 （20%）

**3、**多解析query （20%）

**4、**多插入操作 （10% \* 词条数目）

**5、**多插入index （10% \* Index的数目）

**6、**多关闭链接 （10%）

从这里可以看出来，真正耗时的不是操作，而是链接，解析的过程。

MySQL插入数据在写阶段是独占的，但是插入一条数据仍然需要解析、计算、最后才进行写处理，比如要给每一条记录分配自增id，校验主键唯一键属性，或者其他一些逻辑处理，都是需要计算的，所以说多线程能够提高效率。

### **多线程插入(多表)** ###

分区分表后使用多线程插入。

### **预处理SQL** ###

**普通SQL：**即使用Statement接口执行SQL

**预处理SQL：**即使用PreparedStatement接口执行SQL

使用PreparedStatement接口允许数据库预编译SQL语句，以后只需传入参数，避免了数据库每次都编译SQL语句，因此性能更好。

String sql = "insert into testdb.tuser (name, remark, createtime, updatetime) values (?, ?, ?, ?)";
    for(int i = 0; i < m; i++) {
    //从池中获取连接
    Connection conn = myBroker.getConnection();
    PreparedStatement pstmt = conn.prepareStatement(sql);
    for(int k = 0; k < n; k++) {
            pstmt.setString(1, RandomToolkit.generateString(12));
            pstmt.setString(2, RandomToolkit.generateString(24));
            pstmt.setDate(3, newDate(System.currentTimeMillis()));
            pstmt.setDate(4, newDate(System.currentTimeMillis()));
    //加入批处理
            pstmt.addBatch();
    }
        pstmt.executeBatch(); //执行批处理
        pstmt.close();
        myBroker.freeConnection(conn); //连接归池
    }

### **多值插入SQL** ###

**普通插入SQL：**INSERT INTO TBL\_TEST (id) VALUES(1)

**多值插入SQL：**INSERT INTO TBL\_TEST (id) VALUES (1), (2), (3)

使用多值插入SQL，SQL语句的总长度减少，即减少了网络IO，同时也降低了连接次数，数据库一次SQL解析，能够插入多条数据。

### **事务(N条提交一次)** ###

在一个事务中提交大量INSERT语句可以提高性能。

1、将表的存储引擎修改为myisam 2、将 sql 拼接成字符串，每 1000 条左右提交事务。

*  执行多条SQL语句，实现数据库事务。  
 *  mysql数据库  
 *  多条SQL语句

public void ExecuteSqlTran(List<string> SQLStringList)
    {
        using (MySqlConnection conn = new MySqlConnection(connectionString))
        {
            if (DBVariable.flag)
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        //后来加上的  
                        if (n > 0 && (n % 1000 == 0 || n == SQLStringList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    //tx.Commit();//原来一次性提交  
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
    }

10w条数据大概用时10s！

作者：36691683

https://blog.csdn.net/qq\_36691683/article/details/89297261

**参考资料：**

https://www.cnblogs.com/aicro/p/3851434.html

http://blog.jobbole.com/29432/

**推荐阅读：**

1、[JVM加载Class文件的原理机制][JVM_Class]

2、[项目中常用的19条MySQL优化][19_MySQL]

3、[最新SQL数据库面试题以及答案！][SQL]

4、[老要求用java将word转为PDF，这么搞？][java_word_PDF]

5、[面试官：来说说单点登录的三种实现方式][Link 1]

6、[36张图带你全方位梳理Intellij IDEA常用配置][36_Intellij IDEA]

7、[卧槽，为什么volatile关键字保证不了线程安全啊][volatile]

8、[单怼60道][60][多线程][60][面试题，你能答上几个?(附答案)][60]

9、[面试：如何决定使用 HashMap 还是 TreeMap？][HashMap _ TreeMap]

10、[如果mysql磁盘满了，会发生什么？还真被我遇到了][mysql]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjYxMTcx_size_16_color_FFFFFF_t_70][]

[format_png]: /images/20221120/17d63f035c044f4d9a4aefc56fe59dfb.png
[JVM_Class]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247492840&idx=1&sn=d648d08050dd2a9a1ea2a74f946222e5&chksm=fbc22a24ccb5a332f7f6f597d4c3c703b50b9a13aec76b4176e7d7115fe153b7fca4ed67ff81&scene=21#wechat_redirect
[19_MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247492315&idx=1&sn=37c6888e30892c7fae56d0769c35a571&chksm=fbc22c17ccb5a501df41ddeaecf0f7e85e292b45d6c7522e4d6a4d9c4a93a389338ee175eae8&scene=21#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247493386&idx=1&sn=6014e7fb6c2a43ca8df3b011754fa4e1&chksm=fbc229c6ccb5a0d068fdce76d8e457510c9c43e866952a917ae63ee0f1da8c5c25bd5d3879a2&scene=21#wechat_redirect
[java_word_PDF]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247492363&idx=1&sn=6975a1a5de92dc49d04654f21ffa7c21&chksm=fbc22dc7ccb5a4d1b94f2a9bbce26082c9f845fbbf7d588a5540d072bcef39b2281d0b705f91&scene=21#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247493492&idx=1&sn=cd0a9b65387d5107fe9d0e1ded268d22&chksm=fbc229b8ccb5a0ae5f433e40aeb031d462bc825b837ca9e09720b6b58439dada694eeb54bbaf&scene=21#wechat_redirect
[36_Intellij IDEA]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247493819&idx=1&sn=adbe3d51c9c76a6b633bc5a2e28722d6&chksm=fbc22677ccb5af615a8f19b9a525feaa0feb1ee49e86a984c058137317ef428fed71f9cd13a4&scene=21#wechat_redirect
[volatile]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247492809&idx=1&sn=47c50b227f8063129ed09edc91449100&chksm=fbc22a05ccb5a31383ee13bb6c58f8c12dada691c16624e028f23f24c32d1e4201b4ebd1a354&scene=21#wechat_redirect
[60]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247490459&idx=1&sn=66bc23ae41a8abcb9a6f00be754b2e47&chksm=fbc1d557ccb65c41ec5d3b7dedd6c7a22949ddcb5de1406de482c3b702a1a4768c66b730b219&scene=21#wechat_redirect
[HashMap _ TreeMap]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247493801&idx=1&sn=6d045289ce73d7938a09c457159949ad&chksm=fbc22665ccb5af730da57265ad08e12a1639b6476210d4e2b297b361d836afe9fba00e236d54&scene=21#wechat_redirect
[mysql]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ==&mid=2247493855&idx=1&sn=58f38e39b2c01ea679e954fd1d799c9e&chksm=fbc22613ccb5af0566b07d1809703b7b5e6cdc1b734aae414bec51b68b888ef98d563e493469&scene=21#wechat_redirect
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjYxMTcx_size_16_color_FFFFFF_t_70]: /images/20221120/b80e578dff594dfa84b5e1c3b4e7dc5f.png