KnightIT 2020-01-02 17:30 采纳率: 0%
浏览 398

作为后端,优化才是价值所在!已尽最大能力优化,只求进一步!

视图再进一步优化!
最近遇到sql优化瓶颈
这是一个插入艺术家数据到es的sql查询,

SELECT 
a.id,
a.`name`,
a.pinyin,
a.first_letter,
a.gender,
a.follow_num,
a.art_num,
a.audit_status,
a.create_time,
a.user_id,
a.certificate_status,
b.exhibition_id,
c.avatar,
c.birthday,
d.academy,
d.major,
d.edu_degree,
e.my  
FROM artist_artist a 
LEFT JOIN (SELECT t2.artist_id,GROUP_CONCAT(t1.exhibition_id SEPARATOR ' ') as exhibition_id FROM exhibition_artwork t1 LEFT JOIN artwork_artwork t2 on t1.artwork_id=t2.id and t2.removed=FALSE WHERE t1.removed=FALSE and t1.selected=TRUE GROUP BY t2.artist_id) as b on a.id=b.artist_id 
LEFT JOIN account_user c on a.user_id=c.id 
LEFT JOIN (SELECT artist_id,GROUP_CONCAT(academy SEPARATOR ',') as academy,GROUP_CONCAT(major SEPARATOR ',') as major,GROUP_CONCAT(edu_degree SEPARATOR ',') as edu_degree from artist_education WHERE removed=FALSE GROUP BY artist_id)  as d on a.id=d.artist_id 
LEFT JOIN 
(select t3.artist_id,GROUP_CONCAT(CONCAT_WS(',',t3.id,t3.title,t3.category,t3.creation_age,t3.size_l,t3.size_w,t3.size_h,t3.certificate_status,t4.filename) SEPARATOR ';') as my FROM (select s.artist_id,s.id,s.title,s.category,s.creation_age,s.size_l,s.size_w,s.size_h,s.picture_id,s.create_time,s.certificate_status    
from artwork_artwork s 
where (select count(*) from artwork_artwork 
where artist_id = s.artist_id and create_time > s.create_time and removed=FALSE and audit_status=1)<3 and s.removed=FALSE and s.audit_status=1 
order by s.create_time desc)  as t3 LEFT join artwork_picture t4 on t3.picture_id=t4.id  GROUP BY t3.artist_id ) as e on a.id=e.artist_id 
WHERE a.removed=FALSE and a.audit_status=1 and a.switch_status=TRUE

这是explain的结果,优化了已经不知何处优化了
图片说明
因为需要处理的数据太过,所以子查询多,涉及的表也多,左关联的第一张表数据量在七八万,管理以后筛选只要三万,但是取数据的速度太慢了,求各位大神帮看看怎么优化?

  • 写回答

2条回答 默认 最新

  • 关注

    有没有考虑sql拆解后,在内存中关联和处理数据

    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题