现有DELETE操作走全表扫描，如何创建索引优化？技术咨询

首先，我们来拆解你的问题：你的表mytable主键是(latest_creation_date, t1, t2, data_category_id)，但当前DELETE语句触发了全表扫描，这大概率是因为查询过滤条件被重写后，数据库优化器认为全表扫描比走索引更高效——不过我们可以通过改写查询逻辑或者创建针对性索引来改变这个状况。

一、先尝试改写查询（无需新增索引） #

你的原查询逻辑是删除latest_creation_date = 0且不匹配指定(t1,t2)对的行，我们可以把它改写成更简洁的NOT IN形式，让优化器更容易识别并利用现有主键索引：

DELETE FROM mytable
WHERE latest_creation_date = 0
AND (t1, t2) NOT IN (
    (5, 10), (15, 20), (215, 320), (315, 420),
    (415, 520), (515, 620) -- 这里继续补充你的数百组条件
);

如果(t1,t2)的数量非常多（数百组），用NOT EXISTS结合临时表的方式通常会更高效，还能避免NOT IN可能带来的空值问题：

-- 创建临时表存储需要保留的(t1,t2)对，并加索引加速匹配
CREATE TEMP TABLE keep_pairs (
    t1 INT NOT NULL,
    t2 INT NOT NULL,
    PRIMARY KEY (t1, t2)
);

-- 插入所有需要保留的(t1,t2)组合
INSERT INTO keep_pairs VALUES
    (5,10), (15,20), (215,320), (315,420),
    (415,520), (515,620); -- 补充你的其他组合

-- 执行删除：删除latest_creation_date=0且不在保留列表中的行
DELETE FROM mytable m
WHERE m.latest_creation_date = 0
AND NOT EXISTS (
    SELECT 1 FROM keep_pairs k
    WHERE k.t1 = m.t1 AND k.t2 = m.t2
);

这种方式下，优化器可以利用你的主键索引快速定位所有latest_creation_date=0的行，再通过临时表的索引快速排除需要保留的行。

二、创建针对性索引（如果改写查询后仍无改善） #

如果改写查询后还是走全表扫描，可能是因为latest_creation_date=0的行占表的比例极高（比如超过30%），此时优化器会认为全表扫描更高效。但如果这部分行占比不大，你可以创建一个部分索引来精准覆盖你的查询场景：

CREATE INDEX idx_mytable_lcd0_t1_t2 ON mytable (t1, t2)
WHERE latest_creation_date = 0;

这个索引只包含latest_creation_date=0的行，并且按t1,t2排序，当执行你的DELETE查询时，优化器可以直接通过这个索引找到所有不在指定(t1,t2)列表中的行，彻底避免全表扫描。

三、额外注意事项 #

• 数据量评估：如果要删除的行数占latest_creation_date=0行的大多数，全表扫描其实是更高效的选择——因为索引扫描需要回表读取数据，而全表扫描可以直接批量处理。
• 主键的利用：你的现有主键索引(latest_creation_date, t1, t2, data_category_id)本身就可以用于快速定位latest_creation_date=0的行，改写查询后优化器应该会优先选择它，无需额外创建索引。

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