记录一次大表处理_java 大表处理-CSDN博客

本文链接：https://blog.csdn.net/lh19960925/article/details/125420741

1.查询当前wait的线程

show full processlist

2.分区

CREATE TABLE `locate_info` (
  `gid` varchar(36) NOT NULL,
  `car_id` varchar(36) DEFAULT NULL,
  `source_id` varchar(255) DEFAULT NULL,
  `equipment_gid` varchar(128) DEFAULT NULL,
  `sync_time` datetime NOT NULL,
  `lng` double DEFAULT NULL COMMENT '经度',
  `lat` double DEFAULT NULL COMMENT '纬度',
  `speed` double DEFAULT NULL COMMENT '当前车速，单位km/h',
  `direction` int(10) DEFAULT '0' COMMENT '方向 0-359，正北为 0，顺时针',
  PRIMARY KEY (`gid`,`sync_time`) USING BTREE,
  KEY `car_id` (`car_id`) USING BTREE,
  KEY `syncTime` (`sync_time`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
/*!50500 PARTITION BY RANGE  COLUMNS(sync_time)
(PARTITION p20220622 VALUES LESS THAN ('2022-06-22') ENGINE = InnoDB,
 PARTITION p20220623 VALUES LESS THAN ('2022-06-23') ENGINE = InnoDB,
 PARTITION p20220624 VALUES LESS THAN ('2022-06-24') ENGINE = InnoDB,
 PARTITION p20220625 VALUES LESS THAN ('2022-06-25') ENGINE = InnoDB) */;
 
 
 

-- 定义mysql提交查询语句 结束标识 为 $$
DELIMITER $$
DROP PROCEDURE IF EXISTS p_partition_test # 创建过程 p_partition_test
$$
CREATE DEFINER=`root`@`%` PROCEDURE `p_partition_test`()
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 into A from B 用法；A表作为临时表，不存在。 
  -- 不同于insert into A select from B，A必须存在。
  SELECT MAX(CAST(REPLACE(partition_description, '''', '') AS DATE)) AS val
  INTO   v_maxdate
  FROM   INFORMATION_SCHEMA.PARTITIONS
  WHERE  TABLE_NAME = 'locate_info' AND TABLE_SCHEMA = 'fxcg_0518';
  SET v_sysdate = SYSDATE(); # 赋值v_sysdate为当前时间
  
  -- INTERVAL 时间计算的关键字
  WHILE v_maxdate <= (v_sysdate + INTERVAL 1 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 locate_info add partition (partition p', v_pt, ' values less than(''', v_maxval, '''))');
    -- 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 = 'locate_info' AND TABLE_SCHEMA = 'fxcg_0518';
    
  -- 删除7天前的旧分区
  WHILE v_mindate <= (v_sysdate - INTERVAL 7 Day) DO
    SET v_pt = DATE_FORMAT(v_mindate,'%Y%m%d');
    SET @sql = CONCAT('alter table locate_info drop partition p', v_pt);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET v_mindate = v_mindate + INTERVAL 1 DAY;
  END WHILE;
 
END
-- 重新定义 MySQL提交查询语句的 结束标识 为 ;
DELIMITER ;


show variables like '%event_scheduler%';

set global event_scheduler = 1;

delimiter $$
drop event if exists auto_pt $$
create event auto_pt
on schedule
-- every 1 minute
every 1 day
starts '2022-06-23 23:00:00'
do
BEGIN
    call p_partition_test();
END$$
delimiter ;