网站首页 返回列表像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。

Golang Mysql笔记（三）--- Prepared剖析

2020-06-09 19:08:06 admin 1342

**## prepare**

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

### 查询

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

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

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

### prepared

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

1. 准备prepare语句
  2. 执行prepared语句和参数
  3. 关闭prepared语句

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

1. 避免通过引号组装拼接sql语句。避免sql注入带来的安全风险
  2. 可以多次执行的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连接或者等待一个可用连接，甚至也可能建立一个新的连接；后者表示打开连接时的策略为每次建立一个新的连接。这就是签名所说的retry10次连接。

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的使用方式，基于其好处和缺点，我们将会再后面的最佳实践再讨论。目前需要注意的大致就是：

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

2. 不要循环中创建prepare语句

3. 注意关闭 stmt

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

对于Qeruy操作如此，同理Exec操作也一样。

### 总结

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

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

转载文章，原文链接： Golang Mysql笔记（三）--- Prepared剖析

关键字词golang mysql

分享到：

上一篇：Golang Mysql笔记（四）--- 事务

下一篇：Golang处理JSON（一）--- 编码

如需留言，请登录，没有账号？请注册

0 条评论 0 人参与

网站首页 返回列表像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。

Golang Mysql笔记（三）--- Prepared剖析

zhou 3年前

zhou 3年前

zhou 3年前

zhou 3年前

武云 3年前

网站首页 返回列表 像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。

Golang Mysql笔记（三）--- Prepared剖析

zhou 3年前

zhou 3年前

zhou 3年前

zhou 3年前

武云 3年前

网站首页返回列表像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。