乐趣区

mysql学习InnoDB数据结构

jiezi

原来知道有一些索引失效的条件，最近看了看 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 的话，联合索引会失效，所以要遵循最左原则。

    退出移动版