嵌套循环内连接带索引查询与过滤性能优化求助

一、创建(user_id, time)索引能否提升速度 #

能，但不是最优解，更推荐针对性创建联合索引。

当前执行流程的核心问题是：从library取出59631条user_id=20977的记录后，要逐条到plays表通过path索引查数据，再额外过滤user_id和time条件，这5万多次循环的累积开销直接导致查询慢到12秒多。

如果创建plays表的(user_id, time)索引，优化器可以先筛选出user_id=20977且time>=2022-10-17的记录，再关联library的path，能减少部分过滤开销，但还是需要匹配path。最优方案是创建plays表的(user_id, path, time)联合索引：

• 这个索引同时覆盖关联条件（user_id、path）和过滤条件（time），属于覆盖索引，不需要回表查询原数据，能直接在索引层面完成所有筛选和匹配，彻底消除逐条循环的额外过滤开销。

另外你plays表已有user_id_3索引(user_id,name,path,time)，但因为name不在查询条件中，这个索引的前缀匹配效率不如(user_id, path, time)，可以考虑替换，或者先执行ANALYZE TABLE plays;更新统计信息，看优化器是否会自动选中user_id_3。

二、为何library表选中(user_id, title)索引 #

你提到的user_id_2实际是library表的(user_id, title)索引，优化器选它的原因主要有两点：

1. 索引选择性判断：InnoDB中，单独的user_id索引和(user_id, title)索引都需要回表获取path字段（查询需要path关联plays），但优化器认为(user_id, title)的区分度更高（能更精准定位记录），所以优先选择它。
2. 统计信息过时：如果library表的统计信息没更新，优化器可能误判索引的扫描成本，比如认为(user_id, title)的扫描行数更少，从而做出这个选择。你可以执行ANALYZE TABLE library;更新统计信息，再查看执行计划是否变化。

另外，library表最适配当前查询的索引是**(user_id, path)联合索引**——它能直接覆盖查询需要的user_id和path字段，不需要回表，比(user_id, title)索引效率更高，建议创建这个索引，同时可以考虑删除冗余的user_id或user_id_by_title索引。

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