前言

参考借鉴文章 我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？

这里自己在总结一下，原因是相关知识欠缺，看别人的文章研究很久才弄明白，所以这里记录一些心得。

作者：阿杆

链接：https://juejin.cn/post/7165689453124517896

来源：稀土掘金

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

抛砖

很多人说，MySQL每张表最好不要超过2000万条数据，否则就会导致性能下降。阿里的Java开发手册上也提出：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

但实际上，这个2000万或者500万都只是一个大概的数字，并不适用于所有场景，如果盲目的以为表数据只要不超过2000万条就没问题了，很可能会导致系统的性能大幅下降。

实际情况下，每张表由于自身的字段不同、字段所占用的空间不同等原因，它们在最佳性能下可以存放的数据量也就不同。

引玉

概念

简单讲一下：

1. 一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，每个索引都会有一颗单独的树。

2. 聚簇索引和非聚簇索引：

   主键索引也是聚簇索引（非叶子节点）：只存储主键和索引列等索引数据。

   非主键索引都是非聚簇索引（叶子节点）：存储真正的所有字段信息。

3. B+树的查询是从上往下一层层查询的，一般情况下我们认为B+树的高度保持在3层以内是比较好的，也就是上两层是索引，最后一层存数据，这样查表的时候只需要进行3次磁盘IO就可以了(实际上会少一次，因为根节点会常驻内存)，且能够存放的数据量也比较可观。

   如果数据量过大，导致B+数变成4层了，则每次查询就需要进行4次磁盘IO了，从而使性能下降。所以我们才会去计算InnoDB的3层B+树最多可以存多少条数据。

4. MySQL每个节点大小默认为16KB，也就是每个节点最多存16KB的数据，可以修改，最大64KB，最小4KB。

节点存储

在Innodb的B+树中，我们常说的节点被称之为 页(page)，每个页当中存储了用户数据，所有的页合在一起组成了一颗B+树；

页 是InnoDB存储引擎管理数据库的最小磁盘单位，我们常说每个节点16KB，其实就是指每页的大小为16KB。

这16KB的空间，里面需要存储 页格式 信息和 行格式 信息，其中行格式信息当中又包含一些元数据和用户数据。所以我们在计算的时候，要把这些数据的都计算在内。

下边具体介绍下啥是页格式和行格式；

注意：如果你不明白，那么就不要硬去理解，先去看后边的计算环节，结合计算再来一点点看这些介绍，因为后边的计算用到的数据都要出自这里的介绍。

页格式

每一页的基本格式，也就是每一页都会包含的一些信息，总结表格如下：

另外，当新记录插入到 InnoDB 聚集索引中时，InnoDB 会尝试留出 1/16 的页面空闲以供将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的页大约可用 15/16 的空间。如果以随机顺序插入记录，则页大约可用 1/2 到 15/16 的空间。

除了 User Records和Free Space 以外所占用的内存是 38+56+26+8=128字节，每一页留给用户数据的空间就还剩 16 × 15/16 × 1024−128=15232字节（保留了1/16）。

当然，这是最小值，因为我们没有考虑页目录。页目录留在后面根据再去考虑，这个得根据表字段来计算。

重点是页目录，下边计算会取一个平均值，根据图上说的是每个槽（不用管啥是槽）会放4~8条，取平均值就是6条，一个槽会占用2byte（取最大值吧）。

行格式

首先，我觉得有必要提一嘴，MySQL5.6的默认行格式为COMPACT(紧凑)，5.7及以后的默认行格式为DYNAMIC(动态)，不同的行格式存储的方式也是有区别的，还有其他的两种行格式，本文后续的内容主要是基于DYNAMIC(动态)进行讲解的。（了解即可）

特性对比（了解即可）：

• DYNAMIC(动态)：

  当使用 DYNAMIC 创建表时，InnoDB 会将较长的可变长度列（比如 VARCHAR、VARBINARY、BLOB 和 TEXT 类型）的值剥离出来，存储到一个溢出页上，只在该列上保留一个 20 字节的指针指向溢出页。

  优点

  DYNAMIC 行格式避免了用大量数据填充 B+ 树节点从而导致长列的问题。

  DYNAMIC 行格式的想法是，如果长数据值的一部分存储在页外，则通常将整个值存储在页外是最有效的。

  使用 DYNAMIC 格式，较短的列会尽可能保留在 B+ 树节点中，从而最大限度地减少给定行所需的溢出页数。

