MySQL数据库分区表总结

MySQL数据库分区总结


(1)MySQL支持水平分区不支持垂直分区,此外,MySQL数据库的分区是局部分区索引,一个分区既包括数据也包括索引.


(2)首先查看数据库是否启用分区功能
mysql> show variables like '%partition%'\G;
Variable_name: have_partitioning
Value : Yes


(3)当前MySQL支持的分区类型
RANEG分区,Mysql5.5可以使用RANGE COLUMNS分区
LIST分区,Mysql5.5可以使用LIST COLUMNS分区
HASH分区,根据自定义表达式返回值分区.返回值不能是负数
KEY分区,根据哈希函数分区
注意:不管创建哪种分区,如果表中存在主键或者唯一键,分区列必须是唯一索引的一个组成部分,唯一索引可以是Null.当表没有主键和分区的时候可以指定任何一个列做为分区列
例1:
有唯一索引
create table test1 (
a1 int null,
a2 date null,
a3 int null,
a4 int null,
unique key (a1,a2,a3,a4
)
partition by hash(a2)
partition 4;


例2:
无主键和唯一索引
create table test1 (
a1 int null,
a2 date null,
a3 int null,
a4 int null,
)engine=innodb
partition by hash(a2)
partition 4;


create table test1 (
a1 int null,
a2 date null,
a3 int null,
a4 int null,
key(a4)
)engine=innodb
partition by hash(a2)
partition 4;


(4)RANGE分区
例1:
create table test2 (
id int)engne=innodb
partition by range (id)(
partition a0 values less than (10),
partition a1 values less than (20),
partition a2 values less than (30));
磁盘的物理文件会显示
test2.frm 
test.par
t##A#a0.ibd
t##A#a1.ibd
添加无穷大的分区
alter table test2 add partition (partition a3 value less than maxvalue);
利用to_year函数进行分区
create table sales(
money int unsigned not null,
date datetime
)engine=innodb
partition by range(YEAR(date)) (
partition p2011 values less than (2011),
partition p2012 values less than (2012),
partition p2013 values less than (2013),
);
删除一个分区
alter table sales drop partition p2011;
查看优化器特定分区扫描
explain partitions select * from sales where date>='2011-01-01' and date<='2011-12-31'\G;


(5)LIST分区
create table test2 (
a int,
b int) engine=innodb
partition by list(a)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8)
);




(6)HASH分区
partition分两种
partition by hash (expr) expr返回整数的表达式
partition by (num) num分区的数量
create table test2 (
a int,
b datetime) engine=innodb
partition by hash (YEAR(b))
partition 6;  ---分区数量


linear hash分区
create table test2(
a int,
b datetime) engine=innodb
partition by linear hash(year(b))
partition 6;


(7)KEY分区
KEY分区和hash分区区别,key使用MySQL自带函数分区,hash使用自定义函数分区.NDB使用MD5函数进行分区
create table test2 (
a int,
b datetime) engine=innodb
partition by key (b)
parttion 6;
KEY分区也可以使用关键字LINEAR


(8)MySQL5.5的COLUMNS分区
COLUMNS可以直接使用非整数分区,RANGE COLUMN可以对多个列的值进行分区
create table test2 (
a int,
b datetime) engine=innodb
partition by range columns (b)(
partition p1 values less than ('2011-01-01'),
partition p2 values less than ('2012-01-01')
);


(9)子分区
MySQL支持在RANGE、LIST分区的基础上在进行HASH、KEY子分区 
create table test2 (
a int,
b datetime)engine=innodb
partition by range(year(b))
subpartition by hash(to_days(b))
subpartitions 4 (
partition p1 values less than (2010),
partition p2 values less than (2011),
partition p3 values less than (2012),
partition p4 values less than maxvalue
);


(10)分区中的Null处理
RANGE分区插入Null会存在最左边的分区
LIST分区插入Null必须显示指定放入哪个分区否则报错
HASH、KEY分区插入Null都会返回0