本文已获得第十九期技能专题（数据库技能）TOP1 开奖公示

工作是这样的

下面是我朋友的面试记载：

面试官：讲一下你实习做了什么。

朋友：我在实习期间做了一个存储用户操作记载的功用，主要是从MQ获取上游服务发送过来的用户操作信息，然后把这些信息存到MySQL里边，提供给数仓的搭档运用。

朋友：因为数据量比较大，每天大约有四五千多万条，所以我还给它做了分表的操作。每天定时生成3张表，然后将数据取模分别存到这三张表里，防止表内数据过多导致查询速度下降。

这表述，如同没什么问题是吧，别急，接着看：

面试官：那你为什么要分三张表呢，两张表不行吗？四张表不行吗？

朋友：因为MySQL每张表最好不超越2000万条数据，否则会导致查询速度下降，影响功能。咱们每天的数据大约是在五千万条左右，所以分红三张表比较稳妥。

面试官：还有吗？

朋友：没有了…… ~~你干嘛，哎呦~~

面试官：那你先回去等通知吧。

🤣🤣🤣讲完了，看出什么了吗，你们觉得我这位朋友回答的有什么问题吗？

前言

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

但实践上，这个2000万或许500万都仅仅一个大约的数字，并不适用于一切场景，假如盲目的以为表数据只要不超越2000万条就没问题了，很或许会导致体系的功能大幅下降。

实践状况下，每张表因为自身的字段不同、字段所占用的空间不平等原因，它们在最佳功能下能够寄存的数据量也就不同。

那么，该怎么核算出每张表合适的数据量呢？别急，慢慢往下看。

本文合适的读者

阅览本文你需求有一定的MySQL根底，最好对InnoDB和B+树都有一定的了解，或许需求有一年以上的MySQL学习经验（大约一年？），知道 “InnoDB中B+树的高度一般保持在三层以内会比较好” 这条理论常识。

本文主要是针对 “InnoDB中高度为3的B+树最多能够存多少数据” 这一话题进行解说的。且本文对数据的核算比较严厉（至少比网上95%以上的相关博文都要严厉），假如你比较介意这些细节而且现在不太清楚的话，请持续往下阅览。

阅览本文你大约需求花费10-20分钟的时刻，假如你在阅览的过程中对数据进行验算的话，或许要花费30分钟左右。

本文思维导图

InnoDB三层B+数的存储核算-思维导图.png

根底常识快速回忆

众所周知，MySQL中InnoDB的存储结构是B+树，B+树大家都了解吧？特性大约有以下几点，一起快速回忆一下吧！

注：下面这这些内容都是精华，看不懂或许不了解的同学主张先收藏本文，之后有常识根底了再回来看
。🤣🤣

一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，每个索引都会有一颗独自的树。
聚簇索引和非聚簇索引：

主键索引也是聚簇索引，非主键索引都对错聚簇索引。除格局信息外，两种索引的非叶子节点都是只存索引数据的，比方索引为id，那非叶子节点便是存的id数据。

叶子节点的差异如下：
- 聚簇索引的叶子节点一般状况下存的是这条数据的一切字段信息。所以咱们 select * from table where id = 1 的时分，都是要去叶子节点拿数据的。
- 非聚簇索引的叶子节点存的是这条数据所对应的主键和索引列信息。比方这条非聚簇索引是username，然后表的主键是id，那该非聚簇索引的叶子节点存的便是 username 和 id，而不存其他字段。
  适当所以先从非聚簇索引查到主键的值，再依据主键索引去查数据内容，一般状况下要查两次（除非索引掩盖），这也称之为回表，就有点类似于存了个指针，指向了数据寄存的实在地址。
B+树的查询是从上往下一层层查询的，一般状况下咱们以为B+树的高度保持在3层以内是比较好的，也便是上两层是索引，最后一层存数据，这样查表的时分只需求进行3次磁盘IO就能够了(实践上会少一次，因为根节点会常驻内存)，且能够寄存的数据量也比较可观。

