聚集索引与非聚集索引的总结

痛定思痛。 2022-05-28 12:18 172阅读 0赞

原文：[https://www.cnblogs.com/s-b-b/p/8334593.html][https_www.cnblogs.com_s-b-b_p_8334593.html]

**G: 聚集索引：每一行所有列的数据都存在一起，根据主键的索引查找；非聚集索引，每列分开存储，根据单列值索引查找。**

## 一.索引简介 ##

众所周知，索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，**聚集索引以及非聚集索引用的是B+树索引**。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引。

> SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。  
>   
> MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。  
>

## 二.聚集索引 ##

聚集（clustered）索引，也叫聚簇索引。

> 定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

单单从定义来看是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，现在用一个简单的示意图来大概说明一下在数据库中的样子：

<table style="color:rgb(51,51,51);margin:0px;padding:0px;border-spacing:0px;max-width:850px;border:1px solid #C0C0C0;"> 
 <thead style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">地址</th> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">id</th> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">username</th> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">score</th> 
  </tr> 
 </thead> 
 <tbody style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">0x01</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">1</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小明</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">90</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">0x02</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">2</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小红</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">80</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">0x03</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">3</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小华</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">92</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">0xff</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">256</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小英</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">70</td> 
  </tr> 
 </tbody> 
</table>

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。

结合上面的表格就可以理解这句话了吧：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

![799055-20180123110627022-1271761295.png][]  
  
  
聚集索引实际存放的示意图

从上图可以看出聚集索引的好处了，索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别），可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，后面会详细讲。因此在查询方面，聚集索引的速度往往会更占优势。

### 创建聚集索引 ###

如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。

1.创建表的时候指定主键（注意：SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引）

create table t1(
        id int primary key,
        name nvarchar(255)
    )

2.创建表后添加聚集索引

SQL Server

create clustered index clustered_index on table_name(colum_name)

MySQL

alter table table_name add primary key(colum_name)

值得注意的是，最好还是在创建表的时候添加聚集索引，由于聚集索引的物理顺序上的特殊性，因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序，会非常地耗费时间以及性能。

## 三.非聚集索引 ##

非聚集（unclustered）索引。

> 定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

![799055-20180123110652600-1035019400.png][]  
  
  
非聚集索引实际存放的示意图

### 聚合索引的二次查询问题 ###

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

如有以下表t1：

<table style="color:rgb(51,51,51);margin:0px;padding:0px;border-spacing:0px;max-width:850px;border:1px solid #C0C0C0;"> 
 <thead style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">id</th> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">username</th> 
   <th style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">score</th> 
  </tr> 
 </thead> 
 <tbody style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">1</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小明</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">90</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">2</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小红</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">80</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">3</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小华</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">92</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">..</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">256</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">小英</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">70</td> 
  </tr> 
 </tbody> 
</table>

以及聚集索引clustered index(id), 非聚集索引index(username)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
    select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

在SQL Server里面查询效率如下所示，Index Seek就是索引所花费的时间，Key Lookup就是二次查询所花费的时间。可以看的出二次查询所花费的查询开销占比很大，达到50%。

![799055-20180123110711303-356385119.png][]

在SQL Server里面会对查询自动优化，选择适合的索引，因此如果在数据量不大的情况下，SQL Server很有可能不会使用非聚集索引进行查询，而是使用聚集索引进行查询，即便需要扫描整个聚集索引，效率也比使用非聚集索引效率要高。

![799055-20180123110721569-1686471054.png][]

本人试过在含有30w行表上建立非聚集索引，查询非聚集索引覆盖以外的列就会变成聚集索引的全索引扫描（index scan）查询来避免二次查询，而在另外一张200w行表才会用到非聚集索引seek对应的列再进行kek lookup，有关于SQL Server的有Index seek，index scan, table scan，key LookUp这几个概念，可以查看这个[blog][]，描写比较详细。

但在MySQL里面就算表里数据量少且查询了非键列，也不会使用聚集索引去全索引扫描，但如果强制使用聚集索引去查询，性能反而比非聚集索引查询要差，这就是两种SQL的不同之处。

还有一点要注意的是非聚集索引其实叶子节点除了会存储索引覆盖列的数据，也会存放聚集索引所覆盖的列数据。

### 如何解决非聚集索引的二次查询问题 ###

#### 复合索引（覆盖索引） ####

建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句

select col1, col2 from t1 where col1 = '213';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

在SQL Server中还有include的用法，可以把非聚集索引里包含的列包含进来，而不一定需要建立复合索引。

## 四.总结与使用心得 ##

1.  使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
2.  非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
3.  不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

另附本人[博客地址][Link 1]

参考资料：  
  
\[1\]：[微软技术支持官方博客][Link 2]  
\[2\]：[聚集索引和非聚集索引（整理）][Link 3]

http://blog.sbb.fun/

[https_www.cnblogs.com_s-b-b_p_8334593.html]: https://www.cnblogs.com/s-b-b/p/8334593.html
[799055-20180123110627022-1271761295.png]: /images/20220528/5356e2bb461641bfb2873c64705a01b2.png
[799055-20180123110652600-1035019400.png]: /images/20220528/76d66b634bcf46c691959677cb475a72.png
[799055-20180123110711303-356385119.png]: /images/20220528/d202af4633da4742aaf830182e0b203b.png
[799055-20180123110721569-1686471054.png]: /images/20220528/2029f6ca0c8d4d57a91cfa53364c98e7.png
[blog]: http://www.cnblogs.com/xwdreamer/archive/2012/07/06/2579504.html
[Link 1]: http://blog.sbb.fun/blog/SQL/index.html
[Link 2]: https://blogs.msdn.microsoft.com/apgcdsd/2012/08/01/sql-serverscan-seek/
[Link 3]: https://www.cnblogs.com/aspnethot/articles/1504082.html