MySQL十四：单表最大2000W行数据

转载~

在互联网技术圈中有一个说法：「MySQL 单表数据量大于 2000 W行，性能会明显下降」。网传这个说法最早由百度传出，真假不得而知。但是却成为了行业内一个默认的标准。

单表超过2000W行数据一定会导致性能下降吗？我认为是不一定的，虽然说建议单表不超过2000W，但是我不接受它的建议可不可以？那必然也是可以的。

一、单表最大到底能存多少数据

先来看看下面这张图，了解一下mysql各个类型的大小

我们知道在MySQL是支持主键自增长的，不考虑其他因素的前提下，理论上只有主键没有用完，表中的数据就可以一直增加。从上图可以中可以分析出：

• 「主键类型为Int时」

  主键32位，数据最大为2^32-1，大约可以存储21亿的数据，远远大约2KW。

• 「主键类型为bigint时」

  主键64位，数据最大为2^64-1，存储的数据远远大于了常用的计量单位了，磁盘都达不到这个数量级。

• 「主键类型为tinyint时」

  主键8位，数据最大为255，Id自增超过255就会报错

「由此可見：MySQL能够存储的数据在一定程度上受限与主键的类型。但是数据量的大小却跟2000W没啥影响，既然百度大佬推荐单表最大2000W行数据，那肯定不会是空口白话，一定定会有其他影响行数的因素」。

二、数据存储的结构

先不要着急，影响数据行数的因素肯定是有的，在此之前，先来看看数据在InnoDB中是怎么存储在磁盘的，又是怎么读取的。

2.1 数据存储的结构

在MySQL中默认的存储引擎是InnoDB，在之前的《存储引擎》中有说过，InnoDB为每个表都生成了两个文件：

• .frm文件：表结构文件
• .ibd文件：数据文件（聚簇索引包含数据与索引），又叫「表空间」。

我们表中的的数据其实都是存储在磁盘的.ibd文件中，而每次读取整个.ibd文件无疑是非常慢的，所以在《InnoDB数据文件》中又提到，「InnoDB将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16KB」。如下

从上图中可以很清晰的看出，「一个.ibd文件文件是由多个数据页组成的，也就是说一个表的数据会被分散存储在多个数据页中」。当然数据页也不仅仅只是存储表中的数据，先来回顾一下页的组成

• 「页的组成」

如图所示，InnoDB数据页由以下七个部分组成，

从也得组成中我们知道，「数据页中还存储了除数据之外的东西，比如数据页的前后指针，页号，页目录等，因此虽然一个数据页一共16KB，但是能够用来存储数据的其实是不足16KB的」 。

通过页的组成，我们可以大致分析在数据页中一下「查找数据的整体过程」：

• 记录被分散在不同的数据页中，InnoDB通过「页号【表空间的地址偏移量】来标识数据具体在哪一页中」。
• 不通的数据页之间使用「前后指针」进行关联，避免检索消耗，
• 当找到数据在那个数据页之后，InnoDB为避免遍历检索而提供了一个「页目录」，页目录通过「二分查找」将查找效率「从O(n) 变成O(lgn)」，从而快速定位数据的位置。

2.2 索引的结构

既然在.ibd文件中只要知道了页号，就可以快速定位数据行的位置，从而读取到相应的数据。那么问题来了，我咋知道我要找的数据在那个数据页里，咋知道页号是啥？

万事都有解决的方式，要知道页号其实也简单，无非就两种方式：

• 「全表扫描」：简单粗暴，没那么多花花肠子，干就完事，但是「数据量大了，性能就会下降，非长久之计」。
• 「通过索引找到数据页」：重点了解一下这个

在《索引基本原理》中解释了InnoDB索引是基于B+tree实现的，InnoDB在构建B+tree结构时，一般会找出每个数据页中id最小（或者说索引最小， InnoDB主键即聚簇索引）的记录与其对应的页号，「将id与页号组成一个新的记录，存储在一个新生成的数据页中，其大小也为16K，为与存储数据的数据页区分，引入了数据页之间的上下层级关系」，也就是「页层级（page level）」。因此我们知道在B+tree中分为两部分：