假如数据量过大，导致B+数变成4层了，则每次查询就需求进行4次磁盘IO了，然后使功能下降。所以咱们才会去核算InnoDB的3层B+树最多能够存多少条数据。
MySQL每个节点巨细默许为16KB，也便是每个节点最多存16KB的数据，能够修改，最大64KB，最小4KB。

扩展：那假如某一行的数据特别大，超越了节点的巨细怎么办？
MySQL5.7文档的解释是：
- 关于 4KB、8KB、16KB 和 32KB设置，最大行长度略小于数据库页面的一半。例如：关于默许的 16KB页巨细，最大行长度略小于 8KB ，默许32KB的页巨细，则最大行长度略小于16KB。
- 而关于 64KB 页面，最大行则长度略小于 16KB。
- 假如行超越最大行长度，则将可变长度列用外部页存储，直到该行契合最大行长度限制。
  便是说把varchar、text这种长度可变的存到外部页中，来减小这一行的数据长度。
文档地址：MySQL :: MySQL 5.7 Reference Manual :: 14.12.2 File Space Management
MySQL查询速度主要取决于磁盘的读写速度，因为MySQL查询的时分每次只读取一个节点到内存中，经过这个节点的数据找到下一个要读取的节点方位，再读取下一个节点的数据，直到查询到需求的数据或许发现数据不存在。

必定有人要问了，每个节点内的数据莫非不必查询吗？这儿的耗时怎么不核算？

这是因为读取完整个节点的数据后，会存到内存傍边，在内存中查询节点数据的耗时其实是很短的，再合作MySQL的查询方法，时刻复杂度差不多为 $O(log_2N)$ ，相比磁盘IO来说，能够忽略不计。

MySQL InnoDB 节点的贮存内容

在Innodb的B+树中，咱们常说的节点被称之为 页(page)，每个页傍边存储了用户数据，一切的页合在一起组成了一颗B+树（当然实践会复杂许多，但咱们仅仅要核算能够存多少条数据，所以姑且能够这么了解😅）。

页是InnoDB存储引擎办理数据库的最小磁盘单位，咱们常说每个节点16KB，其实便是指每页的巨细为16KB。

这16KB的空间，里边需求存储 页格局 信息和 行格局 信息，其中行格局信息傍边又包含一些元数据和用户数据。所以咱们在核算的时分，要把这些数据的都核算在内。

页格局

每一页的根本格局，也便是每一页都会包含的一些信息，总结表格如下：

称号	空间	含义和效果等
`File Header`	38字节	文件头，用来记载页的一些头信息。包含校验和、页号、前后节点的两个指针、页的类型、表空间等。
`Page Header`	56字节	页头，用来记载页的状况信息。包含页目录的槽数、闲暇空间的地址、本页的记载数、已删去的记载所占用的字节数等。
`Infimum & supremum`	26字节	用来限定当前页记载的鸿沟值，包含一个最小值和一个最大值。
`User Records`	不固定	用户记载，咱们刺进的数据就存储在这儿。
`Free Space`	不固定	闲暇空间，用户记载增加的时分从这儿取空间。
`Page Directort`	不固定	页目录，用来存储页傍边用户数据的方位信息。每个槽会放4-8条用户数据的方位，一个槽占用1-2个字节，当一个槽位超越8条数据的时分会自动分红两个槽。
`File Trailer`	8字节	文件结束信息，主要是用来校验页面完整性的。

示意图：

页格局这块的内容，我在官网翻了良久，硬是没找到🤧。。。。不知道是没写仍是我眼瞎，有找到的朋友期望能够在谈论区帮我挂出来😋。

所以上面页格局的表格内容主要是依据一些博客中学习总结的。

别的，当新记载刺进到 InnoDB 集合索引中时，InnoDB 会尝试留出 1/16 的页面闲暇以供将来刺进和更新索引记载。假如按次序（升序或降序）刺进索引记载，则生成的页大约可用 15/16 的空间。假如以随机次序刺进记载，则页大约可用 1/2 到 15/16 的空间。参阅文档：MySQL :: MySQL 5.7 Reference Manual :: 14.6.2.2 The Physical Structure of an InnoDB Index

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

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

