背景:
前天的 SQL 语句任务完成后,要求对效率进行优化;
任务:
需要用到的表:
最后一张单体最高分表帮助文档中没有:
思路:
这里是比较复杂的多表连接查询,需要查询的数据分别在几张不同的表中,结合内外连接进行数据查询:
首次提交版本:
SELECT stu.student_number, stu.student_name, IFNULL(SUM(score.topic_maxscore), 0)SUM, sets.attopicset_starttime
FROM (SELECT DISTINCT bind.student_id
FROM usergroup_authority aut
INNER JOIN usergroup_bind bind ON aut.topicset_id = 3
AND bind.usergroup_id = aut.usergroup_id) filter
INNER JOIN student_info stu ON stu.student_id = filter.student_id
INNER JOIN stu_topicset_sets sets ON sets.attopicset_starttime IS NOT NULL AND sets.topicset_id = 3 AND sets.account_id = stu.account_id
LEFT JOIN topicset_topic_max score ON score.account_id = stu.account_id AND score.topicset_id = sets.topicset_id
GROUP BY filter.student_id, sets.attopicset_starttime
ORDER BY stu.student_number;
考虑到同一个学生绑定多个用户组的情况下查出完全一样的数据,因此直接在连接之前就用子查询进行去重;
存在问题:
在这条 SQL 语句当中连接的表多达 5 张,在最后进行分组时需要同时操作五张表的数据,当数据规模很大的时候效率非常低;
优化思路:
1、对于不需要用到的字段不需要进行连接,用子查询过滤出需要用到的字段,减少连接后成表的臃肿,以此减少分组时数据的操作量;
2、看看是否能将分组直接插入到被连接的参照表中去,先分组,再连接,对于复杂的多表查询可以大大优化运行效率;
优化后版本:
SELECT stu.student_number, stu.student_name, score.score_max, sets.attopicset_starttime
FROM (SELECT DISTINCT bind.student_id
FROM usergroup_authority aut
INNER JOIN usergroup_bind bind ON aut.topicset_id = 3
AND bind.usergroup_id = aut.usergroup_id) filter
INNER JOIN (SELECT account_id, student_id, student_number, student_name FROM student_info) stu ON stu.student_id = filter.student_id
INNER JOIN (SELECT account_id, attopicset_starttime FROM stu_topicset_sets WHERE topicset_id = 3 AND attopicset_starttime IS NOT NULL) sets ON sets.account_id = stu.account_id
LEFT JOIN (SELECT account_id, IFNULL(SUM(topic_maxscore), 0) score_max FROM topicset_topic_max WHERE topicset_id = 3 GROUP BY account_id) score ON score.account_id = sets.account_id
ORDER BY stu.student_number;
优化后的版本大体上并没有太大变化,只是在每个连接表后加了一个子查询进行过滤,只连接需要用的字段,最后将分组插入到了连接表中,但这样确实大大减少了对数据的操作量,经测试优化前是 0.008 秒,优化后是 0.002 秒;
总结:
这里学习到的主要不是这种操作,而是怎样去减少数据操作量的思想,运行效率不会无故变慢,一定是因为查询过程中操作的数据量过大,只要能减少查询过程中对数据量的操作那都是优化;