Golang在for循环中执行MySQL查询性能缓慢问题探讨

问题出在N+1查询模式上。每次循环都执行两次独立的数据库查询，导致性能急剧下降。应该使用JOIN或批量查询来优化。

优化方案：使用JOIN查询一次性获取所有数据：

query := `
    SELECT 
        p.id,
        p.b64,
        p.title,
        p.description,
        p.total_likes,
        p.total_favorites,
        p.published_date,
        CASE WHEN f.postID IS NOT NULL THEN 'yes' ELSE 'no' END as favorited,
        CASE WHEN l.postID IS NOT NULL THEN 'yes' ELSE 'no' END as likedPost
    FROM posts p
    LEFT JOIN favorites f ON p.id = f.postID 
        AND f.userID = ? 
        AND f.status = 'added'
    LEFT JOIN likes l ON p.id = l.postID 
        AND l.userID = ? 
        AND l.status = 'liked'
    ORDER BY p.id DESC 
    LIMIT 6`

rows, err := Config.DB.Query(query, userID, userID)
if err != nil {
    fmt.Println(err)
    panic(err.Error())
}
defer rows.Close()

posts := make([]*MembersModel.Post, 0, 6)
for rows.Next() {
    var postID int
    var b64 string
    var title string
    var description string
    var total_likes int
    var total_favorites int
    var published_date string
    var favorited string
    var likedPost string
    
    err := rows.Scan(&postID, &b64, &title, &description, 
        &total_likes, &total_favorites, &published_date,
        &favorited, &likedPost)
    if err != nil {
        fmt.Println(err)
        continue
    }
    
    post := &MembersModel.JobList{
        PostID:      b64,
        Title:       title,
        Description: description,
        Likes:       total_likes,
        PubDate:     published_date,
        Bookmarked:  favorited,
        Liked:       likedPost,
    }
    
    posts = append(posts, post)
}

如果无法使用JOIN，可以使用批量查询：

// 先获取所有帖子
postsRows, err := Config.DB.Query(`
    SELECT id, b64, title, description, total_likes, total_favorites, published_date  
    FROM posts 
    ORDER BY id DESC 
    LIMIT 6`)
if err != nil {
    fmt.Println(err)
    panic(err.Error())
}
defer postsRows.Close()

var postIDs []int
var tempPosts []struct {
    ID            int
    B64           string
    Title         string
    Description   string
    TotalLikes    int
    TotalFavorites int
    PublishedDate string
}

for postsRows.Next() {
    var p struct {
        ID            int
        B64           string
        Title         string
        Description   string
        TotalLikes    int
        TotalFavorites int
        PublishedDate string
    }
    
    err := postsRows.Scan(&p.ID, &p.B64, &p.Title, &p.Description, 
        &p.TotalLikes, &p.TotalFavorites, &p.PublishedDate)
    if err != nil {
        fmt.Println(err)
        continue
    }
    
    postIDs = append(postIDs, p.ID)
    tempPosts = append(tempPosts, p)
}

// 批量查询收藏状态
favoritesMap := make(map[int]bool)
if len(postIDs) > 0 {
    query := "SELECT postID FROM favorites WHERE userID = ? AND postID IN (?" + 
        strings.Repeat(",?", len(postIDs)-1) + ") AND status = 'added'"
    
    args := make([]interface{}, len(postIDs)+1)
    args[0] = userID
    for i, id := range postIDs {
        args[i+1] = id
    }
    
    rows, err := Config.DB.Query(query, args...)
    if err != nil {
        fmt.Println(err)
    } else {
        defer rows.Close()
        for rows.Next() {
            var postID int
            rows.Scan(&postID)
            favoritesMap[postID] = true
        }
    }
}

// 批量查询点赞状态
likesMap := make(map[int]bool)
if len(postIDs) > 0 {
    query := "SELECT postID FROM likes WHERE userID = ? AND postID IN (?" + 
        strings.Repeat(",?", len(postIDs)-1) + ") AND status = 'liked'"
    
    args := make([]interface{}, len(postIDs)+1)
    args[0] = userID
    for i, id := range postIDs {
        args[i+1] = id
    }
    
    rows, err := Config.DB.Query(query, args...)
    if err != nil {
        fmt.Println(err)
    } else {
        defer rows.Close()
        for rows.Next() {
            var postID int
            rows.Scan(&postID)
            likesMap[postID] = true
        }
    }
}

// 组装最终结果
posts := make([]*MembersModel.Post, 0, len(tempPosts))
for _, p := range tempPosts {
    favorited := "no"
    if favoritesMap[p.ID] {
        favorited = "yes"
    }
    
    likedPost := "no"
    if likesMap[p.ID] {
        likedPost = "yes"
    }
    
    post := &MembersModel.JobList{
        PostID:      p.B64,
        Title:       p.Title,
        Description: p.Description,
        Likes:       p.TotalLikes,
        PubDate:     p.PublishedDate,
        Bookmarked:  favorited,
        Liked:       likedPost,
    }
    
    posts = append(posts, post)
}

使用JOIN方案可以将查询次数从13次（1+6×2）减少到1次，性能提升最明显。批量查询方案将查询次数减少到3次，也能显著改善性能。