代码之家 › 专栏 › 技术社区 › Mr. Flibble

SQL Server群集索引-索引顺序问题

indexing performance database sql-server sql

Mr. Flibble · 技术社区 · 16 年前

我有一张这样的桌子:

keyA keyB data

keyA和keyB一起是唯一的,是我的表的主键,构成了一个聚集索引。

keyB有5个可能的值,但keyA的可能值数量不受限制,。按键B通常递增。

例如,以下数据可以按两种方式排序,具体取决于先排序的键列:

keyA keyB data
A    1    X
B    1    X
A    3    X
B    3    X
A    5    X
B    5    X
A    7    X
B    7    X

或

keyA keyB data
A    1    X
A    3    X
A    5    X
A    7    X
B    1    X
B    3    X
B    5    X
B    7    X

我是否需要告诉聚集索引哪个键列的可能值较少,以便它首先按该值对数据进行排序?或者,就性能而言,先订购哪个并不重要吗?

9 回复 | 直到 15 年前

Sam 16 年前

您应该先用最有选择性的列对复合聚集索引进行排序。这意味着与总行数相比,具有最明显值的列。

“B*TREE索引提高了从表中选择一小部分行的查询的性能。” http://www.akadia.com/services/ora_index_selectivity.html ?

本文适用于Oracle,但仍然具有相关性。

此外,如果您有一个持续运行并返回少量字段的查询,您可以考虑创建一个包含所有字段的复合索引——它不必访问基表,而是从索引中提取数据。

ligget78关于确保在复合索引中提到第一列的评论值得记住。

liggett78 16 年前

如果你用(keyA,keyB)创建一个索引(无论是否聚集),那么这就是值的排序方式,例如先是keyA,然后是keyB(这是你问题中的第二种情况)。如果你想反过来,你需要指定(keyB,keyA)。

性能方面可能很重要,当然取决于您的查询。例如,如果你有(keyA,keyB)索引,查询看起来像WHERE keyB=。..(未提及keyA)则无法使用索引。

Tom H zenazn 16 年前

正如其他人所说,排序是基于您在索引创建脚本(或PK约束)中如何指定它。不过,关于聚集索引,有一点需要牢记。

通过在PK之外的其他内容上使用聚集索引,您可能会获得更好的整体性能。例如,如果您正在编写一个财务系统,并且报告几乎总是基于活动的日期和时间(过去一年的所有活动等),那么该日期列的聚集索引可能会更好。正如HLGEM所说,排序也会受到您选择的聚集索引的影响。

聚集索引对插入的影响也比其他索引大。如果您有大量的插入,并且您的聚集索引位于类似IDENTITY列的位置,那么由于所有新行都插入到同一位置,因此磁盘的特定部分可能会出现争用问题。

对于小型查找表,我总是把聚集索引放在PK上。对于高影响的表,在选择最佳聚集索引之前,花时间思考(和测试)各种可能的聚集索引是一个好主意。

Andrew Rollings 16 年前

我相信SQL Server会按照您的指示进行排序。它假设您最了解如何访问索引。

无论如何,我会说,在可能的情况下,明确指定你想要什么是一个好主意,而不是希望数据库能解决这个问题。

您也可以尝试这两种方式,运行一堆具有代表性的查询,然后比较生成的执行计划,以确定哪种最适合您。

Brian Rudolph 16 年前

请记住,聚集索引是表在磁盘上存储的物理顺序。

因此,如果您的聚集索引定义为ColA,那么当按与聚集索引相同的顺序排序时,ColB查询会更快。如果SQL必须排序B、A,则需要执行后排序才能达到正确的顺序。

我的建议是在B、a上添加第二个非聚集索引。还需要根据数据列的大小来包含(读取包含列)它,以防止需要进行键查找。当然,前提是这个表没有大量插入,因为您必须始终平衡查询速度和写入速度。

实际上,您的聚集索引应该表示数据最有可能被访问的顺序,并保持插入\更新IO成本的微妙平衡。如果你的聚集索引是这样的,你不断地插入到页面的中间,你可能会在那里遭受性能损失。

正如其他人所说,不知道表长度、列大小等。没有正确答案。反复试验和大量测试是你最好的选择。

shsteimer 16 年前

以防万一这并不明显:你的排序顺序指数对排序顺序没有太多承诺 查询结果 .

在查询中,您仍然必须添加

ORDER BY KeyA, KeyB

或

ORDER BY KeyB, KeyA

优化器可能很乐意根据需要在索引中找到已按物理顺序排列的数据并节省一些时间,但每个应该以特定顺序传递数据的查询都必须在末尾有order BY子句。如果没有order BY,SQL Server就不会对记录集的顺序做出任何承诺,甚至不会保证它会以相同的顺序从一个查询返回到另一个查询。

Davide Vosti 16 年前

你能做的最好的事情就是尝试这两种解决方案并测量执行时间。

根据我的经验,索引调优几乎是一门精确的科学。

也许在索引列顺序中,将keyB放在keyA之前会更好

HLGEM 16 年前

您可以按照通常希望在报告和查询中排序的顺序指定列。

不过,我对创建多列聚集索引持谨慎态度。根据其宽度,您可能会对创建的任何其他索引的大小产生巨大影响,因为所有非聚集索引都包含聚集索引值。此外,如果值频繁更改,则必须对行进行重新排序,根据我的经验,非代理键往往会更频繁地更改。因此,如果您的值可能会更改,则将其创建为聚集副非聚集索引可能会消耗更多的服务器资源。我并不是说你不应该这样做,因为我不知道你的列实际上包含什么类型的数据(尽管我怀疑它们比A1、a2等更复杂);我的意思是,你需要考虑这样做的后果。在决定这样做之前,彻底阅读BOL关于聚集副非聚集索引的内容可能是一个好主意。

user1481803 13 年前

是的,您应该建议,通常查询引擎会尝试找出最佳执行计划和要使用的索引,但有时最好强制查询引擎使用特定的索引。在规划索引以及在查询中使用索引时,还有其他一些考虑因素。例如,索引中的列顺序,where子句中的列排序。您可以参考以下链接了解:

http://ashishkhandelwal.arkutil.com/sql-server/quick-and-short-database-indexes/

使用索引的最佳实践
如何从指标中获得最佳性能
聚集指数注意事项
非聚集索引注意事项

我相信这将有助于您规划索引。