《后端架构设计·高性能与高可用》11.2 分页、批处理与连接管理

// file: internal/perf/paging/pagination.go
// tier: L1
// verify: go test ./internal/perf/paging/ -count=1 -v
package paging

import "time"

// Item is one order row. CreatedAt+ID is the unique sort key: two orders can
// share a timestamp, so ID breaks the tie and keeps the cursor total.
type Item struct {
	ID         int64
	CustomerID int64
	CreatedAt  time.Time
	TotalCents int64
}

type Cursor struct {
	CreatedAt time.Time
	ID        int64
}

type Page struct {
	Items  []Item
	Cursor Cursor
	More   bool
}

// NewDataset returns items sorted newest first. The generator is deterministic
// so scan counts in tests do not depend on time or randomness.
func NewDataset(n int) []Item {
	base := time.Date(2026, 10, 1, 0, 0, 0, 0, time.UTC)
	out := make([]Item, n)
	for i := range out {
		out[i] = Item{
			ID:         int64(n - i),
			CustomerID: int64(i%1000 + 1),
			CreatedAt:  base.Add(-time.Duration(i) * time.Second),
			TotalCents: int64(1000 + i%500),
		}
	}
	return out
}

// OffsetPage must skip offset rows before returning anything. The second
// return value is the number of rows a database would examine, not the number
// copied into the page.
func OffsetPage(items []Item, offset, limit int) (Page, int) {
	if offset < 0 {
		offset = 0
	}
	if limit < 1 {
		limit = 1
	}
	start := min(offset, len(items))
	end := min(start+limit, len(items))
	p := Page{Items: append([]Item(nil), items[start:end]...), More: end < len(items)}
	if end > start {
		last := items[end-1]
		p.Cursor = Cursor{CreatedAt: last.CreatedAt, ID: last.ID}
	}
	return p, start + (end - start)
}

// KeysetPage binary-searches the first row older than the cursor, so its cost
// grows with log(n)+limit instead of offset+limit.
func KeysetPage(items []Item, after Cursor, limit int) (Page, int) {
	if limit < 1 {
		limit = 1
	}
	lo, hi, scanned := 0, len(items), 0
	for lo < hi {
		mid := (lo + hi) / 2
		scanned++
		if older(items[mid], after) {
			hi = mid
		} else {
			lo = mid + 1
		}
	}
	end := min(lo+limit, len(items))
	p := Page{Items: append([]Item(nil), items[lo:end]...), More: end < len(items)}
	if end > lo {
		last := items[end-1]
		p.Cursor = Cursor{CreatedAt: last.CreatedAt, ID: last.ID}
	}
	scanned += end - lo
	return p, scanned
}

func older(it Item, c Cursor) bool {
	if c.CreatedAt.IsZero() {
		return true // zero cursor means "before the first page"
	}
	if it.CreatedAt.Equal(c.CreatedAt) {
		return it.ID < c.ID
	}
	return it.CreatedAt.Before(c.CreatedAt)
}

n=200000 offset=100000 limit=20 offset_scanned=100020 keyset_scanned=37

// file: internal/perf/paging/batch.go
// tier: L1
// verify: go test ./internal/perf/paging/ -count=1 -v
package paging

import (
	"errors"
	"fmt"
	"regexp"
	"strings"
	"time"
)

type Customer struct {
	ID   int64
	Name string
}

// Store is an in-memory stand-in for the order and customer tables. Queries
// counts logical round trips so an N+1 pattern is measurable without a DB.
type Store struct {
	items     []Item
	customers map[int64]Customer
	Queries   int
}

func NewStore(items []Item) *Store {
	customers := make(map[int64]Customer, 1000)
	for i := int64(1); i <= 1000; i++ {
		customers[i] = Customer{ID: i, Name: fmt.Sprintf("customer-%d", i)}
	}
	return &Store{items: items, customers: customers}
}

func (s *Store) OrdersByCustomer(cid int64) []Item {
	s.Queries++
	var out []Item
	for _, it := range s.items {
		if it.CustomerID == cid {
			out = append(out, it)
		}
	}
	return out
}

func (s *Store) Customer(id int64) Customer {
	s.Queries++
	return s.customers[id]
}

func (s *Store) CustomersByIDs(ids []int64) map[int64]Customer {
	s.Queries++
	out := make(map[int64]Customer, len(ids))
	for _, id := range ids {
		if c, ok := s.customers[id]; ok {
			out[id] = c
		}
	}
	return out
}

type OrderView struct {
	OrderID  int64
	Customer Customer
}

// LoadNaive issues one customer query per order: the classic N+1.
func LoadNaive(s *Store, cid int64) []OrderView {
	orders := s.OrdersByCustomer(cid)
	out := make([]OrderView, 0, len(orders))
	for _, o := range orders {
		out = append(out, OrderView{OrderID: o.ID, Customer: s.Customer(o.CustomerID)})
	}
	return out
}

