1.分区一般用于非常大的表,采用“分而治之”的策略,将一个很大的对象分成多个小对象进行管理,每个分区都是一个独立的对象。
分区使用分区键将数据根据范围值,特定列值或HASH值等规则分布在不同的分区中。查看当前MySQL是否支持分区,如下所示。
mysql> show variables like '%partition%'; --或者使用select @@have_partitioning;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
2.分区类型,主要分为以下四种:
RANGE:基于一个连续的区间范围,将数据分配到不同的分区。
LIST:基于枚举出的值列表分区
HASH:基于给定的分区数,将数据分配到不同的分区
KEY:类似于HASH分区,但不允许用户自定义表达式
3.设置表的分区,类似指定表的存储引擎,分区不支持CSV和merge引擎对象,如下所示
create tablestudent(
snoint not null,
snamevarchar(30) not null,
sclassvarchar(10) not null,
sageint not null,
sgendervarchar(6)
)engine=InnoDB default charset=utf8
partitionbylist columns(sgender)(
partition p0values in ('男'),
partition p1values in ('女')
);
不管哪种分区类型,分区键必须是主键或唯一键,除非两者都没有,否者将会报如下错误。
Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function
4.RANGE分区
RANGE分区利用取值范围来进行分区,区间必须连续且不重叠,使用values less than进行分区定义,分区键必须是INT,或者表达式返回INT。
create tableusers_par(
idint not null,
usrNamevarchar(50) not null,
usrEmailvarchar(50) not null,
ageint not null,
regDate datenot null)engine=InnoDB default charset=utf8
partitionbyrange(age)(
partition p0values less than(20),
partition p1values less than(30),
partition p2values less than maxvalue --防止插入大于30岁的用户,出现错误
);
如果是将注册日期作为分区键,则须要使用日期处理函数转换为整型,例如year(regDate),to_days(regDate),to_seconds(regDate),且只支持这三个函数。
或者使用RANGE COLUMNS分区,则不需要转换日期,如下所示
create tableusers_par(
idint not null,
usrNamevarchar(50) not null,
usrEmailvarchar(50) not null,
ageint not null,
regDate datenot null)engine=InnoDB default charset=utf8
partitionbyrange columns(regDate)(
partition p0values less than('2005-05-05'),
partition p1values less than('2009-09-09'),
partition p2values less than('2015-05-05'),
);
RANGE分区特别适用于删除过期数据或者某范围数据,只需要alter table tbl_name truncate partition partition_name即可,比delete语句效率要高很多,还有就是经常使用分区键的查询,可以提高查询性能,因为只需扫描某些分区就OK,如下所示。
mysql> explain partitions select * from users_par where age=30;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | users_par | p2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
5.LIST分区
LIST分区是建立离散的值列表来实现特定的值属于哪个分区,使用partition by list来实现,values in 来定义。
CREATE TABLE`student` (
`sno`int(11) NOT NULL,
`sname`varchar(30) NOT NULL,
`sclass`varchar(10) NOT NULL,
`sage`int(11) NOT NULL,
`sgender`varchar(6) DEFAULT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITIONBYLIST COLUMNS(sgender)(
PARTITION p0VALUES IN ('男'),
PARTITION p1VALUES IN ('女')
);
如果试图插入的值不在分区值列表中,插入语句将会报错,将要匹配的值必须在分区值列表中找到。
6.COLUMNS分区
columns分区是对range,list分区的补充,弥补了后两者只支持整型数分区(或者通过转换为整型数),使得支持数据类型增加很多(所有整数类型,日期时间类型,字符类型),还支持多列分区。
columns分区可细分为range columns分区和list columns分区,多列分区示例:
create tablerange_columns(
aint not null,
bint not null)
partitionbyrange columns(a,b)(
partition p0values less than(0,10),
partition p1values less than(10,20),
partition p2values less than(10,30),
partition p3valuesless than(maxvalue,maxvalue)
);
在多列分区表上插入数据时,采用元组的比较,即多列排序,先根据field1排序,再根据field2排序,根据排序结果来来分区存储数据。
mysql> insert into range_columns values (0,9);
Query OK,1 row affected (0.00sec)
mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema()and table_name='range_columns';+----------------+----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS |
+----------------+----------------------+------------+
| p0 | `a`,`b` | 1 |
| p1 | `a`,`b` | 0 |
| p2 | `a`,`b` | 1 |
| p3 | `a`,`b` | 0 |
+----------------+----------------------+------------+
4 rows in set (0.01 sec)
7.HASH分区
HASH主要是为了让数据在设定个数的分区中尽可能分布平均,执行哈希分区时,mysql会对分区键执行哈希函数,以确定数据放在哪个分区中。HASH分区分为常规HASH分区和线性HASH分区,前者使用取模算法,后者使用线性2的幂的运算规则。HASH分区示例如下,
create tablehash_par(
idint not null,
namevarchar(50) not null)
partitionby hash(id) partitions 4; ----如果要指定为线性hash,可以使用partition by linear hash
插入一个id为31的数据,如下所示
mysql> insert into hash_par values (31,'zhumuxian');
Query OK,1 row affected (0.00sec)
mysql> explain partitions select * from hash_par where id=31;+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | hash_par | p3 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
HASH分区尽可能让数据平均地分布在每个分区上,提高了查询效率,但是增加了分区管理的代价,比如以前有5个分区,现在要加上一个分区,算法有mod(expr,5)变成(expr,6),原来5个分区的数据大部分要重新结算重新分区。虽然使用线性HASH分区会降低分区管理的代价,但是数据却没有常规HASH分布得那么均匀。
8.KEY分区
KEY分区类似与HASH分区,但是不能自定义表达式,不过支持分区键的类型很多,除却Text,Blob等文本类型。KEY分区的设置如下:
create tablehash_par(
idint not null,
namevarchar(50) not null)
partitionby key(id) partitions 4;
KEY分区的分区键可以不指定,默认会使用表的主键作为分区键,如果没有主键,就使用唯一键,两者都没有的话就必须指定分区键了。
9.子分区(关键字subparttition)
RANGE分区或者LIST分区可以再次分割形成子分区,子分区可以是HASH分区或者KEY分区,例如:
create tableusers_par(
idint not null,
usrNamevarchar(50) not null,
usrEmailvarchar(50) not null,
ageint not null,
birth datenot null)engine=InnoDB default charset=utf8
partitionbyrange(age)
subpartitionbyhash(age)
subpartitions2(
partition p0values less than(20),
partition p1values less than(30),
partition p2valuesless than maxvalue
);
10.分区时NULL值的处理
一般情况下,MYSQL把NULL当成零值后者最小值来处理。
在RANGE分区中,NULL会被当成最小值处理;LIST分区中,NULL值必须出现在枚举值列表中,否则报错;HASH/KEY分区中,NULL值被当成零值处理。
mysql> insert into student values (4010404,'zhumuxian','A1114',20,'男');
Query OK,1 row affected (0.04sec)
mysql> insert into student values (4010405,'zhongzhaoxi','A1114',20,NULL);
ERROR1526 (HY000): Table has no partition for value fromcolumn_list
mysql> select PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS frominformation_schema.partitions where table_schema=schema() and table_name='student';+----------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+----------------------+-----------------------+------------+
| p0 | `sgender` | '男' | 1 |
| p1 | `sgender` | '女' | 0 |
+----------------+----------------------+-----------------------+------------+
2 rows in set (0.01 sec)
11.分区管理
RANGE和LIST分区在删除,添加,重新定义等分区管理上非常类似,如下所示。
删除分区(alter table tbl_name drop partition partition_name),分区被删除后,该分区的数据一起被删除。
mysql> alter table users_par droppartition p0;
Query OK,0 rows affected (0.24sec)
Records:0 Duplicates: 0 Warnings: 0
添加分区(alter table tbl_name add partition)
mysql> alter table users_par add partition (partition p0 values less than (20));
ERROR1481 (HY000): MAXVALUE can only be used inlast partition definition--这里报错是因为添加分区必须在原分区的最大端添加,在为LIST分区添加分区时,新分区的值列表的值不能包含任意一个现有分区中值列表中的值,否则报错
mysql> alter table student add partition (partition p2 values in ('男'));
ERROR1495 (HY000): Multiple definition of same constant in list partitioning
重新定义分区(alter table tbl_name reorganize partition partition_name into),可以将一个分区拆开成多个,反之可以合并多个成一个或多个。
mysql> alter table users_par reorganize partition p1 into (partition p0 values less than (20),partition p1 valuesless t
han(30));
Query OK,1 row affected (0.05sec)
Records:1 Duplicates: 0 Warnings: 0
需要注意的是:RANGE和LIST分区在重新定义时,只能重新定义相邻的分区,不可以跳过分区,并且重新定义的分区区间必须和原分区区间一致,也不可以改变分区的类型。
HASH和KEY分区的管理
减少分区数量,使用coaleace关键字
mysql> alter table hash_par coalesce partition 2;
Query OK,1 row affected (0.04sec)
Records:1 Duplicates: 0 Warnings: 0
增加分区数量
mysql> alter table hash_par add partition partitions 2;
Query OK,1 row affected (0.04sec)
Records:1 Duplicates: 0 Warnings: 0
MySQL分区有利于查询优化,快速删除过期数据,提高查询吞吐量等。