一、什么是分库分表
从字面上简单理解,就是**把原本存储于一个库的数据分块存储到多个库上,把原本存储于一个表的数据分块存储到多个表上。**
二、为什么要分库分表
**数据库中的数据量不一定是可控的,在未进行分库分表的情况下,随着时间和业务的发展,库中的表会越来越多,表中的数据量也会越来越大,相应地,数据操作,增删改查的开销也会越来越大。**另外,由于无法进行分布式式部署,而一台服务器的资源(CPU、磁盘、内存、IO等)是有限的,最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。
2.1数据库瓶颈
不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值。
2.1.1.IO瓶颈
- 第一种:磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的IO,降低查询速度 --> 分库和垂直分表。
- 第二种:网络IO瓶颈,请求的数据太多,网络带宽不够 --> 分库。
2.1.2.CPU瓶颈
- 第一种:SQL问题,如SQL中包含
join
,group by
,order by
,非索引字段条件查询等,增加CPU运算的操作 --> SQL优化,建立合适的索引,在业务Service层进行业务计算。 - 第二种:单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈 -->水平分表。
2.2.节约公司成本
主流数据库性能已经很优,采用升级服务器(小型机)也能解决性能和存储问题
,但是综合考虑成本的原因,小型机动辄几百万,大型机甚至上千万,分库分表可以解决的问题,对大部分公司来说都是成本极低的,特殊是对于一些初创小公司。
2.3.数据库Replication主从同步存在延时缺陷
对于热点数据,采用Replication机制的确可以解决很多问题,但是它也存在自身的缺陷,首先它依赖于读操作的比例(读操作越高性能越好),Master往往成为瓶颈所在,因为写操作需要顺序排队来执行,过载的话Master首先抗不住,其次Slaves的数据同步的延迟也可能比较大,write操作在Master上执行以后还需要在每台slave机器上跑一次。
三、分库分表常见方案
分库分表有垂直切分和水平切分两种方式,包括水平分库、垂直分库、水平分表、垂直分表四种解决方案,也可根据实际需求进行方案混合优化。
3.1.水平分库
- 概念:以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中。
- 结果:
- 每个库的结构都一样;
- 每个库的数据都不一样,没有交集;
- 所有库的并集是全量数据;
- 场景:系统绝对并发量上来了,分表难以根本上解决问题,并且还没有明显的业务归属来垂直分库。
- 分析:库多了,io和cpu的压力自然可以成倍缓解。
3.2.垂直分库
- 概念:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。
- 结果:
- 每个库的结构都不一样;
- 每个库的数据也不一样,没有交集;
- 所有库的并集是全量数据;
- 场景:系统绝对并发量上来了,并且可以抽象出单独的业务模块。
- 分析:到这一步,基本上就可以服务化了。例如,随着业务的发展一些公用的配置表、字典表等越来越多,这时可以将这些表拆到单独的库中,甚至可以服务化。再有,随着业务的发展孵化出了一套业务模式,这时可以将相关的表拆到单独的库中,甚至可以服务化。
3.3.水平分表
- 概念:以字段为依据,按照一定策略(hash、range等),将一个表中的数据拆分到多个表中。
- 结果:
- 每个表的结构都一样;
- 每个表的数据都不一样,没有交集;
- 所有表的并集是全量数据;
- 场景:系统绝对并发量并没有上来,只是单表的数据量太多,影响了SQL效率,加重了CPU负担,以至于成为瓶颈。
- 分析:表的数据量少了,单次SQL执行效率高,自然减轻了CPU的负担。
3.4.垂直分表
- 概念:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。
- 结果:
- 每个表的结构都不一样;
- 每个表的数据也不一样,一般来说,每个表的字段至少有一列交集,一般是主键,用于关联数据;
- 所有表的并集是全量数据;
- 场景:系统绝对并发量并没有上来,表的记录并不多,但是字段多,并且热点数据和非热点数据在一起,单行数据所需的存储空间较大。以至于数据库缓存的数据行减少,查询时会去读磁盘数据产生大量的随机读IO,产生IO瓶颈。
- 分析:可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据(可能会冗余经常一起查询的数据)放在一起作为主表,非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来,进而减少了随机读IO。拆了之后,要想获得全部数据就需要关联两个表来取数据。但记住,千万别用join,因为join不仅会增加CPU负担并且会讲两个表耦合在一起(必须在一个数据库实例上)。关联数据,应该在业务Service层做文章,分别获取主表和扩展表数据然后用关联字段关联得到全部数据。
3.5.分库分表方案选择
应该使用哪一种方式来实施数据库分库分表,这要看数据库中数据量的瓶颈所在,并综合项目的业务类型进行考虑。
3.5.1.垂直切分方案适用场景
数据库是因为表太多而造成海量数据,并且项目的各项业务逻辑划分清晰、低耦合,那么规则简单明了、容易实施的垂直切分必是首选。
![img](https://i-blog.csdnimg.cn/blog_migrate/1bd5a4374f6b84ad29776e7f2972f308.png)
3.5.2.水平切分方案适用场景
数据库中的表并不多,但单表的数据量很大、或数据热度很高,这种情况之下就应该选择水平切分,水平切分比垂直切分要复杂一些,它将原本逻辑上属于一体的数据进行了物理分割,除了在分割时要对分割的粒度做好评估,考虑数据平均和负载平均,后期也将对项目人员及应用程序产生额外的数据管理负担。
![img](https://i-blog.csdnimg.cn/blog_migrate/074f7a459165f054ae1dda2cbad4ae0a.png)
3.5.2.组合应用场景
在现实项目中,往往是这两种情况兼而有之,这就需要做出权衡,甚至既需要垂直切分,又需要水平切分。我们的游戏项目便综合使用了垂直与水平切分,我们首先对数据库进行垂直切分,然后,再针对一部分表,通常是用户数据表,进行水平切分。
四、分库分表的常见中间件
根据实际需求选择对应中间件需求,工具的利弊,请自行调研,官网和社区优先。
4.1.简单易用
4.2.强悍重量级
- sharding
- TDDL Smart Client的方式(淘宝)
- Atlas(Qihoo 360)
- alibaba.cobar(是阿里巴巴(B2B)部门开发)
- MyCAT(基于阿里开源的Cobar产品而研发)
- Oceanus(58同城数据库中间件)
- OneProxy(支付宝首席架构师楼方鑫开发)
- vitess(谷歌开发的数据库中间件)
目前使用过Mycat,目前稳定版本为1.6.7.4。Mycat2是Mycat社区开发的一款分布式关系型数据库(中间件)。它支持分布式SQL查询,兼容MySQL通信协议,以Java生态支持多种后端数据库,通过数据分片提高数据查询处理能力。
五、分库分表的优缺点
- 优点:提高应用的性能,缓解同一个库和同一张表的压力,减轻单台主机的负载压力 。实现业务的线性可控,通过横向扩展增加数据库的方式,实现业务的线性增长。
- 缺点: 需要投入人力、物力进行开发与维护。
六、分库分表带来的问题
6.1.非partition key的查询问题
基于水平分库分表,拆分策略为常用的hash法。
6.1.1.端上除了partition key只有一个非partition key作为条件查询
-
映射法
-
基因法
注:写入时,基因法生成user_id,如图。关于xbit基因,例如要分8张表,23=8,故x取3,即3bit基因。根据user_id查询时可直接取模路由到对应的分库或分表。根据user_name查询时,先通过user_name_code生成函数生成user_name_code再对其取模路由到对应的分库或分表。id生成常用snowflake算法。
6.1.2.端上除了partition key不止一个非partition key作为条件查询
-
映射法
-
冗余法
注:按照order_id或buyer_id查询时路由到db_o_buyer库中,按照seller_id查询时路由到db_o_seller库中。感觉有点本末倒置!有其他好的办法吗?改变技术栈呢?
6.1.3.后台除了partition key还有各种非partition key组合条件查询
-
-
冗余法
6.2.partition key跨库跨表分页查询问题
基于水平分库分表,拆分策略为常用的hash法。
注:用**NoSQL法**解决(ES等)
6.3.扩容问题
基于水平分库分表,拆分策略为常用的hash法。
6.3.1.水平扩容库(升级从库法)
注:扩容是成倍的。
6.3.2.水平扩容表(双写迁移法)
第一步:(同步双写)修改应用配置和代码,加上双写,部署;
第二步:(同步双写)将老库中的老数据复制到新库中;
第三步:(同步双写)以老库为准校对新库中的老数据;
第四步:(同步双写)修改应用配置和代码,去掉双写,部署;
注:双写是通用方案。
6.4.分库分表的一些其它问题
6.4.1.事务一致性问题
6.4.1.1分布式事务
- 当更新内容同时分布在不同库中,不可避免会带来跨库事务问题。跨分片事务也是分布式事务,没有简单的方案,一般可使用"XA协议"和"两阶段提交"处理。
- 分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点,推后了提交事务的时间点,延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多,这种趋势会越来越严重,从而成为系统在数据库层面上水平扩展的枷锁。
6.4.1.2.最终一致性
对于那些性能要求很高,但对一致性要求不高的系统,往往不苛求系统的实时一致性,只要在允许的时间段内达到最终一致性即可,可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同,事务补偿是一种事后检查补救的措施,一些常见的实现方法有:对数据进行对账检查,基于日志进行对比,定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。
6.4.2.跨节点关联查询 join 问题
切分之前,系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后,数据可能分布在不同的节点上,此时join带来的问题就比较麻烦了,考虑到性能,尽量避免使用join查询。
解决这个问题的一些方法:
6.4.2.1.全局表
全局表,也可看做是"数据字典表",就是系统中所有模块都可能依赖的一些表,为了避免跨库join查询,可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改,所以也不担心一致性的问题。
6.4.2.2字段冗余
一种典型的反范式设计,利用空间换时间,为了性能而避免join查询。例如:订单表保存userId时候,也将userName冗余保存一份,这样查询订单详情时就不需要再去查询"买家user表"了。
但这种方法适用场景也有限,比较适用于依赖字段比较少的情况。而冗余字段的数据一致性也较难保证,就像上面订单表的例子,买家修改了userName后,是否需要在历史订单中同步更新呢?这也要结合实际业务场景进行考虑。
6.4.2.3.数据组装
在系统层面,分两次查询,第一次查询的结果集中找出关联数据id,然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。
6.4.2.4.ER分片
关系型数据库中,如果可以先确定表之间的关联关系,并将那些存在关联关系的表记录存放在同一个分片上,那么就能较好的避免跨分片join问题。在1:1或1:n的情况下,通常按照主表的ID主键切分。如下图所示:
![img](https://i-blog.csdnimg.cn/blog_migrate/a0f9233f152d4a8c6ab64f3f60e42797.png)
这样一来,Data Node1上面的order订单表与orderdetail订单详情表就可以通过orderId进行局部的关联查询了,Data Node2上也一样。
6.4.3.跨节点分页、排序、函数问题
跨节点多库进行查询时,会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序,当排序字段就是分片字段时,通过分片规则就比较容易定位到指定的分片;当排序字段非分片字段时,就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序,最终返回给用户。如图所示:
![img](https://i-blog.csdnimg.cn/blog_migrate/e05688f9c06a55e3c76b5d89b153f31e.png)
上图中只是取第一页的数据,对性能影响还不是很大。但是如果取得页数很大,情况则变得复杂很多,因为各分片节点中的数据可能是随机的,为了排序的准确性,需要将所有节点的前N页数据都排序好做合并,最后再进行整体的排序,这样的操作时很耗费CPU和内存资源的,所以页数越大,系统的性能也会越差。
在使用Max、Min、Sum、Count之类的函数进行计算的时候,也需要先在每个分片上执行相应的函数,然后将各个分片的结果集进行汇总和再次计算,最终将结果返回。如图所示:
![img](https://i-blog.csdnimg.cn/blog_migrate/37baeedb630c4e06f0b9dd155fc28ab4.png)
6.4.4.全局主键避重问题
在分库分表环境中,由于表中数据同时存在不同数据库中,主键值平时使用的自增长将无用武之地,某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键,以避免跨库主键重复问题。有一些常见的主键生成策略:
6.4.4.1.UUID
UUID标准形式包含32个16进制数字,分为5段,形式为8-4-4-4-12的36个字符,例如:550e8400-e29b-41d4-a716-446655440000
UUID是主键是最简单的方案,本地生成,性能高,没有网络耗时。但缺点也很明显,由于UUID非常长,会占用大量的存储空间;另外,作为主键建立索引和基于索引进行查询时都会存在性能问题,在InnoDB下,UUID的无序性会引起数据位置频繁变动,导致分页。
6.4.4.2.结合数据库维护主键ID表
在数据库中建立 sequence 表:
CREATE TABLE `sequence` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`stub` char(1) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM;
stub字段设置为唯一索引,同一stub值在sequence表中只有一条记录,可以同时为多张表生成全局ID。sequence表的内容,如下所示:
+-------------------+------+
| id | stub |
+-------------------+------+
| 72157623227190423 | a |
+-------------------+------+
使用 MyISAM 存储引擎而不是 InnoDB,以获取更高的性能。MyISAM使用的是表级别的锁,对表的读写是串行的,所以不用担心在并发时两次读取同一个ID值。
当需要全局唯一的64位ID时,执行:
REPLACE INTO sequence (stub) VALUES ('a');
SELECT LAST_INSERT_ID();
这两条语句是Connection级别的,select last_insert_id() 必须与 replace into 在同一数据库连接下才能得到刚刚插入的新ID。
使用replace into代替insert into好处是避免了表行数过大,不需要另外定期清理。
此方案较为简单,但缺点也明显:存在单点问题,强依赖DB,当DB异常时,整个系统都不可用。配置主从可以增加可用性,但当主库挂了,主从切换时,数据一致性在特殊情况下难以保证。另外性能瓶颈限制在单台MySQL的读写性能。
flickr团队使用的一种主键生成策略,与上面的sequence表方案类似,但更好的解决了单点和性能瓶颈的问题。
这一方案的整体思想是:建立2个以上的全局ID生成的服务器,每个服务器上只部署一个数据库,每个库有一张sequence表用于记录当前全局ID。表中ID增长的步长是库的数量,起始值依次错开,这样能将ID的生成散列到各个数据库上。如下图所示:
![img](https://i-blog.csdnimg.cn/blog_migrate/cc5416fa328504e89ca725cf11cf00ea.png)
由两个数据库服务器生成ID,设置不同的auto_increment值。第一台sequence的起始值为1,每次步长增长2,另一台的sequence起始值为2,每次步长增长也是2。结果第一台生成的ID都是奇数(1, 3, 5, 7 …),第二台生成的ID都是偶数(2, 4, 6, 8 …)。
这种方案将生成ID的压力均匀分布在两台机器上。同时提供了系统容错,第一台出现了错误,可以自动切换到第二台机器上获取ID。但有以下几个缺点:系统添加机器,水平扩展时较复杂;每次获取ID都要读写一次DB,DB的压力还是很大,只能靠堆机器来提升性能。
可以基于flickr的方案继续优化,使用批量的方式降低数据库的写压力,每次获取一段区间的ID号段,用完之后再去数据库获取,可以大大减轻数据库的压力。如下图所示:
![img](https://i-blog.csdnimg.cn/blog_migrate/de5acb3ac1c80bd6fc00b11803ec9db3.png)
还是使用两台DB保证可用性,数据库中只存储当前的最大ID。ID生成服务每次批量拉取6个ID,先将max_id修改为5,当应用访问ID生成服务时,就不需要访问数据库,从号段缓存中依次派发05的ID。当这些ID发完后,再将max_id修改为11,下次就能派发611的ID。于是,数据库的压力降低为原来的1/6。
6.4.4.3.Snowflake分布式自增ID算法
Twitter的snowflake算法解决了分布式系统生成全局ID的需求,生成64位的Long型数字,组成部分:
- 第一位未使用
- 接下来41位是毫秒级时间,41位的长度可以表示69年的时间
- 5位datacenterId,5位workerId。10位的长度最多支持部署1024个节点
- 最后12位是毫秒内的计数,12位的计数顺序号支持每个节点每毫秒产生4096个ID序列
![img](https://i-blog.csdnimg.cn/blog_migrate/2032ed4acf296ded1debe5ddf6ac8dbc.png)
这样的好处是:毫秒数在高位,生成的ID整体上按时间趋势递增;不依赖第三方系统,稳定性和效率较高,理论上QPS约为409.6w/s(1000*2^12),并且整个分布式系统内不会产生ID碰撞;可根据自身业务灵活分配bit位。
不足就在于:强依赖机器时钟,如果时钟回拨,则可能导致生成ID重复。
6.4.4.4.综上
结合数据库和snowflake的唯一ID方案,可以参考业界较为成熟的解法:Leaf——美团点评分布式ID生成系统,并考虑到了高可用、容灾、分布式下时钟等问题。
6.4.5.数据迁移、扩容问题
当业务高速发展,面临性能和存储的瓶颈时,才会考虑分片设计,此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据,然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和QPS,以及业务发展的速度,进行容量规划,推算出大概需要多少分片(一般建议单个分片上的单表数据量不超过1000W)
如果采用数值范围分片,只需要添加节点就可以进行扩容了,不需要对分片数据迁移。如果采用的是数值取模分片,则考虑后期的扩容问题就相对比较麻烦。
七、分库分表总结
- 分库分表的实际使用方案,根据项目实际情况去进行适配组合,才能得到最适合的方案;
- 方案的确定,要实际了解项目的实际需求以及现有数据库的瓶颈,不仅仅是为了分库而分库,不要过度使用与项目需求不符合的技术;
- 使用合适分库分表的中间件,选择合适分库分表的分区字段key很重要,既要考虑到拆分均匀,也要考虑到非partition key的查询。
- 只要能满足需求,拆分规则越简单越好。
- 项目需求简单,单库优化可满足需求,能不分库就不分库,减少很多不必要的问题;