如何改写UPDATE语句？带主键索引的更新语句性能优化咨询

给定的UPDATE语句通过主键entity_id定位目标行，但执行耗时过长，以下是针对性的优化方案：

• 减少不必要的字段更新：逐个检查SET子句里的字段，判断是否每次更新都需要修改。比如created_at一般只在插入时赋值，非业务强制要求就别每次覆盖；updated_at可以改成数据库自动维护（MySQL可设置ON UPDATE CURRENT_TIMESTAMP），无需手动传参。减少更新字段数量能直接降低锁持有时间和索引维护开销。

• 清理冗余二级索引：UPDATE操作会同步更新所有关联的二级索引，若表上存在过多非必要的二级索引（比如email、contact_no这类字段的索引），会大幅增加性能损耗。先梳理业务查询场景，删除那些低频使用的索引；如果某些索引字段更新频繁但查询少，直接移除更划算。

• 缩小事务范围：如果这个UPDATE是嵌套在大事务里执行，行锁会被长时间持有，加剧锁竞争。尽量把该操作放在独立的小事务中，执行完成后立即提交，缩短锁占用时长。要是批量处理这类更新，别循环单条执行，改用CASE WHEN批量处理多个entity_id，减少事务次数。

• 优化数据库配置与硬件：

  • 检查innodb_buffer_pool_size，确保足够容纳表数据，避免频繁磁盘IO；
  • 若用的是机械硬盘，换成SSD能大幅提升随机写性能；
  • 业务允许的话，把innodb_flush_log_at_trx_commit设为2，减少每次事务的磁盘刷写次数（注意权衡数据安全性）。

• 排查行锁等待：用SHOW ENGINE INNODB STATUS查看是否存在行锁等待，要是有其他事务持有目标entity_id的行锁，当前UPDATE会一直等待。排查是否有长事务未提交，或者存在SELECT ... FOR UPDATE这类占用行锁的操作。

优化后的语句示例 #

先修改表结构让updated_at自动更新：

ALTER TABLE `customer_entity` MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

再精简UPDATE语句（移除无需每次更新的字段）：

UPDATE `customer_entity` 
SET `website_id` = ?, `email` = ?, `group_id` = ?, `store_id` = ?,
    `disable_auto_group_change` = ?, 
    `firstname` = ?, `lastname` = ?, `password_hash` = ?, `rp_token` = ?, 
    `rp_token_created_at` = ?, `confirmation` = ?, `gender` = ?,
    `failures_num` = ?, `first_failure` = ?, `lock_expires` = ?,
    `contact_no` = ?, `alt_mobile_no` = ?, `employee_code` = ?,
    `mobileapp_customer_id` = ? 
WHERE (`entity_id`=?)

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