database/sql: Stmt的使用以及坑

前言

众所周知，golang操作数据库，是通过database/sql包，以及第三方的实现了database/sql/driver接口的数据库驱动包来共同完成的。

其中database/sql/driver中的接口Conn和Stmt，官方交给第三方实现驱动，并且是协程不安全的。官方实现的database/sql包中的DB和Stmt是协程安全的，因为内部实现是连接池。

如何使用

刚开始接触database/sql包，并且网上的很多使用例子，都类似下面这种方式：

db,err := sql.Open("mysql","test:test@tcp(127.0.0.1:3306)/abwork?charset=utf8")
rows,err := db.Query("select id,name,age from test limit 0,5")
res, err := db.Exec("INSERT test SET name=?,age =?", "xiaowei", 18)

或者下面这种：

stmt,err := db.Prepare("insert into test(name,age)values(?,?)")
defer stmt.Close()
stmt.Exec("张三",20)

其实仔细看database/sql的源码实现，上面两个例子的方式都是一样的。 首先看一下第一种方式的使用，以下是golang1.3版本database/sql包sql.go中，type DB的exec方法，第899-906行的实现：

dc.Lock()
si, err := dc.ci.Prepare(query)
dc.Unlock()
if err != nil {
    return nil, err
}
defer withLock(dc, func() { si.Close() })
return resultFromStatement(driverStmt{dc, si}, args...)

可以看到，dc.ci.Prepare(query)这句，会先创建一个预处理语句，然后调用resultFromStatement方法执行sql操作。而Query方法，最终的实现也是一样。

我们再看第二种方式，先调用Papare方法生成一个Stmt，在Prepare中，会调用dc.prepareLocked(query)，请看sql.go中844行，而最终dc.prepareLocked(query)这个方法，还是会调用dc.ci.Prepare(query)创建预处理语句，请看251行。接下来，就是调用Stmt的Exec或者Query方法，而最终这两个方法还是会调用resultFromStatement方法去执行。

那么两种方式的相通之处，都是会预处理，不同的是使用db.Prepare会额外的创建Stmt，由Stmt实例在去处理具体的数据库操作。

那么大家也看出来了，如果不是批量的操作，是没必要使用db.Papare方法的，否则即多了Stmt创建和关闭的性能开销，又多写了两行代码，有点得不偿失。如果是批量的操作，那么毋庸置疑，肯定是db.Papare拿到Stmt，再由Stmt去执行sql，这样保证批量操作只进行一次预处理。

发现的问题

按照上文说的，在实际使用过程中，也发现了一些问题。

DB默认的最大open连接数是0，而最大空闲数是defaultMaxIdleConns = 2。在数据库操作很频繁的实际使用场景中，尤其是一波又一波访问高峰不间断来临的时候，数据库性能会不断的消耗在连接的创建和销毁上，这是很拖累数据和和机器的，所以我们根据mysql的max_user_connections参数，设置合理的值之后，这种现象很快的稳定下来。

而连接稳定，在使用db.Exec和Query时，每次都会向数据库创建预处理语句。虽说在批量操作时使用db.Papare拿到Stmt然后执行批量操作更好，但我们的实际业务场景中，涉及到批量操作的地方很少（可以说没有），那么问题来了。。。不是挖掘机哪家强，而是该如何合理的使用Stmt！

众所周知，我们的手游《暖暖环游世界》非常适合广大妹纸和宅男朋友们暖身解闷，ios版请点击此处下载，android版请点击此处下载。所以数据流量也非常的大。在大推期间，访问量不断的刷新最高值，mysql的压力爆表，经过分析，除了刚开始最大open连接数和最大空闲连接数设置不当，导致mysql连接方面极大的性能开销外，还有就是这第二个问题，不断的创建预处理语句又关闭销毁，也是性能杀手。（暖暖推广期间毛老师和丁丁刘做了大量的mysql优化，详情请看《mysql在高内存、IO利用率上的几个优化点》）

