优化SQL查询，TSQL

Question

优化SQL查询，TSQL

sqlsql-servert-sqlquery-optimization

4

我是一名软件开发人员，最近DBA找到我优化我的应用程序正在使用的查询。DBA报告说，当查询运行时，它占用了大约50％的CPU和高I/O操作。查询非常直接，我不确定如何进行优化。

问题1：我该如何优化这个查询？

问题2：这甚至是我的工作吗？难道DBA不应该更加知识渊博吗？请注意，我们没有DB开发人员，只有DBA和软件开发人员。

数据库中大约有3,000-5,000万条记录，由DBA不断维护/监视，但我不确定具体情况。服务器在专用机器上，并且是Microsoft SQL Server 2005 - 9.00.5057.00 (X64)

附言：请勿提供通过结构性更改来改进数据库的方法。我知道将货币存储为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)

Fee - 未索引，decimal(6,2)

PDate - 聚集索引，datetime

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

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

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

Locked - 未索引，bit

执行计划: SELECT---计算标量---筛选---嵌套循环-|--索引搜索 (内连接) | cost 0% Cost 0% Cost 0% Cost 0% | cost 4% |---键查找 Cost 96%

- George

表上有哪些索引？查询返回多少行？执行计划是什么样子的？ - Martin Smith

1

除了一些索引之外，这里并没有太多可以做的。然而，我强烈建议您停止使用NOLOCK。这涉及到金钱问题，该提示意味着您的结果不准确。http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ - Sean Lange

提醒一下：SQL Server 2005 将在不到一年的时间内停止维护。 - Joel Coehoorn

@ Martin：原帖中已提供索引。我已编辑并附上执行路径。请告诉我您还需要什么。谢谢。 - George

抱歉，我忘记指定：4％在“INNO”上，96％在“PDate”上。 - George

显示剩余3条评论

5个回答

1

我想看看使用ISNULL而不是COALESCE是否能获得更好的结果。

另一件事是查看索引。您列出了已索引的字段。如果这些字段被多个索引覆盖，建议为此查询创建一个良好的覆盖索引。

覆盖索引是指查询所需的所有数据都包含在索引中。如果查询使用的索引不是覆盖索引，则需要额外的访问（或几次访问）表以获取其余字段。如果所有数据都在查询中，那么效率会更高。

请查看以下文章：

SQL Server中的覆盖索引和覆盖查询是什么？

https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

对于不属于连接或where子句的数据，可以使用include关键字。包含的字段不是索引中可搜索的部分，但可以节省到数据库的通信时间。

尝试使用下面的索引。where子句中的所有字段都是可搜索索引的一部分，并且返回的所有不属于where子句的字段都被包含在内。您可能需要根据执行计划进行排序，但我已经尽力了。

Create Nonclustered Index Ix_Ncl_History_CoveringBigSelect on History(PDate, PMode, INNO, PStatus, Locked) Include (PAmount, Fee)

这是一篇关于包含列（included columns）的文章。

- Sean

如果我完全删除COALESCE（不检查ISNULL，只是纯粹删除COALESCE），每个索引都是单独的，表中还有其他字段和其他索引，彼此独立。我不太确定您所说的“一个索引或多个”以及“可以尝试为此查询创建一个良好的覆盖索引”，您能否详细说明一下？ - George

你回答了我的一个关于一个索引还是多个索引的问题。我想知道你列出的索引字段是否都在一个索引中，还是有几个不同的索引。我会编辑我的答案，提供更多关于覆盖索引的信息。 - Sean

谢谢，你的索引几乎和Joel的一样。当我使用1万条数据测试他的索引时，它似乎表现更好，但当我注入更多数据时，查询的性能又回到了原来的水平。我想不出为什么。我的测试用例有问题吗？ - George

看到Joes，我看到的最大不同之处是他作为一个包含项具有状态，而我则在索引中拥有它。我会尝试这样做。然后，如Joel建议的那样，在索引中调整顺序。使用不同的配置查看执行计划。 - Sean

1

我会在下面的表上简单创建索引：

CREATE NONCLUSTERED INDEX idx_History_Locked_PMode_INNO_PStatus_PDate_iPAmount_iFee
    ON dbo.History (Locked, PMode, INNO, PStatus, PDate)
    INCLUDE (PAmount, Fee)
