主页 / dba / 问题 / 345297
Accepted
jajanken
Asked: 2025-02-15 02:38:23 +0800 CST

排序 UNION 查询的选择列表中列的顺序和性质会影响性能

  • 772

我观察到排序UNION查询中的选择列表对性能有显著的影响。

UNION我处理的查询的一般形式是：

SELECT * FROM (
  SELECT <select_list> FROM <table>
  UNION ALL
  ...
) q
ORDER BY <column>

使用外部选择是因为它在存在的情况下显著提高了性能ORDER BY，但这超出了本问题的范围。 UNION ALL总是使用，而不是UNION。

我将把性能称为“快”（瞬间）或“慢”（5 秒或更长时间）。

通过在 DBeaver 控制台中运行查询来测量性能，默认情况下仅检索第一页，其大小为 200。

更改选择列表会以下列方式影响性能：

  1. 当<select_list> = <column>（即，仅选择排序列）时，查询速度很快。执行计划显示排序列的索引扫描和连接的合并连接。
  2. 当选择列表除了排序列外还包含其他列时，查询可能会很快，也可能会很慢。据观察，当以下条件全部满足时，查询会很快：
    • 选择列表包括聚集索引列。
    • 选择列表以聚集索引列开始，或者以排序列和紧接着聚集索引列的序列开始。

使用 Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) 进行观察。

UNION我在 SQL Server 文档中找不到有关选择列表对性能的影响的任何信息。

下面给出了进行实验的环境和查询本身的简单描述。

CREATE TABLE AUDIT1 (
    ID bigint NOT NULL,
    AUDITDATE datetime2 NULL,
    [USER] bigint NULL,
  -- Implies clustered index.
    CONSTRAINT PK_AUDIT1 PRIMARY KEY (ID)
);
CREATE INDEX I_AUDIT1_AUDITDATE ON AUDIT1 (AUDITDATE);

CREATE TABLE AUDIT2 (
    ID bigint NOT NULL,
    AUDITDATE datetime2 NULL,
    [USER] bigint NULL,
  -- Implies clustered index.
    CONSTRAINT PK_AUDIT2 PRIMARY KEY (ID)
);
CREATE INDEX I_AUDIT2_AUDITDATE ON AUDIT2 (AUDITDATE);
  • 表AUDIT1包含 1000 万条记录。
  • 表AUDIT2包含 100 万条记录。
  • AUDITDATE中的值AUDIT2大于 中的值AUDIT1。
  • 的值与按递增顺序AUDITDATE排列，即总是在递增， 也是如此。IDIDAUDITDATE

查询 1：选择列表仅包含排序列（快速）。

