mysql表分区

简单介绍

mysql数据库中的数据是以文件的形式存在磁盘上的，默认放在/mysql/data下面，一张表主要对应着三个文件：
（1）frm存放表结构
（2）myd存放表数据
（3）myi存表索引

如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢。这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，查找则可以分块查找。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

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

分块和分表的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

表分区的好处

1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据。

2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。也可以通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

表分区的限制

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

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

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

详见此文

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

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

支持的分区类型

1.RANGE分区

这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

可以通过数字大小来分区：

create table mytable(
    id int not null,
    dt date not null ,
    job int not null default 0,
    store int not null default 0
)engine=InnoDB default charset=utf8
partition by range(store)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21),
    partition p4 values less than MAXVALUE 
);

也可以通过时间（date）来分区：

create table mytable(
  id int not null,
  email varchar(35),
  dt date not null
)
partition by range columns(dt)(
  partition p0 values less than ('1960-01-01'),
  partition p1 values less than ('1970-01-01'),
  partition p2 values less than ('1980-01-01'),
  partition p3 values less than ('1990-01-01'),
  partition p4 values less than maxvalue
);

注意第二个例子中range的用法和第一种不同，原因在于range后面的括号中直接带的参数列必须是int类型的。添加COLUMNS关键字可支持非整数和多列比较。

下面是一个多列比较的例子：

CREATE TABLE mytable (
a int,
b int,
c int
)
PARTITION BY RANGE COLUMNS (a,b) (
PARTITION p01 VALUES LESS THAN (10,20),
PARTITION p02 VALUES LESS THAN (20,30),
PARTITION p03 VALUES LESS THAN (30,40),
PARTITION p04 VALUES LESS THAN (40,MAXVALUE),
PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

多列比较的依据是先比第一个数，如果第一个数相同再比第二个数，以此类推。

2.List分区

和RANGE分区类似。但是分区依据通过in来判断。

create table mytable(
  id int not null,
  job int not null default 0,
  store int not null default 0
)
partition by list(store)(
  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)
);

3.HASH分区

Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。

create table mytable(
  id int not null,
  job int not null default 0,
  store int not null default 0
)
partition by hash(store)
partitions 4;

使用linear关键字，可以使hash分区更具有可扩展性。

...
partition by linear hash(store)
...

4.KEY分区

Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。

对分区的操作

增加分区：

ALTER TABLE mytable ADD PARTITION (PARTITION p5 VALUES LESS THAN(1))

删除分区：

ALTER TABLE mytable DROP PARTITION p1;

在指定分区中查找：

没有必要用其他的语句，mysql会自动定位到该分区进行查找。