SQL调优案例，MYSQL服务器CPU100%问题解决

Mysql服务器经常性CPU占用100%,按照Mysql解决思路，应该是有大的SQL语句在占用CPU时间。

使用如下命令将语句打印到文本

mysql -uroot -ppasswd -e "show full processlist" > c:\test.txt

使用文本编辑工具分析后

得到如下语句：

select ret.*
  from (select tt.sourcetype         as sourcetype,
               tt.pointtype          as pointtype,
               tt.upid               as upid,
               tt.gotdate            as gotdate,
               tt.credit             as credit,
               tt.classid            as classid,
               tt.classname          as classname,
               tt.xxkc               as xxkc,
               tt.xskc               as xskc,
               tt.trainMode          as trainMode,
               tt.place              as place,
               tt.courseid           as courseid,
               tt.coursetype         as coursetype,
               tt.coursename         as coursename,
               tt.rewardPointApplyId as rewardPointApplyId,
               tt.achieveWay         as achieveWay,
               tt.type               as type
          from (select '9' as sourcetype,
                       uk.C_POINTTYPE as pointtype,
                       uk.C_OID as upid,
                       utc.CREATEDATE as gotdate,
                       uk.credit as credit,
                       utc.classid as classid,
                       utc.classname as classname,
                       utc.xxkc as xxkc,
                       utc.xianshangkc as xskc,
                       utc.trainMode as trainMode,
                       utc.place as place,
                       '' as courseid,
                       '' as coursetype,
                       '' as coursename,
                       '' as rewardPointApplyId,
                       '' as achieveWay,
                       '' as type
                  from (select distinct (k.C_CLASSID) as C_CLASSID,
                                        '9' as C_POINTTYPE,
                                        '' as C_OID,
                                        k.C_USERID as C_USERID,
                                        sum(k.c_point) as credit
                          from LES_USERPOINTTASK k
                         where k.C_USERID = '14964'
                           and k.C_CLASSID is not null
                         group by k.C_CLASSID) uk,
                       (select tc.OID         as classid,
                               tc.CREATEDATE  as CREATEDATE,
                               tc.CLASSNAME   as classname,
                               t1.xxkc        as xxkc,
                               t1.xianshangkc as xianshangkc,
                               tc.trainMode   as trainMode,
                               tc.place       as place
                          from LMS_CLASS tc
                          left join (select t0.classid,
                                           group_concat(t0.xianxiakc) as xxkc,
                                           group_concat(t0.xianshangkc) as xianshangkc,
                                           sum(t0.credit) as credit
                                      from (select tcc.CLASS_ID as classid,
                                                   CASE
                                                     WHEN tcc.THEACH_MODE > 0 THEN
                                                      group_concat(tcc.COURSE_NAME)
                                                     ELSE
                                                      ''
                                                   END as xianxiakc,
                                                   CASE
                                                     WHEN tcc.THEACH_MODE > 0 THEN
                                                      ''
                                                     ELSE
                                                      group_concat(tcc.COURSE_NAME)
                                                   END as xianshangkc,
                                                   sum(integration) as credit
                                              from LMS_CLASS_COURSE tcc
                                             group by tcc.CLASS_ID,
                                                      tcc.THEACH_MODE) t0
                                     group by t0.classid) t1
                            on t1.classid = tc.OID) utc
                 where uk.C_CLASSID = utc.classid
                   and uk.credit is not null
                   and uk.C_USERID = '14964'
                union
                select '9' as sourcetype,
                       '' as pointtype,
                       lup.C_OID as upid,
                       lup.CREATEDATE as gotdate,
                       lup.C_POINT as credit,
                       lup.C_CLASSID as classid,
                       lup.C_CLASSNAME as classname,
                       '' as xxkc,
                       '' as xskc,
                       '1' as trainMode,
                       ltc.place as place,
                       '' as courseid,
                       '' as coursetype,
                       '' as coursename,
                       '' as rewardPointApplyId,
                       '' as achieveWay,
                       '' as type
                  from LES_USERPOINTTASK lup, LMS_CLASS ltc
                 where lup.C_TRAINWAY = '外派'
                   and ltc.OID = lup.C_CLASSID
                   and lup.C_USERID = '14964'
                union
                select cs.C_COURSETYPE as sourcetype,
                       up.C_POINTTYPE as pointtype,
                       up.C_OID as upid,
                       up.CREATEDATE as gotdate,
                       cs.C_CREDIT as credit,
                       '' as classid,
                       '' as classname,
                       '' as xxkc,
                       '' as xskc,
                       '' as trainMode,
                       '' as place,
                       cs.C_COURSEID as courseid,
                       cs.C_COURSETYPE as coursetype,
                       cs.C_COURSENAME as coursename,
                       '' as rewardPointApplyId,
                       '' as achieveWay,
                       '' as type
                  from LES_COURSE cs, LES_USERPOINTTASK up
                 where cs.C_COURSEID = up.C_ONLINECOURSEID
                   and up.c_classid is null
                   and up.C_USERID = '14964'
                union
                select '8' as sourcetype,
                       'optional' as pointtype,
                       '' as upid,
                       rp.C_ACHIEVEDATE as gotdate,
                       rp.C_REWARDPOINT as credit,
                       '' as classid,
                       '' as classname,
                       '' as xxkc,
                       '' as xskc,
                       '' as trainMode,
                       '' as place,
                       '' as courseid,
                       '' as coursetype,
                       '' as coursename,
                       rp.C_OID as rewardPointApplyId,
                       rp.C_ACHIEVEWAY as achieveWay,
                       rp.C_TYPE as type
                  from LES_REWARDPOINTAPPLY rp
                 where rp.C_USERID = '14964'
                   and rp.C_RESULT = '1'
                   and rp.C_APPROVESTATUS = '3') tt
         where date_format(tt.gotdate, '%Y') = '2017'
         order by tt.pointtype) ret limit

