关于 mysql 联表执行顺序问题

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

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

8 条回复 • 2021-06-24 17:24:39 +08:00