阿里云dms历史数据清理过程

dms 历史数据清理， xxx_history_detail_0476 保留35天的数据。

sql语句原始语句

DELETE FROM `xxx_history_detail_0476` WHERE `gmt_create` < SUBDATE(CURDATE(),INTERVAL 35 DAY);

如果gmt_create 没有索引，执行过程如下

LOG
explain SELECT 1 from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1


/* query from idb-toolkit */  SELECT `id` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` ORDER BY `id` ASC LIMIT 1

/* query from idb-toolkit */  SELECT `id` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` ORDER BY `id` DESC LIMIT 1

"/* Query from DMS-DATA_CORRECT-9399511-SJob_xxxxx by user xxxx */ explain /* query from idb-toolkit */
  SELECT `id` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) ORDER BY `id` ASC LIMIT 1"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124084') OR ( ( `id` = _binary'2124084') )) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124084') OR ( ( `id` = _binary'2124084') )) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124084') OR ( ( `id` = _binary'2124084') )) AND (( `id` < _binary'2124123') OR ( ( `id` = _binary'2124123') ))"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124123')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124123')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124123')) AND (( `id` < _binary'2124163') OR ( ( `id` = _binary'2124163') ))"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124163')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124163')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39


"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124163')) AND (( `id` < _binary'2124203') OR ( ( `id` = _binary'2124203') ))"


/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124203')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124203')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124203')) AND (( `id` < _binary'2124243') OR ( ( `id` = _binary'2124243') ))"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124243')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124243')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124243')) AND (( `id` < _binary'2124283') OR ( ( `id` = _binary'2124283') ))"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124283')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124283')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124283')) AND (( `id` < _binary'2124323') OR ( ( `id` = _binary'2124323') ))"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2124323')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2124323')) AND (( `id` < _binary'3183989') OR ( ( `id` = _binary'3183989') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xxx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2124323')) AND (( `id` < _binary'2124363') OR ( ( `id` = _binary'2124363') ))"

省略下面的内容

如果gmt_create 有索引，执行过程如下

LOG
select table_name, table_schema schema_name, create_time, create_time as last_ddl_time, table_comment description,ceil((data_length+index_length)/1024/1024 + ifnull(data_free, 0)/1024/1024) store_capacity,data_length data_bytes,index_length index_bytes, substring(table_collation, 1, if(locate('_', table_collation),locate('_', table_collation) - 1,length(table_collation))) encoding, table_collation as collation, auto_increment,table_rows num_rows,engine from information_schema.tables where table_type != 'VIEW' and table_schema = 'db_sc_xx_score_14'  and table_name in ('xxx_history_detail_0476') order by 1

select table_name, column_name ,column_type,is_nullable='YES' as nullable, column_default as default_value, extra='auto_increment' as auto_increment,column_comment AS description,numeric_precision as data_precision,numeric_scale as data_scale,character_maximum_length as data_length,  datetime_precision ,ordinal_position as position, collation_name as collation, character_set_name as encoding, extra ,generation_expression ,(EXTRA like 'VIRTUAL%' OR EXTRA like 'STORED%') as generation_column from information_schema.columns  where table_schema='db_sc_xx_score_14' and table_name in ('xxx_history_detail_0476') order by ordinal_position

SELECT DISTINCT table_name, index_name, IF(INDEX_NAME='PRIMARY','Primary',IF(NON_UNIQUE=1,IF(INDEX_TYPE='FULLTEXT','FullText',IF(INDEX_TYPE='SPATIAL','Spatial','Normal')),'Unique')) as index_type, column_name, sub_part,index_comment FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='db_sc_xx_score_14' and table_name in ('xxx_history_detail_0476')


/* Query from DMS-null-0-dc_explain_xxxx by user xxx */ explain SELECT * FROM `xxx_history_detail_0476` WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY)

explain SELECT 1 from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1

/* query from idb-toolkit */  SELECT `id` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` ORDER BY `id` ASC LIMIT 1

/* query from idb-toolkit */  SELECT `id` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` ORDER BY `id` DESC LIMIT 1

"/* Query from DMS-DATA_CORRECT-9399511-SJob_77419372_27205612964581902o by user xxx */ explain /* query from idb-toolkit */
  SELECT `id` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) ORDER BY `id` ASC LIMIT 1"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2152145') OR ( ( `id` = _binary'2152145') )) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2152145') OR ( ( `id` = _binary'2152145') )) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39


"DELETE FROM `db_sc_xx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2152145') OR ( ( `id` = _binary'2152145') )) AND (( `id` < _binary'2152184') OR ( ( `id` = _binary'2152184') ))"


/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2152184')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2152184')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39


"DELETE FROM `db_sc_xx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2152184')) AND (( `id` < _binary'2152224') OR ( ( `id` = _binary'2152224') ))"


/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2152224')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2152224')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2152224')) AND (( `id` < _binary'2152264') OR ( ( `id` = _binary'2152264') ))"


/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2152264')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2152264')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39


"DELETE FROM `db_sc_xx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2152264')) AND (( `id` < _binary'2152304') OR ( ( `id` = _binary'2152304') ))"


/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2152304')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2152304')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2152304')) AND (( `id` < _binary'2152344') OR ( ( `id` = _binary'2152344') ))"

/* query from idb-toolkit */ SELECT `id`,(SELECT 1 as isNeedDml from (SELECT `gmt_create` FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` where  (( `id` > _binary'2152344')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC  LIMIT 40 ) t WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) limit 1) as isNeedDml FROM `db_sc_xx_score_14`.`xxx_history_detail_0476` WHERE (( `id` > _binary'2152344')) AND (( `id` < _binary'3213236') OR ( ( `id` = _binary'3213236') )) ORDER BY `id` ASC LIMIT 1 OFFSET 39

"DELETE FROM `db_sc_xx_score_14`.`xxx_history_detail_0476`
WHERE `gmt_create` < SUBDATE(CURDATE(), INTERVAL 35 DAY) AND (( `id` > _binary'2152344')) AND (( `id` < _binary'2152384') OR ( ( `id` = _binary'2152384') ))"
省略下面的内容