聚集索引与非聚集索引

悠悠 2022-01-26 15:53 415阅读 0赞

**索引能提高速度的原因**是找到待搜索数据范围的开始和结束位置，然后根据头尾把中间的数据*快速*的取出来。索引其实是把查询语句所需要的少量数据添加到索引分页中，查询的时候通过访问少量的索引分页就可以到可以快速的确定搜索数据范围的开始和结束位置。

**索引存在的目的**是在大量数据中找寻少量数据时，提高查询速度。查询！查询！查询！所以试图用索引增加IUD（insert\\update\\delete）操作效率的人就把枕头垫高点醒一醒。

把一张表放在最前面，因为真正用的时候，你不会去看一大堆的概念分析，看这张表就够了。

**聚集索引与非聚集索引使用的情况：**

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp;动作描述 </td> 
 <td> 使用聚集索引&nbsp; </td> 
 <td> &nbsp;使用非聚集索引 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;外键列 </td> 
 <td> &nbsp;应 </td> 
 <td> &nbsp;应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;主键列 </td> 
 <td> &nbsp;应 </td> 
 <td> &nbsp;应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;列经常被分组排序(order&nbsp;by) </td> 
 <td> &nbsp;应 </td> 
 <td> &nbsp;应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;返回某范围内的数据 </td> 
 <td> &nbsp;应 </td> 
 <td> &nbsp;不应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;小数目的不同值 </td> 
 <td> &nbsp;应 </td> 
 <td> &nbsp;不应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;大数目的不同值 </td> 
 <td> &nbsp;不应 </td> 
 <td> &nbsp;应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;频繁更新的列 </td> 
 <td> &nbsp;不应&nbsp; </td> 
 <td> &nbsp;应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;频繁修改索引列 </td> 
 <td> &nbsp;不应 </td> 
 <td> &nbsp;应 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;一个或极少不同值 </td> 
 <td> &nbsp;不应 </td> 
 <td> &nbsp;不应 </td> 
 </tr> 
 </tbody> 
</table>

# 堆 #

数据库有一个堆的概念。

堆就是存储索引页和数据结点的一个逻辑区。在堆中页面是没有顺序的，页中的行也是没有顺序的。

总结，**堆就是不带聚集索引的表集合存放的一个逻辑区。**

# 聚集索引 #

聚集索引是索引顺序与表中数据的物理存储顺序一致。注意：是索引顺序与数据的顺序一致，物理顺序会因为聚集索引的建立而把【同一类】数据放在同一个数据节点，这样子可以方便查找。所以聚集索引的索引指向看起来会非常乱。

因为表中数据的物理存储顺序只有一个，所以聚集索引在一张表中只有一个。所以聚集索引对于一张表非常宝贵。所以，了解聚集索引在什么情况下使用是非常重要的。

聚集索引使用的一个非常重要的规则是“字段用于范围查询”的规则。这个规则还有一个翻译版——“聚集索引不适合建立在相同值数量特别多或特别少的字段上。”1）若某字段A绝大多数相同，那么建立索引并不能带来明显速度提升（因为它不能尽可能缩小数据范围），还会额外占用空间，说不定还会影响查询速度。比如字段姓别。2）如果某个字段只有极少数相同的值，完全可以用非聚集索引代替。你可以使用聚集索引，比如我们日常会默认主键就是聚集索引。但是这其实是一种浪费。数据库表中会根据主键决定数据是否存储在同一个数据节点上。所以如果主键作为聚集索引，建起来的索引顺序正好与物理顺序一致，会把数据按主键分在不同的结点里。对于底层而言会存在非常少的跨页指针这种消耗。所以相比用主键建立非聚集索引，用主键建聚集索引的空间消耗会更小。但是，出于聚集索引只能有一个。所以如果你的业务需求有范围查询的前提下，你的主键正好不是范围查询使用到的字段，那么聚集索引放在范围查询的字段上的作用会比放在主键上强很多。**举例：有些情况会针对一个自增ID字段建立主键，如果这个字段ID如果不涉及到范围查询，那么没有必要去建立聚集索引。**对于sqlserver，会默认把主键建一个聚集索引，但是大家在做性能优化的时候，需要关注这个聚集索引真的发挥他的价值。

所以聚集索引更准确的建议建立索引的规则是“**用于范围查询的字段才去建立聚集索引**”。

聚集索引最好在范围查询内是返回**整行数据。**

又会有人问，如果是索引的顺序与物理顺序一致，那为什么聚集索引只能有一个？请大家要保持一个观点：没有一个东西是绝对好或者绝对坏的。有得会也有失。索引是为了提高查询效率的。所以对于IUD操作是有影响。因此，如果你一表中有10个聚集索引，我做一个update操作，十个索引都需要做改变。这个消耗是巨大的，那么跟我们建立索引的目的相悖了。而且如果建了多个聚集索引，那么以哪个聚集索引为准来建立物理顺序呢？！因此，聚集索引只能有一个。

# 非聚集索引 #

非聚集索引与聚集索引在概念上的区别我就不说了。非聚集索引与聚集索引实现上最大的不同在于，**非聚集索引是利用聚集索引实现数据快速定位**——非聚集索引先找到聚集索引里具体的页A，再利用页A找到对应的数据存储位置。所以非聚集索引我给他起一个外号叫【基于索引的索引】。

非聚集索引对于只输出某几列时性能更佳。因为此时形成了覆盖索引，索引所存储的内容就是最终输出的数据，所以查询性能更优。

还有一种情况需要用到非聚集索引，就是order by 字段。order by 字段用非聚集索引可以避免执行时进行排序运算。

我个人认为非聚集索引的索引页也是根据索引字段排序好的。这才能达到查询高效的目的。

# 索引的顺序 #

建立索引的时候，索引的顺序非常重要。我们举例来说明。

有索引（a,b,c）。那么查询条件中(a) (a,b) (a,b,c)都会被使用到。注意，查询条件的顺序是不会影响索引的命中。你写where a=1 and b=2 and c=3和where c=3 and b=2 and  a=1是没有区别的。

**但是，如果你的查询条件是(a,d)或者是(b,c)，就不会命中索引（a,b,c）。**

# 如何查看某个sql的执行时间 #

把中间执行的脚本（代码第三行）替换成实际的执行脚本即可。

declare @d datetime
    set @d=getdate()
    select * from table --这里就是没有分号
    select 执行时间 = datediff(ms,@d,getdate())