sql-server - SQL Server，对多个表使用UNION ALL然后分页实现

Question

我还需要有关分页和对多个表使用 UNION ALL 的帮助:

使用 UNION ALL 连接多个表并仅返回特定行数时，如何实现优化分页...

<小时/>

declare @startRow int
declare @PageCount int

set @startRow = 0
set @PageCount = 20

set rowcount @PageCount

select Row_Number() OVER(Order by col1) as RowNumber, col1, col2
from
(
    select col1, col2 from table1 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table2 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table3 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table4 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table5 where datetimeCol between (@dateFrom and @dateTo)
) as tmpTable
where RowNumber > @startRow

<小时/>

表 3、表 4 和表 5 具有大量行(数百万行)，而表 1 和表 2 可能只有几千行。

如果 startRow 为“0”，我只期望第 1 行到第 20 行的数据(来自表 1)。我得到了正确的结果，但在 sql server 尝试所有数据并过滤它时，剩余表的开销很高......

@dateFrom 和 @dateTo 的间隔越长，在尝试从整个结果集中仅检索几行时，查询速度就会明显变慢

请帮助我如何使用类似的逻辑实现一个简单但更好的方法。 :(

Answer 1

考虑使用 OFFSET FETCH 子句(从 MSSQL 2012 开始使用):

declare @startRow int
declare @PageCount int

set @startRow = 0
set @PageCount = 20


select col1, col2
from
(
    select col1, col2 from table1 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table2 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table3 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table4 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table5 where datetimeCol between (@dateFrom and @dateTo)
) as tmpTable
order by col1
offset @startRow rows
fetch next @PageCount rows only

这里我还想提一下，为什么这个查询总是需要 O(n*log(n)) 时间。
要执行此查询，数据库需要:

1. 将多个列表合并为一个列表 - 每个表需要 O(n) 时间，其中 n - 表中的总行数；
2. 按 col1 对列表进行排序 - 需要 O(n*log(n))，其中 n - 是总行数
3. 按排序顺序遍历列表，跳过 @startRow 行，获取接下来的 @PageCount 行。

如您所见，您需要对所有数据进行合并和排序才能获得预期结果(数字 3)。

如果此查询的性能仍然很差并且您想提高性能，请尝试:

• 在所有表中基于col1创建聚集索引
• 在所有表中基于 col1 创建非集群索引，并**包含您要在选择列表中输出的所有其他列**。