数据库分片策略设计与实践

# 数据库分片策略设计与实践 ## 何时需要分片 当单表数据量超过 5000 万行、单库容量超过 500GB 或单机写入 QPS 超过上限时，就需要考虑分片方案。 ## 分片策略 ### 水平分片 ``` orders 表（10亿行） -> orders_0 (user_id % 4 == 0) -> orders_1 (user_id % 4 == 1) -> orders_2 (user_id % 4 == 2) -> orders_3 (user_id % 4 == 3) ``` ### 分片键选择原则 1. 高基数（cardinality）：保证数据分布均匀 2. 查询频率高：大多数查询包含分片键 3. 稳定不变：分片键值不应频繁变化 ## ShardingSphere 配置 ```yaml # ShardingSphere-JDBC 配置 dataSources: ds_0: url: jdbc:mysql://10.0.1.10:3306/orders_db_0 username: root password: secret ds_1: url: jdbc:mysql://10.0.1.11:3306/orders_db_1 username: root password: secret rules: - !SHARDING tables: orders: actualDataNodes: ds_${0..1}.orders_${0..3} databaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: db_mod tableStrategy: standard: shardingColumn: user_id shardingAlgorithmName: table_mod keyGenerateStrategy: column: order_id keyGeneratorName: snowflake shardingAlgorithms: db_mod: type: MOD props: sharding-count: 2 table_mod: type: MOD props: sharding-count: 4 ``` ## 分布式 ID 生成 ```java public class SnowflakeIdGenerator { private final long epoch = 1704067200000L; // 2024-01-01 private final long workerIdBits = 10L; private final long sequenceBits = 12L; private long workerId; private long sequence = 0L; private long lastTimestamp = -1L; public synchronized long nextId() { long timestamp = System.currentTimeMillis(); if (timestamp == lastTimestamp) { sequence = (sequence + 1) & ((1L << sequenceBits) - 1); if (sequence == 0) { timestamp = waitNextMillis(timestamp); } } else { sequence = 0L; } lastTimestamp = timestamp; return ((timestamp - epoch) << (workerIdBits + sequenceBits)) | (workerId << sequenceBits) | sequence; } } ``` ## 跨分片查询 跨分片的 JOIN、排序、聚合是最大挑战。常见应对策略： - 冗余数据：将高频关联字段冗余到分片表 - 全局表：配置、字典等小表在所有分片中保持完整副本 - 应用层聚合：在应用侧合并多分片查询结果 分片不是银弹，引入后运维复杂度大增。能通过读写分离、缓存、归档等手段解决的问题，尽量避免过早分片。