浅谈数据表分区技术基础

mysql 分区技术介绍

1 为什么使用分区：
   数据库单表到达一定量后，性能会有衰减，像mysql\sql server等犹为明显，所以需要把这些数据进行分区处理。同时有时候可能出现数据剥离什么的，分区表就更有用处了！
   分区的优势：
        --与单个磁盘或文件系统分区相比，可以存储更多的数据
    --很容易就能删除不用或者过时的数据
    --一些查询可以得到极大的优化
    --涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
    --IO吞吐量更大
    --分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

2、分区技术支持
   1 mysql> SHOW VARIABLES LIKE '%partition%';
   2  SHOW PLUGINS;
 
3、分区类型及举例     创建  添加 删除
   3.1范围分区
RANGE 分区：基于属于一个给定连续区间的列值（比如创建时间，付款时间，订单号），把多行分配(范围内的数据)给分区。如时间，连续的常量值等

  mysql> create table range_p(
    -> perid int(11),
    -> pername char(12) not null,
    -> monsalary DECIMAL(10,2),
    -> credate datetime
    -> ) partition by range(year(credate))(
    -> partition p2011 values less than (2008),
    -> partition p2012 values less than (2012),
    -> partition p2013 values less than (2013),
    -> partition p2014 values less than (2014),
    -> partition p2015 values less than maxvalue
    -> );

    范围分区 添加 删除：
    (2)添加分区：
备注：不能超过p04的范围，严格递增每个分区，即最小不能小于前一个分区
下面新曾了两个分区n01和n02
 alter table range_p
 reorganize partition p2012 into(
 partition n01 values less than(2009),
 partition n02 values less than(2010),
 partition p2012 values less than(2012')
 );
   (3)删除分区：
删除分区即删除数据。
mysql> select count(*) from range_p where from_date<'1985-12-31';
+----------+
| count(*) |
+----------+
|    18238 |
+----------+
1 row in set (0.09 sec)

mysql> alter table range_p drop partition p2011;
        
   3.2列举分区   list只能是数字
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。比如说类似性别(1,2)等属性值。
mysql> create table list_p(
    -> perid int(11),
    -> pername char(12) not null,
    -> sex int(1) not null,
    -> monsalary DECIMAL(10,2),
    -> credate datetime
    -> ) partition by list(sex) (
    -> partition psex1 values in(1),
    -> partition psex2 values in(2));

   注意，list只能是数字，使用字符会报错ERROR 1697 (HY000): VALUES value for partition 'psex1'    must have type INT

   (2)添加分区
mysql> alter table list_p add partition(partition p7 values in('CEO'));
   
   (3)删除分区：
mysql> alter table list_p drop partition p7;
   
   3.3离散分区
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
--以int字段hash分区
    create table hash_p(
    perid int(11),
    pername char(12) not null,
    sex int(1) not null,
    monsalary DECIMAL(10,2),
    credate datetime
    ) partition by hash (perid)
    partitions 8;
--以时间函数hash分区
    mysql> create table hash_p(
        -> perid int(11),
        -> pername char(12) not null,
        -> sex int(1) not null,
        -> monsalary DECIMAL(10,2),
        -> credate datetime
        -> ) partition by hash (year(credate))
        -> partitions 4;
     (2)删除hash分区
4个分区减少到两个，4-2=2
mysql> alter table hash_p coalesce partition 2;
     (3)添加hash分区
增加到5个，2+3=5
mysql> alter table hash_p add partition partitions 3;
    
    3.4键值分区
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。
其分区方法与hash很相似
mysql> create table key_p(
    -> perid int(11),
    -> pername char(12) not null,
    -> sex int(1) not null,
    -> monsalary DECIMAL(10,2),
    -> credate datetime
    -> ) partition by key (perid)
    -> partitions 8;
    (2)删除分区，同hash
mysql> alter table key_p coalesce partition 2;
    (3)添加分区，同hash

mysql> alter table key_p add partition partitions 3;

    3.5其它说明
mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：
所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
日期类型，如DATE和DATETIME。其余日期类型不支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
COLUMNS可以使用多个列进行分区。