使用WHERE EXISTS条件时MySQL宕机及性能异常问题求助

遇到带WHERE EXISTS的查询直接搞崩MySQL的情况确实挺闹心的，我来帮你一步步排查和解决：

首先你得知道这个查询到底在干啥，会不会是全表扫描把资源耗尽了。跑一下这个命令：

EXPLAIN DELETE FROM `records` where exists ( select * from `measurements` where `file_id` = 17 and measurements.id = records.measurement_id )

重点看这几个点：

• type列是不是ALL（全表扫描），如果是说明没有用到索引，GB级表全扫肯定会把资源吃光
• key列有没有显示用到的索引，要是空的，那就是索引缺失的问题
• rows列预估的扫描行数，如果是几十万甚至上百万，那就是查询效率极低的根源

你的查询是关联measurements.id和records.measurement_id，还要过滤measurements.file_id=17，这几个字段的索引大概率没建对：

• 给measurements建联合索引：CREATE INDEX idx_measurements_fileid_id ON measurements(file_id, id); 这个索引能让数据库快速定位到file_id=17的行，同时直接拿到id去关联records
• 给records建单字段索引：CREATE INDEX idx_records_measurement_id ON records(measurement_id); 让关联查询不用扫整个records表

GB级数据的查询非常吃内存和磁盘IO，你得看看MySQL的配置是不是跟不上：

• 查看InnoDB缓冲池大小：SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 如果这个值远小于你的表总大小（比如表加起来有10GB，但缓冲池只给了2GB），那数据库会频繁读写磁盘，直接拖垮服务。建议调整到服务器内存的50%-70%（比如16GB内存的话设成8G-12G）
• 执行查询时用top看CPU占用，iostat看磁盘IO，如果磁盘IO使用率直接到100%，那就是磁盘性能跟不上，或者查询太耗IO

其他库类似查询正常，那你得对比主库和其他库的差异：

• 是不是主库的records和measurements数据量比其他库大很多？
• 其他库的这两张表有没有建合适的索引，而主库没有？
• 检查存储引擎：是不是主库用了MyISAM而其他库用InnoDB？MyISAM在大表查询时锁表更严重，容易导致服务无响应
• 看MySQL参数：比如innodb_flush_log_at_trx_commit、sync_binlog这些参数，主库可能因为要保证数据安全设置得更严格，导致写入（比如DELETE）更慢

如果暂时找不到根本原因，你可以先改写查询，降低资源占用：

• 用JOIN代替EXISTS，有时候MySQL对JOIN的优化更好：

DELETE r FROM `records` r
JOIN `measurements` m ON m.id = r.measurement_id
WHERE m.file_id = 17;

• 分批删除，避免一次性处理大量数据：

DELETE r FROM `records` r
JOIN `measurements` m ON m.id = r.measurement_id
WHERE m.file_id = 17
LIMIT 1000;

循环执行这个命令，直到删除行数为0，这样每次只处理1000条，不会一下子把资源占满

MySQL宕机时肯定会在错误日志里留下痕迹，去看看日志文件（一般在/var/log/mysql/error.log或者数据目录下的[hostname].err），有没有这些关键词：

• Out of memory：内存不足直接崩了
• Deadlock found：死锁导致服务异常
• Disk full：磁盘满了写不进去
• InnoDB: Assertion failure：InnoDB引擎的内部错误，可能需要修复表或者升级MySQL版本

内容的提问来源于stack exchange，提问作者Vlad Vladimir Hercules