MySQL分区表

一、什么是分区

就是一张表分成多个区域（如：p0,p1...）,mysql5.1开始支持数据表分区了。如：若user表的记录超过了几百万条，那么就可以根据入库日期将表分区，也可以根据其他条件来进行分区。

优点：代码维护量小，基本不用改动，提高IO吞吐量

缺点：表的并发程度没有增加

二、什么是分表

1、分表，即把一个很大的表达数据分到几个表中，这样每个表数据都不多。 

优点：提高并发量，减小锁的粒度

缺点：代码维护成本高，相关sql都需要改

三、为什么要对表进行分区

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

分区的一些优点包括：

      1）、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区

      2）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果

四、分区类型

· RANGE分区：基于属于一个给定连续区间的列值（VALUES less THAN），把多行分配给分区。

· LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择(VALUESin)，数据不重复。 

· HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算(

PARTITIONS num 分区的数量)。

· KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

五、查看数据库是否支持分区---->show plugins

 

六、创建分区表

 （1）RANGE分区

    CREATE TABLE  f_user (
     id INT not null PRIMARY key,
     userName VARCHAR (20) DEFAULT '' 
 ) ENGINE = myisam DEFAULT CHARSET = utf8 
 PARTITION BY RANGE (id)(       
     PARTITION p0  VALUES less THAN (300), 
     PARTITION p1  VALUES less THAN (500),
     partition p2 values less than (MAXVALUE)
 )

查看有多个分区

explain PARTITIONS select * from f_user;

新增分区或删除分区

 alter table f_user add partition(partition p2 values less than (MAXVALUE));   
 alter table f_user DROP partition p2;  

（2）list 分区

 CREATE TABLE  f_user (
     id INT not null PRIMARY key,
     userName VARCHAR (20) DEFAULT '' 
 ) ENGINE = myisam DEFAULT CHARSET = utf8 
 PARTITION BY list(id)(       
  PARTITION p0VALUESin(1,2,3),----区间值不能重复

    PARTITION p1 VALUES in(4,5,6)

)

（3）hash分区--->HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；

 CREATE TABLE  f_user (
     id INT not null PRIMARY key,
     userName VARCHAR (20) DEFAULT '' 
 ) ENGINE = myisam DEFAULT CHARSET = utf8 
  PARTITIONBYHASH(id)

    PARTITIONS 4；---分区个数 

（4）key分区

 CREATE TABLE  f_user (     id INT not null PRIMARY key,     userName VARCHAR (20) DEFAULT ''  ) ENGINE = myisam DEFAULT CHARSET = utf8   
    PARTITION BY key(id)  
    PARTITIONS 4；---分区个数 
（5）子分区

CREATE TABLE  f_user222 (     id INT not null PRIMARY key,     userName VARCHAR (20) DEFAULT ''  ) ENGINE = myisam DEFAULT CHARSET = utf8      
    PARTITION by range(id)
    SUBPARTITION by key(id)
    SUBPARTITIONs 2 (
        PARTITION p0  VALUES less THAN (500),
      PARTITION p1   VALUES less than (MAXVALUE)
    )
建立两个range分区，每个分区根据key有分别有两个子分区