MySql(26)------mysql分区功能_网站建设

MySql(26)------mysql分区功能

2025-07-22 10:00 点击:0

一 MySql分区功能概要

MySql从5.1版本开始支持分区功能，分区的原则就是把一个数据库表分界为多个小的，容易管理的部分。

就访问数据库而言，逻辑上只有一个表或一个索引，实际上可能由多个物理分区对象组成，每一个分区

的对象都是一个独立的对象，可以单独处理，也可以作为表的一部分处理。不管我们怎么分区，不影响

应用的业务逻辑。

为什么要使用分区，分区的优点如下
（1）和单个磁盘或系统文件相比，分区可以存储更多数据。

（2）优化查询，准确定位。

（3）对于某些过去或一类不需要的数据，通过删除和这些数据相关的分区实现快速删除数据。

（4）跨多个磁盘来分散数据查询，获得更大的查询吞吐量。

通过SHOW VARIABLES LIKE '%partition%'命令确定当前MySql版本是否支持分区的功能。

mysql> show variables like '%partition%';

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set

如果我们看到了have_partitioning对应的是YES，说明该MySql版本支持分区功能。

使用show engines命令输出信息，不包含是否支持分区的信息，只能通过show variables命令判断是否支持分区功能。

MySql支持使用大部分存储引擎创建分区表，比如：MyISAM,InnoDB,Memory等存储引擎可以创建分区表,

但是不支持MERGE和CSV存储引擎创建分区表。同时注意的是一个表不能出现多种存储引擎创建的分区。

二分区类型介绍

MySql主要分区类型：

（1）RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。

（2）LIST分区：基于枚举出的列表值进行分区，与RANGE不同的是前者为枚举列表，后者基于区间范围。

（3）HASH分区：基于给定的分区个数，把数据分配到不同的分区。

（4）KEY分区：与HASH分区类似，都是先给定分区个数，然后把数据分配到不同的分区，只是算法有区别。

注意：

MySql无论使用哪种分区类型，分区字段的原则是：如果表上有主键或唯一键，就必须使用主键或唯一键作为

分区字段，否则，通过其他字段创建分区表，返回错误；如果表上没有主键或唯一键，可以使用表的任意字段

作为分区字段（一般为整数字段作为分区键，与分区类型有关）。

eg1，有主键时，用其他字段分区报错:

mysql> create table t_user_main(f_id int(11),f_bankid int(11),f_userName varchar(50),primary key(f_id))
-> partition by range(f_bankid)(
-> partition p0 values less than(100),
-> partition p1 values less than(200),
-> partition p2 values less than(300)
-> );
1503 - A PRIMARY KEY must include all columns in the table's partitioning function

eg2，有唯一键时，用其他字段分区报错:

mysql> create table t_user_main(f_id int(11) not null, f_userId int(11) not null, f_userName varchar(50),unique key(f_id))
-> partition by range(f_userId)(
-> partition p0 values less than(100),
-> partition p1 values less than(200),
-> partition p2 values less than(300)
-> );
1503 - A PRIMARY KEY must include all columns in the table's partitioning function

eg3，没有主键或唯一键时可以使用表中的任何字段分区,把上面的主键去掉:

mysql> create table t_user_main(f_id int(11),f_bankid int(11),f_userName varchar(50))
-> partition by range(f_id)(
-> partition p0 values less than(100),
-> partition p1 values less than(200),
-> partition p2 values less than(300)
-> );

通过show create table t_user_main或者图形界面查看表的定义语句：

