数据库的表分区

一、查看数据库是否支持分区

• SHOW VARIABLES LIKE '%partition%';  如果输出HAVE_PARTITONING  YES表示支持分区
• SHOW PLUGINS；显示所有的插件，如果有PARTITION ACTIVE STORAGE ENGINE GPL插件则表示支持分区

二、 表分区的特点

1. 同一分区表的所有分区必须使用同一个存储引擎
2. 分区适用于一个表的所有数据和索引，同时也不能只对表的一部分进行分区
3. 通过分区可以方便删除旧数据和增加分区添加新数据
4. 优化查询

三、分区类型

1. RANGE分区：基于属于同一个给定连续区间的列值，把多行分配给分区
2. LIST分区：基于列值匹配一个离散值集合中的某一个值来进行选择
3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区
4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供自身的哈希函数

四、RANGE分区

RANGE分区的每个分区的值都在一个给定的连续区间内，这些区间要连续且不能互相重叠

例如表bank：

自增ID	姓名	存款金额	存款日期

1.利用range方式创建分区表：

create table bank (
  id int(11),
  money int(11) unsigned not null,
  date datetime)
  partition byrange(year(date)) (
  partition p2007 values less than (2008),
  partition p2008 values less than (2009),
  partition p2009 values less than (2010),
  partition p2010 values less than (2011)
);

2.新增分区：

ALTER TABLE bank ADD PARTITION (

  PARTITION p2011 values less than (2012)

);

但是RANGE分区的VALUES LESS  THAN值必须严格增长，因此如果想增加分区p2006将少于2007年的数据存入该分区，必须使用合并分区的方法在p2007下建立两个子分区的方式实现: 

ALTER TABLE bankREORGANIZE PARTITION p2007 INTO (

  PARTITION s0 VALUES LESS THAN (1960),

  PARTITION s1 VALUES LESS THAN (1970)

);

3.删除分区

当删除了一个分区，也同时删除了该分区中所有的数据。

ALTER TABLE bank  DROP PARTITION p2007;

五、LIST分区

类似于RANGE分区，每个分区必须明确定义，LIST分区的每个分区的定义和选择基于某列的值从属于一个值列表集中的一个值。 

PARTITION BY LIST(expr) 其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

地区	商店ID 号
北区	3, 5, 6, 9, 17
东区	1, 2, 10, 11, 19, 20
西区	4, 12, 13, 14, 18
中心区	7, 8, 15, 16

CREATE TABLE emplyees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )

    PARTITION  BY LIST(store_id) (

    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)

);

六、HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

例一、使用基于‘store_id’列进行哈希处理的表，该表被分为了4个分区：

CREATE TABLE emplyees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )

PARTITION BY HASH(store_id)

PARTITION 4;

例二、“expr”还可以是返回一个整数的SQL表达式，基于雇员的年份进行分区

CREATE TABLE emplyees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )

PARTITION BY HASH（YEAR(hired)）

PARTITIONS 4;

七、KEY分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
) 

PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

八、子分区

• 子分区是分区表中每个分区的再次分割。
• 对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning）。 
• 每个分区必须有相同数量的子分区。如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区
  
• 在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一。
  

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    SUBPARTITIONS 2
    (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

九.分区的合并

改变表的分区而又不丢失数据。

1.RANGE和LIST分区的合并

ALTER TABLE table_name REORGANIZE PARTITION partition_list INTO (partition_definitions);

ALTER TABLE bank REORGANIZE PARTITION p2007,p2008,p2009,p2010,p2011 INTO (

PARTITION p1 VALUES LESS THAN (2009),

PARTITION p2 VALUES LESS THAN (2011),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

2.HASH和KEY分区的合并(COALESCE不能用来增加分区的数量)

ALTER TABLE table_name COALESCE PARTITION num;

十、分区维护

1.重建分区：

这和先删除分区中的所有记录，然后重新插入他们具有同样的效果，它可用于整理分区碎片。

ALTER TABLE t1 REBUILD PARTITION (p0,p1);

2.优化分区

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTER TABLE t1 OPTIMIZE PARTITION (p0,p1);

3.分析分区

读取并保存分区的键分布。

ALTER TABLE t1 ANALYZE PARTITION (p3);

4.修补分区修补被破坏的分区

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

5.检查分区

ALTER TABLE trb3 CHECK PARTITION (p1);