fix(database/gdb): Fix N+1 query problem and add batch query control #4672

LanceAdd · 2026-02-03T03:32:00Z

问题背景

什么是 N+1 查询问题？

在处理关联数据时，传统的 ORM 查询方式会导致严重的性能问题：

场景示例：

查询 100 个用户
每个用户有多个订单
每个订单有多个订单项

传统查询方式（Legacy Mode）：

1 次查询获取用户
+ 100 次查询获取每个用户的订单
+ N 次查询获取每个订单的订单项
= 总共数百次甚至上千次数据库查询！

这就是经典的 N+1 查询问题，会导致：

❌ 大量数据库往返
❌ 性能急剧下降
❌ 数据库连接池耗尽
❌ 响应时间过长

N+1 查询问题详解

实际案例分析

数据结构：

User (用户)
├── UserDetail (用户详情) - 一对一
└── UserScores (用户分数) - 一对多
    └── ScoreDetails (分数详情) - 一对多
        └── DetailMeta (详情元数据) - 一对多

数据规模：

100 个用户
每个用户 5 个分数
每个分数 4 个详情
每个详情 3 个元数据

Legacy 模式查询次数：

1 (users)
+ 100 (每个 user 查询 scores)
+ 500 (每个 score 查询 details)
+ ~500 (每个 detail 查询 meta，可能分批)
≈ 1,101 次查询！

性能影响：

执行时间：918.60ms
数据库压力：极高
网络往返：上千次

解决方案：Preload 预加载

核心思想

使用 批量查询 + IN 子句 替代逐个查询：

-- 传统方式：N+1 查询
SELECT * FROM users WHERE id = 1;
SELECT * FROM scores WHERE user_id = 1;
SELECT * FROM scores WHERE user_id = 2;
...

-- Preload 方式：批量查询
SELECT * FROM users WHERE id IN (1,2,3,...,100);
SELECT * FROM scores WHERE user_id IN (1,2,3,...,100);

使用分批查询缓解大数据量查询
通过添加TAGchunkName、chunkSize、chunkMinRows 控制批次大小，作为兜底分批查询策略。
通过PreloadOptions(options ...gdb.PreloadOption)动态覆盖精准控制，作为动态分批查询策略

核心功能特性

1. Preload() 方法

启用预加载模式，解决 N+1 问题。

// 启用预加载
db.Model("user").Preload().WithAll().Scan(&users)

2. 智能分批（Chunk）

通过 chunkName、chunkSize、chunkMinRows 控制批次大小。

ORM 标签配置：

type User struct {
    UserScores []*UserScores `orm:"with:uid=id, chunkName:scoreChunk, chunkSize:15, chunkMinRows:8"`
}

API 运行时覆盖：

db.Model("user").
    Preload().
    PreloadOptions(
        gdb.PreloadOption{ChunkName: "scoreChunk", ChunkSize: 20, ChunkMinRows: 10},
    ).
    WithAll().
    Scan(&users)

3. 支持复杂查询条件

每层关联都支持：

✅ where - 过滤条件
✅ order - 排序
✅ unscoped - 包含软删除记录

type User struct {
    UserScores []*UserScores `orm:"with:uid=id, where:score >= 10, order:priority desc, unscoped:true"`
}

4. 多层嵌套支持

支持任意深度的关联嵌套：

User
└── UserScores (Layer 2)
    └── ScoreDetails (Layer 3)
        └── DetailMeta (Layer 4)
            └── ... (更深层级)

使用指南

基础用法

1. 定义结构体

type UserDetail struct {
    gmeta.Meta `orm:"table:user_detail"`
    Uid        int    `json:"uid"`
    Address    string `json:"address"`
}

type UserScores struct {
    gmeta.Meta `orm:"table:user_scores"`
    Id         int `json:"id"`
    Uid        int `json:"uid"`
    Score      int `json:"score"`
}

type User struct {
    gmeta.Meta `orm:"table:user"`
    Id         int             `json:"id"`
    Name       string          `json:"name"`

    // 一对一关系
    UserDetail *UserDetail   `orm:"with:uid=id"`

    // 一对多关系
    UserScores []*UserScores `orm:"with:uid=id"`
}

2. 使用 Preload 查询

var users []*User

// 方式 1：使用 WithAll() 加载所有关联
err := db.Model("user").Preload().WithAll().Scan(&users)

