本文最后更新于 2024-04-10，欢迎来到我的Blog! https://www.zpeng.site/

MySQL表分区

1、简介

https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

MySQL表分区是一种数据库管理技术，用于将大型表拆分成更小、更可管理的分区（子表）。每个分区可以独立进行维护、备份和查询，从而提高数据库性能和管理效率。

mysql8及以上只有存储引擎InnoDB、NDB支持分区。

2、优缺点

分区的好处：

可以让单表存储更多的数据。
分区表的数据更容易维护，可以通过清除整个分区来批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
部分查询能够从查询条件确定只落在少数分区上，速度会很快（查询条件尽量扫描少的分区）。
分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备。
可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争。
可以备份和恢复单个分区。
对于查询分区表的SELECT语句，建议包含分区列，这样就只会在这个分区内查询，不用全表搜索，效率更快

分区的限制和缺点：

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

3、分表和表分区的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如银行交易流水记录根据时间每月一个表分成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表（虽然在物理层面上是有多个表文件），而分表则是将一张表分解成多张表。

如下：创建分区表后物里层面会有多个表文件

create table if not exists product
(
	id int not null ,
	name varchar(20) null comment '商品名称',
	type varchar(20) not null comment '商品类型',
    primary key(id,type)
) partition by key(type) partitions 4;  -- 根据商品类型进行分区

4、分区类型

范围分区（RANGE）：按照某个连续的范围将数据分区，例如按照订单日期进行分区。在创建表时，可以使用 PARTITION BY RANGE 子句来设置分区方式。
列表分区（LIST）：按照某个离散的列表将数据分区，例如按照订单状态进行分区。在创建表时，可以使用 PARTITION BY LIST 子句来设置分区方式。
哈希分区（HASH）：根据数据的哈希值将数据均匀地分散到多个分区中，可以提高查询和负载均衡的效率。在创建表时，可以使用 PARTITION BY HASH 子句来设置分区方式。
组合分区：将多个分区方式结合起来，例如先按照日期范围进行分区，再按照订单状态进行分区。在创建表时，可以使用 PARTITION BY RANGE/LIST/HASH 子句和 PARTITION BY SUBPARTITION 子句来设置组合分区方式。
自定义分区：MySQL 还支持自定义分区方式，可以通过 PARTITION BY subexpression 子句来设置自定义分区键。在分区函数中，可以使用 MySQL 内置的函数、用户自定义函数或存储过程等。

在使用分区表时，我们需要注意以下几个细节：

如果表中存在primary key 或者 unique key 时，分区的列必须是paimary key或者unique key的一个组成部分，也就是说，分区函数的列只能从pk或者uk这些key中取子集
如果表中不存在任何的paimary key或者unique key，则可以指定任何一个列作为分区列
使用分区表时，必须指定一个分区列。分区列的数据类型必须与分区方式对应。
如果需要创建多级分区，只需要在创建一级分区时，将第二级分区的信息一起写在括号中即可。
对于查询分区表的SELECT语句，必须包含分区列，并且只查询特定分区的数据时，需要使用类似“SELECT * FROM orders PARTITION (p2010)”这样的语法。
分区的命名默认是p0,p1,p2.........并且分区名称不区分大小写
全表查询时，分区表默认排序是按分区排序，而不是按主键排序

RANGE分区

https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html

RANGE分区是最常用的一种分区类型，基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

RANG分区特点：

根据分区键的值的范围把不同数据存储到表的不同分区中。
多个分区的分区键的值的范围要连续，但是不能重叠。
默认情况下使用VALUES LESS THAN属性，但每个分区并不包括指定的那个值。

创建RANGE分区

下面创建一个以id列为区间分区表，当id小于10时，数据插入p0分区；当id大于等于10小于20时，数据插入p1分区。

create  table  t(
  id int
)engine=innodb
partition by range(id)(
     partition  p0  values  less  than(10),-- 存放<10的数据
     partition  p1  values  less  than(20)-- 存放>=10  <20的数据
);

建立分区后，我们在插入数据时，mysql 会自动根据数据的值来将数据插插入到对应分区内。

insert into t values (9);
insert into t values (11);

-- 查看p0分区的数据
SELECT * FROM t PARTITION (p0);
-- 查看p1分区的数据
SELECT * FROM t PARTITION (p1);

在对表建立分区后，插入的数据的值应该严格遵守分区的定义，当插入一个不属于任何分区的值时，MySQL数据库会直接报错。

insert into t values (55)
> 1526 - Table has no partition for value 55
> 时间: 0.05s

为了避免这种情况发生，我们可以对分区添加一个MAXVALUE值的分区，MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值被放入p2分区。

alter table t add partition(partition p2 values less than maxvalue);

此时，所有 >=20 的值都会被插入到 p2 分区中。

