分库分表笔记

robbendev
485 阅读4分钟

MySQL表的限制

操作系统块大小最大文件尺寸｜最大系统文件尺寸
1KB16GB2TB
2KB256GB8TB
4KB2TB16TB
8KB16TB32TB
# getconf PAGESIZE
4096
# fdisk -l /dev/vda

这就说明 MySQL 单表的最大尺寸不能超过 2TB，我们简单来算一下，假设一个表的平均行长度为32KB（InnoDB最大行长度限制65536字节，64KB），那么他最大能存储多少行数据？4 x 1024 x 1024 x 1024 / 32 = 134217728大约 1.4 亿不到。

分表方案

分表的场景是单表数据增长速度过快，影响了业务接口的时间，但是MySQL实例的负载并不高，这时候只需要分表，不需要分库（拆分实例）。

一个表的大小满足公式 TABLE_SIZE = AVG_ROW_SIZE x ROWS 从这里可以知道表太大要么是平均长度太大，也就是说表的字段太多，要么是表的记录太多。从而产生两种不同的分表方案，即切分字段（垂直分表）和切分记录（水平分表）。

垂直分表

以订单表为例子，按照字段进行拆分，这里面需要考虑一个问题，如何拆分字段才能将表上的DML性能最大化。常规的方案是冷热分离（频率高的放到一张表里面，剩下使用比较低频的字段放到另一张表里）。

orders表经过拆分变成orders1，orders2。文件也变成两个orders1.ibd，orders2.idb。

原来的查询

select id,order_amount,order_explain from orders where order_id = 1;

如果order_amount在orders1中，orders_explain在orders2中。sql需要重写：

select t1.id ,t1.order_amount,t2.order_explain from orders1 t1 ,orders2 t2
on t1.id = t2.id where t1.id = 1;

如果使用了查询中间件如mycat，sharding-jdbc 会实现自动重写。
如果业务表用了text字段来存储数据，这时候可以利用垂直拆分来减少表的大小，将text字段拆分到子表中。

水平分表

还是以订单表为例子，按照阿里云RDS实践，单表不建议超过500W。

按照id分表

问题是按照什么来分表，按照订单id分表的话，根据id查询订单id的场景几乎没有，业务场景大多数是根据用户id访问的，用户id唯一性又很高，一个user_id对应的订单也不多，所以按照user_id作为sharding key可以满足大部分业务场景，拆分后的数据也很均匀。

按照实践分表

在账务费用系统中，每天晚上都会做前一天的结算或者日账任务。每月1号需要做月结任务。这些数据都是静态数据，业务层面不再需要，这个时候可以按月创建表，比如账单bills，就可以按月创建表，出完任务后就可以归档到历史数据库了，用于数据仓库ETL来做分析报表，确认数据都同步到历史库之后就可以删除这些表释放空间。

MySQL内部分区表

MySQL 的 HASH 分区也是基于分区个数取模（%）运算的，跟上面的user_id % 分区数是一样的。

create table orders(
    ...
    PARTITION BY HASH(user_id)
    PARTITIONS 20;
)

这样就创建了20个分区，对应磁盘上就是20个文件。

分库方案

按业务分库

比如订单服务一个数据库，产品服务一个库。

按表分库

比如20亿数据被拆分成20个子表，一个库10张表。

分布式数据库

  1. MySQL单Master写入瓶颈。
  2. 分库分表SQL解析处理，服务调用链路变长，系统变的不稳定。
  3. 分库分表动态扩容不好实现，开始分了20个表，不影响业务的情况下扩容至50个表。

问题

垂直拆分

1.跨库join问题

  • 业务层面避免
  • 全局表（mycat）
  • 数据同步 比如直接把product库的数据同步orders库中，可以直接在orders库作join操作。比较依赖同步工具，需要做好风险评估和兜底方案。

2.分布式事务问题

老生常谈

  • XA
  • 本地消息表

水平拆分

1.分布式全局唯一ID

雪花算法

2.分片键选择

既要数据均匀分布 又要快速定位数据

3.数据扩容

扩容的时候可能停机

4.跨库join

同垂直拆分

5.跨库排序

order by create_tiem这种 需要每个分片将数据排序返回，将不同分片返回的结果进行汇总和再次排序，然后在返回给用户。

跨库函数处理

也是先分片处理再二次处理汇总。

ER分片

聚合根分片 比如订单和订单详情一起分片 就避免跨库join

非分片键过滤

痛苦 实时数据仓库。

avatar
文章
阅读
粉丝