如何优化SQL Server 2008 R2表的性能？

Question

我为我的浏览器游戏开发了一个私信系统。当我检查大部分CPU时间使用的查询时，我发现这个表是最消耗CPU的。我不擅长索引和查询时间优化。所以，我想得到你对这个表的优化建议。

好的，现在我将首先展示表结构：

结构图片

好的，以下查询读取用户有多少未读消息，而这个查询是最消耗CPU的，因为它在每个页面加载时都会读取：

SELECT COUNT([Id]) [Number] 
FROM [MyTable] 
WHERE [ReceiverUserId] = @1 
  AND [ReceiverReaded] = @2 
  AND [ReceiverDeleted] = @3

那么，哪些类型的索引等可能会提高我的性能呢？

- Furkan Gözükara

Answer 1

为什么要在这些列上允许NULL值 - 要么读取，要么不读取。只需默认为0。然后按顺序对Read/Deleted/ReceivedUser进行索引（如果需要大量的ALL READ访问，则它们将被“分区”，或者如果大多数读取只是针对单个用户，则在ReceivedUser上放置一个索引）。

你想做的是查看索引是否覆盖。在你的情况下，你可以在ReceiverUserId上放置一个索引，并包括ReceiverReaded和ReceiverDeleted列，它将是覆盖的（对于该查询）。在执行计划中，你应该只看到一个索引查找，因为你只有一个用户。

你可以捕获工作负载，然后通过SQL Server中的索引调整向导运行它，它可能会提供非常好的建议。当然，你需要解释它告诉你的内容。

- Cade Roux

---

谢谢你的回答。实际上，它们的默认值是0，所以永远不会为空。你能给我一些关于索引调优向导的好参考资料吗？ - Furkan Gözükara

@MonsterMMORPG 在“工具”菜单中，你可以在窗口中调整特定的查询，或者使用分析器来捕获跟踪信息，然后使用数据库引擎调整顾问来为你提供整个工作负载的建议。 - Cade Roux

Answer 2

您总是希望在搜索的字段上建立索引，因此通过在[ReceiverUserId]、[ReceiverReaded]和[ReceiverDeleted]上添加索引，您可能会改善查询性能。当然，您索引的列越多，插入和更新数据时就会变得更慢。

- PaulStock

---

这完全取决于你将在此表上运行哪些其他查询。如果您有另一个仅查看ReceiverReaded的查询，那么3个单独的索引将为您提供保障。 - PaulStock

如果我没有只查看ReceiverReaded的内容，我该如何创建索引？ - Furkan Gözükara

在这种情况下，我会同意上面@Cade的答案，只需创建一个包括所有3列的索引，按照您的顺序。 - PaulStock

这个程序相关的内容是：CREATE NONCLUSTERED INDEX [_dta_index_MyTable_8_1547868581__K6_K3_K5] ON [dbo].[MyTable] ( [ReceiverReaded] ASC, [ReceiverUserId] ASC, [ReceiverDeleted] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]。请注意，这里只提供翻译文本。 - Furkan Gözükara

看起来不错，除了我会改变顺序，让ReceiverUserId首先出现，然后是ReceiverReaded，最后是ReceiverDeleted。 - PaulStock

Answer 3

在数据库优化中，一个相当简单的经验法则是对出现在WHERE子句或JOIN中作为谓词的任何列进行索引。从你的例子中，这些包括：

• ReceiverUserId
• ReceiverReaded
• ReceiverDeleted

此外，还有许多优化工具可供使用，它们可以“观察”您的数据库，并告诉您哪些列适合进行索引以获得最佳性能。

- Paul Sasik

---

请查看前三个链接：http://www.google.com/search?rlz=1C1SNNT_enUS377US377&sourceid=chrome&ie=UTF-8&q=sql+server+optimization+tools。我个人喜欢RedGate的SQ Server产品，但还有其他选择。 - Paul Sasik

Answer 4

1. 为搜索字段建立索引（参考@PaulStock的回答）。
2. 将tinyint字段更改为bit字段（默认值= 0）
3. 你真的需要nvarchar（4000）作为主体吗？那太大了！请考虑使用更短的消息（例如nvarchar（300）或更小-供参考，Twitter只有140个字符）。

- Chains

---

将它们转换为位（二进制）从未在我脑海中出现，太棒了:D 我不认为消息列会影响此查询的性能，因为我没有寻找它。 - Furkan Gözükara

大小很重要...:-)...对于您列出的特定查询，它可能不会有太大差异（仍将在虚拟表1中），但是您提到调整其他查询-如果其中任何一个选择正文，则如果能使字段更小，应该会看到改进。 - Chains

Answer 5

您的应用可能可行的不同方法是：当用户没有明确请求任何内容时，完全不查询消息表，例如当他不在您游戏的“消息”部分时。

尝试使用整数值列扩展用户表，指示有多少条消息以及已读取多少条消息。每次修改消息表时，也要修改用户表中相应的值。

这样，您就不需要在每次刷新时查看整个表格。请注意，这种方法的不利影响是程序员需要进行一些额外的同步工作。如果您已经适当地封装了消息表的修改（添加消息、阅读消息、删除消息），这不应该成为问题。

- DerManu

---

其实这是最好的想法 :) 我能做到这一点。需要编写更多的代码，但由于这是最消耗 CPU 的查询，所以值得 ^ ^ - Furkan Gözükara

我不认为这会提高整体性能。它可能会增加这个查询的速度...但代价是减慢写入消息表的速度。 - Chains

请看这张图片：d http://img18.imageshack.us/img18/6430/howincrease.png - Furkan Gözükara

@kuru kuru pa：它可以提高整体性能，因为查询在每次刷新时都会执行，在游戏的每个页面上都会执行。改进程序逻辑应始终优先于过早地对查询进行低级别优化。顺便说一句：在这种情况下，发布/阅读/删除消息的减速是可以忽略不计的，因为它只是增加/减少一个整数。 - DerManu