MySQL分库分表的那些事

Rambo
187 阅读10分钟

前言

在日常开发中是否有经常听见同事说MySQL的每张表最好不要超过2000万条数据，否则会影响查询速度进而影响性能。亦或是在阿里的Java开发手册上看到：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

实际上，2000万和500万都只是一个大概数字，并不适合所有的场景。

注：本文以InnoDB中B+树的高度为3层为基础条件来计算每张表适合的数据量！！！

一、MySQL基础知识

1、存储引擎

MySQL的常用引擎：

MyISAM、InnoDB

MyISAM的主要特性：

  • 不支持事务、不支持外键约束

  • 支持全文索引

  • 读取速度较快，占用资源较少

  • 表级锁定：

使用MyISAM存储引擎时，当发生数据更新时，会锁定整个表，以防止其他会话对该表中数据的同时修改所致的混乱。这样做可以使得操作简单，但是会减少并发量。

  • 读写相互阻塞：

在MyISAM类型表中，在向数据表中写入数据的同时，不允许另一个会话也向该表写入数据，也不允许其他的会话读取该表中的数据。只允许多个会话同时读取该数据表中的数据。

InnoDB的主要特性：

  • 支持事务、支持外键约束

  • 支持全文索引（MySQL5.5 版本以后支持）

  • 行级锁定

  • 支持MVCC:

在不同事务之间实现数据的隔离性和一致性，以提高并发性能和可扩展性。

  • 读写阻塞与事务隔离级别相关

2、B+树

  1. 一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，每个索引都会有一颗单独的树。

  2. 聚簇索引和非聚簇索引（从物理实现上划分）：

主键索引是聚簇索引，而非主键索引都是非聚簇索引。除格式信息外，两种索引的非叶子节点都是只存索引数据的，比如索引为id，那么非叶子节点存的就是id数据。

聚簇/非聚簇索引叶子节点的区别如下：

  • 聚簇索引的叶子节点存的是这条数据的所有字段信息。例如：id为主键的情况下 select * from table where id = 1 就是直接从叶子节点取数据的。

  • 非聚簇索引的叶子节点存的是这条数据所对应的主键和索引列信息。比如这条非聚簇索引是name，然后表的主键是id，那该非聚簇索引的叶子节点存的就是 name 和 id。 所以当我们根据非聚簇索引查询时，会先从非聚簇索引查到主键的值，再根据主键索引去查数据内容，一般情况下要查两次（除非索引覆盖），这也称之为 回表。

  1. B+树的查询是从上往下一层层查询的，一般情况下我们认为B+树的高度保持在3层以内是比较好的，也就是上两层是索引，最后一层存数据，这样查表的时候只需要进行3次磁盘IO就可以了(实际上会少一次，因为根节点会常驻内存)。

  2. MySQL每个节点大小默认为16KB，也就是每个节点最多存16KB的数据，这个值可以修改，最大64KB，最小4KB。

那如果某一行的数据特别大，超过了节点的大小怎么办？

MySQL5.7给出的文档的解释是：

  • 对于 4KB、8KB、16KB 和 32KB设置 ，最大行长度略小于数据库默认页的一半 。例如：对于默认的 16KB页大小，最大行长度略小于 8KB ，默认32KB的页大小，则最大行长度略小于16KB。

  • 对于 64KB 页面，最大行则长度略小于 16KB。

  • 如果行数据大小不超过最大行长度，则所有行都本地存储在页中。如果行超过最大行长度， 则将该行数据里的可变长度列用外部页存储，直到该行符合最大行长度限制。 意思是会把varchar、text这种长度可变的存到外部页中，来减小这一行的数据长度。

文档链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html

二、InnoDB存储内容

页 是InnoDB存储引擎管理数据库的最小磁盘单位，我们常说每个节点16KB，其实就是指每页的大小为16KB。这16KB的空间，里面需要存储 页格式 信息和 行格式 信息，其中行格式信息当中又包含一些元数据和用户数据。

1、页格式

示意图：





备注：当有新记录插入到 InnoDB 集群索引中时，InnoDB 会尝试留出 1/16 的页面空闲以供将来插入和更新使用。如果按顺序（升序或降序）插入索引记录，则生成的页大约有15/16 的空间可用 。如果以随机顺序插入记录，则页大约可用 1/2 到 15/16 的空间。

文档链接：dev.mysql.com/doc/refman/…

在页大小默认16KB且不考虑（User Records、Free Space、Page Directort）这3个不固定占用得情况下，每页默认占用的内存是 38+56+26+8=128 字节，每一页留给用户数据的空间就还剩 （16×15/16×1024）−128=15232 字节（保留了1/16）。

2、行格式

MySQL5.6的默认行格式为COMPACT(紧凑)

MySQL5.7及以后的默认行格式为DYNAMIC(动态)

文档链接：dev.mysql.com/doc/refman/…

备注：以下仅列出了计算空间占用所用到的信息