针对各段执行结果的评估，查询到该段占用时间最长，需要将近8分钟才能有结果：

select tc.OID         as classid,
                               tc.CREATEDATE  as CREATEDATE,
                               tc.CLASSNAME   as classname,
                               t1.xxkc        as xxkc,
                               t1.xianshangkc as xianshangkc,
                               tc.trainMode   as trainMode,
                               tc.place       as place
                          from LMS_CLASS tc
                          left join (select t0.classid,
                                           group_concat(t0.xianxiakc) as xxkc,
                                           group_concat(t0.xianshangkc) as xianshangkc,
                                           sum(t0.credit) as credit
                                      from (select tcc.CLASS_ID as classid,
                                                   CASE
                                                     WHEN tcc.THEACH_MODE > 0 THEN
                                                      group_concat(tcc.COURSE_NAME)
                                                     ELSE
                                                      ''
                                                   END as xianxiakc,
                                                   CASE
                                                     WHEN tcc.THEACH_MODE > 0 THEN
                                                      ''
                                                     ELSE
                                                      group_concat(tcc.COURSE_NAME)
                                                   END as xianshangkc,
                                                   sum(integration) as credit
                                              from LMS_CLASS_COURSE tcc
                                             group by tcc.CLASS_ID,
                                                      tcc.THEACH_MODE) t0
                                     group by t0.classid) t1
                            on t1.classid = tc.OID

其中LMS_CLASS表12800多条记录，LMS_CLASS_COURSE这张表也才9000多条记录，没理由这么慢。在两张表的classid和oid上均存在索引

查看执行计划，并未走索引

改写了SQL结果如下：

SELECT
	classid,
	CREATEDATE,
	classname,
	trainMode,
	place,
	group_concat(t0.xianxiakc) AS xxkc,
	group_concat(t0.xianshangkc) AS xianshangkc,
	sum(t0.credit) AS credit
FROM
	(
		SELECT
			classid,
			CREATEDATE,
			classname,
			trainMode,
			place,
			CASE
		WHEN tcc.THEACH_MODE > 0 THEN
			group_concat(tcc.COURSE_NAME)
		ELSE
			''
		END AS xianxiakc,
		CASE
	WHEN tcc.THEACH_MODE > 0 THEN
		''
	ELSE
		group_concat(tcc.COURSE_NAME)
	END AS xianshangkc,
	sum(integration) AS credit
FROM
	(
		SELECT
			tc.OID AS classid,
			tc.CREATEDATE AS CREATEDATE,
			tc.CLASSNAME AS classname,
			tc.trainMode AS trainMode,
			tc.place AS place,
			t1.THEACH_MODE,
			t1.COURSE_NAME,
			t1.integration
		FROM
			LMS_CLASS tc
		LEFT JOIN LMS_CLASS_COURSE t1 ON t1.CLASS_ID = tc.OID
	) tcc
GROUP BY
	classid,
	CREATEDATE,
	classname,
	trainMode,
	place,
	THEACH_MODE
	) t0
GROUP BY
	classid,
	CREATEDATE,
	classname,
	trainMode,
	place

结果如下，查询结果月0.88s，快了近1000倍