MYSQL 之 分库分表

分库分表

关系型数据库本身⽐较容易成为系统瓶颈，单机存储容量、连接数、处理能⼒都有限。当单表的数据量 达到2000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严 重。此时就要考虑对其进⾏切分了，切分的⽬的就在于减少数据库的负担，缩短查询时间。

数据库分布式核⼼内容⽆⾮就是数据切分（Sharding），以及切分后对数据的定位、整 合。数据切分 就是将数据分散存储到多个数据库中，使得单⼀数据库中的数据量变⼩，通过扩充主机的数量缓解单⼀ 数据库的性能问题，从⽽达到提升数据库操作性能的⽬的。

数据切分根据其切分类型，可以分为两种⽅式：垂直（纵向）切分和⽔平（横向）切分

• 垂直（纵向）切分

  垂直切分常见有垂直分库和垂直分表两种。

  垂直分库

  垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与⼤系统拆分为多 个⼩系统类似，按业务分类进⾏独⽴划分。与"微服务治理"的做法相似，每个微服务使⽤单独的⼀ 个数据库。如图：

垂直分表

垂直分表是基于数据库中的"列"进⾏，某个表字段较多，可以新建⼀张扩展表，将不经常⽤或字段 ⻓度较⼤的字段拆分出去到扩展表中。在字段很多的情况下（例如⼀个⼤表有100多个字段），通 过"⼤表拆⼩表"，更便于开发与维护，也能避免跨⻚问题，MySQL底层是通过数据⻚存储的，⼀条 记录占⽤空间过⼤会导致跨⻚，造成额外的性能开销。另外数据库以⾏为单位将数据加载到内存 中，这样表中字段⻓度较短且访问频率较⾼，内存能加载更多的数据，命中率更⾼，减少了磁盘 IO，从⽽提升了数据库性能。

垂直切分的优点：

• 解决业务系统层⾯的耦合，业务清晰, 与微服务的治理类似，也能对不同业务的数据进⾏分级 管理、维护、监控、扩展等

• ⾼并发场景下，垂直切分⼀定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

• 缺点:

  • 部分表⽆法join，只能通过接⼝聚合⽅式解决，提升了开发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过⼤的问题（需要⽔平切分）

• ⽔平（横向）切分

  当⼀个应⽤难以再细粒度的垂直切分，或切分后数据量⾏数巨⼤，存在单库读写、存储性能瓶颈， 这时候就需要进⾏⽔平切分了。 ⽔平切分分为库内分表和分库分表，是根据表内数据内在的逻辑 关系，将同⼀个表按不同的条件分散到多个数据库或多个表中，每个表中只包含⼀部分数据，从⽽ 使得单个表的数据量变⼩，达到分布式的效果。如图所示：库内分表只解决了单⼀表数据量过⼤的 问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压⼒来说，帮助不是很 大，大家还是竞争同⼀个物理机的CPU、内 存、⽹络IO，最好通过分库分表来解决。

  ⽔平切分的优点：

  • ​ 不存在单库数据量过⼤、⾼并发的性能瓶颈，提升系统稳定性和负载能⼒
  • 不存在单库数据量过⼤、⾼并发的性能瓶颈，提升系统稳定性和负载能⼒

  缺点：

  • 跨分⽚的事务⼀致性难以保证跨库的join关联查询性能较差
  • 跨分⽚的事务⼀致性难以保证跨库的join关联查询性能较差

  ⽔平切分后同⼀张表会出现在多个数据库/表中，每个库/表的内容不同。⼏种典型的数据

• 分片规则：

  根据数值范围

  按照时间区间或ID区间来切分。例如：按⽇期将不同⽉甚⾄是⽇的数据分散到不同的库中；将userId为 1_{9999的记录分到第⼀个库，10000}20000的分到第⼆个库，以此类推。某种意义上，某些系统中使 ⽤的"冷热数据分离"，将⼀些使⽤较少的历史数据迁移到其他库 中，业务功能上只提供热点数据的查询，也是类似的实践。

  优点：

  1. 单表⼤⼩可控
  2. 然便于⽔平扩展，后期如果想对整个分⽚集群扩容时，只需要添加节点即可， ⽆需对其他分⽚ 的数据进⾏迁移 .
  3. 使⽤分⽚字段进⾏范围查找时，连续分⽚可快速定位分⽚进⾏快速查询，有效避免跨分⽚查询的问 题。

  缺点：

  1. 热点数据成为性能瓶颈。连续分⽚可能存在数据热点，例如按时间字段分⽚，有些分⽚存储最近时 间段内的数据，可能会被频繁的读写，⽽有些分⽚存储的历史数据，则很少被查询

