mysql表分区 笔记_MySQL学习笔记十三：表分区

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分区有利于查询优化，快速删除过期数据，提高查询吞吐量等。