10万行报表导出优化实战：从 OFFSET 到游标分页

// MemberListExport 会员列表导出
func (s *LpjService) MemberListExport(c *gin.Context, param *MemberListDto) {
	user, ok := repository.Jwt.GetJwtData(c)
	if !ok || user == nil {
		bzgin.BadRequestResponse(c, "用户信息异常")
		return
	}

	filterFunc := s.memberListFilterFunc(param)

	// 会员汇总信息
	memberSummary, err := s.memberDomain.GetMemberSummary(func(tx *gorm.DB) *gorm.DB {
		tx = s.memberListSearchFilterFunc(param)(tx)
		return tx.Unscoped().Scopes(filterFunc, s.sysUserMemberFilter(user)).Order("`id` DESC")
	})

	if err != nil {
		bzgin.ErrorResponse(c, "获取会员汇总信息失败", err.Error())
	}

	// 流式对象初始化
	file := excelize.NewFile()
	sw, err := file.NewStreamWriter("Sheet1")
	if err != nil {
		bzgin.ErrorResponse(c, "创建流对象失败")
	}
	colNames := []any{"ID", "昵称", "注册时间", "最后登录时间", "地域", "设备端", "VIP级别", "总充值", "金币数", "积分数", "代理ID", "代理昵称", "推广员ID", "推广员昵称"}
	sw.SetRow("A1", colNames)

	// 并发任务
	type memberChunk struct {
		rowData [][]any
		page    int
	}
	chunkSize := 10000                                                   // 数据块大小
	chunkCount := (int(memberSummary.Count) + chunkSize - 1) / chunkSize // 数据块个数
	memberCh := make(chan memberChunk, chunkCount)

	var wg sync.WaitGroup
	wg.Add(chunkCount)
	// 生产者
	for i := 0; i < chunkCount; i++ {
		go func(currentPage int) {
			defer wg.Done()
			list, _ := s.memberDomain.GetMembersWithLastPayTimeInPaginate(func(tx *gorm.DB) *gorm.DB {
				tx = tx.Debug()
				tx = s.memberListSearchFilterFunc(param)(tx)
				tx = tx.Unscoped().
					Select("long_id,wx_nickname,created_at,last_online_at,address_last,user_agent_last,vip,pay_count,coin,score,agent_id,promoter_id").
					Scopes(filterFunc, s.sysUserMemberFilter(user))
				return tx.Order("`id` DESC")
			}, currentPage, chunkSize)
			data := list.Data.([]*model.MemberWithIDNumberAndAgentPromoter)
			fmt.Println("len(data)", len(data))
			startRow := int64(2 + currentPage*chunkSize) // 每个分页对应 Excel 的起始行
			fmt.Println("startRow", startRow)

			rowData := make([][]any, len(data))
			for idx, m := range data {
				row := []any{
					m.LongId,     // ID
					m.WxNickname, // 昵称
					m.CreatedAt.Format("2006-01-02 15:04:05"),    // 注册时间
					m.LastOnlineAt.Format("2006-01-02 15:04:05"), // 最后登录时间
					m.AddressLast,         // 地域
					m.UserAgentLast,       // 设备端
					m.Vip,                 // VIP级别
					m.PayCount,            // 总充值
					m.Coin,                // 金币数
					m.Score,               // 积分数
					m.AgentId,             // 代理ID
					m.Agent.WxNickname,    // 代理昵称
					m.PromoterId,          // 推广员ID
					m.Promoter.WxNickname, // 推广员昵称
				}
				rowData[idx] = row
			}
			memberCh <- memberChunk{rowData: rowData, page: currentPage}
		}(i)
	}

	wg.Wait()
	close(memberCh)

	// 消费者, 并发写Excel不安全，放在主goroutine串行处理
	for mc := range memberCh {
		rowStart := 2 + mc.page*chunkSize
		for idx, row := range mc.rowData {
			sw.SetRow(fmt.Sprintf("A%d", rowStart+idx), row)
		}
	}
	// 将缓冲写入excel
	if err := sw.Flush(); err != nil {
		bzgin.ErrorResponse(c, "缓冲写入excel失败", err.Error())
	}

	// 设置响应头，提示浏览器下载
	c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	c.Header("Content-Disposition", "attachment; filename=report.xlsx")
	c.Header("Content-Transfer-Encoding", "binary")

	// 把 Excel 内容写入响应
	if err := file.Write(c.Writer); err != nil {
		bzgin.ErrorResponse(c, "导出失败", err.Error())
		return
	}
}

// 优化：使用 lastId 作为子查询条件，避免大 OFFSET 性能问题
var lastId uint
if currentPage > 0 {
    // 只在第一页之后查找 lastId
    subQuery := s.deviceDB.Engine.Table("member").
    Select("id").
    Scopes(func(tx *gorm.DB) *gorm.DB {
        tx = s.memberListSearchFilterFunc(param)(tx)
        return tx.Unscoped().Scopes(filterFunc, s.sysUserMemberFilter(user))
    }).
    Order("`id`").
    Limit(1).
    Offset(currentPage * chunkSize - 1)
    if err := subQuery.Pluck("id", &lastId).Error; err != nil {
        bzgin.ErrorResponse(c, "lastId获取失败", err.Error())
        return
    }
}

query := s.deviceDB.Engine.Debug().Table("member").
		Select("long_id,member.wx_nickname,member.created_at,member.last_online_at,member.address_last,member.user_agent_last,member.vip,member.pay_count,member.coin,member.score,member.agent_id,s1.wx_nickname AS agent_wx_nickname,member.promoter_id,s2.wx_nickname AS promoter_wx_nickname").
Joins("LEFT JOIN cloud_game_admin.sys_user AS s1 ON member.agent_id = s1.user_id").
Joins("LEFT JOIN cloud_game_admin.sys_user AS s2 ON member.promoter_id = s2.user_id").
Scopes(func(tx *gorm.DB) *gorm.DB {
    tx = s.memberListSearchFilterFunc(param)(tx)
    return tx.Unscoped().Scopes(filterFunc, s.sysUserMemberFilter(user))
}).
Order("`id`").
Limit(chunkSize)

if currentPage > 0 && lastId > 0 {
    query = query.Where("member.id > ?", lastId)
}