获取嵌套JSON结果的最佳实践：如何解决N+1查询问题？

需要生成嵌套结构的JSON结果，样本JSON如下：

{"total_result" : 25,"questions" : [{"id" : 1,"text" : "the question of user 1 here","user" : {"id" : 5,"name" : "user 5",},"answers" : [{"id" : 5,"text" : "first answer to user 1 question","user" : {"id" : 10,"name" : "user 10",}},{"id" : 6,"text" : "second answer to user 1 question","user" : {"id" : 11,"name" : "user 11",}},{"id" : 10,"text" : "third answer to user 1 question","user" : {"id" : 12,"name" : "user 12",}}]},{"id" : 2,"text" : "the question by user 2 here","user" : {"id" : 6,"name" : "user 6",},"answers" : [{"id" : 5,"text" : "first answer to user 2 question","user" : {"id" : 30,"name" : "user 30",}},{"id" : 6,"text" : "second answer to user 2 question","user" : {"id" : 20,"name" : "user 20",}},{"id" : 10,"text" : "third answer to user 2 question","user" : {"id" : 1,"name" : "user 1",}}]}]}

对应的Go结构体定义：

type Question struct {
  Id int 
  Text string 
  User User 
  Answers []Answer 
}
type User struct {
  Id int 
  Name string 
}
type Answer struct {
  Id int 
  Text string 
  User User 
}

当前实现步骤：

1. 查询questions及对应的user_id，收集用户ID列表：

query := "select text, user_id from questions limit 10 offset 10"
rows, err := db.QueryCtx(ctx, query)
// 处理错误

var questions []Question
var userIds []string
for rows.Next() {
  var q Question
  var userId string
  // 将结果扫描到`question`和`userId`
  questions = append(questions, q)
  userIds = append(userIds, userId) // 修正原代码笔误
}

2. 批量查询获取提问用户信息：

query = "select id, name from users where id = any($1)" // 修正原代码漏查id问题
userRows, err := db.QueryCtx(ctx, query, pq.Array(userIds))
// 处理错误
var users []User
// 扫描结果到users

3. 循环查询每个问题对应的answers及回答用户信息，填充结构体：

query = "select answers.id, answers.text, u.id, u.name from answers join users as u on u.id=answers.user_id where answers.question_id=$1" // 移除冗余join
for i := 0; i < len(questions); i++{
  rowAnswer, err := db.QueryCtx(ctx, query, questions[i].Id)
  // 处理错误
  var answers []Answer
  for rowAnswer.Next(){
    var answer Answer
    // 扫描结果到answer
    answers = append(answers, answer)
  }
  questions[i].User = users[i] // 存在顺序不匹配风险
  questions[i].Answers = answers
}

数据库表结构：

users表 #

questions表 #

answers表 #

当前代码能正常运行并得到正确结果，但存在N+1查询问题（循环查询answers），请问该实现是否合理？有没有优化建议？

实现合理性评价 #

当前实现能满足基础功能需求，但存在明显性能隐患和逻辑瑕疵：

• 循环查询answers属于典型的N+1问题，当questions数量较多时，会发起大量数据库请求，大幅增加接口延迟和数据库负载
• 原代码存在细节错误：收集userIds时的笔误、批量查询用户漏查id、通过索引直接取users[i]可能因数据库返回顺序与questions顺序不匹配导致用户信息关联错误

优化建议 #

1. 批量查询所有answers及关联用户（推荐） #

将循环查询answers改为一次批量查询，获取当前所有questions对应的answers和回答用户信息，再通过内存映射关联到对应问题，彻底解决N+1问题。

示例代码：

// 步骤1：查询questions，记录问题ID、内容、提问用户ID，同时用map存储问题指针方便后续修改
query := "select id, text, user_id from questions limit 10 offset 10"
rows, err := db.QueryCtx(ctx, query)
if err != nil {
    // 处理错误
}
defer rows.Close()

var questions []Question
var questionIds []int
var allUserIds []string
questionMap := make(map[int]*Question)

for rows.Next() {
    var q Question
    var userId int
    if err := rows.Scan(&q.Id, &q.Text, &userId); err != nil {
        // 处理错误
    }
    q.User.Id = userId
    questions = append(questions, q)
    questionMap[q.Id] = &questions[len(questions)-1]
    questionIds = append(questionIds, q.Id)
    allUserIds = append(allUserIds, strconv.Itoa(userId))
}

