go语言操作数据库性能
The database/sql package provides a generic interface around SQL (or SQL-like) databases.
See the official documentation for details
数据库驱动包
各种第三方实现 //github.com/golang/go/wiki/SQLDriversmysql数据库 选择Go-MySQL-Driver
地址 //github.com/go-sql-driver/mysql/
$ go get github.com/go-sql-driver/mysql
导入包
import "database/sql"
import _ "github.com/go-sql-driver/mysql"
Go连接数据库
db, err := sql.Open("mysql", "root:root@/uestcbook")执行Exec
result, err := db.Exec("INSERT INTO users (name, age) VALUES (?, ?)","gopher",27,)
查询Query
rows, err := db.Query("SELECT name FROM users WHERE age = ?", age)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
log.Fatal(err)
}
fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
查询一条QueryRow
var age int64
row := db.QueryRow("SELECT age FROM users WHERE name = ?", name)
err := row.Scan(&age)
Prepared statements
age := 27
stmt, err := db.Prepare("SELECT name FROM users WHERE age = ?")
if err != nil {
log.Fatal(err)
}
rows, err := stmt.Query(age)// process rows
Go事务
tx, err := db.Begin()if err != nil {
log.Fatal(err)
}
效率分析
db.exec和statement.exec和tx.exec区别
package main
import (
"strconv"
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
"time"
"log"
)
var db = &sql.DB{}
func init(){
db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/cms?charset=utf8") //创建连接池 sql.Open
if err != nil {
log.Fatal(err)
}
}
func main() {
insert()
query()
update()
delete()
}
func insert() {
//m1 insert//strconv,int转string:strconv.Itoa(i)
start := time.Now()
for i := 1;i<=100;i++{//每次循环内部都 去连接池获取一个新的连接 效率低下
db.Exec("INSERT INTO user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)
}
end := time.Now()
fmt.Println("m1 insert total time:",end.Sub(start).Seconds())
start = time.Now()
for i := 101;i<=200;i++{//m2 Prepare函数每次循环内部都 去连接池获取一个新的连接 效率低下
stm,_ := db.Prepare("INSERT INTO user(uid,username,age) values(?,?,?)")
stm.Exec(i,"user"+strconv.Itoa(i),i-1000)
stm.Close()
}
end = time.Now()
fmt.Println("m2 insert total time:",end.Sub(start).Seconds())
start = time.Now() //m3 insert
stm,_ := db.Prepare("INSERT INTO user(uid,username,age) values(?,?,?)")
for i := 201;i<=300;i++{ //Exec内部 没有去获取连接 效率还是低呢？
stm.Exec(i,"user"+strconv.Itoa(i),i-1000)
}
stm.Close()
end = time.Now()
fmt.Println("m3 insert total time:",end.Sub(start).Seconds())
start = time.Now()//m4 insert
tx,_ := db.Begin() //Begin函数内部会去获取连接
for i := 301;i<=400;i++{//每次循环用的都是tx内部的连接 没有新建连接 效率高
tx.Exec("INSERT INTO user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)
}//最后释放tx内部的连接
tx.Commit()
end = time.Now()
fmt.Println("m4 insert total time:",end.Sub(start).Seconds())
start = time.Now()//m5 insert
for i := 401;i<=500;i++{ //Begin函数每次循环内部都会去连接池获取一个新的连接 效率低下
tx,_ := db.Begin()
tx.Exec("INSERT INTO user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)
tx.Commit()//Commit执行后连接也释放了
}
end = time.Now()
fmt.Println("m5 insert total time:",end.Sub(start).Seconds())
}
func query(){
start := time.Now() //m1 query
rows,_ := db.Query("SELECT uid,username FROM USER")
defer rows.Close()
for rows.Next(){
var name string
var id int
if err := rows.Scan(&id,&name); err != nil {
log.Fatal(err)
}//fmt.Printf("name:%s ,id:is %d\n", name, id)
}
end := time.Now()
fmt.Println("m1 query total time:",end.Sub(start).Seconds())
start = time.Now() //m2 query
stm,_ := db.Prepare("SELECT uid,username FROM USER")
defer stm.Close()
rows,_ = stm.Query()
defer rows.Close()
for rows.Next(){
var name string
var id int
if err := rows.Scan(&id,&name); err != nil {
log.Fatal(err)
} // fmt.Printf("name:%s ,id:is %d\n", name, id)
}
end = time.Now()
fmt.Println("m2 query total time:",end.Sub(start).Seconds())
start = time.Now() //m3 query
tx,_ := db.Begin()
defer tx.Commit()
rows,_ = tx.Query("SELECT uid,username FROM USER")
defer rows.Close()
for rows.Next(){
var name string
var id int
if err := rows.Scan(&id,&name); err != nil {
log.Fatal(err)
}//fmt.Printf("name:%s ,id:is %d\n", name, id)
}
end = time.Now()
fmt.Println("m3 query total time:",end.Sub(start).Seconds())
}
func update(){
start := time.Now() //m1 update
for i := 1;i<=100;i++{
db.Exec("UPdate user set age=? where uid=? ",i,i)
}
end := time.Now()
fmt.Println("m1 update total time:",end.Sub(start).Seconds())
start = time.Now() //m2 update
for i := 101;i<=200;i++{
stm,_ := db.Prepare("UPdate user set age=? where uid=? ")
stm.Exec(i,i)
stm.Close()
}
end = time.Now()
fmt.Println("m2 update total time:",end.Sub(start).Seconds())
start = time.Now() //m3 update
stm,_ := db.Prepare("UPdate user set age=? where uid=?")
for i := 201;i<=300;i++{
stm.Exec(i,i)
}
stm.Close()
end = time.Now()
fmt.Println("m3 update total time:",end.Sub(start).Seconds())
start = time.Now() //m4 update
tx,_ := db.Begin()
for i := 301;i<=400;i++{
tx.Exec("UPdate user set age=? where uid=?",i,i)
}
tx.Commit()
end = time.Now()
fmt.Println("m4 update total time:",end.Sub(start).Seconds())
start = time.Now()//m5 update
for i := 401;i<=500;i++{
tx,_ := db.Begin()
tx.Exec("UPdate user set age=? where uid=?",i,i)
tx.Commit()
}
end = time.Now()
fmt.Println("m5 update total time:",end.Sub(start).Seconds())
}
func delete(){
start := time.Now()//m1 delete
for i := 1001;i<=1100;i++{
db.Exec("DELETE FROM USER WHERE uid=?",i)
}
end := time.Now()
fmt.Println("m1 delete total time:",end.Sub(start).Seconds())
start = time.Now() //m2 delete
for i := 1101;i<=1200;i++{
stm,_ := db.Prepare("DELETE FROM USER WHERE uid=?")
stm.Exec(i)
stm.Close()
}
end = time.Now()
fmt.Println("m2 delete total time:",end.Sub(start).Seconds())
start = time.Now() //m3 delete
stm,_ := db.Prepare("DELETE FROM USER WHERE uid=?")
for i := 1201;i<=1300;i++{
stm.Exec(i)
}
stm.Close()
end = time.Now()
fmt.Println("m3 delete total time:",end.Sub(start).Seconds())
start = time.Now() //m4 delete
tx,_ := db.Begin()
for i := 1301;i<=1400;i++{
tx.Exec("DELETE FROM USER WHERE uid=?",i)
}
tx.Commit()
end = time.Now()
fmt.Println("m4 delete total time:",end.Sub(start).Seconds())
start = time.Now() //m5 delete
for i := 1401;i<=1500;i++{
tx,_ := db.Begin()
tx.Exec("DELETE FROM USER WHERE uid=?",i)
tx.Commit()
}
end = time.Now()
fmt.Println("m5 delete total time:",end.Sub(start).Seconds())
}
结果
m1 insert total time: 3.7952171
m2 insert total time: 4.3162468
m3 insert total time: 4.3392482
m4 insert total time: 0.3970227
m5 insert total time: 7.3894226
m1 query total time: 0.0070004
m2 query total time: 0.0100006
m3 query total time: 0.0100006
m1 update total time: 7.3394198
m2 update total time: 7.8464488
m3 update total time: 6.0053435
m4 update total time: 0.6630379000000001
m5 update total time: 4.5402597
m1 delete total time: 3.8652211000000003
m2 delete total time: 3.8582207
m3 delete total time: 3.6972114
m4 delete total time: 0.43202470000000004
m5 delete total time: 3.7972172
原因分析sql.Open("mysql", "username:pwd@/databasename")
返回DB对象
DB对象对于多个goroutines并发使用是安全的
DB对象内部封装了连接池
open函数没有创建连接
只是验证参数是否合法
开启一个单独goroutines监听是否需要建立新的连接
当有请求建立新连接时就创建新连接
open函数应该被调用一次 通常没必要close
DB.Exec()
执行不返回行 row 的查询 如INSERT UPDATE DELETE
DB交给内部的exec方法负责查询
exec先调用DB内部的conn方法从连接池里面获得一个连接
后检查内部的driver.Conn实现了Execer接口没有
如果实现了该接口 会调用Execer接口的Exec方法执行查询
否则调用Conn接口的Prepare方法负责查询
DB.Query()
检索 retrieval 如SELECT
DB交给内部的query方法负责查询
query先调用DB内部的conn方法从连接池里面获得一个连接
后调用内部的queryConn方法负责查询
DB.QueryRow()
用于返回单行的查询
转交给DB.Query()查询
db.Prepare()
返回一个Stmt
Stmt对象可以执行Exec,Query,QueryRow等操作
DB交给内部的prepare方法负责查询
prepare首先调用DB内部的conn方法从连接池里面获得一个连接
后调用driverConn的prepareLocked方法负责查询
Stmt方法
st.Exec()
st.Query()
st.QueryRow()
st.Close()
db.Begin()
开启事务 返回Tx对象
调用该方法后 TX和指定的连接绑定在一起
事务提交或者回滚
该事务绑定的连接就还给DB的连接池
DB交给内部的begin方法负责处理
begin首先调用DB内部的conn方法从连接池里面获得一个连接
后调用Conn接口的Begin方法获得一个TX
TX方法//没有先去获取连接 Tx建立时就和一个连接绑定了 所以这些操作内部共用一个TX内部的连接
tx.Exec()
tx.Query()
tx.QueryRow()
tx.Prepare()
tx.Commit()
tx.Rollback()
tx.Stmt()//用于将一个已存在的statement和tx绑定在一起
一个statement可以不和tx关联 比如db.Prepare()返回的statement就没有和TX关联
updateMoney, err := db.Prepare("UPDATE balance SET money=money+? WHERE id=?")
...
tx, err := db.Begin()
...
res, err := tx.Stmt(updateMoney).Exec(123.45, 98293203)
Stmt定义 // Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.
type Stmt struct {
// Immutable:
db *DB // where we came from
query string // that created the Stmt
stickyErr error // if non-nil, this error is returned for all operations
closemu sync.RWMutex // held exclusively during close, for read otherwise.
// If in a transaction, else both nil:
tx *Tx
txsi *driverStmt
mu sync.Mutex // protects the rest of the fields
closed bool
// css is a list of underlying driver statement interfaces
// that are valid on particular connections. This is only
// used if tx == nil and one is found that has idle
// connections. If tx != nil, txsi is always used.
css []connStmt
}
//github.com/golang/go/wiki/SQLInterface
//github.com/go-sql-driver/mysql/
尊贵的董事大人
英文标题不为空时 视为本栏投稿
需要关键字 描述 英文标题