分区模式:
1.RANGE(范围)分区:基于属于一个给定连续区间的列值,把多行分配给分区。MySQL5.5开始支持RANGE COLUMNS的分区。
2.HASH(哈希)分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。通过对表的一个
或多个列的hash key进行计算,最后通过这个hash码不同数值对应的数据区域进行分区。
3.KEY(键值)分区:类似于按HASH分区,区别在于key分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。Key分区是Hash模式的一
种延伸。
4.LIST(预定义列表)分区:类似于按Range分区,区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择。MySQL5.5开始支持
RANGE COLUMNS的分区。
不论创建何种类型的分区,如果表中存在主键或者是唯一索引时,分区列必须是唯一索引的一个组成部分。否则会报如下错误。
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
当创建表时没有指定主键,唯一索引时,可以指定任何一个列为分区列。
RANGE分区:
表结构如下:
CREATE TABLE `stat_counts` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`href` varchar(255) NOT NULL,
`domain_id` int(8) NOT NULL,
`domain_name` varchar(32) NOT NULL,
`domain` varchar(32) NOT NULL,
`pv_count` int(10) NOT NULL,
`ip_count` int(10) NOT NULL,
`swaraj_count` int(10) NOT NULL,
`swaraj_new_count` int(10) NOT NULL,
`day` int(10) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8
根据day列分区,每三个月为一个分区。
mysql> alter table stat _counts modify `id` int(8) NOT NULL ;
mysql> alter table stat _counts drop primary key;
mysql> alter table stat _counts add primary key(id,day);
mysql> alter table stat _counts modify `id` int(8) NOT NULL AUTO_INCREMENT;
mysql> alter table stat_counts PARTITION BY RANGE (day)
-> (PARTITION p201103 VALUES LESS THAN (1298908800) ,
-> PARTITION p201106 VALUES LESS THAN (1306857600) ,
-> PARTITION p201109 VALUES LESS THAN (1314806400) ,
-> PARTITION p201112 VALUES LESS THAN (1325347200) ,
-> PARTITION p201203 VALUES LESS THAN (1330531200) ,
-> PARTITION P999999 VALUES LESS THAN MAXVALUE);
LIST分区:
LIST分区和RANGE分区非常相似,只是分区列的值是离散的而不是连续的。不同于RANGE分区中定义的VALUES LESS THAN语句,LIST分区使
用VALUES IN,离散值只能是定义的值。
mysql> create table stat_id (
-> id int,
-> department_id int)engine=innodb
-> partition by list(department_id)(
-> partition P0 values in (1,3,5,7,9),
-> partition P1 values in (0,2,4,6,8));
HASH分区:
HASH分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致一样。
mysql> create table stat_date (
-> id int,
-> day datetime)engine=innodb
-> partition by hash (year(day))
-> partitions 10; //分区的数量
数据的分布按照mod(year(day),10)来进行的。
mysql> select count(*) from stat_date;
+-------------+
| count(*) |
+-------------+
| 0 |
+-------------+
mysql> insert into stat_date values (1,'2011-4-22');
mysql> select table_name,partition_name ,table_rows
from information_schema.partitions
where table_name='stat_date';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| stat_date | p0 | 0 |
| stat_date | p1 | 1 |
| stat_date | p2 | 0 |
| stat_date | p3 | 0 |
| stat_date | p4 | 0 |
| stat_date | p5 | 0 |
| stat_date | p6 | 0 |
| stat_date | p7 | 0 |
| stat_date | p8 | 0 |
| stat_date | p9 | 0 |
+------------+----------------+------------+
mysql> select mod(year('2011-4-22'),10) ;
+---------------------------+
| mod(year('2011-4-22'),10) |
+---------------------------+
| 1 |
+---------------------------+
KEY分区:
KEY分区和HASH分区相似,不同在于,HASH分区使用用户自定义的函数进行分区,KEY分区使用MySQL数据库提供的函数进行分区。
mysql> create table stat_date_2 (
-> id int,
-> day datetime)engine=innodb
-> partition by key(day)
-> partitions 10;
分区的编号是通过2的幂(powers-of-two)算法得到的。powers-of-two算法是如何计算的请参考具体文档。
COLUMNS分区:
RANGE,LIST,HASH,KEY分区的条件必须的整型int,如果不是整型需要通过函数将其转换成整型。
mysql> create table stat_test ( id int, value char(10)) partition by hash(value) partitions 10;
ERROR 1491 (HY000): The PARTITION function returns the wrong type
mysql> create table stat_test ( id int, value char(10)) partition by hash(id) partitions 10;
Query OK, 0 rows affected (0.02 sec)
MySQL5.5版本开始支持COLUMNS分区。COLUMNS分区可以直接使用非整型的数据进行分区。还可以对多个列的值进行分区。
COLUMNS分区支持的数据类型有:
1.所有整型。FLOAT,DECIMAL类型不支持。
2.日期类型。
3.字符串类型。BLOB,TEST类型不支持。
mysql> create table stat_domain (
-> id int,
-> domain_id int,
-> domain varchar(15))
-> partition by list columns(domain) (
-> partition P0 values in ('http://hd.xuhh.com'),
-> partition P1 values in ('http://img.xuhh.com'),
-> partition P2 values in ('http://cms.xuhh.com'));
对多列进行分区:
mysql> create table stat_post (
-> id int,
-> pid int,
-> fid int)
-> partition by range columns(id,pid,fid) (
-> partition P0 values less than (5,5,10),
-> partition P1 values less than (10,10,15),
-> partition P2 values less than (15,15,20),
-> partition P3 values less than (maxvalue,maxvalue,maxvalue));
mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='stat_post';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| stat_post | P0 | 0 |
| stat_post | P1 | 0 |
| stat_post | P2 | 0 |
| stat_post | P3 | 0 |
+------------+----------------+------------+
4 rows in set (0.00 sec)
mysql> insert into stat_post value (1,6,18);
Query OK, 1 row affected (0.00 sec)
mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='stat_post';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| stat_post | P0 | 1 |
| stat_post | P1 | 0 |
| stat_post | P2 | 0 |
| stat_post | P3 | 0 |
+------------+----------------+------------+
4 rows in set (0.01 sec)
mysql> insert into stat_post value (5,6,18);
Query OK, 1 row affected (0.00 sec)
mysql> select table_name,partition_name ,table_rows from information_schema.partitions where table_name='stat_post';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| stat_post | P0 | 1 |
| stat_post | P1 | 1 |
| stat_post | P2 | 0 |
| stat_post | P3 | 0 |
+------------+----------------+------------+
分区的分布是按照分区列的顺序进行判断的。
子分区:
子分区是在分区的基础上再进行分区。MySQL数据库允许在RANGE,LIST的分区上再进行HASH或者KEY的子分区。
子分区的建立注意的问题:
1.每个子分区的数量必须相同。
2.如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区,那么就必须定义所有的子分区。
3.每个SUBPARTITION子分区必须包括子分区的一个名称。
4.在每个分区内,子分区的名称必须是唯一的。
分区的维护:
1.重建分区:
alter table table_name rebuild partition (p0,p1);
2.优化分区:
alter table table_name optimize partition (p0,p1);
3.分析分区:
alter table table_name analyze partition (p0,P1);
4.修复分区:
alter table table_name repair partition (p0,p1);
5.检查分区:
alter table table_naem check partition (p0,p1);
转载于:https://blog.51cto.com/who0168/552442