blocks|key|280748|text|您应该使用索引来提高SQL+server的性能。通常，这意味着用于查找表中的行的列被编入索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|280749|聚集索引使SQL+server可以根据索引顺序对磁盘上的行进行排序。这意味着如果您以聚集索引的顺序访问数据，那么数据将以正确的顺序出现在磁盘上。但是，如果具有聚集索引的列频繁更改，则行将在磁盘上来回移动，从而导致开销-这通常不是一个好主意。|280750|索引太多也不好。它们的维护成本很高。因此，从那些显而易见的开始，然后分析一下你错过了哪些，并从中受益。你从一开始就不需要它们，它们可以在以后添加。|280751|大多数列数据类型都可以在索引时使用，但对小列进行索引比对大列进行索引更好。此外，在列组上创建索引也很常见(例如，国家%2B城市%2B街道)。|280752|而且，在表中有相当多的数据之前，您不会注意到性能问题。要考虑的另一件事是，SQL+server需要统计信息才能以正确的方式进行查询优化，因此请确保您确实生成了统计信息。|280753|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

You should be using indexes to help SQL server performance. Usually that implies that columns that are used to find rows in a table are indexed.

Clustered indexes makes SQL server order the rows on disk according to the index order. This implies that if you access data in the order of a clustered index, then the data will be present on disk in the correct order. However if the column(s) that have a clustered index is frequently changed, then the row(s) will move around on disk, causing overhead - which generally is not a good idea.

Having many indexes is not good either. They cost to maintain. So start out with the obvious ones, and then profile to see which ones you miss and would benefit from. You do not need them from start, they can be added later on.

Most column datatypes can be used when indexing, but it is better to have small columns indexed than large. Also it is common to create indexes on groups of columns (e.g. country + city + street).

Also you will not notice performance issues until you have quite a bit of data in your tables. And another thing to think about is that SQL server needs statistics to do its query optimizations the right way, so make sure that you do generate that.

blocks|key|3201418|text|通常，对将用于(大量)搜索表的列使用索引，例如主键(默认情况下具有聚集索引)。例如，如果您有这样的查询(伪代码)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3201419|SELECT+*+FROM+FOO+WHERE+FOO.BAR+=+2|code-block|syntax|javascript|3201420|您可能希望将索引放在FOO.BAR上。应该对将用于排序的列使用聚集索引。聚集索引用于对磁盘上的行进行排序，因此每个表只能有一个。例如，如果您有以下查询|3201421|SELECT+*+FROM+FOO+ORDER+BY+FOO.BAR+ASCENDING|3201422|您可能需要考虑在FOO.BAR上使用聚集索引。|3201423|可能最重要的考虑因素是查询花费了多少时间。如果查询不需要花费太多时间或不经常使用，则可能不值得添加索引。一如既往，首先分析，然后优化。SQL+Server+Studio可以为您提供优化位置的建议，而MSDN提供了一些您可能会发现有用的1信息|offset|length|3201424|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189271.aspx^0|0|0|0|0|0|39|1|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@$O|14|P|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

In general, use an index on a column that's going to be used (a lot) to search the table, such as a primary key (which by default has a clustered index). For example, if you have the query (in pseudocode)

<pre><code>SELECT * FROM FOO WHERE FOO.BAR = 2
</code></pre>

You might want to put an index on FOO.BAR. A clustered index should be used on a column that will be used for sorting. A clustered index is used to sort the rows on disk, so you can only have one per table. For example if you have the query

<pre><code>SELECT * FROM FOO ORDER BY FOO.BAR ASCENDING
</code></pre>

You might want to consider a clustered index on FOO.BAR.

Probably the most important consideration is how much time your queries are taking. If a query doesn't take much time or isn't used very often, it may not be worth adding indexes. As always, profile first, then optimize. SQL Server Studio can give you suggestions on where to optimize, and MSDN has some information<a href="http://msdn.microsoft.com/en-us/library/ms189271.aspx" rel="nofollow">1</a> that you might find useful

