下面是我朋友的面试记录: 面试官:讲一下你实习做了什么。 朋友:我在实习期间做了一个存储用户操作记录的功能,主要是从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+树大家都熟悉吧?特性大概有以下几点,一起快速回顾一下吧! 注:下面这这些内容都是精华,看不懂或者不理解的同学建议先收藏本文,之后有知识基础了再回来看 。 在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=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 行格式”。 示意图: 另外还有几点需要注意: 注意:这一点是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) ,则有以下情况: 说实话对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。 则有: OK计算完毕。 前面我们提到,最大行长度略小于数据库页面的一半,之所以是略小于一半,是由于每个页面还留了点空间给页格式 的其他内容,所以我们可以认为每个页面最少能放两条数据,每条数据略小于8KB。如果某行的数据长度超过这个值,那InnoDB肯定会分一些数据到 溢出页 当中去了,所以我们不考虑。 那每条数据8KB的话,每个叶子节点就只能存放 2 条数据,这样的一张表,在主键为 bigint 的情况下,只能存放 2×619369=12387382 \times 619369 = 12387382×619369=1238738 条数据,也就是一百二十多万条,这个数据量,没想到吧。 假设我们的表是这样的: 先来分析一下这张表的行数据:无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亿条数据!没想到吧。 大部分情况下我们的表字段都不是上面那样的,所以我选择了一场比较常规的表来进行分析,看看能存放多少数据。表情况如下: 分析一下这张表的行记录: 统计上面的所有分析,共占用 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亿条,这个跨度还是非常大的,同时我们也计算了一张博客信息表,可以存储 约一千万条 数据。 所以啊,我们在做项目考虑分表的时候还是得多关注一下表的实际情况,而不是盲目的认为两千万数据就是那个临界点。 如果面试时谈到这块的问题,我想面试官也并不是想知道这个数字到底是多少,而是想看你如何分析这个问题,看你得出这个数字的过程。 如果本文中有任何写的不对的地方,欢迎各位朋友在评论区指正。 这篇文章写了整整两周(虽然第一周在划水),真的超级干货了,前前后后查了好多资料,也看了好多博文,官方文档有些地方写的确实含糊,我看了好久都没看懂。 学到知识的小伙伴请一定要给我点个赞啊。你干嘛,哎呦前言
本文适合的读者
本文思维导图
基础知识快速回顾
MySQL InnoDB 节点的储存内容
页格式
行格式
溢出页(外部页)的存储
优点
字符编码不同情况下的存储
开始计算
非叶子节点计算
单个节点计算
前两层非叶子节点计算
数据条数计算
最少存放记录数
较多的存放记录数
-- 这是一张非常普通的课程安排表,除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;
常规表的存放记录数
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;
数据计算总结
写在后面的一些话