白季飞龙的个人主页

MySQL分库分表

什么是分库分表

分库分表指的是将原有的单机单库的单表横向拆分到多机、多库的多表

为什么要分库分表

一般情况下,分库分表主要是为了防止:

  1. 单表数据量太大,行数过多,影响读写速度
  2. 单表数据量太大,磁盘占用过多,难以存储、备份、还原

什么时候需要分库分表

过早优化是万恶之源。分库分表成本极高,不到万不得已,最好不要分库分表。

分库分表之前,我们可以先试试以下几个选项。如果实在必要,再进行分库分表。

分库分表前的几个选项

1. 什么都不做

很多时候,分库分表都是世上无事,庸人自扰。

一千万数据量的表,对我们来说可能是个大表,对MySQL来说,可能毫无压力。MySQL本身对表容量没有做限制,有的用户甚至用MySQL跑着50亿行以上的大表。

一般情况下,MySQL使用InnoDB存储引擎,InnoDB默认的索引页大小是16KB。InnoDB的索引使用的数据结构是B+Tree,一个索引节点存储一个索引值加一个子节点地址。以INT类型的索引为例,一个索引节点占用4+4=8个字节,一个索引页可以存放节点数16KB/8B=2K=2048,构成了一棵2048叉树。

如果在磁盘上查找索引,1000万的数据量需要寻址log(10**7, 2048)=2.11次,也就是2次IO。100亿的数据量需要寻址log(10**10, 2048)=3.02次,也就是3次IO。

100亿的数据量大约需要磁盘空间10**10*100B=1TB,索引占用大约10**10*8B=80GB

所以,只要磁盘、内存和胆子都够大,用MySQL存储100亿的数据完全没问题,也完全可以做到毫秒级的读写。

2. 优化业务

在对数据库动手动脚前,先看看业务系统里是不是有不必要的或者可以整合的读写,是不是储存了太多毫无价值的数据

3. 数据库前端加缓存

适当添加缓存,可以极大地减少数据库的读取操作

4. 数据库加索引

OLTP操作尽量避免全表扫面

5. 数据库垂直切分

按业务分库,按冷热拆表

6. 表分区

将一个表在物理层拆分为多个表,对业务层完全透明。不同的表分区可以指定不同的物理磁盘,加大单表容量,提高并发读写速度,方便进行大表的备份与还原。

7. 换存储引擎

MySQL默认的存储引擎是InnoDB,在不开启压缩的情况下,空间浪费严重,一般1000万行无索引的表占用空间可达1GB。开启压缩后,大概可以节省一半的空间。换用ARCHIVE归档引擎,可以大幅提高压缩率,但是不支持索引,无法进行OLTP查表。换用TokuDB引擎进行折中,可以在支持索引的情况下,大幅压缩磁盘空间占用,大约可以节省80%左右的存储空间。

8. 读写分离

搭建MySQL集群,分离读写,主机写,从机读,主机写入同步从机。多主机可提高可用性,多从机可提高并发。

走完以上八步,一般情况就不需要分库分表了。

如何分库分表

分库分表主要有以下三种方式:

1. 手工分库分表

手工建立多库多表,按照特定的分库规则,读取或写入相应的表。一般用分表键对分表数量取余确定分表名。需要大量修改业务层代码,与业务层耦合严重。性能相对较高,但不易维护与扩展。

2. 中间件分库分表

将手工分库分表的逻辑抽象为一个中间件,对Java来说是自定义javax.sql.DataSource的实现,将分库分表逻辑封装在JDBC后面。对业务层相对透明,但是业界没有太靠谱的免费解决方案。

3. 代理分库分表

代理MySQL连接,业务层不连接真实的MySQL服务器,连接的是MySQL服务器的代理。MySQL代理服务器根据配置好的分库分表规则代理SQL请求,修改并转发SQL到真实的MySQL服务器,最后对接收到的请求结果进行归并处理。对业务层完全透明,但是多开了一个服务,增加了维护成本。

结论

No silver bullet, sometimes no necessay.

文章首发: https://baijifeilong.github.io/2018/11/27/mysql-sharding


漫漫路,莫论逍遥;潜心修,只为悟道