事件是这样的
上面是我敌人的面试记录：
面试官：讲一下你实习做了什么。
敌人：我在实习期间做了一个存储用户操作记录的性能，次要是从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分钟左右。
本文思维导图
基础知识疾速回顾
家喻户晓，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(log2N)O(log_2N)O(log2N) ，相比磁盘IO来说，能够忽略不计。
MySQL InnoDB 节点的贮存内容
在Innodb的B+树中，咱们常说的节点被称之为 页(page)，每个页当中存储了用户数据，所有的页合在一起组成了一颗B+树（当然理论会简单很多，但咱们只是要计算能够存多少条数据，所以权且能够这么了解😅）。
页 是InnoDB存储引擎治理数据库的最小磁盘单位，咱们常说每个节点16KB，其实就是指每页的大小为16KB。
这16KB的空间，外面须要存储 页格局 信息和 行格局 信息，其中行格局信息当中又蕴含一些元数据和用户数据。所以咱们在计算的时候，要把这些数据的都计算在内。
页格局
每一页的根本格局，也就是每一页都会蕴含的一些信息，总结表格如下：
名称空间含意和作用等File Header38字节文件头，用来记录页的一些头信息。包含校验和、页号、前后节点的两个指针、页的类型、表空间等。Page Header56字节页头，用来记录页的状态信息。包含页目录的槽数、闲暇空间的地址、本页的记录数、已删除的记录所占用的字节数等。Infimum & supremum26字节用来限定当前页记录的边界值，蕴含一个最小值和一个最大值。User Records不固定用户记录，咱们插入的数据就存储在这里。Free Space不固定闲暇空间，用户记录减少的时候从这里取空间。Page Directort不固定页目录，用来存储页当中用户数据的地位信息。每个槽会放4-8条用户数据的地位，一个槽占用1-2个字节，当一个槽位超过8条数据的时候会主动分成两个槽。File Trailer8字节文件结尾信息，次要是用来校验页面完整性的。
示意图：
页格局这块的内容，我在官网翻了良久，硬是没找到🤧。。。。不晓得是没写还是我眼瞎，有找到的敌人心愿能够在评论区帮我挂进去😋。
所以下面页格局的表格内容次要是基于一些博客中学习总结的。
另外，当新记录插入到 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=12838 + 56 + 26 + 8 = 12838+56+26+8=128 字节，每一页留给用户数据的空间就还剩 16×1516×1024−128=1523216 \times \frac{15}{16} \times 1024 – 128 = 1523216×1615×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 字节）。
列的最大长度为： 字符编码的最大字符长度×N字符编码的最大字符长度 \times N字符编码的最大字符长度×N，比方 name 字段的编码为 utf8mb4，那就是 4×104 \times 104×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=198 + 6 + 5 = 198+6+5=19 字节。每页能够存 15232÷19≈80115232 \div 19 \approx 80115232÷19≈801 条索引数据。
那算上页目录的话，按每个槽均匀6条数据计算的话，至多有 801÷6≈134801 \div 6 \approx 134801÷6≈134 个槽，须要占用 268 字节的空间。
把存数据的空间分一点给槽的话，我算进去大概能够存 787 条索引数据。
如果是主键是 int 型的话，那能够存更多，大概有 993 条索引数据。
前两层非叶子节点计算
在 B+ 树当中，当一个节点索引记录为 NNN 条时，它就会有 NNN 个子节点。因为咱们 3 层B+树的前两层都是索引记录，第一层根节点有 NNN 条索引记录，那第二层就会有 NNN 个节点，每个节点数据类型与根节点统一，依然能够再存 NNN 条记录，第三层的节点个数就会等于 N2N^2N2。
则有：
主键为 bigint 的表能够寄存 7872=619369787 ^ 2 = 6193697872=619369 个叶子节点
主键为 int 的表能够寄存 9932=986049993 ^ 2 = 9860499932=986049 个叶子节点
OK计算结束。
数据条数计算
起码寄存记录数
后面咱们提到，最大行长度略小于数据库页面的一半，之所以是略小于一半，是因为每个页面还留了点空间给页格局 的其余内容，所以咱们能够认为每个页面起码能放两条数据，每条数据略小于8KB。如果某行的数据长度超过这个值，那InnoDB必定会分一些数据到 溢出页 当中去了，所以咱们不思考。
那每条数据8KB的话，每个叶子节点就只能寄存 2 条数据，这样的一张表，在主键为 bigint 的状况下，只能寄存 2×619369=12387382 \times 619369 = 12387382×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=304 + 4 + 4 + 6 + 7 + 5 = 304+4+4+6+7+5=30 字节，每个叶子节点能够寄存 15232÷30≈50715232 \div 30 \approx 50715232÷30≈507 条数据。
算上页目录的槽位所占空间，每个叶子节点能够寄存 502 条数据，那么三层B+树能够寄存的最大数据量就是 502×986049=494,996,598502 \times 986049 = 494,996,598502×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字节。
字段内容信息：
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(50 + 250) \times (0.7 \times 3 + 0.25 \times 1 + 0.05 \times 4 ) = 765(50+250)×(0.7×3+0.25×1+0.05×4)=765 字节。
统计下面的所有剖析，共占用 869 字节，则每个叶子节点能够寄存 15232÷869≈1715232 \div 869 \approx 1715232÷869≈17 条，算上页目录，依然能放 17 条。
则三层B+树能够寄存的最大数据量就是 17×619369=10,529,27317 \times 619369 = 10,529,27317×619369=10,529,273，约一千万条数据，再次没想到吧👴。
数据计算总结
依据下面三种不同状况下的计算，能够看出，InnoDB三层B+树状况下的数据存储量范畴为 一百二十多万条 到 将近5亿条，这个跨度还是十分大的，同时咱们也计算了一张博客信息表，能够存储 约一千万条 数据。
所以啊，咱们在做我的项目思考分表的时候还是得多关注一下表的理论状况，而不是自觉的认为两千万数据就是那个临界点。
如果面试时谈到这块的问题，我想面试官也并不是想晓得这个数字到底是多少，而是想看你如何剖析这个问题，看你得出这个数字的过程。
如果本文中有任何写的不对的中央，欢送各位朋友在评论区斧正🥰。
发表回复