上篇;https://blog.csdn.net/qq_44691484/article/details/106678831
背景:这周接了一个另一个项目的列表展示问题,测试环境对于正确性验证完毕后,投产,发现生产卡死,一万八千条数据,前台卡死,后台发现是一个sql 122秒,还原版本进行优化
原始sql :
SELECT
aaa.*
FROM
(
(
SELECT
'确认' C_CHECK_STATUS_desc,
'0' BASEFLAG,
tpc.C_ORGCODE,
tpc.C_PARTNER_ID,
tpc.C_PROVNAME,
tpc.C_PRODGRADE_CHECK_ID,
tpc.C_PRODTRAIN_ID,
tpc.C_STAFF_NUM,
tpc.C_USER_NAME,
tpc.N_POINT,
tpc.T_EXAM_DATE,
tpc.C_CHECK_STATUS,
tpc.C_CHECK_ADVICE,
tpc.T_VERIFY_DATE,
tpc.C_CONFIRM_STATUS,
tpc.C_CONFIRM_USER,
tpc.T_CONFIRM_DATE,
tpc.C_TYPE,
tpc.C_CRT_USER,
tpc.T_CRT_DATE,
IFNULL(
tpt.C_PRODUCT_CODE,
tpc.C_PRODUCT_CODE
) AS C_PRODUCT_CODE,
IFNULL(
tpt.C_PRODUCT_NAME,
tpc.C_PRODUCT_NAME
) AS C_PRODUCT_NAME,
IFNULL(
tpt.C_PRODTRAIN_NAME,
tpc.C_PRODUCT_NAME
) AS C_PRODTRAIN_NAME,
(
SELECT
t.C_CODE_NAME
FROM
t_code t
WHERE
t.C_CODETYPE_CODE = 'C_PRODTRAIN_FLAG'
AND t.C_CODE_CODE = tpc.C_PRODTRAIN_FLAG
) AS C_PRODTRAIN_FLAG,
(
SELECT
t.C_CODE_NAME
FROM
t_code t
WHERE
t.C_CODETYPE_CODE = 'C_CONFIRM_STATUS'
AND t.C_CODE_CODE = tpc.C_CONFIRM_STATUS
) C_CONFIRM_STATUS_desc,
DATE_FORMAT(
tpc.T_EXAM_DATE,
'%Y-%m-%d %T'
) T_EXAM_DATE_desc
FROM
T_PRODGRADE_CHECK tpc
LEFT JOIN T_PRODUCT_TRAIN tpt ON tpc.C_PRODTRAIN_ID = tpt.C_PRODTRAIN_ID
)
UNION ALL
(
SELECT
'确认' C_CHECK_STATUS_desc,
'1' BASEFLAG,
(select SUBSTRING(usr.INST_ID,5) from iips.u_base_user usr WHERE usr.USER_ID = b.C_USER_ID LIMIT 0,1) C_ORGCODE,
b.C_PARTNER_ID,
CASE
WHEN org.class = '03' THEN
(
SELECT org1.orgname FROM iips.tborg org1
LEFT JOIN iips.tborg org2 ON org1.orgcode = org2.superorgcode AND org2.systemstatus = '1'
WHERE org.superorgcode = org2.orgcode AND org1.systemstatus = '1' LIMIT 0,1
)
WHEN org.class = '02' THEN
(
SELECT org1.orgname FROM iips.tborg org1
WHERE org.superorgcode = org1.orgcode AND org1.systemstatus = '1' LIMIT 0,1
)
WHEN org.class = '01' THEN org.orgname
ELSE '' end as C_PROVNAME,
b.C_TRAIN_STU_SCHEDULE_ID C_PRODGRADE_CHECK_ID,
'' C_PRODTRAIN_ID,
b.C_USER_ID C_STAFF_NUM,
(select usr.USER_CNAME from iips.u_base_user usr WHERE usr.USER_ID = b.C_USER_ID LIMIT 0,1) C_USER_NAME,
IFNULL(truncate(N.N_SCORE_POINT,2),'未考') AS N_POINT,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) T_EXAM_DATE_desc,
'1' C_CHECK_STATUS,
'' C_CHECK_ADVICE,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) AS T_VERIFY_DATE,
case when (select bbb.C_CONFIRM_STATUS from T_PRODGRADE_CHECK bbb where
bbb.C_STAFF_NUM = b.C_USER_ID and bbb.C_PRODTRAIN_FLAG = '2' limit 0,1
) is null then '0'
else (select bbb.C_CONFIRM_STATUS from T_PRODGRADE_CHECK bbb where
bbb.C_STAFF_NUM = b.C_USER_ID and bbb.C_PRODTRAIN_FLAG = '2' limit 0,1
)
end as C_CONFIRM_STATUS,
'' C_CONFIRM_USER,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) AS T_CONFIRM_DATE,
'' C_TYPE,
'' C_CRT_USER,
'' T_CRT_DATE,
'3' C_PRODUCT_CODE,
b.C_COURSE_NAME C_PRODUCT_NAME,
b.C_COURSE_NAME C_PRODTRAIN_NAME,
'基础培训' C_PRODTRAIN_FLAG,
case when (select bbb.C_CONFIRM_STATUS from T_PRODGRADE_CHECK bbb where
bbb.C_STAFF_NUM = b.C_USER_ID and bbb.C_PRODTRAIN_FLAG = '2' limit 0,1
) is null then '待确认'
else (
SELECT
t.C_CODE_NAME
FROM
t_code t
WHERE
t.C_CODETYPE_CODE = 'C_CONFIRM_STATUS'
AND t.C_CODE_CODE = (select bbb.C_CONFIRM_STATUS from T_PRODGRADE_CHECK bbb where
bbb.C_STAFF_NUM = b.C_USER_ID and bbb.C_PRODTRAIN_FLAG = '2' limit 0,1
)
)
end as C_CONFIRM_STATUS_desc,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) AS T_EXAM_DATE
FROM
t_train_stu_schedule b
LEFT JOIN iips.tborg org ON
org.orgincode = (select usr.INST_ID from iips.u_base_user usr WHERE usr.USER_ID = b.C_USER_ID LIMIT 0,1)
LEFT JOIN (
SELECT
tcl.C_CLASSCOURSE_ID,
E.C_CLASS_COURSE_ID,
E.C_USER_ID AS C_USER_ID,
E.N_SCORE_POINT,
1 AS C_REC_STATUS,
3 AS C_DATA_SOURCE,
E.T_SCORE_TIME,
E.C_TEST_ID
FROM
(SELECT
S.C_CLASS_COURSE_ID,
S.N_SCORE_POINT,
p.C_USER_ID,p.C_PARTNER_ID,
MAX(S.T_SCORE_TIME) AS T_SCORE_TIME
FROM
(SELECT
C_CLASS_COURSE_ID,
C_USER_ID,C_PARTNER_ID,
MAX(N_SCORE_POINT) AS N_SCORE_POINT
FROM
T_SCORE_SUMMARY
WHERE C_REC_STATUS = '1'
AND C_DATA_SOURCE = '3'
GROUP BY C_USER_ID,C_CLASS_COURSE_ID
) P
LEFT JOIN T_SCORE_SUMMARY S
ON P.C_CLASS_COURSE_ID = S.C_CLASS_COURSE_ID
AND S.C_USER_ID = p.C_USER_ID
AND S.C_PARTNER_ID = p.C_PARTNER_ID
AND P.N_SCORE_POINT = S.N_SCORE_POINT
AND S.C_REC_STATUS = '1'
AND S.C_DATA_SOURCE = '3'
GROUP BY S.C_CLASS_COURSE_ID,
S.N_SCORE_POINT) B
LEFT JOIN T_SCORE_SUMMARY E
ON B.C_CLASS_COURSE_ID = E.C_CLASS_COURSE_ID
AND B.N_SCORE_POINT = E.N_SCORE_POINT
AND B.T_SCORE_TIME = E.T_SCORE_TIME
AND E.C_USER_ID = B.C_USER_ID
AND E.C_PARTNER_ID = B.C_PARTNER_ID
AND E.C_REC_STATUS = '1'
AND E.C_DATA_SOURCE = '3'
left join t_classcourse_exam_rel tcl on
B.C_CLASS_COURSE_ID = tcl.C_CLASSIFY_ID
group by E.C_USER_ID,tcl.C_CLASSCOURSE_ID
) N ON N.C_USER_ID = b.C_USER_ID
AND N.C_CLASSCOURSE_ID = b.C_CLASS_COURSE_ID
WHERE
b.C_COURSESORT_TYPE = '2'
)
) aaa
WHERE 1=1
and aaa.C_PARTNER_ID='1106'
-- AND aaa.C_STAFF_NUM LIKE concat ("%",'110000138',"%")
-- limit 0,10
注:因为刚接手,不知道具体逻辑,就将App中的查询sql逻辑不变,放在了网页列表中,导致的卡死
改造过程:由于上班文章的经验,
1.我先依次删除左连接,查看速度,发现N这个临时表取出后,效率会降低,我想的还是如果这一个左连接导致的时间长,是否可以更换基础表、或者从业务表来进行取值。
2.之后,对于属性值的取值来源进行更改,对于一些嵌套查询,进行优化
这次改造,时间依次从122秒,到90秒,72秒,50秒,但是降不下去了
sql改成了:
-- EXPLAIN
SELECT
aaa.*
FROM
(
(
SELECT
'确认' C_CHECK_STATUS_desc,
'0' BASEFLAG,
tpc.C_ORGCODE,
tpc.C_PARTNER_ID,
tpc.C_PROVNAME,
tpc.C_PRODGRADE_CHECK_ID,
tpc.C_PRODTRAIN_ID,
tpc.C_STAFF_NUM,
tpc.C_USER_NAME,
tpc.N_POINT,
tpc.T_EXAM_DATE,
tpc.C_CHECK_STATUS,
tpc.C_CHECK_ADVICE,
tpc.T_VERIFY_DATE,
tpc.C_CONFIRM_STATUS,
tpc.C_CONFIRM_USER,
tpc.T_CONFIRM_DATE,
tpc.C_TYPE,
tpc.C_CRT_USER,
tpc.T_CRT_DATE,
IFNULL(
tpt.C_PRODUCT_CODE,
tpc.C_PRODUCT_CODE
) AS C_PRODUCT_CODE,
IFNULL(
tpt.C_PRODUCT_NAME,
tpc.C_PRODUCT_NAME
) AS C_PRODUCT_NAME,
IFNULL(
tpt.C_PRODTRAIN_NAME,
tpc.C_PRODUCT_NAME
) AS C_PRODTRAIN_NAME,
(
SELECT
t.C_CODE_NAME
FROM
t_code t
WHERE
t.C_CODETYPE_CODE = 'C_PRODTRAIN_FLAG'
AND t.C_CODE_CODE = tpc.C_PRODTRAIN_FLAG
) AS C_PRODTRAIN_FLAG,
(
SELECT
t.C_CODE_NAME
FROM
t_code t
WHERE
t.C_CODETYPE_CODE = 'C_CONFIRM_STATUS'
AND t.C_CODE_CODE = tpc.C_CONFIRM_STATUS
) C_CONFIRM_STATUS_desc,
DATE_FORMAT(
tpc.T_EXAM_DATE,
'%Y-%m-%d %T'
) T_EXAM_DATE_desc
FROM
T_PRODGRADE_CHECK tpc
LEFT JOIN T_PRODUCT_TRAIN tpt ON tpc.C_PRODTRAIN_ID = tpt.C_PRODTRAIN_ID
where
1=1
and tpc.C_PARTNER_ID='1106'
-- AND tpc.C_STAFF_NUM LIKE concat ("%",#{C_STAFF_NUM},"%")
)
UNION ALL
(
SELECT
'确认' C_CHECK_STATUS_desc,
'1' BASEFLAG,
SUBSTRING(usr.INST_ID,5) C_ORGCODE,
b.C_PARTNER_ID,
CASE
WHEN org.class = '03' THEN
(
SELECT org1.orgname FROM iips.tborg org1
LEFT JOIN iips.tborg org2 ON org1.orgcode = org2.superorgcode AND org2.systemstatus = '1'
WHERE org.superorgcode = org2.orgcode AND org1.systemstatus = '1' LIMIT 0,1
)
WHEN org.class = '02' THEN
(
SELECT org1.orgname FROM iips.tborg org1
WHERE org.superorgcode = org1.orgcode AND org1.systemstatus = '1' LIMIT 0,1
)
WHEN org.class = '01' THEN org.orgname
ELSE '' end as C_PROVNAME,
b.C_TRAIN_STU_SCHEDULE_ID C_PRODGRADE_CHECK_ID,
'' C_PRODTRAIN_ID,
b.C_USER_ID C_STAFF_NUM,
usr.USER_ENAME C_USER_NAME,
IFNULL(truncate(N.N_SCORE_POINT,2),'未考') AS N_POINT,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) T_EXAM_DATE_desc,
'1' C_CHECK_STATUS,
'' C_CHECK_ADVICE,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) AS T_VERIFY_DATE,
coalesce((select IFNULL(bbb.C_CONFIRM_STATUS,0) from T_PRODGRADE_CHECK bbb where
bbb.C_STAFF_NUM = b.C_USER_ID and bbb.C_PRODTRAIN_FLAG = '2' limit 0,1
),0) C_CONFIRM_STATUS,
'' C_CONFIRM_USER,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) AS T_CONFIRM_DATE,
'' C_TYPE,
'' C_CRT_USER,
'' T_CRT_DATE,
'3' C_PRODUCT_CODE,
b.C_COURSE_NAME C_PRODUCT_NAME,
b.C_COURSE_NAME C_PRODTRAIN_NAME,
'基础培训' C_PRODTRAIN_FLAG,
(
SELECT
t.C_CODE_NAME
FROM
t_code t
WHERE
t.C_CODETYPE_CODE = 'C_CONFIRM_STATUS'
AND t.C_CODE_CODE = coalesce((select bbb.C_CONFIRM_STATUS from T_PRODGRADE_CHECK bbb where
bbb.C_STAFF_NUM = b.C_USER_ID and bbb.C_PRODTRAIN_FLAG = '2' limit 0,1
),0)
) C_CONFIRM_STATUS_desc,
IFNULL(
DATE_FORMAT(
N.T_SCORE_TIME,
'%Y-%m-%d %H:%i:%s'
),
'未考'
) AS T_EXAM_DATE
FROM
t_train_stu_schedule b
left join iips.u_base_user usr on usr.USER_ID = b.C_USER_ID
LEFT JOIN iips.tborg org ON
org.orgincode = usr.INST_ID
LEFT JOIN (
SELECT
tcl.C_CLASSCOURSE_ID,
B.C_CLASS_COURSE_ID,
MAX(IFNULL(B.N_SCORE_POINT,0)) N_SCORE_POINT,
B.C_USER_ID,B.C_PARTNER_ID,
B.T_SCORE_TIME AS T_SCORE_TIME
FROM
T_SCORE_SUMMARY B
left join t_classcourse_exam_rel tcl on
B.C_CLASS_COURSE_ID = tcl.C_CLASSIFY_ID
WHERE B.C_REC_STATUS = '1'
AND B.C_DATA_SOURCE = '3'
group by B.C_USER_ID,tcl.C_CLASSCOURSE_ID
) N ON N.C_USER_ID = b.C_USER_ID
AND N.C_CLASSCOURSE_ID = b.C_CLASS_COURSE_ID and b.C_COURSESORT_TYPE = '2'
WHERE
b.C_COURSESORT_TYPE = '2'
and usr.USER_ID >= 0
and N.N_SCORE_POINT >= 0
and b.C_PARTNER_ID='1106'
-- AND b.C_USER_ID LIKE concat ("%",#{C_STAFF_NUM},"%")
)
) aaa
WHERE 1=1
-- and aaa.C_STAFF_NUM = '520000005'
使用了EXPLAIN关键词,查看sql具体的执行影像参数,:这一参照这篇文章
索引优化前:
优化后:
具体就看id,type,key,row来进行相应索引优化,
我加的也是加普通索引,sql中的连接字段
希望对你们有所帮助