MySQL如何进行分库分表

什么是分库分表

分库

• 垂直拆分 例如原本所有的服务都在同一个数据库实例中，可以将其通过**功能划分**拆分为 OrderDb、ProductDb等
• 水平拆分 数据量过大，同一个类型的数据库拆分为多个表 OrderDb_01、OrderDb_02
  

分表

• 垂直拆分 对于Order表来说，可以根据数据的**查询频率**进行拆分，对于某些Text、Blob等**较长的字段**，需要单独拆分出一个表
• 水平拆分 数据量过大，将Order表拆分为Order_01、Order_02等
  

为什么要进行分库分表

• **性能瓶颈**，MySQL单表在数据量过大的情况下，索引的B+树达到三层，**查询性能**会急剧下降
• 单表占用的**存储空间过大**，如果需要备份等操作，单表需要耗费大量的时间，在业务迭代需要**新增字段或新增索引**时，锁表的时间会很长
• 数据库实例的**连接数是有限**的，单库的情况下连接数可能不足，因此会报错 “Too many connections”
  

如何判断是否需要分库分表

• 分库分表会带来很多问题，**增大排查问题的难度**，**在业务编写时更为复杂**，因此需要合理的评估是否需要进行分库分表，不到特殊时刻，不进行分库分表
• **当前数据量**，如果当前的数据量或是接下来一两年的数据量都不会到达**百万级**，即不需要分库分表。即使是千万级或亿级的数据，在**字段较少并且索引查询**的情况下，仍然可以**有较好的性能**。因此具体的数量级判断是否需要分库分表需要根据**SQL的性能**判断。
• **数据量存在瓶颈**，当数据量过大占用磁盘空间过多时，先考虑**先增加硬盘配置**，可以考虑通过业务进行垂直分库，将不同的数据库分配到不同的磁盘中
• **性能存在瓶颈**，在性能存在瓶颈时，先通过SQL优化、索引优化、数据库表结构、数据库参数优化、表可以根据冷热拆分进行优化
• 在**以上的解决方案都尝试**后，仍然存在性能瓶颈时，才考虑最复杂的**水平分库分表**
  

数据库瓶颈

IO瓶颈

• **磁盘IO**，**Buffer Pool的空间不足**以存储热点数据，因此需要**频繁加载磁盘中的数据页**到Buffer Pool中。解决方案：垂直分库，垂直分表
• **垂直分库**后，多个实例能有**更大的Buffer Pool**空间，因此能够存储的热点数据也就更多，从而减少对磁盘的访问
• MySQL从磁盘中查询数据的最小单位是页，在**垂直分表**后，**页的大小变小**了，因此Buffer Pool中可以**存在更多的热点数据页**，从而减少对磁盘的访问
• 网络IO，查询的数据量过大，分库让网络压力分配到不同的数据库实例中。
  

CPU瓶颈

• SQL问题，join、group、order等，或者增加**CPU运算**的操作。需要进行索引优化、将更多的业务放在Java层面做
• **单表的数据量过大**，扫描的行数过多，需要水平分表
  

如何查看IO瓶颈还是CPU瓶颈

• ps -ef|grep mysqld 查询MySQL的进程号
• top -H -p mysql_pid 查询MySQL的详细信息，通过cpu和io指标判断是IO瓶颈还是CPU瓶颈
  

问题

无论是CPU瓶颈还是IO瓶颈，最终都会导致MySQL在**处理请求的时间变长**，从而会导致连接需要很长时间才会释放，容易导致MySQL连接池满等问题。

分库分表步骤

根据容量或增长量决定分库或分表数量

• **先判断数据量**，以12306用户表举例，所有的人都可以成为用户，一个身份证只能注册一个用户，目前中国有14亿人口，然而12306是一个长期运行的服务，预计若干年后用户量能够达到**30亿数据量**
• 判断**单表的瓶颈**，先通过插入模拟数据的方式找出数据表大致的瓶颈，假设在数据量2000w数据量后数据会变慢
• 计算总共大致**需要多少张表**，因此需要总计大约 30亿/2000w=150张表，因为方便于数据库扩容的关系，分库和分表的数量通常设定为2的次方
• 根据**连接数判断需要分多少个库**，在统计完总共需要多少张表后，通过数据库连接的能力判断分库的数量
  

选定分表键

• 访问频率，经常访问的数据尽量在同一个分片上
• 数据均匀性，数据的分片相对均匀，不能出现大量热点数据集中出现在同一个分片上的情况
• 业务关联性，分片键需要与其他业务字段关联性较强
• 不可变性
  

分片规则

