一道很常见的 MySQL 面试题,你能一眼看出来那个语句执行效率最好吗?

有两张表 user 用户表 1000万 数据和 thread 帖子表 5000万 数据,写一条 SQL 语句查询前十名最多发帖的用户名和帖子数。

user

uid username password create_at
主键 testnamne testpass time

thread

tid uid title contents create_at
主键 普通索引 title content time

SQL1

SELECT username,count(*) tc FROM `user` LEFT JOIN thread ON `user`.uid = thread.uid GROUP BY  thread.uid ORDER BY  tc DESC limit 0,10 ; 

SQL2 :

SELECT username,tc FROM `user` INNER JOIN  (SELECT uid,count(*) tc FROM thread GROUP BY uid ORDER BY tc DESC limit 0,10 ) AS td ON `user`.uid = td.uid ; 

SQL3 :

SELECT username, tc FROM `user` LEFT JOIN  (SELECT uid, count(*) tc FROM thread GROUP BY uid ORDER BY tc DESC limit 0,10 ) AS td ON `user`.uid = td.uid limit 0,10 ; 
本作品采用《CC 协议》,转载必须注明作者和本文链接

高永立

讨论数量: 1

明显第二条吧。先查帖子,再对一下用户表的id,比对也就10条啊。

1个月前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!