作者：董诚怡
爱可生 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，也就是说后端通讯和普通的即时查询无异，只是需要做一些协议上的包的转换。
原理
- prepare 阶段将语句暂存，不进行预编译
- 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
客户端
- 使用支持游标的driver（MySQL官方的JDBC driver就支持）
- 如果是JDBC需开启useServerPrepStmts和useCursorFetch选项
- 执行 prepareStatement 后设置 fetchSize，必须大于 0.
- 执行 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
原理：
- prepare 阶段下发特殊语句。用于计算 SQL 中的列数，这是 client 所需的开启游标的必要条件。
- execute 阶段把结果集存储到临时文件
- fetch 阶段把结果集分批次一次次取出来
相关参数
maxHeapTableSize
heapTableBufferChunkSize
见文档 https://actiontech.github.io/dble-docs-cn/1.config_file/1.02_bootstrap.cnf.html
网友评论