添加索引能否解决锁等待超时？200万+数据表相关技术问询

问题背景 #

我们有一张超过200万条记录的数据表，通过UI和AWS Lambda/Step Functions两个渠道进行数据插入。当通过Lambda执行插入或更新操作时，数据库抛出报错：General error: 1205 Lock wait timeout exceeded; try restarting transaction。由于该表未创建任何索引，引发问题的UPDATE语句执行时扫描了1246661条记录，语句如下：

UPDATE xxx SET 
is_primary = 0, 
updated_at = '2022-11-23 09:36:11' 
WHERE x.id = 1684585 AND 
xxx.id is not null AND 
is_primary = 1 AND 
xxx.deleted_at IS NULL;

1. 为xxx_id添加索引能否加速该UPDATE查询？ #

绝对可以。当前这条UPDATE因为没有索引，必须全表扫描120多万条记录才能定位到目标行，这不仅执行缓慢，还会锁定大量无关数据行，直接触发锁等待超时。
给xxx.id添加索引后，数据库能通过索引快速定位到id=1684585的行，再过滤is_primary=1和deleted_at IS NULL的条件，扫描行数会从百万级降到个位数，执行时间大幅缩短，同时锁的范围也会极小，从根源上解决锁等待问题。如果能创建联合索引(id, is_primary, deleted_at)，效果会更好——查询时直接从索引就能完成过滤，无需回表查询原数据。

2. 为超200万条记录的表添加索引是否安全？ #

安全，但要选对操作时机。
200万条数据的表属于中等规模，加索引的操作在主流数据库（如MySQL）中是可控的：

• 避开业务高峰：加索引会消耗数据库的IO和CPU资源，高峰操作可能影响正常业务；
• 利用在线DDL：如果是InnoDB引擎，MySQL 5.6及以上版本支持Online DDL（在线创建索引），仅在操作开始和结束时有短暂锁表，几乎不会影响业务运行；
• 提前备份：虽然风险极低，但操作前做一次数据快照备份更稳妥。

3. 添加索引是否会产生负面影响，比如反而减慢更新操作？ #

会有轻微的性能开销，但完全在可接受范围内，且远小于当前锁等待问题的危害。
索引的本质是用空间换时间，代价是执行INSERT/UPDATE/DELETE时，数据库需要同步维护索引结构。但对于你这种单条记录的更新操作，维护索引的开销微乎其微——如果索引不包含updated_at字段，甚至不需要修改索引内容。
对比当前无索引的情况：每次UPDATE要扫描百万行、锁大量数据，导致业务超时失败，这个影响比维护索引的代价大得多。如果担心开销，优先创建覆盖查询的联合索引，既能提升查询效率，又能把索引维护的代价降到最低。

内容的提问来源于stack exchange，提问作者Schrödinger's Cat