为什么MySQL数据库单表建议最大2KW数据？

为什么MySQL数据库单表建议最大2KW数据？

  我们经常会听到一种说法，在MySQL中，数据库单表建议最大两千万条数据，如果超过了，性能就会下降得比较厉害。那么2000w这个值的依据是什么？怎么算出来的呢？今天就带大家来研究一下。

一、 Innodb 存储引擎

1、数据存储

  大部分场景我们都是使用 Innodb 存储引擎，在 MySQL 的 Innodb 引擎中，每一张表在磁盘上都会有对应的一个.ibd文件（Innodb data 文件），这个文件叫表空间。在表空间中，数据是以数据页的形式进行存储的，每一页只有 16KB 的大小。

三个重要的最小单元：

• 磁盘扇区：磁盘上存储数据最小单元是扇区，一个扇区的大小是 512 字节；
• 块 （block）：文件系统（例如EXT4）最小单元是块 （block），一个block 块的大小是 4KB；
• 页（Page） ：Innodb 存储引擎 的最小储存单元——页（Page），一个页的大小是 16KB。
  
    由于文件系统（例如EXT4）的最小单元是块 （block），一个block 块的大小是 4KB，所以，假设一个文件大小只有1个字节，那么这个文件在磁盘上，还是不得不占4KB的空间。
    Innodb 的所有数据文件（后缀为 ibd 的文件），也是存储在磁盘的，当然也是由block组成，所以，Innodb 的所有数据文件，全部都是 16384（16KB）的整数倍。
    在 MySQL 中， InnoDB 页的大小可以通过命令查看，show variables like ‘innodb_page_size’。

2、数据页的结构

  行数据 record 被分成好多份，放到各个页里了，为了唯一标识具体是哪一页，那就需要引入页号（其实是一个表空间的地址偏移量）。同时为了把这些数据页给关联起来，于是引入了前后指针，用于指向前后的页，这些都被加到了页头里，页头 120 B 字节。

  页需要支持读写，16KB 说小也不小，写一半电源线被拔了也是有可能发生的，所以，为了保证数据页的正确性，还引入了校验码，这个被加到了页尾，页尾 8 B 字节。

  剩下的空间才是用来放行数据 record 的，如果行数据 record 行数特别多，进入到页内时会挨个遍历，效率也不太行，所以，为这些数据生成了一个页目录。具体实现细节不重要，只需要知道，它可以通过二分查找的方式将查找效率从 O(n) 变成 O(logn)。

数据页的结构：

在数据插入的过程中，数据页的变化如下：

（1）申请新的数据页；
（2）此时并没有User Records这个部分，当我们插入一条记录时会从Free Space也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records中用于记录的存储；
（3）当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了；
（4）继续有新的记录插入；
（5）重新申请新的页。

二、 B+ tree 结构

  如果想查找一条行数据 record，可以把表空间里每一页查出来，再遍历里面的行数据 record ，挨个判断是不是我们要找的。行数小的时候，这么操作也没啥问题。行数多了，性能就慢了。于是为了加快搜索，可以在每个数据页里选出主键 id 最小的 record，而且只需要它们的主键 id 和所在页的页号，将它们组成新的 record，放入到一个新生成的一个数据页中，这个新数据页跟之前的页结构没啥区别，大小还是 16KB。但为了跟之前的数据页进行区分，数据页里加入了页层级（Page Level）信息，从 0 开始往上算，于是页与页之间就有了上下层级的概念，页跟页之间看起来就像是一棵倒过来的树，也就是我们常说的 B+ 树索引。

  所有的数据以数据页的形式进行存储，为了增加查询效率，数据页与数据页之间是以 B+ 树的形式进行关联的。页的页号并不是连续的，它们在磁盘里也不一定是挨在一起的。使用二分查找，时间复杂度从 O(n) 变成 O(logn)。

• B+ 树的叶子节点存放的是实际存储的行记录数据，数据页；
• B+ 树的非叶子节点存放的是索引内容（子节点中，页的第一条数据的主键ID + 页的地址），索引页；
• B+树的每一层代表一次磁盘IO（性能损耗的点）。

三、 B+ 树存放数据的行数

设：
  非叶子结点存放的指向其他数据页的指针数量为 X ；
  叶子节点存放的行数据数量为 Y ；
  B+ 树的层数（高）为 Z 。

则：

  B+树能存放的总行数 = (X ^ (Z-1)) * Y。

代入计算：

  一个数据页 16KB ，扣除各种信息，可用的空间约为15KB。

  假设主键 ID 为 bigint 类型为 8 byte，页号占用4 byte，非叶子节点里的一条数据是 12 Byte 左右，则：X = 15*1024/12 ≈ 1280，指向 1280 个新的数据页。

  Y 是数据页中能容纳的最大行记录数量，所以与实际存储的行记录的大小有关，假设一条行记录占用的空间大小为1KB，则：Y = 15。

  假设B+树是两层，那 Z = 2，则总行数 = (1280 ^ (2-1)) * 15 ≈ 2 W。

  假设B+树是三层，那 Z = 3，则总行数 = (1280 ^ (3-1)) * 15 ≈ 2.5 KW。

  这个数据是根据每条行记录的大小为 1KB 时估算而来的，而实际情况中并不一定是这个值，所以说，最大建议行数2000w这个值只是一个建议，并非一个标准。

  B 树，B 树叶子结点和非叶子结点上都放数据表行数据。每个数据页 16KB，掐头去尾每页剩15KB，假设一条数据表行数据还是占 1KB，就算不考虑各种页指针的情况下，也只能放个 15 条数据，数据页的扇出明显变小了。计算可承载的总行数的公式也变成了一个等比数列。其中 Z 还是层数的意思。
  15 + 15^2 +15^3 + … + 15^Z
  为了能放两千万左右的数据需要 Z>=6，也就是树需要有 6 层，查一次要访问 6 个页，假设这 6 个页并不连续，为了查询其中一条数据，最坏情况需要进行 6 次磁盘 IO。而 B+ 树同样情况下放两千万数据左右，查一次最多是 3 次磁盘 IO。磁盘 IO 越多则越慢，这两者在性能上差距略大。因此，B+ 树比 B 树更适合成为 MySQL 索引。

  在 InnoDB 中 B+ 树高度一般为1-3层，它就能满足千万级的数据存储。查询数据时，每加载一页（page）代表一次IO，所以通过主键索引查询通常只需要1~3次 I/O 操作即可查找到数据。

  MySQL 都有缓存， B+ 树高度为 3 时，第 1 层和第 2 层的数据都在缓存中，查询只需要一次 I/O 操作，速度很快，但是当数据超出 2KW 时， B+ 树层高会增加，需要再多一次 I/O 操作，查询效率就急速下降了。所以MySQL数据库单表建议最大2KW数据。

InnoDB表必须有主键，并且推荐使用整型的自增主键！！！为什么？
  InnoDB使用聚簇索引将主键组织到一棵B+树中，聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替，如果连这样的索引没有，InnoDB 会生产一个隐藏列 Row_id 来充当默认主键，