对这条sql的性能损耗有很多不清晰的地方,希望各位多多提醒,帮助!谢谢大家
SELECT
k.resource_id,
k.resource_type,
(k.resource_type%2) resOffset,
z.type_index,
c.question_id resourceId,
k.resource_name resoutceName,
l.erStatus erStatus,
COALESCE(j.is_recommend,0) isRecommend,
if (ADDDATE(MAX(j.insert_time),365) > NOW(),1,0) isAdd
FROM t_user_question c
JOIN (
#问题1:性能与该子查询返回记录数成反比(一条记录100%,两条200% T_T````),这太恐怖了
select
c.question_id ,
ABS(d.question_state) erStatus
from t_class_homework a
JOIN t_user_homework b on a.homework_id = b.homework_id
JOIN t_user_question c on a.homework_id = c.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on f.class_id = a.class_id and f.user_id = c.user_id
WHERE f.class_id = 1001
and f.user_id = 10001
and b.state in (2,3)
and a.homework_id = 2
and b.user_id= f.user_id
GROUP BY c.question_id
) l on l.question_id = c.question_id
JOIN t_user_homework h on c.homework_id = h.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on c.user_id = f.user_id
JOIN t_user g on c.user_id = g.user_id #问题2:下面一句大概影三分之二的性能,对于这个查询只有8条匹配数据 ,为什么影响如此之大
LEFT JOIN t_question_user_recommen j on j.question_id = c.question_id JOIN t_question k on k.question_id = l.question_id
JOIN t_subject_question_type z on z.question_type_id = k.question_type_id and z.subject_id = k.subject_id
WHERE f.class_id = 1001
and c.homework_id = 2
and h.state in (2,3)
AND h.user_id = f.user_id
GROUP BY c.question_id小弟,第一次发帖 提问,有不专业的地方多多包涵
SELECT
k.resource_id,
k.resource_type,
(k.resource_type%2) resOffset,
z.type_index,
c.question_id resourceId,
k.resource_name resoutceName,
l.erStatus erStatus,
COALESCE(j.is_recommend,0) isRecommend,
if (ADDDATE(MAX(j.insert_time),365) > NOW(),1,0) isAdd
FROM t_user_question c
JOIN (
#问题1:性能与该子查询返回记录数成反比(一条记录100%,两条200% T_T````),这太恐怖了
select
c.question_id ,
ABS(d.question_state) erStatus
from t_class_homework a
JOIN t_user_homework b on a.homework_id = b.homework_id
JOIN t_user_question c on a.homework_id = c.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on f.class_id = a.class_id and f.user_id = c.user_id
WHERE f.class_id = 1001
and f.user_id = 10001
and b.state in (2,3)
and a.homework_id = 2
and b.user_id= f.user_id
GROUP BY c.question_id
) l on l.question_id = c.question_id
JOIN t_user_homework h on c.homework_id = h.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on c.user_id = f.user_id
JOIN t_user g on c.user_id = g.user_id #问题2:下面一句大概影三分之二的性能,对于这个查询只有8条匹配数据 ,为什么影响如此之大
LEFT JOIN t_question_user_recommen j on j.question_id = c.question_id JOIN t_question k on k.question_id = l.question_id
JOIN t_subject_question_type z on z.question_type_id = k.question_type_id and z.subject_id = k.subject_id
WHERE f.class_id = 1001
and c.homework_id = 2
and h.state in (2,3)
AND h.user_id = f.user_id
GROUP BY c.question_id小弟,第一次发帖 提问,有不专业的地方多多包涵
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货