深入浅出MySQL海量数据批量更新插入、批量查询

深入浅出MySQL海量数据批量更新插入、批量查询

引用

CSDN

1.

https://blog.csdn.net/Xiaowu_First/article/details/139465784

在处理大量数据时，如何高效地进行数据的批量写入和读取是数据库开发人员经常面临的问题。本文将深入探讨MySQL中批量写入和批量读取的实现方法，包括使用PrepareStatement的批量写入、load data infile的高性能插入，以及游标查询和流式查询的渐进式读取策略。通过具体的SQL语句、参数配置和性能优化建议，帮助读者更好地理解和应用这些技术。

1. MySQL的批量写入

1) PrepareStatement.executeBatch真的能批量写数据吗？

MySQL批量插入可以用下面这种，在VALUES之后跟上各种多个值列表。但这种写法可能导致SQL长度超长、锁超时等问题。

在数据量较大的时候，上面这种方式就不太合适。MySQL提供了批量写入的方法，将大批量的SQL脚本一批次发送到服务端，减少IO次数，然后统一一次执行SQL。这种写入效率会高很多。如下所示，先执行statement.addBatch()，先将SQL添加到statement列表，然后执行statement.executeBatch()，统一批量执行SQL。MyBatis-Plus的批量提交的底层实现就是基于此，它默认将1000条SQL作为一个批次。

理论上，上面的SQL是批量提交到MySQL server统一执行的，但在默认情况下实际上它还是一条条执行命令，要真正的批量执行SQL，需要在JDBC连接URL加上rewriteBatchedStatements=true，这个参数的默认值是false。

从Wireshark抓包的情况来看，在默认配置下，SQL批量执行貌似没起作用。这里的客户端发送一个SQL脚本得到一个response响应，发送一个SQL得到一个响应，循环往复，这根本就是在串行化执行SQL嘛。

下面这图是添加了rewriteBatchedStatements=true这个参数后的抓包截图。此时JDBC客户端重写了SQL语句，它把多个SQL语句用分号分隔并连接在一起，形成一个大SQL脚本，然后将这个SQL脚本一次性发送到server端，最后接收到了多次的response响应。这种情况才是我们想要的结果呀。

rewriteBatchedStatements这个参数不只是对插入数据有效，对UPDATE、DELETE语句也有同样的效果。

2）冷门的load data infile有更高的插入性能

如果有更大批量的结构化数据需要插入，可以使用LOAD DATA LOCAL INFILE这个指令。这个指令是客户端的数据导入指令，与之相对应的还有服务端导入指令LOAD DATA INFILE。

现在回到LOAD DATA LOCAL INFILE这个指令，它可以将本地文本文件中的格式化数据快速导入到远程MySQL server，这个指令比普通的SQL语句快20倍以上。本地测试在没有激烈的锁竞争情况下插入100万数据只用了10秒钟，当然在真实环境中需要考虑文本文件传输的网络I/O，这会增加不少耗时。

这个指令一般和REPLACE INTO、INSERT INTO结合起来使用。

这个指令需要指定数据文件名、字符集编码、文本文件的字段分隔符，数据库的字段名列表（注意和文本文件中字段列的顺序一致）。

注意：要使用这功能需要先在服务端将环境变量local_infile为on，表示启用这个特性。另外还要在客户端启动这个功能，在JDBC连接URL上加上参数allowLoadLocalInfile=true。

顺便提一下，MySQL有一个自带的命令行工具mysqlimport，它和LOAD DATA语法类似。

2. 批量读

大数据的查询需要渐进式查询，如果用普通的查询可能导致MySQL数据报文太大、JVM内存溢出等问题。

MySQL上有两种解决方案，(1) 游标查询；（2）流式查询。

国内一般都用MyBatis做ORM框架，现在用MyBatis实现这两种功能。

MyBatis提供了org.apache.ibatis.cursor.Cursor进行游标查询，但这本质上还是客户端的游标查询，这跟MySQL底层的游标查询是两回事儿，它实际上还是一次性从MySQL服务器检索出所有数据。不信，请往下看。

1) 你的游标查询可能用了个伪游标

从上面可以看到我定义了一个游标结果集类型的接口，且定义了fetchSize，但是从com.mysql.cj.protocol.a.BinaryResultsetReader#read方法可以看到，客户端一次性获取到了所有的数据，它是一个静态结果集，并没有分段渐进获取。

现在我在连接参数上加上useCursorFetch=true，重启项目再执行接口。再看看debug时截图信息，现在结果集是游标类型结果集ResultsetRowsCursor。而且从wirekshar抓包来看，还出现了Fetch Data这个数据报文，这个数据报文内容是10000，这恰好和XML中配置的fetchSize="10000"对应上了。在之前的那个示例中，没加useCursorFetch连接参数，JDBC客户端是没有Fetch Data数据报文的。

2）流式查询原来这么简单

流式查询也可以用org.apache.ibatis.cursor.Cursor实现，只需要将fetchSize设为-2147483648即可启用。为啥是-2147483648尼，因为这个值是Integer的最小值Integer.MIN_VALUE。MySQL驱动判断是否是流式查询的方法在com.mysql.cj.jdbc.StatementImpl#createStreamingResultSet中，它的判断逻辑是：

• 结果集类型是FORWARD_ONLY
• 结果集并发类型是CONCUR_READ_ONLY
• 且fetchSize是Integer.MIN_VALUE

就启用流式结果集。其中ResultType默认是FORWARD_ONLY、resultSetConcurrency默认是CONCUR_READ_ONLY，所以我们只要保证fetchSize是Integer.MIN_VALUE，那么就可以启用流式结果集。

MyBatis XML中修改下fetchSize参数，其他的不用变更（这里得先把参数useCursorFetch恢复成默认值false）。

从下面的截图可以看出，此时返回结构是流式结果集ResultsetRowsStreaming。

3) 优缺点对比

1. 游标查询:

• 优点：游标查询可以向前向后移动，方便数据跳转（MySQL原生游标而非MyBatis游标），分批渐进查询，对大数据查询较友好。
• 缺点：可能会导致CPU 、IOPS飙升（磁盘每秒的读写次数）、游标查询必须启用全局useCursorFetch参数，使得在其他普通查询MySQL底层也会启动游标查询，并且会让生成临时表空间，可能导致磁盘使用率爆增。

2. 流式查询:

• 优点：流式查询从服务器获取数据并每次处理一个数据包，减少了内存消耗和资源占用，适合大数据量的处理；流式查询只需要修改statement级别的fetchSize，不会影响数据库Connection级别的行为。
• 缺点：流式查询在处理过程中缺乏复杂的控制结构和逻辑，不如游标灵活，只能向前移动。