代码之家 › 专栏 › 技术社区 › kheya

TSQL查询和索引问题

non-clustered-index indexing sql

kheya · 技术社区 · 14 年前

我有一个表,它存储以id作为主键的照片信息:

ID(pk),标题,专辑ID,发布人, 已发布,文件名,标记,分级, 数据发布

此表将包含1亿张以上照片的信息,以及我需要经常这样运行这个查询:

1)获取给定相册的所有照片(仅ID、文件名、标题列)

从照片中选择ID、文件名、标题其中相册id=@album id和发表= 1

2)获取给定用户的所有已发布照片,但不包括当前查看相册的照片

从照片中选择ID、文件名、标题哪里张贴了“bob”和专辑“id” <gt;10和published=1

我想避免索引和表扫描。我需要尽可能多地使用seek(比如100%)。

能做到吗? 什么类型的索引以及哪些列可以帮助我实现这一点?

谢谢

4 回复 | 直到 12 年前

marc_s 14 年前

在现实中,你只能通过在你调整,然后再调整,一次又一次的测量之前测量性能来发现这一点。

但是,根据您的查询,您应该考虑(或者至少首先尝试)这样的非聚集索引:

CREATE NONCLUSTERED INDEX IX01_Photos
  ON dbo.Photos(album_id, published, posted_by)
  INCLUDE(id, filename, title)

推理:

两个最常见的查询都有使用 album_id 和 published -所以在索引中首先使用这两列
第二个查询还包括 posted_by 在WHERE子句中-将其放入与第三列相同的索引中
为了避免在实际数据表中查找昂贵的书签,可以将 id, filename, title 索引中的列

所有这些都准备好了,你应该大多数情况下,索引会在新的非聚集索引上查找,以满足您的查询。但是,还有很多其他的因素也在起作用,你可能没有在你的问题中提到,甚至可能没有考虑到你自己-但这种方法应该给你一个良好的起点不亚于。

bobs 14 年前

您没有提到是否需要在查询中使用发布日期或ID作为筛选条件,因此最好在非按时间顺序的列上使用聚集索引(我假设当前聚集索引是pk)。对吗?).

我将在相册的ID上创建一个聚集索引。

如果您不能更改聚集索引,或者有许多其他查询可以从现有聚集索引中受益,那么我支持@marc_的答案(并将进行相应的投票)。

Caleb Hattingh 14 年前

我建议在 album_id 和第二个索引 posted_by 如果前者受到的打击最大。反转它们 波斯德比 最受打击。取决于每张照片的数量 白蛋白类 或 波斯德比 ,过滤可能相当可行。 published 在调用代码中(换句话说,不要将其添加为查询中的限制,而是筛选客户端)。如果不是,则必须将该已发布约束添加到查询中,但是 白蛋白类 应该意味着只有一小部分扫描 出版 发生了。但如前所述,只需过滤就可以了 出版 客户端。

Philip Kelley 14 年前

ID上的主键。将其设置为非群集。我想这不会被用得太多(特别是如果所有的查找都是通过专辑或海报)。

albumid上的聚集索引。似乎在大多数查询中都会用到。

发布者的非聚集索引。对于聚集索引albumid,它将出现在该索引的叶级,因此其作用非常类似于包含列。根据使用情况,最好将其作为聚集索引…但是作为一个varchar(20),它会占用更多的磁盘空间,并且性能会比albumid差(假设albumid是int)。

不能作为索引中的列发布,因为不能对位列进行索引。您也不想这样做——因为在100M+行中只有两个可能的值,所以SQL可能永远不会使用它来优化查询。

我建议规范化已发布的\u by(将其移动到自己的表中,给它自己的代理键,并将其用作此表中的外键)。这将显著减少主表中的存储空间,提高总体性能,并允许您根据需要将聚集索引翻转到该列。(同样,如果“bob”贴到桌子上,然后“bob”也贴到镇上,您如何区分bob和bob?)