对字段类型date, timestamp进行range分区

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE members (
    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 COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

LIST分区

https://dev.mysql.com/doc/refman/8.0/en/partitioning-list.html

LIST分区和RANGE分区类似，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

LIST分区的特点：

按分区键取值的列表进行分区
同范围分区一样，各分区的列表值不能重复
每一行数据必须能找到对应的分区列表，否则数据插入失败

CREATE TABLE employees (
    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 LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

HASH分区

https://dev.mysql.com/doc/refman/8.0/en/partitioning-hash.html

HASH分区的特点：

根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中
数据可以平均的分布在各个分区中
HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型

CREATE TABLE employees (
    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 HASH( YEAR(hired) )
PARTITIONS 4;

如果你在employees 中插入一条记录，它的hired值是’2005-09- 15’，那么它所存储的分区如下所示：

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

key分区

https://dev.mysql.com/doc/refman/8.0/en/partitioning-key.html

按键分区类似于按散列分区，不同之处在于散列分区使用用户定义的表达式，用于键分区的散列函数由MySQL服务器提供。NDB集群使用MD5（）来实现此目的;对于使用其它存储引擎的表，服务器采用其自身的内部散列函数。

key分区类似于创建按哈希分区的表的方法。主要区别如下：

使用KEY而不是HASH。
KEY只接受零个或多个列名的列表。任何用作分区键的列都必须包含表的部分或全部主键（如果表有主键的话）。如果没有将列名指定为分区键，则使用表的主键（如果有）。例如，下面的CREATE TABLE语句在MySQL 8.0中是有效的：

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY() -- 注意这里的key()没有指定列，则默认是按主键分区
PARTITIONS 2;

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

5、COLUMNS分区

COLUMNS分区是在MYSQL5.5中引入的分区类型，引入COLUMNS分区的目的是为了解决MYSQL5.5之前版本的RANGE分区和LIST分区只支持整数分区，需要通过额外函数计算得到整数或者额外的转换表转换成整数在分区，COLUMNS分区可以细分为RANGE COLUMNS分区和LIST COLUMNS分区，它们都支持整数、日期、字符串三大数据类型：

所有整数类型：tinyint、smallint、mediumint、int（Integer）、bigint，其他数值类型都不支持，如Decimal和Float
日期时间类型：date、datetime
字符类型：char、varchar、binary、varbinary，不支持text和blob类型作为分区键

注意：COLUMNS分区仅支持一个或者多个字段名作为分区键，不支持表达式作为分区键。(和RANGE分区和LIST分区不同)，如下使用多列组合的RANGE COLUMNS分区：

range columns

https://dev.mysql.com/doc/refman/8.0/en/partitioning-columns-range.html

RANGE COLUMNS分区在以下方面与RANGE分区有显著不同：

RANGE COLUMNS不接受表达式，只接受列的名称。
RANGE COLUMNS接受一个或多个列的列表。

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

CREATE TABLE rcx (
    a INT,
    b INT,
    c CHAR(3),
    d INT
)
PARTITION BY RANGE COLUMNS(a,d,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,MAXVALUE,MAXVALUE)
);

-- 对日期类型的分区，不再需要year()函数了，而直接使用columns
create table t_columns(
a int,
b datetime
)engine=innodb
partition by range columns (b) (
partition p0 values less than ('2009-01-01'),
partition p1 values less than ('2010-01-01')
);

-- 可以直接使用字符串的分区
create table t_columns2(
a int,
b datetime,
city varchar(15)
)engine=innodb
partition by list columns(city) (
partition p1 values in ('a','b','c'),
partition p2 values in ('d','e','f'),
partition p3 values in ('g','h','k'),
);

-- 可以使用多个列进行分区：
create table rcx (
a int,
b int,
c char(3)
)engine=innodb
partition by range columns(a,b,c) (
partition p0 values 
);

list columns

https://dev.mysql.com/doc/refman/8.0/en/partitioning-columns-list.html

CREATE TABLE customers_1 (
    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')
);

CREATE TABLE customers_2 (
    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(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

6、分区使用

查询数据库是否支持分区

从MySQL5.1开始引入分区功能

老版本（5.6之前）：

SHOW VARIABLES LIKE '%partition%';

新版本（5.6之后）：

show plugins;

显示所有插件，如果有partition - ACTIVE - STORAGE ENGINE - GPL 插件则表明支持分区

MySQL 8数据库的plugins里通常不会看见partition插件，因为PARTITION BY子句是MySQL的数据库引擎的一部分，它不是作为一个独立的插件提供的。

创建分区

create table if not exists product
(
	id int not null ,
	name varchar(20) null comment '商品名称',
	type varchar(20) not null comment '商品类型',
    primary key(id,type)
) partition by key(type) partitions 4;  -- 根据商品类型进行分区，创建4个分区


insert into product values
(1,'格力空调','空调'),
(2,'美的空调','空调'),
(3,'九阳电饭煲','电饭煲'),
(4,'苏泊尔电饭煲','电饭煲'),
(5,'小米手机','手机'),
(6,'华为手机','手机');

创建分区时，若只指定了分区数没有指定分区名称，则分区名称默认从0开始，p0,p1,p2…

创建分区并指定分区名称

create table if not exists tt
(
	id int not null primary key,
   name varchar(20) null comment '商品名称'
)engine=InnoDB partition by hash(id) partitions 3 (partition t1,partition t2,partition t3);

查看schema中哪些表是分区表

以及/每个表有几个分区/已使用了几个分区/分区名称/每个分区内的数据量

select * 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA=SCHEMA();

或者

select * 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA='your schema name';

以上图片中我们能看到schema为learn的库中所有表的信息：是否分区、分区名称、分区数量、分区类型(list,range,key,hash)、分区字段、每个分区内的数据量等等。

查询表是否是分区表

通过显示出来的ddl语句，发现该表是分区表

show create table product;

CREATE TABLE `product` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  `type` varchar(20) NOT NULL COMMENT '商品类型',
  PRIMARY KEY (`id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/*!50100 PARTITION BY KEY (`type`)
PARTITIONS 4 */

通过查询information_schema.PARTITIONS获得分区详细信息

select PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA='learn' AND TABLE_NAME='product';

查询指定分区内的数据

查询单个分区内的数据

select * from product partition (p1) ;

查询多个分区内的数据

select * from product partition (p2,p3) ;

分区与where组合查询

select * from product partition (p0,p1)  where id in (1,6);

有分区的表，查询数据默认按分区排序

CREATE TABLE `person` (
     `id` INT,
     `name` VARCHAR(50),
     `birthday` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(birthday) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000)
);

INSERT INTO person VALUES
(1, 'lava', '1998-12-25'),
(2, 'admin', '1993-11-05'),
(3, '张三', '1996-03-10'),
(4, '李四', '1982-01-10'),
(5, '王五', '1984-09-16'),
(6, '赵六', '1987-06-05'),
(7, 'tony', '1992-08-04');

id=4,5,6是p0分区；id=2,7是p1分区，id=1,3的p2分区

增加分区

-- range/list参考
alter table person add partition(PARTITION p6 VALUES LESS THAN (2020));

-- key/hash参考
alter table product add partition(PARTITION p6);
或者
alter table product add partition partitions 2;

注意：

只支持删除range、 list分区，不支持删除key、 hash分区。若添加key/hash分区时需注意。
对于key/hash分区，新增分区会导致之前表中的数据重新分区
对于RANGE分区的表，只可以添加新的分区到分区列表的最高端。
对于LIST分区的表，不能添加已经包含在现有分区值列表中的任意值。

-- 新增分区   新增的分区数值只能比之前的更大
alter table person add partition (PARTITION p3 VALUES LESS THAN (2005));

删除分区

注意：

只支持删除range、 list分区，不支持删除key、 hash分区
删除分区后，分区内的数据也会被删除

# list/range删除分区
alter table person drop partition p3;

# key/hash不能通过drop删除分区，可以通过coalesce减少分区
语法：ALTER TABLE table_name COALESCE PARTITION num
示例：alter table product coalesce partition 2; -- 将原来的分区减少两个

清空分区数据

# 清空某分区的数据
alter table person truncate partition p0;

删除所有分区但是保留数据

# 格式
ALTER TABLE table_name remove partitioning;

# 样例
alter table person remove partitioning;

重定义分区（拆分分区、合并分区、重命名）

# 格式  range/list
alter table tbl_name reorganize partition partition_list into(partition_definitions)

# key/hash不能通过REORGANIZE合并分区，可以通过coalesce减少分区，达到合并分区的效果
语法：ALTER TABLE table_name COALESCE PARTITION num
示例：alter table product coalesce partition 2; -- 将原来的分区减少两个

# 样例：range/list  合并分区
ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

#样例：range/list  拆分分区
alter table person reorganize partition p0,p1 into(partition s0 values less than(5),partition s1 values less than(10));
或者如：
alter table tbl_name reorganize partition p0 into(partition s0 values in(1,2,3), partition s1 values in(4,5));

注意：如果要拆分分区，则需将分区内的数据全部拆分完。

查询的时候使用到的分区

如果查询是基于分区表的话，会显示查询将访问的分区。在5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示filtered需要使用explain extended命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

explain select * from product where type ='手机';

其他分区管理语句

1.重建分区：类似于先删除保存在分区中的所有记录，然后重新插入它们，可用于整理分区碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

2.优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB或TEXT类型的列）做了许多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION来收回没有使用的空间，并整理分区数据文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

mysql8.0开始，ALTER TABLE … OPTIMIZE PARTITION 语句对于InnoDB分区表不再有效，使用如下语句代替：
ALTER TABLE … REBUILD PARTITION
ALTER TABLE … ANALYZE PARTITION

3.分析分区：读取并保存分区的键分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

4.检查分区：检查分区中的数据或索引是否已经被破坏，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

5.修补分区：修补被破坏的分区，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;