为何MySQL 5.5 MyISAM表DELETE比SELECT慢？主键DELETE却很快

这问题我维护老系统时也碰到过，MyISAM的表级锁特性加上批量操作的逻辑，确实容易出现这种「SELECT快到飞起，DELETE慢到超时」的情况，咱们一步步拆解原因，再给可行的解决方案：

为什么会出现这种差异？ #

• MyISAM表级锁机制：MyISAM在执行DELETE时会锁定整个表，而非行级锁。如果你的DELETE语句需要扫描大量行（哪怕最终只删少量），或者带ORDER BY需要先排序，锁表时间会被拉得很长；而主键DELETE是直接定位到目标行，锁表时间极短，自然快。
• 执行计划差异：有时候SELECT能利用索引快速过滤，但DELETE优化器可能因为锁表成本、排序需求等原因，选择了全表扫描而非索引扫描，导致耗时飙升。
• ORDER BY的额外开销：带ORDER BY的DELETE，MySQL需要先把符合条件的行全部检索出来排序，这个过程不仅要消耗内存/CPU，还会让表在排序期间一直处于锁定状态，数据量大时直接超时。

可行的解决方案 #

1. 分批小量删除（最直接的应急方案） #

既然一次性删除锁表太久，那就拆成多次小批量删除，每次只删固定行数，减少锁表时间：

-- 每次删1000行，可根据数据量调整LIMIT值
DELETE FROM your_table 
WHERE [你的WHERE过滤条件] 
ORDER BY [你的排序字段] 
LIMIT 1000;

循环执行这条语句，直到返回的影响行数为0。这样每次操作锁表时间极短，不会阻塞其他业务。

2. 用临时表存主键ID，再分批主键删除 #

如果ORDER BY导致无法直接用JOIN DELETE，先把要删的主键ID用快的SELECT查出来存到临时表，再通过主键批量删除：

-- 1. 创建临时表存储要删除的主键ID（类型和原表主键一致）
CREATE TEMPORARY TABLE tmp_delete_ids (
    id INT PRIMARY KEY
) ENGINE=MyISAM;

-- 2. 用快速的SELECT把符合条件的ID插入临时表（复用你那耗时1秒的查询逻辑）
INSERT INTO tmp_delete_ids 
SELECT id FROM your_table 
WHERE [你的WHERE过滤条件] 
ORDER BY [你的排序字段];

-- 3. 分批通过主键删除，每次删1000行
DELETE t FROM your_table t
JOIN tmp_delete_ids tmp ON t.id = tmp.id
LIMIT 1000;

同样循环执行第3步的DELETE语句，直到影响行数为0。因为是通过主键匹配，删除速度和直接主键DELETE一样快，还避开了原DELETE的排序和全表扫描问题。

3. 重建表（适合删除大量数据的场景） #

如果要删除的行数占表数据量的比例很高（比如超过30%），直接重建表会比逐行DELETE快得多：

-- 1. 创建和原表结构完全一致的新表
CREATE TABLE your_table_new LIKE your_table;

-- 2. 把需要保留的数据插入新表（用NOT反向过滤删除条件）
INSERT INTO your_table_new 
SELECT * FROM your_table 
WHERE NOT ([你的WHERE过滤条件]);

-- 3. 原子替换原表（这一步几乎瞬间完成）
RENAME TABLE your_table TO your_table_old, your_table_new TO your_table;

-- 4. 确认数据无误后删除旧表
DROP TABLE your_table_old;

这个方法的优势是避免了大量DELETE的逐行锁表和磁盘IO，MyISAM的INSERT批量写入效率远高于批量DELETE。

4. 检查并优化执行计划 #

先对比SELECT和DELETE的执行计划，看看是不是DELETE没用到索引：

-- 查看DELETE的执行计划
EXPLAIN DELETE FROM your_table WHERE [你的WHERE条件] ORDER BY [排序字段];

-- 对比SELECT的执行计划
EXPLAIN SELECT * FROM your_table WHERE [你的WHERE条件] ORDER BY [排序字段];

如果DELETE的type字段是ALL（全表扫描），而SELECT是range或ref（索引扫描），那就要优化WHERE条件，确保用到合适的索引——比如给过滤字段加联合索引，或者调整WHERE条件的写法让优化器选择索引。

5. 调整MyISAM相关配置（辅助优化） #

如果服务器内存充足，可以调整以下配置提升排序和索引扫描效率：

• key_buffer_size：增大MyISAM的索引缓存大小，建议设为服务器内存的20%-30%（不要超过4G）
• sort_buffer_size：增大排序缓存，适合带ORDER BY的操作
• read_buffer_size：增大读缓存，提升全表扫描或范围扫描的速度
  注意：调整配置后需要重启MySQL生效，且要根据服务器实际内存情况调整，避免内存溢出。

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