分区表
分区概念
- 分区功能并不是在存储引擎层完成的,因此不止有InnoDB存储引擎支持分区,常见的存储引擎MyISAM,NDB等都支持。但是也并不是所有存储引擎都支持,比如CSV,FEDERATED,MERGE等就不支持,在使用分区功能之前应该先了解选择的存储引擎是否支持分区操作。
- MySQL是在5.1版本之后添加了对于分区的支持。功能是将表或者索引分解成更小的多个,更可管理的部分。就访问数据库的应用而言,逻辑上还是一个表或者一个索引,但是物理上这个表或者索引可能由数十个物理分区组成,每个分区都是独立的对象,可以独立出来,也可以作为一个更大的对象的一部分进行处理。
- MySQL数据库支持的分区类型是水平分区(指同一个表中不同行的记录分配到不同的物理文件),不支持垂直分区(指将同一个表中不同的列分配到不同的物理文件)
- MySQL数据库的分区是局部分区索引,一个分区中即存放了数据又存放了索引。
- 如下命令查看是否开启分区功能:
show variables like '%partition%'
show plugins;
分区功能
- 我们分区的作用一般是为了让数据库变得更快,但是启用分区后,并不一定会变快。通过经验来看分区对某些SQL语句的性能会带来提高,但是分区主要用于高可用,更利于数据库的管理。
- 在OLTP应用中,对于分区的使用应该更小心。因为OLTP类型的数据库对性能要求特别高,也行,分表分区能够对某一批查询效率提升,但是可能对其他某些查询是致命的,导致他需要遍历N个库的全量数据,大大增加了IO次数。
- 先了解当前MySQL数据库支持的以下几种类型的分区:
- RANGE分区:行数据基于属于一个给定连续区间的值放入分区。MySQL数据库5.5开始支持RANGE COLUMNS的分区
- LIST分区:和RANGE分区类似,只是LIST分区面向的是离散的值。同样5.5开始支持LIST COLUMNS的分区
- HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数。
- KEY分区:更具MySQL数据库提供的哈希函数进行分区
错误案例
- 注意,不论任何类型分区,如果表中存在主键或者唯一索引时候,分区必须是唯一索引的一个组成部分,因此如下案例会报错:
create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null,
UNIQUE key(coll1, coll2)
)
PARTITION by Hash(coll3)
PARTITIONS 4;
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function
- 将PARTITION by Hash(coll3) 修改成PARTITION by Hash(coll1)或者coll2 就能正确创建
唯一索引情况
- 唯一索引可以允许是NULL值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引列都是分区列
create table t1(
coll1 int null,
coll2 date null,
coll3 int null,
coll4 int null,
UNIQUE key(coll1, coll2,coll3, coll4)
)
PARTITION by Hash(coll3)
PARTITIONS 4;
没有主键或唯一索引情况
- 当建表时没有指定主键,唯一索引,可以指定任何一个列为分区列,如下两种情况都可以正确创建案例:
create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null
)
PARTITION by Hash(coll3)
PARTITIONS 4;
create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null,
key (coll4)
)
PARTITION by Hash(coll3)
PARTITIONS 4;
RANGE分区
- RANGE分区是常用的一种分区类型。如下案例,我们create table语句创建了一个id列的区间分区表。当id小于10 时候,数据插入P0分区。当大于等于10 小于等于20 插入p1:
create table t(
id int
)ENGINE=INNODB
PARTITION by RANGE(id)(
PARTITION p0 VALUES less than(10),
PARTITION p1 VALUES less than(20)
);
insert into t values(9),(10),(15)
- 插入数据后,因为t根据id进行分区,因此数据是根据id列的值的范围存放在不同的物理文件中,可以通过查information_schema 架构在的 PARTITIONS 表来查看每个分区的具体信息,在数据库中有一个 information_schema数据库
select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';
- TABLE_ROWS列说明了每个分区记录中记录的数量,由于之前项表中插入了9,10,15三条记录,因此可以看到,当前分区P0有一条记录,分区p1 有2条记录,PARTITION_METHOD 标识分区的类型,这里显示的是RANGE
- 对于表t,因为我们定义了分区,因此对于插入的值都严格遵循分区的定义,当插入不属于分区的数据时候,会抛出异常:
[SQL]
insert INTO t select 30;
[Err] 1526 - Table has no partition for value 30
- 应该添加新的范围,使得数据库分区能够兼容所有的数据清空,如下:
alter table t add PARTITION(PARTITION p2 values less than MAXVALUE);
insert INTO t select 30;
RANGE日期分区
- RANGE分区主要用于日期列的分区,如对于销售类的表,可以根据年份来分区存储销售数据,如下案例:
create table sales(
money int UNSIGNED not null,
date datetime
)ENGINE=INNODB
PARTITION by range(YEAR(date))(
PARTITION p2008 values less than(2009),
PARTITION p2009 values less than(2010),
PARTITION p2010 values less than(2011)
);
insert into sales values(100, '2008-01-01'),
(100, '2008-02-01'),
(200, '2008-01-02'),
(100, '2009-03-01'),
(200, '2010-03-01')
select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 'sales';
- 如上创建好,可以对sales这张表按时间处理账本,如果我们需要删除18年的数据,就不需要去delete from sales where date >=‘2008-01-01’ and date < ‘2009-01-01’ ,我们可以直接删掉p2008分区即可,如下操作
alter table sales drop PARTITION p2008;
- 分区操作还有另外一个好处,可以加快某些查询的操作,如果我们只需要查询19年的销售额,如下:
EXPLAIN partitions select * from sales where date >='2009-01-01' and date < '2009-12-31'
’
- 如上通过explain partitions 命令,上述语句sql优化器只需要搜索p2009分区,不用去全表扫描数据,大大提高执行速度,但是如上语句则不同:
EXPLAIN partitions select * from sales where date >='2009-01-01' and date < '2020-01-01'
-
条件修改成了< 2020-01-01,这个时候优化器选择p2009,p2010两个分区,这是不希望看到的。因此对于启用分区应该更具分区的特点编写sql语句
-
如果我们需要更细分,按月来分区,如下分区方式第查询来说是无效的,如下:
create table sales(
money int UNSIGNED not null,
date datetime
)ENGINE=INNODB
PARTITION by range(YEAR(date)*100+MONTH(date))(
PARTITION p201001 values less than(201002),
PARTITION p201002 values less than(201003),
PARTITION p201003 values less than(201004)
);
insert into sales values(100, '2008-01-01'),
(100, '2008-02-01'),
(200, '2008-01-02'),
(100, '2009-03-01'),
(200, '2010-03-01')
EXPLAIN partitions select * from sales where date >='2010-01-01' and date < '2010-01-31'
- 如上查询语句扫描的全分区,但是之前的逻辑应该只扫p2020001分区,这个问题原因在于RANGE分区的查询,优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择,因此对于上述要求,需要将分区函数改为TO_DAY,如下:
create table sales(
money int UNSIGNED not null,
date datetime
)ENGINE=INNODB
PARTITION by range(TO_DAYS(date))(
PARTITION p201001 values less than(TO_DAYS('2010-02-01')),
PARTITION p201002 values less than(TO_DAYS('2010-03-01')),
PARTITION p201003 values less than(TO_DAYS('2010-04-01'))
);
EXPLAIN partitions select * from sales where date >='2010-01-01' and date < '2010-01-31'
- 此时查询只对p201001 分区进行扫描
LIST分区
- LIST分区和RANGE分区类似,只是分区列的值是离散的,不连续,如下:
create table t(
a int,
b int
)engine=INNODB
PARTITION by list(b)(
PARTITION p0 values in (1,3,5,7,9),
PARTITION p1 values in (0,2,4,6,8)
);
insert into t values(1,1),
(1,2),
(1,3),
(1,4)
select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';
- 如上,按奇数偶数分区,如果插入的值不值分区的定义,MySQL会抛出异常:
[SQL]insert into t values(1,100)
[Err] 1526 - Table has no partition for value 100
- 而且List有一个特殊地方,在insert插入多行时候,遇到未分区定义的值时候,MyISAM和InnoDB存储引擎的处理完全不同,MyISAM会将之前的行都insert,之后的不成功,InnoDB存储引擎会将他看成是一个事务,因此都会失败,
[SQL]insert into t values(1,1),
(1,2),
(1,3),
(1,4),
(1,100)
[Err] 1526 - Table has no partition for value 100
- 如上是InnoDB存储引擎,返回的是没有对应的分区值,并且数据全失败,
create table t(
a int,
b int
)engine=MyISAM
PARTITION by list(b)(
PARTITION p0 values in (1,3,5,7,9),
PARTITION p1 values in (0,2,4,6,8)
);
insert into t values(1,1),
(1,2),
(1,3),
(1,4),
(1,100)
select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';
- 可以看到,插入的1,10,记录是没有成功的,之前的2,3,4都是已经成功insert。
HASH分区
- HASH分区目的是将数据均由的分布的预定的各个分区中,保证个分区的数据大致一样。在RANGE和LIST分区中,必须明确知道一个列值或者列值的集合。而在HASH分区中MySQL自动完成了这些工作,你需要做的是基于要被hash的列值指定一个列值或者表达式,以及指定备份区的表要被分割的分区数量,如下案例
create table t_hash(
a int,
b datetime
)ENGINE=INNODB
PARTITION by hash(YEAR(b))
PARTITIONS 4;
- 如上hash规则,如果b列的值2010-04-01,那么这个记录在t_hash中,保存的确认值如下
MOD(YEAR('2010-04-01'), 4)= MOD(2010,4) = 2
- 如上计算,应该放到2分区中,
insert into t_hash values (1, '2010-04-01')
select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't_hash';
- 如上数据落在了P2分区
- MySQL数据库还支持一种LINEAR HASH的分区,更负责的分区算法,
create table t_hash(
a int,
b datetime
)ENGINE=INNODB
PARTITION by LINEAR HASH (YEAR(b))
PARTITIONS 4;
- MySQL数据库用一下方式判断分区
- 取大于分区数量4 的下一个2 的幂值V, V = POWRE(2, CEILING(LOG(2,num))) = 4
- 所在分区N= YEAR(‘2010-04-01’) & (V-1) = 2
- 得到的分区还是2,但是HASH算法与之前的完全不同,
- LINEAR HASH分区的有点在于,增加,删除,合并和拆分分区变得更加快速,这有利于处理含有大量数据的表,缺点在于与使用HASH分区得到的数据分布相比,各个分区数据的分布不均匀。