Mysql按时间进行表分区

Mysql按时间进行表分区,第1张

以下是创建一张测试表TEST并且按照时间CREATE_TIME创建RANGE分区,并使用ID创建hash分区,组成复合分区。

CREATE TABLE TEST (

CREATE_TIME DATETIME DEFAULT NULL, ID BIGINT(15) DEFAULT NULL

) ENGINE=INNODB DEFAULT CHARSET=utf8

PARTITION BY RANGE(TO_DAYS(CREATE_TIME)) PARTITIONS 7 SUBPARTITION BY HASH(ID) SUBPARTITIONS 16

(PARTITION P1710 VALUES LESS THAN (TO_DAYS ('2017-10-01'))

(SUBPARTITION P1710sp0 ,SUBPARTITION P1710sp1 ,

SUBPARTITION P1710sp2 ,SUBPARTITION P1710sp3 ,

SUBPARTITION P1710sp4 ,SUBPARTITION P1710sp5 ,

SUBPARTITION P1710sp6 ,SUBPARTITION P1710sp7 ,

SUBPARTITION P1710sp8 ,SUBPARTITION P1710sp9 ,

SUBPARTITION P1710sp10 ,SUBPARTITION P1710sp11 ,

SUBPARTITION P1710sp12 ,SUBPARTITION P1710sp13 ,

SUBPARTITION P1710sp14 ,SUBPARTITION P1710sp15 ),

PARTITION P1711 VALUES LESS THAN (TO_DAYS ('2017-11-01'))

(SUBPARTITION P1711sp0 ,SUBPARTITION P1711sp1 ,

SUBPARTITION P1711sp2 , SUBPARTITION P1711sp3 ,

SUBPARTITION P1711sp4 , SUBPARTITION P1711sp5 ,

SUBPARTITION P1711sp6 , SUBPARTITION P1711sp7 ,

SUBPARTITION P1711sp8 , SUBPARTITION P1711sp9 ,

SUBPARTITION P1711sp10 , SUBPARTITION P1711sp11 ,

SUBPARTITION P1711sp12 , SUBPARTITION P1711sp13 ,

SUBPARTITION P1711sp14 , SUBPARTITION P1711sp15 ),

我们的业务只存近一段时间的数据,因此有大量表需要清理 历史 数据,目前使用的delete清理数据,存在以下问题。为避免同时支持大量delete,我们的清理任务只在低峰期串行执行,导致任务过多时需要排队,甚至失败的情况;数据清理使用delete语句,表数据量较大时,对数据库造成很大压力;即使我们删除了旧数据,已删除的数据仍占据存储空间,底层数据文件并没有立刻变小,以至于形成数据空洞。

查看MySQL官方文档时,发现了分区表,因此基于官方文档总结一下。

MySQL逻辑上为一个表,物理上存储在多个文件中,这是 MySQL 支持的功能(5.1 开始), 8.0 版本只 InnoDB 和 NDB 支持分区表。

优点:

缺点:

根据分区表键值的范围把数据存储到表的不同分区中,适用于以时间或日期作为分区类型,方便数据清理。

小提示:

1.当插入数据分区不存在时会报错:Table has no partition for value xxx

2.Range类型分区字段必须是数值,时间类型可用函数转换为数值;

3.分区字段列值可以为null,所有为null的数据将存在最小的分区中;

按分区键取值的列表进行分区,每一行数据须找到对应的分区列表,否则数据插入失败

小提示:

根据指定分区表达式的整数值以及分区数进行数据划分(mod函数)

小提示:

按键分区类似于按哈希分区,只是哈希分区使用用户定义的表达式,用于键分区的哈希函数由 MySQL 服务器提供。NDB 集群为此使用 MD5() 对于使用其他存储引擎的表,服务器使用自己的内部哈希函数。

小提示:

子分区(subpartitioning)也称为复合分区(composite partitioning) ,是已分区表中每个分区的进一步划分

小提示:

小提示:

用mysql的表分区功能(逻辑上还是一个表,对程序来说是透明的),通过分区函数可实现自动分表。如果想实现根据数据每月或每周动态的再分区,可以写一个存储过程实现分区调整逻辑,最后写一个mysql event(自动化作业)按周期调用这个存储过程就行了。


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/7389687.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-05
下一篇 2023-04-05

发表评论

登录后才能评论

评论列表(0条)

保存