blocks|key|2988005|text|你真的需要把两个问题分开：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2988006|1)+主键是一个逻辑结构--唯一且可靠地标识表中每一行的候选键之一。这可以是任何东西，真的-一个INT，一个GUID，一个字符串-选择对你的场景最有意义的。|offset|length|style|BOLD|2988007|2988008|默认情况下，SQL+Server表上的主键也用作聚集键-但不需要这样做！|2988009|我的一条经验法则是:任何“常规”表(用来存储数据的表，即查找表等)应该有一个聚类键。没有聚集键真的没有意义。实际上，与人们普遍认为的相反，拥有聚集键实际上加快了所有常见操作的速度-甚至是插入和删除(因为表组织不同，通常比使用堆-没有聚集键的表)更好。|2988010|Kimberly+Tripp,+the+Queen+of+Indexing有很多优秀的文章，主题是为什么要有聚集键，以及最好使用哪种列作为聚集键。因为每个表只有一个，所以最重要的是选择正确的聚集键-而不是任何一个聚集键。|2988011|2988012|GUIDs+as+PRIMARY+KEY+and/or+clustered+key|unordered-list-item|2988013|The+clustered+index+debate+continues|2988014|Ever-increasing+clustering+key+-+the+Clustered+Index+Debate..........again!|2988015|Disk+space+is+cheap+-+that's++the+point!|2988016|2988017|Marc|2988018|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx|1|2|http://sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx|3|http://www.sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx|4|http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx^0|0|3|2|0|0|0|0|0|11|0|0|0|0|15|1|0|0|10|2|0|0|23|3|0|0|14|4|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@$D|1I|E|1J|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1L|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1M|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1N|8|@]|9|@$D|1O|E|1P|1|1Q]]|A|$]]|$1|O|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1S|8|@]|9|@$D|1T|E|1U|1|1V]]|A|$]]|$1|S|3|T|5|R|7|1W|8|@]|9|@$D|1X|E|1Y|1|1Z]]|A|$]]|$1|U|3|V|5|R|7|20|8|@]|9|@$D|21|E|22|1|23]]|A|$]]|$1|W|3|X|5|R|7|24|8|@]|9|@$D|25|E|26|1|27]]|A|$]]|$1|Y|3|-4|5|6|7|28|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|29|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|2A|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]|19|$5|14|15|16|A|$17|18]]|1A|$5|14|15|16|A|$17|1B]]|1C|$5|14|15|16|A|$17|1D]]|1E|$5|14|15|16|A|$17|1F]]]]

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option. 

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! 

One rule of thumb I would apply is this: any "regular" table (one that you use to store data in, that is a lookup table etc.) should have a clustering key. There's really no point not to have a clustering key. Actually, contrary to common believe, having a clustering key actually speeds up all the common operations - even inserts and deletes (since the table organization is different and usually better than with a heap - a table without a clustering key).

<a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx" rel="noreferrer">Kimberly Tripp, the Queen of Indexing</a> has a great many excellent articles on the topic of why to have a clustering key, and what kind of columns to best use as your clustering key. Since you only get one per table, it's of utmost importance to pick the right clustering key - and not just any clustering key.

<ul>
<li><a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx" rel="noreferrer">GUIDs as PRIMARY KEY and/or clustered key</a></li>
<li><a href="http://sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx" rel="noreferrer">The clustered index debate continues</a></li>
<li><a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx" rel="noreferrer">Ever-increasing clustering key - the Clustered Index Debate..........again!</a></li>
<li><a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx" rel="noreferrer">Disk space is cheap - that's not the point!</a></li>
</ul>

Marc

blocks|key|277219|text|聚集索引改变了行的存储方式。在对一列(或多列)创建聚集索引时，SQL+server将按该列对表的行进行排序。它就像一本字典，在整本书中，所有的单词都按字母顺序排序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|277220|另一方面，非聚集索引不会改变行在表中的存储方式。它在表中创建一个完全不同的对象，该对象包含选定用于索引的列和一个指向包含数据的表的行的指针。它就像是一本书最后一页的索引，其中的关键字被排序，并包含了该书材料的页码，以便更快地参考。|277221|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. 

