MySQL高级特性——绑定变量

从MySQL 4.1 版本开始，就支持服务器端的绑定变量，这大大提高了客户端和服务器端数据传输的效率

介绍

当创建一个绑定变量 SQL 时，客户端会向服务器发送一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回个客户端一个 SQL 语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。

绑定变量的SQL，使用问号标记可以接受参数的位置，当真正需要执行具体查询的时候，则使用具体值来替代这些问号。例如，下面是一个绑定变量的SQL语句：

INSERT INTO tb1(col1, col2, col3) VALUES(?,?,?);

可以通过向服务器端发送各个问号的取值和这个SQL的句柄来执行一个具体的查询。反复使用这样的方式执行具体的查询，正是使用绑定变量的优势所在。具体如何发送取值参数和SQL句柄，这各个客户端的编程语言有关。

因为如下的原因，MySQL可以在使用绑定变量的时候高效执行大量的重复语句：

1. 在服务器端只需要执行一次SQL语句

2. 在服务器端某些优化器的工作只需要执行一次，因为它会缓存一部分的执行计划

3. 以二进制的方式只发送参数和句柄，比每次都发送 ASCII 码文本效率更高

4. 仅仅是参数而不是整个查询语句，因此网络开销会更小

5. MySQL在存储参数的时候，直接将其存放到缓存中，不再需要在内存中多次复制

绑定变量相对也更加安全。因为无需在应用程序中处理转义，一则更加简单了，二则也大大减少了SQL诸如和攻击的风险。

绑定变量的优化

对于绑定变量的SQL，MySQL 能够缓存其部分执行计划，如果某些执行计划需要根据传入的参数来计算时，MySQL就无法缓存这部分的执行计划。根据优化器什么时候工作，可以将优化分为三类：

准备阶段

服务器解析SQL语句，移除不可能的条件并且重写子查询

在第一次执行的时候

如果可能，服务器先简化嵌套循环的关联，并将外关联转化成内关联

在每次SQL语句执行时

服务器会做如下事情：

• 过滤分区

• 如果可能的话，尽可能移除COUNT()、 MIN() 和 MAX()

• 移除常数表达式

• 检测常量表

• 做必要的等值传播

• 分析和优化 ref、 range和索引优化等访问数据的方法

• 优化关联顺序

SQL 接口的绑定变量

在4.1 和更新版本中，MySQL 支持了SQL接口的绑定变量。不使用二进制传输协议也可以直接以SQL 的方式使用绑定变量：

SQL接口的绑定变量的最主要用途就是在存储过程中使用。在MySQL5.0 版本中，这就可以在存储过程中使用绑定变量，其语法和前面介绍的SQL接口的绑定变量类似。这意味着可以在存储过程中构建并且执行动态的SQL语句，即可以通过灵活的拼接字符串等参数构建SQL语句。

绑定变量的限制

关于绑定变量的一些限制和注意事项如下：

• 绑定变量是会话级别的，所以链接之间不能公用绑定变量句柄。同样的，一旦连接断开，则原本的句柄也不能再使用了。（连接池和持久化连接可以在一定程度上缓解这个问题）

• 并不是所有的时候使用绑定变量都能获得更好的性能。如果只是执行一次的SQL，那么使用绑定变量方式无疑比直接执行多了一次额外的准备阶段消耗，而且还需要一次额外的网络开销。

• 绑定变量SQL总数的限制是一个全局限制，如果总是忘记释放绑定变量资源，则在服务器端很容易发生资源“泄漏”。

不过使用绑定变量最大的障碍可能是：它是如何实现以及原理是什么样的，这两点很容易让人困惑。有时候，很难解释如下三种绑定变量类型之间的区别是什么：

客户端模拟的绑定变量

客户端的驱动程序接收一个带参数的SQL，再将指定的值带入其中，最后将完整的查询发送到服务器端。

服务器端的绑定变量

客户端使用特殊的二进制协议将带参数的字符串发送到服务器端，然后使用二进制协议将具体的参数值发送给服务器端并执行。

SQL接口的绑定变量

客户端先发送一个带参数的字符串到服务器端，这类似于使用 PREPARE 的 SQL 语句，然后发送设置参数的SQL，最后使用 EXECUTE 来执行SQL。所有这些都是使用普通的文本传输协议。

Java 填坑手册，欢迎fork我的GitHub仓库

作者：AmosH

原文链接：https://www.cnblogs.com/AmosH/p/10291717.html