MySQL 表分区

MySQL表分区是将一个大表分割成若干个小表的技术。这样做可以提高查询和维护的效率，并且还可以改善数据的管理方式。

注意：如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来（即分区的字段也必须是主键或者唯一索引）

分区适用场景

  • 大型表处理
  • 时间范围查询
  • 数据归档和数据保留
  • 并行查询和负载均衡
  • 数据删除和维护

判断当前MySQL是否支持分区？

1

show variables like '%partition%';

以下是几种常见的分区类型：

  • RANGE分区：基于给定的一个给定列的值，将数据划分到不同的分区。

  • LIST分区：类似于RANGE分区，但是列值是离散的集合。

  • HASH分区：基于用户定义的表达式的返回值进行分区，该表达式对分区的数量进行哈希。

  • KEY分区：类似于HASH分区，但是使用的哈希函数是由MySQL服务器提供的。

  • COLUMNS分区：基于列值的范围或列表来进行分区，适用于某些特定的数据类型。

RANGE分区

以下是一个RANGE分区的示例：

假设有一个orders表，包含一个order_date字段，可以根据order_date字段的值将表分为几个分区：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE orders (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_amount DECIMAL(10, 2) NOT NULL
) 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2024),
    PARTITION p2 VALUES LESS THAN (2025),
    PARTITION p3 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

创建了一个按年份划分的orders表，将订单按照它们的年份放入不同的分区。p0分区包含order_date在2023年以前的所有订单，p1分区包含2024年，以此类推。pmax分区包含所有2026年以后的订单。

LIST分区

LIST 分区是根据某一列的离散值将数据分布到不同的分区。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY LIST (region) (
 PARTITION p_east VALUES IN ('New York', 'Boston'), 
 PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'), 
 PARTITION p_other VALUES IN (DEFAULT)
);

HASH分区

HASH 分区是使用哈希算法将数据均匀地分布到多个分区中。

示例：

1
2
3
4
5
6

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY HASH (id) PARTITIONS 4;

PARTITIONS 4 指定4个分区

KEY分区

KEY 分区是根据某一列的哈希值将数据分布到不同的分区。和HASH分区不同在于使用的哈希算法不同。

示例：

1
2
3
4
5
6

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY KEY (id) PARTITIONS 4;

PARTITIONS 4 指定4个分区

COLUMNS 分区

包括 RANGE COLUMNS 分区和 LIST COLUMNS 分区

RANGE COLUMNS 分区是根据列的范围值将数据分布到不同的分区的分区策略。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50),
 birthday DATE NOT NULL
)
PARTITION BY LIST COLUMNS (region, YEAR(category)) (
 PARTITION p_east VALUES LESS THAN (('New York', 2022), ('Boston', 2022)), 
 PARTITION p_west VALUES LESS THAN (('Los Angeles', 2022), ('San Francisco', 2022)), 
 PARTITION p_east_other VALUES LESS THAN (('New York', MAXVALUE)),
 PARTITION p_west_other VALUES LESS THAN (('Los Angeles', MAXVALUE))
);

LIST COLUMNS 分区是根据列的离散值将数据分布到不同的分区的分区策略。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50),
 category VARCHAR(50)
)
PARTITION BY LIST COLUMNS (region, category) (
 PARTITION p_east VALUES IN (('New York', 'A'), ('Boston', 'B')), 
 PARTITION p_west VALUES IN (('Los Angeles', 'C'), ('San Francisco', 'D')), 
 PARTITION p_other VALUES IN (DEFAULT)
);

添加分区

1
2

alter table table_name add partition (partition new_part values less than (4000)); -- range 分区
alter table table_name add partition (partition new_part values in (40));  -- lists分区

删除分区

1

alter table table_name drop partition part_name;

drop方式删除会删除分区数据

1

alter table table_name remove partitioning;

remove不会删除数据

重新分区

range分区

1
2
3
4

alter table table_name partition by range(id)(
partition p1 values less than (2000),
partition p2 values less than (4000)
);

hash分区

1

alter table table_name partition by hash(id) partitions 7;

合并分区

合并相邻两个分区

1

ALTER TABLE table_name COALESCE PARTITION p1, p2;

合并两个分区

1

alter table table_name reorganize partition p1,p3 into (partition p1 values less than (1000));

重组分区

1
2
3
4
5

alter table table_name
reorganize partition p1 into(
partition p1 values less than (100),
partition p3 values less than (1000)
);

查询分区分析

1

explain partitions select ...

分区统计信息

1

ALTER TABLE users ANALYZE PARTITION p1;

参考