MySQL数据库分区

可儿·四系桜

已于 2024-11-14 16:57:44 修改

阅读量1.3k

点赞数 30

CC 4.0 BY-SA版权

分类专栏： # MySQL 数据库文章标签：数据库 mysql

于 2024-11-14 11:19:26 首次发布

本文链接：https://blog.csdn.net/kersixy/article/details/143676789

MySQL 同时被 2 个专栏收录

7 篇文章

订阅专栏

数据库

3 篇文章

订阅专栏

官方文档：MySQL :: MySQL 8.0 Reference Manual :: 26 Partitioning

1. 分区介绍

表分区（Partition）是一种数据存储方案，可以解决单表数据较多的问题。MySQL5.1开始支持表分区功能。

数据库的表最终肯定是保存在磁盘中，对于InoDB引擎，一张表的数据在磁盘上对应一个ibd文件。比如，在test库中创建一个t_user表：

CREATE TABLE `t_user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '账号',
  `password` varchar(50) NOT NULL COMMENT '密码',
  `role` tinyint NOT NULL COMMENT '角色：0-管理员、1-1区用户、2-2区用户',
  PRIMARY KEY (`id`)
)

会在MySQL数据目录的Data文件夹下test库生成一个t_user.ibd文件，如下图：

数据目录文件位置说明：

windows系统：在MySQL安装过程中，指定的Data Directory目录位置：

Linux系统：输入cat /etc/my.cnf | grep datadir命令

如果表数据过多，就会导致文件体积非常大。文件就会跨越多个磁盘分区，数据检索时的速度就会非常慢。

为了解决这个问题，MySQL在5.1版本引入表分区功能。简单来说，就是按照某种规则，把表数据对应的ibd文件拆分成多个文件来存储。从物理上来看，一张表的数据被拆到多个表文件存储了；从逻辑上来看，他们对外表现是一张表。

例如，将t_user表按用户角色拆分：

此时，t_user表的磁盘文件就被分成了两个文件。但逻辑上还是一张表。增删改查的方式不会有什么变化，只不过底层MySQL底层的处理上会有变更。例如检索时可以只检索某个文件，而不是全部。

注意：

操作分区表和操作普通表没有区别，数据库会自动根据数据来将该数据插入对应分区中。

2. 表分区的优点

可以存储更多的数据，突破单表上限。甚至可以存储到不同磁盘，突破磁盘上限
查询时可以根据规则只检索某一个文件，提高查询效率
数据统计时，可以多文件并行统计，最后汇总结果，提高统计效率
对于一些历史数据，如果不需要时，可以直接删除分区文件，提高删除效率

3. 表分区的限制和缺点

在MySQL 5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。
MySQL的分区字段，必须包含在主键字段内。如果一个表有主键，那么分区字段必须包含在主键内，也就是分区字段必须是主键的一部分或者全部，不能以非主键的字段作为分区字段。当然，也可以为没有主键的表建立分区。
分区表无法使用外键约束。
NULL值会使分区过滤无效。
所有分区必须使用相同的存储引擎。

4. 常见的分区方式

4.1. RANGE 分区

RANGE 分区是一种常用的分区方式，它根据某个列的值范围将数据分散到不同的分区中。这种分区方式特别适用于时间序列数据或具有明显范围特征的数据。

RANG分区特点：

每个分区包含一个值的范围。
多个分区的分区键的值的范围要连续，依次递增，但是不能重叠。
默认情况下使用VALUES LESS THAN属性，但每个分区并不包括指定的那个值。
MAXVALUE 是一个特殊值，表示大于所有指定值的范围。例如：PARTITION p3 VALUES LESS THAN MAXVALUE表示分区 p3 包含所有大于上一个分区的最大范围值。
分区键仅支持整数类型及通过函数转换的整数类型。

4.1.1. RANGE COLUMNS 分区

RANGE COLUMNS 分区与RANGE 分区类似，但允许使用基于多个列值的范围定义分区。此外，可以使用DATE、DATETIME类型，及通过函数转换的DATE、DATETIME类型。

4.2 LIST 分区

LIST 分区是一种根据列值的离散列表来分区数据的方式。这种方式特别适用于列值是特定集合的情况，例如部门 ID、地区代码等。

LIST分区的特点：

每个分区包含一组固定的值。
支持非连续的值。
分区值必须在创建分区时明确指定，不支持动态值。如PARTITION p0 VALUES IN (MAXVALUE) 。
各分区的列表值不能重复。
每一行数据必须能找到对应的分区列表，否则数据插入失败。
分区键仅支持整数类型及通过函数转换的整数类型。

4.2.1. LIST COLUMNS 分区

LIST COLUMNS 分区是 LIST分区的变体，它允许使用多个列作为分区键，以及使用非整数类型的数据类型作为分区列；可以使用字符串类型、DATE和 DATETIME类型。

4.3. HASH 分区

HASH 分区是一种基于哈希函数对数据进行分区的方式。它通过计算分区键的哈希值，并将数据分布到不同的分区中。这种方式特别适用于需要均匀分布数据的场景，如用户 ID、订单 ID 等。

HASH分区的特点：

通过哈希函数将数据均匀分布到各个分区中，避免数据倾斜。
分区逻辑简单，只需指定分区键和分区数量。例如，PARTITION BY HASH (customer_id) PARTITIONS 4 表示根据 customer_id 的哈希值将数据分成 4 个分区。
键值必须是一个整数类型的值，或是通过函数可以转为整数类型。

HASH分区的算法：

使用简单的哈希函数，将哈希值对分区数量取模。例如，表结构为：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

如果向该表插入col3='2005-09-15'，然后按如下方式确定其存储的分区：

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

4.3.1. 线性HASH 分区

在MySQL中，线性哈希（Linear Hash）分区是一种特殊的哈希分区方法，它通过线性哈希算法将数据均匀分布到多个分区中。线性哈希分区的主要优点是可以动态地增加分区数量，而不需要重新分配所有数据。

HASH分区的算法：

线性哈希分区：使用更复杂的线性哈希函数，允许动态增加分区数量。计算公式为：

V = POWER(2, CEILING(LOG(2, num)))
N = F(column_list) & (V - 1)
While N >= num:
  Set V = V / 2
  Set N = N & (V - 1)

例如，表结构为：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

情况一：如果向该表插入col3='2005-09-15'，然后按如下方式确定其存储的分区：

V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

由于3小于分区总分区数6（分区编号为#0~#5），所以会存到分区#3。

情况二：如果向该表插入col3='1998-10-19'，然后按如下方式确定其存储的分区：

V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('1998-10-19') & (8 - 1)
  = 1998 & 7
  = 6

由于6大于等于分区总分区数6（分区编号为#0~#5），所以继续计算：

N = 6 & ((8 / 2) - 1)
  = 6 & 3
  = 2

由于2小于分区总分区数6（分区编号为#0~#5），所以会存到分区#2。

4.4. KEY 分区

KEY 分区是一种基于哈希函数对数据进行分区的方式，类似于 HASH 分区，但它使用的是数据库内部的哈希函数，而不是用户指定的哈希函数。KEY 分区特别适用于需要均匀分布数据的场景。

KEY 分区的特点：

通过哈希函数将数据均匀分布到各个分区中，避免数据倾斜。
分区逻辑简单，只需指定分区键和分区数量。例如，PARTITION BY KEY (customer_id) PARTITIONS 4 表示根据 customer_id 的哈希值将数据分成 4 个分区。
使用数据库内部的哈希函数，无需用户指定具体的哈希算法。
KEY 分区的键值不限定字段类型。

4.5. 子分区（复合分区）

子分区是分区表中每个分区的再次分割，适合保存非常大量的数据。在MySQL 5.1中，对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区。

5. 常见的分区语句

5.1. 创建分区

1. 格式：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
)
PARTITION BY partition_type (partition_expression)
[
    PARTITIONS num_partitions
]
[
SUBPARTITION BY partition_type (partition_expression) 
SUBPARTITIONS num_partitions
]
(
    [PARTITION partition_name 
        VALUES {LESS THAN | IN} (value_list)
        [TABLESPACE tablespace_name]
        [DATA DIRECTORY 'directory']
        [INDEX DIRECTORY 'directory']
        [MAX_ROWS = max_rows]
        [MIN_ROWS = min_rows]
        [ENGINE = engine_name]
    ],
    ...
);

参数说明：

table_name: 表的名称。
column1, column2, ...: 表的列及其数据类型。
partition_type: 分区类型，可以是 RANGE, LIST, HASH, KEY, LINEAR HASH, RANGE COLUMNS, LIST COLUMNS。
partition_expression: 用于分区的表达式，例如 YEAR(date_column) 或 id。
num_partitions: 指定分区的数量（仅适用于 HASH 和 KEY 分区）。
partition_name: 分区的名称。
VALUES {LESS THAN | IN} (value_list): 指定分区的值范围或列表。
TABLESPACE tablespace_name: 指定表空间。
DATA DIRECTORY 'directory': 指定数据目录。
INDEX DIRECTORY 'directory': 指定索引目录。
MAX_ROWS = max_rows: 指定分区的最大行数。
MIN_ROWS = min_rows: 指定分区的最小行数。
ENGINE = engine_name: 指定存储引擎。

2. SQL示例：

RANGE 分区：

CREATE TABLE cus_range (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990)
);

RANGE COLUMN 分区：

CREATE TABLE rc (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

LIST 分区：

CREATE TABLE cus_list (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    region INT(10), -- 注意：分区列一定是数字类型
    PRIMARY KEY (customer_id,region)  -- 注意：分区列必须包含在主键中
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN (1,3,5,7),
    PARTITION p_south VALUES IN (2,4,6,8),
    PARTITION p_east VALUES IN (10,13,15,54),
    PARTITION p_west VALUES IN (12,16,17)
);

LIST COLUMN 分区：

CREATE TABLE lc (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

HASH 分区：

CREATE TABLE cus_hash (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    region VARCHAR(10),
    PRIMARY KEY (customer_id)
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;

线性HASH 分区：

CREATE TABLE lh (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

KEY 分区：

CREATE TABLE cus_key(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    region VARCHAR(10),
    PRIMARY KEY (customer_id,name)
)
PARTITION BY KEY (name)
PARTITIONS 4;

RANGE-HASH 复合分区

CREATE TABLE range_hash (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

亦可以写成：

CREATE TABLE range_hash (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

说明：

如果设置主键，分区列必须包含在主键中。
如果分区键插入null值，LIST 分区如果没有指定存哪个分区，则会抛出ERROR 1504 (HY000): Table has no partition for value NULL 错误，其他分区类型默认存最小分区。
在对表建立分区后，插入的数据的值应该严格遵守分区的定义，当插入一个不属于任何分区的值时，MySQL数据库会直接报错。如下：
INSERT INTO members ( firstname, lastname, username, email, joined ) 
VALUES ( '七', '韩', 'hanqi', 'hanqi@163.com', '2099-11-11' );

5.2. 增加分区

1. 格式：

ALTER TABLE table_name ADD PARTITION 
[
SUBPARTITIONS num_partitions
]
(
    [PARTITION partition_name 
        VALUES {LESS THAN | IN} (value_list)
        [TABLESPACE tablespace_name]
        [DATA DIRECTORY 'directory']
        [INDEX DIRECTORY 'directory']
        [MAX_ROWS = max_rows]
        [MIN_ROWS = min_rows]
        [ENGINE = engine_name]
    ],
    ...
);

参数说明：

table_name: 表的名称。
num_partitions: 指定分区的数量（仅适用于 HASH 和 KEY 分区）。
partition_name: 分区的名称。
VALUES {LESS THAN | IN} (value_list): 指定分区的值范围或列表。
TABLESPACE tablespace_name: 指定表空间。
DATA DIRECTORY 'directory': 指定数据目录。
INDEX DIRECTORY 'directory': 指定索引目录。
MAX_ROWS = max_rows: 指定分区的最大行数。
MIN_ROWS = min_rows: 指定分区的最小行数。
ENGINE = engine_name: 指定存储引擎。

注意：

复合分区的子分区无法添加，只能删除分区重新创建。
HASH 分区和KEY 分区设置的分区数是新增的分区数而非总分区数，即新分区数 = 原分区数 + 新增的分区数。
分区的改变会导致数据库的数据重新分配到对应分区。

2. SQL示例：

RANGE 分区：

ALTER TABLE cus_range ADD PARTITION ( 
PARTITION p6 VALUES LESS THAN (2000),
PARTITION p7 VALUES LESS THAN MAXVALUE
);

HASH 分区：

ALTER TABLE cus_hash ADD PARTITION PARTITIONS 2; -- 新分区 = 原分区数 + 2

5.3. 拆分分区

1. 格式：

ALTER TABLE table_name
REORGANIZE PARTITION original_partition INTO (
    [PARTITION partition_name 
        VALUES {LESS THAN | IN} (value_list)
        [TABLESPACE tablespace_name]
        [DATA DIRECTORY 'directory']
        [INDEX DIRECTORY 'directory']
        [MAX_ROWS = max_rows]
        [MIN_ROWS = min_rows]
        [ENGINE = engine_name]
    ],
    ...
);

说明：

table_name: 表的名称。
original_partition: 原分区名称。
partition_name: 新分区的名称。
VALUES {LESS THAN | IN} (value_list): 指定分区的值范围或列表。
TABLESPACE tablespace_name: 指定表空间。
DATA DIRECTORY 'directory': 指定数据目录。
INDEX DIRECTORY 'directory': 指定索引目录。
MAX_ROWS = max_rows: 指定分区的最大行数。
MIN_ROWS = min_rows: 指定分区的最小行数。
ENGINE = engine_name: 指定存储引擎。

注意：

分区的改变会导致数据库的数据重新分配到对应分区。
HASH 分区、KEY分区和复合分区的子分区不支持拆分分区。

RANGE 分区：

拆分之后的总范围不能比原分区大也不能比原分区小。
拆分后的分区不能和上一个分区或下一个分区重叠。
拆分的分区范围依次递增。

LIST 分区：

拆分的分区列表内容不能重叠。
拆分的分区列表可以和原分区的列表内容、数量可以完全不同。

2. SQL示例：

ALTER TABLE range_hash 
REORGANIZE PARTITION p2 INTO (
    PARTITION p3 VALUES LESS THAN (2100), -- 分区范围依次递增，第一个分区范围大于原分区最小值
    PARTITION p4 VALUES LESS THAN (2200),
	PARTITION p5 VALUES LESS THAN MAXVALUE -- 最后一个分区等于原分区范围
);

5.4. 合并分区

1. 格式：

ALTER TABLE table_name
REORGANIZE PARTITION original_partition1, original_partition2 INTO (
    [PARTITION partition_name 
        VALUES {LESS THAN | IN} (value_list)
        [TABLESPACE tablespace_name]
        [DATA DIRECTORY 'directory']
        [INDEX DIRECTORY 'directory']
        [MAX_ROWS = max_rows]
        [MIN_ROWS = min_rows]
        [ENGINE = engine_name]
    ]
);

说明：

table_name: 表的名称。
original_partition1, original_partition2: 原分区名称。
partition_name: 新分区的名称。
VALUES {LESS THAN | IN} (value_list): 指定分区的值范围或列表。
TABLESPACE tablespace_name: 指定表空间。
DATA DIRECTORY 'directory': 指定数据目录。
INDEX DIRECTORY 'directory': 指定索引目录。
MAX_ROWS = max_rows: 指定分区的最大行数。
MIN_ROWS = min_rows: 指定分区的最小行数。
ENGINE = engine_name: 指定存储引擎。

注意：

分区的改变会导致数据库的数据重新分配到对应分区。
HASH 分区、KEY分区和复合分区的子分区不支持合并分区。

RANGE 分区：

合并之后的总范围为合并分区的总范围。
要合并的分区是连续的范围。

LIST 分区：

合并的分区列表内容不能和其他分区重叠。
合并的分区列表可以和原分区的总列表内容、数量可以完全不同。

2. SQL示例：

ALTER TABLE range_hash 
REORGANIZE PARTITION p0, p1 INTO (
    PARTITION p0_1 VALUES LESS THAN (2000)
);

5.5. 删除分区

5.5.1. 非HASH分区、KEY分区

1. 格式：

ALTER TABLE table_name DROP PARTITION partition_name;

说明：

table_name: 表的名称。
partition_name: 分区的名称。

注意：

删除分区会连带对应分区的数据一块删除。

2. SQL示例：

ALTER TABLE cus_range DROP PARTITION p2;

5.5.2. HASH分区、KEY分区

1. 格式：

ALTER TABLE table_name COALESCE PARTITION partition_num;

说明：

table_name: 表的名称。
partition_num: 删除的分区数。

注意：

partition_num是分区的数量而非剩余的分区数量，即新分区数 = 原分区数 - 删除的分区数。

2. SQL示例：

ALTER TABLE cus_hash COALESCE PARTITION 4;

5.6. 查看分区

5.6.1. 查看建表语句

SHOW CREATE TABLE 表名;

返回示例：

5.6.2. 查看分区信息

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = '表名' AND TABLE_SCHEMA = '库名';

返回示例：

5.6.3 查看某个分区的数据

注意：

MySQL 5.5.41之前不支持对指定分区的查询，在5.6增强了分区表的分区的相关操作，其中包括支持了对指定分区的查询。

SELECT * FROM 表名 PARTITION (分区名);

5.7. 清空分区数据

1. 格式：

ALTER TABLE 表名 TRUNCATE PARTITION 分区名1, 分区名2, ...;

说明：

分区名可以是子分区名。

2. SQL示例：

ALTER TABLE cus_range TRUNCATE PARTITION p2;

5.8 分区表变普通表

ALTER TABLE 表名 REMOVE PARTITIONING;