golang 一度のinsertで複数のデータをまとめてDBに入れる方法

Question

type InsertItem struct {
		Name string
		Age  int
		Address string
}

var insert_content = []InsertItem{
		{"taro",15,"tokyo"},
		{"jiro",22,"osaka"},
}

を

ret, err := db.Exec(`INSERT INTO table名 (name,age,address) VALUES (?),(?)`,insert_content)

のような感じで一度のinsertでまとめてDBに入れたいのですがどのようにすれば良いのでしょうか？

Answer 1

https://github.com/t-tiger/gorm-bulk-insert

のバルクインサート？を試してみた所速くinsertができましたのでこれを使用してみようと思いました。

Answer 2

いくらかバルクインサートの事例はありますが、あまりオススメできません。
パラメータとプレースホルダとの帳尻合わせしつつ、SQLを動的に組み立てることになります。その記述はトリッキーであり、SQL記述ミスを誘発しやすいです。

GoではPreparedしたひとつインサートだけのステートメントを繰り返し呼ぶ方が確実だし、この場合SQLのパースコストが下げられて性能も良いかもしれません。

僕が複数レコードインサートを実装する場合は以下のサンプルと同じ方法を採ります。
https://golang.org/pkg/database/sql/#Tx.Prepare

package main

import (
	"context"
	"database/sql"
	"log"
)

var (
	ctx context.Context
	db  *sql.DB
)

func main() {
	projects := []struct {
		mascot  string
		release int
	}{
		{"tux", 1991},
		{"duke", 1996},
		{"gopher", 2009},
		{"moby dock", 2013},
	}

	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback() // The rollback will be ignored if the tx has been committed later in the function.

	stmt, err := tx.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

	for id, project := range projects {
		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
			log.Fatal(err)
		}
	}
	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}
}

この方法の良いところ

• SQLの調整がしやすい
• 既にある項目をフラットなスライスに並べ直すオーバーヘッドがない
• reflectを使わないので性能劣化が無い
• PrepareサポートなDBではかなり性能が向上する
• ナイーブなエラーのハンドリング実装を追加しやすい
• コネクションプールによる実装（Go標準）でPrepareステートメントの消費量が抑えられる
• 他の複雑なクエリにも同様の記述で対応できる（select&updateなど）

例えばMySQLではPrepareした性能向上効果は大きいのですが、
デフォルトで16K個のPrepareステートメントを作ろうとした時点でエラーになります。
SQLビルダーで一つのことに対しN個のPrepareステートメントを作ると
その数にコネクション数Mを掛けたNｘM個のPrepareステートメントを作成してしまう可能性があります。
コネクション数やPrepareステートメント数の上限を調整するという考えは
データベースへのアクセス規模に応じてどのみち必要なのですが
わざわざこれらの問題を呼び寄せてしまうバルク操作は
「 Goが並列に強いからこそ、お勧めしません。 」
（LL言語実装の多くは並列にDBアクセスをしないのでこの問題は起きにくいです）

Comments

[退会済みユーザー]

ご回答ありがとうございます
一つずつinsertを致しますとデータの数だけinsertを呼ぶことになりかなり遅くなるのかなと想像していたのですが性能は良くなるのでしょうか？？

[nobonobo]

最終的には計測しなくては比較できませんが、動的（例えばテンプレート）にSQLを作るとデータベースエンジンのパース済みキャッシュの利用率が大幅に減るのです。SQL文が固定であれば、キャッシュ（プリペアードステートメント）を使って毎回パースする必要が減ります。
つまり繰り返しの二回目以降はSQL文そのものをデータベースエンジンには送りません。キャッシュされた時の番号とパラメータだけを送ります。

[nobonobo]

「データベースエンジンとの接続」や「SQLのパース」は他のいろんな処理に比べ負荷の大きい処理です。これらの頻度を如何に下げるかがデータベースエンジンを利用する際のキモです。

[nobonobo]

軽量言語の場合、外部の機能呼び出しそのもののオーバーヘッドがネイティブに比べかなり大きいのでおっしゃるようなデータベースエンジンの機能呼び出し回数を下げるためにバルクインサート機能を用意するのが一般的ですが、Goではその心配はいりません。

[退会済みユーザー]

構造体型が要素のスライスを早くinsertするにはnobonobo様ですとどの様な手段を取られるのでしょうか？

https://stackoverflow.com/questions/53285928/bulk-mysql-inserts-are-2x-slower-than-php
ですと10万件のデータを10個のgo routines処理にすれば9.2から2.6秒になったと書かれていましたので今はこのやり方を考えています

https://stackoverflow.com/questions/21108084/how-to-insert-multiple-data-at-once/55311498#55311498
それと、MAPのバルクインサートの方法は見つかったのですがスライスのバルクインサートのやり方を教えていただく事はできませんでしょうか？
おすすめではないとのことだったのですがどちらも試してみたいと思っています

[nobonobo]

db.Execメソッドのシグネチャは以下の宣言なので必ず[]interface{}にまとめて展開渡しをすることになります。
func (db *DB) Exec(query string, args ...interface{}) (Result, error)
なのでどのような実装も最終的にスライスのバルクインサートになります。
（ベストアンサーのライブラリもそのように実装されていますね）

そして、トランザクションを貼ったうえでPrepareした繰り返しバージョンも是非お試しください。

[nobonobo]

Prepareした方がおおむね高性能な結果が得られるのですが、ベストアンサーのようなライブラリにPrepareを入れると別の問題が発生しやすいのでご注意ください。
（追記： gorm経由でPrepareが利用済みでした。READMEにも注意が書いてありますがlimitとの兼ね合いがあるのでやはりこのライブラリ自体多用にはお気をつけください）

[退会済みユーザー]

ご回答ありがとうございます
for文でstmt.Execを回すやり方もhttps://github.com/t-tiger/gorm-bulk-insert以前に試してはいたのですが１５件/sでしたのであまり速くないのかなと思っておりました
https://golang.org/pkg/database/sql/#DB.SetMaxOpenConns
をみる限り接続はデフォルトで無制限になっている様ですのでこれが限界になってしまうのでしょうか？流石に遅すぎるのでどこか間違っている気はしているのですが

[nobonobo]

ちなみに、追記したコードとベストアンサーのコードとで同じレコード投入してみた場合、追記したコードの方が5倍ほど早かったです。gormがオーバーヘッドが大きいのもありますが。

[退会済みユーザー]

ご調査ありがとうございます
ctx context.Contextは宣言だけされ使われていないのかなと思ってしまい先ほどは省いて１５件/sと記載してしまったのですが、やり直してみますと5万件のinsertが19秒でできました
ただhttps://github.com/t-tiger/gorm-bulk-insertのやり方ですと5万件4秒となりなぜか私の方はこちらの方が５倍速くなってしまいました

私のコードの記載がないので何ともなのですが、
https://golang.org/pkg/database/sql/#Tx.Prepareとの違いはtx, err := db.Begin()の前に
sql.Open文を書いた事と、projectsスライスの作成ではなく自分のデータのスライスのスライスをfor文で回しstmt.Execに代入していることくらいになります

[nobonobo]

まあ、ここから先はDBMS種類や設置場所、設定やコードが示されてからでないとなんとも言えないですね。

[退会済みユーザー]

確かにそうですね
でも単純にExecをfor文で回すやり方しか知らなかったのでとても参考になりました
ご回答ありがとうございます