InnoDB存储结构全解析：行页区段与单表2000W行的关系
<h2 id="逻辑存储结构">逻辑存储结构</h2><p>表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：</p>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261830288.png" alt="" loading="lazy"></p>
<h3 id="行row">行（row）</h3>
<p>数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。</p>
<h3 id="页page">页（page）</h3>
<p>记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。</p>
<p>因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。</p>
<p>数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。</p>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261830259.png" alt="" loading="lazy"></p>
<p>页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的</p>
<p>在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261830220.png" alt="" loading="lazy"></p>
<p>数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。</p>
<p>因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。</p>
<h3 id="区extent">区（extent）</h3>
<p>我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。</p>
<p>B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。</p>
<p>解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。</p>
<p>那具体怎么解决呢？</p>
<p>在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。</p>
<h3 id="段segment">段（segment）</h3>
<p>表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。</p>
<ul>
<li>索引段：存放 B + 树的非叶子节点的区的集合；</li>
<li>数据段：存放 B + 树的叶子节点的区的集合；</li>
<li>回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。</li>
</ul>
<h2 id="为什么说mysql-一般单表不要超过-2000w-行">为什么说MySQL 一般单表不要超过 2000W 行</h2>
<p>总的来说，是因为超过2000W行后，B+树的层级会变高，导致IO次数增多</p>
<h3 id="b树承载的记录数量">B+树承载的记录数量</h3>
<p>Mysql是根据数据页存储的，一个数据页默认是16KB。</p>
<p>当要查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。</p>
<p>在B+树中非叶子节点是不存储数据的，只存储下一级的索引，所以在同样一个 16K 的页，非叶子节点里的每条数据都指向新的页，而新的页有两种可能</p>
<ul>
<li>如果是叶子节点，那么里面就是一行行的数据</li>
<li>如果是非叶子节点的话，那么就会继续指向新的页</li>
</ul>
<p>假设：</p>
<ul>
<li>非叶子节点内指向其他页的数量为 x</li>
<li>叶子节点内能容纳的数据行数为 y</li>
<li>B+ 数的层数为 z</li>
</ul>
<p>所以B+树中存储数据的总数 Total =$x^{z-1} *y$，也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。</p>
<blockquote>
<p>非叶子节点容纳数据行数 —— x = ?</p>
</blockquote>
<p>而页的中包含File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右，假设这些信息就是 1K，那整个页的大小是 16K，剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。</p>
<p>那么一页中就能存储x= $15*1024/12$≈1280 行数据</p>
<blockquote>
<p>叶子结点容纳数据行数 —— y = ?</p>
</blockquote>
<p>叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。</p>
<p>假设一条行数据 1k 来算，那一页就能存下 15 条，Y = $15*1024/1000$ ≈15。</p>
<p>Total =$x^{z-1} *y$，已知 x=1280，y=15：</p>
<ul>
<li>假设 B+ 树是两层，那就是 z = 2， Total = $1280 ^1 *15$ = 19200</li>
<li>假设 B+ 树是三层，那就是 z = 3， Total = $1280 ^2 *15$ = 24576000 （约 2.45kw）</li>
</ul>
<p>这个2.45kw，就是我们常说的单表建议最大行数2kw的由来。毕竟再加一层，数据就大得有点离谱了。三层数据页对应最多三次磁盘IO，也比较合理。</p>
<ul>
<li>临界点 ：当行数突破约2000万时，树高可能从3层变为4层：</li>
<li>树高=4时：最大行数 ≈ 1280^3 × 15 结果已超过百亿（远大于2000万）</li>
<li>性能断崖 ：树高从3→4，查询I/O次数从3次增至4次 （多一次磁盘寻址），尤其在回表查询、高并发、深分页时性能骤降。</li>
</ul>
<h3 id="行数超一亿就慢了吗">行数超一亿就慢了吗？</h3>
<p>上面假设单行数据用了1kb，所以一个数据页能放个15行数据。</p>
<p>如果我单行数据用不了这么多，比如只用了250byte。那么单个数据页能放60行数据。</p>
<p>那同样是三层B+树，单表支持的行数就是 (1280 ^ (3-1)) * 60 ≈ 1个亿。</p>
<p>你看我一个亿的数据，其实也就三层B+树，在这个B+树里要查到某行数据，最多也是三次磁盘IO。所以并不慢。</p>
<h3 id="行数500w就一定不慢吗">行数500w就一定不慢吗？</h3>
<p>比如实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据。<br>
还是按照 z = 3 的值来计算，那 Total = $1280 ^2*3$ = 4915200 （近 500w）<br>
那么建议值就是不超过500w</p>
<h3 id="b树承载的记录数量-1">B树承载的记录数量</h3>
<p>我们都知道，现在MySQL的索引都是B+树，而有一种树，跟B+树很像，叫<strong>B树，也叫B-树</strong>。</p>
<p>它跟B+树最大的区别在于，<strong>B+树只在末级叶子结点处放数据表行数据，而B树则会在叶子和非叶子结点上都放。</strong></p>
<p>于是，B树的结构就类似这样：</p>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202509171228865.png" alt="" loading="lazy"></p>
<p>B树将行数据都存在非叶子节点上，假设每个数据页还是16kb，掐头去尾每页剩15kb，并且一条数据表行数据还是占1kb，就算不考虑各种页指针的情况下，也只能放个15条数据。<strong>数据页扇出明显变少了</strong>。</p>
<p>计算可承载的总行数的公式也变成了一个<strong>等比数列</strong>。15 + 15^2 +15^3 + ... + 15^z</p>
<p>其中<strong>z还是层数</strong>的意思。为了<strong>能放2kw左右的数据，需要z>=6</strong>。也就是树需要有6层，查一次要访问6个页。假设这6个页并不连续，为了查询其中一条数据，最坏情况需要进行<strong>6次磁盘IO</strong>。</p>
<p>而B+树同样情况下放2kw数据左右，查一次最多是<strong>3次磁盘IO</strong>。</p>
<p>磁盘IO越多则越慢，这两者在性能上差距略大。</p>
<p>为此，<strong>B+树比B树更适合成为MySQL的索引</strong>。</p>
<h3 id="小结">小结</h3>
<p>B+树叶子和非叶子结点的数据页都是16k，且数据结构一致，区别在于叶子节点放的是真实的行数据，而非叶子结点放的是主键和下一个页的地址。</p>
<p>B+树一般有两到三层，由于其高扇出，三层就能支持2kw以上的数据，且一次查询最多1~3次磁盘IO，性能也还行。</p>
<p>存储同样量级的数据，B树比B+树层级更高，因此磁盘IO也更多，所以B+树更适合成为MySQL索引。</p>
<p>索引结构不会影响单表最大行数，2kw也只是推荐值，超过了这个值可能会导致B+树层级更高，影响查询性能。</p>
<p>单表最大值还受主键大小和磁盘大小限制。</p>
<p>16KB页与B+树的平衡 ：页大小限制了单页行数和指针数，B+树通过多阶平衡确保低树高。</p>
<p>2000万不是绝对 ：若行小于1KB（如只存ID），上限可到5000万+；若行较大（如含大字段），可能500万就性能下降。</p>
<p><strong>优化建议：</strong></p>
<ul>
<li>控制单行大小（避免TEXT/BLOB直接入表）。</li>
<li>分库分表：单表接近千万级时提前规划。</li>
<li>冷热分离：历史数据归档。</li>
</ul>
<h2 id="总结">总结</h2>
<ul>
<li>MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。</li>
<li>页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。</li>
<li>在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。</li>
<li>索引结构不会影响单表最大行数，2000W 只是推荐值，<strong>超过了这个值可能会导致 B + 树层级更高，影响查询性能</strong>。</li>
</ul>
</div>
<div id="MySignature" role="contentinfo">
<p>本文来自在线网站：seven的菜鸟成长之路，作者：seven，转载请注明原文链接：www.seven97.top</p><br><br>
来源：https://www.cnblogs.com/sevencoding/p/19735717
頁:
[1]