WHERE Locked = 1;                -- This is optional, can reduce index size.

这应该会改善您当前的查询。所有条件都应该在此得到满足。

- Evaldas Buinauskas

索引内的顺序很重要。将“Locked”列在首位似乎有些奇怪，因为我猜测特定记录会经常锁定和解锁。作为一个位标志，它的选择性也较差。最好先列出那些更具体且更稳定的项目。 - Joel Coehoorn

我不喜欢猜测，但我认为一旦行被锁定，它就永远被锁定。我可能错了。很难判断没有示例数据时哪个索引最合适。我同意你的索引可能效果更好。也许在这里添加筛选器会更好：WHERE Locked = 1。这应该可以减小索引大小。谢谢。 - Evaldas Buinauskas

锁定的 bit 更多地表示相关记录链中最后一个更新的记录。想象一下，一个名为apple12345的记录从“已种植”状态到“幼苗”到“成长”再到“已收获”。每次apple12345的记录进入下一个阶段时，DB触发器都会将LOCKED设为1到最新的记录，并将其设置为0到任何先前相关的记录。 - George

0

你说得对，查询看起来很正常。这是一个简单的查询，只有'AND'子句，没有“NOT NULL”约束、连接或子查询。条件大多数是相等的（只有日期是关系型的）。如果条件中的值（如'C'、'P'、1、'XYZ'、'CONSERVED'等）足够选择性，那么你（或DBA）应该定义一些索引，优化器可以使用它。请向DBA请求为表创建适当的索引。

你期望得到多少结果行？如果有很多（例如>> 10,000），ORDER BY子句可能会花费很多时间。

- dev.null

结果非常小，应该在一百条记录以下。问题在于它从中提取记录的表很大（2000万、3000万、4000万条记录，我不确定具体数字，但是有数百万条记录）。 - George

0

如你所说，我假设你无法对数据库进行任何操作，包括索引和结构更改。那么客户端应用环境如何？它是否足够强大以进行客户端计算？

如果答案是肯定的，我建议将计算移动到客户端：

不要在查询中转换数据类型，将varchar转换为decimal会消耗CPU资源。直接获取结果并在应用程序中进行转换。
对于IO问题，请尝试删除IN条件，因为IN本质上是一个"OR"条件。因此，将查询拆分成小块使用"="条件并发送到您的应用程序，使用客户端应用程序将它们"Union"起来。

- cqi

不进行强制转换似乎没有什么区别。将IN转换为一堆OR也是一样的情况。我可以将计算带到客户端，但是单独提取字段并不能改善查询。将它们保留为varchar并不进行强制转换也无法改善它。 - George

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Joel Coehoorn · Accepted Answer

似乎您对索引有误解。索引不能相互组合，因此问题不在于某一列是否“已索引”。单独为各个列建立索引并不好。关键是要针对特定查询建立包含多个列的索引。如果数据库仍然更有效地选择先选择另一列，则对某一列进行索引将无法帮助查询。

我对此有点陈旧了，但对于这个查询，我建议使用类似以下的索引：

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

根据其选择性，您可能需要交换PDate、PMode和PStatus。

在构建索引时，您希望首先列出最具体的项目。一般的想法是索引按顺序存储每个连续的项目。对于此索引，所有INNO的XYZ值的行将被分组在一起，因此查询引擎可以直接定位到索引的该部分。下一个最具体的列是Locked。尽管这是一个bit值，但由于它仅限于一个确切的值，我们仍然能够直接定位到整个查询所关心的索引的一个特定部分。再次强调：我已经有一段时间没有做过这种事情了，所以您也可以在此处列出PMode；我只是不记得Sql Server查询优化器是否足够聪明，能够以高效的方式处理这两个值。

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

最后，在INCLUDES子句中添加的内容将允许您完全从索引中完成此查询，而不必实际返回到完整表格。您使用INCLUDES子句而不是仅将值附加到查询中，以避免Sql Server为更新这些列而重建索引。这就是为什么PStatus等状态可能不应该成为主索引的一部分，如果状态是可以更改的，则可能更好地将Locked也排除在索引之外。然而，这些都是您自己想要测量和测试的事情。