首页 > 数据库 > MySQL >

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

2016-09-15

MySql从5 1版本开始支持分区功能,分区的原则就是把一个数据库表分界为多个小的,容易管理的部分。就访问数据库而言,逻辑上只有一个表或一个索引,实际上可能由多个物理分区对象组成,每一个分区的对象都是一个独立的对象,可以单独处理,也可以作为表的一部分处理。

一 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
/*!50100 PARTITION BY RANGE (f_id)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB) */

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

这个地方关于/**/的含义是表示如果使用的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设置分区的最大值,避免局限于这几个数:

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),
-> partition p_max 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&#39;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
/*!50100 PARTITION BY RANGE (f_id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */;

******添加分区:

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
/*!50100 PARTITION BY RANGE (f_id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (100) ENGINE = InnoDB) */;
******拆分区:

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
/*!50100 PARTITION BY RANGE (f_id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (100) ENGINE = InnoDB) */;

******分区合并:

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
/*!50100 PARTITION BY RANGE (f_id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */;

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
/*!50100 PARTITION BY HASH (f_id)
PARTITIONS 4 */;

减少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
/*!50100 PARTITION BY HASH (f_id)
PARTITIONS 2 */;

增加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
/*!50100 PARTITION BY HASH (f_id)
PARTITIONS 12 */;

相关文章
最新文章
热点推荐