MySQL - 分库分表

1. 数据库瓶颈

不管是 IO 瓶颈，还是 CPU 瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载的活跃连接数阈值，可能就会导致整个程序崩溃；

• 1.1 IO（输入input/输出output）瓶颈

  • 磁盘 IO 瓶颈：热点数据太多，数据库缓存放不下，查询数据产生大量的 IO，影响查询速度 --> 分库和垂直分表；

  • 网络 IO 瓶颈：请求的数据太多，网络带宽不够 --> 分库；

• 1.2 CPU 瓶颈

  • SQL问题：SQL中包含 join、group by、order by、非索引字段条件查询等和增加 CPU 运算的操作 --> 优化SQL语句，建立合适的索引，业务逻辑计算等在 Service 层进行；

  • 单表数据量太大：查询时扫描的行太多，SQL 效率低，增加 CPU 运算的操作 --> 水平分表；

2. 分库分表

• 2.1 水平分库

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

    • 每个库的结构都一样；

    • 每个库的数据都不一样，没有交集；

    • 所有库的并集是全量数据；

  

• 场景：

• 系统绝对并发量上来了，分表难以根本上解决问题，并且也没有明显的业务归属来垂直分库；

  分析：

  库多了，io 和 cpu 的压力自然可以成倍缓解；

• 2.2 水平分表

  • 以字段为依据，按照一定策略（hash：哈希、range：范围 等），将一个表中的数据拆分到多个表中；

    • 每个表的结构都一样；

    • 每个表的数据都不一样，没有交集；

    • 所有表的并集是全量数据；

    

  场景：

  系统绝对并发量并没有上来，只是单表的数据量太多，影响了 SQL 效率，加重了 CPU 负担，以至于成为瓶颈；

  分析：

  表的数据量少了，单次 SQL 执行效率高，自然减轻了经 CPU 的负担；

• 2.3 垂直分库

  • 以表为依据，按照业务归属不同，将不同的表拆分到不同的库中；

    • 每个库的结构都不一样；

    • 每个库的数据也不一样，没有交集；

    • 所有库的并集是全量数据；

  

• 场景：

        系统绝对并发量上来了，并且可以抽取出单独的业务模板；

• 分析：

  到这一步，基本上就可以服务化（微服务）了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中。

• 2.4 垂直分表

  • 以字段为依据，按照字段的活跃性，将表中字段拆解到不同得到表中（主表和扩展表）；

    • 每个表的结构都不一样；

    • 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联这些表之间的数据；

    • 所有表的并集是全量数据；

  场景：

  系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需要的存储空间较大，以至于数据库缓存的数据行减少，查询时会去读取磁盘数据产生大量的随机读 io，产品 io 瓶颈；

  分析：

  垂直分表的拆分原则是将热点数据（可能会冗余一些常查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能会缓存下来，进而减少了随机读 io。

  拆分之后，要想获取全部数据就需要关联两张表来获取。关联数据应尽量少用 join，join 不仅会增加 CPU 负担并且会将两张表耦合在一起，我们可以在业务 Service 层上来实现数据关联，分别获取主表和扩展表数据然后用关联字段关联起来获取全部数据；

• 2.5 分库分表工具

  • Sharding-jdbc：Sharding-jdbc 是当当网开源的适用于微服务的分布式数据访问基础类库，完整的实现了分库分表，读写分离和分布式主键功能，并初步实现了柔性事务。这种 client 层方法的优点在于不用部署，运维成本低，性能高，但是各个系统都需要耦合 Sharding-jdbc 的依赖，升级比较麻烦；

  • Mycat：Mycat 是用 Java 语言开发的目前比较主流的数据库中间件之一，前端可以把 Mycat 看作是一个数据库代理。这种代理层方案的缺点在于需要部署，自己运维一套中间件，运维成本高。优点就是对于各个项目是透明的，如果遇到升级等只需要处理中间件就行；