• COMPACT(紧凑)：

  将前 768 个字节和 20 字节的指针存储在 B+ 树节点的记录中，其余部分存储在溢出页上。

  列是否存储在页外取决于页大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合 B+ 树页（文档里没说具体是多少😅）。小于或等于 40 字节的 TEXT 和 BLOB 直接存储在行内，不会分页。

  言归正传：每一行的基本格式，总结表格如下：

  

  重点是事务ID和指针字段、行记录头信息 ，计算的时候要用，因为固定的值也要算上。

  字符编码不同情况下的存储（重要）

  char 、varchar、text 等需要设置字符编码的类型，在计算所占用空间时，需要考虑不同编码所占用的空间。

  • varchar、text等类型会有长度字段列表来记录他们所占用的长度，但char是固定长度的类型，情况比较特殊，假设字段 name 的类型为 char(10) ，则有以下情况：
  • 对于长度固定的字符编码（比如ASCII码），字段 name 将以固定长度格式存储，ASCII码每个字符占一个字节，那 name 就是占用 10 个字节。
  • 对于长度不固定的字符编码（比如utf8mb4），至少将为 name 保留 10 个字节。如果可以，InnoDB会通过修剪尾部空格空间的方式来将其存到 10 个字节中。

    如果空格剪完了还存不下，则将尾随空格修剪为 列值字节长度的最小值（一般是 1 字节）。

    列的最大长度为： 字符编码的最大字符长度×N，比如 name 字段的编码为 utf8mb4，那就是 4×10。

  • 大于或等于 768 字节的 char 列会被看成是可变长度字段（就像varchar一样），可以跨页存储。例如，utf8mb4 字符集的最大字节长度为 4，则 char(255) 列将可能会超过 768 个字节，进行跨页存储。

    总结一下：

    字符编码不同，字段所占用的字节也会不同，固定字符编码（比如ASCII码）就是写多少就是多少，char(10)，就占用10个字节，不固定的（比如utf8mb4）超过10个字节就用字符编码的最大字符长度（utf8mb4的这个值是4，结尾的mb4就代表4个字节，这个可以自己去查）x N，那么char(20)占用字节=4 x 20 =80，如果结果大于768，那么就会跨页存储（知道就行）。

    计算

    计算InnoDB的3层B+树最多可以存多少条数据

    计算之前，先回顾一下前边的东西，页格式和行格式；

    存储总量 = 可变数据 + 固定数据;

    可变数据就是我们实际要存储的数据，固定数据就是内置好的数据，从上边的两个表格就能看到了。

    通过页格式的介绍，我们知道，刨除固定数据量之后，我们得到的剩余存储空间是15232字节。

    那么，我们开始计算不固定的数据量，也就是我们要存储的实际信息，这个就只能举例说明了。

    • 简单一例：

      先计算单个节点的：

      假设我们的主键id为 bigint 型，也就是8个字节；

      那索引页中每行数据占用的空间就等于 8 + 6（事物ID[上表有写]） + 5（行记录头） = 19 字节。

      每页可以存 15232 ÷ 19 ≈ 801 条索引数据。

      那算上页目录的话，按每个槽平均6条数据计算的话，至少有 801 ÷ 6 ≈ 134 个槽，需要占用 268 字节的空间。

      所以最终的结果是（15232-268）÷ 19 ≈ 787条索引数据；

      三层的数据量：

      三层的叶子节点就是787²（反正就是这么算的，不用管，记住公式），也就是787x787= 619369 个叶子节点，每个节点可以存储787条数据，最终能存储 787 x 619369 = （自己算）条数据。

      主键为 int 的表可以存放 993 ^ 2 = 986049 个叶子节点，下边会用。

      • 简单二例：

        – 这是一张非常普通的课程安排表，除id外，仅包含了课程id和老师id两个字段

        – 且这几个字段均为 int (4个字节)型（当然实际生产中不会这么设计表，这里只是举例）。

        CREATE TABLE course_schedule (

        id int NOT NULL,

        teacher_id int NOT NULL,

        course_id int NOT NULL,

        PRIMARY KEY (id) USING BTREE

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        每行数据所占用的空间就是 4 + 4 + 4 + 6 + 7 + 5 = 30 字节，每个叶子节点可以存放 15232÷30≈507条数据。
        页目录占用空间：507 ÷ 6 ≈ 85 x 2 = 170字节
        算上页目录的槽位所占空间，每个叶子节点可以存放 (15232 - 170) ÷ 30 ≈ 502 条数据;
        那么三层B+树可以存放的最大数据量就是 502×986049(主键是int)=494,996,将近5亿条数据！
        

        • 常规一例：

        • CREATE TABLE blog (

          id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘博客id’,

          author_id bigint unsigned NOT NULL COMMENT ‘作者id’,

          title varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘标题’,

          description varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘描述’,

          school_code bigint unsigned DEFAULT NULL COMMENT ‘院校代码’,

          cover_image char(32) DEFAULT NULL COMMENT ‘封面图’,

          create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,

          release_time datetime DEFAULT NULL COMMENT ‘首次发表时间’,

          modified_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,

          status tinyint unsigned NOT NULL COMMENT ‘发表状态’,

          is_delete tinyint unsigned NOT NULL DEFAULT 0,

          PRIMARY KEY (id),

          KEY author_id (author_id),

          KEY school_code (school_code) USING BTREE

          ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

          1、行记录头信息：肯定得有，占用5字节。

          2、可变长度字段列表：表中 title占用1字节，description占用2字节（虽然看着没超过255，但是用的是utf8mb4编码，所以实际是255 x 4,所以占用两个字节），共3字节。

          3、null值列表：表中仅school_code、cover_image、release_time3个字段可为null，故仅占用1字节。

          4、事务ID和指针字段：两个都得有，占用13字节。

          5、字段内容信息：

          • id、author_id、school_code 均为bigint型，各占用8字节，共24字节。
          • create_time、release_time、modified_time 均为datetime类型，各占8字节，共24字节。
          • status、is_delete 为tinyint类型，各占用1字节，共2字节。
          • cover_image 为char(32)，字符编码为表默认值utf8，由于该字段实际存的内容仅为英文字母（存url的），结合前面讲的字符编码不同情况下的存储 ，故仅占用32字节。
          • title、description 分别为varchar(50)、varchar(250)，这两个应该都不会产生溢出页（不太确定），字符编码均为utf8mb4，实际生产中70%以上都是存的中文(3字节)，25%为英文(1字节)，还有5%为4字节的表情，则存满的情况下将占用：

            (50 + 250) × ( 0.7 × 3 + 0.25 × 1 + 0.05 × 4) = 765 字节。

          统计上面的所有分析，共占用 869 字节，则每个叶子节点可以存放 15232 ÷869≈17条，算上页目录，仍然能放 17 条。
          则三层B+树可以存放的最大数据量就是 17 × 619369（根据主键是bigint） = 10,529,27317，约一千万条数据