MySql批量插入的性能比较
为了提高性能，需要对mysql批量插入进行一次性对对比，下面是测试完后的结果，存档以备后查。
1. 测试结果
|
记录条数 |
循环插入,一个事务提交insert00（ms） |
批量函数插入 insert01（ms） |
一个inser多个values insert02 （ms） |
多个insert拼接 insert03（ms） |
|
4681 |
1225 |
203 |
420 |
2566 |
|
100 |
34 |
5 |
5 |
51 |
|
500 |
131 |
22 |
18 |
231 |
|
1000 |
251 |
46 |
37 |
476 |
|
2000 |
484 |
89 |
111 |
1047 |
|
4000 |
950 |
173 |
366 |
2683 |
|
8000 |
2092 |
334 |
1073 |
7684 |
|
15000 |
3646 |
588 |
3283 |
16900 |
|
20000 |
4716 |
780 |
6638 |
|
|
30000 |
7102 |
1146 |
10699 |
注：批量函数的上限是：4681条记录
2. 源码
package main
import (
"fmt"
"strconv"
"strings"
"time"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
func main() {
accountings := initData(15000)
//insert00(accountings)
//insert01(accountings)
//insert02(accountings)
insert03(accountings)
}
func initData(length int) []Accounting {
accountings := make([]Accounting, length)
for i := 0; i < length; i++ {
ii, _ := strconv.Atoi(time.Now().Format("20060102150405") + fmt.Sprint(i))
accountings[i] = Accounting{
No: uint64(ii),
AccountNo: 132079455772000256,
AccountTime: time.Now(),
Amount: 1000,
Balance: 0,
Currency: "JPY",
Direction: 1,
Operation: "charge",
IsDischarge: false,
BusinessID: "b" + fmt.Sprint(i),
BusinessTime: time.Now(),
BusinessCode: "StarPay",
CreateTime: time.Now(),
GroupID: "group000" + fmt.Sprint(i),
}
}
return accountings
}
func insert00(accountings []Accounting) {
begin := time.Now()
insertSQL := `INSERT INTO accounting
(no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`
db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8")
conn, _ := db.Beginx()
for _, e := range accountings {
conn.Exec(insertSQL, e.No, e.AccountNo, e.AccountTime, e.Amount, e.Balance, e.Currency, e.Direction, e.Operation, e.IsDischarge, e.BusinessID, e.BusinessTime, e.BusinessCode, e.CreateTime, e.GroupID)
}
conn.Commit()
fmt.Println(time.Now().Sub(begin).Microseconds())
defer db.Close()
}
func insert01(accountings []Accounting) {
begin := time.Now()
insertSQL := `INSERT INTO accounting
(no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id)
VALUES
(:no,:account_no,:account_time,:amount,:balance,:currency,:direction,:operation,:is_discharge,:business_id,:business_time,:business_code,:create_time,:group_id)`
db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8")
conn, _ := db.Beginx()
ind := len(accountings) / 4000
yu := len(accountings) % 4000
if yu > 0 {
ind = ind + 1
}
for i := 0; i < ind; i++ {
end := (i + 1) * 4000
if i == ind-1 && yu != 0 {
end = (i * 4000) + yu
}
accs := accountings[i*4000 : end]
conn.NamedExec(insertSQL, accs)
}
conn.Commit()
fmt.Println(time.Now().Sub(begin).Microseconds())
defer db.Close()
}
func insert02(accountings []Accounting) {
begin := time.Now()
values := make([]string, len(accountings))
for i := 0; i < len(values); i++ {
account := accountings[i]
value := `(` + fmt.Sprint(account.No) + "," + fmt.Sprint(account.AccountNo) + ",'" + account.AccountTime.Format("2006-01-02 15:04:05.000") + "'," + strconv.FormatFloat(account.Amount, 'f', 4, 64) + "," + strconv.FormatFloat(account.Balance, 'f', 4, 64) + ",'" + account.Currency + "'," + strconv.Itoa(int(account.Direction)) + ",'" + account.Operation + "'," + strconv.FormatBool(account.IsDischarge) + ",'" + account.BusinessID + "','" + account.BusinessTime.Format("2006-01-02 15:04:05.000") + "','" + account.BusinessCode + "','" + account.CreateTime.Format("2006-01-02 15:04:05.000") + "','" + account.GroupID + "')"
values[i] = value
}
insertSQL := `INSERT INTO accounting
(no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id)
VALUES`
for i := 0; i < len(values); i++ {
insertSQL = insertSQL + values[i] + ","
}
insertSQL = strings.TrimRight(insertSQL, ",")
fmt.Println(time.Now().Sub(begin).Microseconds())
db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8")
db.Exec(insertSQL)
fmt.Println(time.Now().Sub(begin).Microseconds())
defer db.Close()
}
func insert03(accountings []Accounting) {
begin := time.Now()
insertSQL := `INSERT INTO accounting(no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id) VALUES`
sumSql := ""
for i := 0; i < len(accountings); i++ {
account := accountings[i]
sumSql = sumSql + insertSQL + `(` + fmt.Sprint(account.No) + "," + fmt.Sprint(account.AccountNo) + ",'" + account.AccountTime.Format("2006-01-02 15:04:05.000") + "'," + strconv.FormatFloat(account.Amount, 'f', 4, 64) + "," + strconv.FormatFloat(account.Balance, 'f', 4, 64) + ",'" + account.Currency + "'," + strconv.Itoa(int(account.Direction)) + ",'" + account.Operation + "'," + strconv.FormatBool(account.IsDischarge) + ",'" + account.BusinessID + "','" + account.BusinessTime.Format("2006-01-02 15:04:05.000") + "','" + account.BusinessCode + "','" + account.CreateTime.Format("2006-01-02 15:04:05.000") + "','" + account.GroupID + "');"
}
fmt.Println(time.Now().Sub(begin).Microseconds())
db, _ := sqlx.Open("mysql", "root:!Gsw790622@tcp(127.0.0.1:3306)/starpay_acct?charset=utf8&multiStatements=true")
db.Exec(sumSql)
fmt.Println(time.Now().Sub(begin).Microseconds())
defer db.Close()
}
type Accounting struct {
No uint64 `db:"no"`
AccountNo uint64 `db:"account_no"`
AccountTime time.Time `db:"account_time"`
Amount float64 `db:"amount"`
Balance float64 `db:"balance"`
Currency string `db:"currency"`
Direction int8 `db:"direction"`
Operation string `db:"operation"`
IsDischarge bool `db:"is_discharge"`
DischaregID string `db:"dischareg_id"`
DischaregAccountNo string `db:"dischareg_account_no"`
BusinessID string `db:"business_id"`
BusinessTime time.Time `db:"business_time"`
BusinessCode string `db:"business_code"`
CreateTime time.Time `db:"create_time"`
GroupID string `db:"group_id"`
}
3. 监控批量插入函数NamedExec生成的SQL
2021-06-18T03:21:52.751480Z 12 Execute INSERT INTO accounting
(no,account_no,account_time,amount,balance,currency,direction,operation,is_discharge,business_id,business_time,business_code,create_time,group_id) VALUES(202106181221520,132079455772000256,'2021-06-1803:21:52.749515',1000,0,'JPY',1,'charge',0,'b0','2021-06-1803:21:52.749515','StarPay','2021-06-1803:21:52.749515','group0000'),(202106181221521,132079455772000256,'2021-06-1803:21:52.749519',1000,0,'JPY',1,'charge',0,'b1','2021-06-1803:21:52.749519','StarPay','2021-06-18 03:21:52.749519','group0001'),(202106181221522,132079455772000256,'2021-06-1803:21:52.749521',1000,0,'JPY',1,'charge',0,'b2','2021-06-1803:21:52.749521','StarPay','2021-06-1803:21:52.749521','group0002'),(202106181221523,132079455772000256,'2021-06-1803:21:52.749522',1000,0,'JPY',1,'charge',0,'b3','2021-06-1803:21:52.749523','StarPay','2021-06-1803:21:52.749523','group0003'),(202106181221524,132079455772000256,'2021-06-1803:21:52.749524',1000,0,'JPY',1,'charge',0,'b4','2021-06-1803:21:52.749524','StarPay','2021-06-18 03:21:52.749524','group0004')
