主页 / dba / 问题 / 316831
Accepted
Geezer
Asked: 2022-09-14 02:21:17 +0800 CST

TSQL 慢查询，未按预期使用索引

  • 772

我有一个在 Azure SQL 数据库上运行的宽表，相对较大，有 14,264,775 行。

以下查询需要一些 TLC。

IF EXISTS (
    SELECT  1/0 
    FROM dbo.table1 src
      INNER JOIN dbo.table1 tgt 
        ON tgt.Col1 = src.Col1
    WHERE tgt.ValidFrom <= src.ValidTo
    AND tgt.ValidTo >= src.ValidFrom
    AND tgt.RecordId <> src.RecordId  
)
 BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
 END ;

我有这个索引。

CREATE NONCLUSTERED INDEX [IX__table1] ON dbo.table1
        ( Col1 ) 
INCLUDE (ValidFrom, ValidTo, RecordId)
GO

这是查询中的 io 统计信息。逻辑读取是通过屋顶。

在此处输入图像描述

这是计划 XML。我尝试了 PasteThePlan，但它不会解析计划 XML。（也许它不喜欢 Axure sql 数据库计划 xml）。

如您所见， [src] 上有一个索引扫描；读取 14,264,775 行（与表中的所有行数相同）。并在 [tgt] 上进行索引查找；读取 194,405,307 行。

我需要更改什么来提高查询的性能？

在 1400 万行中，有 210 万个唯一的 Col1 值。

sql-server query-performance

2 个回答

  1. Best Answer

    您似乎手动编辑了 XML 并使其无效，主要是通过添加无效字符，如<和>。修复一些问题后，我能够将计划加载到 SSMS 和计划资源管理器中：

    计划

    这表明您有一个名为[IX__dbo_table1__DateRange]- 问题中未提及的索引。从seek谓词来看，这个索引至少有Col1和ValidTo在索引中的键。

    另一个问题是使用IF EXISTS. 这引入了一个行目标，这导致优化器支持嵌套循环解决方案。请参阅相关的问答IF EXISTS 花费的时间比嵌入的 select 语句要长。

    也就是说，找到任何可能的重叠范围是一个很难用 b-tree 索引完全解决的问题，请参阅Resolving a performance issue with BETWEEN join- eager spool。

    在不了解完整的表定义、索引和数据分布的情况下，很难提出合适的解决方案。如果您只是想快速轻松地尝试一些东西，而不需要过多地更改索引或源查询，请尝试使用哈希连接提示：

    IF EXISTS (
    SELECT  1/0 
    FROM dbo.table1 src
      INNER HASH JOIN dbo.table1 tgt -- hint added
        ON tgt.Col1 = src.Col1
    WHERE tgt.ValidFrom <= src.ValidTo
    AND tgt.ValidTo >= src.ValidFrom
    AND tgt.RecordId <> src.RecordId  
)
 BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
 END ;

    这将完全扫描索引两次，但如果您的系统可以处理内存或 I/O 要求，并且并行或批处理模式执行可用，这可能不会太糟糕。如果有相当数量的不同Col1值，这将最有效。

    并行批处理模式哈希连接计划

    假设应该不允许重叠，我的偏好是首先使用约束来避免这种情况发生。请参阅在时态数据库设计中确保唯一条目的正确方法是什么？

    或者，正如ypercubeᵀᴹ在聊天中建议的那样：

    IF EXISTS
(
    SELECT 1/0
    FROM
    (
        SELECT 
            T.ValidFrom,
            PrevValidTo =
                LAG(T.ValidTo) OVER ( 
                    PARTITION BY T.Col1
                    ORDER BY T.ValidFrom)
        FROM dbo.table1 AS T
    ) AS T1
    WHERE 
        T1.PrevValidTo >= T1.ValidFrom
)
BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
END;

    使用如下索引：

    CREATE NONCLUSTERED INDEX [IX__table1] 
ON dbo.table1
    (Col1, ValidFrom) 
INCLUDE 
    (ValidTo, RecordId);

    窗口功能解决方案

    • 6

  2. 当前将读取所有记录，因为您正在查询 tgt.Col1 = src.Col1 的记录，这是完整的表。

    您应该通过添加ValidFrom和/或ValidTo到索引来使索引更具选择性。

    将这些添加到索引时，可以在 INCLUDE 部分之后删除该列。

    • 0

相关问题