MySQL 表分区使用详解

什么是表分区？

表分区(Partitioning)是MySQL将一个大表的数据分散存储在不同的物理文件中的一种技术。每个分区可以看作是表的独立子集，但对外表现为一个完整的表。

分区的主要优势

1. 提高查询性能：查询可以只扫描相关分区而非整个表
2. 简化数据管理：可以单独备份、恢复或优化特定分区
3. 提高数据可用性：损坏的分区不会影响其他分区
4. 改善维护操作：可以单独对分区进行维护操作

分区类型

MySQL支持以下几种分区类型：

1. RANGE分区

按范围划分数据，每个分区包含一定范围内的值。

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

2. LIST分区

按离散值列表划分数据。

CREATE TABLE employees (
    id INT NOT NULL,
    dept_id INT NOT NULL,
    name VARCHAR(50)
) PARTITION BY LIST (dept_id) (
    PARTITION p_sales VALUES IN (1, 3, 5),
    PARTITION p_hr VALUES IN (2, 4),
    PARTITION p_other VALUES IN (NULL, 6, 7, 8)
);

3. HASH分区

通过对分区键应用哈希函数将数据均匀分布到各分区。

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

4. KEY分区

类似于HASH分区，但使用MySQL内置的哈希函数。

CREATE TABLE logs (
    id INT NOT NULL,
    log_time DATETIME,
    message TEXT
) PARTITION BY KEY(log_time) PARTITIONS 8;

5. COLUMNS分区

MySQL 5.5+支持，可以直接按列值分区，支持多列和更多数据类型。

CREATE TABLE rcx (
    a INT,
    b INT,
    c CHAR(3),
    d INT
) PARTITION BY RANGE COLUMNS(a, b, c) (
    PARTITION p0 VALUES LESS THAN (5, 10, 'ggg'),
    PARTITION p1 VALUES LESS THAN (10, 20, 'mmm'),
    PARTITION p2 VALUES LESS THAN (15, 30, 'sss'),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

分区管理操作

添加分区

ALTER TABLE sales ADD PARTITION (PARTITION p4 VALUES LESS THAN (2025));

删除分区

ALTER TABLE sales DROP PARTITION p0; -- 会删除该分区中的所有数据

重组分区

ALTER TABLE sales REORGANIZE PARTITION p3 INTO (
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN (2025),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

合并分区

ALTER TABLE sales REORGANIZE PARTITION p0, p1 INTO (
    PARTITION p01 VALUES LESS THAN (2015)
);

分区注意事项

1. 分区键选择：应选择高选择性的列作为分区键，避免数据倾斜
2. 分区数量：过多分区会影响性能，一般建议不超过1024个
3. 索引：每个分区有自己的索引，但全局索引有限制
4. 外键：分区表不支持外键约束
5. 存储引擎：InnoDB和MyISAM支持分区，但NDB Cluster有特殊要求
6. 查询优化：查询应包含分区键条件以利用分区裁剪(partition pruning)

分区裁剪示例

-- 有效利用分区裁剪的查询
SELECT * FROM sales WHERE YEAR(sale_date) = 2020;

-- 需要重写为
SELECT * FROM sales WHERE sale_date BETWEEN '2020-01-01' AND '2020-12-31';

分区维护建议

1. 定期检查分区大小和性能
2. 根据数据增长情况提前规划分区策略
3. 对于时间序列数据，考虑按时间范围分区
4. 监控分区操作对性能的影响

分区是管理大型表的有效工具，但需要根据具体业务场景谨慎设计分区策略。