MySQL--分区表

在下面场景中，分区可以起到非常大的作用：

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
• 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
• 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
• 可以使用分区表来避免某些特殊的瓶颈，例如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_2012explain partitions select * from sales_by_day where day > '2011-01-01'//partitions:p_2011,p_2012explain partitions select * from sales_by_day where year(day) = 2010//表达式无效：partitions:p_2010,p_2011,p_2012explain partitions select * from sales_by_day where day between '2010-01-01' AND '2010-12-31'//partions:p_2010