Fork me on GitHub

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')

 

　　想要更快更方便的了解相关知识，可以关注微信公众号 
 

 

 

posted @ 2022-02-08 20:32  桂素伟  阅读(176)  评论(0)  编辑  收藏  举报