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 1select 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')))"
省略下面的内容