mysql 分区表 归档_mysql分区表

分区

分区是mysql本身支持的技术，能把一个表从物理上分成多个区域，但逻辑上还是使用的一个表，以达到数据拆分存放，提高读写效率，分区是水平划分数据的一种方式，但单机所存的数量还是没有发生变化。

查看是否支持分区技术：show variables like '%partition%';

使用分区技术，需要在建表时确定好：

create tabletable_name(--建表信息

)engine myisam charser utf8

partitionby分区算法(分区字段)(--分区选项

);

分区算法

数据拆分的方式，称为分区算法，共有4种。分区是根据一个数字来划分的，而这个数字可以来源于表中的某一列(或函数调用的返回值)。

MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

List

为每一个分区指定一个固定的数字(枚举)列表。只要表记录中的分区字段的存在于这个列表中，则这个表记录就会被划分到这个分区中。

以下指定了4个分区，以及对应的列表：

create tablep_list(

idint,

namevarchar(32),

store_idint)engine myisam charset utf8

partitionbylist (store_id)(

partition p_northvalues in (1,4,5,6,17,18),

partition p_eastvalues in(2,7,9,10,11,13),

partition p_southvalues in(3,12,19,20),

partition p_westvalues in(8,14,15,16)

);

判断查询是否使用到了分区

如下查询，没有分区条件，则会到所有的分区里面去查找

Range

范围判断。

create tablep_range(

idint,

namevarchar(32),

birthday date

)engine myisam charset utf8

partitionby range (month(birthday))(

partition p_1values less than (4),

partition p_2values less than(7),

partition p_3values less than(10),

partition p_4valuesless than MAXVALUE

);

MAXVALUE是内置的一个无穷大的值。

Hash

指定分区个数n，然后通过对数字求余来划分。以下根据月份划分出5个区域

create tablep_hash(

idint,

namevarchar(20),

birthday date

)engine myisam charset utf8

partitionby hash(month(birthday)) partitions 5;

Key

KEY分区允许多列，而HASH分区只允许一列。

如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

KEY分区对象必须为列，而不能是基于列的表达式。

KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列加密后的字符串值(对于NDB集群引擎，使用的是MD5()，其他引擎使用的是PASSWORD())。

不指定分区字段，默认使用主键或唯一键：

CREATE TABLEk1 (

idINT NOT NULL PRIMARY KEY,

nameVARCHAR(20)

)

PARTITIONBY KEY() PARTITIONS 2;

在没有主键或者唯一键的情况下，需要显式指定分区字段，格式如下：

CREATE TABLEtm1 (

s1CHAR(32)

)

PARTITIONBY KEY(s1) PARTITIONS 10;

分区管理

删除分区

① 在key/hash分区方式下不会造成数据丢失(删除分区后数据会重新整合到剩余的分区去)

alter table 表名 coalesce partition 要删除分区的数量;

② 在range/list分区方式下会造成数据丢失

alter table 表名 drop partition 要删除的分区名称;

增加

求余方式： key/hash

alter table 表名  add  partition partitions  要新添加分区的数量;

分表增加好后，会自动把数据分配给各个分表存储。

范围方式： range/list

alter table 表名 addpartition(

partition 名称valuesless than (常量)

或

partition 名称values in(n,n,n)

);

优缺点

优点：

1、冷热分离：表非常大且只在表的最后部分有热点数据，冷数据根据分区规则自动归档。

2、定期淘汰历史数据：按时间写入，历史数据可淘汰，可快速删除，空间可快速回收。

3、优化查询：在where字句中包含分区列时，分区可以大大提高查询效率，减少缓存开销、减少IO开销。

4、统计性能提升：在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

缺点：

因为对应多个物理文件，所以存在文件描述符数量限制

Innodb分区的话，不支持外键

不管再怎么划分，数据都还是集中在一台机器上。

测试

CREATE TABLE`d2` (

`one`varchar(10) DEFAULT NULL,

`id`int(11) NOT NULLAUTO_INCREMENT,

`str` char(20) DEFAULT NULL,

`crc`int(10) unsigned DEFAULT NULL,

`big`varchar(50) DEFAULT NULL,

`uuid`char(36) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

以上是拆分数据前的表，共200W左右数据。以下进行分区

CREATE TABLE`d3` (

`one`varchar(10) DEFAULT NULL,

`id`int(11) NOT NULLAUTO_INCREMENT,

`str` char(20) DEFAULT NULL,

`crc`int(10) unsigned DEFAULT NULL,

`big`varchar(50) DEFAULT NULL,

`uuid`char(36) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

partitionby hash(id) partitions 5;

数据复制：

insert into d3 select * from d2

在没分区的表中全表扫描(4.2s)

select * from d2 where one = 'a'

分区表全表扫描(1.1s)

select * from d3 where one = 'a'

全表扫描的速度更快。因为分区表的索引也都是分开独立到不同的物理文件中的，所以不同部分索引时对应的数据更少，速度会更快。