MySQL之Prepared Statements
1.概述
prepared statement在MySQL4.1中引进并且增加了一些新的命令:
- COM_STMT_PREPARE
- COM_STMT_EXECUTE
- COM_STMT_CLOSE
- COM_STMT_RESET
- COM_STMT_SEND_LONG_DATA
它还定义了一个更紧凑简洁的结果集格式代替ProtocolText::Resultset来返回结果集。
记住不是所有的语句都是可以预处理的:
1.1 预处理说明
sql预处理首先要求客户端提交需要执行的sql,这时提交的sql不传递真实的参数值,参数以问号的形式传递过去。
eg:
insert into user(id, name) values(?, ?); select * from user where id = ?;
在mysql服务端完成预编译解析,这里的预编译解析包括解析参数的个数、类型等,然后响应给客户端。接下来客户端第二次交互只需传递参数过去就可以完成一个完整的sql的执行。相比传统的sql执行,预处理需要两次交互,才能完成一次sql执行。
预处理的优势:
(1)预处理sql能一定程度上防止sql注入
(2)sql预编译效率更高
(3)二进制包协议让sql预处理更加高效。
mysql预处理命令参数的封装以及结果集的返回,均采用二进制格式封装数据,体积更小,面向底层,能直接被mysql服务端利用。相比普通sql文本协议传输的数据,二进制协议传输数据更加高效。
2.二进制协议结果集
二进制协议结果集类似ProtocolText::Resultset.它仅包含二进制协议结果集行格式。
ProtocolBinary::Resultset:
Packet:
- lenenc_int column_cout > 0
- column_count * Protocol::ColumnDefinition
- 没有或者很多ProtocolBinary::ResultsetRow
- EOF_Packet
注意:如果CLIENT_DEPRECATE_EOF客户端性能标志被设置,发送OK_Packet,否则发送EOF_Packet。
例如:
3. 二进制协议结果集行
3.1 NULL-Bitmap
二进制协议结果集行由NULL位图组成,该位图包含与结果集+ 2中的列一样多的位以及二进制协议值格式中非NULL的列的值。
ProtocolBinary::ResultsetRow:
3.2 二进制结果集的行(COM_STMT_EXECUTE)
payload:
1 packet header[00]
string[$len] NULL-bitmap,length: (列数 + 7 + 2 )/8
string[$len] values
例子:
3.2.1 NULL-Bitmap
二进制协议将NULL值作为位发送到位图内而不是像ProtocolText :: ResultsetRow那样发送完整字节。 如果发送了许多NULL值,则它比旧方法更有效。
警告:
对于二进制协议结果集行,num-fields和field-pos需要添加2的偏移量。对于COM_STMT_EXECUTE,此偏移量为0。
NULL位图需要足够的空间来为发送的每个列存储可能的NULL位。 其空间计算如下:
NULL-bitmap-bytes = (num-fields + 7 + offset) / 8
导致:
4. 预处理命令说明
4.1 COM_STMT_PREPARE
COM_STMT_PREPARE命令用于客户端往服务端提交一个预处理的sql,如上面提到的:
1 insert into user(id, name) values(?, ?);
4.2 COM_STMT_EXECUTE
COM_STMT_EXECUTE用于执行预处理sql,正如前面说到的,如果预处理sql需要传递参数,这个命令会发送预处理语句所需要的参数到服务端。如上面的例子,需要传递两个参数id和user的具体值到服务端。
4.3 COM_STMT_CLOSE
COM_STMT_CLOSE用于关闭服务端预处理sql,每一个预处理预处理的sql提交后都保存在mysql服务端的内存当中,每个预处理sql都有一个唯一的id标识,这个命令将发送需要关闭的sql的id,通知服务端可以将所有该预处理sql的资源释放掉(过多的预处理sql保留在服务端会占用较多的内存,因此有必要执行该命令清理无用的预处理sql)。
4.4 COM_STMT_RESET
COM_STMT_RESET命令用于重置COM_STMT_SEND_LONG_DATA命令发送的blob数据。
4.5 COM_STMT_SEND_LONG_DATA
COM_STMT_SEND_LONG_DATA用于往服务端发送字节流数据,通常来说只有在发送blob字段数据才用到该命令。可以多次调用该命令连续传同一个字段的字节的数据,这个命令必须在COM_STMT_EXECUTE命令发送之前执行。
5. 预处理协议结果包说明
mysql预处理结果集采用了二进制协议包进行封装,与普通的查询结果集格式不同。(普通的结果集包采用文本协议包进行封装)。
5.1 普通查询结果集协议包
普通sql查询(相比预处理sql查询)返回的结果集包用文本协议(官方称为Text Protocol)封装。文本协议的结果集包格式根据官网的一个图来说明:
一个结果集包主要包括以下部分(顺序传输):
- one pakcet show field count(第一个packet用于表示返回结果集列数)
- column defines packets(一个列就是一个packet, 格式参考Column Define Pakcet)
- EOF Packet
- row packets(一行数据就是一个packet, 格式参考ResultsetRow Packet)
- EOF Packet
5.2 预处理结果集协议包
预处理结果集包的组成和普通协议包类似,区别只在于row packet(数据以二进制协议格式存放)。
- one packet show field count(第一个packet用于表示返回结果集列数)
- column define packets(一列就是一个packet,格式参考普通协议包的Column Define Packet)
- EOF Packet
- binary row packets(一行数据一个packet,格式参考Binary Row Pakcet)
- EOF Packet
说明:
Binary Row Packet的第一个字节恒为0, 表示paket header, 接下来,由NULL-Bitmap标识那些值为NULL的列,NULL-Bitmap的长度计算方式为(column-count +7 + 2)/8,其中column-count表示列数,而非空的列值以二进制协议格式(协议格式参考Binary Protocol Value)顺序存储在NULL-Bitmap的后面。
提示:
返回相同结果行,预处理协议包所占字节比普通协议包小,在列数越多,列越长的情况下,相差的大小越明显。
5.3 mysql jdbc 预处理
java.sql.preparestatement可以执行预处理sql,mysql jdbc实现了该接口,并且将预处理分为客户端和服务端预处理
5.3.1 jdbc客户端预处理
mysql jdbc默认情况下采用的就是客户端预处理。客户端预处理的意思是,所有预处理参数都将被缓存在mysql jdbc层,而不是缓存在mysql server。在PrepareStatement执行的时候,在jdbc端完成sql语句的拼接(主要是使用缓存的参数对sql中问号?进行替换, 最终发送到mysql的就是完整的sql语句)。客户端预处理走得是普通的查询协议,而不是真正的mysql预处理协议。
6 Mycat预处理实现机制
Mycat也实现了mysql的预处理协议,可以接收预处理命令的处理。当使用预处理查询,也可以返回正确的二进制结果集包。Mycat预处理的实现是一种取巧的设计,查询走到后端mysql实际上不是发送了预处理命令,而是普通的COM_QUERY命令,后端mysql返回给Mycat的结果集包也是文本协议包,只是在Mycat将结果集包发送往客户端的中间过程,将普通的文本协议结果集包包装成为二进制协议结果集包,然后再返回给客户端。
Mycat预处理的处理流程:
(1)Mycat接收到客户端发送的COM_STMT_PREPARE命令后,解析协议包的内容得到预处理sql语句,eg:insert into user(id, name)value(?, ?),将这些预处理语句缓存在Mycat里面;
(2)当Mycat再次接收到客户端发送的COM_STMT_EXECUTE命令,就把相应的问号替换为实际传递过来的参数值,这时候已经得到了完整的sql语句。
(3)接下来,直接把这个语句丢给Mycat sql查询处理器去执行,中间会经过sql解析模块,路由解析模块以及最后的执行。
(4)最后,当收到后端mysql传递给Mycat的数据准备发往客户端的时候,做一个协议转换,将普通文本结构集协议包转换为二进制结果集协议包并发往客户端。