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  

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