SQL非群集索引 [英] SQL non-cluster index

问题描述

大家好，


我想在同一列上创建2个非群集索引会发生什么情况?
因为在我的Windows应用程序中，特定表上有1个非群集索引
当时速度较慢，我为同一列创建了另一个非群集，但我没有注意到，但是它给我的结果比早期的要快.我不知道早先创建索引的确切时间是什么以前可能是损坏的.
我只想知道在同一列上保留2个索引是否是最佳实践?

hello guys,


I want to what will happen if i create 2 non-cluster index on same column?
because in my windows application there was 1 non-cluster index on particular table
at that time it was slower and i create another non-cluster for same column and i didn''t notice but it''s giving me faster result than earlier.i don''t know what exactly happened that earlier index was created long time ago may be that was corrupted .
i just want to know is it best practice to keep 2 index on same column?

推荐答案

如果索引相同，则只需使用较新的索引，SQL查询优化器将根据最佳拟合方法确定使用哪个索引.索引不会被破坏，它们可能会过时，这意味着它们会根据可能已更改的旧数据模式进行填充，更新和分段.
最好一次清理并重新创建索引.

If the index is the same then just use the newer one, SQL query optimizer will determine which index to use based on a best fit approach. Indexes don''t get currupted they might get out dated, meaning they were filled and updated and fragmented based on old data patterns which might have changed.

It is good practise to cleanup and re do the indexes once in a while.

即使您可以在同一列上创建2个索引，也没有必要这样做.通常，发生的唯一事情是您使用了额外的空间.

在少数情况下，拥有多个索引将是有益的，但这种情况很少见.

正如Mehdi指出的那样，您应该定期重建索引.可能每月一次或每年一次，具体取决于您数据更改的频率.

另一件事是您的统计数据.查询变慢的问题可能是由于统计信息已过时.例如，每周应执行一次更新统计信息，这比重建索引要频繁得多.

为确保您拥有良好的统计信息:

• 从数据库选项中检查自动创建统计信息和自动更新统计信息都是正确的
• 使用更新统计信息 [

Even though you can create 2 indexes on the same column, there''s no point of doing that. Normally the only thing that happens is that you use extra space.

There are few special situations when having more than one index would be beneficial, but these are rare cases.

As Mehdi already pointed out you should rebuild the indexes regularly. It could be once a month or once a year depending how often you have changes in your data.

Another thing is your statistics. The problem that queries become slower could be caused by the fact that the statistics are out-of-date. Updating statistics is something you should do for example every week, meaning a lot more often than rebuilding indexes.

To ensure that you have good statistics:

• check from your database options that Auto Create Statistics and Auto Update Statistics are both true
• update your statistics on every table using UPDATE STATISTICS[^]
• OR create for example a maintenance plan where you update the statistics on regular intervals

这篇关于SQL非群集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！