关于 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

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