mysql优化问题

[jip0303]

wamp环境 mysql 5.7.19

//查询花费 36.2291 秒 这个肿么优化啊？？？才10万条的简单记录就这么久

/**
* 数据表信息
* stu table id, name, sex, grade_id 1万条
* sub table id, name 10条
* grade table id, name 5条
* score table id, stu_id, sub_id, score 10万条
**/

/**
* 各年级总分前三名
**/

sql语句

select c.* from
(
select b.id, b.name, b.grade_id, a.sum from
(select stu_id, sum(score) as sum from score group by stu_id ) as a
inner join
stu as b
on
a.stu_id = b.id
) as c
where (
select count(*) from
(
select a.sum, b.grade_id, b.name, b.id from
(select stu_id, sum(score) as sum from score group by stu_id ) as a
inner join
stu as b
on
a.stu_id = b.id
) as d where d.grade_id = c.grade_id and d.sum > c.sum
) < 3
order by c.grade_id, c.sum desc

[xuzuning]

在 score.stu_id 上建索引

[jip0303]

因为grade_id是确定的，所以可以拎出来改为union all写法，最终查询时间小于1S 在本例中，score.stu_id是否建立索引时间差别不大， 是否增加score.grade_id时间也差别不大。但是navicat的状态数据显示增加该字段效果更好 //如下语句是增加score.grade_id字段 (select c.* from ( select b.id, b.name, b.grade_id, a.sum from (select stu_id, sum(score) as sum from score where grade_id = 1 group by stu_id ) as a inner join stu as b on a.stu_id = b.id ) as c order by c.sum desc limit 3) union all (select c.* from ( select b.id, b.name, b.grade_id, a.sum from (select stu_id, sum(score) as sum from score where grade_id = 2 group by stu_id ) as a inner join stu as b on a.stu_id = b.id ) as c order by c.sum desc limit 3) union all (select c.* from ( select b.id, b.name, b.grade_id, a.sum from (select stu_id, sum(score) as sum from score where grade_id = 3 group by stu_id ) as a inner join stu as b on a.stu_id = b.id ) as c order by c.sum desc limit 3) union all (select c.* from ( select b.id, b.name, b.grade_id, a.sum from (select stu_id, sum(score) as sum from score where grade_id = 4 group by stu_id ) as a inner join stu as b on a.stu_id = b.id ) as c order by c.sum desc limit 3) union all (select c.* from ( select b.id, b.name, b.grade_id, a.sum from (select stu_id, sum(score) as sum from score where grade_id = 5 group by stu_id ) as a inner join stu as b on a.stu_id = b.id ) as c order by c.sum desc limit 3) ;

[qlkj666]

弄个索引，完美解决

[伟洪winni]

explain