MySQL分区

MySQL 分区表：大规模数据的高效管理方案

MySQL 分区表是针对大表的一种优化方案，通过将表数据物理拆分到多个独立分区，实现数据的逻辑统一管理与物理分散存储。分区对应用透明（逻辑上仍是一张表），但能显著提升查询效率、简化数据维护。

分区表的核心概念

分区的本质

逻辑统一性：应用访问分区表时，无需关心数据分布，SQL 语句与普通表一致。
物理独立性：每个分区对应独立的数据文件（如 .ibd），可分布在不同磁盘，降低单文件大小限制。

分区的核心限制

分区列约束：若表存在 PRIMARY KEY 或 UNIQUE KEY，分区列必须是这些键的组成部分（确保数据唯一性可验证）。
无全局索引：索引仅在分区内有效，跨分区查询需扫描所有相关分区。

分区类型及创建方式

MySQL 支持 4 种主要分区类型，适用于不同的数据分布场景：

RANGE 分区：按连续区间划分（最常用）

根据列值所在的连续区间分配数据，适合时间、ID 等有序数据（如按月份分区日志表）。

创建示例：

CREATE TABLE test_staff_range (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  age INT,
  PRIMARY KEY (id)  -- 分区列 id 是主键的一部分
) ENGINE=InnoDB
PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (1000),  -- id < 1000 存于此分区
  PARTITION p1 VALUES LESS THAN (2000),  -- 1000 ≤ id < 2000
  PARTITION p2 VALUES LESS THAN MAXVALUE  -- id ≥ 2000（MAXVALUE 表示最大值）
);

特点：

新增分区只能添加到区间末尾（如新增 p3 VALUES LESS THAN (3000)）。
适合范围查询（如 WHERE id BETWEEN 500 AND 1500 仅扫描 p0 和 p1）。

LIST 分区：按离散值列表划分

根据列值是否匹配离散值列表分配数据，适合枚举类数据（如按地区、状态分区）。

创建示例：

CREATE TABLE test_staff_list (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  region INT,  -- 地区编码（1: 华北, 2: 华东, 3: 华南）
  PRIMARY KEY (id, region)  -- 分区列 region 需包含在主键中
) ENGINE=InnoDB
PARTITION BY LIST (region) (
  PARTITION p_north VALUES IN (1),    -- 华北地区
  PARTITION p_east VALUES IN (2),     -- 华东地区
  PARTITION p_south VALUES IN (3)     -- 华南地区
);

特点：

分区值必须是明确的离散值，不可重复。
插入未定义的值会报错（需确保所有可能值都被包含）。

HASH 分区：按哈希值均匀分布

根据用户定义的哈希函数结果分配数据，适合均匀分布数据（如分散热点数据）。

创建示例：

CREATE TABLE test_staff_hash (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  PRIMARY KEY (id)
) ENGINE=InnoDB
PARTITION BY HASH (id)  -- 基于 id 哈希
PARTITIONS 4;  -- 分为 4 个分区

特点：

分区数量固定（创建时指定），数据按哈希值均匀分配到各分区。
适合随机访问场景，但范围查询效率低（需扫描所有分区）。

KEY 分区：按 MySQL 内置哈希函数划分

类似 HASH 分区，但哈希函数由 MySQL 自动生成（通常基于主键），适合简化哈希分区配置。

创建示例：

CREATE TABLE test_staff_key (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  PRIMARY KEY (id)
) ENGINE=InnoDB
PARTITION BY LINEAR KEY (id)  -- LINEAR 表示线性哈希（分区增长更均衡）
PARTITIONS 4;

特点：

无需手动定义哈希函数，MySQL 自动优化。
LINEAR KEY 使分区扩展更高效（新增分区时只需重新分布部分数据）。

分区表的管理操作

查看分区信息

-- 查看表的所有分区详情
SELECT * FROM information_schema.partitions 
WHERE table_schema = '数据库名' AND table_name = '表名';

-- 查看指定分区的数据
SELECT * FROM 表名 PARTITION (分区名);

新增分区

RANGE/LIST 分区：

-- 给 RANGE 分区新增 p3
ALTER TABLE test_staff_range 
ADD PARTITION (PARTITION p3 VALUES LESS THAN (3000));

-- 给 LIST 分区新增 p_west（地区 4）
ALTER TABLE test_staff_list 
ADD PARTITION (PARTITION p_west VALUES IN (4));

HASH/KEY 分区：

-- 增加 2 个分区（总分区数变为 6）
ALTER TABLE test_staff_hash 
ADD PARTITION PARTITIONS 2;

删除分区

RANGE/LIST 分区（删除分区会同时删除数据）：
1
ALTER TABLE test_staff_range DROP PARTITION p0;

HASH/KEY 分区（减少分区数，需指定剩余数量）：

-- 保留 2 个分区（删除多余分区，数据重新分布）
ALTER TABLE test_staff_hash COALESCE PARTITION 2;

分区维护（优化、修复等）

-- 重建分区（整理碎片，类似 OPTIMIZE TABLE）
ALTER TABLE test_staff_range REBUILD PARTITION p1, p2;

-- 检查分区数据完整性
ALTER TABLE test_staff_range CHECK PARTITION p1;

-- 分析分区（更新统计信息）
ALTER TABLE test_staff_range ANALYZE PARTITION p1;

分区表的优势与适用场景

优势：

提升查询效率：范围查询仅扫描相关分区（如按日期查询仅访问对应月份分区）。
简化数据管理：快速删除历史数据（如 DROP PARTITION 比 DELETE 高效）。
突破存储限制：分散数据到多个文件，避免单文件过大（如超过 OS 文件大小限制）。
并行处理：聚合查询（如 SUM、COUNT）可并行扫描多个分区，提升效率。

适用场景：

大表：数据量超过百万行，单表查询变慢。
时间序列数据：如日志、订单表（按月份 / 季度分区，便于归档历史数据）。
热点数据分散：如用户表按 ID 哈希分区，避免单分区访问过于频繁。

不适用场景：

小表：分区带来的管理开销超过性能收益。
随机访问为主：哈希分区可能导致跨分区扫描，效率低于普通表。
频繁跨分区关联：多表 join 涉及多个分区时，性能可能下降