信创数据库下的总页数问题

发表于 分类于

问题

目前业务系统沿袭 Oracle 的习惯，沿袭开发习惯，对查询提供总页数。通过 select count(*) from tab1 where tab1.xxx=yyy 这种形式来获得。
在 Oracle，这个成本还能接受。在 MySQL/PostgreSQL，这个成本大得离谱。

分析

本质是设计问题，针对给出解决方案:

  1. redis记录
  2. 数据库另表记录，但可能无法支持带条件
  3. 彻底废弃总页数，使用流式查询，直到没有为止

深度解析

这是数据库设计和用户体验中一个经典的权衡难题。

问题本质与核心权衡

核心矛盾是：数据的精确性与系统的性能以及用户体验之间的权衡。

• 精确计数：SELECT COUNT(*) 在 MySQL/PostgreSQL 中需要实时扫描索引或表，在数据量大、写操作频繁的表上成本极高。
• 用户体验：总页数提供了可控感和进度感，尤其对于管理员或需要对大量数据进行操作的用户。
• 系统性能：高并发场景下，频繁的 COUNT(*) 可能成为系统瓶颈。

方案评估

方案1：Redis 记录 ✅ 推荐，但有适用范围

– 思路：将计数结果缓存，通过订阅数据库变更（如 Canal、Debezium）来维护计数
– 优点：性能极高，适用于计数需求频繁、实时性要求较高的场景
– 缺点：系统复杂度增加，需要维护缓存与数据库的一致性
– 最佳实践：为不同的查询条件设置不同的缓存键，并设置合理的过期时间。

方案2：数据库另表记录 ⚠️ 条件适用

– 思路：创建一张汇总表，定期或触发式更新各维度的计数
– 优点：保证强一致性，适用于数据仓库或报表系统
– 缺点：无法灵活支持所有随机查询条件，维护成本高
– 变体方案：使用物化视图（如 PostgreSQL 的 MATERIALIZED VIEW）。

方案3：彻底废弃总页数 ✅ 现代推荐方案

– 思路：使用“加载更多”或无限滚动模式，查询语句只取所需数据

SELECT * FROM tab1 WHERE ... ORDER BY id LIMIT 20;

– 下一页：

SELECT * FROM tab1 WHERE ... AND id > ? ORDER BY id LIMIT 20;

– 优点：性能最佳，用户体验流畅，尤其适合移动端和流式数据
– 缺点：无法跳转到任意页面，不适合需要精确导航的场景（如数据审核后台）

更优的混合方案与实践建议

结合业务场景，还有以下几种混合策略，可以在不同场景下取得平衡。

方案4：近似计数 + 精确导航（推荐首选）

对于大多数应用，用户并不需要绝对精确的总数，一个“大约 10,000+ 条结果”的提示足以满足需求。

– MySQL 近似计数（基于存储引擎统计）

SHOW TABLE STATUS LIKE 'tab1'; -- 查看 TABLE_ROWS（近似值）

– PostgreSQL 近似计数

SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_all_tables WHERE tablename = 'tab1';

– 优点：速度极快，适合展示“大约有1万条数据”
– 缺点：数字是估计值，在频繁增删后可能不准确

方案5：条件化计数缓存

针对特定高频查询条件进行优化，而不是试图解决所有可能的 WHERE 条件。

– 只为最常用的几个查询条件维护计数
– 示例：电商平台商品列表

CREATE TABLE query_counters (
    query_hash VARCHAR(32) PRIMARY KEY,  -- 查询条件的哈希值
    counter_value BIGINT,
    last_updated TIMESTAMP,
    query_condition TEXT  -- 原始查询条件（用于调试）
);

– 只有特定的分类、状态等高频条件才维护计数

方案6：基于游标的分页（性能最优）

彻底避免 OFFSET 和 COUNT，使用基于键的分页，特别适合流式加载。

– 传统分页（性能随页码增加而下降）

SELECT * FROM tab1 WHERE ... ORDER BY id LIMIT 20 OFFSET 10000;

– 基于游标的分页（性能恒定）
– 第一页

SELECT * FROM tab1 WHERE ... ORDER BY id LIMIT 21; -- 多取1条，判断是否有下一页
-- 第二页（客户端记住最后一条记录的id）

SELECT * FROM tab1 WHERE ... AND id > ? ORDER BY id LIMIT 21;
方案7：分区表计数汇总

如果表数据量极大，考虑按时间或业务维度分区，分区计数远比全表计数高效。

– PostgreSQL 分区表计数示例

SELECT table_name, (SELECT COUNT(*) FROM table_name) as count
FROM information_schema.tables 
WHERE table_name LIKE 'tab1_part_%';

– 将各分区计数相加，比 COUNT 全表快得多

决策流程图

如何选择最佳方案？

graph TD
    A[开始: 需要分页功能] --> B{查询条件是否固定?}
    
    B -->|是, 条件固定| C{数据更新频率?}
    B -->|否, 条件多变| D{用户是否必须知道精确总数?}
    
    C -->|低频更新| E[方案2: 汇总表+定时更新]
    C -->|高频更新| F[方案1: Redis缓存+实时同步]
    
    D -->|必须精确| G[近似计数是否可接受?]
    D -->|不需要精确| H[🌟方案3: 无限滚动/加载更多]
    
    G -->|可接受近似值| I[🌟方案4: 数据库估算值]
    G -->|必须精确| J[考虑方案1/2 或 承受COUNT性能成本]
    
    E --> K[实施]
    F --> K
    H --> K
    I --> K
    J --> K
    
    style H fill:#e1f5fe
    style I fill:#e1f5fe

具体实施建议

  • 场景1：面向公众的网站/APP（如电商、内容站）

推荐方案：近似计数 + 无限滚动

  1. 显示”约 1,000+ 条结果”
  2. 使用”加载更多”而不是页码
  3. 用户体验流畅，性能最佳
  • 场景2：管理后台、数据分析系统

推荐方案：条件化缓存 + 异步计数

  1. 高频条件：使用缓存计数（方案1）
  2. 低频条件：显示”计算中…”，异步获取
  3. 平衡性能与功能需求
  • 场景3：实时性要求极高的系统

推荐方案：游标分页 + 放弃总页数
• 完全避免 COUNT 操作

• 使用”下一页/上一页”导航

• 性能最优解

高级技巧：数据库特定优化

PostgreSQL 优化

-- 使用并行计数（大表有效）
SET max_parallel_workers_per_gather = 4;
SELECT COUNT(*) FROM tab1 WHERE ...;

-- 使用估算（牺牲精度换速度）
SELECT reltuples::bigint AS estimate_count
FROM pg_class WHERE relname = 'tab1';

MySQL 优化

– 使用索引覆盖计数

SELECT COUNT(*) FROM tab1 USE INDEX(index_name) WHERE ...;

– 从 INFORMATION_SCHEMA 获取估算

SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'tab1';

总结推荐

基于现代 Web 应用的特点，最推荐方案4（近似计数）与方案3（无限滚动）的组合：

  1. 默认使用近似计数：显示”1,000+ 条结果”，满足多数用户需求
  2. 重要场景使用缓存计数：对关键查询条件维护精确计数
  3. 完全避免全表 COUNT：特别是在用户触发的实时查询中

迁移建议：从 Oracle 习惯过渡到现代 Web 模式需要时间，可以分阶段实施：

  1. 第一阶段：先用数据库的估算值替代精确 COUNT
  2. 第二阶段：对性能敏感页面改为”加载更多”模式
  3. 第三阶段：仅为必要功能维护精确计数

既照顾了传统使用习惯，又逐步向更高效的现代架构演进。