关于深分页和COUNT(*)优化的性能优化

深分页

在分页查询中，如果偏移量很大，如LIMIT 10 OFFSET 1000000，MySQL会按 ORDER BY 顺序逐条生成前 1000010 行，把前 100 万行扔掉，返回最后 10 条。

• 有排序索引时：沿 B+ 树顺序扫 100 万个索引项；如果 SELECT 的列不全在索引里，每行还要回表一次(拿主键去聚簇索引取整行)。也就是 100 万次索引遍历 + 100 万次回表，全是为了扔掉。
• 没有合适索引时：全表拉出来做 filesort,排完再扔，更惨。
• 所以第 1 页 5ms、第 10 万页可能 5 秒，耗时随 offset 线性增长。而且扫描的海量页还会把 buffer pool 里的热点数据挤出去，伤的不只是这一条查询。

优化

1. 使用游标分页

   把“页码”换成“上一页最后一条的位置”：

   SELECT id, name, created_at
   FROM ads
   WHERE id < ?          -- 上一页最后一条的 id
   ORDER BY id DESC
   LIMIT 10;
   

   原理:id < ? 让优化器在 B+ 树上直接二分定位(O(log n)),然后顺序读 10 条就结束。成本与页深无关，第 1 页和第 100 万页一样快。

   Go 侧的典型封装：

   func (r *AdRepository) PageAdsByCursor(ctx context.Context, cursor int64, limit int) ([]model.Ad, int64, error) {
   	if limit < 1 || limit > 100 {
   		limit = 20
   	}
   	// cursor=0 表示第一页
   	rows, err := r.db.QueryContext(ctx, `
   		SELECT id, name, campaign_id FROM ads
   		WHERE (? = 0 OR id < ?)
   		ORDER BY id DESC
   		LIMIT ?`, cursor, cursor, limit+1) // 多取一条,顺便判断有没有下一页
   	if err != nil {
   		return nil, 0, err
   	}
   	defer rows.Close()
   	// ... Scan 略
   
   	nextCursor := int64(0)
   	if len(items) > limit { // 取到了 limit+1 条 → 还有下一页
   		items = items[:limit]
   		nextCursor = items[limit-1].ID
   	}
   	return items, nextCursor, nil
   }
   

   使用限制：

   • 只能“上一页/下一页”，不能跳到任意页码。无限滚动、信息流、后台“加载更多”都适合；报表类“共 87 页、跳到第 45 页”不适合，那种场景用延迟关联缓解。

   • 排序列必须唯一且建了索引。按非唯一列排序(比如 created_at,同一秒可能有多条)要加 id 做复合排序键，否则会漏行/重行：

     WHERE created_at < ?
        OR (created_at = ? AND id < ?)
     ORDER BY created_at DESC, id DESC
     LIMIT 10
     

2. 延迟关联

   SELECT a.*
   FROM ads a
   JOIN (
       SELECT id FROM ads
       ORDER BY id DESC
       LIMIT 10 OFFSET 2000000
   ) t ON a.id = t.id;
   

   子查询只查 id,排序索引能覆盖它，所以扫那 200 万行时全程不回表，纯索引扫描；最后只对选中的 10 条回表取整行。

   不过上面例子id 通常是主键，聚簇索引本身就带着整行，原查询并不回表，延迟关联的收益缩水成“扫窄的二级索引而不是宽的聚簇索引”(仍有用，但没那么戏剧化)。真正拉开差距的是按二级索引列排序的场景，比如：

   SELECT a.*
   FROM ads a
   JOIN (
       SELECT id FROM ads
       ORDER BY created_at DESC      -- created_at 上有二级索引
       LIMIT 10 OFFSET 2000000
   ) t ON a.id = t.id;
   

   复杂度仍是 O(offset),但每行成本低一个量级，实践中常见 5~10 倍提升。思路是“先用覆盖索引把主键找出来，再取数据”，所以叫延迟关联。

3. 产品层面

   限制最大可翻页数(Google 也只让你翻到约 1000 条)，或强制时间范围筛选，让用户根本到不了深页。

COUNT(*)

在分页中检查是否含有下一页，或者要显示“共 N 条/共 N 页”，以及粉丝数及收藏等，最简单的方法是使用count直接进行统计，但这样做对于查询会变得很慢。

• InnoDB 因为 MVCC 做不到不同事务看到的行集不同(有未提交的插入、带删除标记的行)，没法维护一个全局精确计数，只能把全表扫一遍。
• 无 WHERE 的 COUNT(*):优化器挑一棵最小的二级索引整棵扫。1 亿行的表，哪怕走最窄的索引也是几百万个 16KB 页的 IO,查询的时间需要几百毫秒到几秒。
• 有 WHERE:每行还得判断条件，更慢。
• 最浪费的一点：总数几乎不变，却在每次翻页时重算。用户翻了 10 页，同一个 COUNT 就会白跑 10 次。

