死磕数据库系列(十二):MySQL 分库分表(何时分?怎么分?)

2023-03-16 00:00:00 数据 数据库 字段 业务 切分


刚开始我们只用单机数据库就够了,随后面对越来越多的请求,我们将数据库的写操作和读操作进行分离, 使用多个从库副本(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 表了。

数据量快速增长

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

安全性和可用性

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

分库分表方式方法

水平分库

  • 1、概念:以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中。
  • 2、结果:
    • 每个库的结构都一样
    • 每个库中的数据不一样,没有交集
    • 所有库的数据并集是全量数据
    • 3、场景:系统并发量上来了,分表难以根本上解决问题,并且还没有明显的业务归属来垂直分库的情况下。
  • 4、分析:库多了,io和cpu的压力自然可以成倍缓解

水平分表

  • 1、概念:以字段为依据,按照一定策略(hash、range等),讲一个表中的数据拆分到多个表中。
  • 2、结果:
    • 每个表的结构都一样
    • 每个表的数据不一样,没有交集,所有表的并集是全量数据。
  • 3、场景:系统并发量没有上来,只是单表的数据量太多,影响了SQL效率,加重了CPU负担,以至于成为瓶颈,可以考虑水平分表。
  • 4、分析:单表的数据量少了,单次执行SQL执行效率高了,自然减轻了CPU的负担。

垂直分库

  • 1、概念:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。
  • 2、结果:
    • 每个库的结构都不一样
    • 每个库的数据也不一样,没有交集
    • 所有库的并集是全量数据
  • 3、场景:系统并发量上来了,并且可以抽象出单独的业务模块的情况下。
  • 4、分析:到这一步,基本上就可以服务化了。例如:随着业务的发展,一些公用的配置表、字典表等越来越多,这时可以将这些表拆到单独的库中,甚至可以服务化。再者,随着业务的发展孵化出了一套业务模式,这时可以将相关的表拆到单独的库中,甚至可以服务化。

垂直分表

  • 1、概念:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表中(主表和扩展表)。
  • 2、结果:
    • 每个表的结构不一样。
    • 每个表的数据也不一样,一般来说,每个表的字段至少有一列交集,一般是主键,用于关联数据。
    • 所有表的并集是全量数据。
  • 3、场景:系统并发量并没有上来,表的记录并不多,但是字段多,并且热点数据和非热点数据在一起,单行数据所需的存储空间较大,以至于数据库缓存的数据行减少,查询时回去读磁盘数据产生大量随机读IO,产生IO瓶颈。
  • 4、分析:可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据(可能经常会查询的数据)放在一起作为主表,非热点数据放在一起作为扩展表,这样更多的热点数据就能被缓存下来,进而减少了随机读IO。拆了之后,要想获取全部数据就需要关联两个表来取数据。

但记住千万别用join,因为Join不仅会增加CPU负担并且会将两个表耦合在一起(必须在一个数据库实例上)。关联数据应该在service层进行,分别获取主表和扩展表的数据,然后用关联字段关联得到全部数据。

分库分表工具

  • sharding-jdbc(当当)
  • TSharding(蘑菇街)
  • Atlas(奇虎360)
  • Cobar(阿里巴巴)
  • MyCAT(基于Cobar)
  • Oceanus(58同城)
  • Vitess(谷歌) 各种工具的利弊自查

分库分表带来的问题

分库分表能有效缓解单机和单表带来的性能瓶颈和压力,突破网络IO、硬件资源、连接数的瓶颈,同时也带来一些问题,下面将描述这些问题和解决思路。

事务一致性问题

分布式事务

当更新内容同时存在于不同库找那个,不可避免会带来跨库事务问题。跨分片事务也是分布式事务,没有简单的方案,一般可使用“XA协议”和“两阶段提交”处理。

分布式事务能大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点,推后了提交事务的时间点,延长了事务的执行时间,导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多,这种趋势会越来越严重,从而成为系统在数据库层面上水平扩展的枷锁。

终一致性

对于那些性能要求很高,但对一致性要求不高的系统,往往不苛求系统的实时一致性,只要在允许的时间段内达到终一致性即可,可采用事务补偿的方式。与事务在执行中发生错误立刻回滚的方式不同,事务补偿是一种事后检查补救的措施,一些常见的实现方法有:对数据进行对账检查,基于日志进行对比,定期同标准数据来源进行同步等。

跨节点关联查询join问题

切分之前,系统中很多列表和详情表的数据可以通过join来完成,但是切分之后,数据可能分布在不同的节点上,此时join带来的问题就比较麻烦了,考虑到性能,尽量避免使用Join查询。解决的一些方法:

全局表

全局表,也可看做“数据字典表”,就是系统中所有模块都可能依赖的一些表,为了避免库join查询,可以将这类表在每个数据库中都保存一份。这些数据通常很少修改,所以不必担心一致性的问题。

字段冗余

一种典型的反范式设计,利用空间换时间,为了性能而避免join查询。例如,订单表在保存userId的时候,也将userName也冗余的保存一份,这样查询订单详情顺表就可以查到用户名userName,就不用查询买家user表了。但这种方法适用场景也有限,比较适用依赖字段比较少的情况,而冗余字段的一致性也较难保证。

数据组装

在系统service业务层面,分两次查询,次查询的结果集找出关联的数据id,然后根据id发起器二次请求得到关联数据,后将获得的结果进行字段组装。这是比较常用的方法。

ER分片

关系型数据库中,如果已经确定了表之间的关联关系(如订单表和订单详情表),并且将那些存在关联关系的表记录存放在同一个分片上,那么就能较好地避免跨分片join的问题,可以在一个分片内进行join。在1:1或1:n的情况下,通常按照主表的ID进行主键切分。这样一来,Data Node1上面的order订单表与orderdetail订单详情表就可以通过orderId进行局部的关联查询了,Data Node2上也一样。

跨节点分页、排序、函数问题

跨节点多库进行查询时,会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序,当排序字段就是分页字段时,通过分片规则就比较容易定位到指定的分片;当排序字段非分片字段时,就变得比较复杂.

需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序,终返回给用户 如下图:上图只是取页的数据,对性能影响还不是很大。但是如果取得页数很大,情况就变得复杂的多,因为各分片节点中的数据可能是随机的,为了排序的准确性,需要将所有节点的前N页数据都排序好做合并,后再进行整体排序,这样的操作很耗费CPU和内存资源,所以页数越大,系统性能就会越差。

在使用Max、Min、Sum、Count之类的函数进行计算的时候,也需要先在每个分片上执行相应的函数,然后将各个分片的结果集进行汇总再次计算。

全局主键避重问题

在分库分表环境中,由于表中数据同时存在不同数据库中,主键值平时使用的自增长将无用武之地,某个分区数据库自生成ID无法保证全局。因此需要单独设计全局主键,避免跨库主键重复问题。这里有一些策略:

UUID

UUID标准形式是32个16进制数字,分为5段,形式是8-4-4-4-12的36个字符。

UUID是简单的方案,本地生成,性能高,没有网络耗时,但是缺点明显,占用存储空间多,另外作为主键建立索引和基于索引进行查询都存在性能问题,尤其是InnoDb引擎下,UUID的无序性会导致索引位置频繁变动,导致分页。

结合数据库维护主键ID表

在数据库中建立sequence表:

CREATE TABLE `sequence` (  
      `id` bigint(20unsigned NOT NULL auto_increment,  
      `stub` char(1NOT NULL default '',  
      PRIMARY KEY  (`id`),  
      UNIQUE KEY `stub` (`stub`)  
    ) ENGINE=MyISAM;

stub字段设置为索引,同一stub值在sequence表中只有一条记录,可以同时为多张表生辰全局ID。sequence表的内容,如下所示:

+-------------------+------+ 
| id               | stub |  
+-------------------+------+ 
| 72157623227190423 |    a |  
+-------------------+------+

使用MyISAM引擎而不是InnoDb,已获得更高的性能。MyISAM使用的是表锁,对表的读写是串行的,所以不用担心并发时两次读取同一个ID。当需要全局的ID时,执行:

REPLACE INTO sequence (stub) VALUES ('a');  
    SELECT 1561439;  

此方案较为简单,但缺点较为明显:存在单点问题,强依赖DB,当DB异常时,整个系统不可用。配置主从可以增加可用性。另外性能瓶颈限制在单台Mysql的读写性能。

另有一种主键生成策略,类似sequence表方案,更好的解决了单点和性能瓶颈问题。这一方案的整体思想是:建立2个以上的全局ID生成的服务器,每个服务器上只部署一个数据库,每个库有一张sequence表用于记录当前全局ID。

表中增长的步长是库的数量,起始值依次错开,这样就能将ID的生成散列到各个数据库上!这种方案将生成ID的压力均匀分布在两台机器上,同时提供了系统容错,台出现了错误,可以自动切换到第二台获取ID。但有几个缺点:系统添加机器,水平扩展较复杂;每次获取ID都要读取一次DB,DB的压力还是很大,只能通过堆机器来提升性能。

Snowflake分布式自增ID算法

Twitter的snowfalke算法解决了分布式系统生成全局ID的需求,生成64位Long型数字,组成部分:

  • 位未使用
  • 接下来的41位是毫秒级时间,41位的长度可以表示69年的时间
  • 5位datacenterId,5位workerId。10位长度多支持部署1024个节点
  • 后12位是毫秒内计数,12位的计数顺序号支持每个节点每毫秒产生4096个ID序列。

数据迁移、扩容问题

当业务高速发展、面临性能和存储瓶颈时,才会考虑分片设计,此时就不可避免的需要考虑历史数据的迁移问题。一般做法是先读出历史数据,然后按照指定的分片规则再将数据写入到各分片节点中。此外还需要根据当前的数据量个QPS,以及业务发展速度,进行容量规划,推算出大概需要多少分片(一般建议单个分片的单表数据量不超过1000W)。

案例分析

用户中心业务场景

用户中心是一个非常常见的业务,主要提供用户注册、登录、查询/修改等功能,其核心表为:

User(uid, login_name, passwd, sex, age, nickname)
uid为用户ID,  主键
login_namepasswdsexagenickname,  用户属性

任何脱离业务的架构设计都是耍流氓,在进行分库分表前,需要对业务场景需求进行梳理:

  • 用户侧:前台访问,访问量较大,需要保证高可用和高一致性。主要有两类需求:
    • 用户登录:通过login_name/phone/email查询用户信息,1%请求属于这种类型
    • 用户信息查询:登录之后,通过uid来查询用户信息,99%请求属这种类型
  • 运营侧:后台访问,支持运营需求,按照年龄、性别、登陆时间、注册时间等进行分页的查询。是内部系统,访问量较低,对可用性、一致性的要求不高。

水平切分方法

当数据量越来越大时,需要对数据库进行水平切分,上文描述的切分方法有"根据数值范围"和"根据数值取模"。

"根据数值范围":以主键uid为划分依据,按uid的范围将数据水平切分到多个数据库上。例如:user-db1存储uid范围为0~1000w的数据,user-db2存储uid范围为1000w~2000wuid数据。

  • 优点是:扩容简单,如果容量不够,只要增加新db即可。
  • 不足是:请求量不均匀,一般新注册的用户活跃度会比较高,所以新的user-db2会比user-db1负载高,导致服务器利用率不平衡

"根据数值取模":也是以主键uid为划分依据,按uid取模的值将数据水平切分到多个数据库上。例如:user-db1存储uid取模得1的数据,user-db2存储uid取模得0的uid数据。

  • 优点是:数据量和请求量分布均均匀
  • 不足是:扩容麻烦,当容量不够时,新增加db,需要rehash。需要考虑对数据进行平滑的迁移。

非uid的查询方法

水平切分后,对于按uid查询的需求能很好的满足,可以直接路由到具体数据库。而按非uid的查询,例如login_name,就不知道具体该访问哪个库了,此时需要遍历所有库,性能会降低很多。

对于用户侧,可以采用"建立非uid属性到uid的映射关系"的方案;对于运营侧,可以采用"前台与后台分离"的方案。

建立非uid属性到uid的映射关系

1)映射关系

例如:login_name不能直接定位到数据库,可以建立login_name→uid的映射关系,用索引表或缓存来存储。当访问login_name时,先通过映射表查询出login_name对应的uid,再通过uid定位到具体的库。

映射表只有两列,可以承载很多数据,当数据量过大时,也可以对映射表再做水平切分。这类kv格式的索引结构,可以很好的使用cache来优化查询性能,而且映射关系不会频繁变更,缓存命中率会很高。

2)基因法

分库基因:假如通过uid分库,分为8个库,采用uid%8的方式进行路由,此时是由uid的后3bit来决定这行User数据具体落到哪个库上,那么这3bit可以看为分库基因。

上面的映射关系的方法需要额外存储映射表,按非uid字段查询时,还需要多一次数据库或cache的访问。如果想要消除多余的存储和查询,可以通过f函数取login_name的基因作为uid的分库基因。生成uid时,参考上文所述的分布式ID生成方案,再加上后3位bit值=f(login_name)。当查询login_name时,只需计算f(login_name)%8的值,就可以定位到具体的库。不过这样需要提前做好容量规划,预估未来几年的数据量需要分多少库,要预留一定bit的分库基因。

前台与后台分离

对于用户侧,主要需求是以单行查询为主,需要建立login_name/phone/email到uid的映射关系,可以解决这些字段的查询问题。

而对于运营侧,很多批量分页且条件多样的查询,这类查询计算量大,返回数据量大,对数据库的性能消耗较高。此时,如果和用户侧公用同一批服务或数据库,可能因为后台的少量请求,占用大量数据库资源,而导致用户侧访问性能降低或超时。

这类业务好采用"前台与后台分离"的方案,运营侧后台业务抽取独立的service和db,解决和前台业务系统的耦合。由于运营侧对可用性、一致性的要求不高,可以不访问实时库,而是通过binlog异步同步数据到运营库进行访问。在数据量很大的情况下,还可以使用ES搜索引擎或Hive来满足后台复杂的查询方式。

来源:cnblogs.com/butterfly100/p/9034281.html



相关文章