根据数值取模

⼀般采⽤hash取模mod的切分⽅式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0 的放到第⼀个库，余数为1的放到第⼆个库，以此类推。这样同⼀个⽤户的数据会分散到同⼀个库中， 如果查询条件带有cusno字段，则可明确定位到相应库去查询。

简单思考？

⽔平切分后，对于按cusno查询的需求能很好的满⾜，可以直接路由到具体数据库。⽽按⾮cusno 的查询，例如name，就不知道具体该访问哪个库了??

优点：

1. 数据分⽚相对⽐较均匀，不容易出现热点和并发访问的瓶颈

缺点：

1. 后期分⽚集群扩容时，需要迁移旧的数据（使⽤⼀致性hash算法能较好的避免这个问题
2. 容易⾯临跨分⽚查询的复杂问题。⽐如上例中，如果频繁⽤到的查询条件中不带cusno时，将会导 致⽆法定位数据库，从⽽需要同时向4个库发起查询，再在内存中合并数据，取最⼩集返回给应 ⽤，分库反⽽成为拖累。

• 分库分表带来的问题

  1、事务⼀致性问题

  ​ 当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。跨分⽚事务也是分布式事务，没 有简单的⽅案，⼀般可使⽤"XA协议"和"两阶段提交"处理。

  ​ 分布式事务能最⼤限度保证了数据库操作的原⼦性。但在提交事务时需要协调多个节点，推后了提 交事务的时间点，延⻓了事务的执⾏时间。导致事务在访问共享资源时发⽣冲突或死锁的概率增 ⾼。随着数据库节点的增多，这种趋势会越来越严重，从⽽成为系统在数据库层⾯上⽔平扩展的枷 锁。

  最终⼀致性对于那些性能要求很⾼，但对⼀致性要求不⾼的系统，往往不苛求系统的实时⼀致性， 只要在允许的时间段内达到最终⼀致性即可，可采⽤事务补偿的⽅式。与事务在执⾏中发⽣错误后 ⽴即回滚的⽅式不同，事务补偿是⼀种事后检查补救的措施，⼀些常⻅的实现⽅法有：对数据进⾏ 对账检查，基于⽇志进⾏对⽐，定期同标准数据来源进⾏同步等等。事务补偿还要结合业务系统来 考虑

  2、跨节点关联查询 join问题

  ​ 切分之前，系统中很多列表和详情⻚所需的数据可以通过sql join来完成。⽽切分之后，数据可能 分布在不同的节点上，此时join带来的问题就⽐较麻烦了，考虑到性能，尽量避免使⽤join查询。 解决这个问题的⼀些⽅法

  1）全局表

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

  2）字段冗余

  ⼀种典型的反范式设计，利⽤空间换时间，为了性能⽽避免join查询。例如：订单表保存userId时 候，也将userName冗余保存⼀份，这样查询订单详情时就不需要再去查询"买家user表"了。

  3）数据组装

  在系统层⾯，分两次查询，第⼀次查询的结果集中找出关联数据id，然后根据id发起第⼆次请求得 到关联数据。最后将获得到的数据进⾏字段拼装。

  3、跨节点分⻚、排序、函数问题

  ​ 跨节点多库进⾏查询时，会出现limit分⻚、order by排序等问题。分⻚需要按照指定字段进⾏排 序，当排序字段就是分⽚字段时，通过分⽚规则就⽐较容易定位到指定的分⽚；当排序字段⾮分⽚ 字段时，就变得⽐较复杂了。需要先在不同的分⽚节点中将数据进⾏排序并返回，然后将不同分⽚ 返回的结果集进⾏汇总和再次排序，最终返回给⽤户。如图所示：

4、全局主键避重问题

​ 在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使⽤的⾃增⻓将⽆⽤武之 地，某个分区数据库⾃⽣成的ID⽆法保证全局唯⼀。因此需要单独设计全局主键，以避免跨库主键 重复问题。有⼀些常⻅的主键⽣成策略：

• UUID

  UUID标准形式包含32个16进制数字，分为5段，形式为844412的36个字符，例如： 550e8400e29b41d4a716446655440000

  UUID是主键是最简单的⽅案，本地⽣成，性能⾼，没有⽹络耗时。但缺点也很明显，由于 UUID⾮常⻓，会占⽤⼤量的存储空间；另外，作为主键建⽴索引和基于索引进⾏查询时都会 存在性能问题，在InnoDB下，UUID的⽆序性会引起数据位置频繁变动，导致分⻚。

