Mysql按天自动表分区教程

前言

在使用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';

在这里插入图片描述

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

注意：
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默认是不会开启事件例程的，需要手动打开，查看是否打开。
在这里插入图片描述

新建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 ;