分布式 | 中间件是如何处理 Prepare Statement

作者：董诚怡

爱可生 dble 团队开发成员，主要负责 dble 需求开发，故障排查和社区问题解答。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

---

PS语句（预编译语句）

以下用 缩写 PS 代替 Pepared Statement

PS 的优点

• 防止SQL注入漏洞
• 标准的 PS 可以 一次编译、多次运行，省去了每次都要解析优化的过程。（被称为预编译，有利于多次执行的结构相同参数不同的语句）
• 更灵活的调用

PS 的缺点

• 普通的查询（即时 SQL ） 需要一次网络开销，但是 PS 需要至少两次网络开销。如果游标的话，次数将更多，和数据量成正比。
• 需要空间开销用于缓存当前的 SQL 预编译后的结构，相对于即时 SQL，这块空间不能及时释放。
• 对 in 语句的支持不太好

分类

• server-side PS：通过 client发送 PS 协议的报文给server，由 server来完成拼装参数、优化、执行。
• client-side PS: 由 client 来实现 PS 接口，prepare 阶段完成拼装参数，拼装完后，一次性发送 即时 SQL给 server，由 server 来完成优化、执行。这本质上是一个伪预编译，上述的 ”省去了每次都要解析优化的过程“ 这个优点无法实现。

使用 dble 侧 PS 必要条件

DBLE 端

• 无

客户端

• 如果是 JDBC 需开启 useServerPrepStmts ，此时才会使用 server-side prepare ，否则属于 client-side prepare。

验证是否开启了

PreparedStatement preparedStatement = con.prepareStatement("select t1.id from no_sharding_t1 t1 where t1.id=?");
//可用于验证是否使用了dble 侧 prepare
assert preparedStatement instanceof ServerPreparedStatement;

协议

• COM_STMT_PREPARE

  发送：SQL

  作用：创建一个statement，完成预编译，执行优化等准备工作，等待下一步执行。

  响应: statement 的 id 以及 column 的数量、argument 数量和类型

• COM_STMT_EXECUTE

  发送：statement的id ，每个占位符绑定的值，以及是否想要开启游标

  作用：执行 SQL

  响应：OK 响应或者结果集

• COM_STMT_FETCH

  发送：statement 的 id ，期望获取的行数

  作用：分批次获取执行后数据

  响应：指定行数的结果集

  注意：该协议可选，也可以在 execute 环节上返回结果集，dble 一般仅在开启游标时使用该报文

• COM_STMT_SEND_LONG_DATA

  作用：发送占位符绑定的值，通常只用于发送 BLOB 数据

• COM_STMT_RESET

  作用：重置 COM_STMT_SEND_LONG_DATA 设置的值 （通常不用）

• COM_STMT_CLOSE

  发送：statement的id

  作用：关闭 之前的 preapred statement，回收所有资源

  响应： 无

流程图:

image

可以看到 client <-> dble 通讯使用了 server-side prepare，dble <-> MySQL 通讯使用了 client-side prepare，也就是说后端通讯和普通的即时查询无异，只是需要做一些协议上的包的转换。

原理

1. prepare 阶段将语句暂存，不进行预编译
2. execute 阶段拼装参数和语句，将其下发，获得结果后转换为PS协议并返回

游标

游标 的优点

• 对于客户端较友好，不会因为大查询而 OOM

游标 的缺点

• 较慢
• 资源不及时释放

分类

• server-side cursor：server 把结果集暂存起来，维护一个游标，client 根据需要读取指定的行数
• client-side cursor: client 从 TCP 层面 控制报文的读取，对报文进行流量控制，当报文太多时暂停读取。(不推荐，因为server 需要等待所有数据发送给 client 后，才能释放资源。）
• 另一种 client-side cursor：client 把所有结果集读取到本地缓存，client 每次从缓存读取指定行数（不推荐，本质上是个伪 cursor，只实现了 cursor API。并且在数据量较大时很容易撑爆 client 的内存）

后两种是否支持取决于 client 端的 driver，dble 支持的是第一种 server-side cursor。

游标开启必要条件

DBLE 端

注：读写分离场景由于不支持 COM_STMT_FETCH 报文，所以不支持游标。以下描述仅针对分库分表。

• 如果版本<3.21.02, 则不支持。
• 如果版本=3.21.02,无需设置
• 如果版本>3.21.02,需在 bootstrap.cnf开启-DenableCursor=false

客户端

1. 使用支持游标的driver（MySQL官方的JDBC driver就支持）
2. 如果是JDBC需开启useServerPrepStmts和useCursorFetch选项
3. 执行 prepareStatement 后设置 fetchSize，必须大于 0.
4. 执行 execute

此时是开启游标的，如果对结果集 resultSet进行遍历，会按 fetchSize 的大小一次次地从 dble 取回数据。

验证是否开启了游标

客户端执行第4步后, 调用私有方法 useServerFetch 可验证。

final ResultSet resultSet = preparedStatement.executeQuery();
//可用于验证是否使用了server-side 游标
Method method = com.mysql.cj.jdbc.StatementImpl.class.getDeclaredMethod("useServerFetch");
method.setAccessible(true);
Boolean useServerFetch = (Boolean) method.invoke(preparedStatement);
assert useServerFetch==true;

流程图：

image

原理：

1. prepare 阶段下发特殊语句。用于计算 SQL 中的列数，这是 client 所需的开启游标的必要条件。
2. execute 阶段把结果集存储到临时文件
3. fetch 阶段把结果集分批次一次次取出来

相关参数

maxHeapTableSize

heapTableBufferChunkSize

见文档 https://actiontech.github.io/dble-docs-cn/1.config_file/1.02_bootstrap.cnf.html