求一纠结的sql 优化，在线等

[uu_lt]

先上 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(ROUND(avg(j.is_recommend),0),0) isRecommend, if (ADDDATE(MAX(j.insert_time),365) > NOW(),1,0) isAdd,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 then 1
when ABS(d.question_state) =3 THEN 0.6 ELSE 0 END
)/
sum(case when ABS(d.question_state) in (1,2,3,4) then 1 ELSE 0 END)*100,2),0) gradeRate,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 and g.user_sex = "男" then 1
when ABS(d.question_state) =3 and g.user_sex = "男" THEN 0.6 ELSE 0 END
) /
sum(case when ABS(d.question_state) in (1,2,3,4) and g.user_sex = "男" then 1 ELSE 0 END)*100,2),0) maleRate,
COALESCE(ROUND(sum(case
when ABS(d.question_state) =2 and g.user_sex = "女" then 1
when ABS(d.question_state) =3 and g.user_sex = "女" THEN 0.6 ELSE 0 END
) /
sum(case when ABS(d.question_state) in (1,2,3,4) and g.user_sex = "女" then 1 ELSE 0 END)*100,2),0) femaleRate

FROM t_user_question c
JOIN (
select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus
UNION
SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus
) l on l.question_id = c.question_id
JOIN t_question_user_recommen j on j.question_id = l.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 AND h.user_id = f.user_id
JOIN t_user g on c.user_id = g.user_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.homework_id = 2
and h.state IN (2,3)
GROUP BY c.question_id

[uu_lt]

[uu_lt]

现在 查询时间为 1.8秒 去除 t_question_user_recommen j on j.question_id = l.question_id 后降至 0.44秒

[uu_lt]

[wwwwb]

select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus UNION SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus 生成表，在question_id上建立索引

[uu_lt]

引用 4 楼 wwwwb 的回复:

select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus UNION SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus 生成表，在question_id上建立索引

这个上面是有索引的，为了sql清晰好看 我把它省略了 ，一下是那个派生表： 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

[WWWWA]

explain 5楼的SQL ，哪个是 select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus UNION SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus 生成的表？

[uu_lt]

select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus UNION SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus 这个是5楼产生的结果 ，我偷懒了 没说清楚

[uu_lt]

引用 6 楼 WWWWA 的回复:

explain 5楼的SQL ，哪个是 select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus UNION SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus 生成的表？ ……

select '4fc8b2eb2bdb4bbc859db2656f612070' question_id , 0 erStatus UNION SELECT 'bcaef47ce49c4ba4aa8f067a56b95a39' question_id , 0 erStatus 他是5楼sql产生的结果

[uu_lt]

现在的瓶颈出在 t_question_user_recommen j 这张表上 删除前后 效率差了三倍