数据库索引是什么？联合索引、聚集索引是什么？—— 理解

本文皆学习自文内链接所指的文章，如想了解详情请前往查看，感谢原创作者的贡献。

1 数据库索引是什么？新华字典来帮你

学习自数据库索引是什么？新华字典来帮你

1.1 什么是联合索引

1.1.1 定义

包含多个字段的数据库索引，比如INDEX idx_test(col_a, col_b)。这种包含多个字段的索引就被称为**“联合索引”**。

1.1.2 新华字典中的“联合索引”

新华字典里有一种目录被称为“部首目录”，要使用这个目录我们首先根据部首的笔画数找到对应该能的部分，然后可以在里面找到我们想找的部首。比如如果我们还是要找险字所在的位置：

找到部首后，右边的页码还不是险字真正的页码，我们还需要根据右边的页码找到对应部首在检字表中的位置。找到第93页的检字表后我们就可以根据险字余下的笔画数（7画）在“6-8画”这一部分里找到险字真正的页码了。

在这个过程中，我们按顺序使用了“两个目录”，一个叫做“部首目录”，一个叫做“检字表”。并且我们可以看到上图中检字表的内容都是按部首分门别类组织的。这两个部分合在一起就是我们在本节讨论的主题——联合索引。通过第一个字段的值（部首）在第一级索引中找到对应的第二级索引位置（检字表页码），然后在第二级索引中根据第二个字段的值（笔画）找到符合条件的数据所在的位置（险字的真正页码）。

1.1.3 最左前缀匹配

联合索引中的字段，只有某个字段（笔画）左边的所有字段（部首）都被使用了，才能使用该字段上的索引。例如，有索引INDEX idx_i1(col_a, col_b)，如果查询条件为where col_b = 1，则无法使用索引idx_i1。

1.2 聚集（簇）索引

一篇非常棒的文章，很好地区分了“聚集索引”和“非聚集索引”：聚集索引与非聚集索引的总结

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

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

地址	id	username	score
0x01	1	小明 90
0x02	2	小红 80
0x03	3	小华 92
…	…	…	…
0xff	256	小英	70

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

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

聚集索引实际存放的示意图：
从上图可以看出聚集索引的好处了，索引的叶子节点就是对应的数据节点（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)

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

1.3 总结

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