是否有多个索引都以相同的列开头会对Sybase优化器的速度或准确性产生负面影响？ [英] Does having several indices all starting with the same columns negatively affect Sybase optimizer speed or accuracy?

问题描述

我们有一个表，比如5个索引（一个聚集）。

We have a table with, say, 5 indices (one clustered).

问题：它会以某种方式对优化器性能产生负面影响 - 索引选择的速度或准确性 - 如果所有5个指数都以相同的确切字段开头？ （所有其他条件相同）。

Question: will it somehow negatively affect optimizer performance - either speed or accuracy of index picks - if all 5 indices start with the same exact field? (all other things being equal).

该公司的某人建议它可能对性能产生不利影响，因此其中一个指数需要切换前两个字段。

It was suggested by someone at the company that it may have detrimental effect on performance, and thus one of the indices needs to have the first two fields switched.

如果没有必要，我宁愿避免改变，因为他们没有用任何事实/推理来支持他们的断言，但这个家伙是高级和足够聪明的我倾向于认真考虑他的建议。

I would prefer to avoid change if it is not necessary, since they didn't back up their assertion with any facts/reasoning, but the guy is senior and smart enough that I'm inclined to seriously consider what he suggests.

注1：基本答案定制where子句和整体查询的索引对我没有帮助 - 要更改的索引是使用它的唯一查询的覆盖索引，因此其中字段的顺序不会影响IO数量。 我问了一个分开SO问题只是为了确认断言。

NOTE1: The basic answer "tailor the index to the where clauses and overall queries" is not going to help me - the index that would be changed is a covered index for the only query using it and thus the order of the fields in it would not affect the IO amount. I have asked a separate SO question just to confirm that assertion.

注2：该字段是插入记录的日期，表格相当大，如果这样事项。它有大约100天的数据，大约相等于每个日期的行数，第一个索引是从该日期字段开始的聚集索引。

NOTE2: That field is a date when the records are inserted, and the table is pretty big, if this matters. It has data for ~100 days, about equal # of rows per date, and the first index is a clustered index starting with that date field.

推荐答案

如果有五个索引，优化器必须更多地考虑使用哪个索引。这个成本通常不算太糟糕，但这取决于你问的问题。原则上，一旦查询被优化，执行它所花费的时间应该大致相同。如果您准备SELECT语句用于多种用途，那将无关紧要。如果每个查询都重新准备好并且从不重用，那么开销可能会对系统性能造成拖累 - 特别是如果事实证明哪个索引实际上用于大多数查询并不重要（当中有一个中等强度的危险时）五个索引都共享相同的前导列。）

The optimizer has to think more about which if any of the indexes to use if there are five. That cost is usually not too bad, but it depends on the queries you're asking of it. In principle, once the query is optimized, the time taken to execute it should be about the same. If you are preparing SELECT statements for multiple uses, that won't matter much. If every query is prepared afresh and never reused, then the overhead may become a drag on the system performance - particularly if it turns out that it really doesn't matter which of the indexes is actually used for most queries (a moderately strong danger when five indexes all share the same leading columns).

数据更改时还有维护成本 - 更新五个索引所需的时间明显长于一个索引，再加上你是使用五个索引的磁盘存储量大约是一个的五倍。

There is also the maintenance cost when the data changes - updating five indexes takes noticably longer than just one index, plus you are using roughly five times as much disk storage for five indexes as for one.

这篇关于是否有多个索引都以相同的列开头会对Sybase优化器的速度或准确性产生负面影响？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！