Golang Mysql笔记(三)--- Prepared剖析


原文链接: Golang Mysql笔记(三)--- Prepared剖析

prepare

前面我们已经学习了sql的基本curd操作。总体而言,有两类操作Query和Exec。前者返回数据库记录,后者返回数据库影响或插入相关的结果。上面两种操作,多数是针对单次操作的查询。如果需要批量插入一堆数据,就可以使用Prepared语句。golang处理prepared语句有其独特的行为,了解其底层的实现,对于用好它十分重要。
查询

我们可以使用Query方式查询记录,Query函数提供了两种选择,第一种情况下参数是拼接好的sql,另外一种情况,第一参数是带有占位符的sql,第二个参数为sql的实际参数。

rows, err := db.Query("SELECT * FROM user WHERE gid = 1") // plaintext

rows, err := db.Query("SELECT * FROM user WHERE gid = ?", 1) // prepared

上面两种方式都能获取数据,那么他们的底层实现是一样的么?实际上,上面两种方式的底层通信不完全一样。一种你是plaintext方式,另外一种是prepared方式。

prepared

所谓prepared,即带有占位符的sql语句,客户端将该语句和参数发给mysql服务器。mysql服务器编译成一个prepared语句,这个语句可以根据不同的参数多次调用。prepared语句执行的方式如下:

准备prepare语句
执行prepared语句和参数
关闭prepared语句

之所以会出现prepare语句方式,主要因为这样有下面的两个好处:

避免通过引号组装拼接sql语句。避免sql注入带来的安全风险
可以多次执行的sql语句。

单纯的看prepared语句发好处,会下意识的觉得既然如此,都使用prepared语句查询不就好了么?其实不然。关于prepared语句注意事项,稍后再讨论。
golang的pliantext和prepare查询方式

现在我们再回顾上面调用Qeury函数的两个操作。对于第一个操作,执行pliantext的sql语句。先看db.Query方法:

// Query executes a query that returns rows, typically a SELECT.
// The args are for any placeholder parameters in the query.
func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
 var rows *Rows
 var err error
 for i := 0; i < maxBadConnRetries; i++ {
    rows, err = db.query(query, args, cachedOrNewConn)    // 查询
    if err != driver.ErrBadConn {
     break
    }
 }
 if err == driver.ErrBadConn {
  return db.query(query, args, alwaysNewConn)
 }
 return rows, err
}

Query方法我们很熟悉了,它的内部调用了db.query方法,并且根据连接重连的状况选择是cachedOrNewConn模式还是alwaysNewConn模式。前者会从返回一个cached连接或者等待一个可用连接,甚至也可能建立一个新的连接;后者表示打开连接时的策略为每次建立一个新的连接。这就是签名所说的retry 10次连接。

func (db *DB) query(query string, args []interface{}, strategy connReuseStrategy) (*Rows, error) {
 ci, err := db.conn(strategy)
 if err != nil {
  return nil, err
 }
 return db.queryConn(ci, ci.releaseConn, query, args)
}

query方法逻辑很简单,通过db.conn方法返回一个新创建或者缓存的 空闲连接。driverConn。随机调用queryConn方法。

// queryConn executes a query on the given connection.
// The connection gets released by the releaseConn function.
func (db *DB) queryConn(dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
 // 判断驱动是否实现了Queryer
 if queryer, ok := dc.ci.(driver.Queryer); ok {
  dargs, err := driverArgs(nil, args)
  if err != nil {
   releaseConn(err)
   return nil, err
  }
  dc.Lock()
  rowsi, err := queryer.Query(query, dargs)  // 调用驱动的查询方法  connection.go 第305行
  dc.Unlock()
  if err != driver.ErrSkip {  // 不带参数的返回
   if err != nil {
    releaseConn(err)
    return nil, err
   }
   // Note: ownership of dc passes to the *Rows, to be freed
   // with releaseConn.
   rows := &Rows{
    dc:          dc,
    releaseConn: releaseConn,
    rowsi:       rowsi,
   }
   return rows, nil
  }
 }
 dc.Lock()
 si, err := dc.ci.Prepare(query)  // 带参数的返回,创建prepare对象
 dc.Unlock()
 if err != nil {
  releaseConn(err)
  return nil, err
 }
 ds := driverStmt{dc, si}
 rowsi, err := rowsiFromStatement(ds, args...)   // 执行语句
 if err != nil {
  dc.Lock()
  si.Close()
  dc.Unlock()
  releaseConn(err)
  return nil, err
 }
 // Note: ownership of ci passes to the *Rows, to be freed
 // with releaseConn.
 rows := &Rows{
  dc:          dc,
  releaseConn: releaseConn,
  rowsi:       rowsi,
  closeStmt:   si,
 }
 return rows, nil
}

