MySQL跨库关联UPDATE执行耗时2.4秒且mysqld CPU达140%的优化咨询

从表结构、监控指标、执行逻辑三个维度看，高CPU、慢UPDATE的核心原因没有复杂的架构矛盾，全是基础配置缺失导致的硬伤：

• 两张业务表的所有关联、过滤字段全部用了longtext类型，且没有针对业务查询建任何索引。InnoDB在无索引的情况下执行UPDATE，会扫描全表所有数据行，从监控数据看，单条UPDATE最多会打6.6万行的排他锁，所有并发的同类请求全部卡在锁等待队列里，CPU被全表扫描、锁排队逻辑占满
• users.ips表连业务主键都没有，InnoDB自动生成的无意义聚簇索引完全无法支撑快速行定位，更新时只能从表头逐行比对hwid、character_name的值，执行效率不可能达标
• 跨库关联的i.hwid = v.hwid条件，因为两边的hwid都是无索引的长文本，每次关联都要做全量数据比对，自然单条UPDATE耗时爬到2.4秒
• SQL里带的LOW_PRIORITY修饰符对InnoDB引擎没有实际优化效果，反而会增加额外的任务调度开销

1. 先修正不合理的字段类型 #

所有用于查询、关联的字段根本不需要用longtext（单字段最大支持4G存储）：

• 把users.ips、auth.visits中的hwid、ip、character_name、botid、botver、locale等字段，按照实际业务的最大长度改成对应的varchar类型，比如hwid用varchar(64)、ip用varchar(64)、character_name用varchar(64)、botid用varchar(128)，从根源降低文本比对、索引存储的成本

2. 补全匹配查询逻辑的业务索引 #

不用建覆盖全表的冗余索引，只需要匹配当前UPDATE的查询路径即可：

• 给users.ips建联合索引，覆盖WHERE条件的过滤逻辑：
  ALTER TABLE users.ips ADD INDEX idx_hwid_char_ip (hwid(64), character_name(64), ip(64));
  建完后优化器可以直接通过索引定位到匹配的目标行，不需要全表扫描，锁范围从几万行直接缩到个位数
• 给auth.visits建联合索引，覆盖跨表关联的过滤逻辑：
  ALTER TABLE auth.visits ADD INDEX idx_hwid_botid (hwid(64), botid(64));
  跨表关联时不需要全表扫描visits表，只需要走索引匹配同hwid下的botid值即可

3. 调整SQL写法，消除低效的跨表OR逻辑 #

把原来的跨表OR条件拆分，避免优化器因为跨表OR放弃索引，同时去掉多余的LOW_PRIORITY修饰，减少额外的调度开销，调整后的SQL如下：

UPDATE users.ips AS i 
LEFT JOIN (
  SELECT DISTINCT hwid FROM auth.visits 
  WHERE hwid = 'abc123' AND botid = 'lalaland'
) AS v ON i.hwid = v.hwid
SET i.last_time = NOW(), i.ping_count = i.ping_count + 1, i.botver='1.0' 
WHERE i.hwid = 'abc123' 
  AND i.character_name = 'exampletest' 
  AND (i.ip = '1.2.3.4' OR v.hwid IS NOT NULL)

4. 优化后的预期效果 #

补完索引、调整完写法之后，单条UPDATE的耗时会从2.4秒降到毫秒级，因为锁范围从几万行缩到1-2行，锁等待的开销基本消失，mysqld的CPU占用会降到正常个位数百分比的水平。如果业务允许，可以把每分钟单次更新改成攒批模式，把1分钟内的更新请求攒在内存里，每分钟统一批量执行一次，进一步降低数据库的压力。

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