MySQL分库分表，何时分？怎么分？

👨‍🎓 博主介绍：
IT邦德，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证

擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，
安装迁移，性能优化、故障应急处理等。

前言

🍁 一、 数据库中间件

1.Cobar 属于阿里 B2B 事业群，始于 2008 年，在阿里服役 3 年多，
接管 3000+个 MySQL 数据库的 schema,集群日处理在
线 SQL请求 50 亿次以上。由于 Cobar 发起人的离职，Cobar停止维护。
2.Mycat 是开源社区在阿里 Cobar 基础上进行二次开发，
解决了 cobar 存在的问题，并且加入了许多新的功能在其中。青出于蓝而胜于蓝。
3.OneProxy基于 MySQL官方的 proxy思想利用 c进行开发的，
OneProxy是一款商业收费的中间件。舍弃了一些功能，专注在性能和稳定性上。
4.kingshard 由小团队用 go 语言开发，还需要发展，需要不断完善。
5.Vitess 是 Youtube生产在使用，架构很复杂。不支持 MySQL原生协议，使用需要大量改造成本。
6.Atlas 是 360 团队基于 MySQL proxy改写，功能还需完善，高并发下不稳定。
7. MaxScale是 mariadb（MySQL原作者维护的一个版本）研发的中间件。
8. MySQL Route是 MySQL官方 Oracle公司发布的中间件。

🍁 二、 分库分表简介

    MySQL 作为互联网公司都会用到的数据库，如果在使用过程中出现性能问题，
    会采用 mysql 的横向扩展，使用主从复制来提高读性能，要是解决写入问题，需要进行分库分表。
    分库分表是业务发展到一定阶段，数据积累到一定量级而衍生出来的解决方案。
    当 DB 的数据量级到达一个阶段， 写入和读取的速度会出现瓶颈，即使是有索引，索引也会变的很大，
    而且数据库的物理文件大的会使备份和恢复等操作变的很困难。
    这个时候由于 DB 的瓶颈已经严重危害到了业务，最有效的解决方案莫过于DB的分库分表了。
    数据库表的拆分解决的问题主要是存储和性能问题，mysql 在单表数据量达到一定量级后，
    性能会急剧下降，相比较于sqlserver 和 Oracle 这些收费 DB 来说，
    mysql 在某些方面还是处于弱势，但是表的拆分这个策略却适用于几乎所有的关系型数据库。

🍃 2.1 、分库分表的目的

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

🍃 2 2 、分库分表标准

存储占用 100G+
数据增量每天 200w+
单表条数 1 亿条+

🍃 2.3、类型

① 分库：垂直分库、水平分库
② 分表：垂直分表、水平分表
分库是指把一个数据库拆分为多个数据库，一般分为垂直分库和水平分库。
分表指的是通过一定规则，将一张表分解成多张不同的表，一般分为垂直分表和水平分表。

a、垂直分库

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

b 、水平分库

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

c、垂直分表

1 、概念：垂直分表即“宽表拆窄表”，以 字段为依据，
按照 字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
垂直分表一般是表中的字段较多，将冗余字段，不常用字段，
数据较大，长度较长（例如 text 类型字段）的拆分到“扩展表“。
一般是针对那种几百列的宽表，也可以避免在查询时，数据量太大造成的“跨页”问题。
2 、结果：垂直分表的结果是
每个表的结构都不一样；
每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
所有表的并集是全量数据。
3 、场景：系统绝对并发量并没有上来，表的记录并不多，
但是字段多，并且热点数据和非热点数据在一起，单行数据所需的
存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读 IO，产生 IO 瓶颈。
4 、分析：可以用列表页和详情页来帮助理解。
垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，
非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读 IO。
拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用 join，
因为 join 不仅会增加 CPU 负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。
关联数据，应该在业务 Service 层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

      垂直分表，比较适用于那种字段比较多的表，假设我们一张表有 100 个字段，
      我们分析了一下当前业务执行的 SQL 语句，有20 个字段是经常使用的，而另外 80 个字段使用比较少。
      这样我们就可以把 20 个字段放在主表里面，我们在创建一个辅助表，存放另外 80 个字段。
      当然主表和辅助表都是有主键的。他们通过主键进行关联合并，就可以凑成 100 个字段的表。
      通常我们按以下原则进行垂直拆分:
      1）把不常用的字段单独放在一张表;
      2）把 text，blob 等大字段拆分出来放在附表中;
	  3）经常组合查询的列放在一张表中;

d 、水平分表（库内分表）

概念：水平分表是以字段为依据，按照一定策略（hash、range 等），
将一个表中的数据拆分到多个表中，也称为库内分表。
结果：水平分表的结果是
①每个表的结构都一样；
②每个表的数据都不一样，没有交集；
③所有表的并集是全量数据。
场景：系统绝对并发量并没有上来，只是单表的数据量太多，
影响了 SQL 效率，加重了 CPU 负担，以至于成为瓶颈。
分析：表的数据量少了，单次 SQL 执行效率高，自然减轻了 CPU 的负担。

🍁 三、分库分表总结

🍃 3.1 垂直拆分优点：

1)跟随业务进行分割，和最近流行的微服务概念相似，方便解耦之后的管理及扩展。
2)高并发的场景下，垂直拆分使用多台服务器的 CPU、I/O、
内存能提升性能，同时对单机数据库连接数、一些资源限制也得到了提升。
3）能实现冷热数据的分离。

🍃 3.2 水平拆分的优点

水平扩展能无线扩展。不存在某个库某个表过大的情况。
能够较好的应对高并发，同时可以将热点数据打散。
应用侧的改动较小，不需要根据业务来拆分。

分库分表的顺序应该是先垂直分，后水平分，先垂直分表，再垂直分库，再水平分库，最后水平分表。因为垂直分更简单，更符合人们处理现实世界问题的方式。

🍃 3.2 分库分表和表分区的区别

表分区（Partitioning）可以将一张表的数据分别存储为多个文件。
如果在写 SQL 的时候，遵从了分区规则，
那么就能把原本需要遍历全表的工作转变为只需要遍历表里某一个或某些分区的工作。
这样降低了查询对服务器的压力，提升了查询效率。如果分区表使用得当，
那么也可以大规模地提升 MySQL 的服务能力。
但是这种分区方式，一方面，在使用的时候必须遵从分区规则写 SQL语句，
如果不符合分区规则，那么性能反而会非常低下；另一方面，分区的结果受到 MySQL 实例，
或者说 MySQL 单实例的数据文件无法分布式存储的限制，不管怎么分区，
所有的数据还是都在一个服务器上，没办法通过水平扩展物理服务的方法把压力分摊出去。
分表与分区的区别在于：分区一般都是放在单机里的，
从逻辑上来讲只有一张表，是 MySQL 的一种内部实现；
而分表则是将一张表分解成多张表，分库分表需要代码实现。分库分表和分区并不冲突，可以结合使用。

大家点赞、收藏、关注、评论啦 👇🏻👇🏻👇🏻微信公众号👇🏻👇🏻👇🏻