虽说批量操作不多，但我们所有的操作都是相同的sql语句，没有在用户使用过程中，根据用户不同行为，生成不同sql语句的业务场景。

所以我们决定改变使用方式，直接在程序初始化的时候，通过db.Papare创建Stmt，在运行期间，总是由预先创建好的Stmt去执行sql，这样就可以达到预处理语句复用的效果，应该能够很大的减少mysql预处理语句上的性能开销。

而且Stmt的源码实现，也是使用的DB连接池，管理着一批已经创建预处理语句的连接，具体就是下面的struct：

type connStmt struct {
    dc *driverConn
    si driver.Stmt
}

可以看见，每一个连接driverConn对应着一个预处理driver.Stmt。

而Stmt在执行Exec和Query等方法时，会先执行connStmt方法（注意，这是及其重要的一个方法）：

func (s *Stmt) connStmt() (ci *driverConn, releaseConn func(error), si driver.Stmt, err error)

拿到type connStmt struct中的driverConn和driver.Stmt，再由resultFromStatement方法去做具体的数据库操作。

Stmt的坑

上文所说的方法，经过实际使用，确实会降低mysql在预处理语句上的性能开销，但问题来了，Stmt的坑是啥呢。。。

回到connStmt方法，Stmt通过该方法获得driverConn和driver.Stmt，那么具体的逻辑是咋样的呢，请看以下源码分析：

func (s *Stmt) connStmt() (ci *driverConn, releaseConn func(error), si driver.Stmt, err error) {
    if err = s.stickyErr; err != nil {
        return
    }
    s.mu.Lock()
    if s.closed {
        s.mu.Unlock()
        err = errors.New("sql: statement is closed")
        return
    }

    // In a transaction, we always use the connection that the
    // transaction was created on.
    if s.tx != nil { // 这里如果有事务，则通过事务tx获取连接（不是本文重点）
        s.mu.Unlock()
        ci, err = s.tx.grabConn() // blocks, waiting for the connection.
        if err != nil {
            return
        }
        releaseConn = func(error) {}
        return ci, releaseConn, s.txsi.si, nil
    }
        // 重点开始
    var cs connStmt
    match := false
    for i := 0; i < len(s.css); i++ { // 如果你翻开Stmt源码，就会发现css是type connStmt的一个slice：css []connStmt
        v := s.css[i]
        _, err := s.db.connIfFree(v.dc) // Stmt首先会从css中找到当前空闲的连接
        if err == nil {
            match = true // 如果有空闲连接，将match设为true
            cs = v
            break
        }
        if err == errConnClosed { // 顺便还处理下关闭的连接，从css中remove掉
            // Lazily remove dead conn from our freelist.
            s.css[i] = s.css[len(s.css)-1]
            s.css = s.css[:len(s.css)-1]
            i--
        }

    }
    s.mu.Unlock()

    // Make a new conn if all are busy.
    // TODO(bradfitz): or wait for one? make configurable later?
    if !match { // 注意match，如果css中没有空闲连接
        dc, err := s.db.conn() // 就会从db的连接池中取
        if err != nil {
            return nil, nil, nil, err
        }
        dc.Lock()
        si, err := dc.prepareLocked(s.query) // 取到连接，这里加把锁去创建预处理语句（注意，这里是重点！！！）
        dc.Unlock()
        if err != nil {
            s.db.putConn(dc, err) // 将连接放到池中
            return nil, nil, nil, err
        }
        s.mu.Lock()
        cs = connStmt{dc, si} // 创建connStmt
        s.css = append(s.css, cs) // 将connStmt加到css
        s.mu.Unlock()
    }

    conn := cs.dc
    return conn, conn.releaseConn, cs.si, nil
}

看完上面的源码，相信大家还是没太明白问题出在哪。

我们先明确一件事，那就是Stmt中css里的driveConn，和db连接池中的conn，有可能数量不同，状态不同，有可能db连接池中被干掉的连接，也有可能还在css中。

