一、问题
今天同事反应系统运行变慢了，问题反馈到我们开发看看，按照套路，按步骤开始排查！
二、排查
（1）查看web服务器系统资源是否充足
-
CPU正常
-
内存正常
-
磁盘正常
-
网络正常
小结：好吧，看样web服务器没问题
（2）查看mysql数据库服务器系统资源是否充足
- CPU正常
- 内存稍高（可能有问题）
- 磁盘正常
- 网络正常
（3）查看mysql慢查询
- 查看慢sql，以时间倒序排列
select * from information_schema.`PROCESSLIST` WHERE info is not null ORDER BY time desc;
我们现在看到好多的慢查询语句，我们挑一个sql来定位一下问题，箭头所指sql，不用特别的关注sql本身，因为很长，_
SELECT
t.*, ifnull(q.count, 0) ext5,
ifNUll(t1.count, 0) ext6
FROM
(
SELECT
hh.exampaperNum,
hh.ext1,
hh.ext2,
hh.ext10,
sum(hh.ext3) ext3,
sum(hh.ext4) ext4,
hh.questionName
FROM
(
SELECT
z.exampaperNum,
su.subjectNum ext1,
su.subjectName ext2,
su.orderNum ext10,
CASE
WHEN d.choosename <> 's' THEN
IFNULL(tt.count, 0)
WHEN d.choosename = 's'
AND z.makType = 1 THEN
IFNULL(r.count * 2, 0) + IFNULL(x.count, 0)
WHEN d.choosename = 's'
AND z.makType = 0 THEN
IFNULL(r.count, 0)
END ext3,
IFNULL(y.d_num, 0) ext4,
IF (t.groupNum IS NULL, 'T', 'F') questionName
FROM
(
SELECT
s.groupNum,
s.groupName,
s.groupType,
s.exampaperNum,
m.makType
FROM
questiongroup s
LEFT JOIN questiongroup_mark_setting m ON m.groupNum = s.groupNum
LEFT JOIN exampaper e ON s.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
) z
LEFT JOIN (
SELECT
t.groupNum,
cast(
count(1) / count(DISTINCT(questionNum)) AS signed
) AS d_num
FROM
task t
LEFT JOIN exampaper e ON t.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND t. STATUS = 'T'
GROUP BY
groupNum
) y ON z.groupNum = y.groupNum
LEFT JOIN (
SELECT
groupNum,
cast(
count(1) / count(DISTINCT(questionNum)) AS signed
) AS count
FROM
task t
LEFT JOIN exampaper e ON t.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND t.userNum = 3
GROUP BY
groupNum
) x ON z.groupNum = x.groupNum
LEFT JOIN (
SELECT
id,
choosename
FROM
define d
LEFT JOIN exampaper e ON d.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
UNION
SELECT
sb.id,
d.choosename
FROM
define d
LEFT JOIN exampaper e ON d.exampapernum = e.exampapernum
LEFT JOIN subdefine sb ON sb.pid = d.id
WHERE
e.examNum = 5
AND e.gradeNum = 12
) d ON z.groupNum = d.id
LEFT JOIN (
SELECT
d.id,
r.dd count
FROM
(
SELECT
d.id,
d.examPaperNum,
CASE
WHEN (
e.xuankaoqufen = 2
OR e.xuankaoqufen = 3
) THEN
d.category
ELSE
d.examPaperNum
END category
FROM
define d
LEFT JOIN exampaper ep ON d.exampapernum = ep.exampapernum
INNER JOIN exampaper e ON d.category = e.examPaperNum
WHERE
ep.examNum = 5
AND ep.gradeNum = 12
AND d.questionType = 1
UNION
SELECT
d.id,
d.examPaperNum,
CASE
WHEN (
e.xuankaoqufen = 2
OR e.xuankaoqufen = 3
) THEN
d.category
ELSE
d.examPaperNum
END category
FROM
subdefine d
LEFT JOIN exampaper ep ON d.exampapernum = ep.exampapernum
INNER JOIN exampaper e ON d.category = e.examPaperNum
WHERE
ep.examNum = 5
AND ep.gradeNum = 12
AND d.questionType = 1
) d
LEFT JOIN (
SELECT
count(DISTINCT r.studentId) dd,
r.exampaperNum ext1
FROM
regexaminee r
LEFT JOIN exampaper e ON r.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND r.scan_import = 0
GROUP BY
r.exampaperNum
UNION
SELECT
count(DISTINCT r.studentId) dd,
e1.exampapernum
FROM
regexaminee r
LEFT JOIN exampaper e ON r.exampapernum = e.exampapernum
INNER JOIN student s ON r.studentId = s.id
INNER JOIN subjectcombinedetail sd ON s.subjectCombineNum = sd.subjectCombineNum
INNER JOIN exampaper e1 ON sd.subjectNum = e1.subjectNum
AND r.examPaperNum = e1.pexampaperNum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND r.scan_import = 0
UNION
SELECT
count(DISTINCT r.studentId) dd,
e1.exampaperNum
FROM
regexaminee r
LEFT JOIN exampaper e ON r.exampapernum = e.exampapernum
LEFT JOIN (
SELECT
r.studentId,
r.exampapernum
FROM
regexaminee r
LEFT JOIN exampaper e ON r.exampapernum = e.exampapernum
INNER JOIN student s ON r.studentId = s.id
INNER JOIN subjectcombinedetail sd ON s.subjectCombineNum = sd.subjectCombineNum
INNER JOIN exampaper e1 ON sd.subjectNum = e1.subjectNum
AND r.examPaperNum = e1.pexampaperNum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND r.scan_import = 0
) r1 ON r.studentId = r1.studentId
AND r.exampapernum = r1.exampapernum
INNER JOIN exampaper e1 ON r.examPaperNum = e1.pexamPaperNum
AND e1.xuankaoqufen = 3
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND r.scan_import = 0
AND r1.studentId IS NULL
) r ON d.category = r.ext1
) r ON z.groupNum = r.id
LEFT JOIN (
SELECT
groupNum,
cast(
count(1) / count(DISTINCT(questionNum)) AS signed
) count
FROM
task t
LEFT JOIN exampaper e ON t.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
GROUP BY
groupNum
) tt ON z.groupNum = tt.groupNum
LEFT JOIN (
SELECT DISTINCT
q.groupNum
FROM
(
SELECT
groupNum,
questionNum
FROM
questiongroup_question qq
LEFT JOIN exampaper e ON qq.exampapernum = e.exampapernum
WHERE
e.examNum = 5
AND e.gradeNum = 12
GROUP BY
groupNum
) q
LEFT JOIN (
SELECT DISTINCT
questionNum
FROM
remark r
LEFT JOIN exampaper e ON r.exampapernum = e.exampapernum
WHERE
r.type = '1'
AND r. STATUS = 'F'
AND e.examNum = 5
AND e.gradeNum = 12
) tt ON q.questionNum = tt.questionNum
WHERE
tt.questionNum IS NOT NULL
) t ON z.groupNum = t.groupNum
LEFT JOIN exampaper epp ON z.examPaperNum = epp.examPaperNum
LEFT JOIN SUBJECT su ON epp.subjectnum = su.subjectNum
WHERE
z.groupNum IS NOT NULL
) hh
GROUP BY
hh.examPaperNum
) t
LEFT JOIN (
SELECT
q.examPapernum,
count(1) count
FROM
questiongroup q
LEFT JOIN exampaper e ON q.examPapernum = e.examPaperNum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND q.stat = 0
GROUP BY
q.examPaperNum
) q ON t.examPaperNum = q.exampapernum
LEFT JOIN (
SELECT
t.examPaperNum,
count(1) count
FROM
testingcentredis t
LEFT JOIN exampaper e ON t.examPapernum = e.examPaperNum
INNER JOIN testingcentre tc ON t.testingCentreId = tc.id
AND e.examNum = tc.examNum
WHERE
e.examNum = 5
AND e.gradeNum = 12
AND t.isDis = 0
GROUP BY
t.examPaperNum
) t1 ON t.examPaperNum = t1.exampapernum
ORDER BY
t.ext10 * 1
-
先用 EXPLAIN 看一下执行计划，rows：731525940 这么多行，而且type是all，全表扫描，这个就要命了，问题大概就出在这里了。
-
定位一下是哪个表：
这东西就不好定位了，我们先看看这个derived 是个啥东西
Derived table实际上是一种特殊的subquery，它位于SQL语句中FROM子句里面，可以看做是一个单独的表，其实就是个子查询的结果集，
-
不过这个还是没办法定位是哪个子查询，这个时候怎么办呢？
-
其实 EXPLAIN 是有参数的，我们可以配置详细的信息 EXPLAIN FORMAT=JSON，这样可以查到一个详细JSON执行计划了，来看看
-
看到这个子查询的别名是 t ，我们回头看看sql，找一下位置
三、收工
剩下的就把别名是t的子查询发给同事检查啦。