请教那个大神帮我看看这个SQL语句有什么问题,我一运行我的CPU就占到50以上,
select t.domainid,t.id,t.formid,t1.ITEM_BRANDCODE,t6.ITEM_NAME item_BrandName,t1.ITEM_PAYSTATE,t4.ITEM_NAME item_PayStateName,t1.ITEM_STATECODE,t3.ITEM_NAME item_SourceStateName,t1.ITEM_VALIDITY,t5.ITEM_NAME item_ValidityName,t.item_Name,t.item_Sex,t.item_leafletAddress,t.item_SourceType,t.item_Address,t.item_Creator,t.item_TMK,t.ITEM_CHANNELTYPECODE,t.item_pdtime,t.ITEM_CHANNELTYPENAME,t.ITEM_CHANNELTYPENAME1,t.item_Relation,t7.ITEM_BELONGSCHOOL ,t7.ITEM_BELONGSCHOOLNAME,t.item_ParentsName,t.item_EnglishName,t.item_CreateTime,track_count.item_tc,t8.ITEM_LXRQ,t8.ITEM_OFFER,t10.ITEM_NAME item_InterstName,t.item_Contract ITEM_ZXJY0,t9.ITEM_NAME item_offerName,case when t7.ITEM_STUDENT is null then '未分配' else '已分配' end ITEM_ALLOTSTATEa,t7.ITEM_ALLOTTIME,t.item_AtSchool,t11.ITEM_NAME ITEM_IDEAAGREEName,t8.ITEM_GXD,t8.ITEM_KJD,t8.item_zxjy,t.item_BirthDates,t.item_Age,t.item_Code,t.item_Phone1,t.item_AdmissionsRepresentatives,t7.ITEM_OFPERSON,t2.`NAME` ITEM_OfPersonName from tlk_sourcemanage t left join tlk_sourcestate t1 on t1.ITEM_SOURCECODE=t.ITEM_CODE and t1.ITEM_BRANDCODE='Bra0001' left join tlk_track_consultant t8 on t8.ITEM_CODE=t.ITEM_CODE and t8.item_Brand='Bra0001' and t8.ITEM_LXRQ=(select max(t81.ITEM_LXRQ) from tlk_track_consultant t81 where t81.item_Brand='Bra0001' and t81.item_code=t.item_code) left join (select ttrack.item_code,ttrack.item_brand,count(*) item_tc from tlk_track_consultant ttrack group by ttrack.item_code,ttrack.item_brand ) track_count on track_count.item_code=t.ITEM_CODE and track_count.item_Brand='Bra0001' left JOIN tlk_bd_offer t9 on t9.ITEM_CODE=t8.ITEM_OFFER left join tlk_bd_intereststate t10 on t10.ITEM_CODE=t8.ITEM_INTERSTSTATE left join tlk_bd_sourcestate t3 on t3.ITEM_CODE=t1.ITEM_STATECODE LEFT JOIN tlk_bd_chargestate t4 on t4.ITEM_CODE=t1.ITEM_PAYSTATE left join tlk_bd_trackstate t5 on t5.ITEM_CODE=t1.ITEM_VALIDITY left join tlk_sourceallot_sublist t7 on t7.ITEM_STUDENT=t.ITEM_CODE and t7.item_brand='Bra0001' and t7.ITEM_STATE='Val0001' LEFT JOIN yh_obpm.t_user t2 on t2.ID=t7.ITEM_OFPERSON LEFT JOIN tlk_bd_ideaagree t11 on t11.ITEM_CODE=t8.ITEM_IDEAAGREE left join tlk_bd_brand t6 on t6.ITEM_CODE='Bra0001' where 1=1 order by t.ITEM_CREATETIME DESC
select t.domainid,t.id,t.formid,t1.ITEM_BRANDCODE,t6.ITEM_NAME item_BrandName,t1.ITEM_PAYSTATE,t4.ITEM_NAME item_PayStateName,t1.ITEM_STATECODE,t3.ITEM_NAME item_SourceStateName,t1.ITEM_VALIDITY,t5.ITEM_NAME item_ValidityName,t.item_Name,t.item_Sex,t.item_leafletAddress,t.item_SourceType,t.item_Address,t.item_Creator,t.item_TMK,t.ITEM_CHANNELTYPECODE,t.item_pdtime,t.ITEM_CHANNELTYPENAME,t.ITEM_CHANNELTYPENAME1,t.item_Relation,t7.ITEM_BELONGSCHOOL ,t7.ITEM_BELONGSCHOOLNAME,t.item_ParentsName,t.item_EnglishName,t.item_CreateTime,track_count.item_tc,t8.ITEM_LXRQ,t8.ITEM_OFFER,t10.ITEM_NAME item_InterstName,t.item_Contract ITEM_ZXJY0,t9.ITEM_NAME item_offerName,case when t7.ITEM_STUDENT is null then '未分配' else '已分配' end ITEM_ALLOTSTATEa,t7.ITEM_ALLOTTIME,t.item_AtSchool,t11.ITEM_NAME ITEM_IDEAAGREEName,t8.ITEM_GXD,t8.ITEM_KJD,t8.item_zxjy,t.item_BirthDates,t.item_Age,t.item_Code,t.item_Phone1,t.item_AdmissionsRepresentatives,t7.ITEM_OFPERSON,t2.`NAME` ITEM_OfPersonName from tlk_sourcemanage t left join tlk_sourcestate t1 on t1.ITEM_SOURCECODE=t.ITEM_CODE and t1.ITEM_BRANDCODE='Bra0001' left join tlk_track_consultant t8 on t8.ITEM_CODE=t.ITEM_CODE and t8.item_Brand='Bra0001' and t8.ITEM_LXRQ=(select max(t81.ITEM_LXRQ) from tlk_track_consultant t81 where t81.item_Brand='Bra0001' and t81.item_code=t.item_code) left join (select ttrack.item_code,ttrack.item_brand,count(*) item_tc from tlk_track_consultant ttrack group by ttrack.item_code,ttrack.item_brand ) track_count on track_count.item_code=t.ITEM_CODE and track_count.item_Brand='Bra0001' left JOIN tlk_bd_offer t9 on t9.ITEM_CODE=t8.ITEM_OFFER left join tlk_bd_intereststate t10 on t10.ITEM_CODE=t8.ITEM_INTERSTSTATE left join tlk_bd_sourcestate t3 on t3.ITEM_CODE=t1.ITEM_STATECODE LEFT JOIN tlk_bd_chargestate t4 on t4.ITEM_CODE=t1.ITEM_PAYSTATE left join tlk_bd_trackstate t5 on t5.ITEM_CODE=t1.ITEM_VALIDITY left join tlk_sourceallot_sublist t7 on t7.ITEM_STUDENT=t.ITEM_CODE and t7.item_brand='Bra0001' and t7.ITEM_STATE='Val0001' LEFT JOIN yh_obpm.t_user t2 on t2.ID=t7.ITEM_OFPERSON LEFT JOIN tlk_bd_ideaagree t11 on t11.ITEM_CODE=t8.ITEM_IDEAAGREE left join tlk_bd_brand t6 on t6.ITEM_CODE='Bra0001' where 1=1 order by t.ITEM_CREATETIME DESC
select t.domainid,t.id,t.formid,t1.ITEM_BRANDCODE,t6.ITEM_NAME item_BrandName,t1.ITEM_PAYSTATE,t4.ITEM_NAME item_PayStateName,t1.ITEM_STATECODE,t3.ITEM_NAME item_SourceStateName,t1.ITEM_VALIDITY,t5.ITEM_NAME item_ValidityName,t.item_Name,t.item_Sex,t.item_leafletAddress,t.item_SourceType,t.item_Address,t.item_Creator,t.item_TMK,t.ITEM_CHANNELTYPECODE,t.item_pdtime,t.ITEM_CHANNELTYPENAME,t.ITEM_CHANNELTYPENAME1,t.item_Relation,t7.ITEM_BELONGSCHOOL ,t7.ITEM_BELONGSCHOOLNAME,t.item_ParentsName,t.item_EnglishName,t.item_CreateTime,track_count.item_tc,t8.ITEM_LXRQ,t8.ITEM_OFFER,t10.ITEM_NAME item_InterstName,t.item_Contract ITEM_ZXJY0,t9.ITEM_NAME item_offerName,case when t7.ITEM_STUDENT is null then '未分配' else '已分配' end ITEM_ALLOTSTATEa,t7.ITEM_ALLOTTIME,t.item_AtSchool,t11.ITEM_NAME ITEM_IDEAAGREEName,t8.ITEM_GXD,t8.ITEM_KJD,t8.item_zxjy,t.item_BirthDates,t.item_Age,t.item_Code,t.item_Phone1,t.item_AdmissionsRepresentatives,t7.ITEM_OFPERSON,t2.`NAME` ITEM_OfPersonName from tlk_sourcemanage t left join tlk_sourcestate t1 on t1.ITEM_SOURCECODE=t.ITEM_CODE and t1.ITEM_BRANDCODE='Bra0001' left join tlk_track_consultant t8 on t8.ITEM_CODE=t.ITEM_CODE and t8.item_Brand='Bra0001' and t8.ITEM_LXRQ=(select max(t81.ITEM_LXRQ) from tlk_track_consultant t81 where t81.item_Brand='Bra0001' and t81.item_code=t.item_code) left join (select ttrack.item_code,ttrack.item_brand,count(*) item_tc from tlk_track_consultant ttrack group by ttrack.item_code,ttrack.item_brand ) track_count on track_count.item_code=t.ITEM_CODE and track_count.item_Brand='Bra0001' left JOIN tlk_bd_offer t9 on t9.ITEM_CODE=t8.ITEM_OFFER left join tlk_bd_intereststate t10 on t10.ITEM_CODE=t8.ITEM_INTERSTSTATE left join tlk_bd_sourcestate t3 on t3.ITEM_CODE=t1.ITEM_STATECODE LEFT JOIN tlk_bd_chargestate t4 on t4.ITEM_CODE=t1.ITEM_PAYSTATE left join tlk_bd_trackstate t5 on t5.ITEM_CODE=t1.ITEM_VALIDITY left join tlk_sourceallot_sublist t7 on t7.ITEM_STUDENT=t.ITEM_CODE and t7.item_brand='Bra0001' and t7.ITEM_STATE='Val0001' LEFT JOIN yh_obpm.t_user t2 on t2.ID=t7.ITEM_OFPERSON LEFT JOIN tlk_bd_ideaagree t11 on t11.ITEM_CODE=t8.ITEM_IDEAAGREE left join tlk_bd_brand t6 on t6.ITEM_CODE='Bra0001' where 1=1 order by t.ITEM_CREATETIME DESC
select t.domainid,t.id,t.formid,t1.ITEM_BRANDCODE,t6.ITEM_NAME item_BrandName,t1.ITEM_PAYSTATE,t4.ITEM_NAME item_PayStateName,t1.ITEM_STATECODE,t3.ITEM_NAME item_SourceStateName,t1.ITEM_VALIDITY,t5.ITEM_NAME item_ValidityName,t.item_Name,t.item_Sex,t.item_leafletAddress,t.item_SourceType,t.item_Address,t.item_Creator,t.item_TMK,t.ITEM_CHANNELTYPECODE,t.item_pdtime,t.ITEM_CHANNELTYPENAME,t.ITEM_CHANNELTYPENAME1,t.item_Relation,t7.ITEM_BELONGSCHOOL ,t7.ITEM_BELONGSCHOOLNAME,t.item_ParentsName,t.item_EnglishName,t.item_CreateTime,track_count.item_tc,t8.ITEM_LXRQ,t8.ITEM_OFFER,t10.ITEM_NAME item_InterstName,t.item_Contract ITEM_ZXJY0,t9.ITEM_NAME item_offerName,case when t7.ITEM_STUDENT is null then '未分配' else '已分配' end ITEM_ALLOTSTATEa,t7.ITEM_ALLOTTIME,t.item_AtSchool,t11.ITEM_NAME ITEM_IDEAAGREEName,t8.ITEM_GXD,t8.ITEM_KJD,t8.item_zxjy,t.item_BirthDates,t.item_Age,t.item_Code,t.item_Phone1,t.item_AdmissionsRepresentatives,t7.ITEM_OFPERSON,t2.`NAME` ITEM_OfPersonName from tlk_sourcemanage t left join tlk_sourcestate t1 on t1.ITEM_SOURCECODE=t.ITEM_CODE and t1.ITEM_BRANDCODE='Bra0001' left join tlk_track_consultant t8 on t8.ITEM_CODE=t.ITEM_CODE and t8.item_Brand='Bra0001' and t8.ITEM_LXRQ=(select max(t81.ITEM_LXRQ) from tlk_track_consultant t81 where t81.item_Brand='Bra0001' and t81.item_code=t.item_code) left join (select ttrack.item_code,ttrack.item_brand,count(*) item_tc from tlk_track_consultant ttrack group by ttrack.item_code,ttrack.item_brand ) track_count on track_count.item_code=t.ITEM_CODE and track_count.item_Brand='Bra0001' left JOIN tlk_bd_offer t9 on t9.ITEM_CODE=t8.ITEM_OFFER left join tlk_bd_intereststate t10 on t10.ITEM_CODE=t8.ITEM_INTERSTSTATE left join tlk_bd_sourcestate t3 on t3.ITEM_CODE=t1.ITEM_STATECODE LEFT JOIN tlk_bd_chargestate t4 on t4.ITEM_CODE=t1.ITEM_PAYSTATE left join tlk_bd_trackstate t5 on t5.ITEM_CODE=t1.ITEM_VALIDITY left join tlk_sourceallot_sublist t7 on t7.ITEM_STUDENT=t.ITEM_CODE and t7.item_brand='Bra0001' and t7.ITEM_STATE='Val0001' LEFT JOIN yh_obpm.t_user t2 on t2.ID=t7.ITEM_OFPERSON LEFT JOIN tlk_bd_ideaagree t11 on t11.ITEM_CODE=t8.ITEM_IDEAAGREE left join tlk_bd_brand t6 on t6.ITEM_CODE='Bra0001' where 1=1 order by t.ITEM_CREATETIME DESC
select
t.domainid,
t.id,
t.formid,
t1.ITEM_BRANDCODE,
t6.ITEM_NAME item_BrandName,
t1.ITEM_PAYSTATE,
t4.ITEM_NAME item_PayStateName,
t1.ITEM_STATECODE,
t3.ITEM_NAME item_SourceStateName,
t1.ITEM_VALIDITY,
t5.ITEM_NAME item_ValidityName,
t.item_Name,
t.item_Sex,
t.item_leafletAddress,
t.item_SourceType,
t.item_Address,
t.item_Creator,
t.item_TMK,
t.ITEM_CHANNELTYPECODE,
t.item_pdtime,
t.ITEM_CHANNELTYPENAME,
t.ITEM_CHANNELTYPENAME1,
t.item_Relation,
t7.ITEM_BELONGSCHOOL ,
t7.ITEM_BELONGSCHOOLNAME,
t.item_ParentsName,
t.item_EnglishName,
t.item_CreateTime,
track_count.item_tc,
t8.ITEM_LXRQ,
t8.ITEM_OFFER,
t10.ITEM_NAME item_InterstName,
t.item_Contract ITEM_ZXJY0,
t9.ITEM_NAME item_offerName,
case when t7.ITEM_STUDENT is null then '未分配' else '已分配' end ITEM_ALLOTSTATEa,
t7.ITEM_ALLOTTIME,
t.item_AtSchool,
t11.ITEM_NAME ITEM_IDEAAGREEName,
t8.ITEM_GXD,
t8.ITEM_KJD,
t8.item_zxjy,
t.item_BirthDates,
t.item_Age,
t.item_Code,
t.item_Phone1,
t.item_AdmissionsRepresentatives,
t7.ITEM_OFPERSON,
t2.`NAME` ITEM_OfPersonName
from tlk_sourcemanage t
left join tlk_sourcestate t1
on t1.ITEM_SOURCECODE=t.ITEM_CODE
and t1.ITEM_BRANDCODE='Bra0001'
left join tlk_track_consultant t8
on t8.ITEM_CODE=t.ITEM_CODE
and t8.item_Brand='Bra0001'
and t8.ITEM_LXRQ=(
select max(t81.ITEM_LXRQ)
from tlk_track_consultant t81
where t81.item_Brand='Bra0001'
and t81.item_code=t.item_code)
left join (
select ttrack.item_code,
ttrack.item_brand,
count(*) item_tc
from tlk_track_consultant ttrack
group by ttrack.item_code,
ttrack.item_brand ) track_count
on track_count.item_code=t.ITEM_CODE
and track_count.item_Brand='Bra0001'
left JOIN tlk_bd_offer t9 on t9.ITEM_CODE=t8.ITEM_OFFER
left join tlk_bd_intereststate t10 on t10.ITEM_CODE=t8.ITEM_INTERSTSTATE
left join tlk_bd_sourcestate t3 on t3.ITEM_CODE=t1.ITEM_STATECODE
LEFT JOIN tlk_bd_chargestate t4 on t4.ITEM_CODE=t1.ITEM_PAYSTATE
left join tlk_bd_trackstate t5 on t5.ITEM_CODE=t1.ITEM_VALIDITY
left join tlk_sourceallot_sublist t7
on t7.ITEM_STUDENT=t.ITEM_CODE
and t7.item_brand='Bra0001'
and t7.ITEM_STATE='Val0001'
LEFT JOIN yh_obpm.t_user t2 on t2.ID=t7.ITEM_OFPERSON
LEFT JOIN tlk_bd_ideaagree t11 on t11.ITEM_CODE=t8.ITEM_IDEAAGREE
left join tlk_bd_brand t6 on t6.ITEM_CODE='Bra0001'
where 1=1
order by t.ITEM_CREATETIME DESC 主要是一堆outer join而已。
除了楼上说的基本语句的优化一以外,MySQL对于太多(比如Percona说12个)的表连接处理不好。