CREATE TABLE `t_user_main` (  `f_id` int(11) DEFAULT NULL,  `f_bankid` int(11) DEFAULT NULL,  `f_userName` varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

我们会奇怪的发现表关于分区部分的定义被注释掉了，

这个地方关于的含义是表示如果使用的mysql版本支持分区，那么创建表的时候自然会按照分区创建表，

如果mysql不支持分区功能，哪就真的是注释掉了，自动的不使用分区部分的语句创建分区表，

而是注释掉分区语句，创建一个不分区的表，这是一种智能的结构。

三关于RANGE, LIST, HASH, KEY四种类型的分区使用

3.1 RANGE分区

RANGE分区是使用取值范围将数据分区，区间联系并且不能互相重复，使用VALUES LESS THAN进行分区定义。

eg：
创建一个t_user_main表，按照RANGE进行分区：

mysql> create table t_user_main(f_id int(11), f_userName varchar(200))
-> partition by range(f_id)(
-> partition p0 values less than(5),
-> partition p1 values less than(10),
-> partition p2 values less than(15)
-> );

插入一条数据：insert into t_user_main values(1,'one');

这条数据根据f_id为1，被保存到p0区，如果我们插入一个f_id为16的数据，
mysql> insert into t_user_main values(16,'sixteen');
1526 - Table has no partition for value 16

提示错误信息告诉我们分区中没有16这个值的容身之地，插入时分区的时候mysql不知道给分到哪里去，

自然报错了，这个地方有个临界值15, 如果插入15，也会报错，因为超出了范围，range给定的范围不包括最右边的值，

也就是说范围(5)表示......-1,0,1,2,3,4,而范围(10)表示5,6,7,8,9，范围(15)表示10,11,12,13,14

不包括最右边的值，说以插入15也会报找不到分区范围。

但是，我们可以通过values less than maxvalue设置分区的最大值，避免局限于这几个数：

通过partition p_max values less than (maxvalue)指定分区的最大插入值，

maxvalue表示最大整数来确定分区的上限值，修改完后再重新插入16的数据，显示插入成功，保存到p_max区。

RANGE分区最适合的两种场景：

（1）当我们需要删除过期，某些一类的数据时，通过ALTER TABLE t_user_main DROP PARTITION p0直接删除掉p0区的数据，

也即是f_id为......-1,0,1,2,3,4的数据被删除。如果是百万级别的数据，分区删除比通过delete删除效率高。

（2）当我们查询数据时，为了提高查询效率，分区可以让我们的sql减少全表扫描，直接定位分区扫描，提高查询效率。

比如select * from t_user_main where f_id > 12,直接扫描p2区扫描，查询数据。

3.2 LIST分区

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，通过values in(value_list)方式定义分区，

value_list是整数值列，多个值可以用逗号隔开，在5.5中可以通过非整数值字段定义分区。

eg:
mysql> create table t_user_main (f_id int(11), f_userName varchar(200))
-> partition by list(f_id)(
-> partition p0 values in(2,5),
-> partition p1 values in(1,10),
-> partition p2 values in(6,8),
-> partition p3 values in(9)
-> );

插入数据mysql> insert into t_user_main values (1,'one')保存在p1区；

插入数据mysql> insert into t_user_main values (2,'two')保存在p0区；

插入数据mysql> insert into t_user_main values (100,'hundred')报错为

1526 - Table has no partition for value 100，说明没有找到对应的分区，

这个地方与RANGE分区不同，LIST分区没有values less than maxvalue这些说法，

从枚举数值集合中如果找不到分区值，就报错，没有最大值之说。

需要注意的是每个枚举列表集合中的值不能重复，否则报错：

mysql> create table t_user_main3(f_id int(11) not null, f_userName varchar(50),primary key(f_id))
-> partition by list(f_id)(
-> partition p0 values in (1,2),
-> partition p1 values in (1,2),
-> partition p2 values in (2,3)
-> );
1495 - Multiple definition of same constant in list partitioning

3.3 HASH分区

HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

当一个表执行HASH分区时，MySql会对分区键应用一个散列函数，以确保数据应当存放在N个分区

中的哪个分区中。

MySql支持两种HASH分区，分别为常规HASH分区和线性HASH分区(LINEAR HASH分区)，

其中常规HASH分区使用的是取模算法，线性HASH分区使用的是一个线性的2的幂运算法则。

先创建一个常规HASH分区，使用PARTITION BY HASH(expr) PARTITIONS num字句定义分区的类型，

分区键和分区个数，其中expr是某个列或基于某个列返回整数值的表达式，num是一个非负整数，

表示分割的分区数量，默认值为1。

区数N的计算公式N = MOD(expr,num),求出的N是几，数据就保存到第几分区。

eg:
mysql> create table t_user_main(f_id int(11), f_userName varchar(200))
-> partition by hash(f_id) partitions 4;

插入数据mysql> insert into t_user_main values(5,'five');

通过取模算法可以算出该数据应该被保存到哪个区，N=MOD(expr,num)=MOD(5,4)=1,所以该数据被保存到第一区。

这个时候如果我们执行查询select * from t_user_main where f_id = 5会确定到第一个区查询数据。

使用常规HASH分区会有一个问题，一开始N=MOD(expr,num)，如果我们增加分区，将t_user_main表从4个分区

变成5个分区，取模就会发生变化，先前的数据合并的时候就会出问题。

为了解决这种常规HASH中出现的分区增减困难问题，降低管理的代价，MySql提供了线性HASH分区。

eg:
mysql> create table t_user_main(f_id int(11), f_userName varchar(200))
-> partition by linear hash(f_id) partitions 4;

插入数据mysql> insert into t_user_main values (234,'testLH')，下面开始计算保存到哪个分区，

与常规HASH分区比，指定类型时多了个linear，假设将要保存的记录分区编号为N,

num是一个非负的整数，也就是分区数量，那么得到N的算法如下：

1. 找到下一个大于或等于num的2的幂，我们把这个值称为V ，V可以通过下面的公式得到：

V = POWER(2, CEILING(LOG(2, num)))

例如，咱们创建的t_user_main分区数num是4。

那么LOG(2,4)就是2，

CEILING(2)就是2，

则V = POWER(2,2),

即V=4

2. 设置 N = F(column_list) & (V - 1) = 234&(4-1) = 2

3. 当 N >= num;

设置 V = CEIL(V / 2)

设置 N = N & (V - 1)

当N < num时，N就是保存的区数，所以，刚才插入的数据被保存到2分区。

总结线性HASH分区：

优点：分区维护时，处理速度相对常规分区较快。

缺点：相对于常规分区取模算法，线性分区计算的数据保存到各个分区不均匀。

3.4 KEY分区

按照KEY进行分区类似于HASH分区，只是这个地方使用的是mysql服务提供的HASH()函数，

HASH分区只支持整数分区，5.5也支持了其他类型分区，而KEY分区支持除了BLOG和TEXT类型外的列作为分区键,

分区算法使用的是线性HASH分区时2的幂算法得到数据保存的区。

eg:
mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
-> partition by key(f_id) partitions 4;

与HASH分区不同的是，KEY分区可以不指定分区键，如果有主键时，自动使用主键作为分区键：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200),primary key(f_id))
-> partition by key() partitions 4;

