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

数据库索引 专栏收录该内容
3 篇文章 0 订阅

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

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字的后面,现在用一个简单的示意图来大概说明一下在数据库中的样子:

地址idusernamescore
0x011小明 90
0x022小红 80
0x033小华 92
0xff256小英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 总结

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

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

张之海

若有帮助,客官打赏一分吧

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值