// 方式 2：使用 With() 加载指定关联
err := db.Model("user").Preload().With(UserDetail{}).Scan(&users)

// 方式 3：加载多个指定关联
err := db.Model("user").Preload().With(UserDetail{}, UserScores{}).Scan(&users)

高级用法

1. 添加查询条件

type User struct {
    gmeta.Meta `orm:"table:user"`
    Id         int           `json:"id"`
    Name       string        `json:"name"`

    // 带条件的关联查询
    UserScores []*UserScores `orm:"with:uid=id, where:score >= 60, order:score desc"`
}

2. 配置分批参数

type User struct {
    gmeta.Meta `orm:"table:user"`
    Id         int           `json:"id"`
    Name       string        `json:"name"`

    // 配置分批大小
    UserScores []*UserScores `orm:"with:uid=id, chunkName:scoreChunk, chunkSize:20, chunkMinRows:10"`
}

参数说明：

chunkName: 分批组名称，用于 API 覆盖
chunkSize: 每批查询的记录数
chunkMinRows: 触发分批的最小行数

3. 运行时覆盖分批参数

err := db.Model("user").
    Preload().
    PreloadOptions(
        gdb.PreloadOption{
            ChunkName:    "scoreChunk",
            ChunkSize:    30,      // 覆盖标签中的 20
            ChunkMinRows: 15,      // 覆盖标签中的 10
        },
    ).
    WithAll().
    Scan(&users)

4. 包含软删除记录

type User struct {
    gmeta.Meta `orm:"table:user"`
    Id         int           `json:"id"`
    Name       string        `json:"name"`
    DeletedAt  *gtime.Time   `json:"deleted_at"`

    // unscoped:true 包含软删除的关联记录
    UserScores []*UserScores `orm:"with:uid=id, unscoped:true"`
}

5. 多层嵌套关联

type DetailMeta struct {
    gmeta.Meta `orm:"table:detail_meta"`
    Id         int    `json:"id"`
    DetailId   int    `json:"detail_id"`
    MetaKey    string `json:"meta_key"`
}

type ScoreDetails struct {
    gmeta.Meta `orm:"table:score_details"`
    Id         int            `json:"id"`
    ScoreId    int            `json:"score_id"`
    DetailInfo string         `json:"detail_info"`

    // 第三层关联
    DetailMeta []*DetailMeta `orm:"with:detail_id=id"`
}

type UserScores struct {
    gmeta.Meta `orm:"table:user_scores"`
    Id         int             `json:"id"`
    Uid        int             `json:"uid"`
    Score      int             `json:"score"`

    // 第二层关联
    ScoreDetails []*ScoreDetails `orm:"with:score_id=id"`
}

type User struct {
    gmeta.Meta `orm:"table:user"`
    Id         int           `json:"id"`
    Name       string        `json:"name"`

    // 第一层关联
    UserScores []*UserScores `orm:"with:uid=id"`
}

// 使用 Preload 自动处理多层嵌套
var users []*User
err := db.Model("user").Preload().WithAll().Scan(&users)
// 自动加载：User -> UserScores -> ScoreDetails -> DetailMeta

- 新增Preload方法实现批量关联查询 - 添加软删除支持包括deleted_at字段 - 实现分块查询优化大数据量性能 - 添加preloadOptions配置支持分块大小设置 - 扩展ORM标签支持chunkName、chunkSize、chunkMinRows属性 - 新增四层嵌套预加载测试验证数据正确性 - 优化关联查询性能对比传统N+1模式

gqcn · 2026-02-11T06:19:32Z

@LanceAdd 这个PR超棒👍👍！！但有几点改进建议，欢迎一起讨论：

原有的With特性本身就应该解决N+1问题，所以是否不新增Preloda方法，而是在开启With特性时自动支持N+1问题的解决。这样可以减少理解成本，同时，原有的PreloadOptions改为WithOptions，保持特性名称一致。在源码实现中也不会出现preload的字眼。
WithOptions中可以扩展增加已有标签的实现，就像parseWithTagInFieldStructOutput结构体定义的那样。
PreloadOptions的数据类型定义为map[string]*PreloadOption，光是看这个定义难以理解含义，是否改为map[ChunkName]*PreloadOption，同时定义类型type ChunkName = string，这样更容易理解含义。
本次源码增加了gdb_model_preload.go和gdb_model_struct_cache.go源文件，使得gdb目录下的源文件进一步增加，建议将内部实现的源码放到gdb/internal目录下对应的内部包中，而外部的gdb目录只是引用这些实现即可，以践行高内聚低耦合的设计理念。
其他细节：