SELECT * FROM (
  SELECT AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

执行计划：

|--Merge Join(Concatenation)
   |--Index Scan(AUDIT2.I_AUDIT2_AUDITDATE), ORDERED BACKWARD
   |--Index Scan(AUDIT1.I_AUDIT1_AUDITDATE), ORDERED BACKWARD

查询 2：选择大小 > 1 的列表，包含排序列，不包含聚集索引列（慢）。

SELECT * FROM (
  SELECT [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

请注意，首先放置排序列似乎没有任何效果。

查询 3：选择大小 > 1 的列表，包含排序列，包含聚集索引列，第一列既不是排序列，也不是聚集索引列（慢）。

SELECT * FROM (
  SELECT [USER], ID, AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT [USER], ID, AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

查询 4：选择大小 > 1 的列表，包含排序列，包含聚集索引列，第一列是排序列，或者是聚集索引列（快速）。

SELECT * FROM (
  SELECT ID, [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT ID, [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

这个查询表明，如果第一列是ID，则查询速度很快。

查询 2、3、4 的执行计划相同：

|--Parallelism(Gather Streams, ORDER BY:([Union1007] ASC))
   |--Sort(ORDER BY:([Union1007] ASC))
      |--Concatenation
         |--Parallelism(Distribute Streams, RoundRobin Partitioning)
         |--Clustered Index Scan(OBJECT:(AUDIT2.PK_AUDIT2))
         |--Clustered Index Scan(OBJECT:(AUDIT1.PK_AUDIT1))
sql-server

1 个回答

  1. Best Answer

    重要的一点是 SQL Server 是否认为昂贵的排序是必要的。

    我在我的文章《使用合并连接串联避免排序》中讨论了这个问题。

    要点：

    • Merge Join Concatenation 是常规 Merge Join 运算符的一种特殊执行模式，它仅保留连接键的顺序。
    • 连接操作中没有“连接键”。相反，连接中的所有投影列都参与“保留顺序”。
    • 作为起点，合并连接输入在输出投影列表上进行排序。
    • 该ORDER BY子句可以覆盖起点，以避免两次排序。
    • 优化器可以使用任何存在的唯一性保证来截断所需的已排序列列表（行模式并行计划中的交换除外）。ID在您的示例中，列扮演了这一角色。在唯一键之后无需进一步排序。
    • 优化器对排序顺序的推理并不完美也不全面。已做出合理的努力。

    您通常可以通过添加到测试查询并检查执行计划来查看合并连接串联输入上的排序正在执行的操作，从而查看优化器所OPTION (MERGE UNION)遵循的输入排序顺序。

    例如，这表明查询 2 需要按 排序的输入(AUDITDATE ASC, [USER] ASC)，但索引无法提供。

    (AUDITDATE ASC, [USER] ASC)或上的索引(AUDITDATE DESC, [USER] DESC)可以提供该顺序。

    查询 3 想要(AUDITDATE ASC, [USER] ASC, ID ASC)。

    查询 4 ​​速度很快，因为保证唯一的列ID列在最前面。这种唯一性意味着在之后不再需要排序AUDITDATE, ID。排序的要求AUDITDATE来自ORDER BY子句。ID合并需要。合并不需要进一步排序，因为ID是唯一的。

    顺序AUDITDATE, ID可以由非聚集索引提供（因为它是非唯一的，所以 ID 是键的一部分）。

    SELECT * FROM (
  SELECT ID, [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT ID, [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE
OPTION (MERGE UNION);

    计划

    您可以看到非聚集索引提供了顺序AUDITDATE, ID：

    SELECT A.AUDITDATE, A.ID 
FROM dbo.AUDIT1 AS A 
ORDER BY A.AUDITDATE, A.ID;

    计划

    我现在看到所有查询都使用了合并连接。但是，只有快速查询才避免使用排序。

    是的，这是主要问题。尽可能避免排序通常对性能有益。

    您所说的“ORDER BY 子句可以覆盖起始点以避免重复排序”似乎不适用于查询 2 和 3，尽管使用了 ORDER BY，但这两个查询仍然很慢。它似乎取决于选择列表中列的顺序。

    是的，正如我所说，投影列表是起点。如果是适合合并要求的安排，该条款可以覆盖这一点。这并不意味着它总是会ORDER BY这样。我在文章中用例子广泛地介绍了这一点。ORDER BY

    您能详细说明一下 SQL 代码段附近的注释ORDER BY吗？为什么“合并连接输出顺序”和排序列之间应该兼容？

    演示顺序需要与合并可以使用的顺序兼容以避免排序。

    另外，如果您采用查询 3 并*在外部选择中替换ID, [USER], AUDITDATE- 它仍然很慢。

    这不在合并范围内。以下使用该顺序并可避免排序：

    SELECT [USER], ID, AUDITDATE FROM (
  SELECT ID, [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT ID, [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE
OPTION (MERGE UNION);

    计划

    请注意，所需的键查找可能仍会使查询“变慢”，但这是另一个问题。当然，您可能会根据所拥有的数据获得不同的计划。

    另一方面，取查询 4 ​​并替换*为[USER], ID, AUDITDATE- 它仍然很快。

    是的，因为具有唯一性保证的 ID 在合并时仍然是第一位的。

    最后，添加OPTION(MERGE UNION)查询 4 ​​会使其变慢，其计划与查询 3 类似，但增加了并行性。

    重点是：不进行排序的计划是可行的。优化器会考虑许多替代方案，并选择成本最低的方案。但由于前面提到的查找等原因，该方案可能仍然“缓慢”。

    一个稍微高级一点的例子，展示了请求的呈现顺序、预计的列、合并顺序保存、优化器推理、成本估算和索引顺序之间的紧张关系：

    SELECT
    Q.AUDITDATE, 
    Q.ID, 
    Q.[USER] 
FROM 
(
    SELECT 
        A2.AUDITDATE, 
        A2.ID, 
        A2.[USER] 
    FROM AUDIT2 AS A2

    UNION ALL

    SELECT 
        A1.AUDITDATE, 
        A1.ID, 
        A1.[USER] 
    FROM AUDIT1 A1
) AS Q
WHERE
    -- Give the optimizer a reason to read the index backwards
    Q.AUDITDATE <= CONVERT(datetime2(7), '9999-12-31T23:59:59.9999999', 126)
ORDER BY
    -- Compatible with merge concat output order
    Q.AUDITDATE DESC,
    Q.ID DESC
OPTION 
(
    MERGE UNION
);

    未排序的执行计划

    附带说明一下，USER列名选择不当。除非引用，否则这是语法错误，因为它是系统 niladic 函数。

    • 4

相关问题