ある要件があった時、それの実現方法は大抵はいくつか思いつくものです。
ある程度は前提条件などで絞り込んだとしても、1つに絞れるほどでもない、ということもままあります。
そんなときに長年の経験則から導くのも良いですが、結局は計測するのが確実ですよね。
今回は、そんなときを仮定していくつかの選択肢を実際に比較してみる、というエントリ。
（2022年に下書きだけ書いて放置していたやつなので、前提条件だけ今の環境に置き換えています）
仮想要件
- 10万レコードのデータの中から特定の1万件を抽出してCSV出力したい
- 出力するCSVは1ファイルにする
- CSVにヘッダ行は不要
- CSVのソート順は問わない
前提条件
- DBは MySQL 8（docker の
mysql:8イメージ。計測時点では 8.4.11 でした）- INTO OUTFILE は使用できないものとする
- 対象を選択するキーには index が張ってある
- 使用言語は Go 1.26
- 使用ライブラリは database/sql と go-sql-driver/mysql（クエリは生SQLを書く）
- encoding/csv は使わないものとする
- 実行環境は、MySQL がローカルPCの docker、Go のプログラムはホスト側で実行
- マシンは Apple M5（10コア）、メモリ 24GB
- 比較するのは、プログラム開始から終了までの実行時間、CPU時間、最大メモリ使用量
DB側の負荷は見ていません。プログラム側だけ。
事前準備
DB作成
docker run --rm --name mysql -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=db -p 3306:3306 -d \
mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_bin
データ作成
func main() {
db, err := sql.Open("mysql", "root:password@(127.0.0.1:3306)/db")
check(err)
defer db.Close()
// テーブル作成
_, err = db.Exec(`create table if not exists something(
id integer not null auto_increment,
uuid varchar(40) not null unique,
data varchar(100),
meta varchar(20),
primary key(id))`)
check(err)
// データ投入
tx, err := db.Begin()
check(err)
stmt, err := tx.Prepare(`insert into something(uuid, data, meta) values(?, ?, ?)`)
check(err)
for i := 0; i < 100000; i++ {
_, err = stmt.Exec(_uuid(), randStr(100), randStr(20))
check(err)
}
stmt.Close()
check(tx.Commit())
}
ダミーデータを something テーブルに10万件投入する。check は err != nil なら log.Fatal するだけの関数で、_uuid と randStr は適当な実装なので省略。
docker exec mysql mysql -uroot -ppassword db -N -e 'select uuid from something order by rand() limit 10000' > list.txt
適当に1万レコードの uuid を抽出する。ホスト側に mysql クライアントを入れてないので docker exec で。
比較案
- 1件ずつ10,000回クエリを実行して都度CSVに書く
一番ダメだと思われる案。当て馬比較用 - 全件取得してプログラム側でフィルタする
全件数が多くなると破綻するやつ。10万レコードならギリいけるか？ - 対象10,000件をIN句で指定して取得し、CSVに一括出力する
メモリ任せ案 - 500件ずつ分割して並列実行でIN句で取得しCSV出力。最後にファイル結合する
それっぽいけど性能がコア数に依存しそう
なんとなく4案が一番効率的じゃないかなーと思っている。（実施前の感想）
比較コード
共通部分
4案とも同じ io.Writer に書くようにして、引数で案を切り替える作りにしました。こんな感じ。
type row struct {
id int
uuid, data, meta string
}
func readKeys(path string) []string {
b, err := os.ReadFile(path)
check(err)
return strings.Fields(string(b))
}
func writeRow(w io.Writer, r row) {
fmt.Fprintf(w, "%d,%s,%s,%s\n", r.id, r.uuid, r.data, r.meta)
}
// in句のプレースホルダをn個並べたselect文
func inQuery(n int) string {
return `select id, uuid, data, meta from something where uuid in (?` + strings.Repeat(",?", n-1) + `)`
}
func toArgs(keys []string) []any {
args := make([]any, len(keys))
for i, k := range keys {
args[i] = k
}
return args
}
func main() {
keys := readKeys("list.txt")
db, err := sql.Open("mysql", "root:password@(127.0.0.1:3306)/db")
check(err)
db.SetMaxOpenConns(10)
defer db.Close()
f, err := os.Create("out.csv")
check(err)
w := bufio.NewWriter(f)
switch os.Args[1] {
case "1":
case1(db, keys, w)
case "2":
case2(db, keys, w)
case "3":
case3(db, keys, w)
case "4":
case4(db, keys, w)
}
check(w.Flush())
check(f.Close())
}
1. 1件ずつクエリを実行してCSVに追記する案
func case1(db *sql.DB, keys []string, w io.Writer) {
stmt, err := db.Prepare(`select id, uuid, data, meta from something where uuid = ?`)
check(err)
defer stmt.Close()
for _, key := range keys {
var r row
check(stmt.QueryRow(key).Scan(&r.id, &r.uuid, &r.data, &r.meta))
writeRow(w, r)
}
}
2. 全件取得してプログラム側でフィルタする案
func case2(db *sql.DB, keys []string, w io.Writer) {
rows, err := db.Query(`select id, uuid, data, meta from something`)
check(err)
defer rows.Close()
var all []row
for rows.Next() {
var r row
check(rows.Scan(&r.id, &r.uuid, &r.data, &r.meta))
all = append(all, r)
}
check(rows.Err())
set := make(map[string]struct{}, len(keys))
for _, k := range keys {
set[k] = struct{}{}
}
for _, r := range all {
if _, ok := set[r.uuid]; ok {
writeRow(w, r)
}
}
}
3. 対象10,000件をIN句で指定して取得し、CSVに一括出力する案
func case3(db *sql.DB, keys []string, w io.Writer) {
rows, err := db.Query(inQuery(len(keys)), toArgs(keys)...)
check(err)
defer rows.Close()
var result []row
for rows.Next() {
var r row
check(rows.Scan(&r.id, &r.uuid, &r.data, &r.meta))
result = append(result, r)
}
check(rows.Err())
for _, r := range result {
writeRow(w, r)
}
}
4. 500件ずつ分割して並列実行でIN句で取得しCSV出力、最後にファイル結合する案
func case4(db *sql.DB, keys []string, w io.Writer) {
const size = 500
n := (len(keys) + size - 1) / size
var wg sync.WaitGroup
for i := range n {
wg.Add(1)
go func() {
defer wg.Done()
part := keys[i*size : min((i+1)*size, len(keys))]
f, err := os.Create(fmt.Sprintf("part_%02d.csv", i))
check(err)
bw := bufio.NewWriter(f)
rows, err := db.Query(inQuery(len(part)), toArgs(part)...)
check(err)
for rows.Next() {
var r row
check(rows.Scan(&r.id, &r.uuid, &r.data, &r.meta))
writeRow(bw, r)
}
check(rows.Err())
rows.Close()
check(bw.Flush())
check(f.Close())
}()
}
wg.Wait()
for i := range n {
name := fmt.Sprintf("part_%02d.csv", i)
f, err := os.Open(name)
check(err)
_, err = io.Copy(w, f)
check(err)
f.Close()
os.Remove(name)
}
}
1万件を500件ずつなので goroutine は20個。コネクションは共通部分の SetMaxOpenConns(10) で10本までにしています。
計測
最初は /usr/bin/time -l で見ていたんですが、2〜4案が全部 0.03 秒で並んでしまって差が読めない。
なので、プログラム側で time.Since と syscall.Getrusage を出すようにしました。main の最後にこれを足しただけ。
var ru syscall.Rusage
check(syscall.Getrusage(syscall.RUSAGE_SELF, &ru))
fmt.Fprintf(os.Stderr, "%d\t%d\t%d\t%d\n",
time.Since(start).Microseconds(),
ru.Utime.Sec*1000000+int64(ru.Utime.Usec),
ru.Stime.Sec*1000000+int64(ru.Stime.Usec),
ru.Maxrss)
（macOS の Maxrss はバイト単位。Linux だと KB らしいので注意）
各案とも warm-up で1回動かしたあと10回実行して、中央値を取っています。
出力の CSV は毎回 sort して md5 を取り、4案とも同じ内容になっていることは確認済み。
10万件から1万件
| 案 | 実行時間 | CPU時間 (user+sys) | 最大RSS |
|---|---|---|---|
| 1. 1件ずつ | 2007ms | 221ms | 15.5MB |
| 2. 全件取得 | 38ms | 45ms | 49.4MB |
| 3. IN句 | 30ms | 12ms | 16.7MB |
| 4. 分割並列 | 40ms | 29ms | 17.8MB |
1案がダメなのは予想通り。1クエリあたり 0.2ms 程度のラウンドトリップが1万回積み上がった結果です。
で、それ以外は実行時間だと横並び。差が出たのはメモリで、2案は10万件をスライスに溜めているぶん 49MB になっています。逆に3案の「メモリ任せ」は1万件 × 160バイト程度なので、Go のランタイムぶんに埋もれて誤差でした。
おまけ、100万件から1万件
10万件だと差が出なかったので、ついでにデータを100万件に増やして同じことをやってみました。（抽出する1万件は取り直し）
| 案 | 実行時間 | CPU時間 (user+sys) | 最大RSS |
|---|---|---|---|
| 1. 1件ずつ | 2414ms | 254ms | 15.6MB |
| 2. 全件取得 | 559ms | 456ms | 360.6MB |
| 3. IN句 | 219ms | 11ms | 16.5MB |
| 4. 分割並列 | 73ms | 28ms | 17.5MB |
こっちは順当な結果になりました。
2案はデータ量に比例して遅くなるし、メモリも 360MB まで膨らみました。よしよし。1万件を出すために100万行を抱えるわけなので、予想通り。（ちなみに、スライスに溜めずに rows.Next() で1行ずつ読みながらフィルタする書き方も試したら、実行時間はほぼ同じで最大RSSは 17MB でした。溜め込まなければ時間の問題だけになるみたい。）
3案は CPU 時間が10万件のときと変わらないのに実行時間だけ伸びているので、待っているのは DB 側。EXPLAIN を見ると range + Using MRR で、index 自体は効いていました。テーブルがデータ 184MB + index 74MB で、デフォルトのバッファプール 128MB に収まらなくなったせいかなーと思っていますが、これもちゃんと追ってません。
4案はその待ちを10本のコネクションで並列にしているぶん速い、という理解です。
注意
- 3案の IN 句に並べるプレースホルダは 65535 個が上限 でした。65536 個にしたら
Error 1390 (HY000): Prepared statement contains too many placeholdersと怒られる。抽出対象が数万件を超えるなら、4案のように分割するか、DSN にinterpolateParams=trueを付けてクライアント側で文字列に展開することになりそう- go-sql-driver/mysql は
interpolateParamsのデフォルトが false で、引数付きのクエリはサーバ側のプリペアドステートメントになるとのこと
- go-sql-driver/mysql は
- 4案の分割ファイルはヘッダ無し前提なので、結合はただ繋げるだけ。ヘッダありなら先頭ファイル以外の1行目を捨てる処理が要る
- DB 側の CPU やメモリは見ていないので、DB が別サーバでそっちが詰まる、みたいなケースはこの計測では分からない
雑感
実施前の感想は「4案が一番効率的」でしたが、10万件だと実行時間は2〜4案で横並び、100万件でようやく4案が効いてくる、という結果でした。実際に利用する場合、コネクション数やDBとの距離を考慮すると、今回の条件では3案が一番良さそうな気がします。
まあ、こういうのは想像するより測ったほうが良いですね。
ちなみに、今回のコードと計測も Claude Code にやってもらっています。