主页 / dba / 问题 / 23766
Accepted
peter
Asked: 2012-09-06 15:04:23 +0800 CST

如何优化查询

  • 772

我有一个类似于此的数据库结构，

CREATE TABLE [dbo].[Dispatch](
    [DispatchId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
    [DispatchDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED 
(
    [DispatchId] ASC,
    [ContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DispatchLink](
    [ContractLink1] [int] NOT NULL,
    [DispatchLink1] [int] NOT NULL,
    [ContractLink2] [int] NOT NULL,
    [DispatchLink2] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (1, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (2, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (3, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (4, 1, N'Test')
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 2)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 3)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 3, 1, 2)
GO

DispatchLink 表的要点是将两个 Dispatch 记录链接在一起。顺便说一句，由于遗留问题，我在我的调度表上使用了复合主键，所以我无法在没有很多痛苦的情况下改变它。另外链接表可能不是正确的方法吗？但又是遗产。

所以我的问题是，如果我运行这个查询

select * from Dispatch d
inner join DispatchLink dl on d.DispatchId = dl.DispatchLink1 and d.ContractId = dl.ContractLink1
or d.DispatchId = dl.DispatchLink2 and d.ContractId = dl.ContractLink2

我永远无法让它在 DispatchLink 表上进行索引查找。它总是进行全索引扫描。有几条记录没问题，但是当该表中有 50000 条记录时，它会根据查询计划扫描索引中的 50000 条记录。这是因为在 join 子句中有'ands'和'ors'，但我不明白为什么 SQL 不能做几个索引查找，一个用于'or'的左侧，一个用于“或”的右侧。

我想要对此进行解释，而不是建议加快查询速度，除非可以在不调整查询的情况下完成。原因是我将上述查询用作合并复制连接过滤器，所以不幸的是我不能只添加另一种类型的查询。

更新：例如，这些是我一直在添加的索引类型，

CREATE NONCLUSTERED INDEX IDX1 ON DispatchLink (ContractLink1, DispatchLink1)
CREATE NONCLUSTERED INDEX IDX2 ON DispatchLink (ContractLink2, DispatchLink2)
CREATE NONCLUSTERED INDEX IDX3 ON DispatchLink (ContractLink1, DispatchLink1, ContractLink2, DispatchLink2)

所以它使用索引，但对整个索引进行索引扫描，因此 50000 条记录它扫描索引中的 50000 条记录。

sql-server performance

1 个回答

  1. Best Answer

    优化器可以考虑许多计划备选方案（包括具有多个搜索的计划），但对于析取（OR谓词），默认情况下它不考虑涉及索引交集的计划。给定索引：

    CREATE CLUSTERED INDEX cx 
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1 
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

    我们可以强制索引查找（假设 SQL Server 2008 或更高版本）：

    SELECT * 
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON 
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

    寻求力量计划

    使用您的示例数据，查找计划的成本为0.0332551个单位，而扫描计划的成本为0.0068057：

    扫描计划

    我们可以尝试各种可能的查询重写和提示。重写以提升优化器未考虑用于原始计划的选项的一个示例是：

    SELECT * 
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

    如果在第一个索引上找到匹配项，则此执行计划不会寻找第二个索引：

    应用顶级计划

    FORCESEEK这可能比默认计划稍微好一点。

    在不添加任何新索引的情况下，我们还可以强制搜索 Dispatch 表：

    SELECT * 
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

    求2

    这可能比第一个示例更好或更差，具体取决于每个表中有多少行。APPLY + TOP改进仍然是可能的：

    SELECT * 
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;
    • 12

相关问题