performance - 返回有限结果集时计算完整结果集
问题描述
我有一个查询需要在高峰时段运行几十秒,看起来像这样:
select
a.id,
b.name,
-- ...
cast(count(a.id) over() as int) as total_count
from a
join b on b.a_id = a.id
-- ...
where
-- complex filter
offset 0 -- or 10 * (page_number - 1)
limit 5
这将返回一页 5 个结果,total_count
每行都有一个字段,该字段给出匹配的结果数,complex filter
前端使用该字段来显示分页控件。示例结果集可能如下所示:
id | name | ... | total_count
-------------------------------------
12345 | Bob | ... | 52000
23456 | Jill | ... | 52000
34567 | Jack | ... | 52000
45678 | Sarah | ... | 52000
56789 | Beelzebub | ... | 52000
我不擅长阅读explain
计划,但仅仅从这个查询的时间来看,算术将表明正在为没有的结果集中的每一行count() over ()
重新运行整个查询,在这种情况下意味着 52,000 次!limit 5
在我的一生中,我无法找出一种更高效的方法来做到这一点,它不涉及where
在子查询中复制整个子句!同时,为了在旺季提高性能,我调整了这个查询,总是返回表中所有记录的计数,不带complex filter
; 这对我们的应用程序来说很好,因为它仅供内部人员使用,不会破坏任何东西。这是新的查询:
select
a.id,
b.name,
-- ...
totals.total_count
from a
join b on b.a_id = a.id
-- ...
join (select count(*) as total_count from a) as totals on true
where
-- complex filter
offset 0 -- or 10 * (page_number - 1)
limit 5
它在几分之一秒内返回相同的结果,但具有误导性total_count
。例如,对 Beelzebub 的查询将返回:
id | name | ... | total_count
-------------------------------------
56789 | Beelzebub | ... | 320000
代替:
id | name | ... | total_count
-------------------------------------
56789 | Beelzebub | ... | 1
那么,有没有办法返回一页结果,其中包括整个结果集的计数,而无需复制where
子句?需要复制很多代码,这会使维护变得乏味。
任何帮助表示赞赏!
解决方案
推荐阅读
- php - 当我尝试使用 PHP(8.0) 编写的注册系统时,我总是收到 SQLSTATE[42000] 1064 错误
- c++ - C++ 有时会输出垃圾值
- html - 在 Netsuite 中访问高级 pdf/html 中的 salesteam 子列表
- google-data-studio - Google Data Studio:按相对于今天的日期为行着色
- spring-boot - 带有 OpenAPI 3 和 Java 16 的 InaccessibleObjectException
- powershell - Powershell 监控文件夹并在有新文件时发送电子邮件
- javascript - 为什么重命名 HTML 文件会修复 Chrome 中的元素定位错误?
- android - 如何将drawable添加到撰写中的文本
- javascript - 禁用 FieldSet 时启用 html 按钮
- c# - C# LINQ JOIN LAMBDA 添加第二个连接表中的列列表