MySQL按月自动创建分区表(千万级大表优化)

什幺是表分区？

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。

MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件。MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。

分区表的好处是

1、可以让单表存储更多的数据。

2、分区表的数据更容易维护，可以通过删除与那些数据有关的分区，更容易删除数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。

3、部分查询能够从查询条件确定只落在少数分区上，查询速度会很快。

4、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

新建分区表

-- 假设有个表叫tmp_logs,设置分区条件为按end_time按月分区
DROP TABLE IF EXISTS `tmp_logs`;
CREATE TABLE `tmp_logs` (
  `id` int(11) UNSIGNED 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(end_time))(
  PARTITION p_202112 VALUES LESS THAN (TO_DAYS('2022-01-01')),
  PARTITION p_202201 VALUES LESS THAN (TO_DAYS('2022-02-01')),
  PARTITION p_202202 VALUES LESS THAN (TO_DAYS('2022-03-01')),
  PARTITION p_202203 VALUES LESS THAN (TO_DAYS('2022-04-01'))
);

存储过程，每月创建新的分区

-- create_table_partition 为创建表分区，调用后为该表创建到下月结束的表分区
DELIMITER $
DROP PROCEDURE IF EXISTS create_table_partition$
CREATE PROCEDURE `create_table_partition`(IN `table_name` varchar(64))
BEGIN	
    SET @next_month = CONCAT(date_format(date_add(now(),interval 2 month),'%Y-%m'),'-01');
    SET @next_p = CONCAT(date_format(date_add(now(),interval 1 month),'%Y%m') );
    SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '`', ' ADD PARTITION (PARTITION p_', @next_p, " VALUES LESS THAN (TO_DAYS('", @next_month ,"')) );" );
    PREPARE STMT FROM @SQL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
END$
DELIMITER ;

存储过程，删除历史分区，空间回收

-- delete_table_partition 为删除N月前的表分区，方便历史数据空间回收
DELIMITER $
DROP PROCEDURE IF EXISTS delete_table_partition$
CREATE PROCEDURE `delete_table_partition`(`str_table_name` VARCHAR(64),`int_reserved_month` INT)
BEGIN
	DECLARE str_part_name VARCHAR(64);
	DECLARE done INT DEFAULT 0;
	DECLARE cursor1 CURSOR FOR SELECT partition_name from information_schema.partitions where table_schema = 'webrtc'
			and table_name=str_table_name and partition_description<=TO_DAYS(CONCAT(date_format(date_sub(now(),interval int_reserved_month month),'%Y-%m'),'-01'));
  	DECLARE CONTINUE HANDLER FOR  SQLSTATE '02000' SET done = 1;

  	open cursor1;
  	read_loop: LOOP
    FETCH cursor1 INTO str_part_name ;
    IF done=1 THEN
      LEAVE read_loop;
    END IF;
	SET @SQL = CONCAT( 'ALTER TABLE `', str_table_name, '` DROP PARTITION ', str_part_name, ";" );	

	PREPARE STMT FROM @SQL;
	EXECUTE STMT;
	DEALLOCATE PREPARE STMT;
	
   END LOOP;
   CLOSE cursor1;
	
END$
DELIMITER ;

触发器，每月自动新建分区，并删除旧分区

-- 创建一个Event,每个月的一号凌晨1点执行存储过程，自动创建创建表分区，同时最多保存6个月的数据
DELIMITER $
CREATE EVENT IF NOT EXISTS `event_records_auto_partition`
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
    BEGIN
        call create_table_partition('tmp_logs');
        call delete_table_partition('tmp_logs',18);
    END$
DELIMITER ;

备注，MySQL EVENT 操作事项：

要使定时事件起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1。

1、查看scheduler的当前状态：

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;

2、修改scheduler状态为打开(0:off , 1:on)：

SHOW VARIABLES LIKE 'event_scheduler'; -- 查看是否开启定时器(OFF：关闭，ON：开启)

3、临时打开定时器(四种方法)：

a、SET GLOBAL event_scheduler=ON;
b、SET @@global.event_scheduler=ON;
c、SET GLOBAL event_scheduler=1;
d、SET @@global.event_scheduler=1;

4、永久生效的方法，修改配置文件my.cnf

event_scheduler = 1 #或者ON

5、临时开启某个事件

ALTER EVENT ent_test ENABLE;

6、临时关闭某个事件

ALTER EVENT ent_test DISABLE;