行格局

首要，我觉得有必要提一嘴，MySQL5.6的默许行格局为COMPACT(紧凑)，5.7及今后的默许行格局为DYNAMIC(动态)，不同的行格局存储的方法也是有差异的，还有其他的两种行格局，本文后续的内容主要是依据DYNAMIC(动态)进行解说的。

官方文档链接：MySQL :: MySQL 5.7 参阅手册 :: 14.11 InnoDB 行格局（包含下面的行格局内容大都能够在里边找到）

每行记载都包含以下这些信息，其中大都是能够从官方文档傍边找到的。我这儿写的不是特别详细，仅写了一些能够咱们核算空间的常识，更详细内容能够去网上搜索 “MySQL 行格局”。

称号	空间	含义和效果等
行记载头信息	5字节	行记载的标头信息包含了一些标志位、数据类型等信息如：删去标志、最小记载标志、排序记载、数据类型、页中下一条记载的方位等
可变长度字段列表	不固定	来保存那些可变长度的字段占用的字节数，比方varchar、text、blob等。若变长字段的长度小于 255字节，就用`1字节`表明；若大于 255字节，用`2字节`表明。表字段中有几个可变长字段该列表中就有几个值，假如没有就不存。
null值列表	不固定	用来存储能够为null的字段是否为null。每个可为null的字段在这儿占用一个bit，便是bitmap的思维。该列表占用的空间是以字节为单位增长的，例如，假如有 9 到 16 个能够为null的列，则运用两个字节，没有占用1.5字节这种状况。
业务ID和指针字段	6+7字节	了解MVCC的朋友应该都知道，数据行中包含了一个6字节的业务ID和一个7字节的回滚指针。假如没有界说主键，则还会多一个6字节的行ID字段当然咱们都有主键，所以这个行ID咱们不核算。
实践数据	不固定	这部分便是咱们实在的数据了。

示意图：

别的还有几点需求留意：

溢出页（外部页）的存储

留意：这一点是DYNAMIC的特性。

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

而 COMPACT 行格局（MySQL5.6默许格局）则是将前 768 个字节和 20 字节的指针存储在 B+ 树节点的记载中，其余部分存储在溢出页上。

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

长处

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

DYNAMIC 行格局的主意是，假如长数据值的一部分存储在页外，则通常将整个值存储在页外是最有用的。

运用 DYNAMIC 格局，较短的列会尽或许保存在 B+ 树节点中，然后最大极限地削减给定行所需的溢出页数。

字符编码不同状况下的存储

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

varchar、text等类型会有长度字段列表来记载他们所占用的长度，但char是固定长度的类型，状况比较特别，假设字段 name 的类型为 char(10) ，则有以下状况：

关于长度固定的字符编码（比方ASCII码），字段 name 将以固定长度格局存储，ASCII码每个字符占一个字节，那 name 便是占用 10 个字节。
关于长度不固定的字符编码（比方utf8mb4），至少将为 name 保存 10 个字节。假如能够，InnoDB会经过修剪尾部空格空间的方法来将其存到 10 个字节中。

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

列的最大长度为： $\times N$ ，比方 name 字段的编码为 utf8mb4，那便是 $\times 10$ 。
大于或等于 768 字节的 char 列会被看成是可变长度字段（就像varchar相同），能够跨页存储。例如，utf8mb4 字符集的最大字节长度为 4，则 char(255) 列将或许会超越 768 个字节，进行跨页存储。

说实话对char的这个规划我是不太了解的，虽然看了良久，包含官方文档和一些博客🤧，期望懂的同学能够在谈论区解惑：

关于长度不固定的字符编码这块，char是不是有点像是一个长度可变的类型了？咱们常用的 utf8mb4，占用为 1 ~ 4 字节，那么 char(10) 所占用的空间便是 10 ~ 40 字节，这个改变仍是挺大的啊，但是它并没有留足够的空间给它，也没有运用可变长度字段列表去记载char字段的空间占用状况，就很特别？

