mysql按天执行分区表sql_Mysql按天自动表分区教程

本文介绍如何使用MySQL的表分区功能来提升大型表的查询效率和管理便利性,包括手动分区、自动增加和删除分区的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Mysql按天自动表分区教程

前言

什么是表分区?

创建测试数据库

创建测试表

手动进行分区

批量进行分区

单条增加分区

删除分区命令

插入数据

查看表分区

创建增加分区和删除分区代码

手动调用存储程序命令

开启事件

新建event事件

前言

在使用mysql时,往往用一张表来存放数据,不同的业务可能产生的数据量也不同,有的业务可能一天需要插入几万条数据,也有的则更多,即使量很小日积月累数据库表的数据堆积也会越来越多,需要人工去删除数据,当数据达到百万或千万级别时,mysql查询速度明显下降,性能也随之下降,解决该问题的办法有很多,比如添加索引、优化SQL,但这都只是治标不治本的做法,无法从根本上提高Mysql的性能和查询速度。

什么是表分区?

mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。

表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。

创建测试数据库

create database test charset=utf8;

创建测试表

use test;// 先切换到test数据库

create table test_log

(

time datetime,

msg varchar(2000)

)

手动进行分区

对自动分区的表必须是在该表有手动分区的前提之下才能进行;对以下代码稍做解释,less than 的意思是小于的意思,所以p20191001这个分区里面存放的数据都是比2019年10月1日之前的数据,以此类推,p20191002存放的是10月1日的数据,p20191003存放的是10月2日的数据。

批量进行分区

alter table test_log partition by range columns(time)(

partition p20191001 values less than('2019-10-01'),

partition p20191002 values less than('2019-10-02'),

partition p20191003 values less than('2019-10-03'),

partition p20191003 values less than('2019-10-04')

);

单条增加分区

alter table test_log add partition (partition p20191003 values less than('2019-10-03'));

删除分区命令

因为数据都是存放在一个一个的分区当中,所以当分区删除的时间,对应的数据也会删除,例如以下命令会把10月3日的数据删除,可自行验证!

alter table test_log drop partition p20191004;

插入数据

insert into test_log values('2019-10-01 10:11:13', 'hi');

insert into test_log values('2019-10-02 10:12:10', 'ni');

insert into test_log values('2019-10-03 10:12:10', 'hao');

查看表分区

select partition_name, partition_description as val from information_schema.partitions

where table_name='test_log' and table_schema='test';

acf793abbd1a4d8c690f2b5c1b958197.png

创建增加分区和删除分区代码

注意:

1.以下代码可以手动mysql -u root -p登录到mysql执行

2.以下代码有table_name和table_schema是表和数据库名,一共6处,另外每次新增几个分区,删除几个旧分区,找到相应位置,根据需要自行修改,程序员多多少少大致能看懂的。

3.执行到delimiter ;继续按回车执行完毕,否则会出现无法执行命令的情况。

delimiter $$

DROP PROCEDURE IF EXISTS pro_test_log

$$

CREATE PROCEDURE pro_test_log()

BEGIN

DECLARE v_sysdate date;

DECLARE v_mindate date;

DECLARE v_maxdate date;

DECLARE v_pt varchar(20);

DECLARE v_maxval varchar(20);

DECLARE i int;

/*增加新分区代码,执行时,不要复制此行*/

SELECT max(cast(replace(partition_description, '''', '') AS date)) AS val

INTO v_maxdate

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_NAME = 'test_log' AND TABLE_SCHEMA = 'test';

set v_sysdate = sysdate();

WHILE v_maxdate <= (v_sysdate + INTERVAL 3 DAY) DO

SET v_pt = date_format(v_maxdate+ INTERVAL 1 DAY ,'%Y%m%d');

SET v_maxval = date_format(v_maxdate + INTERVAL 1 DAY, '%Y-%m-%d');

SET @sql = concat('alter table test_log add partition (partition p', v_pt, ' values less than(''', v_maxval, '''))');

-- SELECT @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET v_maxdate = v_maxdate + INTERVAL 1 DAY;

END WHILE;

/*删除旧分区,执行时,不要复制此行*/

SELECT min(cast(replace(partition_description, '''', '') AS date)) AS val

INTO v_mindate

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_NAME = 'test_log ' AND TABLE_SCHEMA = 'test';

WHILE v_mindate <= (v_sysdate - INTERVAL 6 DAY) DO

SET v_pt = date_format(v_mindate - INTERVAL 1 DAY,'%Y%m%d');

SET @sql = concat('alter table test_log drop partition p', v_pt);

-- SELECT @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET v_mindate = v_mindate + INTERVAL 1 DAY;

END WHILE;

END$$

delimiter ;

手动调用存储程序命令

每次新增分区和删除旧分区需要手动执行以下命令执行,一般一天执行一次,或都一小时都可以,但是我们人不能每天上班都去执行一次,接下来我们会使用事件,让mysql自动每天执行。

call proc_test_log;

开启事件

使用mysql的事件实现定时分区,mysql默认是不会开启事件例程的,需要手动打开,查看是否打开。

c5d6059747a4697cc61f8fdf59c53856.png

新建event事件

为了测试方便,间隔设置为分钟, 也可设置成小时 24 hour

DELIMITER $$

drop event if exists auto_pt $$

create event auto_pt

on schedule

every 1 minute

starts '2019-10-01 13:19:02'

do

BEGIN

call proc_test_log();

END$$

delimiter ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值