Mysql InnoDB聚簇索引二级索引联合索引特点

目录
  • 一、聚簇索引
    • 特点 1
    • 特点 2
  • 二、二级索引
  • 三、联合索引

接上一篇内容：https://www.jb51.net/article/249934.htm

一、聚簇索引

其实之前内容中介绍的 B+ 树就是聚簇索引。

这种索引不需要我们显示地使用 INDEX 语句去创建，InnoDB 引擎会自动创建。另外，在 InnoDB 引擎中，聚簇索引就是数据的存储方式。

它有 2 个特点：

特点 1

使用记录主键值的大小进行记录和页的排序。

其中又包含了下面 3 个点：

  • 页(包括叶节点和内节点)内的记录按照主键的大小顺序排成一个单向链表。页内记录划分为若干组，每个组中主键值最大的记录在页内的偏移量被当做槽依次存放在页目录中。我们可以通过二分法快速定位主键值等于某个值的记录。
  • 各存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
  • 各存放目录项记录的页分为不同层级。在同一层级中的页，也是根据页中目录项记录的主键大小顺序排成一个双向链表。

特点 2

B+树的叶子节点存储的是完整的用户记录。

这里完整的用户记录就是指，这个记录中存储了所有的列的值(包括隐藏列)。

二、二级索引

聚簇索引只能在我们搜索主键值时才能发挥作用，因为 B+ 树中的数据都是按照主键进行排序。

如果现在我用“别的列”作为搜索条件，怎么办？

答案：再建一个 B+ 树，用这个“别的列”(非主键列)的值大小作为排序规则。

比如之前的内容都是以 c1 列为主键，现在用 c2 列再来创建一个 B+ 树：

看起来跟之前的聚簇索引没啥区别啊？实际上还是存在不同的：

  • 使用记录 c2 列的大小进行记录和页的排序。细分的 3 点与上面聚簇索引介绍的一样，只不过上面是主键，这里是用的 c2 列(非主键)。
  • B+ 树的叶子节点存储的不是完整的用户记录，只有c2 列 + 主键这2个列的值。
  • 目录项记录中不再是主键 + 页号，变成了c2 列 + 页号。

另外需要注意的是，因为 c2 列不是主键，所以没有唯一性约束，可能存在多条满足搜索条件的数据。

现在根据条件 c2 = 4 来查找数据记录，过程如下：

确定第一条符合 c2 = 4 的目录项所在页，也就是页 42。

到页 42 中，进一步确定第一条符合条件的记录所在的用户记录页。因为 2 < 4 <= 4，所以可能存在 页 34 或 35 中。

先到页 34 中定位第一条满足 c2 = 4 的用户记录，如果有就不需要再到页 35 中继续定位了。

在页 34 中定位到第一条记录。因为这条用户记录不完整，所以拿到这条记录的主键，再到聚簇索引中找到完整的用户记录。

上面最后一步，通过携带主键信息到聚簇索引中重新定位完整的用户记录的过程也叫回表。

回表后，再回到这颗新的 B+ 树，找到刚才那个第一个符合条件的记录，并沿着记录的单向链表向后继续搜索其他也满足 c2 = 4 的记录，每找到一条就继续回表操作，重复这个过程。

这种以非主键列的大小为排序规则而建立 B+ 树需要执行回表操作才可以定位到完整的用户记录，这种 B+树就称为二级索引或者辅助索引。

为什么要回表？直接把完整用户记录都放叶子节点不就可以了？

没错，思路没问题。但是这样操作就相当于每建立一颗 B+ 树都把所有的用户记录复制一遍，太浪费存储空间。

三、联合索引

我们可以同时为多个列建立索引，比如 c2 列和 c3 列，以这 2 个列的大小为排序规则建立的 B+ 树索引就称为联合索引，也称为符合索引或多列索引。

这里的按照 c2 和 c3 列大小进行排序，需要注意两点：

  • 先把各个记录和页按照 c2 列进行排序。
  • 在记录的 c2 列都相同的情况下，再采用 c3 列进行排序。

现在，给c2 和 c3 建立联合索引，如图所示：

需要注意的是：

  • 每条目录项记录都是由 c2、c3、页号这 3 部分组成。各记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列进行排序。
  • B+ 树叶子节点的用户记录由 c2、c3、和 主键c1 列组成。

本质上，联合索引也是一个二级索引，只不过它的索引列包括 c2、c3 这2个列。

本文参考书籍：《mysql是怎样运行的》