开端核算

好了，咱们现已知道每一页傍边详细存储的东西了，现在咱们现已具有核算才能了。

因为页的剩下空间我现已在上面页格局的当地核算过了，每页会剩下 15232 字节可用，下面咱们直接核算行。

非叶子节点核算

单个节点核算

索引页便是存索引的节点，也就对错叶子节点。

每一条索引记载傍边都包含了当前索引的值 、 一个 6字节的指针信息 、一个 5 字节的行标头，用来指向下一层数据页的指针。

索引记载傍边的指针占用空间我没在官方文档里找到😭，这个 6 字节是我参阅其他博文的，他们说源码里写的是6字节，但详细在哪一段源码我也不知道😭。

期望知道的同学能够在谈论区解惑。

假设咱们的主键id为 bigint 型，也便是8个字节，那索引页中每行数据占用的空间就等于 $8 + 6 + 5 = 19$ 字节。每页能够存 $15232 \div 19 \approx 801$ 条索引数据。

那算上页目录的话，按每个槽平均6条数据核算的话，至少有 $801 \div 6 \approx 134$ 个槽，需求占用 268 字节的空间。

把存数据的空间分一点给槽的话，我算出来大约能够存 787 条索引数据。

假如是主键是 int 型的话，那能够存更多，大约有 993 条索引数据。

前两层非叶子节点核算

在 B+ 树傍边，当一个节点索引记载为 $N$ 条时，它就会有 $N$ 个子节点。因为咱们 3 层B+树的前两层都是索引记载，第一层根节点有 $N$ 条索引记载，那第二层就会有 $N$ 个节点，每个节点数据类型与根节点一致，依然能够再存 $N$ 条记载，第三层的节点个数就会等于 $N^2$ 。

则有：

主键为 bigint 的表能够寄存 $787 ^ 2 = 619369$ 个叶子节点
主键为 int 的表能够寄存 $993 ^ 2 = 986049$ 个叶子节点

OK核算结束。

数据条数核算

最少寄存记载数

前面咱们说到，最大行长度略小于数据库页面的一半，之所以是略小于一半，是因为每个页面还留了点空间给页格局 的其他内容，所以咱们能够以为每个页面最少能放两条数据，每条数据略小于8KB。假如某行的数据长度超越这个值，那InnoDB必定会分一些数据到 溢出页 傍边去了，所以咱们不考虑。

那每条数据8KB的话，每个叶子节点就只能寄存 2 条数据，这样的一张表，在主键为 bigint 的状况下，只能寄存 $\times 619369 = 1238738$ 条数据，也便是一百二十多万条，这个数据量，没想到吧🤣🤣。

较多的寄存记载数

假设咱们的表是这样的：

-- 这是一张非常一般的课程安排表，除id外，仅包含了课程id和教师id两个字段
-- 且这几个字段均为 int 型（当然实践生产中不会这么规划表，这儿仅仅举例）。
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;

先来剖析一下这张表的行数据：无null值列表，无可变长字段列表，需求算上业务ID和指针字段，需求算上行记载头，那么每行数据所占用的空间便是 $4 + 4 + 4 + 6 + 7 + 5 = 30$ 字节，每个叶子节点能够寄存 $15232 \div 30 \approx 507$ 条数据。

算上页目录的槽位所占空间，每个叶子节点能够寄存 502 条数据，那么三层B+树能够寄存的最大数据量便是 $502 \times 986049 = 494,996,598$ ，将近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;

这是我的开源项目“学校博客”（GitHub地址：github.com/stick-i/scb…）中的博客表，用于寄存博客的根本数据。

剖析一下这张表的行记载：

