MySQL分区表概述
我们经常遇到一张表里面保存了上亿甚至过十亿的记录，这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。 分区一个最大的优点就是可以非常高效的进行历史数据的清理。
分区类型
目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。
分区适用场景和优势
表数据量非常大而且大部分为历史数据，不经常被访问；
简单的业务场景，不会产生跨分区查询或跨分区关联；
分区表数据更容易维护（可独立对分区进行优化、检查、修复及批量删除大数据可以采用drop分区的形式等）；
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备；
分区表可以避免某些特殊的瓶颈（ps: InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等）；
可以备份和恢复独立的分区，非常适用于大数据集的场景；
分区表限制
按照时间戳range分区只支持increasing规则；
drop分区后数据会全部被清空，不会做数据迁移；
分区字段必须包含在主键字段内，唯一键必须包含在分区字段；
分区字段不支持timestamps类型，需要使用datetime；
单表最多支持1024个分区；
分区表无法使用外键约束；
分区必须使用相同的Engine；
对于MyISAM分区表，不能在使用LOAD INDEX INTO CACHE操作；
对于MyISAM分区表，使用时会打开更多的文件描述符（单个分区是一个独立的文件）；
分区键最好默认设置为NOT NULL
分区表不支持除了主键外的唯一键
Oracle分区表的坑
oracle分区表删除后会导致索引失效，查询计划变化性能很差
自动创建分区表
-- 按日期格式自动添加分区存储过程
CREATE PROCEDURE `pro_sys_logByWeekDay`(IN tableName VARCHAR(50),IN timeColName VARCHAR(50),IN DateFormat VARCHAR(10) )
COMMENT '按日期格式(年YEAR，月month，周week，日day)添加表分区的存储过程，由定时任务调用'
BEGIN
DECLARE p_id int;
DECLARE lasttime VARCHAR(20);
DECLARE nexttime varchar(20);
SELECT COUNT(partition_name),max(partition_description) des into p_id,lasttime
from INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
set @v_add_a=CONCAT('select adddate(str_to_date(',lasttime,',\'%Y%c%d\'),Interval 1 ',DateFormat,') into @nexttime from dual ');
-- select @v_add_a;
PREPARE stm from @v_add_a;
EXECUTE stm;
DEALLOCATE PREPARE stm;
set nexttime=@nexttime;
set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),' values less than (\'',replace(nexttime,'-',''),'\'))');
-- select @v_add;
PREPARE stmt from @v_add;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END-- 创建每周生成一次表分区的定时任务
create EVENT event_sysLogWeek on SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP
on COMPLETION PRESERVE
ENABLE
DO call pro_sys_logByWeekDay('dispatcher_send_msg_par','SENDDATE','Week');-- 创建每天生成一次表分区的定时任务
create EVENT event_sysLogDay on SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
on COMPLETION PRESERVE
ENABLE
DO call pro_sys_logByWeekDay('dispatcher_send_msg_par','SENDDATE','day');