单测文件中存在fmt.Println的地方，统一使用t.Log来打印调试信息吧。

我再说一次，这个PR超棒👍👍！！期待一起讨论改进建议，继续完善这个功能！我会持续关注该PR。

Copilot

Pull request overview

This PR introduces a new Preload mode for gdb.Model association loading to address the N+1 query problem by switching from per-parent queries to batched WHERE IN (...) queries, with optional chunking controls via struct tags and runtime options.

Changes:

Add Model.Preload() to enable batch recursive association loading and Model.PreloadOptions() to override chunking behavior at runtime.
Extend orm:"with:..." tag parsing to support chunkName, chunkSize, and chunkMinRows.
Add a multi-layer MySQL test + supporting SQL templates for validating preload correctness and soft-delete/unscoped behavior.

Reviewed changes

Copilot reviewed 11 out of 11 changed files in this pull request and generated 7 comments.

Show a summary per file

File	Description
database/gdb/gdb_model_with.go	Adds `Preload/PreloadOptions`, integrates preload path into `With` scanning, and parses chunk tag options.
database/gdb/gdb_model_preload.go	Implements the new batch preload engine (relation discovery, batch queries, result mapping, recursion).
database/gdb/gdb_model_struct_cache.go	Adds a global struct metadata cache to reduce repeated reflection cost for preload/with.
database/gdb/gdb_model.go	Introduces `preload`, `preloadOptions`, and `PreloadOption` configuration on `Model`.
database/gdb/gdb_func.go	Adds ORM tag constants for chunk controls (`chunkName/chunkSize/chunkMinRows`).
contrib/drivers/mysql/testdata/with_tpl_user_soft_delete.sql	New soft-delete-capable user table template for tests.
contrib/drivers/mysql/testdata/with_tpl_user_scores_soft_delete.sql	New soft-delete-capable scores table template for tests.
contrib/drivers/mysql/testdata/with_tpl_user_detail.sql	Adds `deleted_at` to user detail schema for unscoped tests.
contrib/drivers/mysql/testdata/with_tpl_score_details_soft_delete.sql	New soft-delete-capable score details template for tests.
contrib/drivers/mysql/testdata/with_tpl_detail_meta_soft_delete.sql	New soft-delete-capable detail meta template for tests.
contrib/drivers/mysql/mysql_z_unit_preload_four_layers_comparison_test.go	New 4-layer comparison test for legacy vs preload vs chunk behavior.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

Copilot · 2026-02-11T06:26:51Z

database/gdb/gdb_model_with.go

+	m.preload = true
+	if len(enable) > 0 {
+		model.preload = enable[0]
+	}
+	return m


Preload is mutating the receiver (m.preload = true) instead of the model returned by getModel(). In safe mode this means the returned cloned model won’t have preload enabled, and state can also leak across chains. Set model.preload (defaulting to true when no args) rather than m.preload.

Suggested change

m.preload = true

if len(enable) > 0 {

model.preload = enable[0]

}

return m

if len(enable) > 0 {

model.preload = enable[0]

} else {

model.preload = true

}

return model

Copilot · 2026-02-11T06:26:51Z

database/gdb/gdb_model_with.go

+	m.preload = true
+	if len(enable) > 0 {
+		model.preload = enable[0]
+	}
+	return m


Preload currently returns m, not the (possibly cloned) model from getModel(). This breaks chaining semantics when Safe() is enabled (and is inconsistent with methods like WithAll). Return model so the caller receives the updated configuration.

Suggested change

m.preload = true

if len(enable) > 0 {

model.preload = enable[0]

}

return m

model.preload = true

if len(enable) > 0 {

model.preload = enable[0]

}

return model

Copilot · 2026-02-11T06:26:51Z

database/gdb/gdb_model_with.go

+	// If preload mode is enabled, use the new batch preload logic
+	if m.preload {
+		return m.doPreloadScan(pointer)
+	}


Preload fast-path runs before unwrapping pointer when it is a reflect.Value. ScanList recursion passes reflect.Value (e.g. bindToAttrValue.Addr()), so doPreloadScan will see a non-pointer and fail. Move the reflect.Value -> interface conversion above this m.preload check (or make doPreloadScan accept reflect.Value).