优化

1. 用hasMore方法

   在无限滚动或者加载更多的场景下，只需要在分页查询时多查一条数据即可，上面游标分页代码里的 LIMIT limit+1 就是这个技巧：用多取的那条 O(1) 成本，换掉整个 COUNT。这是最优先考虑的方案。

2. 缓存总数

   在需要显示“共 N 条/共 N 页”、但几秒误差可接受时：

   total, err := cache.GetOrSet(ctx, "ads:count:"+filterHash, 10*time.Second,
   	func() (int64, error) { return realCount(ctx) })
   

   按筛选条件组合做 key,TTL 设 5~30 秒。这样虽然也要查，但不是每次翻页都查，极大减少了次数。

3. 近似值

   使用information_schema.TABLES.TABLE_ROWS，其在 InnoDB 下是采样统计值(误差 10%~50%),但查询毫秒级返回。适合“约 1,234 万条”这种展示场景，不适合分页计算。

4. 计数表

   创建单独一张计数表，在业务写入的同一事务里维护：

   INSERT INTO ad_counters(ad_id, cnt) VALUES(?, 1)
   ON DUPLICATE KEY UPDATE cnt = cnt + 1;
   

   这样在需要精确数据的情况下能提高查询性能，代价是每次写多一次更新，而且高并发下热点行会锁竞争(可以用 10 行分片计数、查询时 SUM 再缓存来摊薄)。适合“粉丝数”“收藏数”这类必须实时精确的数字。

   在高并发场景下可以加上分片计数

   -- 1. 分片表结构：ad_counter_shard
   -- ad_id, shard_id(0-9), cnt
   
   -- 2. 写入时：随机更新某一个分片
   INSERT INTO ad_counter_shard (ad_id, shard_id, cnt) 
   VALUES (?, FLOOR(RAND() * 10), 1) 
   ON DUPLICATE KEY UPDATE cnt = cnt + 1;
   
   -- 3. 查询总数时：求和（走覆盖索引，极快）
   SELECT SUM(cnt) FROM ad_counter_shard WHERE ad_id = ?;
   

   • 代价：查询总数时不再是直接读 1 行，而是读 10 行并求和。但 10 行对 MySQL 来说微不足道，完全可以接受。
   • 边界：如果并发量再大 10 倍（比如 1 万并发），你可以把 10 片改成 100 片，锁竞争进一步摊薄。但分片太多（比如 1000 片），查询时就要 Sum 1000 行，会变慢，所以一般 10~20 片是性价比最高的甜蜜点。

总结

深分页和 COUNT(*) 的性能瓶颈，本质上都是 MySQL 在大偏移量或大表统计时需要扫描大量数据页，导致 IO 和 CPU 开销随数据量线性增长。优化的核心思路是 “用索引定位代替全量扫描” 和 “避免重复计算”。

针对深分页：

• 优先使用 游标分页（基于唯一索引条件过滤），将复杂度从 O(offset) 降为 O(log n)，适用于无限滚动、加载更多等常见交互；若排序列非唯一，需补充唯一键构成复合排序条件，避免漏行/重行。
• 若必须支持跳页且深分页频繁，可采用 延迟关联，利用覆盖索引先取主键再回表，减少回表开销，虽然仍为线性扫描，但单行成本大幅降低，实践中能获得数倍提升。
• 产品侧应 限制最大页码 或强制时间范围筛选，从源头切断深分页场景，避免数据库承担无效负载。

针对 COUNT(*)：

• 在“只需判断有无下一页”的场景，用 LIMIT n+1 的 hasMore 技巧替代 COUNT，成本最低且最直接。
• 需要显示总数且允许短时误差时，使用 缓存（按筛选条件组合 key，TTL 5~30 秒）或 统计近似值（information_schema.TABLES.TABLE_ROWS），牺牲精度换取毫秒级响应。
• 需要实时精确计数（如粉丝数、收藏数）时，引入 计数表，在业务事务中同步维护；高并发下采用 分片计数（10~20 片）摊薄锁竞争，查询时 SUM 各分片，兼顾实时性与性能。

整体原则：

• 优先在 业务层 规避重查询（如游标、hasMore），而非依赖数据库优化。
• 其次在 数据库层 用覆盖索引、延迟关联等技巧降低单次查询成本。
• 最后结合 缓存或计数表 满足实时性要求，同时注意分片粒度与查询代价的平衡。
• 所有优化需结合具体业务场景（是否支持跳页、实时性要求、并发量、数据增长率）进行取舍，没有银弹，只有最合适的组合。