《Ultimate Guide Building Databas

2020-05-19 本文已影响0人苏慕漓

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

func main() {
    db, err := sql.Open("mysql", "root:@tcp(:3306)/test")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    _, err = db.Exec("CREATE TABLE IF NOT EXISTS test.hello(world varchar(50))")
    if err != nil {
        log.Fatal(err)
    }

    res, err := db.Exec("INSERT INTO test.hello(world) VALUES('hello world!')")
    if err != nil {
        log.Fatal(err)
    }
    rowCount, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    log.Println(rowCount)

    rows, err := db.Query("SELECT * FROM test.hello")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    for rows.Next() {
        var s string
        err = rows.Scan(&s)
        if err != nil {
            log.Fatal(err)
        }
    }
}

database/sql 包提供了几种数据类型，每一种都代表一个或者一组概念：
- DB：sql.DB 类型代表一个数据库
  - 不表示到数据库的连接，而是将数据库抽象为一个可修改的对象；
  - 连接由内部连接池管理
  - lets you use databases that are actually connectionless, such as shared-memory or embedded databases, through the same abstraction without worrying about exactly how you communicate with them. You can access them directly via a sql.Conn type.
- Results：一组包含了数据库交互结果的数据类型
  - sql.Rows：取出从一个请求获得的多行结果；
  - sql.Row：单条结果；
  - sql.Result：用于检查对数据库修改导致的状态变化
- Statements：sql.Stmt 表示状态
- Transactions and Connections：
  - sql.Tx 表示一个指定条件的事务
  - sql.Conn 提供了一个由驱动管理的可达连接
调用sql.Open()后通常没有真正开始连接，只是在内存中创建了对象并将其关联到一个驱动上。大部分驱动要等到真正开始操作数据库时才会开始发起连接
database/sql 包持有一个连接池，连接池初始化为空，连接通过懒加载的方式在需要时才创建。
- database/sql 包通过驱动来创建和管理单独的连接。
当某个函数需要访问底层数据库时，会向连接池请求一个连接，连接池此时会分配一个空闲连接或者创建一个新连接，并将其所有权移交给函数。当函数调用完后，可能会将连接直接放回连接池，也可能会继续传递连接的所有权给某个对象，最后由对象来将连接归还至连接池。
- db.Ping()：直接将连接归还连接池；
- db.Exec()：直接将连接归还连接池，但是返回的结果对象还持有一个指向连接的引用，当后续需要检查该结果对象时，可以复用该连接；
- db.Query()：将连接的所有权传递给一个sql.Rows 对象，当迭代完所有的结果行或者.Close() 调用时，连接被归还给连接池；
  - 注意，下面的写法会导致内存泄漏：
    
    _, err := db.Query("DELETE FROM hello.world LIMIT 1")
- db.QueryRow()：将连接的所有权传递给一个sql.Row对象，当.Scan()方法调用后，连接被归还；
- db.Begin()：将连接的所有权传递给一个sql.Tx对象，当.Commit()或.Rollback()调用后，连接被归还；
使用连接池可以让用户免于处理连接错误。当连接池中的某一个连接被探测到失效时，database/sql会重新尝试连接10次，之后会重新从连接池中获取一条空闲连接或者重新打开一个连接；
连接池的控制：
- db.SetMaxOpenConns(n int)：设置最大连接数；
  - n = 0 表示没有限制
- db.SetMaxIdleConns(n int)：设置最大空闲连接；如果连接池中的空闲连接数超过上限，则后续被释放的连接会被直接关闭；
  - 默认 n = 0，表示所有连接都不回收，释放后直接关闭；
- db.SetConnMaxLifetime(d time.Duration)：设置连接的超时时间
  - 默认永久存在
rows.Next() 在遇到错误，包括 io.EOF（表示到达了rows的最后）时，会返回 false
- 注意，如果 rows 在没有完全迭代完就退出了迭代循环（不是因为 rows.Next() 返回false导致循环中断），则其持有的连接也不会被释放到连接池，这就造成了内存泄漏；
- 如果 rows 迭代是因为 rows.Next() 返回 false 而退出，不管是正常返回 false 还是异常返回 false，rows.Close() 都会自动调用
- 通用法则：尽可能早的调用rows.Close()，尽可能快的释放资源：
  - 如果对 rows 的处理过程是放在一个封闭函数中，那么使用 defer rows.Close()
  - 如果处理 rows 的封闭函数需要长时连接并且在一个循环中重复执行请求，那么不应该使用 defer，而应该直接在退出循环时调用 rows.Close()，原因：
    - 在长时连接中defer很可能很长时间都不会被执行；
    - defer函数以及其引用的变量都会消耗内存，如果函数长时间没有释放会导致内存泄漏

