前提原因
面试官:讲一下你实习做了什么。
朋友:我在实习期间做了一个存储用户操作记录的功能,主要是从MQ获取上游服务发送过来的用户操作信息,然后把这些信息存到MySQL里面,提供给数仓的同事使用。
朋友:由于数据量比较大,每天大概有四五千多万条,所以我还给它做了分表的操作。每天定时生成3张表,然后将数据取模分别存到这三张表里,防止表内数据过多导致查询速度降低。
这表述,好像没什么问题是吧,别急,接着看:
面试官:那你为什么要分三张表呢,两张表不行吗?四张表不行吗?
朋友:因为MySQL每张表最好不超过2000万条数据,否则会导致查询速度降低,影响性能。我们每天的数据大概是在五千万条左右,所以分成三张表比较稳妥。
缺点:没有说明为什么是2000万,而不是其他如1000万或3000万呢?
解释说明
大部分500——2000万分表的说法由来
阿里的Java开发手册上也提出:单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
但实际上,这个2000万或者500万都只是一个大概的数字,并不适用于所有场景,如果盲目的以为表数据只要不超过2000万条就没问题了,很可能会导致系统的性能大幅下降。
实际情况下,每张表由于自身的字段不同、字段所占用的空间不同等原因,它们在最佳性能下可以存放的数据量也就不同。
索引认识
- 一张数据表一般对应一颗或多颗树的存储,树的数量与建索引的数量有关,每个索引都会有一颗单独的树。
- 聚簇索引和非聚簇索引:
主键索引也是聚簇索引,非主键索引都是非聚簇索引。除格式信息外,两种索引的非叶子节点都是只存索引数据的,比如索引为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。
MySQL InnoDB 节点的储存内容
在Innodb的B+树中,我们常说的节点被称之为 页(page),每个页当中存储了用户数据,所有的页合在一起组成了一颗B+树
页 是InnoDB存储引擎管理数据库的最小磁盘单位,我们常说每个节点16KB,其实就是指每页的大小为16KB
这16KB的空间,里面需要存储 页格式 信息和 行格式 信息,其中行格式信息当中又包含一些元数据和用户数据。所以我们在计算的时候,要把这些数据的都计算在内。
每一页留给用户数据的空间就还剩 15232 字节
详情看原版文章——🔥我说MySQL每张表最好不超过2000万数据,面试官让我回去等通知? - 掘金 (juejin.cn)
开始计算
好了,我们已经知道每一页当中具体存储的东西了,现在我们已经具备计算能力了。
由于页的剩余空间我已经在上面页格式的地方计算过了,每页会剩余 15232 字节可用,下面我们直接计算行。
非叶子节点计算
单个节点计算
索引页就是存索引的节点,也就是非叶子节点。
每一条索引记录当中都包含了当前索引的值 、 一个 6字节 的指针信息 、一个 5 字节的行标头,用来指向下一层数据页的指针。
索引记录当中的指针占用空间我没在官方文档里找到😭,这个 6 字节是我参考其他博文的,他们说源码里写的是6字节,但具体在哪一段源码我也不知道😭。
希望知道的同学可以在评论区解惑。
假设我们的主键id为 bigint 型,也就是8个字节,那索引页中每行数据占用的空间就等于 8+6+5=19 字节。每页可以存 15232÷19≈801 条索引数据。
那算上页目录的话,按每个槽平均6条数据计算的话,至少有 801÷6≈134个槽,需要占用 268 字节的空间。
把存数据的空间分一点给槽的话,我算出来大约可以存 787 条索引数据。
如果是主键是 int 型的话,那可以存更多,大约有 993 条索引数据。
前两层非叶子节点计算
在 B+ 树当中,当一个节点索引记录为 N 条时,它就会有 N 个子节点。由于我们 3 层B+树的前两层都是索引记录,第一层根节点有 N 条索引记录,那第二层就会有 N个节点,每个节点数据类型与根节点一致,仍然可以再存 N条记录,第三层的节点个数就会等于 N2次方。
则有:
- 主键为 bigint 的表可以存放 787的2次方 = 619369 个叶子节点
- 主键为 int 的表可以存放 993的2次方 = 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=30 字节,每个叶子节点可以存放 15232÷30≈507 条数据。
算上页目录的槽位所占空间,每个叶子节点可以存放 502 条数据,那么三层B+树可以存放的最大数据量就是 502×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;
这是转载作者——sticki6 开源项目“校园博客”(GitHub地址:github.com/stick-i/scb…) 中的博客表,用于存放博客的基本数据。
分析一下这张表的行记录:
- 行记录头信息:肯定得有,占用5字节。
- 可变长度字段列表:表中
title
占用1字节,description
占用2字节,共3字节。 - null值列表:表中仅
school_code
、cover_image
、release_time
3个字段可为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 字节。
统计上面的所有分析,共占用 869 字节,则每个叶子节点可以存放 15232÷869≈17 条,算上页目录,仍然能放 17 条。
则三层B+树可以存放的最大数据量就是 17×619369=10,529,273,约一千万条数据,再次没想到吧👴。
数据计算总结
根据上面三种不同情况下的计算,可以看出,InnoDB三层B+树情况下的数据存储量范围为 一百二十多万条 到 将近5亿条,这个跨度还是非常大的,同时我们也计算了一张博客信息表,可以存储 约一千万条 数据。
所以啊,我们在做项目考虑分表的时候还是得多关注一下表的实际情况,而不是盲目的认为两千万数据就是那个临界点。
如果面试时谈到这块的问题,我想面试官也并不是想知道这个数字到底是多少,而是想看你如何分析这个问题,看你得出这个数字的过程。
如果本文中有任何写的不对的地方,欢迎各位朋友在评论区指正🥰