SQL Server：索引的类型如何影响联接的性能？
如果我试图从查询中榨取最后一点性能，那么我的连接使用这些类型的索引会产生什么影响。
- 聚集索引。
- 非聚集索引。
- 具有可能不参与连接的额外列的聚集或非聚集索引。
如果我检查并创建仅包含连接中涉及的列而不包含其他列的聚集索引，我会获得任何性能吗？
（我意识到我可能必须从另一个索引移动聚集索引（使该索引成为非聚集索引），因为它只能有一个。）
If I'm am trying to squeeze every last drop of performance out of a query what affect does having these types of index's being used by my joins.
- clustered index.
- non-clustered index.
- clustered or non-clustered index with extra columns that may not be involved in the join.
Will I gain any performance if I go through and create clustered index's that only contain the columns involved in my joins and nothing else?
(I realize I may have to move the clustered index from another index(making that index non-clustered) since it can only have one.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
除了 Gareth Saul 的回答之外，还有一个小小的澄清：
这个指向实际数据值的指针是集群键中的列（或列集）。
这就是为什么你应该尝试保持集群键小且静态的主要原因之一 - 小是因为否则你会在磁盘和服务器 RAM 中浪费大量空间，而静态是因为否则你将不得不更新如果您的值发生变化，不仅是您的聚集索引，还包括所有非聚集索引。
此“查找指针是集群键”功能自版本 7 起就已存在于 SQL Server 中，如 Kim Tripp 将在这里详细解释：
In addition to Gareth Saul's answer a tiny clarification:
This pointer to the actual data value is the column (or the set of columns) that are in your clustering key.
That's one of the main reasons why you should try and keep the clustering key small and static - small because otherwise you'll waste a lot of space, on disk and in your server's RAM, and static because otherwise, you'll have to update not just your clustering index, but also all your non-clustered indices as well, if your value changes.
This "lookup pointer is the clustering key" feature has been in SQL Server since version 7, as Kim Tripp will explain in great detail here:
不像我理解的那样。聚集索引的要点在于，它然后围绕该索引对磁盘上的数据进行排序（因此您只能拥有一个索引），因此如果您的联接数据也没有按这些确切的列进行排序，我不会我认为这会有什么不同。另外，通过将可能更改的数据（而不是键）放入聚集索引中，您更有可能需要定期重建数据，从而减慢整个数据库的速度。
抱歉，如果这听起来是一个愚蠢的问题，但是您是否尝试过通过索引调整向导运行查询？无论如何，这都不是万无一失的，但我过去已经从中得到了一些不错的改进。
Not as I understand. The point of a clustered index is that it then sorts the data on disk around that index (hence why you can only have the one), so if your join data isn't being sorted by those exact columns as well, I don't think it'd make any difference. Plus by putting data that might change (as opposed to the key) into the clustered index, you make it more likely that things will need rebuilding peridically, slowing the overall database down.
Sorry if this sounds a daft question, but have you tried running your query through the index tuning wizard? Not foolproof by any stretch but I've had some decent improvements from it in the past.
您只能获得一个聚集索引 - 这是控制表在磁盘/内存中的物理存储的因素。
非聚集索引重复包含的字段，并带有指向具有该值的行的指针。在连接中使用的列上建立索引应该可以提高性能。您可以通过在索引中使用“包含列”来进一步优化 - 这会将行信息直接复制到索引中，从而消除必须查找行本身来执行选择的性能损失。
注意连接发生的顺序很有用 - 索引中列的顺序应该与此匹配。请记住，SQL 引擎可能会在内部优化和重新排序您的查询 - 分析可能会有所帮助。
在大多数情况下，您只需使用数据库引擎优化顾问即可 - 它提供的建议非常准确。
You only get one clustered index - this is what controls the physical storage of the table on disk / in memory.
Non-clustered indexes repeat the included fields, with pointer to the rows that have that value. Having an index on the columns being used in your joins should improve performance. You can further optimise by using "included columns" in your index - this duplicates the row information directly into the index, which can remove the performance penalty of having to look up the row itself to perform the select.
It is useful to pay attention to the order in which your joins occur - the sequence of columns in your index should match up to this. Remember that the SQL engine may optimise and re-order your query internally - profiling may be helpful.
In most situations, you can just use the Database Engine Tuning Advisor - the recommendations it provides are pretty much spot on.
如果可以的话，最好的选择是使用非聚集索引，其中包含连接的所有元素，如果可能的话，还包含您选择的字段。
这将创建一个跨度索引，这意味着 SQL 需要执行的所有字段都在一个索引上。
如果可能的话，有一个没有 unnessasery 字段的索引。添加的每个字段都会使单个索引记录变大，每个索引记录越小，每个页面中获得的记录就越多。每页中获得的索引项越多，您需要访问磁盘的索引项就越少。
聚集索引 - 意味着表按照索引中指定的顺序排列，这意味着您将获得更好的性能 select * from TABLE where INDEXFIELD = 3。除非您选择大量大数据数据项这不应该是必需的。
If you can your best bet is for a non-clustered index that has all the element of your join in it and if possible the field you are selecting.
This will create a spanning index meaning that all the fields SQL requires to perform are on one index.
If possible have an index which has no unnessasery field in it. Every field added makes the an individual index record larger, the smaller each index record the more you get in each Page. The more index items you get in each page the less you have to go to the Disk.
Clustered Index - Will mean the table is layed out in the order specified in the Index, this means that you will get better performance for select * from TABLE where INDEXFIELD = 3. Unless you are selecting lots of large data items this should not be required.