如何优化峰值时段执行过慢的带UPDATE语句的SQL请求

1. 索引优化 #

• 优先确认events表的hash字段是否存在有效索引：这条更新语句唯一过滤条件是hash，如果没有索引会触发全表扫描，峰值IO压力下执行时间会指数级上升。
• 如果未建索引，执行以下语句创建索引即可：

CREATE INDEX idx_events_hash ON `events`(`hash`);

如果hash字段值全局唯一，建议创建唯一索引，查询性能会更优。

2. 锁竞争问题优化 #

更换服务器无改善大概率是同一条记录的行锁竞争导致：峰值时大量请求同时更新同一个hash对应的行，后续请求会排队等待前序更新释放行锁，这是硬件升级无法解决的逻辑层问题，可通过以下方案优化：

• 内存聚合更新：业务侧将同一hash的计数更新请求先攒入内存队列，聚合多条请求后只执行一次数据库更新，比如每100次计数更新只做一次count = count + 100的操作，大幅降低行锁争抢频率。
• 乐观锁改造：表内新增version版本号字段，更新时携带版本号校验，校验失败则重试，减少锁持有时长，适合更新频率中等的场景，极高频率场景优先用内存聚合方案。

3. 数据库配置优化 #

• 调整事务隔离级别：将默认的可重复读（RR）调整为读提交（RC），可以减少锁范围和锁持有时间，降低锁冲突概率。
• 优化数据库写入相关参数：增大Buffer Pool、调整Redo Log刷盘策略、优化写入缓存配置，减少峰值时的IO等待耗时。

4. 架构层优化 #

如果这类计数器更新请求量级非常高，可以调整存储架构：

• 用Redis这类内存数据库承接计数器的写请求，直接在Redis中完成计数累加，再通过异步任务将数据定时同步到MySQL中。
• 直接将计数器类的高频更新数据完全迁移到内存数据库存储，完全规避关系型数据库的行锁、IO性能瓶颈。

5. 问题排查验证 #

• 执行EXPLAIN命令确认语句执行计划是否符合预期，确认索引是否生效：

EXPLAIN UPDATE "events"
SET "metas" = 732899,
    "count" = "count" + 1,
    "timestamp" = 1633450429
WHERE "hash" = 'my_counter_453751'

• 峰值时段抓取数据库慢日志、锁等待日志，确认具体的耗时瓶颈点。

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