MySQL 预编译也有深坑

date

Jun 13, 2021

slug

mysql-prepare-statement

status

Published

简单易懂的预编译

MySQL 的 SQL 执行，有大致如下流程：

作为被频繁访问的数据库软件，自然可以通过一些手段来提升效率。比如 cache

实际场景中，我们也可以发现，有很多语句，只有 where 子语句的条件的值产生了改变，而其余部分估计到项目下线都不会改变。

这个时候大量重复的字符串让数据库重新编译解析那不就做了很多无用功，所以是不是可以把这个变量扣掉，保留一份模板，每次仅接收变量，不再重新解析整个语句了。

比如这样：

那么数据库就不需要做很多工作，直接执行得结果，岂不美哉。

容易使用的预编译

在 golang 官方的 sql 库中，可以看到，Query 方法会根据是否传参来决定是否使用 prepare 方法向服务器发送请求，那么我们使用 prepare 就非常简单了，copy paste 官方例子即可；当拿到结果的时候，会自动关闭这条预编译，当然也可以直接调用 rows.Close() 来手动关闭

sql - The Go Programming Language

ErrConnDone is returned by any operation that is performed on a connection that has already been returned to the connection pool. var ErrConnDone =errors.New("sql: connection is already closed") ErrNoRows is returned by Scan when QueryRow doesn't return a row. In such a case, QueryRow returns a placeholder *Row value that defers this error until a Scan.

https://golang.org/pkg/database/sql/#DB.Query

然后线上就崩了

本地开发时开开心心地觉得没啥问题，然后推到生产环境拿真实数据灰度，直接爆炸。

一个劲地 ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 16382)

其余的 SQL 请求直接瘫痪，数据库撂挑子不干了！

16382 是默认值，一般是不用去改的，而且这个值也挺大的了，把这个打满，那说明我们代码肯定有 bug

MySQL :: MySQL 5.6 Reference Manual :: 5.1.7 Server System Variables

Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_prepared_stmt_count

首先是考虑我们使用的 SQL 库会不会自动关闭，虽然我们自己封装了一层，但是底层就是 golang 官方 SQL 库，能确认会自动关闭。同时看到 close 的数量也很大，但是 prepare sql 的量太大了。

这个时候就想到，我们代码中也有直接执行 SQL 语句的，我们把变量写在了字符串里，原计划就是打算当 Immediate Statements 执行。莫非。

然后去看了我们封装的代码，好家伙，也是直接调用的 prepare，带不带参数没有区别，都是预编译了。

向数据库发了一堆这个，并告诉数据库这是预编译啊，你给我预编译。这玩意数据库一看，你这让我把这些当模板，但是接下来一个都用不了啊，没变量啊。虽然数据库不是很满意，但是活还是干了，直到 max_prepared_stmt_count 达到阈值 16382，数据库摆上“任务已满，恕不接客”的牌子，拒绝了我们接下来的请求。

那可能有的小伙伴还可能遇到，我明明也有变量啊，为什么也会报错。比如下面，id 变动范围很大，每个不同 id 都会生成不同的模板，流量低还好，只要及时关闭，一般也不会报错，但是高并发情况下，本应该预编译大显身手的地方，却被难以命中的预编译模板塞得生活不能自理，那除了报错，别无他法。

预编译还是得多测测

那么思考一下，怎么避免这个问题呢：

先看好自己怎么向数据库服务器发送请求的，是否做了预编译的优化；

压测，压测还是得压测；

改默认值可以快速止损，事后一定要将 bug 找到；