mysql数据库分库分表实践

一、背景

随着零售门店数量的增长，库存表，优惠劵表，消息表，订单表数据量不断的增多，目前一主（写）多从的MySQL 架构难于支撑公司业务的爆发式增长

二、调研

前期在于重点解决 MySQL 的单机性能和容量无法线性和灵活扩展的问题，最终选择了 Mycat，在调研阶段，对以下技术特性进行了重点考虑：

1. 协议兼容 MySQL
2. 支持 SQL 92标准
3. 可在线扩展
4. 支持读写分离，支持Mysql双主多从，以及一主多从的模式
5. 支持全局表，数据自动分片到多个节点，用于高效表关联查询
6. 支持独有的基于E-R 关系的分片策略，实现了高效的表关联查询

三、架构设计改造

目前生产数据库架构

改造后数据库架构图

四、测试环境测试

• 数据分片规则的选择
  最终比较选择了：一致性 hash

• 常用的分片规则：

1. 按范围分区
   制定基准列的取值范围，然后把这一范围的所有数据都放到一个DN上面
   优点：适用于整体数量可知或总数量为固定值的情况。
   缺点：dataNode 划分节点是事先建好的，需要扩展时比较麻烦。潜在的问题，如果在短时间发生海量的顺序插入操作，而每一个dataNode（分库）设定的数量比较高(比如说一个dataNode设定的放1000W条数据),那么在这个时候,会出现某一个dataNode（分库）IO压力非常高，而其他几个dataNode（分库）完全没有IO操作，就会出现类似于DB中常见的热块/热盘的现象

2. 按枚举分片
   此种分片规则理解为枚举分区，会比较适合于取值固定的场合，比如说性别（0,1），省份（固定值）。
   优点：用逗号分隔可以把多个值放在一个分区里面。
   缺点：其他非枚举情况不适合

3. 按日期分片
   切分规则根据配置中输入的各项值。配置中配置了格式，开始日期，分区天数，即默认从开始日期算起，分隔10天一个分区，需要提前将分片规划好，建好，否则有可能日期超出实际配置分片数

4. 取模
   切分规则根据配置中输入的数值n。此种分片规则将数据分成n份（通常dn节点也为n），从而将数据均匀的分布于各节点上。
   优点：这种策略可以很好的分散数据库写的压力。比较适合于单点查询的情景。
   缺点：一旦出现了范围查询，就需要MyCAT去合并结果，当数据量偏高的时候，这种跨库查询+合并结果消耗的时间有可能会增加很多，尤其是还出现了order by的时候

5. 一致性hash
   优点有效解决了分布式数据库的扩容问题
   缺点：在横向扩展的时候，需要迁移部分数据,由于虚拟桶倍数与分片节点数都必须是正整数，而且要服从"虚拟桶倍数×分片节点数=设计极限"，因此在横向扩容的过程中，增加分片节点并不是一台一台地加上去的，而是以一种因式分解的方式增加，因此有浪费物理计算力的可能性

• mycat对于DML、DDL支持
  如果需要操作数据，通过mycat可以执行dml ddl语句

• 分库、分区字段的选取
  分区字段建议:时间字段create_time，每个分区根据时间的不同存储对应的数据
  分库：系统级别、访问量选取：比如订单相关业务，优先app端用户订单列表的访问->app详情->admin订单查询->…
  订单可选取member_id(用户ID)进行分库，针对没有memberId的字段表新增memberId字段

• 全局序列
  在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，MyCat 提供了全局sequence，并且提供了包含本地配置和数据库配置等多种实现方式

• 应用程序测试问题发现改造

• 应用强制访问主库配置方式测试，因部分业务必须访问主库

• 读写分离测试

五、线上部署实现