根据数值范围分片

• 根据Id或时间的数值范围进行数据的区分，例如0-9999在表01，10000-19999在表2，也是**冷热数据分离**的实践。
• 根据数值分片可以很好的控制**单表的数据量**，并且根据数值分表**不需要考虑数据迁移**的问题，因为只需要根据数值范围继续创建新的表即可
• 通过分片键进行**范围查询**时，可以快速定位到数据的位置，减少跨分片查询的情况
• 连续分片的数据容易出现热点数据的堆积，某个分片被频繁的进行读写，而其他的分片读写又相对较少
  

根据Hash取模

• 根据Hash取模后数值判断分表的情况
• 不容易出现热点数据，分表的情况会比较均匀，不会因为热点数据而出现瓶颈
• 在扩容时需要进行数据迁移
• 查询时需要带上分片键，如果在查询条件没有分片键的情况下，或是需要进行范围查询等情况，则需要对所有的库表都进行查询
  

分库分表技术选型

JDBC类

• 直接向数据库发起请求，性能较高
• 需要修改代码，需要引入相关jar包后，再修改配置文件，不需要修改业务代码
• 对Java应用的内存有一定影响
  

Proxy类

• 完全不需要修改Java程序的代码
• 集中形式的管理和监控，便于维护和升级
• 在连接数据库的链路上多了一层，产生一定性能的损耗
• 对于Proxy本身需要做高可用，如果Proxy挂了，MySQL没挂，系统也是无法提供服务的
  

分库分表问题

分布式事务

• 强一致性
• 最终一致性
  

跨库JOIN问题

• 全局字典表，对于公共的配置可以在每个库中都冗余一个表，因为不改动所以也不会涉及到数据的一致性问题
• 字段冗余，通过反范式的设计从而不需要进行JOIN查询，但是字段冗余会带来数据更新的数据不一致问题
• 业务组装，在Java程序中经过多次查询从而组装信息
• ER分片，在数据库划分时，尽量将会产生关联的表分配在一个库中
  

跨库分页、排序、函数等问题

• 对每一个表都进行操作，最后再对所有表的结果集再进行一次统一的操作
• 以分页举例，根据id查询出所有表中的前10条，然后再将所有表的结果集再根据id取出前10条，即为最终的结果
• 以sum()举例，计算出所有表中的sum()结果，再将所有表的结果集再进行sum()操作
  

非分片键查询（仅一个字段）

• **关联法**，建立其他字段与分片键的关联关系表，通过建立索引方式优化查询，先查询出元素所对应的分片键，再通过分片键进行查询。需要额外进行一次查询，**影响效率**，在**访问量较大**的情况下**不能使用**这种方案，适合用作于**查询频率较小**，并且需要唯一索引的情况，例如判断手机号、邮箱唯一。
• 例如在以userName作为分片键的情况下，建立phone和userName的关联关系表，在需要以phone作为查询条件时，先通过phone查询到userName，再以userName作为查询条件查找对应的信息
• **基因法**，将分片键的信息融合在其他字段中中，可以通过某种函数得到分片键字段的信息。实际运用中常参考雪花算法的生成思想，将多种信息拼接在一起。
• 例如在userId作为分片键的情况下，在生成订单时，将**userId的末尾x位拼接在订单号**后（参考淘宝订单设计），在使用订单号查询时，可以通过订单号获取到**userId末尾的x位**，再进行取模后即可查找到对应的表分片位置。
• 具体的位数由分表的数量决定，冗余的数字需要大于表数量。这种方式只需要进行一次数据库查询，因此**效率较高**。
• 如果有更复杂的查询需求，只能通过Es、HBase等数据库进行解决
  

分库分表扩容

采用**双倍扩容**策略，避免数据迁移。扩容前每个节点的数据，有一半要迁移至一个新增节点中，对应关系比较简单。 具体操作如下(假设已有 2 个节点 A/B，要双倍扩容至 A/A2/B/B2 这 4 个节点)：

• 无需停止应用服务器；
• 新增两个数据库 A2/B2 作为从库，设置主从同步关系为：A=>A2、B=>B2，直至主从数据同步完毕(早期数据可手工同步)；
• 调整分片规则并使之生效： 原 ID%2=0 => A 改为 ID%4=0 => A, ID%4=2 => A2； 原 ID%2=1 => B 改为 ID%4=1 => B, ID%4=3 => B2。
• 解除数据库实例的主从同步关系，并使之生效；
• 此时，四个节点的数据都已完整，只是有冗余(多存了和自己配对的节点的那部分数据)，择机清除即可(过后随时进行，不影响业务)。