更有可能，s.db.connIfFree(v.dc)时还不是空闲的连接，在执行到if !match 时，已经是了，然后被dc, err := s.db.conn()从连接池中取出，接下来就到了源码分析中，重点的那句，那里不管连接有木有处于css中，都会创建一个新的connStmt，然后再存入css中。这么一来，导致的问题就是不断有driverConn创建新的driver.Stmt，然后存入css却不销毁。同一个driverConn，有可能会对应着成千上万不同的driver.Stmt。

而我们之所以会发现这个坑，就是在观察mysql状态时，发现Prepared_stmt_count的值，会随着时间的推移，不断的上涨，直到达到max_prepared_stmt_count的最大值，然后代码报错。而这个时候，我们只能通过杀掉进程重启，使Prepared_stmt_count归0，来缓解这个问题。

后续

发现这个问题后，我们分别在：
https://code.google.com/p/go/issues/detail?id=8376
https://groups.google.com/forum/#!topic/golang-nuts/bbFX0qQvsB0
这两个地方提出了问题，然后也得到了热心朋友的支持。

ma…@joh.to(找不到对方的真实邮箱，有问题的朋友可以在golang-nuts，通过回复发送邮件联系)帮我们在：
https://codereview.appspot.com/116930043
给官方提了codereview和临时解决方案，遗憾的是他的解决方案依然存在这个问题。

最终，database/sql的作者，Brad Fitzpatrick大神亲自回复，明确了问题，然后在：
https://code.google.com/p/go/source/detail?r=fdb52a28028a
里做了解决，并且在go1.4中会得到修复。

以下是go1.4beta1中，Brad Fitzpatrick大神的修改，可以对比下旧的实现：

func (s *Stmt) connStmt() (ci *driverConn, releaseConn func(error), si driver.Stmt, err error) {
    if err = s.stickyErr; err != nil {
        return
    }
    s.mu.Lock()
    if s.closed {
        s.mu.Unlock()
        err = errors.New("sql: statement is closed")
        return
    }

    // In a transaction, we always use the connection that the
    // transaction was created on.
    if s.tx != nil {
        s.mu.Unlock()
        ci, err = s.tx.grabConn() // blocks, waiting for the connection.
        if err != nil {
            return
        }
        releaseConn = func(error) {}
        return ci, releaseConn, s.txsi.si, nil
    }

    for i := 0; i < len(s.css); i++ {
        v := s.css[i]
        _, err := s.db.connIfFree(v.dc)
        if err == nil {
            s.mu.Unlock()
            return v.dc, v.dc.releaseConn, v.si, nil
        }
        if err == errConnClosed {
            // Lazily remove dead conn from our freelist.
            s.css[i] = s.css[len(s.css)-1]
            s.css = s.css[:len(s.css)-1]
            i--
        }

    }
    s.mu.Unlock()

    // If all connections are busy, either wait for one to become available (if
    // we've already hit the maximum number of open connections) or create a
    // new one.
    //
    // TODO(bradfitz): or always wait for one? make configurable later?
    dc, err := s.db.conn()
    if err != nil {
        return nil, nil, nil, err
    }

    // Do another pass over the list to see whether this statement has
    // already been prepared on the connection assigned to us.
    s.mu.Lock()
    for _, v := range s.css { // 这里又做了一次css的检查，看连接是否已经预处理过了
        if v.dc == dc {
            s.mu.Unlock()
            return dc, dc.releaseConn, v.si, nil
        }
    }
    s.mu.Unlock()

    // No luck; we need to prepare the statement on this connection
    dc.Lock()
    si, err = dc.prepareLocked(s.query)
    dc.Unlock()
    if err != nil {
        s.db.putConn(dc, err)
        return nil, nil, nil, err
    }
    s.mu.Lock()
    cs := connStmt{dc, si}
    s.css = append(s.css, cs)
    s.mu.Unlock()

    return dc, dc.releaseConn, si, nil
}