为何带索引列与合理内连接的MariaDB UPDATE耗时5秒？

1. 排查MyISAM表级锁冲突 #

MyISAM采用表级锁机制，UPDATE操作会锁定整张表：

• 执行SHOW PROCESSLIST查看当前数据库线程状态，确认是否有其他长耗时的读写操作（如批量SELECT、INSERT、其他UPDATE）占用表锁，导致目标UPDATE等待锁释放。
• 检查是否存在多个写操作排队的情况，MyISAM写锁优先级高于读锁，若有大量写请求堆积，会导致单条UPDATE耗时被拉长。

2. 深挖执行计划细节 #

虽然EXPLAIN显示索引已被使用，仍需确认执行逻辑是否符合预期：

• 用EXPLAIN EXTENDED执行目标UPDATE语句，再运行SHOW WARNINGS查看优化后的实际执行语句，排查是否存在字段类型不匹配、隐式转换导致索引未被高效利用的情况。
• 确认products_sort_order字段是否存在索引：若该字段有索引，即使无实际数据修改，MyISAM仍可能触发索引校验逻辑；若索引存在，可临时移除后测试UPDATE耗时（测试后恢复）。

3. 检查表碎片与统计信息 #

MyISAM表长期读写易产生碎片，过时的统计信息也可能影响优化器决策：

• 执行SHOW TABLE STATUS LIKE 'products'和SHOW TABLE STATUS LIKE 'product_premier_extra'，查看Data_free字段值，若数值较大说明存在较多碎片，可在低峰期执行OPTIMIZE TABLE整理碎片（注意此操作会锁表）。
• 运行ANALYZE TABLE products, product_premier_extra更新表统计信息，再重新测试UPDATE耗时，确认是否因统计信息过时导致执行计划低效。

4. 验证MySQL配置参数影响 #

重点检查与MyISAM性能相关的配置：

• 查看key_buffer_size参数：执行SHOW VARIABLES LIKE 'key_buffer_size'和SHOW STATUS LIKE 'Key_read%'，计算索引缓存命中率（(Key_reads / Key_read_requests) * 100），若命中率低于99%，说明索引缓存不足，可适当调大key_buffer_size（建议不超过物理内存的1/4）。
• 检查tmp_table_size和max_heap_table_size：若临时表超出内存限制会写入磁盘，增加IO耗时，可确认这两个参数是否足够覆盖关联操作所需的临时表大小。

5. 拆分UPDATE语句验证 #

将关联UPDATE拆分为两步，排查是否是关联逻辑导致的额外开销：

1. 先查询出目标products_id列表：

   SELECT pe.products_id 
   FROM product_premier_extra pe 
   WHERE pe.model = 'xxx' 
     AND pe.style = 'xxx' 
     AND pe.variation = 'xxx';
   

2. 用IN子句执行UPDATE：

   UPDATE products 
   SET products_sort_order = xxx 
   WHERE products_id IN (...); -- 填入上一步的products_id列表
   

若拆分后UPDATE耗时显著降低，说明关联更新时MyISAM的锁机制或索引遍历逻辑存在额外开销。

6. 排查触发器等附加逻辑 #

确认两张表是否存在自定义触发器，导致UPDATE时执行额外操作：

• 执行SHOW TRIGGERS LIKE 'products'和SHOW TRIGGERS LIKE 'product_premier_extra'，检查是否有触发器在UPDATE时执行批量查询、更新其他表等耗时操作。

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