乐趣区

mysql学习InnoDB数据结构

原来知道有一些索引失效的条件,最近看了看 mysql 底层数据结构,明白了为什么会失效,记录之。众所周知,常用的 mysql 数据引擎有两种,今天全是以 InnoDB 为基础开启探索之旅的,另一种有时间再说吧。

数据页与数据行

我们都知道,数据库数据是存在磁盘中的,不过真正处理数据是在内存中进行的。这就需要从硬盘上不断地把数据读到内存中,由于内存和磁盘速度差了好几个数量级,所以为了避免频繁交互带来的性能问题,mysql 一次会多读取一些,是多少呢?读一页。一页有 16KB,也就是说一次读取一般都是 16KB 的倍数。页是硬盘内存交互的基本单位。

我们平时所说的一条记录叫 数据行,InnoDB 有四种不同类型的数据行,Compact、Redundant、Dynamic 和 Compressed。主要介绍下 Compact

为了方便后面说明,建个表:

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` smallint(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;

    • 变长字段长度列表 :类似于 varchar() 这种可变长度,记录某个属性的长度,方便快速读取某属性的值。长度值是 倒叙 存放的。
    • Null 值列表:记录可为空的那些值,是否为空。

    为了方便说明举例:

    id    name    age
    99    haha    (null)

    1 表示 这个属性为 null,0 表示这个属性不为 null。所以 name 对应着 0,age 对应着 1。由于是 倒叙 存放的,所以 Null 值列表 这个地方存放的是 10(age,name);

    • 记录头信息

    数据有很多,关键的:
    delete_mask:标记该记录是否被删除
    record_type:表示当前记录的类型,0 表示普通记录,1 表示 B + 树非叶子节点记录,2 表示最小记录,3 表示最大记录。

    接下来就是真实数据了,值得一提的是,还有三个 隐藏项

    1. row_id:如果没有主键 ID, 数据库会自动生成一个行的标识 ID。所以这个值是可选的。
    2. transaction_id:事务 ID
    3. roll_pointer: 回滚指针。

    页行关系


    如上图所示,是一张数据页内部结构。一个 16KB 的页,内部存放着很多行,比如说那 3 条记录,除此之外,内部存放着两个特殊的记录,最小记录 最大记录 。数据页内部记录之间是以 单链表 的形式存放的,头尾分别是那两个特殊的记录。在内存中有很多页,页和页之间是用 双链表 连接的。这样方便快速定位到数据在哪一页上。

    B+ 索引

    聚簇索引

    ok,现在知道了数据页、数据行,和它们之间的数据结构之后,就可以看看我们所谓的索引了。正如开头所说的,这边只介绍 InnoDB 的聚簇索引,另一种搜索引擎,先不提它(嗯,现在甚至连名字都不提)。

    聚簇索引,就是说有一颗树,叶子节点就是真实数据行所构成的数据页。


    一般为了搜索快一点,我们主键都是自动生成的(例如咱们的 User 表),所以最下面那层是根据 id 排序生成的。最底下那层的叶子节点是真实的数据,有 4 页,每页里面有一个单链表,就是我们的 真实数据行 。第二行有两页,每页中也是有个数据行构成的单链表,这是的数据行只包含了页码(最底下那层某页)、某页最大 id,由此可见,第二行比最底下那行页数少了很多很多。就这样,一层一层的抽取,一定会有一个所谓的 跟页 。我们搜索数据就是从跟页开始的,一层一层往下找的。由于一个数据页可以存放 16KB 数据,所以三四层的树状图就已经能存放很多很多数据了,所以不要担心树会很深。 再强调一下,页内是单链表,同层的页和页之间是双链表。

    二级索引

    上面那是以主键为搜索条件的索引,一般这棵树是自动生成的。

    我们往往还会自己建立索引,比如给 age 添加索引。与聚簇索引类似,只不过叶子节点存的不是所有数据(并且根据 age 大小排序),而是存的该 age 属性和主键 id,非叶子节点寸的是页码和下面那层某页最大的 age 值。这样,你确定了要搜的是哪些主键,还要 回表(拿着这些主键回去聚簇索引找)去查询真实的数据。这边脑洞一下,即使你给 age 创建了索引,真正执行的时候,也不一定是通过查看二级索引,再回表的方式查数据(比如说通过二级索引搜索出来的是所有的 id,再回表查询,得不偿失啊,还不如直接从聚簇索引直接去搜呢)。也可能根据聚簇索引直接搜索。具体采用哪种方式 mysql 自己会评估。

    联合索引

    还有种特殊的二级索引,联合索引,比如说给(name、age)添加联合索引,底层数据结构和普通二级索引没什么区别,只不过叶子节点存的不是所有数据(并且先根据 name 大小排序,name 相同的情况下再根据 age 排序),而是存的该 name、age 属性和主键 id,非叶子节点寸的是页码和下面那层某页最大的 name 值。所以如果搜索条件只有 age,没有 name 的话,联合索引会失效,所以要遵循最左原则。

    退出移动版