MySql批量插入的性能比较

转载

刘靖凯 2022-02-08 20:31:13 博主文章分类：.net

　　为了提高性能，需要对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条记录

MySql批量插入的性能比较_mysql

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

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

MySql批量插入的性能比较_mysql_02