当前位置：首页 > article >正文

分区表和分表

article 2025/3/19 7:39:09

分区表（Partitioning）

定义

分区表是将单个表的数据按照某种规则（如范围、列表、哈希等）划分为多个逻辑部分，每个部分称为一个分区。
数据仍然存储在一个物理表中，但逻辑上被分割为多个分区。

特点

逻辑划分：分区是对表内数据的逻辑划分，物理上仍然是一个表。
透明性：对用户来说，分区表的操作与普通表无异，无需修改 SQL 查询。
存储位置：分区可以存储在不同的物理位置（如不同的磁盘），以提升 I/O 性能。

常见分区方式

1. 范围分区（RANGE Partitioning）

定义：按字段值范围划分分区。
特点：
- 适合连续范围的数据（如时间、数值）。
- 需要指定每个分区的上限值。
示例：

CREATE TABLE sales (
    sale_id INT NOT NULL,
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

2. 列表分区（LIST Partitioning）

定义：按字段的离散值划分分区。
特点：
- 适合固定分类的数据（如地区、状态）。
- 每个分区明确列出允许的值。
示例：

CREATE TABLE customers (
    customer_id INT NOT NULL,
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('North'),
    PARTITION p_south VALUES IN ('South'),
    PARTITION p_east VALUES IN ('East'),
    PARTITION p_west VALUES IN ('West')
);

3. 哈希分区（HASH Partitioning）

定义：按字段的哈希值将数据分布到多个分区。
特点：
- 数据均匀分布，适合负载均衡。
- 分区数量由 PARTITIONS 指定。
示例：

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50)
)
PARTITION BY HASH(user_id)
PARTITIONS 4;

4. 键分区（KEY Partitioning）

定义：使用 MySQL 内部哈希函数对字段进行分区。
特点：
- 类似于哈希分区，但支持非整数字段。
- 自动选择分区键。
示例：

CREATE TABLE products (
    product_id INT NOT NULL,
    product_name VARCHAR(50)
)
PARTITION BY KEY(product_name)
PARTITIONS 4;

5. 范围列分区（RANGE COLUMNS Partitioning）

定义：按一个或多个字段直接分区，无需表达式。
特点：
- 支持多列分区。
- 适合日期、时间等字段。
示例：

CREATE TABLE orders (
    order_id INT NOT NULL,
    create_time DATETIME NOT NULL
)
PARTITION BY RANGE COLUMNS(create_time) (
    PARTITION p2022 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024 VALUES LESS THAN ('2025-01-01')
);

6. 列表列分区（LIST COLUMNS Partitioning）

定义：按一个或多个字段的离散值分区。
特点：
- 支持多列分区。
- 适合多字段分类场景。
示例：

