读《高性能MySQL》第三版,笔记。
1、绑定变量
官方文档:
https://dev.mysql.com/doc/refman/5.7/en/sql-prepared-statements.html
从 MySQL 4.1 版本开始,就支持服务器端的绑定变量(prepared statement),这大大提供了客户端和服务器端数据传输的效率。
当创建一个绑定变量 SQL 时,客户端向服务器发送了一个 SQL 语句的原型。服务器端收到这个 SQL 语句框架后,解析并存储这个 SQL 语句的执行计划,返回给客户端一个 SQL 语句处理句柄。以后每次执行这类查询,客户端都使用这个句柄。
绑定变量的 SQL,使用问号标记可以接收参数的位置,当真正需要执行具体查询的时候,则使用具体值代替这些问号。
例如,下面是一个绑定变量的 SQL 语句:
INSERT INTO tbl(cal1, cal2, cal3) VALUES (?, ?, ?);
可以通过向服务器端发送各个问号的取值和这个 SQL 的句柄来执行一个具体的查询。反复使用这样的方式执行具体的查询,这正是绑定变量的优势所在。具体如何发送取值参数和 SQL 句柄,则和各个客户端的编程语言有关。
因为如下的原因,MySQL 在使用绑定变量的时候可以更高效的执行大量的重复语句:
- 在服务器端只需要解析一次 SQL 语句。
- 在服务器端某些优化器的工作只需要执行一次,因为它会缓存一部分的执行计划。
- 以二进制的方式只发送参数和句柄,比起每次都发送 ASCII 码文本效率更高。
- 仅仅是参数——而不是整个查询语句——需要发送给服务器端,所以网络开销会更小。
- MySQL 在存储参数的时候,直接将其存放到缓存中,不再需要在内存中多次复制。
绑定变量相对也更安全。无须再应用程序中处理转义,一则更简单了,二则也大大减少了 SQL 注入和攻击的风险。
1.1、绑定变量的优化
对使用绑定变量的 SQL,MySQL 能够缓存其部分执行计划,如果某些执行计划需要根据传入的参数来计算时,MySQL 就无法缓存这部分的执行计划。根据优化器什么时候工作,可以将优化分为三类。
在准备阶段
服务器解析 SQL 语句,移除不可能的条件,并且重写子查询。
在第一次执行的时候
如果可能的话,服务器先简化嵌套的关联,并将外关联转化成内关联。
在每次 SQL 语句执行时
服务器做如下事情:
- 过滤分区。
- 如果可能的话,尽量移除 COUNT()、MIN() 和 MAX()。
- 移除常数表达式。
- 检测常数表。
- 做必要的等值传播。
- 分析和优化 ref、range 和索引优化等访问数据的方法。
- 优化关联顺序。
1.2、SQL 接口的绑定变量
在 4.1 和更新的版本中,MySQL 支持了 SQL 接口的绑定变量。不使用二进制传输协议也可以直接以 SQL 的方式使用绑定变量。
1.3、绑定变量的限制
一些限制:
- 绑定变量是会话级别的,所以连接之间不能共用绑定变量句柄。
- 在 MySQL 5.1 版本之前,绑定变量的 SQL 是不能使用查询缓存的。
- 并不是所有的时候使用绑定变量都能获得更好的性能。
- 当前版本(5.5)下,还不能在存储函数中使用绑定变量(存储过程可以)。
- 如果总是忘记释放绑定变量资源,则在服务器端很容易发送资源 “ 泄漏 ”。
- 有些操作,如 BEGIN ,无法在绑定变量中完成。
有时,很难解释如下三种绑定变量类型之间的区别是什么:
- 客户端模拟的绑定变量
- 客户端的驱动程序接收一个带参数的 SQL,再将指定的值带入其中,最后将完整的查询发送到服务器。
- 服务器端的绑定变量
- 客户端使用特殊的二进制协议将带参数的字符串发送到服务器端,然后使用二进制协议将具体的参数值发送给服务器端并执行。
- SQL 接口的绑定变量
- 客户端先发送一个带参数的字符串到服务器端,这类似于使用 PREPARE 的 SQL 语句,然后发送设置参数的 SQL,最后使用 EXECUTE 来执行 SQL。所有这些都使用普通的文本传输协议。