db.QueryRow()：执行请求，返回零值或者一条记录：

var s string
err = db.QueryRow("select * from hello.world limit 1").Scan(&s)
if err != nil {
    if err == sql.ErrNoRows {
        // special cases: there was no row
    } else {
        log.Fatal(err)
    }
}

db.Exec()：返回一个 sql.Result，sql.Result 的 .RowAffected()方法返回受影响的行数，.LastInsertId() 方法返回最后一条插入记录的编号
- ```
// A Result summarizes an executed SQL command.
type Result interface {
    // LastInsertId returns the integer generated by the database
    // in response to a command. Typically this will be from an
    // "auto increment" column when inserting a new row. Not all
    // databases support this feature, and the syntax of such
    // statements varies.
    LastInsertId() (int64, error)

    // RowsAffected returns the number of rows affected by an
    // update, insert, or delete. Not every database or database
    // driver may support this.
    RowsAffected() (int64, error)
}
```
- 可以用来获取自增ID；
- go 保证了用来创建 Result 的数据库连接与调用 RowAffected() 和 LastInsertId 的连接是同一个;
- Result 是接口类型，其具体行为依赖于底层数据库和提供实现的驱动，如：
  - MySQL 可以使用一个 BIGINT UNSIGNED 作为自增列，所以其自增ID的值可以非常大；
  - 支持LastInsertId() 方法的驱动在调用该值时不需要再次与数据库交互去获得这个值，因为已经被保存在了结构体中，但是依旧会从连接池中取出连接并锁定该连接，然后再放回，尽管并没有使用到连接，这是由 database/sql 来执行的，不是由驱动执行的。
    - 因此，尽管该方法不需要访问数据库，在连接繁忙时还是会造成阻塞等待；
  - RowAffected() 和 LastInsertId 是否返回错误也是由驱动决定的，MySql 的驱动不会返回错误；尽管如此，也应该遵循database/sql中接口的公开定义，对于有错误返回的函数应该执行错误检查；
Prepared Statements:
- Go 的database/sql将 prepared statements 当做一等公民，并为他们定义了一个 sql.Stmt 类型。事实上 database/sql 更倾向于使用 prepared statements：
  
  res, err := db.Exec("INSERT INTO test.hello(world) VALUES(?)", "hello world!")
  - Go 会把 Exec() 的第0个参数当成是statement，然后执行prepare；
  - 执行调用的实际上是用剩下的参数完成准备后生成的结果；
  - 关闭prepared statement
- prepared statement的好处：
  - 方便：避免使用引用或者嵌入 SQL；
  - 安全：避免 SQL 注入；
  - 可能会有一些 driver-specific, protocol-specific, database-specific 的方式或者其它增强；
  - 消除对重复 SQL 语句的解析，生成执行计划等
- 使用 db.Prepare() 语句来明确执行prepare，已达到复用优化：
```
stmt, err := db.Prepare("INSERT INTO test.hello(world) VALUES(?)")
for _, str := range []string{"hello1", "hello2", "hello3"} {
    res, err := stmt.Exec(str)
    if err != nil {
        log.Fatal(err)
    }
}
stamt.Close()
```
- 错误使用 prepare：
  - prepared statements 只使用一次，除非是为了避免 SQL 注入等其他特殊目的，否则应该避免该情况，因为会加大网络交互的开销；
  - 在循环中重复 prepare
Statement 和 connection 的关系：
- 当使用 db.Prepare() 准备了一条 statement ，并获得一个 stmt 的返回值后，该 statement 其实是绑定到了连接池中的某个连接上，然后该连接会被放回连接池，statement 会记下准备时使用的连接。当执行 statement 时，会尝试获取到之前的连接；
- 如果之前的连接已经被使用，则会选取一个新的连接重新执行准备，并将该连接加入到一个 remembered statements 列表中；
- 实际上，一条 statement 会在很多不同连接上进行准备，prepared statements 的数量可能远比代码中定义的 sql.Stmt 变量的数量要大的多；
- 这种重复准备的情况在高负载时尤其容易发生；
以下情况应该避免使用 prepared statements：
- statement 没有参数；
- statement 不会被复用；
- 数据库驱动不支持 prepared statements ;
- 避免使用 prepared statements时：
  - 不要显示的调用 db.Prepare()；
  - 不要在调用如 db.Query() 等方法时传入多个参数；
