在下面场景中,分区可以起到非常大的作用:
- 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据。
- 分区表的数据更容易维护。例如,想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作。
- 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。
- 可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问、ext3文件系统化的iNode锁竞争。
- 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集场景下效果非常好。
分区表本身也有一些限制,以下是比较重要的几点:
- 一个表最多只能有1024个分区。
- 在MySQL5.1中,分区表达式必须是整数,或者是返回整数的表达式。在MySQL5.5中,某些场景中可以直接使用列来进行分区。
- 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。
- 分区表中无法使用外键约束。
分区表类型
- RANGE分区
基于属于一个给定连续区间的列值,把多行分配给分区。
把商店雇员进行分区,按离职时间每5年一个分区,1991年之前的离职雇员保存在分区p0中,1991-1995年离职的雇员保存在p1…以此类推。SQL代码如下:
CREATE TABLE user (
id int(10) not null,
fname varchar(20),
lname varchar(20),
hired_time date not null default '1970-01-01',
separated_time date not null default '9999-12-31'
store_id int(10) not null
)
partition by range(year(separated_time))(
partition p0 values less than (1991),
partition p1 values less than (1996),
partition p2 values less than (2001),
partition p3 values less than maxvalue
);
- LIST分区
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
CREATE TABLE employees (
id int(10) not null,
name varchar(30),
store_id int
)
partition by list(store_id) (
partition pNorth values in (3, 5, 6, 9),
partition pEast values in (1,2),
partition pWest values in (4),
partition pSouth values in (7,8)
);
- HASH分区
基于用户定义的表达式的返回值来进行的分区,该表达式使用将要插入的表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值得任何表达式。
create table employees (
id int not null,
name varchar(30),
store_id int
)
partition by hash(store_id)
partitions 4;
- KEY分区
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须由一列或多列包含整数值。
查询优化
分区的最大优点就是优化器可以根据分区函数来过滤一些分区。所以,对于访问分区表来说,很重要的一点是要在where条件中带入分区列,有时候即使看似多余的也要带上,这样就可以让优化器能够过滤掉无需访问的分区。一个很重要的原则是:即便在创建分区时可以使用表达式,但在查询时却只能根据列来过滤分区。
使用explain partition可以观察优化器是否执行了分区过滤:
explain partitions select * from sales_by_day
//查询所有分区:p_2010,p_2011,p_2012
explain partitions select * from sales_by_day where day > '2011-01-01'
//partitions:p_2011,p_2012
explain partitions select * from sales_by_day where year(day) = 2010
//表达式无效:partitions:p_2010,p_2011,p_2012
explain partitions select * from sales_by_day where day between '2010-01-01' AND '2010-12-31'
//partions:p_2010