MySQL分区表实现按月份归类
作者:嘟嘟 嘟嘟嘟 发布时间:2024-01-17 12:56:36
标签:mysql,表分区
目录
建表
查看数据库文件:
插入
查询
删除
补充:Mysql自动按月表分区
MySQL单表数据量,建议不要超过2000W行,否则会对性能有较大影响。最近接手了一个项目,单表数据超7000W行,一条简单的查询语句等了50多分钟都没出结果,实在是难受,最终,我们决定用分区表。
建表
一般的表(innodb)创建后只有一个 idb 文件:
create table normal_table(id int primary key, no int)
查看数据库文件:
normal_table.ibd
创建按月份分区的分区表,注意!除了常规主键外,月份字段(用来分区的字段)也必须是主键:
create table partition_table(id int AUTO_INCREMENT, create_date date, name varchar(10),
primary key(id, create_date)) ENGINE=INNODB DEFAULT CHARSET=utf8
partition by range(month(create_date))(
partition quarter1 values less than(4),
partition quarter2 values less than(7),
partition quarter3 values less than(10),
partition quarter4 values less than(13)
);
查看数据库文件:
partition_table#p#quarter1.ibd
partition_table#p#quarter2.ibd
partition_table#p#quarter3.ibd
partition_table#p#quarter4.ibd
插入
insert into partition_table(create_date, name) values("2021-01-25", "tom1");
insert into partition_table(create_date, name) values("2021-02-25", "tom2");
insert into partition_table(create_date, name) values("2021-03-25", "tom3");
insert into partition_table(create_date, name) values("2021-04-25", "tom4");
insert into partition_table(create_date, name) values("2021-05-25", "tom5");
insert into partition_table(create_date, name) values("2021-06-25", "tom6");
insert into partition_table(create_date, name) values("2021-07-25", "tom7");
insert into partition_table(create_date, name) values("2021-08-25", "tom8");
insert into partition_table(create_date, name) values("2021-09-25", "tom9");
insert into partition_table(create_date, name) values("2021-10-25", "tom10");
insert into partition_table(create_date, name) values("2021-11-25", "tom11");
insert into partition_table(create_date, name) values("2021-12-25", "tom12");
查询
select count(*) from partition_table;
> 12
查询第二个分区(第二季度)的数据:
select * from partition_table PARTITION(quarter2);
4 2021-04-25 tom4
5 2021-05-25 tom5
6 2021-06-25 tom6
删除
当删除表时,该表的所有分区文件都会被删除
补充:Mysql自动按月表分区
核心的两个存储过程:
auto_create_partition为创建表分区,调用后为该表创建到下月结束的表分区。
auto_del_partition为删除表分区,方便历史数据空间回收。
DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_partition$$
CREATE PROCEDURE `auto_create_partition`(IN `table_name` varchar(64))
BEGIN
SET @next_month:=CONCAT(date_format(date_add(now(),interval 2 month),'%Y%m'),'01');
SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '`',
' ADD PARTITION (PARTITION p', @next_month, " VALUES LESS THAN (TO_DAYS(",
@next_month ,")) );" );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END$$
DROP PROCEDURE IF EXISTS auto_del_partition$$
CREATE PROCEDURE `auto_del_partition`(IN `table_name` varchar(64),IN `reserved_month` int)
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_part_name varchar(100) DEFAULT "";
DECLARE part_cursor CURSOR FOR
select partition_name from information_schema.partitions where table_schema = schema()
and table_name=@table_name and partition_description < TO_DAYS(CONCAT(date_format(date_sub(now(),interval reserved_month month),'%Y%m'),'01'));
DECLARE continue handler FOR
NOT FOUND SET v_finished = TRUE;
OPEN part_cursor;
read_loop: LOOP
FETCH part_cursor INTO v_part_name;
if v_finished = 1 then
leave read_loop;
end if;
SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '` DROP PARTITION ', v_part_name, ";" );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END LOOP;
CLOSE part_cursor;
END$$
DELIMITER ;
下面是示例
-- 假设有个表叫records,设置分区条件为按end_time按月分区
DROP TABLE IF EXISTS `records`;
CREATE TABLE `records` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`start_time` datetime NOT NULL,
`end_time` datetime NOT NULL,
`memo` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
PRIMARY KEY (`id`,`end_time`)
)
PARTITION BY RANGE (TO_DAYS(end_time))(
PARTITION p20200801 VALUES LESS THAN ( TO_DAYS('20200801'))
);
DROP EVENT IF EXISTS `records_auto_partition`;
-- 创建一个Event,每月执行一次,同时最多保存6个月的数据
DELIMITER $$
CREATE EVENT `records_auto_partition`
ON SCHEDULE EVERY 1 MONTH ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
call auto_create_partition('records');
call auto_del_partition('records',6);
END$$
DELIMITER ;
几点注意事项:
对于Mysql 5.1以上版本来说,表分区的索引字段必须是主键
存储过程中,DECLARE 必须紧跟着BEGIN,否则会报看不懂的错误
游标的DECLARE需要在定义声明之后,否则会报错
如果是自己安装的Mysql,有可能Event功能是未开启的,在创建Event时会提示错误;修改my.cnf,在 [mysqld] 下添加event_scheduler=1后重启即可。
来源:https://blog.csdn.net/qq_40310224/article/details/119921331
0
投稿
猜你喜欢
- 1 、据说python3就没有这个问题了2 、u'字符串' 代表是unicode格式的数据,路径最好写成这个格式,别直接跟字
- 有很多时候,我们会在python的运行过程中得到一些重要的变量,比如一个数据量很庞大的dict。而且,后面的某些程序也会用到这个dict,那
- 最近做了一个项目,其中有项目需求涉及到手机号验证码,就是当用户点击获取验证码之后我们会发送一条信息到用户手机,然后就会出现一个倒计时按钮,很
- 前言以前在浏览文章时,看到过一个Android的加载效果,觉得挺好看的,于是自己就模仿了一个。下面话不多说,我们直接来看看详细的介绍吧。运行
- 更新MySQL随意字段里的数据下面是我的mysql所有字段名若字段名太多不建议使用以下方法这里sql语句要注意一下双引号里面用单引号.def
- 前言经常看到这种算法可视化的图片,但往往做不到和画图的人心灵相通,所以想自己画一下,本文主要实现归并排序和希尔排序,如果想实现其他算法可参考
- 最近发现Python课器做很多事情,在监控服务器有其独特的优势,耗费资源少,开发周期短。首先我们做一个定时或者实时脚本timedtask.p
- 一,*和.*的联系和区别。 1,在进行数值运行和数值乘矩阵,这两种没有区别,例如:a*b=a.*b; a*B=a.*B; B*a=B.*a
- Celery文档参考:http://docs.jinkan.org/docs/celery/参考文章:https://www.jb51.ne
- 前言本文通过示例给大家详细介绍了关于python打印三角形的相关,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧1、直角三角
- 木马原理:入侵者使用诸如ASP图片木马生成器之类的工具将一张正常的图片与一个ASP木马文件合并成一个图片文件(即将对网站有害的ASP代码插在
- 一个字典就是一个键对应一个单值的映射。如果你想要一个键映射多个值,那么你就需要将这多个值放到另外的容器中, 比如列表或者集合里面。比如,你可
- 百度了一下。。有说将cmd字符编码用chcp命令改为65001(utf8字符编码),可这样之后根本无法输入中文,查询出的中问结果依旧乱码 。
- 这个技巧将教你如何用css做出漂亮的文本按钮,有活力的按钮将节省你很多制作图片的时间,也能让你一天的工作中成为一个快乐的人,让我们一起看看效
- 前言:今天要总结的是如何用程序来实现短信发送功能。但是呢,可能需要我们调用一些api接口,我会详细介绍。都是自己学到的,害怕忘记,所以要总结
- Numpy模块被广泛用于科学和数值计算,自然有它的强大之处,之前对于特征处理中需要进行数据列表或者矩阵拼接的时候都是自己写的函数来完成的,今
- 1.C++ 代码Demo.h#pragma oncevoid GeneratorGaussKernel(int ksize, float s
- IE的特殊性 IE的DOM元素属性与Firefox, Opera, Safari有些不同。在IE中,我们可以给DOM添加任意自定
- 自执行函数:自动执行的函数。它在被解释时就已经在运行了。一般函数都是在被调用时才会执行的。 自执行函数的一般格式:(function() {
- 功能:实现网页内容的即时编辑,增加页面的可用性、交互性。方法1:直接通过textarea标签实现,请运行下边代码:<!DOCTYPE