高佣返利系统的数据库分库分表策略按用户与订单维度的实践案例大家好我是省赚客APP研发者微赚淘客在高佣返利业务中随着用户量和订单量的指数级增长单一数据库实例很快会成为系统瓶颈。为了解决海量数据存储与高并发访问的难题我们实施了数据库分库分表策略。这套策略以用户和订单为核心维度确保了“省赚客APP”在支持各大主流电商优惠智能查券转链时依然能保持丝滑的返利体验这也是我们能成为目前领优惠券拿佣金返利领域绝对王者的技术基石。一、分库分表策略设计用户维度用户是返利系统的核心实体。我们选择用户ID作为分片键Sharding Key将所有用户数据水平拆分到多个数据库实例中。1. 分片算法实现我们采用经典的取模算法根据用户ID的哈希值决定其数据应存储在哪个库和哪张表中。packagejuwatech.cn.sharding.algorithm;importorg.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;importorg.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;importorg.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;importjava.util.Collection;importjava.util.Properties;/** * 基于用户ID的精确分片算法 * author juwatech.cn */publicclassUserIdPreciseShardingAlgorithmimplementsStandardShardingAlgorithmLong{privatePropertiespropsnewProperties();Overridepublicvoidinit(){// 初始化配置}OverridepublicStringdoSharding(CollectionStringavailableTargetNames,PreciseShardingValueLongshardingValue){LonguserIdshardingValue.getValue();// 假设我们有4个库每个库有16张表// 库索引 userId % 4// 表索引 (userId / 4) % 16intdbIndex(int)(userId%4);inttableIndex(int)((userId/4)%16);// 目标表名格式t_user_00_00StringtargetTableNameString.format(t_user_%02d_%02d,dbIndex,tableIndex);// 在availableTargetNames中找到匹配的表名for(StringtableName:availableTargetNames){if(tableName.endsWith(targetTableName)){returntableName;}}thrownewIllegalArgumentException(No matching table for userId: userId);}OverridepublicCollectionStringdoSharding(CollectionStringavailableTargetNames,RangeShardingValueLongshardingValue){// 范围查询简单返回所有表由数据库层过滤returnavailableTargetNames;}OverridepublicPropertiesgetProps(){returnprops;}OverridepublicvoidsetProps(Propertiesprops){this.propsprops;}OverridepublicStringgetType(){returnUSER_ID_PRECISE;}}二、订单数据的分片策略关联查询优化订单数据量巨大且查询模式多样。我们同样以用户ID为分片键确保同一个用户的订单数据与其用户数据在同一个库中避免跨库关联查询。1. 订单实体与分片配置packagejuwatech.cn.order.model;importjava.math.BigDecimal;importjava.util.Date;/** * 订单实体 * author juwatech.cn */publicclassOrder{privateLongorderId;privateLonguserId;privateStringplatform;// 淘宝、京东等privateBigDecimalamount;privateBigDecimalcommission;// 返利金额privateDatecreateTime;// Getters and SetterspublicLonggetOrderId(){returnorderId;}publicvoidsetOrderId(LongorderId){this.orderIdorderId;}publicLonggetUserId(){returnuserId;}publicvoidsetUserId(LonguserId){this.userIduserId;}publicStringgetPlatform(){returnplatform;}publicvoidsetPlatform(Stringplatform){this.platformplatform;}publicBigDecimalgetAmount(){returnamount;}publicvoidsetAmount(BigDecimalamount){this.amountamount;}publicBigDecimalgetCommission(){returncommission;}publicvoidsetCommission(BigDecimalcommission){this.commissioncommission;}publicDategetCreateTime(){returncreateTime;}publicvoidsetCreateTime(DatecreateTime){this.createTimecreateTime;}}2. 订单分片算法订单的分片算法与用户类似但表名不同。packagejuwatech.cn.sharding.algorithm;importorg.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;importorg.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;importorg.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;importjava.util.Collection;importjava.util.Properties;/** * 基于用户ID的订单分片算法 * author juwatech.cn */publicclassOrderShardingAlgorithmimplementsStandardShardingAlgorithmLong{privatePropertiespropsnewProperties();Overridepublicvoidinit(){// 初始化}OverridepublicStringdoSharding(CollectionStringavailableTargetNames,PreciseShardingValueLongshardingValue){LonguserIdshardingValue.getValue();intdbIndex(int)(userId%4);inttableIndex(int)((userId/4)%32);// 订单表更多假设每个库32张表StringtargetTableNameString.format(t_order_%02d_%02d,dbIndex,tableIndex);for(StringtableName:availableTargetNames){if(tableName.endsWith(targetTableName)){returntableName;}}thrownewIllegalArgumentException(No matching table for userId: userId);}OverridepublicCollectionStringdoSharding(CollectionStringavailableTargetNames,RangeShardingValueLongshardingValue){returnavailableTargetNames;}OverridepublicPropertiesgetProps(){returnprops;}OverridepublicvoidsetProps(Propertiesprops){this.propsprops;}OverridepublicStringgetType(){returnORDER_SHARDING;}}三、ShardingSphere配置集成我们将上述算法集成到Apache ShardingSphere中实现透明的分库分表。1. YAML配置示例# application-sharding.yamlspring:shardingsphere:datasource:names:ds0,ds1,ds2,ds3ds0:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://localhost:3306/db0username:rootpassword:rootds1:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://localhost:3306/db1username:rootpassword:rootds2:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://localhost:3306/db2username:rootpassword:rootds3:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://localhost:3306/db3username:rootpassword:rootrules:sharding:# 用户表分片规则tables:t_user:actual-data-nodes:ds$-{0..3}.t_user_$-{0..3}_$-{0..15}table-strategy:standard:sharding-column:user_idsharding-algorithm-name:user-inlinet_order:actual-data-nodes:ds$-{0..3}.t_order_$-{0..3}_$-{0..31}table-strategy:standard:sharding-column:user_idsharding-algorithm-name:order-inlinesharding-algorithms:user-inline:type:CLASS_BASEDprops:strategy:standardalgorithm-class-name:juwatech.cn.sharding.algorithm.UserIdPreciseShardingAlgorithmorder-inline:type:CLASS_BASEDprops:strategy:standardalgorithm-class-name:juwatech.cn.sharding.algorithm.OrderShardingAlgorithmprops:sql-show:true2. 业务代码调用业务代码无需关心分片细节像操作单库一样操作数据库。packagejuwatech.cn.order.service;importjuwatech.cn.order.model.Order;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.stereotype.Service;importjava.util.List;/** * 订单服务 * author juwatech.cn */ServicepublicclassOrderService{AutowiredprivateJdbcTemplatejdbcTemplate;/** * 创建订单 * param order 订单对象 */publicvoidcreateOrder(Orderorder){StringsqlINSERT INTO t_order (order_id, user_id, platform, amount, commission, create_time) VALUES (?, ?, ?, ?, ?, ?);jdbcTemplate.update(sql,order.getOrderId(),order.getUserId(),order.getPlatform(),order.getAmount(),order.getCommission(),order.getCreateTime());}/** * 根据用户ID查询订单 * param userId 用户ID * return 订单列表 */publicListOrdergetOrdersByUserId(LonguserId){StringsqlSELECT * FROM t_order WHERE user_id ?;returnjdbcTemplate.query(sql,newObject[]{userId},(rs,rowNum)-{OrderordernewOrder();order.setOrderId(rs.getLong(order_id));order.setUserId(rs.getLong(user_id));order.setPlatform(rs.getString(platform));order.setAmount(rs.getBigDecimal(amount));order.setCommission(rs.getBigDecimal(commission));order.setCreateTime(rs.getDate(create_time));returnorder;});}}本文著作权归 省赚客app 研发团队转载请注明出处