DELETE语句偶发耗时过久致CPU满载、数据库锁等待问题排查与优化咨询

• 检查time字段索引：执行EXPLAIN DELETE FROM logs WHERE time < NOW() - INTERVAL 1 WEEK;，查看执行计划是否用到索引。如果没有索引，每次删除都会全表扫描，数据量波动或并发操作时极易卡顿。
• 排查长事务与锁冲突：用SHOW PROCESSLIST;查看是否有长时间运行的INSERT/UPDATE操作未提交，或者SHOW ENGINE INNODB STATUS;查看事务锁状态。未释放的行锁/表锁会阻塞DELETE执行，导致其长时间处于updating状态，进而引发全表级锁等待。
• 确认存储引擎类型：如果是MyISAM引擎，本身就是表级锁机制，任何写操作都会锁全表，若此时有其他写请求或大查询，必然导致阻塞。若为InnoDB，可能是DELETE扫描范围过大触发间隙锁，或是有DDL操作在后台执行抢占锁资源。
• 检查服务器资源波动：查看对应时间点的CPU、磁盘IO、内存监控日志，确认是否有备份、其他大查询等进程占用资源，导致DELETE无法获取足够资源执行。
• 检查表碎片情况：执行SHOW TABLE STATUS LIKE 'logs';查看Data_free字段值，若碎片过多，删除操作需要额外整理碎片，会大幅增加耗时。

• 分批删除：避免一次性删除大量数据，改为每次删除固定行数，循环执行直至无符合条件数据。示例语句：

  DELETE FROM logs WHERE time < NOW() - INTERVAL 1 WEEK LIMIT 1000;
  

  这种方式每次锁的行数少，不会长时间占用表锁，降低对其他业务的影响。
• 给time字段添加索引：执行CREATE INDEX idx_logs_time ON logs(time);，让DELETE快速定位目标行，避免全表扫描，减少锁持有时间与CPU消耗。
• 使用分区表：按时间维度创建RANGE分区（比如每周一个分区），删除旧数据时直接DROP分区，这是效率最高的方式，不会产生锁等待问题。示例分区操作：

  -- 创建分区表（示例）
  CREATE TABLE logs (
      id INT,
      time DATETIME
  ) PARTITION BY RANGE (TO_DAYS(time)) (
      PARTITION p_week1 VALUES LESS THAN (TO_DAYS('2024-05-01')),
      PARTITION p_week2 VALUES LESS THAN (TO_DAYS('2024-05-08')),
      ...
  );
  -- 删除旧分区
  ALTER TABLE logs DROP PARTITION p_week1;
  

• 调整执行时机：将删除任务安排在业务低峰期（如凌晨）执行，减少与正常业务查询的锁冲突。
• 清理表碎片：若碎片过多，可在低峰期执行OPTIMIZE TABLE logs;整理碎片（注意此操作会锁表）。

内容的提问来源于stack exchange，提问作者penguin_P