Gormを使用する際に向き合うMySQLプリペアドステートメントの制限と解決策

はじめまして、バックエンドエンジニアの穴原です。普段はMyTalentという採用MAサービスの開発を担当しています。

MyTalentのバックエンドはGoを利用しており、データベース操作にはGormを利用しています。

GormでMySQLを操作する場合、発行されるクエリはプリペアドステートメントを利用して作成されます。

MySQLのプリペアドステートメントのプレースホルダーの数は65535個までと決まっており、変更もできません。そのため、大量のデータを操作しようとするとこの制限に引っかかり、エラーとなります。

対策としては複数回に分けて処理を行うか、安全な型に限りプレースホルダーを利用しないで実装することです。

今回はGormを利用する際にプリペアドステートメント制限でエラーとなる3つケースとその対策を紹介していきます。

ケース1 大量データのINSERT

エラー

type User struct {
    gorm.Model
    Name string
}

func Insert() {
    sqlDB, _ := sql.Open("mysql", "mydb_dsn")
    defer sqlDB.Close()
    gormDB, _ := gorm.Open(mysql.New(mysql.Config{
        Conn: sqlDB,
    }), &gorm.Config{})

    var users []User
    for i := 0; i < 20000; i++ {
        users = append(users, User{
            Name: fmt.Sprintf("user%d", i),
        },
        )
    }

    // INSERT INTO users (`created_at`,`updated_at`,`deleted_at`, `name`) VALUES (?, ?, ?, ?), (?, ?, ?, ?)......
    gormDB.Save(&users)
}

Save時に実行されるクエリにプレースホルダーが65535個以上利用されてエラーとなります。

このエラーを回避するには以下のように分割してSaveを行うようにします。

分割でSaveを実行してエラー回避する

type User struct {
    gorm.Model
    Name string
}

func Insert() {
    const threshold = 10000

    sqlDB, _ := sql.Open("mysql", "mydb_dsn")
    defer sqlDB.Close()
    gormDB, _ := gorm.Open(mysql.New(mysql.Config{
        Conn: sqlDB,
    }), &gorm.Config{})

    var users []User
    for i := 0; i < 20000; i++ {
        users = append(users, User{
            Name: fmt.Sprintf("user%d", i),
        },
        )
    }

    var (
        page int
        tmp  []User
    )
    for {
        tmp = paginate(users, page, threshold)
        if tmp == nil {
            break
        }
        // INSERT INTO users (`created_at`,`updated_at`,`deleted_at`, `name`) VALUES (?, ?, ?, ?), (?, ?, ?, ?)......
        gormDB.Save(&tmp)
        page++
    }
}

func paginate[T any](slice []T, page, pageSize int) []T {
    start := page * pageSize
    if start >= len(slice) {
        return nil
    }
    end := (page + 1) * pageSize
    if end > len(slice) {
        end = len(slice)
    }
    return slice[start:end]
}

1度のSaveで10000件ずつ登録されるため、Userモデルの場合は40000件のプレースホルダーとなり制限内に収まります。

ケース2 Preloadを利用した大量データの取得

エラー

type User struct {
    gorm.Model
    Name      string
    CompanyID int
    Company   Company
}

type Company struct {
    ID   int
    Name string
}

func PreloadSelect() {
    sqlDB, _ := sql.Open("mysql", "mydb_dsn")
    defer sqlDB.Close()
    gormDB, _ := gorm.Open(mysql.New(mysql.Config{
        Conn: sqlDB,
    }), &gorm.Config{})

    for i := 0; i < 70000; i++ {
        company := Company{
            Name: fmt.Sprintf("company%d", i),
        }
        gormDB.Save(&company)
        user := User{
            Name:      fmt.Sprintf("user%d", i),
            CompanyID: company.ID,
        }
        gormDB.Save(&user)
    }

    var users []User
    // SELECT * FROM users WHERE deleted_at IS NULL;
    // SELECT * FROM companies WHERE id IN (?, ?, ?, ?...) AND deleted_at IS NULL;
    gormDB.Preload("Company").Find(&users)
}