• 「叶子节点」：真正存放表中的数据的数据页，page level = 0
• 「非叶子节点」：存放索引以及索引对应数据所在的页号的数据页

根据这张B+tree的图，我们知道数据页之间是有地址指向的，如果要找一条数据，最多只需要经历三次「磁盘IO」就可以将数据页都加载到内存中，从而找到数据，完成查询。

三、B+Tree能存储多少数据

要知道一个B+Tree能存储多少数据其实也不难，B+Tree中的叶子节点存放的是数据，而一个数据页只有16K，我们「假设：数据页中页目录，页头，页尾加起来总共占用1KB，剩余15KB全部用了存放数据」。

如上图：

• 将B+tree的高度定义为N
• 非叶子节点的数据页存储数量为X，也就是有X个数据页的页号
• 叶子节点的数据页存储数据为Y

根据以上定义，B+tree存储的数据总量：「M ={X ^ (N-1)} * Y」

前文中我们说到主键类型会影响行数，那么此时我们「假设主键类型为bigint类型，占8个字节，而在InnoDB源码中页号（FIL_PAGE_OFFSET）被设置为4字节」。则此时非叶子节点能存储的数据量为

「X = 15 * 1024 / (8 +4) = 1280」

前面已经将目录，页头，页尾作为1KB排除，所以这里是15

「基于此：在来做一个假设，假设叶子节点中存储的数据，每条的大小都为1KB，即每个数据页存储15条数据。」

「Y = 15」

现在来看看B+tree的数据量

• 「两层B+tree的数据量（N=2）」

  「M = {X ^ (N-1)} * Y = {1280 ^ (2-1)} * 15 = 19200 条」

• 「三层B+tree的数据量（N=3）」

  「M = {X ^ (N-1)} * Y = {1280 ^ (3-1)} * 15 = 24579000条」

• 「四层B+tree的数据量（N=4）」

  「M = {X ^ (N-1)} * Y = {1280 ^ (4-1)} * 15 = 计算器都算不清楚了」

  可能还没有写到这么多数据，磁盘已经罢工了

「从这里的24579000条，我们就知道为啥单表不推荐超过2000W了，三层B+tree的时候最多只有三次磁盘IO，四层的时候数据量太大，磁盘可能都造不住了」。

四、啥时候能超过2000W的数据

不知道大家有没有注意到一点，在上面计算中，我们都是做了很多假设，其中就有一条：「假设叶子节点中每条数据占用1KB，以此得出一个数据页的数据量Y=15」。

在实际中，要是我一行的数据非常小，仅仅只占用了100KB（比如一个中间表，记录的仅仅是ID），此时

• 「叶子节点数据页的数据量」

  「Y = 15 * 1024 / 100 = 153」

• 「三层B+tree的数据量」

  「M = {X ^ (N-1)} * Y = {1280 ^ (3-1)} * 153 = 250675200条」

「同样是三层B+tree，此时却可以存储2.5亿条数据，增长十倍，但是查找同样只需要三次磁盘IO，并不会对性能有太大影响」。

这里说的「是【叶子节点】数据页的数据行大小」影响了最终存储的数据总量，「实际上【非叶子节点】的数据页存储数量X的大小变化的时候」，也会影响数据总量，但是这种影响一般会在B-tree中体现。

我们知道B-tree跟B+tree最大的区别就是「B-tree的非叶子节点中存储的是真实的数据行，而数据页的大小是16KB固定的，因此相同数据下，B-tree需要更多数据页才能存储数据，数据页增多势必会造成非叶子节点的层级变高，造成更多的磁盘IO，导致性能下降」。这也是InnoDB使用B+tree作为索引结构，而不用B-tree的原因。

• 「总结」

  这里总结一下前文中提的问题「其他影响行数的因素」？现在就很清晰了，除了主键大小和磁盘限制，最重要的就是索引的结构，即B+tree。