MySQL表分区的使用

MySQL表分区的使用

一 分区和分表的概念

分区 根据指定的规则，将数据表中的行拆分为更小的数据集，随后分布于多个目录和磁盘中，MySQL从5.1.3开始支持Partition

分表 通过人为的手动去建立三张表，每张表的文件都是相对独立的。

分区和分表的对比：

二 MySQL的分区类型介绍

范围分区(RANGE)：

每一个分区中都包含能对应到一组特定值（范围）的一些行，适用的情景包括常用查询直接以对表进行分区的列为条件，或者需要通过删除表来快速删除旧数据时。

列示分区：

列示分区与范围分区类似。主要区别在于，列示分区模式允许根据 DBA 预先定义的值列表（而不是一系列连续值构成的范围）来对数据进行划分。

列分区：

列分区支持在分区键中使用多个列。这些列将用于决定在分区中放置那些行并在分区修剪操作中决定在哪那些行要清掉。列分区是对范围分区和列表分区的扩展。

散列分区(Hash Partition)：

散列分区主要用于确保在预先设定数量的多个分区上平均分配数据。在范围分区和列表分区中，您必须明确指定特定列值存储在哪个分区中。而在散列分区中，MySQL 将自动为您做这个工作，您只需根据执行散列的列值和分区表所划分成的分区数量来指定列值或表达式即可。

线性散列分区是列表分区的一个变体，它使用一种更复杂的数据分区算法，可在处理 TB 级表时大幅提高添加、删除、合并和拆分分区的速度。

键分区：

键分区与散列分区类似，MySQL 通过一个系统生成的散列键来确保数据的平均分布，而不是使用用户定义的表达式做散列分区。键分区支持在分区中使用各种类型的列数据。

子分区：

子分区支持对分区表中各个分区进行进一步划分，专用于处理超大型表，可将数据和索引分布在许多服务器和磁盘上。

分区修剪：

通过在查询中适时使用“WHERE”或“ON”子句，可确保 MySQL 优化器仅访问符合条件的分区。这样一来，查询执行速度将比未分区的表提高一个数量级。

三、常用分区类型的使用

RANGE分区使用:

CREATE TABLE `virus_daily` (
    `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `day` date NOT NULL DEFAULT '0000-00-00', 
    `event_count` INT (11) NULL DEFAULT 0 COMMENT '事件数',
    `ip_count` INT(11) NULL DEFAULT 0 COMMENT 'IP地址数', 
    PRIMARY KEY (`id`, `day`) --注意day这里必须定义为一个索引字段
)ENGINE = MyISAM DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci PARTITION BY RANGE (to_days(`day`))(
PARTITION p1 VALUES LESS THAN (to_days('2013-12-03')),
PARTITION p2 VALUES LESS THAN (to_days('2013-12-04')),
PARTITION p3 VALUES LESS THAN (to_days('2013-12-05')),
PARTITION p4 VALUES LESS THAN (to_days('2013-12-06')),
PARTITION p5 VALUES LESS THAN (to_days('2013-12-07')),
PARTITION p6 VALUES LESS THAN (to_days('2013-12-08')),
PARTITION p7 VALUES LESS THAN (to_days('2013-12-09')),
PARTITION p8 VALUES LESS THAN (to_days('2013-12-10'))
);

基于这种分区方案，将每天的数据存放到一个分区当中，例如当：插入时间为2013-12-04的数据，该数据将被保存到p1这块分区当中。