欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

三张表,联表查询,这个sql还能再优化吗

程序员文章站 2022-06-05 10:20:54
...
SELECT c.username,c.sex,c.attentions,c.fans,a.id,a.content,a.isnum,a.times,a.turm,a.keep,a.comments,a.uid,b.mini,b.mednum,b.maxs
FROM (wb_userinfo c LEFT JOIN wb_weibo a ON c.uid = a.uid)
LEFT JOIN wb_picture b ON a.id = b.wid WHERE a.uid IN(SELECT follow FROM wb_follow WHERE fans = 27) OR a.uid = 27 ORDER BY a.times LIMIT 0,10;

回复内容:

SELECT c.username,c.sex,c.attentions,c.fans,a.id,a.content,a.isnum,a.times,a.turm,a.keep,a.comments,a.uid,b.mini,b.mednum,b.maxs
FROM (wb_userinfo c LEFT JOIN wb_weibo a ON c.uid = a.uid)
LEFT JOIN wb_picture b ON a.id = b.wid WHERE a.uid IN(SELECT follow FROM wb_follow WHERE fans = 27) OR a.uid = 27 ORDER BY a.times LIMIT 0,10;

有用or的可以考虑换成用union来尝试下

(SELECT c.username,c.sex,c.attentions,c.fans,a.id,a.content,a.isnum,a.times,a.turm,a.keep,a.comments,a.uid,b.mini,b.mednum,b.maxs
FROM (wb_userinfo c LEFT JOIN wb_weibo a ON c.uid = a.uid)
LEFT JOIN wb_picture b ON a.id = b.wid WHERE a.uid IN(SELECT follow FROM wb_follow WHERE fans = 27) )
union
(SELECT c.username,c.sex,c.attentions,c.fans,a.id,a.content,a.isnum,a.times,a.turm,a.keep,a.comments,a.uid,b.mini,b.mednum,b.maxs
FROM (wb_userinfo c LEFT JOIN wb_weibo a ON c.uid = a.uid)
LEFT JOIN wb_picture b ON a.id = b.wid WHERE a.uid = 27 )
ORDER BY a.times LIMIT 0,10;

使用explain看看分析报告贴出来看下,ref的值与row是的值索引使用情况等,连表查询的话我正常建议是拆成简单查询来。

就sql而言,可优化的空间不大,建议拆成若干条简单的sql,在代码里实现最终业务逻辑。

PS:尽量避免在sql里出现or

不要用join