// 步骤2：批量查询所有回答用户ID，合并到总用户ID列表
answerUserQuery := "select distinct user_id from answers where question_id = any($1)"
answerRows, err := db.QueryCtx(ctx, answerUserQuery, pq.Array(questionIds))
if err != nil && err != sql.ErrNoRows {
    // 处理错误
}
defer answerRows.Close()

for answerRows.Next() {
    var userId int
    if err := answerRows.Scan(&userId); err != nil {
        // 处理错误
    }
    allUserIds = append(allUserIds, strconv.Itoa(userId))
}

// 去重用户ID，避免重复查询
uniqueUserIds := []string{}
userSet := make(map[string]bool)
for _, id := range allUserIds {
    if !userSet[id] {
        userSet[id] = true
        uniqueUserIds = append(uniqueUserIds, id)
    }
}

// 步骤3：批量查询所有涉及的用户信息，存入map
userQuery := "select id, name from users where id = any($1)"
userRows, err := db.QueryCtx(ctx, userQuery, pq.Array(uniqueUserIds))
if err != nil {
    // 处理错误
}
defer userRows.Close()

userMap := make(map[int]User)
for userRows.Next() {
    var u User
    if err := userRows.Scan(&u.Id, &u.Name); err != nil {
        // 处理错误
    }
    userMap[u.Id] = u
}

// 步骤4：批量查询所有answers，关联到对应问题
answerListQuery := "select id, text, question_id, user_id from answers where question_id = any($1)"
finalAnswerRows, err := db.QueryCtx(ctx, answerListQuery, pq.Array(questionIds))
if err != nil {
    // 处理错误
}
defer finalAnswerRows.Close()

for finalAnswerRows.Next() {
    var ans Answer
    var questionId, userId int
    if err := finalAnswerRows.Scan(&ans.Id, &ans.Text, &questionId, &userId); err != nil {
        // 处理错误
    }
    ans.User = userMap[userId]
    if q, ok := questionMap[questionId]; ok {
        q.Answers = append(q.Answers, ans)
    }
}

// 填充提问用户信息
for i := range questions {
    questions[i].User = userMap[questions[i].User.Id]
}

2. 单次JOIN查询所有数据（适合小数据量场景） #

如果返回的questions和answers数量较少，可以通过一次JOIN查询获取所有数据，再在内存中去重组装结构体：

query := `
SELECT 
    q.id as q_id, q.text as q_text, q.user_id as q_user_id,
    u_q.name as q_user_name,
    a.id as a_id, a.text as a_text, a.user_id as a_user_id,
    u_a.name as a_user_name
FROM questions q
LEFT JOIN users u_q ON q.user_id = u_q.id
LEFT JOIN answers a ON q.id = a.question_id
LEFT JOIN users u_a ON a.user_id = u_a.id
WHERE q.id = any($1)
ORDER BY q.id, a.id
`
rows, err := db.QueryCtx(ctx, query, pq.Array(questionIds))
if err != nil {
    // 处理错误
}
defer rows.Close()

questionMap := make(map[int]*Question)
var questions []Question

for rows.Next() {
    var qId int
    var qText string
    var qUserId int
    var qUserName string
    var aId *int
    var aText *string
    var aUserId *int
    var aUserName *string

    err := rows.Scan(&qId, &qText, &qUserId, &qUserName, &aId, &aText, &aUserId, &aUserName)
    if err != nil {
        // 处理错误
    }

    // 获取或创建问题实例
    q, ok := questionMap[qId]
    if !ok {
        q = &Question{
            Id:   qId,
            Text: qText,
            User: User{Id: qUserId, Name: qUserName},
            Answers: []Answer{},
        }
        questionMap[qId] = q
        questions = append(questions, *q)
    }

    // 若存在回答，添加到问题的回答列表
    if aId != nil && aText != nil && aUserId != nil && aUserName != nil {
        ans := Answer{
            Id:   *aId,
            Text: *aText,
            User: User{Id: *aUserId, Name: *aUserName},
        }
        q.Answers = append(q.Answers, ans)
    }
}

这种方式仅需一次数据库查询，但返回结果会包含重复的question数据，需在内存中去重，适合数据量较小的场景。

3. 其他细节优化 #

• 使用map存储用户和问题，避免依赖索引顺序查找，防止因数据库返回顺序不一致导致的关联错误
• 及时调用rows.Close()，避免数据库连接资源泄漏
• 针对sql.ErrNoRows等特殊错误做针对性处理
• 批量查询时使用any($1)配合数组参数，避免SQL拼接带来的注入风险

内容的提问来源于stack exchange，提问作者Cakrawala