UserデータのリレーションとなるCompanyデータを取得する際にはIN句にプレースホルダーが使用されます。
この例ではUserデータを70000件取得するため、70000件のプレースホルダーを利用されてエラーとなります。

このエラーは以下のようにUserデータ取得を分割して行うことで回避できます。

Userデータを分割で取得してエラー回避する

type User struct {
    gorm.Model
    Name      string
    CompanyID int
    Company   Company
}

type Company struct {
    ID   int
    Name string
}

func PreloadSelect() {
    const limit = 10000

    sqlDB, _ := sql.Open("mysql", "mydb_dsn")
    defer sqlDB.Close()
    gormDB, _ := gorm.Open(mysql.New(mysql.Config{
        Conn: sqlDB,
    }), &gorm.Config{})

    for i := 0; i < 70000; i++ {
        company := Company{
            Name: fmt.Sprintf("company%d", i),
        }
        gormDB.Save(&company)
        user := User{
            Name:      fmt.Sprintf("user%d", i),
            CompanyID: company.ID,
        }
        gormDB.Save(&user)
    }

    var (
        users []User
        page  int
    )
    for {
        var tmp []User
        // SELECT * FROM users WHERE deleted_at IS NULL;
        // SELECT * FROM companies WHERE id IN (?, ?, ?, ?...) AND deleted_at IS NULL;
        gormDB.Preload("Company").Limit(limit).Offset(page * limit).Find(&tmp)
        if len(tmp) == 0 {
            break
        }
        users = append(users, tmp...)
        page++
    }
}

Userデータを10000件ずつ取得するため、Companyデータを取得するためのIN句内は10000件のプレースホルダーとなり、制限内に収まります。

ケース3 IN句に大量の条件を指定して取得

エラー

type User struct {
    gorm.Model
    Name string
}

func INStatement() {
    sqlDB, _ := sql.Open("mysql", "mydb_dsn")
    defer sqlDB.Close()
    gormDB, _ := gorm.Open(mysql.New(mysql.Config{
        Conn: sqlDB,
    }), &gorm.Config{})

    var userIDs []uint
    for i := 0; i < 70000; i++ {
        user := User{
            Name: fmt.Sprintf("user%d", i),
        }
        gormDB.Save(&user)
        userIDs = append(userIDs, user.ID)
    }

    var users []User
    // SELECT * FROM `users` WHERE id IN (?, ?, ?, ?...)
    gormDB.Where("id IN ?", userIDs).Find(&users)
}

GormでIN句を利用する際にはスライスを渡します。

スライスの長さが65535を超えるとプレースホルダーが制限以上に利用されエラーとなります。

このエラーを回避するにはケース2のように分割して取得することで回避できますが、以下のようにクエリ内に直接記載してプレースホルダーを利用しない方法があります。

クエリに直接記載してエラー回避する

func INStatement() {
    sqlDB, _ := sql.Open("mysql", "mydb_dsn")
    defer sqlDB.Close()
    gormDB, _ := gorm.Open(mysql.New(mysql.Config{
        Conn: sqlDB,
    }), &gorm.Config{})

    var userIDs []uint
    for i := 0; i < 70000; i++ {
        user := User{
            Name: "user" + string(i),
        }
        gormDB.Save(&user)
        userIDs = append(userIDs, user.ID)
    }

    var str string
    for i := 0; i < len(userIDs); i++ {
        str = fmt.Sprint(str, userIDs[i], ",")
    }

    var users []User
    // SELECT * FROM `users` WHERE id IN (1, 2, 3, 4...)
    gormDB.Where(fmt.Sprint("id IN (", str[:len(str)-1], ")")).Find(&users)
}

第一引数のクエリ内に記載すればプレースホルダーは利用されず、制限に該当しなくなります。

例ではスライス内の型がintでしたが、string型の場合にSQLインジェクションが発生します。安全な型に限って利用するようにしてください。

まとめ

gormを利用して大量データを操作する際に発生するエラー、その回避策を紹介しました。
大量データを扱うサービスを運用する場合は、実装だけでなくテストで大量データを利用するなど対策をした上で開発・運用を行っていきましょう。

TalentX Tech Blog

Tech Blog