SQL数据库分区表原理_rangehashlist分区实践

分区表通过数据分布逻辑和查询剪枝提升性能，含Range（按范围，如时间）、Hash（按哈希值，适合等值查询）、List（按离散值枚举）及混合分区（如Range+Hash二级分区）四种类型，各具适用场景与限制。

分区表是把一张大表按某种规则拆分成多个物理上独立的子表（分区），查询时数据库能根据条件只扫描相关分区，从而提升性能、简化维护。核心在于“数据分布逻辑”和“查询剪枝能力”，不是简单分表。

Range分区：按连续范围切分，适合时间/序号类字段

最常用，按列值的区间划分，比如按年份、月份或ID段。要求分区键必须是整型、日期或可比较类型，且每个分区范围不能重叠、必须覆盖全集（或留一个MAXVALUE兜底）。

• 创建示例（mysql 8.0+）：
  

  CREATE table orders (id BIGint, order_time dateTIME, amount DECIMAL(10,2))
   PARTITION BY RANGE (YEAR(order_time)) (
     PARTITION p2021 VALUES less THAN (2022),
     PARTITION p2022 VALUES LESS THAN (2023),
     PARTITION p2023 VALUES LESS THAN (2024),
     PARTITION p_future VALUES LESS THAN MAXVALUE
  );

• 查询如 WHERE order_time >= '2022-01-01' AND order_time ，优化器会自动只访问p2022分区；但 WHERE DATE(order_time) = '2022-06-15' 无法剪枝，因函数破坏了分区键的原始值结构。
• 新增分区需手动 ALTER TABLE ... ADD PARTITION，旧数据不会自动迁移，需配合 REORGANIZE 或导出导入。

Hash分区：按哈希值均匀分散，适合高并发写入场景

用分区键计算哈希（如MOD或内部哈希函数），映射到固定数量的分区。优势是数据分布较均衡，写入无热点；缺点是仅支持等值查询（= 或 IN）才能剪枝，范围查询会扫全部分区。

• 创建示例：
  

  CREATE TABLE users (uid BIGINT, name VARchar(50))
   PARTITION BY HASH(uid) PARTITIONS 8;

  
  系统将 uid 对 8 取模，结果 0~7 分别落入 8 个分区。

• 查询 WHERE uid = 123456 能精准定位单一分区；但 WHERE uid > 100000 会触发全分区扫描。
• 分区数一旦设定难以调整（改变会导致哈希结果错位），扩容需重建表或使用一致性哈希方案（部分数据库不原生支持）。

List分区：按离散值枚举分配，适合状态码、区域编码等有限集合

显式指定每组值归属哪个分区，比Range更灵活（值不必连续），比Hash更可控（可预判数据分布）。但维护成本略高——新增枚举值需修改分区定义。

• 创建示例：
  

  CREATE TABLE sales (id INT, region CHAR(2))
   PARTITION BY LIST COLUMNS(region) (
     PARTITION p_north VALUES IN ('BJ', 'TJ', 'HE'),
     PARTITION p_east VALUES IN ('SH', 'js', 'ZJ'),
     PARTITION p_south VALUES IN ('GD', 'GX', 'HN')
  );

• 查询 WHERE region IN ('SH','JS') 只查p_east；但 WHERE region != 'BJ' 无法剪枝，会扫除p_north外所有分区（仍非全扫，但非最优）。
• 插入不在任何LIST中的值会报错，需提前规划好枚举范围或加一个 default 分区兜底（MySQL 8.0+ 支持）。

混合分区（Composite）：结合多种策略应对复杂业务

MySQL 支持二级分区，如先按时间Range，再在每个Range内按用户ID Hash。适用于既有时间维度又有高并发写入需求的场景（如日志表）。

• 示例：
  

  CREATE TABLE logs (log_id BIGINT, create_time DATETIME)
   PARTITION BY RANGE (TO_DAYS(create_time))
   SUBPARTITION BY HASH(log_id) SUBPARTITIONS 4 (
     PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')), 
     PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01'))
  );

• 查询 WHERE create_time BETWEEN '2024-01-10' AND '2024-01-20' AND log_id = 999，先定位p202401，再在该分区的4个子分区中按Hash找目标子分区，两层剪枝。
• 注意子分区数必须一致，且一级分区不能是List（MySQL限制）；过度嵌套会增加元数据开销，一般两级足够。