// LoadBatch keeps the order query and replaces N customer queries with one
// IN-style lookup. Two round trips regardless of N.
func LoadBatch(s *Store, cid int64) []OrderView {
	orders := s.OrdersByCustomer(cid)
	ids := make([]int64, 0, len(orders))
	for _, o := range orders {
		ids = append(ids, o.CustomerID)
	}
	byID := s.CustomersByIDs(ids)
	out := make([]OrderView, 0, len(orders))
	for _, o := range orders {
		out = append(out, OrderView{OrderID: o.ID, Customer: byID[o.CustomerID]})
	}
	return out
}

var reValue = regexp.MustCompile(`'[^']*'|\b\d+\b`)

// Normalize replaces literals so "WHERE id = 7" and "WHERE id = 8" share a
// shape. It is intentionally simple and does not parse SQL.
func Normalize(query string) string { return reValue.ReplaceAllString(query, "?") }

type Detector struct {
	limit int
	count map[string]int
	order []string
}

func NewDetector(limit int) *Detector {
	if limit < 1 {
		limit = 1
	}
	return &Detector{limit: limit, count: make(map[string]int)}
}

func (d *Detector) Record(query string) {
	k := Normalize(query)
	if _, ok := d.count[k]; !ok {
		d.order = append(d.order, k)
	}
	d.count[k]++
}

// Repeated returns shapes seen at least limit times in the current request.
func (d *Detector) Repeated() map[string]int {
	out := make(map[string]int)
	for _, k := range d.order {
		if d.count[k] >= d.limit {
			out[k] = d.count[k]
		}
	}
	return out
}

var (
	ErrLimitTooLarge = errors.New("paging: limit exceeds 100")
	ErrOffsetTooDeep = errors.New("paging: offset exceeds 10000, use keyset")
)

// ValidateListParams is the order-list API contract. Deep offsets are rejected
// instead of silently becoming slower every page.
func ValidateListParams(limit, offset int) error {
	if limit < 1 || limit > 100 {
		return ErrLimitTooLarge
	}
	if offset < 0 || offset > 10000 {
		return ErrOffsetTooDeep
	}
	return nil
}

// Budget ties batch size, connection pool, timeout and retries together. A
// valid plan keeps retries inside the request budget and every concurrent
// batch inside the pool.
type Budget struct {
	PoolSize      int
	Workers       int
	QueryTimeout  time.Duration
	RequestBudget time.Duration
	Retries       int
	BatchSize     int
	Concurrency   int
}

func (b Budget) Validate() []error {
	var errs []error
	if b.PoolSize < 1 {
		errs = append(errs, errors.New("paging: pool size must be positive"))
	}
	if b.Workers < 1 || (b.PoolSize > 0 && b.Workers > 4*b.PoolSize) {
		errs = append(errs, errors.New("paging: workers exceed 4x pool size"))
	}
	if b.QueryTimeout <= 0 || b.RequestBudget <= 0 {
		errs = append(errs, errors.New("paging: timeouts must be positive"))
	}
	if b.Retries < 0 || b.Retries > 3 {
		errs = append(errs, errors.New("paging: retries must be within 0..3"))
	}
	if b.QueryTimeout*time.Duration(b.Retries+1) > b.RequestBudget {
		errs = append(errs, errors.New("paging: retries cannot fit request budget"))
	}
	if b.BatchSize < 1 || b.BatchSize > 500 {
		errs = append(errs, errors.New("paging: batch size must be within 1..500"))
	}
	if b.Concurrency > 0 && b.BatchSize > 0 && b.Concurrency*b.BatchSize > b.PoolSize {
		errs = append(errs, errors.New("paging: concurrent batches exceed pool"))
	}
	return errs
}

// Chunks splits IDs into batches; the last batch may be shorter.
func Chunks(ids []int64, size int) [][]int64 {
	if size < 1 {
		size = 1
	}
	var out [][]int64
	for len(ids) > 0 {
		n := min(size, len(ids))
		out = append(out, append([]int64(nil), ids[:n]...))
		ids = ids[n:]
	}
	return out
}

func JoinQuery(ids []int64) string {
	parts := make([]string, len(ids))
	for i, id := range ids {
		parts[i] = fmt.Sprint(id)
	}
	return "SELECT id, name FROM customers WHERE id IN (" + strings.Join(parts, ",") + ")"
}

T × (R + 1) ≤ B          重试链不能超过上游预算
C × S ≤ P                每个并发批占用一个连接
S ≤ 500                  IN 列表和单次事务的上限
R ≤ 3                    重试预算的上限