V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
simonlu9
V2EX  ›  MySQL

关于 mysql 联表执行顺序问题

  •  
  •   simonlu9 · 162 天前 · 724 次点击
    这是一个创建于 162 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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
    
    

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

    8 条回复    2021-06-24 17:24:39 +08:00
    justfindu
        1
    justfindu  
       162 天前
    下面这个就 20 条进行查询 当然效率大大提升
    simonlu9
        2
    simonlu9  
    OP
       162 天前
    @justfindu 上面那条也是 20
    liprais
        3
    liprais  
       162 天前
    这俩语义都不一样...
    justfindu
        4
    justfindu  
       162 天前
    @simonlu9 #2 明显不是 20 呀 0 0
    simonlu9
        5
    simonlu9  
    OP
       162 天前
    @justfindu 我明白你的意思是 left join 出来可能是一对多,所以下面那个 20 和上面那个 20 可能返回结果不一样。
    pabupa
        6
    pabupa  
       162 天前 via Android
    借楼文革另外的问题,我写成 "select * from a,b,c where a.id =b.aid and a.id =c.aid where a.updste_time > '2001-12-30'"这样。它会被优化成上面两种的那一种呀?
    pabupa
        7
    pabupa  
       162 天前 via Android
    @pabupa “问个”,,,我擦
    pabupa
        8
    pabupa  
       162 天前 via Android
    @pabupa 我觉得会是第一种,,,
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2157 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 05:06 · PVG 13:06 · LAX 21:06 · JFK 00:06
    ♥ Do have faith in what you're doing.