MySQL分区表使用总结-阿里云开发者社区

开发者社区> 怀刚66> 正文

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
on COMPLETION PRESERVE
ENABLE
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/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
分区表查询条件使用浅析
如果有个分区表,分区列为A、B、C三列,我要查询B列为某些值的数据,MaxCompute还能充分发挥分区表的优势吗?答案是肯定的,MaxCompute依然能发挥分区列的优势!在底层解析SQL执行计划时,只会将符合条件的分区纳入计算,而不是进行全表扫描。
1788 0
【Mysql】Mysql数据表区分大小写问题解决方案
原因 由于有的环境下,对数据表的大小写是明确区分的,但是我在导出的时候的数据表全部都是小写的,这个时候需要对环境进行配置 解决 vim /etc/my.cnf 在[mysqld]下加入一行:lower_case_table_names=1 service mysq...
658 0
分区表 区间分区 散列分区 列表分区
oracle 11g 增加了新的分区类型,总结一下目前之前的分区表 区间分区 散列分区 列表分区 区间分区: create table gh_range_example( id varchar2(100), range_date date not n...
590 0
Mysql数据库表分区深入详解
0、mysql数据库分区的由来? 1)传统不分区数据库痛点
12 0
Mysql总结_03_mysql常用命令
一、MySQL服务的启动和停止 net stop mysql net start mysql二、登陆mysql mysql -u用户名 -p用户密码 键入命令mysql -uroot -p, 回车后提示你输入密码,输入12345,然后回车即可进入到mysql中了,mysql的提示符是:mysql>...
783 0
Mysql总结_02_mysql数据库忘记密码时如何修改
1.从cmd进入mysql的bin下,输入命令  mysqld --skip-grant-tables  回车      注:(输入命令前,确保在任务管理器中已没有mysql的进程在运行,可输入命令:net stop mysql  来关闭mysql服务,切忌此命令结尾没有分号) mysqld --skip-grant-tables的作用:跳过了mysql的用户验证   2.重开一个新的命令行,输入命令:mysql,连上数据库。
731 0
+关注
11
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载