Copilot · 2026-02-11T06:26:52Z

database/gdb/gdb_model_preload.go

+				// Append all elements from this child slice to the merged slice
+				// Since the elements are pointers, modifications to them will be reflected in the original slice
+				for j := 0; j < fieldValue.Len(); j++ {
+					mergedSlice = reflect.Append(mergedSlice, fieldValue.Index(j))
+				}


This merge step appends fieldValue.Index(j) into mergedSlice and assumes elements are pointers (“modifications … will be reflected”). If the relation field is []T (non-pointer elements), this copies values and deeper preloads won’t update the originals. Consider building a []*T slice via Addr() for struct elements (when addressable) to preserve in-place updates.

Copilot · 2026-02-11T06:26:52Z

database/gdb/gdb_model_preload.go

+		// For single type relations, collect all non-nil values into a temporary slice
+		// Get the element type of the pointer field
+		fieldType := relation.Field.Type().Type
+		if fieldType.Kind() != reflect.Pointer {
+			return nil // Not a pointer field, skip


For non-slice relations, recursion is skipped unless the relation field is a pointer (fieldType.Kind() != reflect.Pointer). This means nested relations under a struct-typed association field (e.g. UserDetail UserDetail \orm:"with:..."`) will never be preloaded. Add support for struct (and possibly pointer/struct) relation fields by collecting addressable values and calling recursivePreload` on them.

Copilot · 2026-02-11T06:26:52Z

database/gdb/gdb_model_preload.go

+			// IMPORTANT: Clone the model for each chunk to avoid accumulating WHERE conditions
+			chunkModel := model.Clone()
+			chunkRecords, err := chunkModel.Where(relation.sourceField, chunkValues).All()
+			if err != nil && !errors.Is(err, sql.ErrNoRows) {


queryRelation fetches related rows with All() without restricting columns. Legacy With uses Fields(fieldKeys) to limit selected columns to the related struct’s fields; doing that here would reduce IO/memory and better align with the performance goal of preload.

Copilot · 2026-02-11T06:26:52Z

contrib/drivers/mysql/mysql_z_unit_preload_four_layers_comparison_test.go

+		// Enable debug to see SQL queries
+		oldDebug := db.GetDebug()
+		db.SetDebug(true)
+
+		startTime := time.Now()


This test enables db.SetDebug(true) on a large dataset and relies on console output to count queries. In CI this can produce extremely large logs (especially in legacy/N+1 mode) and slow/flakes the suite. Prefer keeping debug off and counting SELECTs via a HookHandler.Select counter (and assert expected counts/reductions) instead of relying on console output.

LanceAdd added 5 commits February 2, 2026 16:56

refactor(database): fix

d0a62e5

refactor(database): 优化预加载功能中的结构体缓存和方法命名

64c87d1

fix

96f1e05

fix

259826b

gqcn added awesome It's awesome! We keep watching. #👍 labels Feb 11, 2026

gqcn requested a review from Copilot February 11, 2026 06:19

Copilot started reviewing on behalf of gqcn February 11, 2026 06:19 View session

Copilot AI reviewed Feb 11, 2026

View reviewed changes

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

fix(database/gdb): Fix N+1 query problem and add batch query control #4672

fix(database/gdb): Fix N+1 query problem and add batch query control #4672

LanceAdd commented Feb 3, 2026 •

edited

Loading

Uh oh!

gqcn commented Feb 11, 2026

Uh oh!

Copilot AI left a comment

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Copilot AI Feb 11, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

fix(database/gdb): Fix N+1 query problem and add batch query control #4672

Are you sure you want to change the base?

fix(database/gdb): Fix N+1 query problem and add batch query control #4672

Conversation

LanceAdd commented Feb 3, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

问题背景

什么是 N+1 查询问题？

N+1 查询问题详解

实际案例分析

解决方案：Preload 预加载

核心思想

核心功能特性

1. Preload() 方法

2. 智能分批（Chunk）

3. 支持复杂查询条件

4. 多层嵌套支持

使用指南

基础用法

1. 定义结构体

2. 使用 Preload 查询

高级用法

1. 添加查询条件

2. 配置分批参数

3. 运行时覆盖分批参数

4. 包含软删除记录

5. 多层嵌套关联

Uh oh!

gqcn commented Feb 11, 2026

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Feb 11, 2026

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

LanceAdd commented Feb 3, 2026 •

edited

Loading