数据库分库分表后的跨分片查询全局索引与二级索引方案一、按用户 ID 分表了但运营要按手机号查用户怎么办分库分表的标准做法是选择一个分片键sharding key所有数据按这个 key 路由到不同的物理表上。比如按用户 ID 取模user_id % 64决定数据落在哪张表。查询时带上用户 ID中间件直接定位到唯一的物理表。一切看起来都很美好——直到有一天运营同学说帮我查一下手机号 138**** 对应的用户是谁。 问题来了手机号不是分片键系统不知道这个用户在 64 张表的哪一张里。这就是分库分表后的跨分片查询问题。分片键让你对一条数据的快速定位但也限制了能从什么维度查询。凡是查询条件里不包含分片键的请求都需要扫描所有分片——这在几十张表时意味着几十次数据库查询和网络往返延迟完全无法接受。flowchart TD A[查询请求按手机号查用户] -- B{是否有全局索引?} B --|有| C[查询全局索引表] C -- D{手机号 → 分片ID映射} D -- E[根据映射定位到具体分片] E -- F[查询目标分片返回结果] B --|无| G[广播查询遍历所有 64 张分片表] G -- H[合并各分片返回的结果] H -- F subgraph 全局索引维护 I[用户注册/更新] -- J[写入分片表 同步写入索引表] J -- K{同步方式} K --|同步双写| L[强一致性能有代价] K --|异步更新| M[最终一致可能有延迟] end二、全局索引表用冗余数据换查询效率全局索引表的思路很直观额外维护一张表只存非分片键 → 分片键的映射关系。比如对于手机号查用户的需求建立一张user_phone_index表字段为phone和user_id。当用户注册时除了在主表写入完整数据还在索引表中写入一条映射记录。后续按手机号查询时先在索引表中找到对应的user_id再根据user_id路由到正确的分片表。这个方案有两个关键问题需要解决。索引表放在哪个数据库可以单独建一个索引库不参与分片。好处是索引表本身的访问不受分片规则的约束。代价是一个额外的数据库实例需要维护。也可以放在用户分片中——比如手机号按同样的取模规则分片但这个规则就和手机号的分布无关了可能需要额外的映射。索引数据的一致性问题。用户注册时主表和索引表的写入不在同一个分片上不可能用一个本地事务包住。如果主表写入成功、索引表写入失败或反过来就出现了不一致。这种不一致会直接导致查不到数据。解决方案有两种。一种是同步双写加事务消息——注册时先在主分片写用户数据然后发一条事务消息由消费者异步写入索引表。另一种是容忍短暂的不一致通过定时对账任务扫描分片表 → 和索引表做全量对比来修复。两种方案的选择取决于业务对一致性的容忍度。/** * 全局索引注册服务 * * 设计要点 * 1. 主表写入 索引表写入通过事务消息保证最终一致 * 2. 索引查询先查索引表找分片 key再查主分片 * 3. 索引表缓存热点手机号映射关系缓存在 Redis 中 */ Service public class UserRegisterWithGlobalIndex { Resource private UserShardingService shardingService; Resource private TransactionMQProducer producer; Resource private RedisTemplateString, String redisCache; /** * 用户注册双写 索引更新 * * 流程 * 1. 在正确的分片表写入用户数据 * 2. 发事务消息异步在索引表中写入 phone → user_id 映射 * 3. 同步更新 Redis 缓存提高后续查询效率 */ public void register(User user) { // 步骤1写入分片表 // 根据 user_id 确定目标分片 String shardKey shardingService.getShard(user.getUserId()); shardingService.insertToShard(shardKey, user); // 步骤2发送索引更新的事务消息 // 消息体包含 phone 和 user_id消费者负责写入索引表 IndexMessage indexMsg new IndexMessage( USER_PHONE, // 索引类型 user.getPhone(), // 查询键 user.getUserId() // 分片键 ); producer.sendMessageInTransaction( new Message(INDEX_UPDATE_TOPIC, JSON.toJSONBytes(indexMsg)), user.getUserId() ); // 步骤3同步更新 Redis 缓存 // 缓存设置为 1 小时过期降低索引表的查询压力 // 注意这里缓存的是 phone → shard_key 的映射 // 而不是 phone → user_id减少一次路由计算 redisCache.opsForValue().set( idx:phone: user.getPhone(), shardKey, 1, TimeUnit.HOURS ); } /** * 按手机号查询用户 * * 查询链路Redis 缓存 → 索引表 → 广播查询兜底 */ public User findByPhone(String phone) { // 第一优先查 Redis 缓存 String shardKey redisCache.opsForValue() .get(idx:phone: phone); if (shardKey ! null) { return shardingService.getFromShard(shardKey, phone); } // 第二优先查全局索引表 Long userId indexTableService.queryUserIdByPhone(phone); if (userId ! null) { shardKey shardingService.getShard(userId); // 回填 Redis 缓存 redisCache.opsForValue().set( idx:phone: phone, shardKey, 1, TimeUnit.HOURS ); return shardingService.getFromShard(shardKey, phone); } // 第三兜底广播查询最慢只作为数据修复后的补偿 // 这里加了流量限制防止广播查询打挂数据库 return broadcastSearch(phone); } }三、二级索引与 ES 异构同步全局索引表的方式在索引维度较少时好用但如果业务有多个非分片键的查询需求按手机号查、按邮箱查、按昵称模糊搜索每个维度建一张索引表的工程量和维护成本都不低。更工程化的方案是将数据异构同步到 Elasticsearch 等搜索引擎中。分片表的数据通过 Binlog 监听如 Canal同步到 ESES 中建立倒排索引后任意字段的查询都能在毫秒级完成。这个方案的好处是不需要为每个查询维度建索引表查询能力全由 ES 提供。代价是引入了一个额外的数据同步链路和一个 ES 集群的运维负担。Binlog 同步的延迟一般在毫秒到秒级属于最终一致性。对于绝大多数业务场景包括按手机号查用户这个延迟完全可接受。四、跨分片查询的边界代价跨分片查询没有完美方案只有权衡方案。全局索引表在查询维度少时性价比高但每个新维度都增加维护成本。ES 异构同步在查询维度多时是最佳选择但引入的中间件依赖也不容忽视。广播查询作为兜底手段可以用在低频查询或按 ID 批量导出的场景但不能用于高频在线查询。另一个隐含的代价是数据迁移。当分片规则改变时如从 64 分片扩到 128 分片索引表的数据也需要重新映射——这是一次全量扫描 重新写入的过程期间数据一致性是一个需要谨慎处理的问题。五、总结分库分表解决了大数据量下的写入性能和存储容量问题却引入了跨分片查询的新挑战。全局索引表用冗余映射换查询效率适合查询维度少、一致性要求高的场景ES 异构同步把查询能力外移给了搜索引擎适合查询维度多、允许最终一致性的场景。无论选哪个方案分片规则变更时的数据迁移和索引重建都是需要提前考虑的不可忽视的成本。
数据库分库分表后的跨分片查询全局索引与二级索引方案一、按用户 ID 分表了但运营要按手机号查用户怎么办分库分表的标准做法是选择一个分片键sharding key所有数据按这个 key 路由到不同的物理表上。比如按用户 ID 取模user_id % 64决定数据落在哪张表。查询时带上用户 ID中间件直接定位到唯一的物理表。一切看起来都很美好——直到有一天运营同学说帮我查一下手机号 138**** 对应的用户是谁。 问题来了手机号不是分片键系统不知道这个用户在 64 张表的哪一张里。这就是分库分表后的跨分片查询问题。分片键让你对一条数据的快速定位但也限制了能从什么维度查询。凡是查询条件里不包含分片键的请求都需要扫描所有分片——这在几十张表时意味着几十次数据库查询和网络往返延迟完全无法接受。flowchart TD A[查询请求按手机号查用户] -- B{是否有全局索引?} B --|有| C[查询全局索引表] C -- D{手机号 → 分片ID映射} D -- E[根据映射定位到具体分片] E -- F[查询目标分片返回结果] B --|无| G[广播查询遍历所有 64 张分片表] G -- H[合并各分片返回的结果] H -- F subgraph 全局索引维护 I[用户注册/更新] -- J[写入分片表 同步写入索引表] J -- K{同步方式} K --|同步双写| L[强一致性能有代价] K --|异步更新| M[最终一致可能有延迟] end二、全局索引表用冗余数据换查询效率全局索引表的思路很直观额外维护一张表只存非分片键 → 分片键的映射关系。比如对于手机号查用户的需求建立一张user_phone_index表字段为phone和user_id。当用户注册时除了在主表写入完整数据还在索引表中写入一条映射记录。后续按手机号查询时先在索引表中找到对应的user_id再根据user_id路由到正确的分片表。这个方案有两个关键问题需要解决。索引表放在哪个数据库可以单独建一个索引库不参与分片。好处是索引表本身的访问不受分片规则的约束。代价是一个额外的数据库实例需要维护。也可以放在用户分片中——比如手机号按同样的取模规则分片但这个规则就和手机号的分布无关了可能需要额外的映射。索引数据的一致性问题。用户注册时主表和索引表的写入不在同一个分片上不可能用一个本地事务包住。如果主表写入成功、索引表写入失败或反过来就出现了不一致。这种不一致会直接导致查不到数据。解决方案有两种。一种是同步双写加事务消息——注册时先在主分片写用户数据然后发一条事务消息由消费者异步写入索引表。另一种是容忍短暂的不一致通过定时对账任务扫描分片表 → 和索引表做全量对比来修复。两种方案的选择取决于业务对一致性的容忍度。/** * 全局索引注册服务 * * 设计要点 * 1. 主表写入 索引表写入通过事务消息保证最终一致 * 2. 索引查询先查索引表找分片 key再查主分片 * 3. 索引表缓存热点手机号映射关系缓存在 Redis 中 */ Service public class UserRegisterWithGlobalIndex { Resource private UserShardingService shardingService; Resource private TransactionMQProducer producer; Resource private RedisTemplateString, String redisCache; /** * 用户注册双写 索引更新 * * 流程 * 1. 在正确的分片表写入用户数据 * 2. 发事务消息异步在索引表中写入 phone → user_id 映射 * 3. 同步更新 Redis 缓存提高后续查询效率 */ public void register(User user) { // 步骤1写入分片表 // 根据 user_id 确定目标分片 String shardKey shardingService.getShard(user.getUserId()); shardingService.insertToShard(shardKey, user); // 步骤2发送索引更新的事务消息 // 消息体包含 phone 和 user_id消费者负责写入索引表 IndexMessage indexMsg new IndexMessage( USER_PHONE, // 索引类型 user.getPhone(), // 查询键 user.getUserId() // 分片键 ); producer.sendMessageInTransaction( new Message(INDEX_UPDATE_TOPIC, JSON.toJSONBytes(indexMsg)), user.getUserId() ); // 步骤3同步更新 Redis 缓存 // 缓存设置为 1 小时过期降低索引表的查询压力 // 注意这里缓存的是 phone → shard_key 的映射 // 而不是 phone → user_id减少一次路由计算 redisCache.opsForValue().set( idx:phone: user.getPhone(), shardKey, 1, TimeUnit.HOURS ); } /** * 按手机号查询用户 * * 查询链路Redis 缓存 → 索引表 → 广播查询兜底 */ public User findByPhone(String phone) { // 第一优先查 Redis 缓存 String shardKey redisCache.opsForValue() .get(idx:phone: phone); if (shardKey ! null) { return shardingService.getFromShard(shardKey, phone); } // 第二优先查全局索引表 Long userId indexTableService.queryUserIdByPhone(phone); if (userId ! null) { shardKey shardingService.getShard(userId); // 回填 Redis 缓存 redisCache.opsForValue().set( idx:phone: phone, shardKey, 1, TimeUnit.HOURS ); return shardingService.getFromShard(shardKey, phone); } // 第三兜底广播查询最慢只作为数据修复后的补偿 // 这里加了流量限制防止广播查询打挂数据库 return broadcastSearch(phone); } }三、二级索引与 ES 异构同步全局索引表的方式在索引维度较少时好用但如果业务有多个非分片键的查询需求按手机号查、按邮箱查、按昵称模糊搜索每个维度建一张索引表的工程量和维护成本都不低。更工程化的方案是将数据异构同步到 Elasticsearch 等搜索引擎中。分片表的数据通过 Binlog 监听如 Canal同步到 ESES 中建立倒排索引后任意字段的查询都能在毫秒级完成。这个方案的好处是不需要为每个查询维度建索引表查询能力全由 ES 提供。代价是引入了一个额外的数据同步链路和一个 ES 集群的运维负担。Binlog 同步的延迟一般在毫秒到秒级属于最终一致性。对于绝大多数业务场景包括按手机号查用户这个延迟完全可接受。四、跨分片查询的边界代价跨分片查询没有完美方案只有权衡方案。全局索引表在查询维度少时性价比高但每个新维度都增加维护成本。ES 异构同步在查询维度多时是最佳选择但引入的中间件依赖也不容忽视。广播查询作为兜底手段可以用在低频查询或按 ID 批量导出的场景但不能用于高频在线查询。另一个隐含的代价是数据迁移。当分片规则改变时如从 64 分片扩到 128 分片索引表的数据也需要重新映射——这是一次全量扫描 重新写入的过程期间数据一致性是一个需要谨慎处理的问题。五、总结分库分表解决了大数据量下的写入性能和存储容量问题却引入了跨分片查询的新挑战。全局索引表用冗余映射换查询效率适合查询维度少、一致性要求高的场景ES 异构同步把查询能力外移给了搜索引擎适合查询维度多、允许最终一致性的场景。无论选哪个方案分片规则变更时的数据迁移和索引重建都是需要提前考虑的不可忽视的成本。
