MySQL查询耗时过长引发死锁问题求助

一、核心问题分析 #

从执行计划和表信息来看，当前查询的性能瓶颈主要来自三个方面：

1. 主表查询依赖临时表和文件排序（Using temporary; Using filesort），排序操作开销过大；
2. 关联表virt_assist_bandwidth_incoming_media触发全表扫描，未利用messageID索引，关联效率极低；
3. MyISAM引擎的表级锁机制，长时间查询会引发锁等待（并非InnoDB的行级死锁，而是表锁竞争导致的阻塞）。

二、针对性优化方案 #

1. 索引优化（优先级最高） #

主表virt_assist_bandwidth_incoming

当前使用的vidx_8索引(from_id, sms_type, messageID)未包含排序字段received，导致排序时必须创建临时表并执行文件排序。建议创建覆盖索引：

CREATE INDEX vidx_9 ON virt_assist_bandwidth_incoming (from_id, sms_type, received DESC, messageID);

该索引可直接满足WHERE条件过滤、ORDER BY排序需求，同时提供关联所需的messageID，无需回表查询原数据，彻底消除Using temporary和Using filesort。

关联表virt_assist_bandwidth_incoming_media

执行计划中该表全表扫描的核心原因是字符集不匹配：主表messageID为utf8字符集，关联表为latin1，字符串比较时MySQL无法使用索引。解决方法：

• 统一字符集，将关联表的messageID字段改为utf8：

ALTER TABLE virt_assist_bandwidth_incoming_media MODIFY COLUMN messageID varchar(55) DEFAULT NULL CHARSET utf8;

修改后，关联操作会自动利用vabimind_1索引，避免全表扫描。

2. 查询语句优化 #

• **避免SELECT ***：只查询业务需要的字段，减少数据传输量和内存占用（例如不需要text、media等大字段时直接排除）：

SELECT i.messageID, i.from_id, i.sms_type, i.received, m.media
FROM virt_assist_bandwidth_incoming i
LEFT JOIN virt_assist_bandwidth_incoming_media m ON m.messageID = i.messageID
WHERE i.from_id = 0  -- from_id是int类型，用数字0而非字符串'0'，避免隐式转换
  AND i.sms_type = 0
ORDER BY i.received DESC;

• 分页处理：如果业务不需要一次性返回19万条数据，添加LIMIT分页，大幅降低排序和数据传输开销：

SELECT ... 
WHERE ... 
ORDER BY ... 
LIMIT 100 OFFSET 0;

3. 锁等待（伪死锁）问题解决 #

MyISAM引擎采用表级锁，长时间查询会持有读锁，阻塞其他写操作（或被写操作阻塞），表现为类似“死锁”的现象。解决建议：

• 迁移表到InnoDB引擎：支持行级锁，大幅降低锁冲突概率：

ALTER TABLE virt_assist_bandwidth_incoming ENGINE=InnoDB;
ALTER TABLE virt_assist_bandwidth_incoming_media ENGINE=InnoDB;

• 排查锁竞争：用SHOW PROCESSLIST查看当前运行的线程，确认是否有长时间写操作（如UPDATE、INSERT）与该查询竞争表锁。

4. 其他优化 #

• 消除隐式类型转换：from_id是int(11)类型，查询中使用'0'字符串会触发隐式转换，统一用数字0更稳妥；
• 整理MyISAM表碎片：若暂时不迁移引擎，执行OPTIMIZE TABLE整理表碎片，提升查询效率：

OPTIMIZE TABLE virt_assist_bandwidth_incoming;
OPTIMIZE TABLE virt_assist_bandwidth_incoming_media;

三、排查验证步骤 #

1. 先修复字符集不匹配问题，重新查看执行计划，确认m表的type变为ref；
2. 创建覆盖索引vidx_9，验证主表是否消除Using temporary和Using filesort；
3. 修改查询语句，避免SELECT *，测试性能变化；
4. 若锁问题仍存在，迁移到InnoDB引擎并观察锁竞争情况。

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