MySQL预处理知多少

经常在网上看到说预处理可以提高性能和防注入，到底是为啥呢？

MySQL数据库支持预处理，预处理或者说是可传参的语句用来高效的执行重复的语句。

工作流

预处理语句执行包含两个阶段：预处理与执行。在预处理阶段，预处理语句模板被发送到服务端，服务端执行语法检查并且初始化之后使用的资源。

MySQL预处理支持匿名占位符 ‘?'。

预处理阶段：

$obj = new Pdo(
    "mysql:host=127.0.0.1;dbname=test;port=1234",
    'wuzehui',
    'kkkkkk',
    array(PDO::ATTR_EMULATE_PREPARES => false)
);

$sth = $obj->prepare('insert into t1 (id, name, age) values (?, "wuzehui", 18)');

预处理之后是执行。执行期间，客户端绑定参数并且将他们发送到服务器端。服务端根据语句模板和绑定好的值创建语句，并且使用之前申请的内部资源来执行。

绑定和执行：

$a = 3;
$sth->bindParam(1, $a, PDO::PARAM_INT);
$sth->execute();

重复执行

预处理语句可以重复执行，每次执行时，被绑定的变量会被解析并且发送到服务器。语句不用再次解析，预处理模板也不用再次传送到服务端。

for($a = 4; $a < 10; $a++){
    $sth->execute();
}

$sth->closeCursor();
$sth = $obj->query("select * from t1");
$sth->setFetchMode(PDO::FETCH_ASSOC);

$result = $sth->fetchAll();
print_r($result);

注：第一次用绑定参数的时候可能会直接这样写$sth->bindValue(1, 5, PDO::PARAM_INT);，这样是不行的，第二个参数必须是可引用的，就是必须是变量，在重复执行那里就知道为啥了，因为不用重新绑定就可以执行了。

每个预处理语句都会占用服务器资源，在使用完之后应该显示的关闭。如果没有关闭，最终会被PHP释放。

使用预处理语句并不是执行语句最高效的方式。仅执行一次的预处理语句会比没有预处理的语句多一次服务端与客户端之间的往返次数。这就是上面的SELECT语句没有使用预处理的原因。

另外，可以考虑使用MySQL的多句插入的语法，如下。这种语法比上面的预处理语句有更少的服务端往返次数：

$obj->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)");

协议

MySQL客户端服务端针对预处理语句和非预处理语句定义了不同的数据传输协议。预处理语句使用所谓的二进制传输协议，MySQL服务端以二进制发送数据，发送之前不会被序列化。客户端不会仅接收字符串，相反的，他们将获取二进制数据并且尝试去转换值到合适的PHP数据类型。例如，SQL INT列的值将会转换为PHP的整形。

这种行为和非预处理的语句不同，默认情况下非预处理的语句用字符串返回所有的结果，这个可以通过连接选项修改，那样就没有区别了。

例如：PDO::ATTR_STRINGIFY_FETCHES

注：他这里的意思是使用非预处理的方式，对于id这种字段返回的可能是字符串。我用PDO做了测试，发现也是int，官网用的mysqli。

buffer

预处理语句默认返回无缓冲的结果集，语句的结果不会被隐式提取并且传送到客户端进行客户端缓冲。结果集会占用服务端资源，直到被客户端全部获取，因此建议及时使用。如果客户端获取所有的结果集失败或者客户端在获取所有结果之前关闭了语句，那么这些数据会被mysqli获取。

也可以通过使用mysqli_stmt_store_result函数缓冲预处理的结果。

使用mysqli_stmt_get_result函数获取被缓冲的结果集。

PDO可以使用PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 来达到同样的效果。

转义与sql注入

被绑定的变量与查询分开传输到服务端，因此不会干扰查询。服务端在执行点直接使用这些值，是在语句解析之后。被绑定的参数不需要转义，因为他们不会直接替换到查询语句中。调用函数的时候会为绑定的变量添加类型提示，以便服务端进行合适的转换。

这种分离机制有时候被认为是防止sql注入的唯一方案，但是如果值被正确的格式化，非预处理也能达到相同的安全性。需要注意的是，正确的格式化与转义是不同的，并且比转义有更多的逻辑。因此，预处理对数据库安全来说只是更简单和更不容易出错的方案。

比较预处理与非预处理

总结：

回到文章开头说的，如果是只有一个sql，在不考虑安全性的情况下，非预处理反而要好一些，因为传输一次就可以了，预处理需要传输两次，这个和客户端（pdo or mysqli）有关后面我面会介绍。

如果是一个sql，需要使用不同的值来执行，预处理会省去后面所有的语句的解析、编译、调优，对性能有很大的帮助。

在安全性方面，如果语句中所有的变量都进行绑定，预处理安全性更好。

update 2018.7.28

根据上面的介绍，我们可以知道一次预处理的过程会发送多个请求。如图抓的general log里面的日志，分别是非预处理和预处理：

所以当我们应用了负载均衡的时候，就有可能模版与参数分别发送到不同的数据库服务上，从而出现错误。例如 Atlas的issue “Prepare语句报错：SQLSTATE[HY000]: General error: 1243 Unknown prepared statement handler (4) given to mysqld_stmt_execute” PDO可以这样设置：

$obj = new Pdo(
    "mysql:host=127.0.0.1;dbname=test;port=1234",
    'wuzehui',
    'kkkkkk',
    array(PDO::ATTR_EMULATE_PREPARES => true)  // 这里设置成true
);

设置好这个参数之后，本地强制模拟预处理，对数据库来说就是一条语句，没有走预处理的流程。

针对360的Atlas工具，做了测试，发现开启预处理之后负载均衡就不起作用了，所有的查询一直都走某一台机器。猜测可能是为了避免上面的问题做的处理，具体可到官方提issue。

（完）