MariaDB 的分区表有哪些类型？如何创建和管理分区表？

MariaDB 的分区表（Partitioning）是将大表分割成更小、更易管理的部分的技术，可以显著提升查询性能和管理效率。

1. 分区类型

RANGE 分区

sql
-- 按日期范围分区
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 按数值范围分区
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    age INT
) PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (18),
    PARTITION p1 VALUES LESS THAN (30),
    PARTITION p2 VALUES LESS THAN (50),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

LIST 分区

sql
-- 按离散值列表分区
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE,
    region VARCHAR(20),
    amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_east VALUES IN ('New York', 'Boston', 'Philadelphia'),
    PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco', 'Seattle'),
    PARTITION p_midwest VALUES IN ('Chicago', 'Detroit', 'Cleveland'),
    PARTITION p_south VALUES IN ('Atlanta', 'Miami', 'Dallas')
);

HASH 分区

sql
-- 哈希分区（均匀分布数据）
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category_id INT,
    price DECIMAL(10,2)
) PARTITION BY HASH(id) PARTITIONS 4;

-- 线性哈希分区（更快但分布可能不均匀）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY LINEAR HASH(customer_id) PARTITIONS 8;

KEY 分区

sql
-- KEY 分区（类似 HASH，使用主键或唯一键）
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY KEY(id) PARTITIONS 4;

2. 分区管理

添加分区

sql
-- RANGE 分区添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- LIST 分区添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p_other VALUES IN ('Other', 'Unknown')
);

-- HASH/KEY 分区增加分区数量
ALTER TABLE products REORGANIZE PARTITION (
    PARTITION p0, PARTITION p1, PARTITION p2, PARTITION p3
) INTO (
    PARTITION p0, PARTITION p1, PARTITION p2, 
    PARTITION p3, PARTITION p4, PARTITION p5
);

删除分区

sql
-- 删除分区（同时删除数据）
ALTER TABLE orders DROP PARTITION p2022;

-- 删除所有分区（转换为普通表）
ALTER TABLE orders REMOVE PARTITIONING;

合并分区

sql
-- 合并 RANGE 分区
ALTER TABLE orders REORGANIZE PARTITION p2022, p2023 INTO (
    PARTITION p2022_2023 VALUES LESS THAN (2024)
);

3. 分区查询

sql
-- 查看分区信息
SELECT 
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';

-- 查询特定分区
SELECT * FROM orders PARTITION (p2023);

-- 使用分区裁剪优化查询
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

4. 分区维护

sql
-- 检查分区
ALTER TABLE orders CHECK PARTITION p2023;

-- 优化分区
ALTER TABLE orders OPTIMIZE PARTITION p2023;

-- 分析分区
ALTER TABLE orders ANALYZE PARTITION p2023;

-- 修复分区
ALTER TABLE orders REPAIR PARTITION p2023;

5. 分区索引

sql
-- 创建本地索引（每个分区独立索引）
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 创建全局索引（MariaDB 10.3+）
CREATE UNIQUE INDEX idx_global ON orders(id);

6. 分区使用场景

时间序列数据

sql
CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    log_time TIMESTAMP,
    message TEXT,
    level VARCHAR(10)
) PARTITION BY RANGE (UNIX_TIMESTAMP(log_time)) (
    PARTITION p2024_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
    PARTITION p2024_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01')),
    PARTITION p2024_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01')),
    PARTITION p2024_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01'))
);

大表归档

sql
-- 定期归档旧数据
ALTER TABLE orders DROP PARTITION p2022;

7. 分区注意事项

1. 分区键选择：选择查询中常用的列作为分区键
2. 分区数量：不宜过多，建议 10-100 个分区
3. 主键约束：主键必须包含分区键
4. 唯一索引：唯一索引必须包含分区键
5. 数据分布：确保数据在各分区间均匀分布
6. 维护成本：分区表需要额外的维护操作

分区表是处理大数据量的有效手段，合理使用可以显著提升查询性能和管理效率。