mysql分表理由

忘是亡心i 2024-05-05 22:08 41阅读 0赞

## 前提原因 ##

> 面试官：讲一下你实习做了什么。
> 
> 朋友：我在实习期间做了一个存储用户操作记录的功能，主要是从MQ获取上游服务发送过来的用户操作信息，然后把这些信息存到MySQL里面，提供给数仓的同事使用。
> 
> 朋友：**由于数据量比较大，每天大概有四五千多万条，所以我还给它做了分表的操作。每天定时生成3张表，然后将数据取模分别存到这三张表里，防止表内数据过多导致查询速度降低**。

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

> 面试官：那你为什么要分三张表呢，两张表不行吗？四张表不行吗？
> 
> 朋友：因为MySQL每张表最好不超过2000万条数据，否则会导致查询速度降低，影响性能。我们每天的数据大概是在五千万条左右，所以分成三张表比较稳妥。
> 
> **缺点：没有说明为什么是2000万，而不是其他如1000万或3000万呢？**

## 解释说明 ##

#### 大部分500——2000万分表的说法由来 ####

阿里的Java开发手册上也提出：单表行数超过 500 万行或者单表容量超过 2GB，**才推荐**进行分库分表。

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

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

#### 索引认识 ####

> 1.  一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，**每个索引都会有一颗单独的树。**
> 2.  聚簇索引和非聚簇索引：
> 
>  **主键索引也是聚簇索引，非主键索引都是非聚簇索引**。**除格式信息外，两种索引的非叶子节点都是只存索引数据的**，比如索引为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)][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…][github.com_stick-i_scb]） 中的博客表，用于存放博客的基本数据。

分析一下这张表的行记录：

1.  行记录头信息：肯定得有，占用5字节。
2.  可变长度字段列表：表中 `title`占用1字节，`description`占用2字节，共3字节。
3.  null值列表：表中仅`school_code`、`cover_image`、`release_time`3个字段可为null，故仅占用1字节。
4.  事务ID和指针字段：两个都得有，占用13字节。
5.  字段内容信息：
    
    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+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亿条**，这个跨度还是非常大的，同时我们也计算了一张博客信息表，可以存储 **约一千万条** 数据。

**所以啊，我们在做项目考虑分表的时候还是得多关注一下表的实际情况，而不是盲目的认为两千万数据就是那个临界点。**

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

如果本文中有任何写的不对的地方，欢迎各位朋友在评论区指正?

转载地址：[?我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？ - 掘金 (juejin.cn)][MySQL_2000_ - _ _juejin.cn]

[MySQL_2000_ - _ _juejin.cn]: https://juejin.cn/post/7165689453124517896
[github.com_stick-i_scb]: https://link.juejin.cn?target=https%3A%2F%2Fgithub.com%2Fstick-i%2Fscblogs