亲身经历的痛--database/sql: Stmt的使用以及坑
亲身经历的痛--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
}