关于 mysql 联表执行顺序问题

user 表的记录有几百万，update_time 有索引，limit 0,20 和 limit 19999,20 的效果也是一样

SELECT * FROM USER user0_ LEFT JOIN user_statistic userstatis1_ ON user0_.user_statistic_id = userstatis1_.id LEFT JOIN language_level languagele2_ ON user0_.language_level_id = languagele2_.id LEFT JOIN user_contact usercontac3_ ON user0_.user_contact_id = usercontac3_.id LEFT JOIN user_social_info usersocial4_ ON user0_.user_social_info_id = usersocial4_.id LEFT JOIN user_detail userdetail5_ ON user0_.user_detail_id = userdetail5_.id WHERE user0_.update_time > '2021-06-23 09:40:00.019' ORDER BY user0_.update_time ASC LIMIT 0, 20 执行计划 1 SIMPLE user0_ range idx_update_time idx_update_time 6 1143267 100 Using index condition; Using temporary; Using filesort 1 SIMPLE userstatis1_ eq_ref PRIMARY PRIMARY 150 flo.user0_.user_statistic_id 1 100 1 SIMPLE languagele2_ ALL 7 100 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE usercontac3_ eq_ref PRIMARY PRIMARY 150 flo.user0_.user_contact_id 1 100 1 SIMPLE usersocial4_ eq_ref PRIMARY PRIMARY 150 flo.user0_.user_social_info_id 1 100 1 SIMPLE userdetail5_ eq_ref PRIMARY PRIMARY 150 flo.user0_.user_detail_id 1 100 

上面的语句很明显从索引找出符合的条件然后回表在临时表排序 不太明白 mysql 为什么不根据索引排序后的 row_id 回表进行查询，本身索引也是有序的，过滤 20 行回表不就可以了吗 难道回表的随机查询导致分析成本过高，

改写后

SELECT * FROM ( SELECT * FROM USER WHERE USER .update_time > '2021-06-23 09:40:00.019' ORDER BY USER .update_time ASC LIMIT 0, 20 ) user0_ LEFT OUTER JOIN user_statistic userstatis1_ ON user0_.user_statistic_id = userstatis1_.id LEFT OUTER JOIN language_level languagele2_ ON user0_.language_level_id = languagele2_.id LEFT OUTER JOIN user_contact usercontac3_ ON user0_.user_contact_id = usercontac3_.id LEFT OUTER JOIN user_social_info usersocial4_ ON user0_.user_social_info_id = usersocial4_.id LEFT OUTER JOIN user_detail userdetail5_ ON user0_.user_detail_id = userdetail5_.id; 执行计划 1 PRIMARY <derived2> ALL 20 100 1 PRIMARY userstatis1_ eq_ref PRIMARY PRIMARY 150 user0_.user_statistic_id 1 100 1 PRIMARY languagele2_ ALL 7 100 Using where; Using join buffer (Block Nested Loop) 1 PRIMARY usercontac3_ eq_ref PRIMARY PRIMARY 150 user0_.user_contact_id 1 100 1 PRIMARY usersocial4_ eq_ref PRIMARY PRIMARY 150 user0_.user_social_info_id 1 100 1 PRIMARY userdetail5_ eq_ref PRIMARY PRIMARY 150 user0_.user_detail_id 1 100 2 DERIVED user range idx_update_time idx_update_time 6 1143267 100 Using index condition 

执行时间大大缩减了，没有临时表和文件排序。