大数据库LIMIT分页时获取无限制总条数的高效方法

针对大型数据库分页时需获取符合条件总条数的问题，以下是几种比现有方案更优的实现方式：

1. 通用窗口函数方案（主流数据库支持） #

利用窗口函数 COUNT(*) OVER()，可以在返回分页数据的同时，让每一行都携带符合条件的总条数。数据库仅需执行一次WHERE过滤逻辑，避免了重复计算：

SELECT *, COUNT(*) OVER() AS total_count
FROM table_name
WHERE param > 3
LIMIT 10

• 优势：无需两次查询，WHERE条件仅执行一次；支持MySQL 8.0+、PostgreSQL、SQL Server等多数主流数据库。
• 注意：返回的10条数据中每条都会包含相同的total_count，前端只需取第一条的该值即可；确保param字段有合适的索引，能大幅提升过滤和计数效率。

2. MySQL专属方案（旧版本兼容） #

如果使用MySQL 8.0.17之前的版本，可以用SQL_CALC_FOUND_ROWS配合FOUND_ROWS()。第一次查询时MySQL会缓存符合条件的总行数，第二次查询直接读取缓存结果，无需重复执行过滤逻辑：

-- 第一步：获取分页数据，同时让MySQL计算符合条件的总行数
SELECT SQL_CALC_FOUND_ROWS * FROM table_name WHERE param > 3 LIMIT 10;
-- 第二步：直接读取缓存的总行数
SELECT FOUND_ROWS();

• 优势：比两次独立执行复杂查询高效很多，因为第二次查询无过滤开销。
• 注意：MySQL 8.0.17及以后版本已废弃该特性，官方推荐使用窗口函数方案，不建议新项目采用。

3. PostgreSQL专属WITH子句方案 #

PostgreSQL可以通过WITH子句将过滤逻辑抽离，仅执行一次，同时生成分页数据和总条数：

WITH filtered_data AS (
    SELECT * FROM table_name WHERE param > 3
)
SELECT *, (SELECT COUNT(*) FROM filtered_data) AS total_count
FROM filtered_data
LIMIT 10;

• 优势：过滤逻辑仅执行一次，优化器会自动复用中间结果；适合复杂过滤场景，逻辑更清晰。

关键优化建议 #

无论采用哪种方案，一定要给WHERE条件中的字段（如param）建立合适的索引，避免全表扫描，这是提升大数据库查询性能的核心前提。

内容的提问来源于stack exchange，提问作者user9102437