queryConn函数内容比较多。先判断驱动是否实现了Queryer,如果实现了即调用其Query方法。方法会针对sql查询语句做查询。例如mysql的驱动如下,connection.go 第305行左右,即:


func (mc *mysqlConn) Query(query string, args []driver.Value) (driver.Rows, error) {
 if mc.netConn == nil {
  errLog.Print(ErrInvalidConn)
  return nil, driver.ErrBadConn
 }
 if len(args) != 0 {
  if !mc.cfg.InterpolateParams {
   return nil, driver.ErrSkip
  }
  // try client-side prepare to reduce roundtrip
  prepared, err := mc.interpolateParams(query, args)
  if err != nil {
   return nil, err
  }
  query = prepared
  args = nil
 }
 // Send command
 err := mc.writeCommandPacketStr(comQuery, query)
 if err == nil {
  // Read Result
  var resLen int
  resLen, err = mc.readResultSetHeaderPacket()
  if err == nil {
   rows := new(textRows)
   rows.mc = mc
   if resLen == 0 {
    // no columns, no more data
    return emptyRows{}, nil
   }
   // Columns
   rows.columns, err = mc.readColumns(resLen)
   return rows, err
  }
 }
 return nil, err
}

Query先检查参数是否为0,然后调用writeCommandPacketStr方法执行sql并通过readResultSetHeaderPacket读取数据库服务返回的结果。
如果参数不为0,会先判断是否是prepared语句。这里会返回一个driver.ErrSkip错误。把函数执行权再返回到queryConn函数中。然后再调用si, err := dc.ci.Prepare(query)创建Stmt对象,接下来调用rowsiFromStatement执行查询:

func rowsiFromStatement(ds driverStmt, args ...interface{}) (driver.Rows, error) {
 ds.Lock()
 want := ds.si.NumInput()
 ds.Unlock()
 // -1 means the driver doesn't know how to count the number of
 // placeholders, so we won't sanity check input here and instead let the
 // driver deal with errors.
 if want != -1 && len(args) != want {
  return nil, fmt.Errorf("sql: statement expects %d inputs; got %d", want, len(args))
 }
 dargs, err := driverArgs(&ds, args)
 if err != nil {
  return nil, err
 }
 ds.Lock()
 rowsi, err := ds.si.Query(dargs)
 ds.Unlock()
 if err != nil {
  return nil, err
 }
 return rowsi, nil
}

rowsiFromStatement方法会调用驱动的ds.si.Query(dargs)方法,执行最后的查询。大概再statement.go的第84行

func (stmt *mysqlStmt) Query(args []driver.Value) (driver.Rows, error) {
 if stmt.mc.netConn == nil {
  errLog.Print(ErrInvalidConn)
  return nil, driver.ErrBadConn
 }
 // Send command
 err := stmt.writeExecutePacket(args)
 if err != nil {
  return nil, err
 }
 mc := stmt.mc
 // Read Result
 resLen, err := mc.readResultSetHeaderPacket()
 if err != nil {
  return nil, err
 }
 rows := new(binaryRows)
 if resLen > 0 {
  rows.mc = mc
  // Columns
  // If not cached, read them and cache them
  if stmt.columns == nil {
   rows.columns, err = mc.readColumns(resLen)
   stmt.columns = rows.columns
  } else {
   rows.columns = stmt.columns
   err = mc.readUntilEOF()
  }
 }
 return rows, err
}

