MySQL分区表：创建表时设置分区；对现有表进行分区；使用临时分区表逐步迁移数据（大表场景）...

我不吃洋葱啊

于 2024-08-11 02:20:36 发布

阅读量19

点赞数

文章标签： mysql 数据库

创建表时设置分区

CREATE TABLE sales (
    sale_date DATE NOT NULL,
    product VARCHAR(20) NOT NULL,
    amount INT NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date))
(
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2005),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2015),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

对现有表进行分区

ALTER TABLE sales 
PARTITION BY RANGE (YEAR(sale_date))
(
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2005),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2015),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

执行这个命令后，MySQL 会自动将表中的所有数据重新组织到相应的分区中。这个过程可能会很慢，并且会占用大量的磁盘空间和计算资源，因为MySQL 需要创建一个新的分区表，并将数据从原来的表中复制到新的分区表中。

在以上的分区定义中：

p0 分区将包含所有 YEAR(sale_date) 小于2000的数据。
p1 分区将包含所有 YEAR(sale_date) 大于等于2000且小于2005的数据。
p2 分区将包含所有 YEAR(sale_date) 大于等于2005且小于2010的数据。
p3 分区将包含所有 YEAR(sale_date) 大于等于2010且小于2015的数据。
p4 分区将包含所有 YEAR(sale_date) 大于等于2015的数据。

使用临时分区表逐步迁移数据

-- 创建一个临时分区表
CREATE TABLE sales_temp LIKE sales;

-- 为临时表添加分区
ALTER TABLE sales_temp
PARTITION BY RANGE (YEAR(sale_date))
(
    PARTITION p0 VALUES LESS THAN (2001),
    PARTITION p1 VALUES LESS THAN (2005),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2015),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

-- 使用INSERT INTO SELECT语句逐步将数据从原表复制到分区表
INSERT INTO sales_temp SELECT * FROM sales WHERE YEAR(sale_date) < 2001;
INSERT INTO sales_temp SELECT * FROM sales WHERE YEAR(sale_date) BETWEEN 2001 AND 2004;
INSERT INTO sales_temp SELECT * FROM sales WHERE YEAR(sale_date) BETWEEN 2005 AND 2009;
INSERT INTO sales_temp SELECT * FROM sales WHERE YEAR(sale_date) BETWEEN 2010 AND 2014;
INSERT INTO sales_temp SELECT * FROM sales WHERE YEAR(sale_date) >= 2015;

-- 检查数据是否正确迁移
SELECT COUNT(*) FROM sales;
SELECT COUNT(*) FROM sales_temp;

-- 如果数据一致，可以删除旧表并重命名新表
DROP TABLE sales;
RENAME TABLE sales_temp TO sales;