sql - 优化我的 SQL 查询——选择正确的索引

Question

我有一个基本表如下。

create table Orders
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Company VARCHAR(3),
    ItemID INT,
    BoxID INT,
    OrderNum VARCHAR(5),
    Status VARCHAR(5),
    --about 10 more columns, varchars and ints and dates
)

我正在尝试优化我的所有 SQL，因为我遇到了一些死锁和一些缓慢的问题 - 但我不是这类事情的专家!

我创建了几个索引:
聚簇在ID(Primary Key)上。
([ItemID]) 上的非聚集索引
([BoxID]) 上的非聚集索引
([Company],[OrderNum],[Status]) 上的非聚集索引
在其他一些专栏上可能还有 1 或 2 个

但我对结果不是 100% 满意。

SELECT * FROM Orders WHERE ItemID=100

给我一​​个索引搜索+一个键查找和一个嵌套循环(内部连接)。我明白为什么 - 但不知道我是否应该对此做任何事情。他们的键查找是批处理的 97%，这看起来很糟糕!

使用的每个查询都会拉回表中的每一列，但我不喜欢将每一列都包含在索引中的想法。

我现在进行更改以查询 [Company] 字段中的所有内容。 每个 查询都将使用它，因为结果应该永远 包含超过 1 个值。所以他们都会改变:

SELECT * FROM Orders WHERE ItemID=100    --Old
SELECT * FROM Orders WHERE Company='a' and ItemID=100    --New

但是它的执行计划与不包括公司完全一样(这让我感到惊讶!)。

• 为什么上面的两个执行计划是一样的？ (我目前没有[公司]的索引)

• 是否值得将 [Company] 添加到我的所有索引中，因为它似乎使0 与执行计划不同？

• 我是否应该只向 [Company] 添加 1 个索引并保留原始索引？ - 但那会吗意味着每个查询都会有 2 次搜索？

• 是否值得在我的索引中“包含”所有其他列以避免关键查找？ (使指数增加一吨，但可能加快速度？)即

  CREATE NONCLUSTERED INDEX [IX_Orders_MyIndex] ON [Orders]
  ( [Company] ASC, [OrderNum] ASC, [Status] ASC )
  INCLUDE ([ID],[ItemID],[BoxID],
  [Column5],[Column6],[Column7],[Column8],[Column9],[Column10],etc)
  

如果我在 4 或 5 个索引上这样做，那看起来很乱。

基本上我有 4-5 个经常运行的查询(一些选择和更新)所以我想让它尽可能高效。所有查询都将使用 [company] 字段，并且至少使用 1 个其他字段。我应该怎么做。

感谢任何帮助:)

Answer 1

在您的执行计划中，您说查找占了批处理的 97%。

在这种情况下，它没有任何意义，因为索引查找非常快，而且您没有那么多操作要完成。

那个lookup其实就是你根据你指定的索引读取的记录。

为什么上面的两个执行计划是一样的？ (我目前没有关于 [company] 的索引)

([Company],[OrderNum],[Status]) 上的非聚集索引

仅当 Company、OrderNum 和 Status 出现在您的 where 子句中时，才会考虑此索引。

当您仅传递 company 时，串联索引会生成一个类似于 0000000000000 的键，它会创建一个不完整的键，需要对其他值使用通配符。

它看起来有点像这样:key like 'XXX%' 这个逻辑需要索引扫描，这很耗时。

优化器将确定最好先从 ItemID 索引中查找行，然后扫描这些行以匹配所需的公司。

是否值得将 [Company] 添加到我的所有索引中，因为它似乎使 0 与执行计划不同？

您应该考虑使用 Company 索引，而不是将其添加到所有索引中。复合索引可以通过减少嵌套循环的数量来加快速度，但你必须仔细考虑。

添加到此类索引中的字段的顺序非常重要，它们应该按唯一性排序以便更好地查找。此外，您永远不应添加可能不会在查询中使用的字段。

我是否应该只向 [Company] 添加 1 个索引并保留原始索引？ - 但这是否意味着每个查询都会有 2 次搜索？

有多个索引查找并没有那么糟糕，它们通常是并行的，只有两者的结果匹配在一起。

是否值得在我的索引中“包含”所有其他列以避免键查找？ (使索引增加一吨，但可能会加快速度？)

当只有少数字段在 where 子句中是可选的，或者当您在使用指定索引时查询仅选择那些字段时，这是值得的。

最后的笔记

所有索引都不相等，比较字符串(varchar)与比较数字(整数、日期时间、字节等)不同。

此外，保持它们干净有很大帮助，如果您的索引是碎片化的，它们在性能提升方面几乎毫无用处。