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

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级别的行为。
   缺点：流式查询在处理过程中缺乏复杂的控制结构和逻辑，不如游标灵活，只能向前移动。