行记载头信息：必定得有，占用5字节。
可变长度字段列表：表中 title占用1字节，description占用2字节，共3字节。
null值列表：表中仅school_code、cover_image、release_time3个字段可为null，故仅占用1字节。
业务ID和指针字段：两个都得有，占用13字节。
字段内容信息：
1. id、author_id、school_code 均为bigint型，各占用8字节，共24字节。
2. create_time、release_time、modified_time 均为datetime类型，各占8字节，共24字节。
3. status、is_delete 为tinyint类型，各占用1字节，共2字节。
4. cover_image 为char(32)，字符编码为表默许值utf8，因为该字段实践存的内容仅为英文字母（存url的），结合前面讲的字符编码不同状况下的存储 ，故仅占用32字节。
5. title、description 分别为varchar(50)、varchar(250)，这两个应该都不会发生溢出页（不太确认），字符编码均为utf8mb4，实践生产中70%以上都是存的中文(3字节)，25%为英文(1字节)，还有5%为4字节的表情😁，则存满的状况下将占用 $50 \times (0.7 \times 3 + 0.25 \times 1 + 0.05 \times 4 ) = 765$ 字节。

核算上面的一切剖析，共占用 869 字节，则每个叶子节点能够寄存 $15232 \div 869 \approx 17$ 条，算上页目录，依然能放 17 条。

则三层B+树能够寄存的最大数据量便是 $17 \times 619369 = 10,529,273$ ，约一千万条数据，再次没想到吧👴。

数据核算总结

依据上面三种不同状况下的核算，能够看出，InnoDB三层B+树状况下的数据存储量范围为 一百二十多万条 到 将近5亿条，这个跨度还对错常大的，同时咱们也核算了一张博客信息表，能够存储 约一千万条 数据。

所以啊，咱们在做项目考虑分表的时分仍是得多重视一下表的实践状况，而不是盲目的以为两千万数据便是那个临界点。

假如面试时谈到这块的问题，我想面试官也并不是想知道这个数字到底是多少，而是想看你怎么剖析这个问题，看你得出这个数字的过程。

假如本文中有任何写的不对的当地，欢迎各位朋友在谈论区指正🥰。

写在后面的一些话

这篇文章写了整整两周😭😭（虽然第一周在划水），真的超级干货了，前前后后查了好多资料，也看了好多博文，官方文档有些当地写的的确迷糊，我看了良久都没看懂😂😂。

学到常识的小伙伴请一定要给我点个赞啊🤧🤧。

最后分享一下我在项目中说到的那个开源项目“学校博客”，GitHub地址：github.com/stick-i/scb…

项目的技能栈主要是：

后端 Java + SpringBoot + SpringCloud + Nacos + Getaway + Fegin + MybatisPlus + MySQL + Redis + ES + RabbitMQ + Minio + 七牛云OSS + Jenkins + Docker

前端 Vue2 + ElementUI + Axios

现在项目还有很大改进和完善的空间，欢迎各位有意愿的同学参与项目奉献（现在特缺前端），一起学习一起前进😋。

🔥我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？

工作是这样的

前言

本文合适的读者

本文思维导图

根底常识快速回忆

MySQL InnoDB 节点的贮存内容

页格局

行格局

溢出页（外部页）的存储

长处

字符编码不同状况下的存储

开端核算

非叶子节点核算

单个节点核算

前两层非叶子节点核算

数据条数核算

最少寄存记载数

较多的寄存记载数

常规表的寄存记载数

数据核算总结

写在后面的一些话

作者信息

🔥我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？

工作是这样的

前言

本文合适的读者

本文思维导图

根底常识快速回忆

MySQL InnoDB 节点的贮存内容

页格局

行格局

溢出页（外部页）的存储

长处

字符编码不同状况下的存储

开端核算

非叶子节点核算

单个节点核算

前两层非叶子节点核算

数据条数核算

最少寄存记载数

较多的寄存记载数

常规表的寄存记载数

数据核算总结

写在后面的一些话

相关文章

【Go】gin实战笔记—template

计算机网络常用知识总结！

Redis布隆过滤器的原理和应用场景，解决缓存穿透

并发编程中的锁、条件变量和信号量

作者信息