如何正确地创建索引

I have the following very large table in SQL Server 2005:

我在SQL Server 2005中有一个非常大的表:

create table Blah 
(
  FirstName varchar(30),
  Rank int,
  Position int,
  ...
)

I will run the following query on it:

我会对它进行如下查询:

declare @PassedInFirstName varchar(30)
set @PassedInFirstName = 'SomeName'

select TOP 1 Position
from Blah
where FirstName = @PassedInFirstName
order by Rank DESC

I am setting up the following index on it:

我正在为此建立以下索引:

CREATE INDEX IX_Blah ON Blah (FirstName, Rank)

Given that I order it by Rank DESC, should I change the index to order Rank in a descending way:

考虑到我是按Rank DESC排序的，我是否应该将索引按降序进行排序:

CREATE INDEX IX_Blah ON Blah (FirstName ASC, Rank DESC)

Or it does not matter?

还是无所谓?

Thanks.

谢谢。

3 个解决方案

#1

If should benefit if the WHERE returns many rows.

如果应该受益如果哪里返回多行。

I've seen results where logical IO was reduced by 50% by using DESC in the INDEX to match an ORDER BY

我看到的结果是，通过在索引中使用DESC匹配ORDER by，逻辑IO减少了50%

Also, change the query to covering:

另外，将查询更改为覆盖:

SQL 2005 +:

CREATE INDEX IX_Blah ON Blah (FirstName, Rank DESC) INCLUDE (Position)

SQL 2000, SQL 7:

SQL 2000、SQL 7:

CREATE INDEX IX_Blah ON Blah (FirstName, Rank DESC, Position)

#2

Adding Rank as a descending value in your index is only a minor change. Sql Server can probably reverse the sorting used, or in this case, easily iterate to the last item in the list.

在索引中添加Rank作为降序值只是一个很小的改变。Sql Server可能可以反转所使用的排序，或者在本例中，可以轻松地迭代到列表中的最后一项。

Is Position your primary key? An index is built of indexed column, the primary key, and optionally included columns. If Position is not your primary key then you're currently looking up your primary key in your index, then going through the result with a primary index seek to find the Position value. Try adding the Position value as an included column and you should be able to execute your query based on only one index, no other indexes will be used.

位置是你的主键吗?索引由索引列(主键)和可选包含的列组成。如果位置不是您的主键，那么您当前正在索引中查找主键，然后使用主索引查找结果以查找位置值。尝试将位置值添加为包含的列，并且您应该能够基于一个索引执行您的查询，不会使用其他索引。

CREATE INDEX IX_Blah ON Blah (FirstName, Rank DESC) INCLUDE (Position)

Dont forget to check out your query plans, they can tell you if you lack any indexes (assuming Sql Server 2008), what indexes are used, etc.

不要忘记检查查询计划，它们可以告诉您是否缺少索引(假设Sql Server 2008)、使用了哪些索引等等。

#3

I messed up authentication on my previous post, this is my registered user.

我在之前的帖子中搞砸了认证，这是我的注册用户。

An index is based on those columns you choose, and the primary key. You're basicly storing a hashmap, where the key (FirstName, Rank) resolves to your Id (assuming your primary key is Id). This Id is then used to read the Position value.

索引基于您选择的列和主键。您正在存储一个hashmap，其中的key (FirstName, Rank)解析为您的Id(假设您的主键是Id)。然后使用这个Id读取位置值。

My proposition would be to include the Position value into the index as an included column. This would allow you to read the data from the index, avoiding the second lookup.

我的建议是将位置值作为包含的列包含到索引中。这将允许您从索引中读取数据，避免第二次查找。

#1