记一次mysql的preparedStatement使用超限问题

【现象&背景】

        本服务是个为数据库的分库分表提供路由规则计算,sql过滤执行的中间服务。即上游将请求发给本服务,本服务根据分库分表规则将相应的sql执行发送给对应的分库、分表去执行,并整理结果返回给上游。

        2016-07-14下午,上游流量切换后,mysql大量报出" Can't create more than max_prepared_stmt_count statements (current value: 16382)",mysql不能工作导致本层数据库路由服务不能工作,大量请求失败。

        大概意思就是说,同一时间在mysqld上所有session中preparedStatement语句超过了mysql的限制,导致新建preparedStatement失败,产生错误。

        临时解决:上游回退。

【原因分析】

  • 为什么会建立这么多的preparedStatement?

        1)其中有个接口由于bug导致会对所有分库、分表进行全表sql执行操作,而每个sql执行前都会新建一个preparedStatement

        2)上游切换流量后使用的新库扩大了分库数和分表数,导致一个请求对应分表数量成倍数增加,那一个请求对应preparedStatement数量也成倍数增加。

  • preparedStatement是为何物?

        preparedStatement是在多次重复执行只用参数值不同的sql语句时,先发一个请求去mysql服务端将sql语句编译好,并将其预编译结果存储在preparedStatement对象中,以后便可以使用该对象高效地多次执行该语句而不用预编译,直接使用数据库的缓冲区,提高数据库访问的效率。

【preparedStatement】

  • 原理

上图为mysql查询执行过程,包括:

1、传输sql给数据库

2、服务器先检查查询缓存,如果命中,吉利返回结果;否则进入下一阶段

3、数据库验证&解析sql

4、计算执行计划

5、根据执行计划调用存储引擎的api执行查询

6、返回数据

        在上面步骤中,第4步非常耗时。为了提高性能,数据库会缓存执行语句及其执行计划。但是,sql语句本身为key,执行计划为value,sql语句中只要有一个字节不一样就不能命中缓存。

        因此,有了preparedStatement,能够提高在只有参数不一样的sql的缓存命中率。

        preparedStatement创建的时候,会将参数化的语句发给数据库,进行语法检测和执行计划计算。sql语句被预编译并且存储在preparedStatement对象中,下次执行相同的sql语句时,数据库不会再进行预编译,而是直接使用数据库的缓冲区,提高数据库的访问效率。

 

  • 作用

        1、代码可读性&可维护性

        2、在批量执行时提高cache命中率,提高性能

        3、提高安全性,防止sql注入

 

  • 适用场景

        有大量的sql结构相同,只有数值不同的sql请求时。或者处于对写入的sql的安全考虑。

 

【解决】

        由于执行的sql是由上游传过来的,并不能确保sql的结构相同,所以其实使用preparedStatement并不能提高缓存的命中率,只能做防止sql注入的功能;并且,如果每次sql前都使用preparedStatement,还为每次sql执行增加了一次网络交互,得不偿失。因此,解决方法就是没有使用preparedStatement进行预编译,而是直接使用Statement执行sql语句。

posted @ 2017-02-12 20:27  小树桩的朋友  阅读(2735)  评论(0编辑  收藏