MySQL分区表使用总结

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

DO call  pro_sys_logByWeekDay('dispatcher_send_msg_par','SENDDATE','day');

总结

分区表看上去很帅气，但使用上存在约束，分区键设计不太灵活，如果不走分区键很容易出现全表锁，适用于简单的大数据量业务场景，复杂的大数据量场景可以考虑分表分库水平扩展方案。

参考

MySQL最佳实践分区表基本类型 ：

http://mysql.taobao.org/monthly/2017/11/09/

MySQL存储过程按月创建表分区：

https://blog.csdn.net/aofavx/article/details/50378360

分区表不建议创建主键的原因 ：

https://yq.aliyun.com/articles/48791

MySQL表分区操作错误1503解决方案：

http://google3030.blog.163.com/blog/static/16172446520103591023745/