拼接 SQL 语句会导致 SQL 注入，解决该问题而又不调用 prepared statements 的方法是：对传入参数进行校验；
Transactions：
- 错误的用法：
```
_, err = db.Exec("BEGIN")
_, err = db.Exec("UPDATE account SET balance = 100 WHERE user = 83")
_, err = db.Exec("COMMIT")
```
  - 错误的原因：底层连接池，无法保证这些 statements 都在同一个连接上执行。
- 正确的用法：使用 sql.Tx
  - db.Begin() 或 db.BeginTx() 开启事务
- 当一个 sql.Stmt 关联到一个 sql.Tx 上时，在底层只会绑定到一个连接上，不会出现重复准备的情况；
  - 由 db 准备的 stmt 只能在 db 上使用，由 tx 准备的 stmt 也只能在 tx 上使用；
  - 在事务的领域中，通常隐含的重试10次的逻辑也会失效；
- 在使用事务时，经常需要处理死锁和回滚；
- 事务中没有并发，所有交互都是顺序执行；
```
rows, _ := tx.Query("SELECT id FROM master_table")
for rows.Next() {
    var mid, did int
    rows.Scan(&mid)
    tx.QueryRow("SELECT id FROM detail_table WHERE master = ?", mid).Scan(&did)
}
```
  - 错误的用法，循环中的第二条请求语句会尝试在 tx 的连接上发起一个新的请求，但是连接当前在忙于执行行提取，没办法接受新的请求；
使用单一连接：
- 有时需要在不使用事务时也保证只使用单一连接，如：
  - Connection-specific state, such as temp tables or user-defined variables, or setting the current database with USE or similar;
  - Limiting concurrency and avoiding unwanted connections to the database;
  - Explicit locks;
  - The use of database-specific extensions of behaviors
- 在 Go 1.9 之后，可以通过 db.Conn() 来获得一个与数据库的连接;
  - func (db *DB) Conn(ctx context.Context) (*Conn, error)
  - 注意在使用完后要调用 conn.Close() 来关闭

使用内置接口：

使用标准接口可以提升代码质量；

在与数据库交互过程中，传输和提取数据的两个重要接口：

driver.Valuer：influences how values are transformed as they are sent to the database

// Valuer is the interface providing the Value method.
//
// Types implementing Valuer interface are able to convert
// themselves to a driver Value.
type Valuer interface {
  // Value returns a driver Value.
  // Value must not panic.
  Value() (Value, error)
}

sql.Scanner：influences how values are transformed upon retrieval.

// Scanner is an interface used by Scan.
type Scanner interface {
  // Scan assigns a value from a database driver.
  //
  // The src value will be of one of the following types:
  //
  //    int64
  //    float64
  //    bool
  //    []byte
  //    string
  //    time.Time
  //    nil - for NULL values
  //
  // An error should be returned if the value cannot be stored
  // without loss of information.
  //
  // Reference types such as []byte are only valid until the next call to Scan
  // and should not be retained. Their underlying memory is owned by the driver.
  // If retention is necessary, copy their values before the next call to Scan.
  Scan(src interface{}) error
}

示例：

//use interface to make sure the read and write string are all lowercase
type LowercaseString string

//Implements driver.Valuer
func (ls LowercaseString) Value() (driver.Value, error) {
    return driver.Value(strings.ToLower(string(ls))), nil
}

//Implements sql.Scanner simplistic
func (ls *LowercaseString) Scan(src interface{}) error {
    var source string
    switch src.(type) {
    case string:
        source = src.(string)
    case []byte:
        source = string(src.([]byte))
    default:
        return errors.New("Incompatible type for LowercaseString")
    }
    *ls = LowercaseString(strings.ToLower(source))
    return nil
}

