MySQL批量数据为什么逐个效率高？

该问题的根本原因，其实是老版本oproxy的一个UG，即部分老版本的oproxy（oproxy-4.3.1.0-712024111215之前的版本），在preparedStatement协议下，不支持大小超过16M的TCP数据包，解析此类数据包时，oproxy服务端志中会报错”wecannotsupportpacketwhichislargerthan16M(ps_pkt_len=16777219,MYSQL_PACKET_MAX_LENGTH=16777215,ret=-4007”，并重置该TCP连接。详细说明见连接：s://oceanase/knowledge-ase/oceanase-dataase-proxy-1000000000210030?ack=k该问题的临时解决方案，可以关闭服务端预编译机制或关闭批量语句重写，即去掉JDL中的usePrepStmts/userCsorFetch/rewriteatchedStatements，也可以绕开oproxy，即JDL中直接连接OSERVER而不通过OPROXY；该问题的正式解决方案，建议升级OProxy到新版本如oproxy-4.3.1.0-712024111215，这些新版本的oproxy中已经修复了该问题，可以解析大小超过16M的TCP数据包了。

2. Oceanase数据库中，什么情况下TCP数据包大小可能会超过16M?

问题虽然解决了，但残留了一个疑问点，即Oceanase数据库中，什么情况下TCP数据包大小可能会超过16M?毕竟16M还是挺大的，在没有LO/CLO大对象字段时，数据库中单条记录很少会有超过16M的情况。经查询表结构和实际数据，该业务表包含字段xxxvarcha2(1600)，该字段实际存储的部分数据确实长度较长；另经咨询业务同学，业务代码中会通过preparedStatement以批量方式数据到Oceanase，且JDL指向oproxy并配置了rewriteatchedStatements=true和userCsorFetch=true，此时在底层会使用服务端预编译协议和批量语句重写。<>至此问题原因确认了:在使用服务端预编译协议和批量语句重写时，欲批量的同一个批次内的所有记录，都会被打包到同一个TCP数据包中，所以当批次大小较如2048条数据作为一个批次，且单条记录的字段长度较大时，每个批次的所有记录被打包到单个TCP数据包时，其大小就有可能超过16M，从而触发该部分老版本的oproxy在ps协议下，不支持大于16M的TCP报文的UG,进而重置该TCP连接，从而触发了客户端的上述JD连接异常断开的报错。在后文，我们将通过TCPDUMP抓包，辅助分析下，不同数据库对PreparedStatement和批量的实现机制的异同，即基于服务端实现的预编译语句-sidepreparedstatements，和基于客户端实现的预编译语句-sidepreparedstatements；同时也重点分析下MYSQL派系数据库的L参数usePrepStmts和rewriteatchedStatements。

3. 技术背景-statementVSpreparedStatement

在向数据库数据时，我们一般会推荐使用preparedStatement而不是statement，因为PreparedStatement在安全性、性能、可读性和功能支持方面都优于Statement：

安全性-防止SQL注入：PreparedStatement可以预编译SQL语句，并通过参数化查询的方式传递参数。这种方式可以有效防止SQL注入攻击，因为参数不会被直接拼接到SQL语句中；性能更优-预编译：对于重复执行的SQL语句，PreparedStatement可以提高性能，它会预编译SQL语句并缓存编译后的结果，当再次执行相同的SQL语句时，可以直接使用缓存的结果，而不需要重新编译；性能更优-支持批处理：PreparedStatement支持批量或更新作，通过addatch()和executeatch()方法，可以批量执行多个相似的SQL语句，来完成批量的或更新，因为减少了TCP网络交互，执行性能也更高效；当然具体批次的大小，需要基于多种因素综合考量，包括JVM堆空间的大小，每条数据的大小，读取上游和下游的速度等；代码可读性和性更好：使用PreparedStatement可以使代码更加清晰和易于，参数化查询使得SQL语句和参数分离，避免了复杂的字符串拼接作，减少了出错的可能性；

4. 不同数据库JD驱动对preparedStatement实现机制的异同

JD规范定义了PreparedStatement的相关API，如connection.setAutoCommit(false)/connection.prepareStatement(sql)/preparedStatement.addatch()/preparedStatement.executeatch()/connectionmit()等，但不同数据库厂商在其JD驱动中对这些API的具体实现不同。在对PreparedStatement的内部实现机制上，分为两大类，即基于服务端实现的预编译语句-sidepreparedstatements，和基于客户端实现的预编译语句-sidepreparedstatements：

主流关系型数据库，包括Oracle/PostgreSQL，以及MySQL派系的mysql/mariad/oceanase等，都支持服务端预编译语句，也都支持批量；ORACLE/postgresql等非MYSQL派系的数据库，默认就支持服务端预编译语句，这些数据库也没有所谓的客户端预编译机制；mysql/mariad/oceanase等MYSQL派系的数据库，支持服务端预编译语句，但默认使用客户端预编译机制;mysql/mariad/oceanase等MYSQL派系的数据库，向数据库服务端批量数据时，默认不是正的批量；mysql/mariad/oceanase等MYSQL派系的数据库，如果需要使用某些特性，如流式批量地从数据库服务端获取数据，就需要使用服务端游标/服务端预编译语句，此时必须在jdcl中显示指定参数usePrepStmts或useCusrorFetch;mysql/mariad/oceanase等MYSQL派系的数据库，如果需要使用某些特性，如向数据库服务端正批量地数据，必须在jdcl中显示指定参数rewriteatchedStatements等；本文重点分析下MYSQL派系数据库的参数usePrepStmts和rewriteatchedStatements。

5. 通过tcpdump抓包看下常见数据库对preparedStatement的实现机制的异同

5.1. ORACLE数据库

ORACLE数据库，默认就支持服务端预编译语句，没有所谓的客户端预编译的机制；ORACLE数据库，通过ps进行批量时，默认就是正的批量：欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中，仅需要一次网络交互；从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL,数据包中通过“:1,:2”等占位符代表SQL中的参数变量；从JD客户端发往数据库服务端的tcp数据包，也包括欲批量的多条记录的内容：这些欲批量的同一个批次内的所有记录,被打包到同一个TCP数据包中,仅需要一次网络交互；包含预编译SQL的数据包，和包含首批欲批量的多条记录内容的数据包，会被合并到一个TCP数据包中，以进一步减少网络交互次数；这些数据包在wireshark中显示如下：

5.2. postgresql数据库

postgresql数据库，默认就支持服务端预编译语句，没有所谓的客户端预编译的机制；postgresql数据库，通过ps进行批量时，默认就是正的批量：欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中，仅需要一次网络交互；从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL,数据包中通过“2”等占位符代表SQL中的参数变量；从JD客户端发往数据库服务端的tcp数据包，也包括欲批量的多条记录的内容，这些欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中，仅需要一次网络交互；包含预编译SQL的数据包，和包含首批欲批量的多条记录内容的数据包，是作为的TCP数据包进行发送的，分别对应SQL的预编译阶段和SQL的执行阶段，即connection.prepareStatement(sql)和preparedStatement.executeatch()；这些数据包在wireshark中显示如下：

5.3. mysql数据库-JDL中使用默认参数

MYSQL数据库，支持服务端预编译语句也支持客户端预编译语句，但默认使用客户端预编译语句；MYSQL数据库，默认状况下，会通过TLS协议加密TCP数据包，为方便通过wireshark查看数据包内容，需要在JDL中显示配置参数useSSL=false；MYSQL数据库，默认状况下，通过ps进行批量时，不是正的批量：欲批量的同一个批次内的多条记录，每条记录都对应一个TCP请求，都需要一个网络交互：TCP数据包中，包含的是客户端预编译完毕的完整的可执行的SQL语句，而不是通过“2”等占位符代表的SQL中的参数变量，如：.insertintouser(id,name)values(0,'name0')；insertintouser(id,name)values(1,'name1')；insertintouser(id,name)values(2,'name2')；示例L如下："jdc:mysql://10.20.25.28:3306/?useSSL=false"这些数据包在wireshark中显示如下：

5.4. mysql数据库-JDL中指定参数usePrepStmts

MYSQL数据库，支持服务端预编译语句也支持客户端预编译语句，但默认使用客户端预编译语句；MYSQL数据库，支持在jdcl指定参数usePrepStmts，以使用服务端预编译语句，此时从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL，数据包中通过“?,?”等占位符代表SQL中的参数变量，如insertintouser(id,name)values(?,?)；MYSQL数据库，当jdcl指定参数usePrepStmts以使用服务端预编译时，默认状况下，通过ps进行批量时，仍不是正的批量：欲批量的同一个批次内的多条记录，每条记录都对应一个TCP请求，都需要一个网络交互，TCP数据包中包含的是该条记录的实际内容（参数变量值）；示例l如下："jdc:mysql://10.20.25.28:3306/?useSSL=false&usePrepStmts=true";这些数据包在wireshark中显示如下：

5.5. mysql数据库-JDL中指定参数rewriteatchedStatements

MYSQL数据库，支持服务端预编译语句也支持客户端预编译语句，但默认使用客户端预编译语句；MYSQL数据库，默认状况下，通过ps进行批量时，不是正的批量：欲批量的同一个批次内的多条记录，每条记录都对应一个TCP请求，都需要一个网络交互；MYSQL数据库，支持在jdcl中指定参数rewriteatchedStatements，以开启批量语句重写功能，此时会在客户端自动重写SQL语句，从而实现正的批量，此时从JD客户端发往数据库服务端的tcp数据包，包括欲批量的多条记录的内容，这些欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中，如insertintouser(id,name)values(0,'name0'),(1,'name1'),(2,'name2'),(3,'name3'),(4,'name4'),(5,'name5');示例L如下："jdc:mysql://10.20.25.28:3306/?useSSL=false&rewriteatchedStatements=true"这些数据包在wireshark中显示如下：

5.6. mysql数据库-JDL中指定参数usePrepStmts和rewriteatchedStatements

MYSQL数据库，支持服务端预编译语句也支持客户端预编译语句，但默认使用客户端预编译语句；MYSQL数据库，默认状况下，通过ps进行批量时，不是正的批量：欲批量的同一个批次内的多条记录，每条记录都对应一个TCP请求，都需要一次网络交互；MYSQL数据库，支持在jdcl指定参数usePrepStmts，以使用服务端预编译语句，此时从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL，数据包中通过“?,?”等占位符代表SQL中的参数变量;MYSQL数据库，支持在jdcl中指定参数rewriteatchedStatements，以开启批量语句重写功能，此时会在客户端自动重写SQL语句，从而实现正的批量，此时从JD客户端发往数据库服务端的tcp数据包，包括欲批量的多条记录的内容，这些欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中,仅需要一次网络交互；MYSQL数据库，支持在jdcl中同时指定参数usePrepStmts和rewriteatchedStatements，以达到使用服务端游标和通过批量语句重写，正实现高效的批量数据的效果，此时从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL，数据包中通过“?,?”等占位符代表SQL中的参数变量,如insertintouser(id,name)values(?,?)；也包括欲批量的多条记录的内容，这些欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中,也就仅需要一次网络交互，如：insertintouser(id,name)values(?,?),(?,?),(?,?),(?,?);示例L如下："jdc:mysql://10.20.25.28:3306/?useSSL=false&usePrepStmts=true&rewriteatchedStatements=true";这些数据包在wireshark中显示如下：

5.7. oceanase数据库-JDL中指定参数usePrepStmts和rewriteatchedStatement

Oceanase数据库，在JD协议上属于MYSQL派系，其行为跟MYSQL大体一致；Oceanase数据库，支持服务端预编译语句也支持客户端预编译语句，但默认使用客户端预编译语句；Oceanase数据库，默认状况下，通过ps进行批量时，不是正的批量：欲批量的同一个批次内的多条记录，每条记录都对应一个TCP请求，都需要一个网络交互；Oceanase数据库，支持在jdcl指定参数usePrepStmts，以使用服务端预编译语句，此时从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL，数据包中通过“?,?”等占位符代表SQL中的参数变量;Oceanase数据库，支持在jdcl中指定参数rewriteatchedStatements，以开启批量语句重写功能，此时会在客户端自动重写SQL语句，从而实现正的批量，此时从JD客户端发往数据库服务端的tcp数据包，包括欲批量的多条记录的内容，这些欲批量的同一个批次内的所有记录，被打包到同一个TCP数据包中，仅需要一次网络交互；Oceanase数据库，支持在jdcl中同时指定参数usePrepStmts和rewriteatchedStatements时，以达到使用服务端游标和通过批量语句重写，正实现高效的批量数据的效果，此时从JD客户端发往数据库服务端的tcp数据包，包括需要服务端进行预编译的SQL，数据包中通过“?,?”等占位符代表SQL中的参数变量,如insertintouser(id,name)values(?,?)；也包括欲批量的多条记录的内容，这些欲批量的同一个批次内的所有记录记录，被打包到同一个TCP数据包中,仅需要一次网络交互；如：insertintouser(id,name)values(?,?),(?,?),(?,?),(?,?);示例L如下："jdc:oceanase://10.20.190.35:2881/?usePrepStmts=true&rewriteatchedStatements=true";这些数据包在wireshark中显示如下,注意相mysql数据库，oceanase数据库服务端，多发送了很多tcpkeepalive心跳包：

6. 通过JD向数据库服务端千万级别数据的最佳实践

在数据湖仓等场景下，我们经常需要在不同数据库间同步千万级别的大量数据，此时同步作业的稳定性和性能，一方面需要思考如何稳定高效地读上游库，一方面需要思考如何稳定高效地写下游库。在文章《通过JD流式批量地从数据库服务端获取千万级别数据的最佳实践–从一个datax作业的性能优化问题聊起》中，我们分析总结了，各种常见的数据库，通过JD流式批量地从服务端获取数据，并通过配置fetchSize确保整个过程的流式和可控的最佳实践。在本文中，我们进一步分析总结了下，各种常见的数据库，通过JD向数据库服务端千万级别数据的最佳实践，并通过wireshark分析tcpdump网络包进而深入理解了MYSQL派系数据库的L参数usePrepStmts/rewriteatchedStatements。概括起来，为通过JD向数据库服务端千万级别的数据，并确保这一过程的稳定高效，需要解决有限的JVM堆空间和海量的数据，以及有限的TCP网络带宽和网络延时之间的矛盾。针对此问题，业界通用的解决方案是，使用preparedStatement批量地向数据库服务端数据，并合理控制每个批次大小，确保整个过程的流式和可控。为确保不同数据库不同JD驱动版本的兼容性，在具体实现上，最好遵循如下最佳实践：

关闭连接的自动提交：显示配置conn.setAutoCommit(false)；进一步精确控制每个批次的大小：批量提交时，每个批次中的所有记录都会被封装到同一个TCP数据包中，也只需要一次网络交互，所以理论上来说，每个批次包含的记录数越多，整体的网络交互次数就越少，性能就越高；但现实情况下，还需要考虑JD客户端JVM堆空间大小，下游数据库处理速度等多种因素；针对MYSQL派系的数据库，还需要显示指定jdcl参数usePrepStmts和rewriteatchedStatement，以达到使用服务端游标和通过批量语句重写，正实现高效的批量数据的效果；以下是开源高效数据同步工具datax的部分源码，可以看到其使用了preparedStatement进行批量，且批量大小atchSize可以在任务级别进行配置：