以上就是Mysql InnoDB聚簇索引二级索引联合索引特点详解的详细内容，更多关于Mysql InnoDB聚簇二级联合索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • Mysql InnoDB B+树索引目录项记录页管理

    Mysql InnoDB B+树索引目录项记录页管理

    目录 Mysql InnoDB B+树索引目录项记录管理 一.目录项记录页 二.当目录项记录页也变多后 三.B+ 树 Mysql InnoDB B+树索引目录项记录管理 接上一篇内容,InnoDB 的作者想到一种更灵活的方式来管理所有目录项,是什么? 一.目录项记录页 其实这些用户目录项与用户记录很像,只是目录项中的两个列记录的是主键和页号而已,那么就可以复用之前存储用户记录的数据页来存储目录项. 为了区分用户记录和目录项,仍然使用 record_type 这个属性,当值为 1 时,表示目录项记

  • MySQL中B树索引和B+树索引的区别详解

    MySQL中B树索引和B+树索引的区别详解

    目录 1.多路搜索树 2.B树-多路平衡搜索树 3.B树索引 4.B+树索引 总结 如果用树作为索引的数据结构,每查找一次数据就会从磁盘中读取树的一个节点,也就是一页,而二叉树的每个节点只存储一条数据,并不能填满一页的存储空间,那多余的存储空间岂不是要浪费了?为了解决二叉平衡搜索树的这个弊端,我们应该寻找一种单个节点可以存储更多数据的数据结构,也就是多路搜索树. 1. 多路搜索树 1.完全二叉树高度:O(log2N),其中2为对数,树每层的节点数: 2.完全M路搜索树的高度:O(logmN),其

  • 为什么Mysql 数据库表中有索引还是查询慢

    为什么Mysql 数据库表中有索引还是查询慢

    目录 前言: 1.字段类型不匹配导致的索引失效 2.被索引字段使用了表达式计算 3.被索引字段使用了内置函数 4.like 使用了 %X 模糊匹配 5.索引字段不是联合索引字段的最左字段 6.or 分割的条件 7.in.not in 可能会导致索引失效 总结 前言: 问题分析: 在进行数据库查询的时候,我们都知道索引可以加快数据查询的效率.但是在实际的业务场景下,经常会遇到即使在表中增加了索引,但是同样还是会出现数据查询慢的问题.这就需要具体分析数据查询慢的具体原因到底是什么了. 首先需要进行确

  • MySQL优化中B树索引知识点总结

    为什么要进行SQL优化呢?很显然,当我们去写sql语句时: 1会发现性能低 2.执行时间太长, 3.或等待时间太长 4.sql语句欠佳,以及我们索引失效 5.服务器参数设置不合理 SQL语句执行过程分析 1.编写过程: 编写过程就是我们平常写sql语句的过程,也可以理解为编写顺序,以下就是我们编写顺序: select from join on where 条件 group by 分组 having过滤组 order by排序 limit限制查询个数 我们虽然是这样去写的,但是它mysql的引擎去

  • Mysql InnoDB中B+树索引使用注意事项

    Mysql InnoDB中B+树索引使用注意事项

    目录 一.根页面万年不动 二.内节点中目录项记录的唯一性 三.一个页面至少容纳 2 条记录 一.根页面万年不动 在之前的文章里,为了方便理解,都是先画存储用户记录的叶子节点,然后再画出存储目录项记录的内节点. 但实际上 B+ 树的行成过程是这样的: 每当为某个表创建一个 B+ 树索引,都会为这个索引创建一个根节点页面.最开始表里没数据,所以根节点中既没有用户记录,也没有目录项记录. 当往表里插入用户记录时,先把用户记录存储到这个根节点上. 当根节点页空间用完,继续插入记录,此时会将根节点中所有记

  • 浅谈MySQL的B树索引与索引优化小结

    浅谈MySQL的B树索引与索引优化小结

    MySQL的MyISAM.InnoDB引擎默认均使用B+树索引(查询时都显示为"BTREE"),本文讨论两个问题: 为什么MySQL等主流数据库选择B+树的索引结构? 如何基于索引结构,理解常见的MySQL索引优化思路? 为什么索引无法全部装入内存 索引结构的选择基于这样一个性质:大数据量时,索引无法全部装入内存. 为什么索引无法全部装入内存?假设使用树结构组织索引,简单估算一下: 假设单个索引节点12B,1000w个数据行,unique索引,则叶子节点共占约100MB,整棵树最多20

  • Mysql InnoDB聚簇索引二级索引联合索引特点

    Mysql InnoDB聚簇索引二级索引联合索引特点

    目录 一.聚簇索引 特点 1 特点 2 二.二级索引 三.联合索引 接上一篇内容:https://www.jb51.net/article/249934.htm 一.聚簇索引 其实之前内容中介绍的 B+ 树就是聚簇索引. 这种索引不需要我们显示地使用 INDEX 语句去创建,InnoDB 引擎会自动创建.另外,在 InnoDB 引擎中,聚簇索引就是数据的存储方式. 它有 2 个特点: 特点 1 使用记录主键值的大小进行记录和页的排序. 其中又包含了下面 3 个点: 页(包括叶节点和内节点)内的记

  • mysql索引(覆盖索引,联合索引,索引下推)

    mysql索引(覆盖索引,联合索引,索引下推)

    目录 什么是索引? 索引的实现方式 innodb的索引模型 索引维护 覆盖索引 联合索引 索引下推 什么是索引? 当我们使用汉语字典查找某个字时,我们会先通过拼音目录查到那个字所在的页码,然后直接翻到字典的那一页,找到我们要查的字,通过拼音目录查找比我们拿起字典从头一页一页翻找要快的多,数据库索引也一样,索引就像书的目录,通过索引能极大提高数据查询的效率. 索引的实现方式 在数据库中,常见的索引实现方式有哈希表.有序数组.搜索树 哈希表 哈希表是通过键值对(key-value)存储数据的索引实现

  • mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

    mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

    目录 聚集索引(Clustered Index) 辅助索引(Secondary Index) 覆盖索引(Covering index) 联合索引 <MySQL技术内幕 InnoDB存储引擎>学习笔记 聚集索引(Clustered Index) 聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据. 举个例子,直观感受下聚集索引. 创建表t,并以人为的方式让每个页只能存放两个行记录(不清楚怎么人为控制每页只存放两个行记录): 最后<MySQL技术内幕>

  • Mysql Innodb存储引擎之索引与算法

    Mysql Innodb存储引擎之索引与算法

    目录 一.概述 二.数据结构与算法 1.二分查找 2.二叉查找树和平衡二叉树 1)二叉查找树 2)平衡二叉树 三.B+树 1.B+树完整定义 2.关于 M 和 L的选定案例 四.B+树索引 1.聚集索引 2.辅助索引 五.关于 Cardinality 值 1.Cardinality定义 2.Cardinality的更新 六.B+树索引的使用 1.联合索引 2.覆盖索引 3.优化器选择不使用索引的情况 4.索引提示 5.Multi-Range Read 优化 (MRR) 6.Index Condi

  • 验证Mysql中联合索引的最左匹配原则详情

    验证Mysql中联合索引的最左匹配原则详情

    目录 前言 如何验证联合索引的有效性 多个单一索引进行验证 联合索引 总结 前言 后端面试中一定是必问mysql的,在以往的面试中好几个面试官都反馈我Mysql基础不行,今天来着重复习一下自己的弱点知识.在Mysql调优中索引优化又是非常重要的方法,不管公司的大小只要后端项目中用到了mysql,几乎都会遇到Mysql查询需要优化的需求.经常有时候前端业务没有压力,经常会在管理后台逻辑中遇到mysql统计查询压力,可能是代码写太烂了,哈哈.在日常工作中我遇到过同事建立索引后问我某个查询条件是否能命

  • MySQL InnoDB 二级索引的排序示例详解

    排序问题 最近看了极客时间上 <MySQL实战45讲>,纠正了一直以来对 InnoDB 二级索引的一个理解不到位,正好把相关内容总结下. PS:本文的所有测试基于 MySQL 8.0.13 . 先把问题抛出来,下面的 SQL 所创建的表,有两个查询语句,哪个索引是非必须的? CREATE TABLE `geek` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, `d` int(11) NOT NULL, P

  • MySQL联合索引用法示例

    MySQL联合索引用法示例

    本文实例讲述了MySQL联合索引.分享给大家供大家参考,具体如下: 员工表 userid 部门表 deptid 员工部门表 条件:一个员工可以对应多个部门 问题:怎么样设置数据库,让其不能重复添加 userid 和deptid? uuid userid deptid 111 212 311(这个就不能让其添加) DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dep

  • MySQL中的联合索引学习教程

    联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引.复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏

  • 深入讲解MySQL Innodb索引的原理

    引言 回想四年前,我在学习mysql的索引这块的时候,老师在讲索引的时候,是像下面这么说的 索引就像一本书的目录.而当用户通过索引查找数据时,就好比用户通过目录查询某章节的某个知识点.这样就帮助用户有效地提高了查找速度.所以,使用索引可以有效地提高数据库系统的整体性能. 嗯,这么说其实也对.但是呢,大家看完这种说法,其实可能还是觉得太抽象了!因此呢,我还想再深入的细说一下,所以就有了此文! 需要说明的是,我说的内容只在Mysql的Innodb引擎中是成立的.在Sql Server.oracle.

随机推荐