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}