go实测 ——Mysql 通过预处理（Prepare）提升性能

package main
 
import (
    _ "github.com/go-sql-driver/mysql"
    "fmt"
    "database/sql"
    "time"
    "flag"
    "math/rand"
)
 
 
const (
    USERNAME = "root"
    PASSWORD = "123456"
    NETWORK  = "tcp"
    SERVER   = "localhost"
    PORT     = 3306
    DATABASE = "code369_account"
)
 
 
type Account struct {
    ID uint64        `gorm:"AUTO_INCREMENT" redis:"id"` // 内部唯一ID, 自增
    Account string `gorm:"size:32;unique" redis:"account"`
    Password string `gorm:"size:16" redis:"password"`
    Uid int64         `redis:"uid"`// 对应User表ID
}
var runtime int
 
func main() {
 
    flag.IntVar(&runtime, "i", 10000, "run time")
    flag.Parse()
 
    dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME,PASSWORD,NETWORK,SERVER,PORT,DATABASE)
    DB,err := sql.Open("mysql",dsn)
    if err != nil{
        fmt.Printf("Open mysql failed,err:%v\n",err)
        return
    }
 
    prepareQueryTest1(DB)
    sqlQueryTest(DB)
}
 
func sqlQueryTest(db *sql.DB) {
    sqlStr := "SELECT id, account, password FROM accounts WHERE id = %d"
    t1:=time.Now()
 
    var u Account
    for k:=0;k<runtime;k++{
        id := rand.Int()%10000
        sql := fmt.Sprintf(sqlStr, id)
        rows,err := db.Query(sql)
        if err != nil {
            panic(err)
        }
 
        if rows.Next(){
            err := rows.Scan(&u.ID, &u.Account, &u.Password)
            if err != nil {
                fmt.Printf("scan data failed, err:%v\n", err)
                return
            }
        }
        rows.Close()
    }
    fmt.Printf("no prepare use time:%v\n",  time.Since(t1))
 
}
 
// 预处理查询数据
func prepareQueryTest1(db *sql.DB) {
    // 生成一条预处理
    sqlStr := "SELECT id, account, password FROM accounts WHERE id = ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare sql failed, err:%v\n", err)
        return
    }
    defer stmt.Close()
 
    t1:=time.Now()
    var u Account
    for k:=0;k<runtime;k++{
        id := rand.Int()%10000
        rows, err := stmt.Query(id)
        if err != nil {
            fmt.Printf("exec failed, err:%v\n", err)
            return
        }
        if rows.Next(){
            err := rows.Scan(&u.ID, &u.Account, &u.Password)
            if err != nil {
                fmt.Printf("scan data failed, err:%v\n", err)
                return
            }
        }
        rows.Close()
    }
    fmt.Printf("prepare use time:%v\n", time.Since(t1))
}