mysql 表分区技术

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

表分区有什么好处：
a.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 
b.和单个磁盘或者文件系统相比，可以存储更多数据 
c.优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
d.分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。 
e.可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

   今天，我通过查阅相关资料与动手操作，学习了一下数据库表分区的技术。个人理解，其实就是当有大数据的数据表时，将数据表中的数据按照一定的规则，分门别类存储到规定的区域空间，
   如果要对表进行“增删改查”的操作时，执行操作的区域不会是整张表，而是该表中的某个区域，实际就是“以空间换时间”，无疑会提高执行效率。

  分区表的限制因素

  a.一个表最多只能有1024个分区

  b.MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

  c.如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

  d.分区表中无法使用外键约束

  e.MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

  如何判断当前MySQL是否支持分区

  命令：show variables like '%partition%'

    

   其中 Variable_name 的Value = 1,我测试过了，表示可以正常分区的。我数据库的版本是：

  

    MySQL支持的分区类型有：RANGE分区，LIST分区，HASH分区，KEY分区。其中RANGE,LIST,HASH分区等一般使用Int类型，KEY分区使用BLOB，TEXT类型等。

  接下来创建数据表
 
  紧接着：
  
   以上的报错，说明partition by 不能够单独的使用（stand-alone）.此处要注意！
   然后采取第二种用法，如图所示：  
  

  

  
    分区成功了，呵呵呵！

  

 有几点注意：
 a. 对于分区s1,表示 1 <= id < 10;对于分区s2,表示 10<= id < 20;对于分区s3，表示 20<= id < 30；对于分区s4,表示 id >= 30,无上限                                  
 b. 如果将less than(10) 和less than (20)的顺序颠倒过来，那么将报错,如： VALUES LESS THAN value must be strictly increasing for each partition，
    所以也用注意顺序问题                                                                                                       
 c. 一个表最大分区为：1024.(上面已经提到过)，在有限的表分区内，最后加上 ：partition xxx values less than maxvalue,是很有必要的。 
 d. 不管哪种分区类型，分区键必须是主键或唯一键，除非两者都没有，否者将会报如下错误。

如果是将注册日期作为分区键，则须要使用日期处理函数转换为整型，例如year(regDate),to_days(regDate),to_seconds(regDate),且只支持这三个函数。

或者使用RANGE COLUMNS分区，则不需要转换日期，如下所示

create table users_par(
    id int not null,
    usrName varchar(50) not null,
    usrEmail varchar(50) not null,
    age int not null,
    regDate date not null
    partition by range columns(regDate)(
    partition p0 values less than('2005-05-05'),
    partition p1 values less than('2009-09-09'),
    partition p2 values less than('2015-05-05'),
);

RANGE分区特别适用于删除过期数据或者某范围数据，只需要alter table tbl_name truncate partition partition_name即可，
比delete语句效率要高很多，还有就是经常使用分区键的查询，可以提高查询性能，因为只需扫描某些分区就OK

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

 接下来，我们查看，我们建立的分区，相关的语法：EXPLAIN PARTITIONS SELECT * FROM `demo`

 

  当然,我们需要做测试，“实践是检验真理的唯一标准”嘛。

 

 

 

 语法： explain partitions sql语句，如

 

 等等，也可以做其他的测试。

 接下来，尝试其他方式的表分区形式。

 List 表分区创建：

语法：desc partitions select * from table_name;

同时，语法：show create table table_name;

Hash表分区创建：

Key分区创建与Hash分区类似。可以参考上面的。

子分区的创建：分区表中对每个分区再次分割，又成为复合分区。可参考：数据切分——Mysql分区表的建立及性能分析



地址：http://www.cnblogs.com/zmxmumu/p/4450857.html

分区管理补充
RANGE和LIST分区在删除，添加，重新定义等分区管理上非常类似，如下所示。
删除分区(alter table tbl_name drop partition partition_name)，分区被删除后，该分区的数据一起被删除。

mysql> alter table users_par drop partition p0;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加分区(alter table tbl_name add partition)

mysql>  alter table users_par add partition (partition p0 values less than (20));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
--这里报错是因为添加分区必须在原分区的最大端添加，在为LIST分区添加分区时，新分区的值列表的值不能包含任意一个现有分区中值列表中的值，否则报错
mysql> alter table student add partition (partition p2 values in ('男'));
ERROR 1495 (HY000): Multiple definition of same constant in list partitioning

重新定义分区(alter table tbl_name reorganize partition partition_name into)，可以将一个分区拆开成多个，反之可以合并多个成一个或多个。

mysql> alter table users_par reorganize partition p1 into (partition p0 values less than (20),partition p1 values less t
han(30));
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

需要注意的是：RANGE和LIST分区在重新定义时，只能重新定义相邻的分区，不可以跳过分区，并且重新定义的分区区间必须和原分区区间一致，也不可以改变分区的类型。

HASH和KEY分区的管理

减少分区数量，使用coaleace关键字

mysql> alter table hash_par coalesce partition 2;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

增加分区数量

mysql> alter table hash_par add partition partitions 2;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL分区有利于查询优化，快速删除过期数据，提高查询吞吐量等。

补充：http://www.cnblogs.com/zmxmumu/p/4450857.html