Mysql按天自动表分区教程
前言
在使用mysql时，往往用一张表来存放数据，不同的业务可能产生的数据量也不同，有的业务可能一天需要插入几万条数据，也有的则更多，即使量很小日积月累数据库表的数据堆积也会越来越多，需要人工去删除数据，当数据达到百万或千万级别时，mysql查询速度明显下降，性能也随之下降，解决该问题的办法有很多，比如添加索引、优化SQL，但这都只是治标不治本的做法，无法从根本上提高Mysql的性能和查询速度。
什么是表分区？
mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。
表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。
创建测试数据库
create database test charset=utf8;
- 1
创建测试表
use test; // 先切换到test数据库
create table test_log
(
time datetime,
msg varchar(2000)
)
- 1
- 2
- 3
- 4
- 5
- 6
手动进行分区
对自动分区的表必须是在该表有手动分区的前提之下才能进行；对以下代码稍做解释，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')
);
- 1
- 2
- 3
- 4
- 5
- 6
单条增加分区
alter table test_log add partition (partition p20191003 values less than('2019-10-03'));
- 1
删除分区命令
因为数据都是存放在一个一个的分区当中，所以当分区删除的时间，对应的数据也会删除，例如以下命令会把10月3日的数据删除，可自行验证！
alter table test_log drop partition p20191004;
- 1
插入数据
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');
- 1
- 2
- 3
查看表分区
select partition_name, partition_description as val from information_schema.partitions
where table_name='test_log' and table_schema='test';
- 1
- 2
创建增加分区和删除分区代码
注意：
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 ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
手动调用存储程序命令
每次新增分区和删除旧分区需要手动执行以下命令执行，一般一天执行一次，或都一小时都可以，但是我们人不能每天上班都去执行一次，接下来我们会使用事件，让mysql自动每天执行。
call proc_test_log;
- 1
开启事件
使用mysql的事件实现定时分区，mysql默认是不会开启事件例程的，需要手动打开，查看是否打开。
新建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 ;