MySQL 分库分表（何时分？怎么分？）

数据运维 2023-07-10 剑圣无痕 手机阅读

开始我们只用单机数据库就够了，随后面对越来越多的请求，我们将数据库的写操作和读操作进行分离， 使用多个从库副本（Slaver Replication）负责读，使用主库（Master）负责写， 从库从主库同步更新数据，保持数据一致。架构上就是数据库主从同步。 从库可以水平扩展，所以更多的读请求不成问题。

但是当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的，因为数据要保存一致性，写操作需要 2 个 master 之间同步，相当于是重复了，而且更加复杂。

上一期我们学习了：，在正式介绍分库分表之前，我们需要了解一下数据库的性能瓶颈在哪？

数据库瓶颈

不管是 IO 瓶颈还是 CPU 瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载的活跃连接数的阈值。在业务 service 来看，

就是可用数据库连接少甚至无连接可用，接下来就可以想象了（并发量、吞吐量、崩溃）。

IO 瓶颈

  • 第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询会产生大量的IO，降低查询速度->分库和垂直分表
  • 第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 ->分库

CPU 瓶颈

  • 第一种：SQl问题：如SQL中包含join,group by, order by，非索引字段条件查询等，增加CPU运算的操作->SQL优化，建立合适的索引，在业务Service层进行业务计算。
  • 第二种：单表数据量太大，查询时扫描的行太多，SQl效率低，增加CPU运算的操作。->水平分表。

什么时候考虑分库分表

能不分就不分

并不是所有表都需要切分，主要还是看数据的增长速度。切分后在某种程度上提升了业务的复杂程度。不到万不得已不要轻易使用分库分表这个“大招”，避免“过度设计”和“过早优化”。分库分表之前，先尽力做力所能及的优化：升级硬件、升级网络、读写分离、索引优化等。当数据量达到单表瓶颈后，在考虑分库分表。

数据量过大，正常运维影响业务访问

这里说的运维，指：

1）对数据库备份，如果单表太大，备份时需要大量的磁盘IO和网络IO。例如1T的数据，网络传输占50MB时候，需要20000秒才能传输完毕，整个过程的风险都是比较高的

2）对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用pt-online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险。

3）大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力

随着业务发展，需要对某些字段垂直拆分

举个例子，假如项目一开始设计的用户表如下：

id                  bigint     #用户的ID
name                varchar    #用户的名字
last_login_time     datetime   #最近登录时间
personal_info       text       #私人信息
.....                          #其他信息字段

在项目初始阶段，这种设计是满足简单的业务需求的，也方便快速迭代开发。而当业务快速发展时，用户量从10w激增到10亿，用户非常的活跃，每次登录会更新 last_login_name 字段，使得 user 表被不断update，压力很大。而其他字段：id, name, personal_info 是不变的或很少更新的，此时在业务角度，就要将 last_login_time 拆分出去，新建一个 user_time 表。

personal_info 属性是更新和查询频率较低的，并且text字段占据了太多的空间。这时候，就要对此垂直拆分出 user_ext 表了。

数据量快速增长

随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量

安全性和可用性

鸡蛋不要放在一个篮子里。在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到100%的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高。

分库分表方式方法

水平分库

MySQL 分库分表（何时分？怎么分？）

  • 1、概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

  • 2、结果：
    • 每个库的结构都一样
  • 每个库中的数据不一样，没有交集
  • 所有库的数据并集是全量数据
  • 3、场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库的情况下。
  • 4、分析：库多了，io和cpu的压力自然可以成倍缓解
  • 2、结果：
    • 每个表的结构都一样
  • 每个表的数据不一样，没有交集，所有表的并集是全量数据。
  • 3、场景：系统绝对并发量没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈，可以考虑水平分表。
  • 4、分析：单表的数据量少了，单次执行SQL执行效率高了，自然减轻了CPU的负担。
  • 2、结果：
    • 每个库的结构都不一样
  • 每个库的数据也不一样，没有交集
  • 所有库的并集是全量数据
  • 3、场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块的情况下。
  • 4、分析：到这一步，基本上就可以服务化了。例如：随着业务的发展，一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再者，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。
  • 2、结果：
    • 每个表的结构不一样。
  • 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据。
  • 所有表的并集是全量数据。
  • 3、场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大，以至于数据库缓存的数据行减少，查询时回去读磁盘数据产生大量随机读IO，产生IO瓶颈。
  • 4、分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能经常会查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表，这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获取全部数据就需要关联两个表来取数据。
  • TSharding（蘑菇街）
  • Atlas（奇虎360）
  • Cobar（阿里巴巴）
  • MyCAT（基于Cobar）
  • Oceanus（58同城）
  • Vitess（谷歌） 各种工具的利弊自查
  • 接下来的41位是毫秒级时间，41位的长度可以表示69年的时间
  • 5位datacenterId,5位workerId。10位长度最多支持部署1024个节点
  • 最后12位是毫秒内计数，12位的计数顺序号支持每个节点每毫秒产生4096个ID序列。
  • 用户信息查询：登录之后，通过uid来查询用户信息，99%请求属这种类型
  • 运营侧：后台访问，支持运营需求，按照年龄、性别、登陆时间、注册时间等进行分页的查询。是内部系统，访问量较低，对可用性、一致性的要求不高。
  • 不足是：请求量不均匀，一般新注册的用户活跃度会比较高，所以新的user-db2会比user-db1负载高，导致服务器利用率不平衡
  • 不足是：扩容麻烦，当容量不够时，新增加db，需要rehash。需要考虑对数据进行平滑的迁移。