[TOC]

MYSQL分库分表的理解

1. 概述

1.1 为什么要分库分表?

生产上解决数据量过大对关系型数据库性能造成影响.

1.2分库分表是什么?

方案1:提升服务器的硬件能力,但是成本太高,如果到达瓶颈硬件提升也是有限的.(不提倡)

方案2:把数据分散在不同的数据库中,使得单一库的数据量减少,从而达到性能提升的目的.同时大表也可以拆分成小的表.

• 分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题,将原来独立的数据库拆分成若干数据库组成,将数据大表拆分成若干数据表组成,使得单一数据库,单一数据表的数据量变小,从而达到性能提升数据库性能的目的.

1.3分库分表的方式

分库分表包括分库和分表两个部分,在生产中通常包括:垂直分库,水平分库,垂直分表,水平分表四种方式.

1.垂直分表(商品信息)

• 垂直分表就是把一部分字段归为一个表,另一部分归为另一个. 还可以多张分表
• 1.为了避免IO争抢并减少锁表的几率,查看详情的用户和商品信息浏览互不影响
• 可以根据热门字段和冷门字段分
• 垂直拆分的原则:
  • 1.把不常用的字段单独放在一张表;
  • 2.吧text,blob等大的字段拆分出来放在附表中;
  • 3.经常组合查询的列放在一张表中.

2.垂直分库

库内垂直分表只解决子单一表数据量过大的问题,但没有将表分布到不同的服务器上,因此每个表还是竞争同一个物理机的CPU,内存,网络IO,磁盘.

• 提升:
  • 1.解决业务层面的耦合,业务清晰.
  • 2.能对不同业务的数据进行分级管理,维护,监护,扩展
  • 3.高并发场景下,垂直分库一定程度的提升IO,数据库连接数,降低单机硬件资源的瓶颈.

3.水平分库

在垂直分完后数据量又上去了,所以我们要考虑水平.

水平分库是把同一个表的数据按规则拆分到不同的数据库中,每个库可以放到在不同的服务器上.(比如按照id的基数偶数划分)

• 提升量:
  • 解决单库大数据,并发的性能瓶颈.
  • 提高了系统的稳定性及可用性.
• 缺点:
  • 每次数据量大都可以,但是运维的成本就有些高.

4.水平分表

不从数据库方面考虑,去考虑表,面临一系列问题:切分策略,库节点路由,表路由,全局主键生成,跨节点排序/分组/分页/表关联等操作,多数据源事务处理,数据库扩容等.

水平分表是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中.从而使得单表的数据量变小,提高检索性能.

• 提升:
  • 单一优化数据量过大产生的性能问题
  • 避免IO争抢并减少锁表的几率

1.4小结

• 垂直分表:可以把一个宽的字段按访问频次,是否是大字段的原因拆分为多个表,这样既能使业务清晰,还能提升部分性能.拆分后,尽量从业务角度避免联查,否则性能方面将得不偿失.
• 垂直分库:可以把多个表按业务耦合松紧归类,分别存放在不同的库,这些库可以分配到不同的服务器,从而使访问压力被多服务器负载,大大提升性能,同时提高整体架构的业务清晰度,不同的业务可根据自身情况定制优化方案.但是它需要解决跨库带来的所有复杂问题.
• 水平分库:可以把一个表的数据(按数据行)分到多个不同的库,每个库只有这个表的部分数据,这些库可以分布在不同服务器,从而使访问压力被多服务器负载,大大提升性能,它不仅需要解决跨库带来的所有负载问题,还要解决数据路由的问题.
• 水平分表:可以把一个表的数据(按数据行)分到多个通过一个数据库的多张表中,每个表只有这个表的部分数据,这杨做就能小幅度提升性能,它仅仅作为水平分库的一个补充优化.

一般来说,在系统设计阶段就应该根据业务的耦合松紧来确定垂直分库,垂直分表方案,在数据量及访问压力不是特别大的情况下,首先考虑缓存,读写分离,索引技术等方案,若数据量极大,且持续增长,再考虑水平分库水平分表方案.

1.5分库分表带来的问题

1.事务一致性问题

​ 由于分库分表把数据分布在不同库甚至不同服务器,不可避免会带来分布式事务问题.

2.跨节点关联查询

​ 在没有分库前,我们检索商品时可以通过以下SQL对店铺信息进行关联查询:

SELECT p.*,r.[地理区域名称],s.[店铺名称],s.[信誉]
FROM [商品信息] p
LEFT JOIN [地理区域] r ON p.[产地] = r.[地理区域编码]
LEFT JOIN [店铺信息] s ON p.id = s.[所属店铺]
WHERE ... ORDER BY...LIMIT...

​ 但垂直分库后**[商品信息]**和**[店铺信息]**不在一个数据库,甚至不在一台服务器,无法进行关联查询.

​ 可将原关联查询分为两次查询,第一次查询的结果集中找出关联数据id,然后根据id发起第二次请求得到关联数据, 最后将获得的数据进行拼装.

3.跨节点分页,排序函数

​ 跨节点多库进行查询时,limit分页,order by 排序等问题,就比较复杂,需要先在不同的分片节点中将数据进行排序 并返回,然后将不同分片返回的结果集进行汇总和再次排序.

​ 以上流程是取第一页的数据,性能影响不大,但是由于商品信息的分布在各数据库的数据可能是随机的,如果是取第 N页,需要将所有节点前N页数据都取出来合并,在进行整体的排序,操作效率可想而知.所以请求页数越大,系统的性 能越大.

5.主键避重

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

6.公共表

​ 可以将这类表在每个数据库都保存一份,所有对公共表的更新操作都同时发送到所有分库执行.

​