MySQL排序查询索引选择及行锁影响问题咨询

一、MySQL未选用复合索引idx_id_status_err_code的原因及解决方法 #

可能原因 #

• 统计信息不准确：MySQL查询优化器依赖表的统计信息估算行数，若统计数据过时，会误判复合索引的效率不如主键索引。
• 范围查询降低索引利用率：你的复合索引顺序为id, status, err_code，查询中id > 123456是范围条件，优化器处理范围条件后，后续的status、err_code无法通过索引直接定位匹配行，只能在范围结果内逐一过滤。如果优化器估算该范围行数较多，会认为用主键索引直接扫描过滤的成本更低。

解决方法 #

1. 更新表统计信息
   执行ANALYZE TABLE table_a;，让MySQL重新收集表的统计数据，帮助优化器更准确判断索引效率。

2. 调整复合索引顺序
   将等值条件列放在范围条件列之前，创建新复合索引idx_status_err_code_id（顺序：status, err_code, id）。这样查询时可先通过status = -1和err_code = 100001快速缩小数据范围，再利用id > 123456做范围过滤，索引利用率会显著提升。

3. 强制使用指定索引
   修改SQL语句，添加FORCE INDEX强制优化器使用目标索引：

   select * from table_a FORCE INDEX(idx_id_status_err_code) where id > 123456 and status = -1 and err_code = 100001 order by id asc LIMIT 500
   

   注意：强制索引前需实际测试性能，避免因优化器原本的判断更准确而导致性能下降。

二、行锁（FOR UPDATE非共享模式）对查询的影响 #

• 若当前查询包含FOR UPDATE：你对目标行添加的排他行锁，不会影响自身查询执行，但会阻塞其他事务对这些行的写操作（更新、删除）以及其他事务对这些行添加排他锁的请求。
• 若当前查询是普通SELECT（无锁）：在InnoDB默认的REPEATABLE READ隔离级别下，查询会通过MVCC读取快照数据，不会被排他行锁阻塞；若使用READ COMMITTED隔离级别，会读取最新提交的数据，同样不会被阻塞。只有其他事务尝试修改被锁行时才会被阻塞。

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