CREATE TABLE tasks (
    task_id INT NOT NULL,
    priority INT NOT NULL,
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST COLUMNS(priority, region) (
    PARTITION p_high_north VALUES IN ((5, 'North'), (10, 'North')),
    PARTITION p_low_south VALUES IN ((3, 'South'))
);

7. 子分区（Subpartitioning）

定义：在主分区的基础上进一步划分子分区。
特点：
- 支持组合分区（如 RANGE + HASH 或 LIST + HASH）。
- 提供更细粒度的分区管理。
示例：

CREATE TABLE orders (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(customer_id) (
    PARTITION p2022 VALUES LESS THAN (2023) (
        SUBPARTITION p2022_sp1,
        SUBPARTITION p2022_sp2
    ),
    PARTITION p2023 VALUES LESS THAN (2024) (
        SUBPARTITION p2023_sp1,
        SUBPARTITION p2023_sp2
    )
);

总结对比

分区方式	特点	示例字段类型
范围分区	按连续范围分区	数值、日期
列表分区	按离散值分区	地区、状态
哈希分区	按哈希值均匀分布	用户 ID、产品 ID
键分区	使用 MySQL 内部哈希函数	字符串、其他字段
范围列分区	按字段直接分区，支持多列	日期、时间
列表列分区	按字段离散值分区，支持多列	分类字段
子分区	主分区基础上进一步划分子分区	组合分区（RANGE+HASH）

优点

提升查询性能：查询时只需扫描相关分区，减少 I/O 开销。
简化管理：可以通过删除整个分区快速清理历史数据。
支持大表：适合单表数据量较大的场景。

缺点

单表限制：分区表本质上仍然是一个表，不能解决跨表查询的问题。
分区规则固定：一旦分区规则确定，后续调整较复杂。

分表（Sharding）

MySQL 本身不直接支持分表，需要通过应用层或中间件实现

定义

分表是将一个大表拆分为多个独立的物理表，每个表存储一部分数据。
数据分布在多个表中，通常需要通过应用层或中间件来管理。

特点

物理分离：分表是对数据的物理分离，每个表是一个独立的实体。
分布式架构：分表通常与分布式数据库结合使用。
手动管理：需要开发人员或中间件负责路由查询到正确的表。

常见分表方式

(1) 水平分表（Horizontal Sharding）

按行拆分，每张表存储部分记录。例如，按用户 ID 范围分表：
- users_0 存储用户 ID 为 0-999 的记录。
- users_1 存储用户 ID 为 1000-1999 的记录。
示例：按用户 ID 范围分表。

手动创建分表

-- 表1：存储用户ID为0-999的记录
CREATE TABLE users_0 (
    user_id INT NOT NULL,
    username VARCHAR(50)
);

-- 表2：存储用户ID为1000-1999的记录
CREATE TABLE users_1 (
    user_id INT NOT NULL,
    username VARCHAR(50)
);

插入数据

根据用户 ID 决定插入哪个表：

-- 用户ID为500，插入users_0
INSERT INTO users_0 (user_id, username) VALUES (500, 'Alice');

-- 用户ID为1500，插入users_1
INSERT INTO users_1 (user_id, username) VALUES (1500, 'Bob');

查询数据

需要手动路由查询：

-- 查询用户ID为500
SELECT * FROM users_0 WHERE user_id = 500;

-- 查询用户ID为1500
SELECT * FROM users_1 WHERE user_id = 1500;

(2) 垂直分表（Vertical Sharding）

按列拆分，将不同的字段分布到不同的表中。例如：
- users_basic 存储用户的基本信息（ID、姓名）。
- users_profile 存储用户的详细信息（地址、兴趣爱好）。
示例：将用户的基本信息和详细信息分开。

创建分表

-- 基本信息表
CREATE TABLE users_basic (
    user_id INT NOT NULL,
    username VARCHAR(50)
);

-- 详细信息表
CREATE TABLE users_profile (
    user_id INT NOT NULL,
    address VARCHAR(200),
    phone VARCHAR(20)
);

插入数据

分别插入基本信息和详细信息：

INSERT INTO users_basic (user_id, username) VALUES (1, 'Alice');
INSERT INTO users_profile (user_id, address, phone) VALUES (1, '123 Main St', '123-456-7890');

查询数据

需要联表查询：

SELECT b.user_id, b.username, p.address, p.phone
FROM users_basic b
JOIN users_profile p ON b.user_id = p.user_id
WHERE b.user_id = 1;

优点

扩展性强：支持水平扩展，适合超大规模数据集。
提升性能：减少单表数据量，降低查询和写入压力。
灵活性高：可以根据业务需求灵活设计分表策略。

缺点

管理复杂：需要额外的逻辑处理跨表查询和事务。
数据一致性：分布式环境下的数据一致性和事务管理更复杂。
查询复杂度增加：需要手动处理表路由和聚合操作。

对比总结

特性	分区表	分表
数据存储	单表内逻辑分区	多个独立物理表
实现方式	数据库内置功能	应用层或中间件实现
透明性	对用户透明，无需修改查询	需要手动处理表路由和查询
扩展性	有限，依赖单实例的硬件资源	强，支持分布式架构
适用场景	单表数据量较大，但仍在单机范围内	数据量极大，需分布式存储和计算
跨表查询	不涉及，仍是单表	需要额外处理
事务支持	数据库原生支持	分布式事务需额外实现

使用场景示例

分区表

日志系统：按日期范围分区，方便清理历史数据。

CREATE TABLE logs (
    log_id INT,
    log_time DATETIME
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);