一道很常見的 MySQL 面試題,你能一眼看出來那個語句執行效率最好嗎？

Ali發表於2020-04-08
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 協議》，轉載必須註明作者和本文連結

高永立

相關文章