指数实际上会降低SELECT性能吗？ [英] Can Indices actually decrease SELECT performance?

问题描述

可能重复：

添加索引后查询的性能下降

在阅读了有关SQL Server索引的一些内容以及它们对更新/插入的选择和缺点的性能优势后，我想知道使用不当的索引是否实际上也会损害选择的性能。
索引会降低纯选择查询的性能需要满足哪些条件？存在这样的情况吗？

after reading some stuff about indices on SQL Server and their performance advantages for selects and disadvantages for updates / inserts, i was wondering if badly used indices could actually also hurt performance for selects. What conditions would have to be fulfilled to have an index decrease performance of a pure select query? Do such situations exist?

谢谢！

（虽然我总是尝试包含代码示例，但我可以'想到任何可以支持这个问题的东西......）

(although I always try to include code examples, i can't think of anything that would support this question...)

推荐答案

是的，虽然非常轻微 - 但是会有点微弱有理由也回答否。

Yes, albeit very slightly - so slightly that it would be justified to also answer "No".

如果你有一个索引可能被认为是查询但是不可用，那么优化器将浪费很短的时间来思考是否以及如何使用它（在极少数情况下使用非常复杂的索引和视图，并且当索引性能提示错误时更常见，您最终可能会选择次优的查询计划）。

If you have an index which might be considered for a query, but is not useable, the optimizer will waste a short time pondering whether and how to use it (in rare cases with REALLY complicated indexes and views, and more frequently when index performance hints are wrong, you might end up choosing a suboptimal query plan).

有些情况可能是：

• 没有索引的表


• 一个具有错误选择索引的表，将被丢弃


• 存在两个索引的表，并且由于某种原因（例如过时的统计信息），第二个索引的存在使优化器选择它，whi使用第一个会更方便。

在前两种情况下，查询时间是相同的（并且需要一个完全扫描），但在第二个，你还必须分析和丢弃索引。

In the first two cases the query time is the same (and entails a full scan), but in the second, you also have to analyze and discard the index.

索引伤害你 - 所有索引伤害你 - 在插入，删除和更新。然后，更新查询未使用但受其影响的任何索引都需要写入索引本身。

Where an index hurts you - where ALL indexes hurt you - is in inserts, deletes and updates. Then, any index not used by the update query, yet affected by same, will require a write to the index itself.

所以你需要拥有索引，但在不牺牲SELECT性能的情况下尽可能少。实际上，您可能决定不使用很少使用的SELECT查询的索引，以避免所有其他UPDATE查询不断更新所需的索引。

So you will want to have indexes, but as few as you can without sacrificing SELECT performances. Actually, you might decide against indexing for a rarely used SELECT query in order to avoid having the needed index constantly updated by all other UPDATE queries.

编辑：阅读Heinzi的答案后，我还想补充一点，大多数数据库服务器都有维护工具来分析表和索引（有时也会查询性能计数器），并正确更新Heinzi所说的提示。因此，定期维护数据库以保持优化器提供有关可供选择的索引的最新信息也很重要。

after reading Heinzi's answer, I'd also like to add that most DB servers have maintenance tools which analyze the tables and indexes (and sometimes query performance counters too), and properly update the hints of which Heinzi spoke. So it's also important to periodically "maintain" the database to keep the optimizer supplied with up-to-date information on which indexes to choose from.

有一个非常漂亮的MySQL分析工具，它实际上可以建议对现有索引进行改进（删除未使用的密钥，添加有用的密钥）： common_schema 。这真的值得一看。

There is a very nifty MySQL analysis tool that can actually suggest improvements to the existing indexing (remove unused keys, add useful keys): common_schema. It's really worth a look.

这篇关于指数实际上会降低SELECT性能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！