调用 stmt和参数执行sql查询。查询完毕之后,返回到queryConn方法中,使用releaseConn释放查询的数据库连接。
自定义prepare 查询

从query查询可以看到,对于占位符的prepare语句,go内部通过的dc.ci.Prepare(query)会自动创建一个 stmt对象。其实我们也可以自定义stmt语句,使用方式如下:

stmt, err := db.Prepare("SELECT * FROM user WHERE gid = ?")
if err != nil {
 log.Fatalln(err)
}
defer stmt.Close()
rows, err :=  stmt.Query(1)
if err != nil{
 log.Fatalln(err)
}

即通过Prepare方法创建一个stmt对象,然后执行stmt对象的Query(Exec)方法得到sql.Rows结果集。最后关闭stmt.Close。这个过程就和之前所说的prepare三步骤匹配了。

创建stmt的preprea方式是golang的一个设计,其目的是Prepare once, execute many times。为了批量执行sql语句。但是通常会造成所谓的三次网络请求( three network round-trips)。即preparing executing和closing三次请求。

对于大多数数据库,prepread的过程都是,先发送一个带占位符的sql语句到服务器,服务器返回一个statement id,然后再把这个id和参数发送给服务器执行,最后再发送关闭statement命令。

golang的实现了连接池,处理prepare方式也需要特别注意。调用Prepare方法返回stmt的时候,golang会在某个空闲的连接上进行prepare语句,然后就把连接释放回到连接池,可是golang会记住这个连接,当需要执行参数的时候,就再次找到之前记住的连接进行执行,等到stmt.Close调用的时候,再释放该连接。

在执行参数的时候,如果记住的连接正处于忙碌阶段,此时golang将会从新选一个新的空闲连接进行prepare(re-prepare)。当然,即使是重新reprepare,同样也会遇到刚才的问题。那么将会一而再再而三的进行reprepare。直到找到空闲连接进行查询的时候。

这种情况将会导致leak连接的情况,尤其是再高并发的情景。将会导致大量的prepare过程。因此使用stmt的情况需要仔细考虑应用场景,通常在应用程序中。多次执行同一个sql语句的情况并不多,因此减少prepare语句的使用。

之前有一个疑问,是不是所有sql语句都不能带占位符,因为这是prepare语句。只要看了一遍database/sql和驱动的源码才恍然大悟,对于query(prepare, args)的方式,正如我们前面所分析的,database/sql会使用ds.si.Query(dargs)创建stmt的,然后就立即执行prepare和参数,最后关闭stmt。整个过程都是同一个连接上完成,因此不存在reprepare的情况。当然也无法使用所谓的创建一次,执行多次的目。

对于prepare的使用方式,基于其好处和缺点,我们将会再后面的最佳实践再讨论。目前需要注意的大致就是:

单次查询不需要使用prepared,每次使用stmt语句都是三次网络请求次数,prepared execute close

不要循环中创建prepare语句

注意关闭 stmt

尽管会有reprepare过程,这些操作依然是database/sql帮我们所做的,与连接retry10次一样,开发者无需担心。

对于Qeruy操作如此,同理Exec操作也一样。
总结

目前我们学习database/sql提供两类查询操作,Query和Exec方法。他们都可以使用plaintext和preprea方式查询。对于后者,可以有效的避免数据库注入。而prepare方式又可以有显示的声明stmt对象,也有隐藏的方式。显示的创建stmt会有3次网络请求,创建->执行->关闭,再批量操作可以考虑这种做法,另外一种方式创建prepare后就执行,因此不会因为reprepare导致高并发下的leak连接问题。

具体使用那种方式,还得基于应用场景,安全过滤和连接管理等考虑。至此,关于查询和执行操作已经介绍了很多。关系型数据库的另外一个特性就是关系和事务处理。下一节,我们将会讨论database/sql的数据库事务功能。

`