MySQL又双叒崩了——记beego的stmt优化-技术圈

问题抛出

近来 beego 收到用户反馈，有时候 MySQL 数据库会出现：

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

而且这个只会出现在 1.12.x 的版本，早期的 1.11.x 版本并不会出现。

这个错误是因为 MySQL 缓存的 Prepare Statement 超出了上限，无法再创建新的 Prepare Statement 了。

而 beego 内部的所有的SQL查询，基本上都是通过 Prepare Statement 来执行的，这主要是因为 Prepare Statement 可以性能和安全方面的优势：

性能优势： Prepare Statement 会被预编译，执行计划也会被缓存；
安全：因为 Prepare Statement 在执行的时候是绑定参数的，也就是它不会把参数视为指令的一部分。这可以防范大多数的 SQL 注入攻击。

beego 所有的 SQL 执行都是通过 Prepare Statement 来实现的。

但是问题来了，为什么会创建这么多的 Prepare Statement 呢？

问题分析

按照我们的分析，如果使用 beego 的 orm 的话，是不会生成太多的 Prepare Statement 的。我们假设说一个模型增删改查加起来有十个 Prepare Statement 语句，那么 100 个模型也才 1000 个。

所以我们的分析比较可能的原因是：

beego 内部每次查询都创建了 Prepare Statement ，没有复用，也没有关闭；
用户绕开了标准`orm`，使用了我们提供的执行原始 SQL 的功能；
用户部署了非常多的实例 beego 实例；

第三条是比较难处理的，只能是每次创建 Prepare Statement 之后都关闭，不考虑复用任何的`Prepare Statement`。而按照我们的计算，这也得有几十个实例共享一个 MySQL 实例才有可能导致 MySQL 出现这种问题。

通过跟用户的交流，确定了用户的确使用到了我们执行原始 SQL 的功能。而且他们犯了一个很严重的错误：即直接拼接 SQL 参数，而不是通过绑定参数来执行。

我举个例子。比如说我们想要查询一个用户，一般的 SQL 都是：

select * from User where id = ?

而后通过参数绑定，将用户的 id 绑定。而这个用户则是直接使用字符串拼接，将 SQL 拼接成了：

select * from User where id = 1

这很显然，每次来一个用户，在 beego 都会创建一个 Prepare Statement 并且缓存起来。当然用户的真实例子要复杂多了，但是原理是一致的。

这里提到，我们 beego 是会缓存创建出来的 Prepare Statement 。虽然用户用法不太对，但是我们未能考虑周详也是事实。毕竟作为基础框架，你不兜底谁来兜底？

我们根据提交记录，很快定位到了那一段代码：

//git hash:cc0eacbe023b95f74c240b35419c14722df45041
//orm/db_alias.go
type DB struct {
*sync.RWMutex
    DB    *sql.DB
//此处没有对 stmts 的 size进行限制
    stmts map[string]*sql.Stmt
}

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

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

问题就出在这 getStmt 方法之内。

乍一看，这代码看起来毫无破绽。但是实际上，它有两个问题：

stmts 变量是简单的 map 结构，并不存在数量限制；
在 17-23 行之间有并发问题。

一般人可能会觉得，怎么会有并发问题呢？往 map 里面塞进去东西的确没有并发问题。问题出在 d.Prepare(query) 这一句。

当多个 goroutine 发现 stmts 里面并没有缓存当前 query 的时候，就会同时创建出来新的`stmt`，但是最终都会试图放进去 map 里面，加锁只会让他们排好队一个个放，但是后面的会覆盖前面的。而被覆盖的，却没有被关闭掉。

解决方案

从前面分析，我们实际上要解决两个问题：

设置缓存的`Prepare Statement`的数量的上限；
在缓存不命中的时候，有且只有一个 Prepare Statement 被创建出来；

设置上限

第一个问题，要解决很简单，比如说我们维护一个缓存上限的值，而后再往 map 里面塞值之前先判断一下有没有超出上限。

