6.8.6 分区表与分表策略

分区表与分表策略用于控制单表规模、提升查询与维护效率，但需结合业务特征、访问模式与运维成本综合决策。分区是同一表内的逻辑拆分，适合范围查询、批量归档与冷热数据隔离；分表是拆成多张物理表，适合高并发与超大数据量场景。选择策略时应优先满足可维护性与可扩展性。

原理草图：分区与分表路径对比

分区表适用场景与类型
- 适用场景：按时间归档、清理历史数据、按区间过滤查询为主的业务。
- 常用分区类型：RANGE（按时间/区间）、LIST（按枚举值）、HASH/KEY（均匀分布）。
- 分区键设计：需与主要查询条件一致，避免分区裁剪失效；分区字段应为高选择性且稳定的业务字段（如时间、地区、业务线）。
- 约束与限制：分区表主键需包含分区键；跨分区唯一性限制存在约束；不适合频繁跨分区聚合、复杂Join。

分区表示例：RANGE按月分区

-- 1) 创建数据库与表
CREATE DATABASE IF NOT EXISTS ops;
USE ops;

CREATE TABLE orders (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 2) 插入示例数据
INSERT INTO orders VALUES
(1, 1001, 99.90, '2024-01-10 10:00:00'),
(2, 1002, 19.90, '2024-02-10 10:00:00'),
(3, 1003, 199.00, '2024-03-10 10:00:00');

-- 3) 使用EXPLAIN验证分区裁剪
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- 预期：partitions 仅包含 p202402

-- 4) 新增分区（每月维护）
ALTER TABLE orders
ADD PARTITION (PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')));

-- 5) 归档与清理历史分区
ALTER TABLE orders DROP PARTITION p202401;

分表策略与路由设计
- 垂直分表：按业务模块或字段热度拆分，减少宽表，提高缓存与IO效率。
- 水平分表：按用户ID、订单ID等进行哈希或范围路由；需要稳定的路由规则与一致性算法。
- 路由键选择：保证均匀分布，避免热点；推荐使用业务主键或复合路由键。
- 统一规范：表名后缀规则、分片数量与扩容策略需明确（如 user_0000~user_1023）。

水平分表示例：按user_id取模

-- 1) 创建分表（示例4张）
CREATE TABLE user_0000 (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  name VARCHAR(64),
  created_at DATETIME
) ENGINE=InnoDB;

CREATE TABLE user_0001 LIKE user_0000;
CREATE TABLE user_0002 LIKE user_0000;
CREATE TABLE user_0003 LIKE user_0000;

-- 2) 路由规则（应用侧）
-- shard = user_id % 4
-- table = user_000{shard}

-- 3) 插入示例（模拟应用路由）
INSERT INTO user_0001 VALUES (1, 1001, 'alice', NOW()); -- 1001 % 4 = 1
INSERT INTO user_0002 VALUES (2, 1002, 'bob', NOW());   -- 1002 % 4 = 2

分区与分表对比与选型原则
- 数据量中等且维护需求强：优先分区。
- 超大规模与高并发写入：优先分表或分库分表。
- 查询以范围为主：分区更优；查询以主键点查为主：分表更优。
- 能力成本：分表引入路由、事务一致性、跨表查询与聚合复杂度，运维复杂度更高。

扩容与迁移策略
- 分区扩展：提前规划未来分区；定期增加分区并归档历史分区。
- 分表扩容：采用一致性哈希或双写迁移；引入中间件（如ProxySQL）时需评估透明性与回滚方案。
- 数据迁移：采用分批迁移、校验与回放机制；保证业务可用性与数据一致性。

运维命令与监控示例

-- 查看分区信息
SELECT
  TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, TABLE_ROWS, DATA_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA='ops' AND TABLE_NAME='orders';

-- 查看表大小（分表）
SELECT
  TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH/1024/1024 AS data_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='ops' AND TABLE_NAME LIKE 'user_%'
ORDER BY data_mb DESC;

常见问题排错
- 问题1：分区裁剪不生效，EXPLAIN显示全分区扫描
排查点：查询条件是否包含分区键；是否在分区键上使用函数导致失效。
示例：
```sql
-- 错误示例：对分区键使用函数
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE DATE(created_at) = '2024-02-10';

-- 修正：使用范围条件
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE created_at >= '2024-02-10' AND created_at < '2024-02-11';
- 问题2：创建分区表报错“PRIMARY KEY must include all partitioning columns” 解决：主键包含分区键。sql
-- 修正主键
PRIMARY KEY (id, created_at)
```
- 问题3：分表热点导致某分表增长过快
解决：评估路由键；必要时引入二级哈希或一致性哈希并做迁移。

最佳实践
- 先做容量与访问模型评估，再决定分区或分表。
- 分区表优先使用RANGE按时间切分，配合分区裁剪提高性能。
- 分表需配合唯一ID生成策略（雪花ID等），并明确全局唯一性与去重规则。
- 建立分区/分表监控：分区增长、分表热点、慢查询分布与存储占用。

练习
1. 创建按月分区的日志表，插入三个月数据，使用 EXPLAIN PARTITIONS 验证裁剪效果。
2. 设计 8 张用户分表并写出路由函数，测试 1000 个用户的插入分布。
3. 模拟分区扩展：新增一个月分区并删除最早分区，验证查询仍正常。