A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference.

blocks|key|3201550|text|使用示例比较非聚集索引和聚集索引|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3201551|作为非聚集索引的一个示例，假设我们在EmployeeID列上有一个非聚集索引。非聚集索引将存储|3201552|3201553|+EmployeeID|blockquote|3201554|3201555|3201556|以及指向Employee表中实际存储该值的行的指针。但另一方面，聚集索引实际上将存储特定EmployeeID的行数据-因此，如果您正在运行一个查找EmployeeID为15的查询，则来自表中其他列的数据将如下所示|3201557|3201558|+EmployeeName、EmployeeAddress等|3201559|3201560|3201561|。都将实际存储在聚集索引本身的叶节点中。|3201562|这意味着，对于非聚集索引，需要执行额外的工作来跟随指向表中的行的指针来检索任何其他所需的值，而聚集索引只能直接访问该行，因为它的存储顺序与聚集索引本身相同。因此，从聚集索引读取通常比从非聚集索引读取快。|3201563|entityMap^0|0|4|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|14|8|@]|D|@]|E|$]]|$1|I|3|J|5|K|7|15|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|16|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|17|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|18|8|@]|D|@]|E|$]]|$1|P|3|-4|5|6|7|19|8|@]|D|@]|E|$]]|$1|Q|3|R|5|K|7|1A|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]|$1|T|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1D|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|Z|$]]

A comparison of a non-clustered index with a clustered index with an example

As an example of a non-clustered index, let’s say that we have a non-clustered index on the EmployeeID column. A non-clustered index will store both the value of the 

<blockquote>
 EmployeeID
</blockquote>

AND a pointer to the row in the Employee table where that value is actually stored. But a clustered index, on the other hand, will actually store the row data for a particular EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data from other columns in the table like 

<blockquote>
 EmployeeName, EmployeeAddress, etc
</blockquote>

. will all actually be stored in the leaf node of the clustered index itself.

This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generally faster than reading from a non-clustered index.

blocks|key|2988078|text|读取速度比非集群快，因为数据是按索引顺序物理存储的，所以我们只能为每个表创建一个索引。(集群索引)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2988079|insert和update操作比簇索引快。我们可以创建n个非集群索引。|2988080|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

faster to read than non cluster as data is physically storted in index order
we can create only one per table.(cluster index)

quicker for insert and update operation than a cluster index.
we can create n number of non cluster index.

I need to add proper <code>index</code> to my tables and need some help.

I'm confused and need to clarify a few points:

<ul>
<li>Should I use index for <code>non-int</code> columns? Why/why not</li>
<li>I've read a lot about <code>clustered</code> and <code>non-clustered</code> index yet I still can't decide when to use one over the other. A good example would help me and a lot of other developers. </li>
</ul>

I know that I shouldn't use indexes for columns or tables that are often updated. What else should I be careful about and how can I know that it is all good before going to test phase?

Difference between clustered and nonclustered index

5位重磅专家聚焦 AI 时代开发者核心成长需求，带你深入解锁全栈进化、人机协作关键路径！点击报名到场：<a href="https://cloud.tencent.com/developer/salon/salon-2403/signup" target="_blank">https://cloud.tencent.com/developer/salon/salon-2403/signup</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/487c82639d2338df3ef66dfb1450601a.png"/>

腾讯技术创作特训营「年度论坛」正在报名中！

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我需要添加适当的index到我的表中，并需要一些帮助。我很困惑，需要澄清几点：我应该对non-int列使用索引吗？为什么不使用我读了很多关于clustered和non-clustered索引的内容，但是我仍然不能决定什么时候使用它们。一个很好的例子会对我和其他许多开发人员有帮助。我知道我不应该对经常更新的列或表使用索引...

问聚集索引与非聚集索引的区别
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问聚集索引与非聚集索引的区别EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问聚集索引与非聚集索引的区别
EN