MySQL复合主键下如何高效利用索引删除全设备过期时序数据

你的问题本质是对InnoDB复合聚簇索引的排序逻辑理解有偏差，核心规则和原因如下：

• 你定义的复合主键(device_id, timestamp)对应的聚簇索引，排序优先级是先按device_id全局升序，同一个device_id下的数据才会按timestamp升序连续存储。不存在全局按timestamp排序的连续数据块，所有超过1年的过期数据是散落在每一个device_id对应的独立数据分片里的。
• 复合索引的最左前缀匹配规则要求：如果要对索引中靠右的列（这里是timestamp）做范围扫描，必须保证索引中靠左的所有列（这里是device_id）是固定的等值匹配条件，否则优化器无法直接定位到对应的连续索引段。

你写的全量删除SQL无法命中索引的具体原因 #

你尝试执行的全量删除SQL逻辑如下：

DELETE FROM device_data WHERE device_id in
  (SELECT device_id FROM device_data GROUP BY device_id)
  AND timestamp <= CURDATE() - INTERVAL 1 YEAR LIMIT 10000;

这条语句走不了索引有两个直接原因：

1. 最左列device_id的匹配条件不是常量等值集合：你用了一个关联子查询从原表取全量device_id，这个子查询本身就需要做全表扫描才能拿到结果，优化器成本估算时会认为全表扫描逐行校验timestamp条件，比“全表扫拿ID→再回表查数据”的路径成本更低。
2. 即使优化器识别到要按device_id匹配，没有明确的常量ID列表时，它也无法跳过全局排序的device_id维度，直接定位到每个设备下的timestamp过期数据段，自然用不上索引的范围扫描能力。

硬编码批量ID的方案能命中索引的原因 #

你现在用脚本每批取100个device_id硬编码到IN条件里的方案，刚好符合复合索引的最左匹配规则：

• 当IN条件里是明确的常量ID列表时，优化器会把列表拆成多个独立的等值匹配条件，对每一个device_id，都可以直接通过聚簇索引定位到该设备对应的连续数据段，然后在这个段内直接做timestamp的范围扫描，快速定位要删除的行，完全不需要扫描全表。
• 这个方案本质是把多个单设备删除的高效查询合并成了一次批量请求，和你单设备删除能命中索引的底层逻辑完全一致。

场景优化建议 #

针对IoT时序数据定期删历史数据的场景，有两个比你现有脚本方案效率更高的选择：

• 如果你继续用现有表结构：不要从device_data表中group by取device_id，而是从独立存储的设备信息维表中拿全量设备ID，再分批传入删除语句，避免每次取ID都扫大表；每批删除后留几百毫秒的休眠时间，避免删除操作打满数据库IO影响线上写入。
• 长期最优方案：把表改成按timestamp做范围分区的分区表，删除超过1年的历史数据时直接DROP PARTITION即可，属于秒级完成的元数据操作，完全不需要逐行删除数据，是时序类场景的标准实践。

注：你贴的建表语句存在笔误，主键定义里写的是`time，但表中时间列名是timestamp，实际执行会报错，需要修正为PRIMARY KEY (device_id, timestamp)。

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