mysql 数据量大，使用表分区解决

根据时间范围对数据库表进行分区，提高查询速度，这里不介绍分区的基本语法，只提供一套解决方案

一、初始化表分区

修改表分区语句，使用此语句来拼接初始化分区语句，此操作会删除其他分区，但数据依然存在。

ALTER TABLE `table_name` PARTITION by RANGE(to_days(`date_field`)) (	
PARTITION part20220710 VALUES LESS THAN (to_days('2022-07-10'))
);

使用 ALTER TABLE table_name PARTITION 修改表分区是因为： ALTER TABLE table_name add PARTITION 添加表分区则只能按最大分区递增添加

注意：

• 修改表分区所划分的分区需要确保所有 date_field 都有归属分区

• 如果表中有主键，时间字段 date_field 需要和其组成联合主键

1、创建初始化分区存储过程

以指定日期往前 180 天，以天为单位创建分区，具体参数 table_name、date_field 等可以根据自己的需求调整

CREATE PROCEDURE proc_init_table_name_partition()
BEGIN

	set @i = 0;
	set @start_date = date(date_sub('指定日期',interval 180 day));
	set @init_partition_sql = 'ALTER TABLE table_name PARTITION by RANGE(to_days(date_field))(	';
	
	-- 循环拼接初始化语句
	WHILE @i<=180 DO
		set @partition_date = DATE_ADD(@start_date,interval @i day);
		-- 分区名以【part+日期】格式存放，便于后续定时修改分区
		set @part_name = concat('part',date(@partition_date)+0);
		
		set @init_partition_sql = concat(@init_partition_sql
		,if(@i = 180,concat('PARTITION ',@part_name,' VALUES LESS THAN (to_days(''',@partition_date,''')));')
		,concat('PARTITION ',@part_name,' VALUES LESS THAN (to_days(''',@partition_date,''')),'))
		);
		set @i = @i+1;
	END WHILE;
	
	-- 执行初始化语句
	-- select @init_partition_sql;
	PREPARE stmt2 FROM @init_partition_sql;
	EXECUTE stmt2;
	DEALLOCATE PREPARE stmt2;
END

2、调用存储过程初始化分区

call proc_init_table_name_partition();

二、定时修改表分区

1、创建修改表分区存储过程

drop procedure if exists proc_add_table_name_partition;

CREATE PROCEDURE proc_add_table_name_partition()
BEGIN
    -- 1.修改表，删除最小分区
    select partition_name into @p0_name from INFORMATION_SCHEMA.PARTITIONS 
    where table_schema = '数据库名' and table_name='表名' order by partition_ordinal_position limit 1;
		
    SET @s1=concat('ALTER TABLE table_name DROP PARTITION ',@p0_name);
		
    -- 执行删除最小分区预处理语句
    PREPARE stmt1 FROM @s1; 
    EXECUTE stmt1; 
    DEALLOCATE PREPARE stmt1; 
		
    -- 2.到系统表查出这个表的最大分区，通过分区名得到最大分区的日期。在创建分区的时候，名称以【part+日期】格式存放
    SELECT REPLACE(partition_name,'part','') INTO @pmax_name FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE table_schema = '数据库名' and table_name='表名' ORDER BY partition_ordinal_position DESC LIMIT 1;
		
    SET @max_date= DATE(DATE_ADD(@pmax_name, INTERVAL 1 DAY))+0;
	
    -- 修改表，在最大分区的后面增加一个分区，时间范围加1天 
    SET @s2=CONCAT('ALTER TABLE table_name ADD PARTITION (PARTITION part',@max_date,' VALUES LESS THAN (TO_DAYS (''',@max_date,''')+1))');
    
    -- 执行添加最大分区预处理语句
    PREPARE stmt2 FROM @s2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

END

2、创建定时事件执行存储过程

创建前检查mysql的事件计划是否开启

SHOW VARIABLES LIKE 'event_scheduler';

如果为 OFF 使用 SET GLOBAL event_scheduler = ON; 开启

创建每日执行一次的定时事件执行存储过程 proc_add_table_name_partition 来修改表分区

DROP EVENT IF EXISTS add_table_name_partition_schedule_event;	

CREATE EVENT add_table_name_partition_schedule_event
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(CURDATE(), INTERVAL 23 HOUR)  -- 每天 23 点执行一次
ON COMPLETION PRESERVE
ENABLE
DO call prcd_add_table_name_partition()	

至此，针对表数据量过大，使用分区来处理的解决方案完成，可根据自己需求进行调整