这种方案的缺点就是谁先被缓存了，就永远占了位置，后面的 SQL 将无法享受到缓存`Prepare Statement`的优势。

那么很显然，我们可以考虑是用 LRU 来解决上限的问题。很显然，根据程序运行的特征，LRU 缓存局部热点更加契合局部性原理。我们只需要在 LRU 淘汰一个 Prepare Statement 的时候，关闭它就可以。

但是难点在于，这个被淘汰的 Prepare Statement 可能还在被使用中。毕竟 golang 并没有类似于 Java 软引用之类的东西。

所以我们只能考虑说维持一个计数，如果有人使用，就 +1，使用完了就 -1。

因此我们使用了 Decorator 设计模式，封装了一下 Stmt ：

type stmtDecorator struct {
    //借助 waitGroup 进行引用计数
    wg sync.WaitGroup
    lastUse int64
    stmt *sql.Stmt
}

func (s *stmtDecorator) acquire() {
    //返回描述符前，执行引用计数 +1
    s.wg.Add(1)
    s.lastUse = time.Now().Unix()
}

func (s *stmtDecorator) release() {
    //调用者完成操作后，释放引用计数
    s.wg.Done()
}

当我们在 LRU 淘汰的时候，利用 WaitGroup 的特性来等待所有的使用者释放 stmt ：

func newStmtDecoratorLruWithEvict() *lru.Cache {
    cache, _ := lru.NewWithEvict(1000, func(key interface{}, value interface{}) {
        value.(*stmtDecorator).destroy()
    })
    return cache
}

func (s *stmtDecorator) destroy() {
    go func() {
        //等待所有资源释放，进行stmt关闭
        s.wg.Wait()
        _ = s.stmt.Close()
    }()
}

double-check

之前提到的并发问题，其实根源在于没有正确使用 double-check 。当我们加了写锁以后，需要进一步判断，有没有因为并发，而其它的 goroutine 刚才先获得了写锁，创建出来了 Prepare Statement 。

最终经过修改的 getStmt 如下：

type DB struct {
    *sync.RWMutex
    DB             *sql.DB
    stmtDecorators *lru.Cache
}
func (d *DB) getStmtDecorator(query string) (*stmtDecorator, error) {
    d.RLock()
    c, ok := d.stmtDecorators.Get(query)
    if ok {
        // 计数 + 1.
        // 这一步必须在这个方法内完成。
        // 否则可能在LRU淘汰之后，执行Close之前，用户误+1，而stmt又被随后Close了
        c.(*stmtDecorator).acquire()
        d.RUnlock()
        return c.(*stmtDecorator), nil
    }
    d.RUnlock()

    d.Lock()
    //double check
    // 再一次检测，看有没有别的goroutine刚才先拿到了写锁，并且创建成功了
    c, ok = d.stmtDecorators.Get(query)
    if ok {
        c.(*stmtDecorator).acquire()
        d.Unlock()
        return c.(*stmtDecorator), nil
    }

    stmt, err := d.Prepare(query)
    if err != nil {
        d.Unlock()
        return nil, err
    }
    sd := newStmtDecorator(stmt)
    sd.acquire()
    d.stmtDecorators.Add(query, sd)
    d.Unlock()

    return sd, nil
}

总结

经过我们前面的分析，可以看到，这个问题的根源在于我们设计这个`Prepare Statement`的时候，并没有做好兜底的准备，从而导致了用户 MySQL的崩溃。

另外一方面，我们也发现，在 golang 里面，类似于这种资源的关闭都不是很好处理，至少代码不会简洁。当某一个资源被暴露出去之后，在我们框架层面上要释放资源的时候，最重要的问题就是，这个东西到底还有没有人用。

所以我们只能依赖于通过使用一种计数的形式，来迫使使用者加减计数来暴露使用情况。它带来的问题就是，用户可能会遗忘，无论是遗忘增加计数，还是遗忘减少计数，最终都会出问题。这种用法体验并不太好，不知道有没有人有更好的方案。