Mysql 分区表

最新推荐文章于 2025-07-09 14:39:21 发布

原创最新推荐文章于 2025-07-09 14:39:21 发布 · 300 阅读

2 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

30 篇文章

订阅专栏

本文详细介绍了Mysql中分区表的创建与管理，包括范围分区、列表分区、哈希分区、键分区和复合分区的创建方式，以及如何进行分区表的删除、增加、分解、合并等操作。同时，探讨了Mysql分区表的局限性，如唯一约束、NULL值处理、分区键类型等，并对比了分区表与普通表的性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

`Mysql 分区表`

1)创建Range分区表

1-1）：以员工工资为依据做范围分区

create table emp(
empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date,
salary int
)
partition by range(salary)
(partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than maxvalue);

1-2):以year(birthdate)表达式（计算员工的出生日期）作为范围分区依据。这里最值得注意的是表达式必须有返回值。

create table emp(
empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)partition by range(year(birthdate))
(partition p1 values less than (1980),
partition p2 values less than (1990,
partition p3 values less than maxvalue);

2)创建list分区

2-1):以部门作为分区依据，每个部门做一分区。

create table emp
(empno  varchar(20) not null ,
empname varchar(20),
deptno  int,
birthdate date not null,
salary int
)
partition by list(deptno)
(
partition p1 values in  (10),
partition p2 values in  (20),
partition p3 values  in  (30)
);

3)创建hash分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table emp(
empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)partition by hash(year(birthdate)) partitions 4;
#最后一个partition 4是平均分成4个分区

4)创建key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

create table emp
(
empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by key(birthdate)
partitions 4;

5)创建复合分区

5-1)range - hash(范围哈希)复合分区

create table emp
(empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by range(salary)
subpartition by hash(year(birthdate))
subpartitions 3
(
partition p1 values less than (2000),
partition p2 values less than maxvalue
);

5-2)

create table emp
(empno varchar(20) not null ,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by range(salary)
subpartition by key(birthdate)
subpartitions 3
(
partition p1 values less than (2000),
partition p2 values less than maxvalue
);

5-3)list - hash复合分区

CREATE TABLE emp (
empno varchar(20) NOT NULL,
empname varchar(20) ,
deptno int,
birthdate date NOT NULL,
salary int
)
PARTITION BY list (deptno)
subpartition by hash(year(birthdate))
subpartitions 3
(
PARTITION p1 VALUES in  (10),
PARTITION p2 VALUES in  (20)
)
;

5-4)list - key 复合分区

CREATE TABLE empk (
empno varchar(20) NOT NULL,
empname varchar(20) ,
deptno int,
birthdate date NOT NULL,
salary int
)
PARTITION BY list (deptno)
subpartition by key(birthdate)
subpartitions 3
(
PARTITION p1 VALUES in  (10),
PARTITION p2 VALUES in  (20)
)
;

6)分区表的管理操作

6-1)删除分区(不可以删除hash或者key分区)

alter table emp drop partition p1;

6-2)一次性删除多个分区

alter table emp drop partition p1,p2;

6-3)增加分区:

alter table emp add partition (partition p3 values less than (4000));
alter table empl add partition (partition p3 values in (40));

6-4)分解分区:

Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。

alter table tereorganize partition p1
into(partition p1 values less than (100),partition p3 values less than (1000)); 
#不会丢失数据

6-5合并分区:

Merge分区：把2个分区合并为一个。

alter table tereorganize partition p1,p3
into(partition p1 values less than (1000));
----不会丢失数据

6-6重新定义hash分区表:

Alter table emp partition by hash(salary)partitions 7;----不会丢失数据

6-7重新定义range分区表:

Alter table emp partitionbyrange(salary)(partition p1 values less than (2000),partition p2 values less than (4000)); ----不会丢失数据

6-8删除表的所有分区:

Alter table emp removepartitioning;
#不会丢失数据

6-9重建分区：

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

ALTER TABLE emp rebuild partitionp1,p2;

6-10优化分区:

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

ALTER TABLE emp optimize partition p1,p2;

6-11分析分区:

读取并保存分区的键分布。

ALTER TABLE emp analyze partition p1,p2;

6-12修补分区:

修补被破坏的分区。

ALTER TABLE emp repairpartition p1,p2;

6-13检查分区：:

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。ALTER TABLE emp CHECK partition p1,p2;这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

ALTER TABLE emp CHECK partition p1,p2;

7)【mysql分区表的局限性】

7-1）在5.1版本中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。

CREATE TABLE emptt (
empno varchar(20) NOT NULL  ,
empname varchar(20)，
deptno int,
birthdate date NOT NULL,
salary int ,
primary key (empno)
)
PARTITION BY range (salary)
(
PARTITION p1 VALUES less than (100),
PARTITION p2 VALUES less than (200)
);

这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table's partitioning function；
CREATE TABLE emptt (
empno varchar(20) NOT NULL  ,
empname varchar(20) ,
deptno int(11),
birthdate date NOT NULL,
salary int(11) ,
primary key (empno，salary)
)
PARTITION BY range (salary)
(
PARTITION p1 VALUES less than (100),
PARTITION p2 VALUES less than (200)
);

7-2）MySQL分区处理NULL值的方式

如果分区键所在列没有notnull约束。

如果是range分区表，那么null行将被保存在范围最小的分区。

如果是list分区表，那么null行将被保存到list为0的分区。

在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。

为了避免这种情况的产生，建议分区键设置成NOT NULL。

3.      分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分

区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。

 
4.      对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说。

5.      只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

6.      临时表不能被分区。

7-2）MySQL分区处理NULL值的方式

如果分区键所在列没有notnull约束。

如果是range分区表，那么null行将被保存在范围最小的分区。

如果是list分区表，那么null行将被保存到list为0的分区。

在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。

为了避免这种情况的产生，建议分区键设置成NOT NULL。

8) 获取mysql分区表信息的几种方法

1.     show create table 表名
可以查看创建分区表的create语句

2.     show table status 
可以查看表是不是分区表

3.     查看information_schema.partitions表 
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='test';  
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

4.     explain partitions select语句
通过此语句来显示扫描哪些分区，及他们是如何使用的.

9) 分区表性能比较

9-1创建两张表: part_tab(分区表),no_part_tab(普通表)

CREATE TABLE part_tab
(
c1 int default NULL, 
c2 varchar(30) default NULL,
c3 date not null
)
PARTITION BY RANGE(year(c3))
(PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) ,
 PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) ,
PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) ,
PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) ,
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN (MAXVALUE) );



CREATE TABLE no_part_tab
( 
c1 int default NULL, 
c2 varchar (30) default NULL,
c3 date not null
);

9-2)用存储过程插入800万条数据

CREATE PROCEDURE load_part_tab()
    begin
    declare v int default 0;
    while v < 8000000
    do
        insert into part_tab
        values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
         set v = v + 1;
    end while;
end;
insert into no_part_tab  select * frompart_tab;

10) 测试sql性能(分区表的执行时间比普通表少70%)

10-1 查询分区表：

select 
    count(*) 
from part_tab 
where c3 > date '1995-01-01'and c3 < date '1995-12-31';

10-2 查询普通表：

select 
    count(*) 
from no_part_tab 
where c3 > date '1995-01-01'and c3 < date '1995-12-31';

由于数据量少，性能提升没有多明显

11)通过explain语句来分析执行情况

分区表执行扫描了138000行，而普通表则扫描了212000行。
可以看出：分区表扫描的优势了。