func main() {
    db, err := sql.Open("mysql", "root:@tcp(:3306)/test")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    _, err = db.Exec("CREATE TABLE IF NOT EXISTS test.hello(world varchar(50))")
    if err != nil {
        log.Fatal(err)
    }

    _, err = db.Exec("DELETE FROM test.hello")
    if err != nil {
        log.Fatal(err)
    }

    var normalString string = "I AM UPPERCASED NORMAL STRING"
    var lcString LowercaseString = "I AM UPPERCASED MAGIC STRING"

    _, err = db.Exec("INSERT INTO test.hello VALUES(?), (?)", normalString, lcString)
    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query("SELECT * FROM test.hello")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    for rows.Next() {
        var s1 LowercaseString
        err = rows.Scan(&s1)
        if err != nil {
            log.Println(err)
        }
        log.Print(s1)
    }
}

输出结果：

test1.png

数据库中存储的数据：
test2.png

其他用例：

实现数据验证：数据必须按照指定格式来组织
将数据转为统一格式

显示的实现压缩和解压缩数据

type GzippedText []byte

func (g GzippedText) Value() (driver.Value, error) {
  b := make([]byte, 0, len(g))
  buf := bytes.NewBuffer(b)
  w := gzip.NewWriter(buf)
  w.Write(g)
  w.Close()
  return buf.Bytes(), nil
}

func (g *GzippedText) Scan(src interface{}) error {
  var source []byte
  switch src.(type) {
  case string:
      source = []byte(src.(string))
  case []byte:
      source = src.([]byte)
  default:
      return errors.New("Incompatible type for GzippedText")
  }
  reader, _ := gzip.NewReader(bytes.NewReader(source))
  defer reader.Close()
  b, err := ioutil.ReadAll(reader)
  if err != nil {
      return err
  }
  *g = GzippedText(b)
  return nil
}

显示实现数据的加密和解密

观察和监控 database/sql 包内部状态的两种方法：其公开的统计数据和上下文

db.Stats()：可以获得统计数据，会返回一个 DBStats 结构体：

type DBStats struct {
    MaxOpenConnections int // Maximum number of open connections to the database.

    // Pool Status
    OpenConnections int // The number of established connections both in use and idle.
    InUse           int // The number of connections currently in use.
    Idle            int // The number of idle connections.

    // Counters
    WaitCount         int64         // The total number of connections waited for.
    WaitDuration      time.Duration // The total time blocked waiting for a new connection.
    MaxIdleClosed     int64         // The total number of connections closed due to SetMaxIdleConns.
    MaxLifetimeClosed int64         // The total number of connections closed due to SetConnMaxLifetime.
}

db.Stats() 的调用并不会耗费太多资源而且是线程安全的

常见的坑：
- 在循环中调用 defer：可能导致内存和连接的使用量增长超过限制；
- 打开多个 db 对象：可能会导致高延迟，高负载和TCP连接进入TIME_WAIT状态
- 忘记调用rows.Close()：导致连接泄漏，服务器的负载增加；
- Single-use prepared statements
- 过多的 prepared statement：如果代码运行在高并发条件下，谨慎考虑使用 prepared statement 是否是个正确的选择，因为可能会导致在多个连接上多次重复准备；
- Cluttering the code with strconv or casts : Scan into a variable of the type you want, and let .Scan() convert behind the scenes for you
- Cluttering the code with error-handling and retry：应该让 datebase/sql 来处理连接池，重连接和重试逻辑；
- 在 rows.Next() 调用后忘记检查错误；
- 使用 db.Query() 来执行 non-SELECT 请求：不要在结果集不存在的情况下去迭代结果集，否则会导致连接泄漏；不要误用 db.Query() 和 db.Exec()
- 假定子请求语句会使用同一个连接：前后执行两个请求语句，他们可能会分别在两个连接上执行：
  - 在 SELECT * FROM tbl1 后执行 LOCK TABLES tbl1 WRITE 可能会导致阻塞和等待；
- Accessing the db while working with a tx
- 注意 NULL 值：不能将一个 NULL 值 scan 到一个变量中除非实现了 NullXXX 类型（自己实现，数据库驱动实现或者包实现），因此特别要注意数据表中值为 NULL 的项
- 传递一个 uint64 作为参数：Query()、QueryRow() 和 Exec() 不接受 uint64 类型的参数，将大数字转换为 string 类型来解决

《Ultimate Guide Building Databas

猜你喜欢

热点阅读