InnoDB存储结构全解析：行页区段与单表2 _

逻辑存储结构表空间由段segment、区extent、页page、行row组成InnoDB存储引擎的逻辑存储结构大致如下图行row数据库表中的记录都是按行row进行存放的每行记录根据不同的行格式有不同的存储结构。页page记录是按照行来存储的但是数据库的读取并不以「行」为单位否则一次读取也就是一次 I/O 操作只能处理一行数据效率会非常低。因此InnoDB 的数据是按「页」为单位来读写的也就是说当需要读一条记录的时候并不是将这个行记录从磁盘读出来而是以页为单位将其整体读入内存。数据库的 I/O 操作的最小单位是页InnoDB 数据页的默认大小是 16KB意味着数据库每次读写都是以 16KB 为单位的一次最少从磁盘中读取 16K 的内容到内存中一次最少把内存中的 16K 内容刷新到磁盘中。页的类型有很多常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的在 File Header 中有两个指针分别指向上一个数据页和下一个数据页连接起来的页相当于一个双向的链表如下图所示数据页中的记录按照「主键」顺序组成单向链表单向链表的特点就是插入、删除非常方便但是检索效率不高最差的情况下需要遍历链表上的所有节点才能完成检索。因此数据页中有一个页目录起到记录的索引作用就像我们书那样针对书中内容的每个章节设立了一个目录想看某个章节的时候可以查看目录快速找到对应的章节的页数而数据页中的页目录就是为了能快速找到记录。区extent我们知道 InnoDB 存储引擎是用 B 树来组织数据的。B 树中每一层都是通过双向链表连接起来的如果是以页为单位来分配存储空间那么链表中相邻的两个页之间的物理位置并不是连续的可能离得非常远那么磁盘查询时就会有大量的随机I/O随机 I/O 是非常慢的。解决这个问题也很简单就是让链表中相邻的页的物理位置也相邻这样就可以使用顺序 I/O 了那么在范围查询扫描叶子节点的时候性能就会很高。那具体怎么解决呢在表中数据量大的时候为某个索引分配空间的时候就不再按照页为单位分配了而是按照区extent为单位分配。每个区的大小为 1MB对于 16KB 的页来说连续的 64 个页会被划为一个区这样就使得链表中相邻的页的物理位置也相邻就能使用顺序 I/O 了。段segment表空间是由各个段segment组成的段是由多个区extent组成的。段一般分为数据段、索引段和回滚段等。索引段存放 B 树的非叶子节点的区的集合数据段存放 B 树的叶子节点的区的集合回滚段存放的是回滚数据的区的集合之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。为什么说MySQL 一般单表不要超过 2000W 行总的来说是因为超过2000W行后B树的层级会变高导致IO次数增多B树承载的记录数量Mysql是根据数据页存储的一个数据页默认是16KB。当要查询一条记录时InnoDB 是会把整个页的数据加载到 Buffer Pool 中因为通过索引只能定位到磁盘中的页而不能定位到页中的一条记录。将页加载到 Buffer Pool 后再通过页里的页目录去定位到某条具体的记录。在B树中非叶子节点是不存储数据的只存储下一级的索引所以在同样一个 16K 的页非叶子节点里的每条数据都指向新的页而新的页有两种可能如果是叶子节点那么里面就是一行行的数据如果是非叶子节点的话那么就会继续指向新的页假设非叶子节点内指向其他页的数量为 x叶子节点内能容纳的数据行数为 yB 数的层数为 z所以B树中存储数据的总数 Total $x^{z-1} *y$也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。非叶子节点容纳数据行数 —— x ?而页的中包含File Header (38 byte)、Page Header (56 Byte)、Infimum Supermum26 byte、File Trailer8byte, 再加上页目录大概 1k 左右假设这些信息就是 1K那整个页的大小是 16K剩下 15k 用于存数据在索引页中主要记录的是主键与页号主键假设是 Bigint (8 byte), 而页号也是固定的4Byte, 那么索引页中的一条数据也就是 12byte。那么一页中就能存储 x $15*1024/12$≈1280 行数据叶子结点容纳数据行数 —— y ?叶子节点和非叶子节点的结构是一样的同理能放数据的空间也是 15k。假设一条行数据 1k 来算那一页就能存下 15 条Y $15*1024/1000$ ≈15。Total $x^{z-1} *y$已知 x1280y15假设 B 树是两层那就是 z 2 Total $1280 ^1 *15$ 19200假设 B 树是三层那就是 z 3 Total $1280 ^2 *15$ 24576000 约 2.45kw这个2.45kw就是我们常说的单表建议最大行数2kw的由来。毕竟再加一层数据就大得有点离谱了。三层数据页对应最多三次磁盘IO也比较合理。临界点 当行数突破约2000万时树高可能从3层变为4层树高4时最大行数 ≈ 1280^3 × 15 结果已超过百亿远大于2000万性能断崖 树高从3→4查询I/O次数从3次增至4次 多一次磁盘寻址尤其在回表查询、高并发、深分页时性能骤降。行数超一亿就慢了吗上面假设单行数据用了1kb所以一个数据页能放个15行数据。如果我单行数据用不了这么多比如只用了250byte。那么单个数据页能放60行数据。那同样是三层B树单表支持的行数就是 (1280 ^ (3-1)) * 60 ≈ 1个亿。你看我一个亿的数据其实也就三层B树在这个B树里要查到某行数据最多也是三次磁盘IO。所以并不慢。行数500w就一定不慢吗比如实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据。还是按照 z 3 的值来计算那 Total $1280 ^2*3$ 4915200 近 500w那么建议值就是不超过500wB树承载的记录数量我们都知道现在MySQL的索引都是B树而有一种树跟B树很像叫B树也叫B-树。它跟B树最大的区别在于B树只在末级叶子结点处放数据表行数据而B树则会在叶子和非叶子结点上都放。于是B树的结构就类似这样B树将行数据都存在非叶子节点上假设每个数据页还是16kb掐头去尾每页剩15kb并且一条数据表行数据还是占1kb就算不考虑各种页指针的情况下也只能放个15条数据。数据页扇出明显变少了。计算可承载的总行数的公式也变成了一个等比数列。15 15^2 15^3 ... 15^z其中z还是层数的意思。为了能放2kw左右的数据需要z6。也就是树需要有6层查一次要访问6个页。假设这6个页并不连续为了查询其中一条数据最坏情况需要进行6次磁盘IO。