sql - 优化SQL查询、TSQL

Question

我是一名软件开发人员，最近 DBA 联系我，要求我优化我的应用程序正在使用的查询。 DBA 报告说，查询运行时会占用大约 50% 的 CPU 和大量 I/O 操作。该查询非常简单，我不确定如何优化它。

问题 1:如何优化此查询？

问题2:这也是我的工作吗，DBA不应该更了解这方面的知识吗？请注意，我们没有数据库开发人员，只有 DBA 和软件开发人员。

DB 有大约 30-5000 万条记录，它由 DBA 不断维护/监控，但我不确定如何维护/监控。服务器位于专用计算机上，并且是 Microsoft SQL Server 2005 - 9.00.5057.00 (X64)

PS:请不要提供通过结构更改来改进数据库的方法，我知道将货币存储为 varchar 是一个糟糕的设计，但它就是这样，我们无法更改数据库结构，只有查询访问它。

感谢您的见解。

查询:

SELECT
    COALESCE(CAST([PH].[PAmount] AS decimal(15, 2)) + CAST([PH].[Fee] AS decimal(15, 2)), 0.0) AS [PayAmount],
    [PH].[PDate] AS [PayDate]
FROM [History] AS [PH] WITH (NOLOCK)
WHERE [PH].[PMode] IN ('C', 'P')
    AND [PH].[INNO] = 'XYZ'
    AND [PH].[PStatus] IN ('CONSERVED', 'EXPECTING', 'REFRIGERATED', 'POSTPONED', 'FILED')
    AND [PH].[Locked] = 1
    AND [PH].[PDate] >= 'Jan 1, 2015'
ORDER BY [PH].[PDate] ASC

字段:

PAmount - 非聚集索引，varchar(50)

费用 - 未索引，十进制(6,2)

PDate - 聚集索引，日期时间

PMode - 非聚集索引，varchar(5)

INNO - 非聚集索引，varchar(50)

PStatus - 非聚集索引，varchar(50)

锁定 - 未索引，位

执行计划:
SELECT---计算标量---过滤器---嵌套循环-|--索引查找
(内连接)|
成本 0% 成本 0% 成本 0% 成本 0% |成本 4%
|---按键查找
成本 96%


Answer 1

看来您对索引有误解。索引不会相互组合，因此这不是列“索引”或“未索引”的问题。为各个列建立单独的索引并不好。它涉及到具有多个列的索引，这些列与各个查询密切相关。如果数据库首先选择另一列仍然更有效，那么列上的索引将不会对查询有帮助。

我对此有点陈旧，但对于这个查询，我建议使用如下所示的索引:

CREATE NONCLUSTERED INDEX [ix_History_XXXXX] ON [History] 
(
    [INNO] ASC,
    [Locked] ASC,
    [PDate] ASC,
    [PMode] ASC
)
INCLUDE ( PStatus, PAmount, Fee)

您可能想要交换 PDate、PMode 和 PStatus，具体取决于它们的 selectivity .

构建索引时，您希望首先列出最具体的项目。总体思路是索引按顺序存储每个连续的项目。使用此索引，INNO 的所有 XYZ 值的行将被分组在一起，因此查询引擎可以搜索正确到该部分的索引。下一个最具体的列是Locked。尽管这是一个位值，但由于它仅限于一个值，我们仍然能够直接寻找索引中对索引重要的一个特定部分。整个查询。再说一遍:我已经有一段时间没有做这种事情了，所以你也可以在这里列出 PMode ；我只是不记得 Sql Server 查询优化器是否足够智能以有效的方式处理这两个值。

从现在开始，索引的最佳选项取决于每个查询值对结果的限制程度。由于我们无法再将所有结果放入一个空间中，因此我们必须扫描索引的相关部分。我的直觉是接下来使用 Date 值。这将允许扫描从与结果匹配的第一个日期开始遍历索引，并帮助它以正确的顺序获取记录，但同样:这只是我的直觉。首先列出 PMode 或 PStatus 或许可以做得更好。

最后，INCLUDES 子句中的附加内容将允许您从索引完全完成此查询，而无需实际返回整个表。您可以使用 INCLUDES 子句，而不仅仅是将值附加到查询中，以避免 Sql Server 重建索引以更新这些列。这就是为什么 PStatus 等可能不应该成为主索引的一部分(如果状态可以更改)，以及为什么您最好还是保持锁定状态可能> 超出索引。不过，您需要亲自衡量并测试这些内容。