mysql分区

为什么分区？

             1、分区可以在一个表中存储比单个磁盘或文件系统分区上的数据更多的数据，因为我们可以将分区表存储在不同物理磁盘上

             2、对已过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据，他的效率远比delete高；

             3、优化查询，在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；例如下面语句：

SELECT * FROM t PARTITION（p0，p1）WHERE c <5仅选择与WHERE条件匹配的分区p0和p1中的那些行。

在这种情况下，MySQL不检查表t的任何其他分区;

            4、涉及聚合函数SUM()、COUNT()的查询时，可以容易的在每个分区上并行处理,例如在执行下面这条语句：

SELECT salesperson_id，COUNT（orders）as order_total FROM sales GROUP BY salesperson_id ;会在每个分区上都同时运行查询；

            5、凭借在多个磁盘上传播数据，实现更高的查询吞吐量。

分区的缺点

          1、一个表最多只能有1024个分区；

          2、在MySQL5.1中，分区表达式必须为整数或者返回整数，而在MySQL5.5以后可以使用非整数，即其他的数据类型(并不是所有的数据类型)来分区；

         3、同一个分区表的所有分区必须使用相同存储引擎；

         4、分区表无法使用外键约束；

分区类型

1. RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区；
2. LIST分区：类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；
3. HASH分区：基于用户定义的表达式返回值来选择分区，该表达式对要插入到表的行中列值操作；
4. KEY分区：类似HASH，但是HASH允许使用用户自定义表达式，而KEY分区不允许，它需要使用MySQL服务器提供的HASH函数，同时HASH分区只支持整数分区，而KEY分区支持除BLOB和TEXT类型外其他列；
5. 子分区,也成为复合分区,就是多个分区类型结合使用

如何分区

      ①:range分区

create table testwithpartition(
id int DEFAULT null,
name char(5),
datedata date
)
PARTITION BY RANGE (year(datedata)) (
PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,  
PARTITION p3 VALUES LESS THAN (1998) ,
PARTITION p4 VALUES LESS THAN (1999) ,  
PARTITION p5 VALUES LESS THAN (2000) , 
PARTITION p6 VALUES LESS THAN MAXVALUE );

      ②:list分区

create table testwithlistpartition(
id int not null,
name char(5),
category VARCHAR(30)
)
PARTITION BY LIST (id) (
PARTITION p0 VALUES IN (1,5) ,
PARTITION p1 VALUES IN (11,15) ,
PARTITION p2 VALUES IN (6,10) ,
PARTITION p3 VALUES IN (16,20) 
);

      ③:hash分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH( id )
PARTITIONS 4;

      ④:key分区

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY(id)
PARTITIONS 2;

这些都是在创建表的时候进行分区,也可以在表创建完成后再进行分区

alter table  data_1910 partition by  key(id) PARTITIONS 100;

其他操作:

   删除分区: 

alter table 表名 remove partitioning;

遇到问题:

       A PRIMARY KEY must include all columns in the table's partitioning function

       出现问题的原因是,在表的多个字段中,其中id是主键,uuid是普通字段,但是在uuid上建立了索引,使用id进行分区,就会出现这个问题.    也就是说建立分区的时候,只有分区的那个字段可以建立索引,其他的字段包含索引就会建立失败.

       解决办法:删除uuid字段上的索引再使用id建立分区即可成功.

       可能的其他原因: 分区的字段不在主键当中,一种处理方式是把需要建立的分区字段包含到主键中去,另一种处理方式就是移除所有的主键.