删除索引会加快查询速度：为什么？ [英] dropping an index speeds up query: why?

问题描述

通过反复试验，我发现我的数据库上的表上的索引严重影响了查询的性能。

By trial and error, i found that an index on a table on my DB was drastically affecting the performance of a query.

使用索引：执行时间30秒

With index: execution time 30sec

没有索引：执行时间< 1sec

Without index: execution time <1sec

这是我第一次看到类似的东西，无论如何索引有意义，因为它是外键上的非唯一非聚集索引。

It is the first time I see something similar, anyway the index makes sense since it is a non-unique non-clustered index on a Foreign key.

该表与我描述的相同在这篇文章中（感谢我能够创建FK）。下一步是创建索引：

The table is the same I describe in this post (thanks to which I was able to create the FK). The next step was creating the index:

CREATE NONCLUSTERED INDEX [IX_RDATA_COMBO_VALUES] ON [dbo].[RDATA_COMBO_VALUES]
(
    [ID_REFERENCES] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

当此索引存在时，查询变得非常慢，因为我放弃查询的速度很快。查询中的慢速连接是关于此表中的 VALUE 字段，而不是索引中的关键字段（ ID_REFERENCES ），这就是为什么我不理解。

When this index exists a query becomes super slow, as I drop it the query is fast. The slow join in the query is about the VALUE field in this table and not the key field in the index (ID_REFERENCES), this is why I do not understand.

我怀疑SQL服务器的内部工作方式有所不同。

I suspect there is something about the inner workings of SQL server.

这可能是导致这种情况发生的原因？

Which could be the reason why this happen?

我还注意到，如果我为每个表字段创建一个索引，那么查询会再次变快（这是我试过的通过反复试验行事。谢谢。

I also noticed that if I create one index per table field the query becomes fast again (this is something I tried acting by "trial and error"). Thanks.

推荐答案

添加索引是而不是一个神奇的药水，可以自动使您的查询运行得更快。根据您的问题，并基于您正在进行大量试验和错误这一事实，似乎在索引方面的更多知识将为您带来很长的路要走。

查看执行计划并查看你的查询在后台实际做了什么将有助于你理解为什么你的索引搞砸了。

有一个免费的视频来自世界级的DBA，Brent Ozar（网站是brentozar。 com），它解释了索引和SQL Server性能的基础知识。

Adding an index is not a magical potion which automagically makes your queries run faster. Based on your question, and based on the fact that you were doing lots of trial and error, it seems as if some more knowledge in indexing will get you a long way.
Looking at the execution plan and seeing what your query is actually doing in the background will help you a lot in understanding why your index was messing things up.
There's a free video out there from a world class DBA, Brent Ozar (website is brentozar.com), which explains the basics of indexing and SQL Server performance.

这是链接： https://www.brentozar.com/archive/2016/10/think-like-engine-class-now-free-open-source/

这篇关于删除索引会加快查询速度：为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！