|
|
|
|
公众号矩阵

一文讲清MySQL中的二级索引

但我们在开发的过程中,往往会根据业务需要在不同的字段上建立索引,这些索引就是二级索引,今天我们就给大家讲讲二级所有的原理。

作者:互联网高级架构师来源:今日头条|2021-11-18 15:08

主键索引是InnoDB存储引擎默认给我们创建的一套索引结构,我们表里的数据也是直接放在主键索引里,作为叶子节点的数据页。

但我们在开发的过程中,往往会根据业务需要在不同的字段上建立索引,这些索引就是二级索引,今天我们就给大家讲讲二级所有的原理。

比如,你给name字段加了一个索引,你插入数据的时候,就会重新搞一棵B+树,B+树的叶子节点,也是数据页,但是这个数据页里仅仅放了主键字段和name字段。

叶子节点的数据页的name值,跟主键索引一样的,都是按照大小排序的。同一个数据页里的name字段值都是大于上一个数据页里的name字段值。

name字段的B+树也会构建多层索引页,这个索引页里放的是下一层的页号和最小name字段值。就像这样:

一文讲清,MySQL中的二级索引

假设你要根据name字段来搜索数据,比如:select * from user where name=‘xxx',过程与主键索引一样的。从name索引的根节点开始找,一层一层的向下找,一直找到叶子节点,定位到name字段值对应的主键值。

但此时叶子节点的数据页没有完整所有字段,就需要根据主键到主键索引里去查找,从主键索引的根节点一路找到叶子节点,就可以找到这行数据的所有字段了,这个过程就叫回表。

二级索引,可以对多个字段建立联合索引,比如,name + age + sex

此时联合索引与单个字段的索引原理是一样的,只不过叶子节点的数据页里放的是id + name + age + sex,然后默认按照name排序,name一样就按age排序,age一样就按sex排序。

每个name + age +sex的索引页里,放的就是下层节点的页号和最小的name + age + sex值。当你用name + age + sex搜索的时候,就会走name + age + sex联合索引这棵树,再回表查询。

一文讲清,MySQL中的二级索引 

以上就是innoDB二级索引的原理了,有没有感觉也不过如此?

索引的利弊

随着我们不停的在表里插入数据,就会不停的在数据页里插入数据,然后一个数据页放满了就会分裂成多个数据页,这个时候就需要索引页去指向各个数据页。

如果数据页太多了,那么索引页里的数据页指针也就会太多了,索引页也必然会放满的,此时索引页也会分裂成多个,再形成更上层的索引页。

这个过程跟主键索引是一模一样的,所以你如果搞懂了主键索引,二级索引也很简单的。

索引的好处是显而易见的,查找数据的时候不需要全表扫描,性能是很高的。

但索引也有其缺点,如果用的不好,反而对会有副作用。

首先,要创建索引,就要占用存储空间。我们每创建一个索引,MySQL就会搞出一个B+树,每棵B+树都要占用很多的磁盘空间啊,所以搞太多索引,也是很耗费磁盘空间的。

其次,你在进行增删改查的时候,每次都需要维护各个索引的数据有序性,因为每个B+树都要求页内是按照值大小来排序的,页之间也是有序的。所以你不停的增删改查,各个索引的数据页要不停的分裂、增加新的索引页,如果你一个表里搞太多索引,增删改的性能就会比较差

所以综合上面两个原因,我们不建议给一张表搞太多索引的。

联合索引查询原理

之所以要讲联合索引的查询原理,是想带着读者们更清晰的理解索引的工作原理,我们平时设计索引也大多是设计的联合索引。

假如有一个索引KEY(class, name, course),对学生班级、姓名、科目名称建立的联合索引。联合索引的示意图如下:

一文讲清,MySQL中的二级索引

每个数据页都包含了联合索引的三个字段值和主键值,数据页内部也是按照顺序来排序的。

首先按照班级值来排序,如果一样则按照学生姓名来排序,如果一样,则按照科目名称来排序,所以数据页内部都是按照这三个字的值来排序的。

数据页内部与数据页之间也是有序的,数据页内部组成单向链表,数据页之间组成双向链表。

图中索引页分别指向两个数据页,索引页放的是数据页里最小的那个数据值。

假如我们要执行语句:select * from student where class='1班‘ and student_name='张强' and course_name='数学'。

查询时先到索引页里去找,索引页里有多个数据页的最小值记录,此时直接在索引页里基于二分查找方法来找就可以了,先根据班级名来找1班这个值对应的数据页,直接可以定位到所在的数据页。

一文讲清,MySQL中的二级索引 

然后就可以找到索引指向的那个数据页就可以了,在数据页内部是一个单向链表, 你也是基于二分查找就可以了,先按1班这个值查询,你发现有几条数据都是1班,然后按照张强这个学生姓名查找,发现也有多条数据,接着按照科目名称来二分查找。

很快就定位到一条数据了,对应的就是图中的id=127的数据。

一文讲清,MySQL中的二级索引 

然后根据主键id=127回表查找完整的字段,在主键索引开始二分查找迅速定位到各层级的索引页,再逐步向下定位到id=127的那条数据,就可以拿到所有字段的值了。

上面的过程就是联合索引的查找过程。对于联合索引,就是一次安装各个字段来进行二分查找,先定位到第一个字段对应的值在哪个页,如果第一个字段值一样,就按第二个字段值来查找,以此类推,就找到最终的数据了。

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. 【王顶】SQL Server 2005 管理与实现视频课程-微软认证MCTS 70-431
  3. 微软改进Cosmos DB并发布SQL Server 2022预览版
  4. MYSQL主从复制深入解析04
  5. 从零实现基于Linux socket聊天室-增加数据库Sqlite功能-5
  6. 聊聊 MySQL 中的游标
【责任编辑:华轩 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

55人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

14人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

42人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微