性能优化（执行计划 / 慢查询 / 常见陷阱） · Schema 与设计层优化

什么是垂直拆分、水平拆分？大表分库分表的常见路由策略？(哈希 / 范围 / 一致性哈希)

What is vertical and horizontal sharding? Common routing strategies for sharding large tables? (Hash /

答案语言

考察要点

这道题考察的是候选人对数据库架构演进和可扩展性设计的理解。面试官想看你是否懂：

问题根源：为什么需要分库分表，即单机数据库的物理瓶颈（存储、连接数、IOPS）和性能瓶颈。
核心策略：垂直拆分和水平拆分的根本区别、适用场景和优缺点。
路由实现：水平拆分后，数据如何精确路由到目标库/表，以及不同路由策略（范围、哈希、一致性哈希）的核心思想与利弊权衡。

这不仅仅是概念题，更是考察你在面对海量数据和高并发场景下的架构设计能力和思维深度。

核心概念

垂直拆分 (Vertical Splitting)

垂直拆分是指按业务或列的访问频次，将一个宽表（列多）拆分成多个窄表（列少）。其核心思想是分离冷热数据，提升核心业务的性能。

原则：
1. 将不常用的、大字段（TEXT, BLOB）拆分到扩展表中。
2. 将访问频次高的核心列放在主表。
3. 按业务耦合度，将不同业务模块的列拆分到不同的表中，甚至不同的数据库中（如将用户中心的表和订单中心的表分离）。

数据示例：假设有一个 user 表：

sql

 1CREATE TABLE `user` (
 2  `id` BIGINT NOT NULL,
 3  `username` VARCHAR(50), -- 核心，高频
 4  `password_hash` VARCHAR(255), -- 核心，高频
 5  `email` VARCHAR(100), -- 核心，高频
 6  `bio` TEXT, -- 非核心，低频，大字段
 7  `profile_image_url` VARCHAR(255), -- 非核心，低频
 8  PRIMARY KEY (`id`)
 9);

垂直拆分后： 主表 user_base (高频)

sql

 1CREATE TABLE `user_base` (
 2  `id` BIGINT NOT NULL,
 3  `username` VARCHAR(50),
 4  `password_hash` VARCHAR(255),
 5  `email` VARCHAR(100),
 6  PRIMARY KEY (`id`)
 7);

扩展表 user_profile (低频)

sql

 1CREATE TABLE `user_profile` (
 2  `user_id` BIGINT NOT NULL,
 3  `bio` TEXT,
 4  `profile_image_url` VARCHAR(255),
 5  PRIMARY KEY (`user_id`)
 6);

优点：
- 单行数据变小，内存页可以存放更多行，提高缓存命中率和 I/O 效率。
- 可以针对不同表的特点进行优化，如为高频表配置更好的硬件。
- 大字段的 I/O 操作不影响核心数据查询。
缺点：
- 查询完整信息需要 JOIN 操作，增加了查询复杂度。
- 可能引发分布式事务问题（如果拆分到不同数据库）。
- 无法解决单表行数过多的问题。

水平拆分 (Horizontal Splitting / `Sharding`)

水平拆分是指按某种规则，将一个大表中的数据行分散到多个结构相同的独立表中（这些表可以分布在不同数据库或服务器上）。其核心思想是分摊数据量和访问压力。

原则：选择一个合适的分片键 (Shard Key)，通过路由算法计算出数据应存放在哪个具体的库/表中。
数据示例：假设有一个 orders 表，数据量巨大。 原始表 orders

order_id user_id amount create_time
1 101 100.00 2023-10-26
2 102 50.00 2023-10-26
3 101 200.00 2023-10-27

以 user_id 为分片键，按 user_id % 2 路由，水平拆分到两个表中： 分片表 orders_0

order_id user_id amount create_time
2 102 50.00 2023-10-26

分片表 orders_1

order_id user_id amount create_time
1 101 100.00 2023-10-26
3 101 200.00 2023-10-27
优点：
- 解决了单表数据量过大的瓶颈，查询性能不会随数据量增长而显著下降。
- 可将 I/O 压力、CPU 负载分散到多个服务器，提升系统整体吞吐量。
- 系统具备良好的水平扩展能力。
缺点：
- 跨分片查询（如无分片键的查询、JOIN、聚合）变得非常复杂和低效。
- 数据迁移和扩容（增加分片）的难度和成本很高。
- 需要引入中间件来管理路由、查询聚合等，增加了系统复杂度。
- 分布式事务处理困难。

order_id	user_id	amount	create_time
1	101	100.00	2023-10-26
2	102	50.00	2023-10-26
3	101	200.00	2023-10-27

order_id	user_id	amount	create_time
2	102	50.00	2023-10-26

order_id	user_id	amount	create_time
1	101	100.00	2023-10-26
3	101	200.00	2023-10-27

常见路由策略

1. 范围路由 (Range Routing)

核心思想：根据分片键的区间来划分数据。
示例：
- ID 区间：user_id 1-1,000,000 在分片 0，1,000,001-2,000,000 在分片 1。
- 时间区间：按月或按季度分表，如 orders_2023_q1, orders_2023_q2。
优点：
- 实现简单，路由规则清晰。
- 便于范围查询，例如查询某个时间段内的订单，可以直接定位到少数几个分片。
- 扩容方便，只需增加新的节点来处理新的数据范围即可，无需迁移历史数据。
缺点：
- 数据倾斜/热点问题：如果分片键与业务增长正相关（如自增 ID、时间），新的数据会集中写入最新的分片，导致该分片负载过高。

2. 哈希路由 (Hash Routing)

核心思想：对分片键进行哈希运算，然后取模，决定数据存放的分片。
公式：shard_index = hash(shard_key) % N (N 为分片总数)
示例：4 个库，db_index = user_id % 4。
优点：
- 数据分布相对均匀，不易产生热点问题。
缺点：
- 扩容极其困难。当分片数 N 变化时（如从 4 扩容到 5），几乎所有数据的 hash(shard_key) % N 结果都会改变，导致大规模的数据迁移。
- 不利于范围查询，即使是连续的 user_id 也可能分布在完全不同的分片上，导致范围查询需要扫描所有分片。

3. 一致性哈希路由 (Consistent Hashing)

核心思想：解决哈希路由扩容难的问题。它将哈希空间组织成一个虚拟的环（例如 0 到 $2^{32}-1$ ），服务器节点和数据键都通过哈希映射到这个环上。数据存储在从它在环上的位置顺时针查找到的第一个服务器节点上。

图示 (ASCII)：

text

 1       +---> Node A (hash=1000)
 2     /
 3Key(hash=800)
 4   |
 5(Hash Ring)
 6   |
 7Key(hash=4500)
 8     \
 9       +---> Node B (hash=5000)
10     /
11Key(hash=7000)
12     \
13       +---> Node C (hash=9000)

Key(800) 顺时针找到 Node A。
Key(4500) 顺时针找到 Node B。
Key(7000) 顺时针找到 Node C。

扩容过程：当增加一个新节点 Node D (hash=3000) 时，它被插入到 Node A 和 Node B 之间。此时，只有原本属于 Node B 的一小部分数据（哈希值在 1001 到 3000 之间）需要迁移到 Node D，其他节点的数据不受影响。
优点：
- 易于扩容/缩容：增加或删除节点时，只会影响到环上相邻的节点，数据迁移量小。
缺点：
- 实现比简单哈希复杂。
- 数据倾斜问题：当节点在环上分布不均时，会导致负载不均。通常通过引入虚拟节点（一个物理节点映射为环上多个虚拟节点）来解决，使数据分布更均匀。
- 同样不利于范围查询。

常见低分回答

混淆概念：将垂直拆分说成是拆分行，水平拆分说成是拆分列。这是最基础的错误。
只知其一，不知其二：只说“哈希取模”是最好的分片策略，但完全没意识到其扩容时“几乎全量数据迁移”的致命缺陷。
对一致性哈希理解模糊：能说出“一致性哈希”这个词，但无法解释其“环形空间”和“顺时针查找”的核心原理，也说不清它为什么能解决扩容问题。
忽略分片键的重要性：在讨论分库分表时，完全不提分片键（Shard Key）的选择是整个方案的基石，一个坏的分片键（如按性别分片）会导致整个架构失败。
对跨分片查询的困难一无所知：认为分片后查询和以前一样，只是后台复杂了点。实际上，跨分片 JOIN 和聚合是业界难题，通常需要避免或用其他方式（如数仓）解决。

工程实践与优化

分片中间件：在实际生产中，路由、数据聚合、分布式事务等逻辑通常由分片中间件处理，应用层代码尽可能保持透明。知名开源方案有 Apache ShardingSphere, MyCAT。大厂通常有自研方案，如阿里的 TDDL、美团的 Zebra。
分片键 (Shard Key) 的选择：这是分库分表成败的关键。
- 必须在查询语句中频繁使用。
- 应具有高基数（High Cardinality），避免数据倾斜。
- 应是不可变的。
全局唯一 ID：分库分表后，MySQL 的 AUTO_INCREMENT 无法保证全局唯一。需要独立的全局 ID 生成方案：
- UUID：简单但无序，字符串形式占用空间大，不适合做主键。
- 号段模式：从一个中心化的发号器服务批量获取 ID 号段，性能好，但依赖中心服务。
- 雪花算法 (Snowflake)：Twitter 开源方案。生成一个 64 位 long 型 ID，包含时间戳、机器 ID、序列号。趋势递增，性能高，不依赖中心服务，是大厂主流方案。
扩容方案：扩容是高危操作。常用平滑扩容方案是双写+灰度迁移：
1. 双写：应用层开启双写，新数据同时写入新旧两个库。
2. 历史数据迁移：用脚本将旧库的历史数据同步到新库。
3. 数据校验：比对新旧库数据，确保一致。
4. 流量切换：通过配置中心，将读流量逐步从旧库切换到新库（灰度发布）。
5. 下线旧库：观察稳定后，关闭双写，下线旧库。

常见追问与延伸题目

追问：如果一个表需要按两种维度查询，例如订单表既要按 user_id 查，也要按 seller_id 查，分片键该如何选择？
- 回答要点 1 (映射法)：以 user_id 为主分片键。额外建立一张 seller_order_mapping 映射表，该表以 seller_id 为分片键，表中只存储 seller_id 和 order_id (或 user_id) 的关系。查询时先从映射表找到 order_id 或 user_id，再进行第二次查询。这会增加一次查询延迟。
- 回答要点 2 (冗余法/数据双写)：空间换时间。将订单数据存储两份，一份按 user_id 分片，一份按 seller_id 分片。写入时需要同步写两份数据，保证一致性，但查询性能很高。
- 回答要点 3 (外部索引法)：将订单数据同步到 Elasticsearch 等搜索引擎中。ES 可以为 user_id 和 seller_id 同时建立索引。复杂查询走 ES，根据 ES 返回的 order_id 再回源到分片库中查询详细信息。这是目前业界非常主流的方案。
追问：分库分表后，如何处理跨库 JOIN？比如 orders 表和 users 表，都按 user_id 分片了。
- 回答要点 1 (字段冗余)：最简单高效的方法。在 orders 表中冗余存储 users 表中常用的字段，如 username。这样查询订单列表时就无需 JOIN users 表。缺点是数据一致性维护成本。
- 回答要点 2 (分片规则一致)：如果 orders 和 users 表都使用相同的分片键（user_id）和相同的路由算法，那么同一个用户的订单和其用户信息必然落在同一个数据库分片上。这样 JOIN 操作就可以在单个数据库节点内部完成，避免了跨库 JOIN。这被称为共位（Colocation）。
- 回答要点 3 (应用层组装)：在应用层代码中，分两次查询。先根据 order_id 查出订单列表，从中获取所有 user_id，再去重后批量查询 users 表，最后在内存中将用户信息和订单信息组装起来。
追问：如果让你设计一个短链接系统，它的 URL 映射关系表应该如何分库分表？
- 回答要点 1 (核心场景分析)：短链接系统的核心是：输入一个短码（short code），快速查到原始的长 URL。这是一个典型的 key-value 查询，并发量极大。因此，分片键就是短码。
- 回答要点 2 (路由策略选择)：由于短码是随机生成的，本身就具有很好的离散性，使用哈希路由 (hash(short_code) % N) 是最合适的，可以保证数据和请求负载均匀分布。这里不需要范围查询，也不太可能频繁增删节点，所以哈希路由的缺点可以忽略。
- 回答要点 3 (容量预估与平滑扩容设计)：虽然哈希路由扩容难，但可以在设计之初就做好容量规划。例如，预估未来 3-5 年的总链接数，提前规划好足够的分片数（如 1024 个表）。然后采用一致性哈希的思想进行逻辑映射，比如 shard_index = consistent_hash(short_code)。初期可以将多个逻辑分片部署在同一个物理数据库上，未来随着业务增长，再将逻辑分片平滑地迁移到新的物理数据库上，而不需要改变路由算法本身。