beego mysql Can‘t create more than max_prepared_stmt_count 解决方案

问题出现

项目上线之初，一切表现正常。运行一段时间后,报一项错误：

Error 1461: Can’t create more than max_prepared_stmt_count statements (current value: 16382)

一时间，让人束手无策。只有通过更改预处理值上限，临时解决(线上问题，需要快速解决啊)。

set global max_prepared_stmt_count = 1048576

将预处理值改大，临时把线上问题解决(但是,不久后将会再次出现，所以真的很着急!)

预处理

要彻底解决问题，还得深入理解预处理机制。预处理(Prepare SQL),又称变量绑定，在oracle数据库和Mysql数据库中都支持这个功能。下面以Mysql为例，讲解一下Prepare SQL产生的原因。

SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词，语法分析解析SQL语句是否符合sql语法，并得到一棵语法树（Lex）。对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是相同的。而同一SQL随着查询数据的变化，多次查询执行时间可能不同，但硬解析的时间是不变的。对于sql执行时间较短，sql硬解析的时间占总执行时间的比率越高。
Prepare的出现就是为了优化硬解析的问题。Prepare在服务器端的执行过程如下

1） Prepare 接收客户端带”?”的sql, 硬解析得到语法树(stmt->Lex), 缓存在线程所在的preparestatement cache中。此cache是一个HASH MAP. Key为stmt->id. 然后返回客户端stmt->id等信息。

2） Execute 接收客户端stmt->id和参数等信息。注意这里客户端不需要再发sql过来。服务器根据stmt->id在preparestatement cache中查找得到硬解析后的stmt, 并设置参数，就可以继续后面的优化和执行了。

Prepare在execute阶段可以节省硬解析的时间。如果sql只执行一次，且以prepare的方式执行，那么sql执行需两次与服务器交互（Prepare和execute）, 而以普通（非prepare）方式，只需要一次交互。这样使用prepare带来额外的网络开销，可能得不偿失。我们再来看同一sql执行多次的情况，比如以prepare方式执行10次，那么只需要一次硬解析。这时候 额外的网络开销就显得微乎其微了。因此prepare适用于频繁执行的SQL。

压测时通过perf 得到的结果，硬解析相关的函数比重都比较靠前（MYSQLparse 4.93%, lex_one_token 1.79%, lex_start 1.12%）总共接近8%。因此，服务器使用prepare是可以带来较多的性能提升的。

总结一下，预处理的优点：

1. 减少服务器负荷
2. 提供服务器响应速度
3. 绑定变量,防止SQL注入

问题怎么出现的

要知道问题怎么出现的，看beego源代码是最好的方式。说干就干，来我们一起来看看源码。
获取stmt 对象go代码(本地beego版本:v1.12.0)

func (d *DB) getStmt(query string) (*sql.Stmt, error) {
	d.RLock()
	if stmt, ok := d.stmts[query]; ok {
		d.RUnlock()
		return stmt, nil
	}
	d.RUnlock()

	stmt, err := d.Prepare(query)
	if err != nil {
		return nil, err
	}
	d.Lock()
	d.stmts[query] = stmt
	d.Unlock()
	return stmt, nil
}

这段看似平常的代码,有两个问题。

1. 没有设置stmt对象上限,会导致stmt对象无限增长
2. 在第二个锁后,没有进行double-check,会导致stmt泄露

已上两个问题都会导致mysql 报Can’t create more than max_prepared_stmt_count。（只要系统运行的足够久,一定会出现的）

解决方案

1. 不对stmt做缓存,使用stmt完成后，将stmt关闭。这种方式确实能解决这个问题.但是降低了处理数据 的效率。这样修改后,每执行一条sql语句，需要两次通信，这违背了预处理机制的初衷。
2. 将beego版本升级到v1.12.2。我们是幸运的,beego官方已经修复了这个bug。通过LRU算法，控制了stmt数量。double-ckeck在最新版（目前是v1.12.2）也补上了。

或许你有个疑问,为什么beego这个bug没有在更早一些版本出现。原因是，在v1.12.0以前的版本，beego orm没有采用预处理机制,而是采用直接执行sql语句的方式,当然不会出问题了。这个bug，只出现在beego v1.12.0,v1.12.1这两个版本中。如果你不幸，正好采用这两个版本之一的话，请升级到最新版本吧