面试官又给我上了一课：MySQL的prepared statement是啥玩楞？批量更新究竟该如何操作？

前言

今天进行了一场面试，又谈及了项目中MySQL大批量数据插入更新的优化问题，我信心满满的给出了我临时表+分布式锁的批量更新方式，又用到了redis实现了分布式锁，又用到了MySQL的临时表，简直干货满满啊，应付个校招面试还不是手到擒来？

不曾想啊，我讲了N遍的故事在这个面试官这里不管用了，针对我加分布式锁的问题详细的追问，最后我败下阵来，承认除了借用临时表我不了解其他进行批量更新的好办法。面试官跟我说，你批量更新可以用prepared statement啊。我顿时一脸懵逼，赶忙承认自己的无知，表示面试结束一定去好好了解。

从SQL语句的执行过程说起

要了解什么是prepared statement，就要知道从客户端发送了一条SQL语句，到MySQL服务器执行SQL得到结果，再将结果通过MySQL的传输协议返回给客户端的过程中具体发生了什么。

首先，我们建立mysql连接时，我们的uri通常是这样的形式：

mysql://localhost:3306/database?someparameter=somevalue

这里开头的mysql，就如同http一样，是一层基于TCP的MySQL自定义的传输协议。我们由客户端发往服务端的请求，以及由服务端发往客户端的返回数据，均是通过MySQL自定义的二进制传输协议实现。

因此，执行一条SQL语句的第一个耗时的地方就出现了：将请求发送给服务端的网络IO时间。

当这条SQL到达MySQL服务器后，服务器会做什么呢？了解的同学应该知道MySQL的服务器架构，连接层处理请求，得到SQL语句后，交由分析器进行词法分析，语法分析，语义分析，完成后再交由优化器进行执行计划的优化，最后由执行器调用存储引擎完成执行。

在这个过程中，分析器的词法分析及语法分析实际上是在验证SQL语句的正确性，我们将这两个步骤称为硬解析，最后的语义分析则是在判断此条SQL语句要做的事情。

明确了这个过程，我们又了解到了执行SQL语句的其他耗时时间：解析的时间+SQL语句的优化时间+SQL语句实际的执行时间。

当然最终还需要将结果返回给客户端的网络IO时间

什么是prepared statement

明确了以上过程后，让我们考虑一下在大批量不同数据的相同操作，有哪些时间可以优化？

首先就是将请求发送给服务器的网络请求，如果我们批量发送数据，显然能比单条发送数据减少网络交互次数。

第二个点就是我们的硬解析时间了。我们注意到由于SQL语句都是一样的，每条数据都去进行硬解析是没有必要的，这个过程仅进行一次就好了，那么有没有类似预编译的概念把我们的SQL语句编译好，然后直接把数据填充进去，类似于函数调用的形式呢？MySQL提供了这样的操作，就是prepared statement。

对于prepared statement，和他相对的是普通的statement。对于普通的statement，我们每次执行都会进行完整的解析流程。而对于prepared statement，在SQL语句层面，我们会先给出要执行的SQL语句，其中数据用占位符给出：

prepare stmt from "insert into test (`id`,`str`) values (?,?)";

此时MySQL进行SQL语句的硬解析并缓存解析的结果。然后：

set @a = 1, @b = 'gale';
execute stmt using @a, @b;

这样就能直接调用硬解析好的语句进行接下来的流程。当要操作的数据量特别大时，我们能剩下大量的硬解析的时间。这就是prepared statement的意义。而当数据量只有一条时，显然是性能上不如不同的statement，这是因为我们进行了两次网络IO。

prepared statement结合batch操作

那么如果我们还想减少网络IO次数，批量发送数据到服务器，该如何实现呢？(下文为mysql-connector-java-5.1.43的实现方法)

批量插入

对于批量插入，我们直接优化为如下形式：

insert into test (`id`, `str`) values (1, 'a'), (2, 'b'), (3, 'c')...

批量更新

对于批量更新，我们呢先进行SQL语句的硬解析：

prepare stmt from "update test set `str` = ? where `id` = ?";

然后我们批量的将数据传输过去并执行：

set @a = 1, @b = 'a'; execute stmt using @a, @b;
set @a = 2, @b = 'b'; execute stmt using @a, @b;
set @a = 3, @b = 'c'; execute stmt using @a, @b;
set @a = 4, @b = 'd'; execute stmt using @a, @b; 
#封装到一次网络请求中

这样是比较好的方法。

本文地址：https://blog.csdn.net/GaleZhang/article/details/108222197