如果没有主键时，就选择非空唯一键作为分区键：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200),unique key(f_id))
-> partition by key() partitions 4;

如果没有主键，没有非空唯一键，这个时候要想分区就必须制定分区键，否则报错：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
-> partition by key() partitions 4;
1488 - Field in list of fields for partition function not found in table

如果没有主键，没有非空唯一键，指定分区键时可以分区：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
-> partition by key(f_id) partitions 4;

上面是一个递进关系，特别注意的是，当存在主键或唯一键时，必须用主键或唯一键作为分区键，

如果这个时候使用其他键作为分区键，将报错告诉你，必须用主键或唯一键作为分区键：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200), primary key(f_id))
-> partition by key(f_userName) partitions 4;
1503 - A PRIMARY KEY must include all columns in the table's partitioning function

四分区管理

分区管理通过alter table命令实现对分区的添加，删除，重定义，合并，拆分。

4.1 RANGE和LIST分区管理：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
-> partition by range(f_id)(
-> partition p0 values less than(5),
-> partition p1 values less than(10),
-> partition p2 values less than(15)
-> );
******删除分区：

mysql> alter table t_user_main drop partition p2;

删除分区后的表结构:

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

******添加分区:

mysql> alter table t_user_main add partition(partition p2 values less than(100));

添加分区后的表结构:

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
******拆分区:

mysql> alter table t_user_main reorganize partition p2 into(
-> partition p2 values less than(50),
-> partition p3 values less than(100)
-> );

拆完分区之后的表结构：

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

******分区合并:

mysql> alter table t_user_main reorganize partition p1,p2,p3 into(partition p1 values less than(100));

合并分区后的表结构:

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

4.2 HASH和KEY分区管理

创建一个HASH类型4分区表：

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

减少2个分区：

mysql> alter table t_user_main coalesce partition 2;

减少后的表结构：

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

增加10个分区：

mysql> alter table t_user_main add partition partitions 10;

增加10个分区后变成12个分区的表结构:

CREATE TABLE `t_user_main` (
`f_id` int(11) NOT NULL,
`f_userName` varchar(200) DEFAULT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;