注：后续的内容主要基于DYNAMIC(动态)进行讲解

3、外部页的存储

注：外部页为****DYNAMIC的特性

当使用 ROW_FORMAT=DYNAMIC 行格式创建表时，InnoDB 可以将较长的可变长度列（比如 VARCHAR、VARBINARY、BLOB 和 TEXT 类型）的值剥离出来，存储到一个溢出页上，只在该列上保留一个 20 字节的指针指向溢出页（外部页）。

列是否存储在外部页取决于页大小和行的总大小。当一行太长而超过数据库默认页的一半时，选择最长的列进行页外存储，直到这条聚簇索引记录适合 B+ 树页的大小。小于或等于 40 字节的TEXT 和 BLOB 直接存储在行内不会分页。

4、不同字符编码的存储

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

同编码所占用的空间。

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

  • 对于长度固定的字符编码（比如ASCII码），字段 name 将以固定长度格式存储，ASCII码每个字符占一个字节，那 name 就是占用 10 个字节。

  • 对于长度不固定的字符编码（比如utf8mb4），至少将为 name 保留 10 个字节。如果可以，InnoDB会通过修剪尾部空格空间的方式来将其存到 10 个字节中。如果空格剪完了还存不下，则将尾随空格修剪为列值字节长度的最小值（一般是 1 字节）。

列的最大长度为： 字符编码的最大字符长度×N，比如 name 字段的编码为 utf8mb4，那就是 4×10。

  • 大于或等于 768 字节的 char 列会被看成是可变长度字段（就像varchar一样），可以跨页存储。例如，utf8mb4 字符集的最大字节长度为 4，则 char(255) 列将可能会超过 768 个字节，进行跨页存储。

三、数据计算

根据上面得出，每页留给用户数据的存储空间为15232字节。

1、非叶子节点计算

单个节点计算

索引页就是存索引的节点，也就是非叶子节点。每一条索引记录当中都包含了当前索引的值 、 一个 6字节 的指针信息 、一个 5 字节的行标头，用来指向下一层数据页的指针。

主键id为 bigint 型（8个字节）：

索引页中每行数据占用的空间： 8+6+5=19 字节

每页最大可以存：15232÷19≈801条索引数据

算上页目录（且按每个槽平均6条数据）： 801÷6≈134个槽，即槽空间占用268字节

每页存储的索引条数**：（15232-268）÷19≈787** 条索引数据

主键id为 int 型（4个字节）：

索引页中每行数据占用的空间： 4+6+5=15 字节

每页最大可以存：15232÷15≈1015条索引数据

算上页目录（且按每个槽平均6条数据）： 1015÷6≈169个槽，即槽空间占用338字节

每页存储的索引条数**：（15232-338）÷15=993** 条索引数据

2、前两层非叶子节点计算

在 B+ 树当中，当一个节点索引记录为 N 条时，它就会有 N个子节点。由于我们 3 层B+树的前两层都是索引记录，第一层根节点有 N 条索引记录，那第二层就会有 N 个节点，每个节点数据类型与根节点一致，仍然可以再存 N 条记录，第三层的节点个数就会等于 。

示意图：



则有：

主键为 bigint 的表可以存放 

=619369 个叶子节点

主键为 int 的表可以存放 

=986049 个叶子节点

最少存放记录数

最大行长度略小于数据库页面的一半

，之所以是略小于一半，是由于每个页面还留了点空间给

页格式

的其他内容，所以我们可以认为每个页面最少能放两条数据，每条数据略小于8KB。

那每条数据8KB的话，每个叶子节点就只能存放 2 条数据，这样的一张表，

主键为 bigint 的表可以存放 2×619369=1238738（一百二十多万）

主键为 int 的表可以存放 2×986049=1972818（将近两百万）

较多的存放记录数

假设我们的表如下：

CREATE TABLE `user` (

`id` int NOT NULL,

`age` int NOT NULL,

`sex` int NOT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

上表无null值列表，无可变长字段列表，需要算上行记录头、事务ID、指针字段：

每行数据所占用的空间： 4+4+4+5+6+7=30 字节

每个叶子节点最多可以存放： 15232÷30≈507 条数据

算上页目录（且按每个槽平均6条数据：507÷6 ≈84个 槽**，对应168**个字节

每个节点存储的数据条数：（15232-168）÷ 30 ≈502 条数据

根据以上数据，得出三层B+树可以存放的最大数据量：

502×986049=494996598（将近5亿条数据）！

四、总结

在实际开发中，每张表由于自身的字段不同、字段所占用的空间不同等原因，它们在最佳性能下可以存放的数据量也就不同，所以在实际开发中，考虑分表的时候还是得多关注一下表的实际情况，而不是盲目的认为2000万行或500万条数据就是那个临界点。

参考文档：juejin.cn/post/716568…

blog.csdn.net/weixin_4422…

avatar
文章
阅读
粉丝