最近做了个批处理,每天有30万条数据增加。数据量日益增大,查询效率会随之逐渐下降。因此考虑按天对表进行分区,保证查询效率。
1.表分区语法
为当前存在的表添加表分区(table_name:表名,timestap:date类型)
ALTER TABLE table_name PARTITION BY RANGE (TO_DAYS(timestap))
(
PARTITION p20200226 VALUES LESS THAN (TO_DAYS('2020-02-27')),
PARTITION p20200227 VALUES LESS THAN (TO_DAYS('2020-02-28')),
PARTITION p20200228 VALUES LESS THAN (TO_DAYS('2020-02-29')),
PARTITION p20200229 VALUES LESS THAN (TO_DAYS('2020-03-01')),
PARTITION p20200301 VALUES LESS THAN (TO_DAYS('2020-03-02')),
PARTITION p20200302 VALUES LESS THAN (TO_DAYS('2020-03-03')),
PARTITION p20200303 VALUES LESS THAN (TO_DAYS('2020-03-04'))
)
查看表分区(table_name:表名)
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='table_name';
删除分区:
alter table table_name drop partition p1;
不可以删除hash或者key分区。
一次性删除多个分区
alter table table_name drop partition p1,p2;
增加分区:
alter table table_name add partition (PARTITION p20200303 VALUES LESS THAN (TO_DAYS('2020-03-04')));
2.创建存储过程增加分区
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_Partition_monitor_ecs`$$
CREATE PROCEDURE `create_Partition_monitor_ecs`()
BEGIN
/* 事务回滚,其实放这里没什么作用,ALTER TABLE是隐式提交,回滚不了的。*/
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
/* 到系统表查出这个表的最大分区,得到最大分区的日期。在创建分区的时候,名称就以日期格式存放,方便后面维护 */
SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name='monitor_system_ecs' ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 2 DAY))+0;
SET @Current_date = DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
/* 修改表,在最大分区的后面增加一个分区,时间范围加1天 */
SET @s1=CONCAT('ALTER TABLE monitor_system_ecs ADD PARTITION (PARTITION p',@Current_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
/* 输出查看增加分区语句*/
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 提交 */
COMMIT ;
END$$
DELIMITER ;
3.创建定时任务,每天执行上一步中的存储过程
创建定时任务的前提是先检查mysql有没有开启定时任务:
SHOW VARIABLES LIKE 'event_scheduler';
查询结果是ON则已经开启,OFF则未开启。
如果是OFF,修改my.conf:在[mysqld]下添加一行:event_scheduler=1
然后重启mysql
DELIMITER ||
CREATE EVENT Partition_monitor_ecs_event
ON SCHEDULE
EVERY 1 DAY STARTS '2020-03-03 10:17:00'
DO
BEGIN
CALL `create_Partition_monitor_ecs`;
END ||
DELIMITER ;