# 一、背景先交代一下目标表的情况，后台管理系统，表存储的是各个公司的客户线索数据，使用场景是在ORM里面给各个公司提供数据后台系统，各个公司可以通过自己的账号登录查看自己公司的数据。目前，单表数据量已经达到877W+,且增长速度在每月90W-120W的区间，半年之后数据量可能会超过千万，后台页面对这行表的分页筛选查询，统计效率可能会越来越低，因此需要对这张表进行拆分，表里有很多字段，为了简化和脱敏我就不全部写出来了，只列一下主要字段： ``` CREATE TABLE `clue_notify` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', `clue_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '线索ID', `visitor_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '公司ID', `city_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '城市ID', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 0未删除 1删除', PRIMARY KEY (`id`), KEY `idx_clue_visitor_id` (`clue_id`,`visitor_id`) USING BTREE, KEY `idx_create_time` (`create_time`) USING BTREE, KEY `idx_update_time` (`update_time`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='线索推送表' ``` **现有数据量** 877W+ **增长情况** 日增长近4W ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7c1de2f33cb3485db1d4ffb15d4933da~tplv-k3u1fbpfcp-watermark.image?) # 二、拆分方案水平拆分，目标是尽量均匀的将数据均匀分散到10张表，一张表1000W的量来计算的话，可以实现1亿数据量的稳定查询，目前的候选方案主要有两个，一个是按公司数据分表，即按visitor_id字段进行分片，另一个是按线索id，即clue_id字段进行分片。下面对这两种方案进行分析 ## 方案一、按visitor_id分片按公司数据分表，即按visitor_id字段进行分片 ![按visitor_id分片.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6d448d838fa04382babdc4c2682abf65~tplv-k3u1fbpfcp-watermark.image?) ### 优点 1. 同一家公司的的线索数据只会存在同一张表里面，数据不会过于分散，一般只会查同一家公司的数据，不用进行查询结果结果集的归并，性能优势大 2. 后面的关联数据查询中会带来很大的便利，后面查询时知道visitor_id就能确定要到哪一张子表中进行查询，读写逻辑简单 ### 缺点不同公司客户流量不同，且差异很大,数据分散不均匀，增长速度差异大，单按目前接入的公司的数量还做不到分散到10张表中，数据分片效果难以达到预期 ![image.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/31bc9e38b90a45a6bc82d170cd95b7b0~tplv-k3u1fbpfcp-watermark.image?) ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/15759f0fd6e04261bba1521878f43616~tplv-k3u1fbpfcp-watermark.image?) ## 方案二、按clue_id分片 ![未命名文件(1).png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b9064777708d4914b6c0a063d57719f5~tplv-k3u1fbpfcp-watermark.image?) 按线索id，即clue_id字段进行分片，分表的时候忽略公司属性 ### 优点数据分散均匀，避开了方案一的缺点，不受公司业务数据量的影响 ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/46b9711345de40e197287049ed7c6d27~tplv-k3u1fbpfcp-watermark.image?) ### 缺点同一个公司的数据会分散到各个子表中,业务上查询可能会比较麻烦，但是利用数据库分表组件有成熟的解决方案，虽然组件有自动进行归并的功能，但进行查询结果归并还是会在一定程度上影响性能 ## 结论使用方案一，更符合业务特点，虽然数据做不到更均匀的分散，但是能减少多表查询和数据归并，更符合业务特点，数据不均匀的问题通过人工指定和默认分片规则结合这种方式来进行规避 # 三、数据表拆技术实现方案目前国内比较成熟的开源数据库中间件有sharding-jdbc、mycat，下面将对以下两种中间件进行比较，相同点： 1. 设计理念相同，主流程都是SQL解析-->SQL路由-->SQL改写-->SQL还行-->结果归并 2. 查询的原理一致，非分片字段查询都会查询所有分片然后结果归并，分片字段查询则直接搜索分片数据库查询。不同点： 1. Mycat是基于Proxy，类似于nginx，它复写了MySQL协议，将MycatServer伪装成一个MySQL数据库。优点是保证数据库的安全性，归并数据结果完全解耦，缺点是效率偏低。 2. Sharding-JDBC是基于**JDBC**的扩展，是以jar包的形式提供轻量级服务的。优点是效率较高，缺点是归并数据结果没有实现解耦，有可能会影响到我们业务逻辑代码。还容易内存溢出，所以要做分页处理。除了上面的开源中间件，也可以自己实现分表，主要的工作是：自己实现分片规则，分布式主键ID生成算法，为了减少原来代码的改动，自己实现一个mybatis插件来动态地修改表名完成分表查询 ## 方案一、mycat ![Image [7].png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/18041258805a402896d2e1c86fdc8b51~tplv-k3u1fbpfcp-watermark.image?) MyCat是一个第三方服务器端数据库中间件，客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器中。 mycat是一个独立的应用需要单独部署，以逻辑表的形式屏蔽掉应用处理分库分表的复杂逻辑，遵守Mysql原生协议，跨语言，跨平台，有着更为通用的应用场景。 ### 优点跨语言，更通用 ### 缺点实现复杂，需要单独部署集群，官方文档鸡肋 ## 方案二、Sharding-Jdbc Sharding-Jdbc是一个本地数据库中间件框架，采用Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。 ![Image [9].png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6af7c4fbccf04fd28cd338554c484ae1~tplv-k3u1fbpfcp-watermark.image?) ![Image [8].png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/cd1848cc71f84515af1ce97df563654a~tplv-k3u1fbpfcp-watermark.image?) ### 优点从架构上看 sharding-jdbc 更符合分布式架构的设计，直连数据库，没有中间应用，理论性能是最高的（实际性能需要结合具体的代码实现，理论性能可以理解为上限，通过不断优化代码实现，逐渐接近理论性能）。从开发的角度上看，有适用于springboot的starter，代码改动量少 ### 缺点由于作为组件存在，需要集成在应用内，意味着作为使用方，必须要集成到代码里，使得开发成本相对较高；另一方面，由于需要集成在应用内，使得需要针对不同语言（java、C、PHP……）有不同的实现（事实上sharding-jdbc目前只支持java），这样组件本身的维护成本也会很高。最终将应用场景限定在由java开发的应用这一种场景下。 sharding-jdbc会影响项目的所有业务表，因为最终数据库交互都由ShardingPreparedStatement去做了，历史的一些sql语句因为sql函数或者其他写法，使得ShardingPreparedStatement无法处理而出现异常。 ## 方案三、自定义分表策略，通过mybatis插件实现分表这种方式相对于中间件的开箱即用带来的便利性来讲，在开发量上会多出些许的工作，Sharding-Jdbc会直接接管原来的数据源，可能会带来一些不可控的影响，所以自定义分表策略的优缺点主要有下面这些。与sharding相比更加可控，只会在需要分表的地方进行拦截处理，不会全盘接管数据源优点：精准可控缺点：代码量多，需要自己实现分表策略和主键生成策略，需要开发mybatis插件 ## 结论 mycat太重不做考虑，由于原来分表场景很简单以及已经存在一些历史sql, 担心引入sharding会引起其他不涉及扽表的sql异常，最终选择方案三 # 四、分表实现方案 ## 实现方案一、使用Mybatis实现自定义分表策略 ![分表流程设计.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a8af4023bb674432a791efc589a3a137~tplv-k3u1fbpfcp-watermark.image?) ### 分表策略 ShardingMap是写在配置文件中的指定分表，配置如下： ``` sharding: notify: tableCount: 10 shardingMap: {10 : 8} ``` ``` /** * @Description 分表策略 * @Date 2021/11/30 4:06 下午 */ public interface IShardingTableStrategy { String UNDERLINE = "_"; /** * 计算获取对应表号 * @param tableNamePrefix 表名前缀 * @param shardingKey 分表key * @return 分库序号 */ String getTargetTableName(String tableNamePrefix, Object shardingKey); } ``` 默认分表策略： ``` /** *
默认分库策略： *
10个表 *
按分片键取hash之后对10取模 * @Date 2021/11/30 4:08 下午 */ @Component @Slf4j public class DefaultShardingTableStrategy implements IShardingTableStrategy { /** * 表名前缀 */ public static final String TABLE_NAME_PREFIX = "clue_notify"; private static final String UNDERLINE = "_"; @Autowired NotifyShardingMapProperties properties; /** * 有指定规则按指定分配，无指定规则按取模运算结果 * @param tableNamePrefix 表名前缀 * @param shardingKey 分表key * @return 目标表名 */ @Override @Cacheable(value = {"getTargetTableName"}, key = "#root.methodName + ':' + #tableNamePrefix + ':' + #shardingKey") public String getTargetTableName(String tableNamePrefix, Object shardingKey) { Map shardingMap = properties.getShardingMap(); Class aClass = shardingKey.getClass(); if (aClass != Long.class) { //此处可以缓存优化 throw new RuntimeException("分片键类型错误，请检查分片键类型"); } long visitorId = (long) shardingKey; return TABLE_NAME_PREFIX + UNDERLINE + shardingMap.getOrDefault(visitorId, visitorId % properties.getTableCount()); } } ``` ### 分表注解 ``` /** * 分表注解，可以自动替换表名 * 在Mapper接口类名上会扫描所有方法替换表名 * 写在Mapper接口方法上会扫描当前注解的方法替换表名 * @Date 2021/11/30 4:29 下午 */ @Target({ElementType.TYPE, ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) public @interface TableSharding { // 表前缀名 String tableNamePrefix(); //值,visitor_id入参字段名 String value() default ""; //是需要按照给定字段名分表，如果是需要解析请求参数改字段名的值（默认否） boolean fieldFlag() default false; // 对应的分表策略类 Class shardingStrategy(); } ``` ### mybatis拦截器 ``` /** * @Description： * mybatis分表拦截器，本来想在拦截器里面把分布式ID一起写进去的，但是目前写ID的地方只有一个， * 后面如果写ID的地方多的话考虑在拦截器里面统一写入 * 潜在问题：创建时间和主键两者采用的是不同的机制生成的，可能会由于并发的原因，造成二者在递增性上不能驳斥一致 * @Date 2021/11/30 4:19 下午 */ @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}) }) @Component @Slf4j public class TableShardingInterceptor implements Interceptor { private static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory(); @Override @SuppressWarnings(value = {"unchecked", "rawtypes"}) public Object intercept(Invocation invocation) throws Throwable { // MetaObject是mybatis里面提供的一个工具类，类似反射的效果 MetaObject metaObject = getMetaObject(invocation); BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql"); MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement"); //获取Mapper执行方法 Method method = invocation.getMethod(); //获取分表注解 TableSharding tableShard = getTableShard(method, mappedStatement); // 如果method与class都没有TableShard注解或执行方法不存在，说明不需要执行拦截器，执行下一个插件逻辑 if (tableShard == null) { return invocation.proceed(); } //获取值 String value = tableShard.value(); //value是否字段名，如果是，需要解析请求参数字段名的值 boolean fieldFlag = tableShard.fieldFlag(); Object valueObject = null; if (fieldFlag) { //获取请求参数 Object parameterObject = boundSql.getParameterObject(); //ParamMap类型逻辑处理 if (parameterObject instanceof MapperMethod.ParamMap) { MapperMethod.ParamMap parameterMap = (MapperMethod.ParamMap) parameterObject; //根据字段名获取参数值，如果没有该参数，会直接抛出异常 // 所以不需要执行分表逻辑的地方不要用分表注解，用了分表注解的话一定要传参加上分片键 valueObject = parameterMap.get(value); if (valueObject == null) { throw new RuntimeException(String.format("入参字段%s无匹配", value)); } //单参数逻辑 } else { //如果是基础类型抛出异常 if (isBaseType(parameterObject)) { throw new RuntimeException("单参数非法，请使用@Param注解对该参数进行命名"); } if (parameterObject instanceof Map) { Map parameterMap = (Map) parameterObject; valueObject = parameterMap.get(value); } else { //非基础类型对象 Class parameterObjectClass = parameterObject.getClass(); Field declaredField = parameterObjectClass.getDeclaredField(value); declaredField.setAccessible(true); valueObject = declaredField.get(parameterObject); } } } //替换sql replaceSql(tableShard, valueObject, metaObject, boundSql); //执行下一个插件逻辑 return invocation.proceed(); } @Override public Object plugin(Object target) { // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身, 减少目标被代理的次数 if (target instanceof StatementHandler) { return Plugin.wrap(target, this); } else { return target; } } /** * 基本数据类型验证，true是，false否 * * @return: boolean */ private boolean isBaseType(Object object) { return object.getClass().isPrimitive() || object instanceof String || object instanceof Integer || object instanceof Double || object instanceof Float || object instanceof Long || object instanceof Boolean || object instanceof Byte || object instanceof Short; } /** * @param tableShard 分表注解 * @param value 值 * @param metaObject mybatis反射对象 * @param boundSql sql信息对象 * @description: 替换sql */ private void replaceSql(TableSharding tableShard, Object value, MetaObject metaObject, BoundSql boundSql) { String tableNamePrefix = tableShard.tableNamePrefix(); //获取策略class Class strategyClazz = tableShard.shardingStrategy(); //从spring ioc容器获取策略类,也可以通过反射来创建bean，但是显然前者的效率高得多 IShardingTableStrategy tableShardStrategy = SpringUtil.getBean(strategyClazz); //生成分表名 String shardTableName = tableShardStrategy.getTargetTableName(tableNamePrefix, value); // 获取sql String sql = boundSql.getSql(); // 完成表名替换 metaObject.setValue("delegate.boundSql.sql", sql.replaceAll(tableNamePrefix, shardTableName)); } /** * 获取MetaObject对象-mybatis里面提供的一个工具类，类似反射的效果 */ private MetaObject getMetaObject(Invocation invocation) { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); // MetaObject是mybatis里面提供的一个工具类，类似反射的效果 return MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY); } /** * 获取分表注解信息 * * @param method 目标方法 * @param mappedStatement mappedStatement * @return 分表注解信息 */ private TableSharding getTableShard(Method method, MappedStatement mappedStatement) throws ClassNotFoundException { //id的格式是全方法名，packageName.classsName.methodName String id = mappedStatement.getId(); //获取ClassName final String className = id.substring(0, id.lastIndexOf(".")); //分表注解 TableSharding tableShard = null; //获取Mapper执行方法的TableShard注解 tableShard = method.getAnnotation(TableSharding.class); //如果方法没有设置注解，从Mapper接口上面获取TableShard注解 if (tableShard == null) { // 获取TableShard注解 tableShard = Class.forName(className).getAnnotation(TableSharding.class); } return tableShard; } } ``` ### 添加拦截器 ``` sqlSessionFactory.getConfiguration().addInterceptor(tableShardingInterceptor); sqlSessionFactory.getConfiguration().addInterceptor(mobileFillInterceptor); sqlSessionFactory.getConfiguration().addInterceptor(pageHelper); ``` ## 实现方案二、使用Sharding实现分表虽然最终并未采用这个方案，但是也尝试了一下用Sharding实现分表，代码改动量确实小，配置也很简单，优缺点这里就不再进行讨论量，下面是具体的实现方案 ### 相关依赖引入引入sharding-jdbc-spring-boot-starter和sharding-jdbc-spring-namespace即可 ``` 4.1.1 org.apache.shardingsphere sharding-jdbc-spring-boot-starter ${shardingsphere.version} org.apache.shardingsphere sharding-jdbc-spring-namespace ${shardingsphere.version} ``` ### 数据源配置改动多数据源配置只需要修改需要进行分表的数据源配置即可，配置修改主要在两个地方： 1. 数据源使用ShardingDataSource，替换原来的HikariDataSource 2. 配置数据分片策略 ``` @Configuration public class DatabaseConfig { private static final String BRAND_DB_NAME = "db58_cloudstore_brand"; private static final String GROUP_DB_NAME = "ydtyuanquan_db"; /** * 数据库配置 * ydtyuanquan_db * * @return */ @Lazy @Bean(name = "dataSource") public DataSource getDataSource(WConfig wConfig) throws SQLException { HikariDataSource hikariDataSource = getHikariDataSourceByDBName(wConfig, GROUP_DB_NAME); Map dataSourceMap = new HashMap<>(2); dataSourceMap.put(GROUP_DB_NAME, hikariDataSource); // 分片规则 ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration()); // 其他配置 Properties properties = new Properties(); properties.put("sql.show","true"); return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, properties); } /** * 分片规则，clue_notify_sharding为逻辑表 * @return */ private TableRuleConfiguration getOrderTableRuleConfiguration() { // 指定 t_order 表的数据分布情况，配置数据节点 TableRuleConfiguration result = new TableRuleConfiguration("clue_notify_sharding",GROUP_DB_NAME + ".clue_notify_$->{0..9}"); // 指定 t_order 表的分片策略，分片策略包括分片键和分片算法 result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("clue_id", "clue_notify_$->{clue_id%10}")); result.setKeyGeneratorConfig(getKeyGeneratorConfiguration()); return result; } /** * 定义主键生成策略，分表主键采用雪花算法生成主键 * @return */ private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() { return new KeyGeneratorConfiguration("SNOWFLAKE","id"); } } ``` ### 配置效果 $是占位符，具体数字用{}计算结果替代 ![Image [14].png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/5e4b2cdb3c294aa894ec30ea294ea5d3~tplv-k3u1fbpfcp-watermark.image?) ### 遇到的问题 #### 1、分页查询时排序字段分散在子表中原sql: ``` select * from clue_notify_sharding where visitor_id = ? order by update_time desc limit 0, 2 ``` 分表之后的效果： sharding-jdbc会转换成逻辑表分页查询 ![Image [10].png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1729ed31cb374f889565ef493153fde4~tplv-k3u1fbpfcp-watermark.image?) 真正的sql查询会在每张子表中去执行一次，效果如下： ![Image [11].png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/f600d5df15b44b1cb6b00879371adee8~tplv-k3u1fbpfcp-watermark.image?) ... ... ![Image [12].png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/22639f7f5fe94f76bbc2747a446b00db~tplv-k3u1fbpfcp-watermark.image?) 实际效果是在每张子表中并行地执行一次分页查询，然后再将数据进行聚合之后实现分页性能问题： Sharding-JDBC采用流式处理 + 归并排序的方式来避免内存的过量占用，查询偏移量过大的分页会导致数据库获取数据性能低下 #### 2、join语句如何处理通过分片键进行关联的join语句可以直接复用 ![Image [15].png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/2cba5ea85faf48e4b77cbfae86985940~tplv-k3u1fbpfcp-watermark.image?) # 五、分表之后的主键如何处理主键不能再使用原来简单的数据库自增，sharding-jdbc内置两种方案UUID和snowflake，UUID无序不适合做主键，有序的逐渐在解决一些大偏移量分页查询慢的问题的场景更有帮助，还可以使用固定步长实现自增，但是不利于后面增加分表的数量 ##### snowflake snowflake是twitter的开源方案：前面一篇笔记专门介绍了[分布式ID用snowflake生成的方案和代码](https://juejin.cn/post/7036334630881984520)，这里就不再写了的方案和代码，这里就不再写了 # 六、分表替换原来的单表的上线方案 ## 1、老表数据刷到子表为了维护主键的自增性，建议使用单线程的方式处理 ## 2、数据双写系统稳定之前先建议数据双写，老表和字表建议都先写进去，系统稳定无误后可放弃原来的老表【参考】【1】https://blog.csdn.net/xiaojin21cen/article/details/105711180 【2】https://shardingsphere.apache.org/document/legacy/2.x/cn/02-guide/subquery/