问题出现
在这个项目中印象比较深刻的是一次改进sql执行效率。系统有一个 页面需要统计学生是否上传了各类文件，主要有两类，一类是毕设文件，这类文件一种每个学生只能上传一份。随着数据量的增多，这个页面的访问越来越慢。
问题确定
然后，我想起来，我在MySQL的配置文件中，配置了慢SQL日志。查看MySQL慢日志，发现进入该页面时执行的SQL，被当作慢日志记录下了。
使用EXPLAIN命令查看了该SQL的执行计划，发现执行计划是先全文搜索了论文信息表，并使用了filesort文件排序、临时文件存储了中间结果，之后再进行学生表、文件上传记录表等查询。对后几个表的查询都是使用索引查询。判断是由于这个查询要根据学生ID进行分组，和排序。而thesis中的学生id不是排序的，所以进行了filesort操作。由于这个SQL所以判定这个SQL访问缓慢是由于这个表的全表扫描、文件排序等造成的。考虑如果先查询学生表，学生表中的id本来就是排序的，就不需要再额外排序了。所以在sql查询中将学生表放在前边，用学生表去join论文表。但结果还是先查了论文表。经过查询，我了解到mysql会对表的连接顺序进行优化，以它认为的高效率进行查询，但这次显然不能这样。于是我使用了stright_join语句强制指定查询顺序解决了这个问题。
首先，通过资料了解到联合查询的效率是优于子查询的，便使用case when语句和count方法将原查询改写了。改写之后发现效率有所上升，但还是挺不算快（1秒多2秒）。因为之前已经在各个表中的学生id字段都加上了索引，感觉速度不至于这么慢。然后我又使用explain命令来查看sql的执行的计划，发现thesis表没有使用索引，并且使用了filesort，
带引号.png
不带引号.png
过程：
将子查询改写为联合查询，使用case when进行数据统计
使用explain查询执行计划：
发现thesis使用了全表查询。考虑将student放在前边，发现执行计划不变，
不按指定顺序执行,thesis全表查询，加Using temporary;Using filesort,分析了一下是因为先查询thesis表的话，需要再对学生id进行排序，所以用到了filesort。如果使sql先查询student表，则可以避免这种情况。但我本来写的是student表join thesis表，不应该先查student吗？经过查找资料得知，Mysql会进行一定程度的sql优化，这个过程可能会改变查询表的顺序，但这次的改变却不是我们想要的。于是我使用stright join语句强制先查询student。果然，没有的temporary和filesort。效率提高了10倍左右。
使用stright join改写，强制执行顺序
点：sql改写、explain命令的使用，查询表的顺序没有按照指定顺序走时的做法
条件是卸载on后边，还是卸载where后边
如果是主表的条件，则写在where后边
如果是被关联表的条件，则写在on后边
资料
straight join的使用经历（重点看高性能MySQL那一段）
MySQL优化的奇技淫巧之STRAIGHT_JOIN（提出了MySQL选择表顺序的依据）
MySQL优化器如何选择索引和JOIN顺序
MySQL查询优化器概述
MySQL查询执行过程
explain命令详解
MySQL查询条件加引号和不加引号的区别
MySQL分析SQL耗时瓶颈
子查询和联合查询的效率讨论
四种join语句
explain命令执行结果分析：
id：
查询序号，从大到小执行，同样大的按从上往下的顺序
select_type：
- SIMPLE：简单SELECT(不使用UNION或子查询等)
- PRIMARY：最外面的SELECT
- UNION：UNION中的第二个或后面的SELECT语句
- DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
- UNION RESULT：UNION的结果。
- SUBQUERY：子查询中的第一个SELECT
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
- DERIVED：导出表的SELECT(FROM子句的子查询)
table
显示这一行的数据是关于哪张表的
type
分析sql瓶颈的关键一列
结果值从好到坏依次是：
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
ALL：全表扫描，性能最差
index：也是全表扫描，只不过只查询索引中的数据，不需要磁盘数据
range：只检索给定范围的行
ref：非唯一性索引
eq_ref：唯一性索引
const:
possible_keys和key
possible显示可能的索引
key显示实际使用的索引
ref
显示索引的的那一列被使用了
因为使用的索引可能有多个列（联合索引），但真正使用的列不一定有几个
rows
根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数，也就是说，用的越少越好
extra
包含不适合在其他列中显式但十分重要的额外信息
1.Using filesort：
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
2.Using temporary
使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3.Using index
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
SELECT
`student`.`id` AS `id`,
`student`.`sname` AS `sname`,
`student`.`sno` AS `sno`,
`student`.`year` AS `year`,
`teacher`.`tname` AS `tname`,
`student`.`email` AS `email`,
`student`.`phone` AS `tel`,
`student`.`department` AS `department`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 2 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `plan_cnt`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 3 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `open_cnt`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 4 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `midterm_cnt`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 41 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `midterm_ppt_cnt`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 6 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `graduation_cnt`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 16 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `graduation_edit_cnt`,
count(
DISTINCT (
CASE `upfile`.`period`
WHEN 61 THEN
`upfile`.`id`
ELSE
NULL
END
)
) AS `graduation_ppt_cnt`,
count(DISTINCT `week_report`.`id`) AS `weekly_report_cnt`
FROM
`student`
STRAIGHT_JOIN `thesis` ON (
`thesis`.`sid` = `student`.`id`
AND `thesis`.`year` = '2018'
)
LEFT JOIN `upfile` ON (
`thesis`.`sid` = `upfile`.`sid` AND upfile.year = 2018
)
LEFT JOIN `week_report` ON (
`thesis`.`sid` = `week_report`.`sid` AND week_report.year = 2018
)
LEFT JOIN `teacher` ON (
`teacher`.`id` = `thesis`.`tid`
)
WHERE
student.department = '电子工程学院' AND student.`year` = 2018
GROUP BY
`student`.`id`
ORDER BY student.id
LIMIT 0,10
网友评论