数据库分库分表：策略设计与实现

数据库分库分表策略设计与实现核心概念随着业务增长单库单表会成为性能瓶颈。分库分表是一种水平扩展方案通过将数据分散到多个数据库或表中提高系统的吞吐量和可用性。分库分表策略1. 垂直分库// 垂直分库按业务模块划分 // 用户库 Configuration public class UserDataSourceConfig { Bean ConfigurationProperties(spring.datasource.user) public DataSource userDataSource() { return DataSourceBuilder.create().build(); } Bean public JdbcTemplate userJdbcTemplate(Qualifier(userDataSource) DataSource dataSource) { return new JdbcTemplate(dataSource); } } // 订单库 Configuration public class OrderDataSourceConfig { Bean ConfigurationProperties(spring.datasource.order) public DataSource orderDataSource() { return DataSourceBuilder.create().build(); } Bean public JdbcTemplate orderJdbcTemplate(Qualifier(orderDataSource) DataSource dataSource) { return new JdbcTemplate(dataSource); } } // 多数据源配置 Configuration public class MultiDataSourceConfig { Bean public DataSource routingDataSource( Qualifier(userDataSource) DataSource userDataSource, Qualifier(orderDataSource) DataSource orderDataSource) { MapObject, Object dataSources new HashMap(); dataSources.put(user, userDataSource); dataSources.put(order, orderDataSource); AbstractRoutingDataSource routingDataSource new AbstractRoutingDataSource(); routingDataSource.setTargetDataSources(dataSources); routingDataSource.setDefaultTargetDataSource(userDataSource); return routingDataSource; } }2. 水平分表// 水平分表按用户 ID 哈希分表 public class UserShardingStrategy { private static final int TABLE_COUNT 16; public static String getTableName(Long userId) { int tableIndex userId.hashCode() % TABLE_COUNT; return user_ String.format(%02d, tableIndex); } public static String getTableName(String userId) { int hash userId.hashCode(); int tableIndex Math.abs(hash) % TABLE_COUNT; return user_ String.format(%02d, tableIndex); } } // 分表查询服务 Service public class ShardedUserService { private final JdbcTemplate jdbcTemplate; public ShardedUserService(JdbcTemplate jdbcTemplate) { this.jdbcTemplate jdbcTemplate; } public User findById(Long userId) { String tableName UserShardingStrategy.getTableName(userId); String sql SELECT * FROM tableName WHERE id ?; return jdbcTemplate.queryForObject(sql, new Object[]{userId}, new UserRowMapper()); } public void save(User user) { String tableName UserShardingStrategy.getTableName(user.getId()); String sql INSERT INTO tableName (id, name, email) VALUES (?, ?, ?); jdbcTemplate.update(sql, user.getId(), user.getName(), user.getEmail()); } }3. 范围分片// 范围分片按时间范围划分 public class OrderRangeSharding { public static String getTableName(LocalDateTime orderTime) { int year orderTime.getYear(); int month orderTime.getMonthValue(); return String.format(order_%d_%02d, year, month); } public static ListString getTableNames(LocalDateTime startTime, LocalDateTime endTime) { ListString tableNames new ArrayList(); LocalDateTime current startTime; while (!current.isAfter(endTime)) { tableNames.add(getTableName(current)); current current.plusMonths(1); } return tableNames; } } // 范围分片查询 Service public class OrderRangeQueryService { private final JdbcTemplate jdbcTemplate; public OrderRangeQueryService(JdbcTemplate jdbcTemplate) { this.jdbcTemplate jdbcTemplate; } public ListOrder findByTimeRange(LocalDateTime startTime, LocalDateTime endTime) { ListString tableNames OrderRangeSharding.getTableNames(startTime, endTime); ListOrder orders new ArrayList(); for (String tableName : tableNames) { String sql SELECT * FROM tableName WHERE order_time ? AND order_time ?; ListOrder result jdbcTemplate.query(sql, new Object[]{startTime, endTime}, new OrderRowMapper()); orders.addAll(result); } return orders.stream() .sorted(Comparator.comparing(Order::getOrderTime)) .collect(Collectors.toList()); } }4. 一致性哈希// 一致性哈希分片 public class ConsistentHashSharding { private final TreeMapLong, String hashRing new TreeMap(); private final int virtualNodes 100; public ConsistentHashSharding(ListString nodes) { for (String node : nodes) { for (int i 0; i virtualNodes; i) { long hash hash(node _ i); hashRing.put(hash, node); } } } private long hash(String key) { return Math.abs(key.hashCode()); } public String getNode(String key) { if (hashRing.isEmpty()) { throw new IllegalStateException(No nodes available); } long hash hash(key); Map.EntryLong, String entry hashRing.ceilingEntry(hash); if (entry null) { entry hashRing.firstEntry(); } return entry.getValue(); } public String getTableName(String key) { String node getNode(key); return table_ node; } } // 使用一致性哈希 Service public class ConsistentHashService { private final ConsistentHashSharding sharding; public ConsistentHashService() { ListString nodes Arrays.asList(node01, node02, node03, node04); this.sharding new ConsistentHashSharding(nodes); } public String getTargetTable(Long userId) { return sharding.getTableName(String.valueOf(userId)); } }ShardingSphere 集成// ShardingSphere 配置 Configuration public class ShardingSphereConfig { Bean public DataSource dataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig new ShardingRuleConfiguration(); // 添加数据源 MapString, DataSource dataSourceMap new HashMap(); dataSourceMap.put(ds0, createDataSource(jdbc:mysql://localhost:3306/ds0)); dataSourceMap.put(ds1, createDataSource(jdbc:mysql://localhost:3306/ds1)); // 配置分库策略 shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig( new StandardShardingStrategyConfiguration( user_id, new ModuloShardingAlgorithm() ) ); // 配置分表策略 ShardingTableRuleConfiguration tableRuleConfig new ShardingTableRuleConfiguration(t_order); tableRuleConfig.setActualDataNodes(ds${0..1}.t_order_${0..3}); tableRuleConfig.setTableShardingStrategyConfig( new StandardShardingStrategyConfiguration( order_id, new ModuloShardingAlgorithm() ) ); shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig); return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties()); } private DataSource createDataSource(String url) { HikariDataSource dataSource new HikariDataSource(); dataSource.setJdbcUrl(url); dataSource.setUsername(root); dataSource.setPassword(password); return dataSource; } } // 自定义分片算法 public class CustomShardingAlgorithm implements StandardShardingAlgorithmLong { Override public String doSharding(CollectionString availableTargetNames, ShardingValueLong shardingValue) { Long value shardingValue.getValue(); int index (int) (value % availableTargetNames.size()); return availableTargetNames.stream() .sorted() .skip(index) .findFirst() .orElseThrow(() - new IllegalArgumentException(No available targets)); } Override public void init() { } Override public String getType() { return CUSTOM; } }分布式 ID 生成// 雪花算法 ID 生成器 Component public class SnowflakeIdGenerator { private final long epoch 1609459200000L; // 2021-01-01 00:00:00 private final long workerIdBits 5L; private final long datacenterIdBits 5L; private final long sequenceBits 12L; private final long maxWorkerId ~(-1L workerIdBits); private final long maxDatacenterId ~(-1L datacenterIdBits); private final long workerIdShift sequenceBits; private final long datacenterIdShift sequenceBits workerIdBits; private final long timestampLeftShift sequenceBits workerIdBits datacenterIdBits; private final long sequenceMask ~(-1L sequenceBits); private long workerId; private long datacenterId; private long sequence 0L; private long lastTimestamp -1L; public SnowflakeIdGenerator() { this.workerId getWorkerId(); this.datacenterId getDatacenterId(); if (workerId maxWorkerId || workerId 0) { throw new IllegalArgumentException(Worker ID out of range); } if (datacenterId maxDatacenterId || datacenterId 0) { throw new IllegalArgumentException(Datacenter ID out of range); } } public synchronized long nextId() { long timestamp timeGen(); if (timestamp lastTimestamp) { throw new RuntimeException(Clock moved backwards); } if (timestamp lastTimestamp) { sequence (sequence 1) sequenceMask; if (sequence 0) { timestamp tilNextMillis(lastTimestamp); } } else { sequence 0L; } lastTimestamp timestamp; return ((timestamp - epoch) timestampLeftShift) | (datacenterId datacenterIdShift) | (workerId workerIdShift) | sequence; } private long timeGen() { return System.currentTimeMillis(); } private long tilNextMillis(long lastTimestamp) { long timestamp timeGen(); while (timestamp lastTimestamp) { timestamp timeGen(); } return timestamp; } private long getWorkerId() { // 从环境变量或配置中心获取 worker ID String workerIdStr System.getenv(WORKER_ID); return workerIdStr ! null ? Long.parseLong(workerIdStr) : 1L; } private long getDatacenterId() { // 从环境变量或配置中心获取 datacenter ID String datacenterIdStr System.getenv(DATACENTER_ID); return datacenterIdStr ! null ? Long.parseLong(datacenterIdStr) : 1L; } } // 使用分布式 ID Service public class OrderService { private final SnowflakeIdGenerator idGenerator; private final OrderRepository orderRepository; public OrderService(SnowflakeIdGenerator idGenerator, OrderRepository orderRepository) { this.idGenerator idGenerator; this.orderRepository orderRepository; } public Order createOrder(OrderCreateRequest request) { Order order new Order(); order.setId(idGenerator.nextId()); order.setUserId(request.getUserId()); order.setProductId(request.getProductId()); order.setQuantity(request.getQuantity()); order.setStatus(PENDING); return orderRepository.save(order); } }跨分片查询// 跨分片查询服务 Service public class CrossShardQueryService { private final JdbcTemplate jdbcTemplate; public CrossShardQueryService(JdbcTemplate jdbcTemplate) { this.jdbcTemplate jdbcTemplate; } public ListUserOrderStats getUserOrderStats(Long userId) { String sql SELECT u.id as user_id, u.name as user_name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM user_00 u LEFT JOIN order_00 o ON u.id o.user_id WHERE u.id ? UNION ALL SELECT u.id as user_id, u.name as user_name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM user_01 u LEFT JOIN order_01 o ON u.id o.user_id WHERE u.id ? UNION ALL -- ... 其他分片 ; return jdbcTemplate.query(sql, new Object[]{userId, userId}, new UserOrderStatsRowMapper()); } public ListUserOrderStats aggregateResults(ListUserOrderStats results) { return results.stream() .collect(Collectors.groupingBy( UserOrderStats::getUserId, Collectors.collectingAndThen( Collectors.toList(), list - { UserOrderStats aggregated new UserOrderStats(); aggregated.setUserId(list.get(0).getUserId()); aggregated.setUserName(list.get(0).getUserName()); aggregated.setOrderCount(list.stream().mapToLong(UserOrderStats::getOrderCount).sum()); aggregated.setTotalAmount(list.stream().mapToBigDecimal(UserOrderStats::getTotalAmount).sum()); return aggregated; } ) )) .values() .stream() .collect(Collectors.toList()); } }数据迁移// 数据迁移服务 Service public class DataMigrationService { private final JdbcTemplate sourceJdbcTemplate; private final JdbcTemplate targetJdbcTemplate; public DataMigrationService( Qualifier(sourceJdbcTemplate) JdbcTemplate sourceJdbcTemplate, Qualifier(targetJdbcTemplate) JdbcTemplate targetJdbcTemplate) { this.sourceJdbcTemplate sourceJdbcTemplate; this.targetJdbcTemplate targetJdbcTemplate; } public void migrateUsers(int batchSize) { String sourceSql SELECT * FROM user ORDER BY id LIMIT ?, ?; int offset 0; while (true) { ListUser users sourceJdbcTemplate.query( sourceSql, new Object[]{offset, batchSize}, new UserRowMapper() ); if (users.isEmpty()) { break; } for (User user : users) { String targetTable UserShardingStrategy.getTableName(user.getId()); String insertSql INSERT INTO targetTable (id, name, email, created_at) VALUES (?, ?, ?, ?); targetJdbcTemplate.update( insertSql, user.getId(), user.getName(), user.getEmail(), user.getCreatedAt() ); } offset batchSize; System.out.println(Migrated offset users); } } }最佳实践选择合适的分片键选择查询频率高、分布均匀的字段作为分片键避免跨分片查询尽量在单分片内完成查询使用分布式事务使用分布式事务协调器处理跨分片事务监控分片状态实时监控各分片的负载和健康状态预留扩展空间设计时考虑未来的扩展需求数据预热在上线前进行数据迁移和预热回滚方案准备好数据回滚方案实际应用场景电商订单系统按时间或用户 ID 分片社交平台按用户 ID 分片日志系统按时间分片数据分析按数据类型分片总结分库分表是解决大数据量场景下数据库性能瓶颈的有效方案。通过合理选择分片策略可以显著提高系统的吞吐量和可用性。在实际应用中需要根据业务特点选择合适的分片方案并做好数据迁移和监控工作。别叫我大神叫我 Alex 就好。这其实可以更优雅一点合理的分片策略让数据库扩展变得更加灵活和高效。