• 结合数据库维护主键ID表

  ​ 这⼀⽅案的整体思想是：建⽴2个以上的全局ID⽣成的服务器，每个服务器上只部署⼀个数据 库，每个库有⼀张sequence表⽤于记录当前全局ID。表中ID增⻓的步⻓是库的数量， 起始值 依次错开，这样能将ID的⽣成散列到各个数据库上。如下图所示：由两个数据库服务器⽣成 ID，设置不同的auto_increment值。第⼀台sequence的起始值为1，每次步⻓增⻓2，另⼀ 台的sequence起始值为2，每次步⻓增⻓也是2。结果第⼀台⽣成的ID都是奇数（1, 3, 5, 7 …），第⼆台⽣成的ID都是偶数（2, 4, 6, 8 …）。 这种⽅案将⽣成ID的压⼒均匀分布在两台 机器上。同时提供了系统容错，第⼀台出现了错误，可以⾃动切换到第⼆台机器上获取ID。

  但有以下⼏个缺点：系统添加机器，⽔平扩展时较复杂；每次获取ID都要读写⼀次DB，DB 的压⼒还是很⼤，只能靠堆机器来提升性能。

• Snowflake分布式⾃增ID算法

  Twitter的snowflake算法解决了分布式系统⽣成全局ID的需求，⽣成64位的Long型数字，组 成部分：

  • 第⼀位未使⽤

• 接下来41位是毫秒级时间，41位的⻓度可以表示69年的时间

  • 5位datacenterId，5位workerId。10位的⻓度最多⽀持部署1024个节点、

• 最后12位是毫秒内的计数，12位的计数顺序号⽀持每个节点每毫秒产⽣4096个ID序列

理论上QPS约为409.6w/s（1000*2^12），并且整个分布式系统内不会产⽣ID碰撞；可根据⾃身 业务灵活分配bit位。

不⾜就在于：强依赖机器时钟，如果时钟回拨，则可能导致⽣成ID重复。

• Leaf——美团点评分布式ID⽣成系统 考虑到了⾼可⽤、容灾、分布式 推荐使⽤。 https://tech.meituan.com/2017/04/21/mt-leaf.html

5、数据迁移、扩容问题

​ 当业务⾼速发展，⾯临性能和存储的瓶颈时，才会考虑分⽚设计，此时就不可避免的需要考虑历史数据 迁移的问题。⼀般做法是先读出历史数据，然后按指定的分⽚规则再将数据写⼊到各个分⽚节点中。此 外还需要根据当前的数据量和QPS，以及业务发展的速度，进⾏容 量规划，推算出⼤概需要多少分⽚（⼀般建议单个分⽚上的单表数据量不超过1000W）

​ 如果采⽤数值范围分⽚，只需要添加节点就可以进⾏扩容了，不需要对分⽚数据迁移。如果采⽤的是数 值取模分⽚，则考虑后期的扩容问题就相对⽐较麻烦

• 什么时候烤考虑切分

  1、能不切分尽量不要切分并不是所有表都需要进⾏切分，主要还是看数据的增⻓速度。切分后会 在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求 也是其重要⼯作之⼀。不到万不得已不⽤轻易使⽤分库分表这个⼤招，避免"过度设计"和"过早优 化"。分库分表之前，不要为分⽽分，先尽⼒去做⼒所能及的事情，例如：升级硬件、升级⽹络、 读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

  2、数据量过⼤、增⻓过快，正常运维影响业务访问

  ​ 对数据库备份，如果单表太⼤，备份时需要⼤量的磁盘IO和⽹络IO。例如1T的数据，⽹络传输 占50MB时候，需要20000秒才 能传输完毕，整个过程的⻛险都是⽐较⾼的

  ​ 对⼀个很⼤的表进⾏DDL修改时，MySQL会锁住全表，这个时间会很⻓，这段时间业务不能访 问此表，影响很⼤。⼤表会经常 访问与更新，就更有可能出现锁等待。将数据切分，⽤空间换时 间，变相降低访问压⼒

  3、随着业务发展，需要对某些字段垂直拆分

  假如项⽬⼀开始设计的⽤户表如下：
  

  4、安全性和可⽤性

这段时间业务不能访 问此表，影响很⼤。⼤表会经常 访问与更新，就更有可能出现锁等待。将数据切分，⽤空间换时 间，变相降低访问压⼒