同样的一条SQL语句 同样的表 在本地查询很快。在服务器要几十秒。
以下是查询语句：
select
tblcibasei0_.ID as ID113_,
tblcibasei0_.U_COUNT as U2_113_,
tblcibasei0_.AFFECT_ARRANGE as AFFECT3_113_,
tblcibasei0_.APPROVE_STATUS as APPROVE4_113_,
tblcibasei0_.AUDIT_STATUS as AUDIT5_113_,
tblcibasei0_.AUDITREMARK as AUDITREM6_113_,
tblcibasei0_.AUDITRESULT as AUDITRES7_113_,
tblcibasei0_.AVAILABILITY as AVAILABI8_113_,
tblcibasei0_.BACKUP_TYPE as BACKUP9_113_,
tblcibasei0_.BL_CREATEDATE as BL10_113_,
tblcibasei0_.BL_CREATEUSERID as BL11_113_,
tblcibasei0_.BL_DELFLAG as BL12_113_,
tblcibasei0_.BL_RELCIID as BL13_113_,
tblcibasei0_.BL_VERSION as BL14_113_,
tblcibasei0_.CF01 as CF15_113_,
tblcibasei0_.CF02 as CF16_113_,
tblcibasei0_.CF03 as CF17_113_,
tblcibasei0_.CF04 as CF18_113_,
tblcibasei0_.CF05 as CF19_113_,
tblcibasei0_.CF06 as CF20_113_,
tblcibasei0_.CF07 as CF21_113_,
tblcibasei0_.CF08 as CF22_113_,
tblcibasei0_.CF09 as CF23_113_,
tblcibasei0_.CI_CLASS as CI24_113_,
tblcibasei0_.CI_INDEX_PROVIDER as CI25_113_,
tblcibasei0_.MFRID as MFRID113_,
tblcibasei0_.CI_NAME as CI26_113_,
tblcibasei0_.CI_NO as CI27_113_,
tblcibasei0_.CI_PROVIDER as CI28_113_,
tblcibasei0_.CI_RECORDTYPE as CI29_113_,
tblcibasei0_.CI_RUNSTATUS as CI30_113_,
tblcibasei0_.CI_SORTID as CI31_113_,
tblcibasei0_.CI_STATUS as CI32_113_,
tblcibasei0_.CM_FLAG as CM33_113_,
tblcibasei0_.CONFIDENTIALITY as CONFIDE34_113_,
tblcibasei0_.CREATDATE as CREATDATE113_,
tblcibasei0_.CREATEUSER as CREATEUSER113_,
tblcibasei0_.CTI_PARENT_INSTANCE_ID as CTI37_113_,
tblcibasei0_.DELETED_DATE as DELETED38_113_,
tblcibasei0_.DESCRIPTION as DESCRIP39_113_,
tblcibasei0_.DUTY_USER_ID as DUTY40_113_,
tblcibasei0_.EDIT_STATUS as EDIT41_113_,
tblcibasei0_.EFFECT_DEGREE as EFFECT42_113_,
tblcibasei0_.EFFECT_RANGE as EFFECT43_113_,
tblcibasei0_.FD01 as FD94_113_,
tblcibasei0_.FD02 as FD95_113_,
tblcibasei0_.FD03 as FD96_113_,
tblcibasei0_.FD04 as FD97_113_,
tblcibasei0_.FD05 as FD98_113_,
tblcibasei0_.FD06 as FD99_113_,
tblcibasei0_.FD07 as FD100_113_,
tblcibasei0_.FD08 as FD101_113_,
tblcibasei0_.FD09 as FD102_113_,
tblcibasei0_.FD10 as FD103_113_,
tblcibasei0_.FI01 as FI104_113_,
tblcibasei0_.FI02 as FI105_113_,
tblcibasei0_.FI03 as FI106_113_,
tblcibasei0_.FI04 as FI107_113_,
tblcibasei0_.FI05 as FI108_113_,
tblcibasei0_.FI06 as FI109_113_,
tblcibasei0_.FI07 as FI110_113_,
tblcibasei0_.FI08 as FI111_113_,
tblcibasei0_.FI09 as FI112_113_,
tblcibasei0_.FI10 as FI113_113_,
tblcibasei0_.GEOG_ID as GEOG114_113_,
tblcibasei0_.IMANAGE_IP_1 as IMANAGE115_113_,
tblcibasei0_.INSTALL_DATE as INSTALL116_113_,
tblcibasei0_.INSTANCEID as INSTANCEID113_,
tblcibasei0_.INTEGRATORSID as INTEGR118_113_,
tblcibasei0_.IS_BACK_RECOD as IS119_113_,
tblcibasei0_.LASTAUDITDATE as LASTAU120_113_,
tblcibasei0_.LASTAUDITUSER as LASTAU121_113_,
tblcibasei0_.LASTINSPECTDATE as LASTIN122_113_,
tblcibasei0_.LASTINSPECTUSER as LASTIN123_113_,
tblcibasei0_.LASTMODEFYUSER as LASTMO124_113_,
tblcibasei0_.LASTMODIFYDATE as LASTMO125_113_,
tblcibasei0_.MANAGE_MAIN_ID as MANAGE129_113_,
tblcibasei0_.MAINTAINID as MAINTAINID113_,
tblcibasei0_.MAINUSERINFO as MAINUS127_113_,
tblcibasei0_.MANAGE_IP as MANAGE128_113_,
tblcibasei0_.MANAGE_SPARE_ID as MANAGE130_113_,
tblcibasei0_.MANAGE_WINDOW as MANAGE131_113_,
tblcibasei0_.MANAGERID as MANAGERID113_,
tblcibasei0_.MANAGERGROUPID as MANAGE132_113_,
tblcibasei0_.MODEL as MODEL113_,
tblcibasei0_.MODELID as MODELID113_,
tblcibasei0_.NEXTAUDITDATE as NEXTAU137_113_,
tblcibasei0_.ONLINE_DATE as ONLINE138_113_,
tblcibasei0_. OUT_OF_FACT_DATE as column139_113_,
tblcibasei0_.PARENT_CIID as PARENT140_113_,
tblcibasei0_.PLACE as PLACE113_,
tblcibasei0_.POSITION_ID as POSITION142_113_,
tblcibasei0_.PRIORITY as PRIORITY113_,
tblcibasei0_.PURCHASE_DATE as PURCHASE144_113_,
tblcibasei0_.PURPOSE as PURPOSE113_,
tblcibasei0_.REMARK as REMARK113_,
tblcibasei0_.SCRAPPED_DATE as SCRAPPED147_113_,
tblcibasei0_.SERVER_LEVEL as SERVER148_113_,
tblcibasei0_.SHORTDESCRIPTION as SHORTD149_113_,
tblcibasei0_.SN as SN113_,
tblcibasei0_.SUPPLIESID as SUPPLIESID113_,
tblcibasei0_.SYSTEMMANAGER as SYSTEM152_113_,
tblcibasei0_.USE_DEPT as USE153_113_,
tblcibasei0_.VERSIONNUMBER as VERSIO154_113_
from
TBL_CI_BASE_INFO tblcibasei0_
where
'luceneKey'='luceneKey'
and (
tblcibasei0_.GEOG_ID is null
or tblcibasei0_.GEOG_ID in (
999999999999 , 100001
)
)
and (
tblcibasei0_.ID not in (
select
tblcirelat1_.PRI_CIID
from
TBL_CI_RELATION_INFO tblcirelat1_
where
tblcirelat1_.SEC_CIID=66
or tblcirelat1_.PRI_CIID=66
)
)
and tblcibasei0_.APPROVE_STATUS<>1
and tblcibasei0_.APPROVE_STATUS<>3
and (
tblcibasei0_.CI_STATUS is null
or tblcibasei0_.CI_STATUS not in (
select
tblcistatu2_.ID
from
TBL_CI_STATUS_DEFINE tblcistatu2_
where
tblcistatu2_.STATUS_CODE='Deleted'
)
)
and tblcibasei0_.CM_FLAG=1
and tblcibasei0_.EDIT_STATUS=0
and (
tblcibasei0_.IS_BACK_RECOD=0
or tblcibasei0_.IS_BACK_RECOD is null
)
and (
tblcibasei0_.CI_CLASS in (
select
tblcicikin3_.KIND_CODE
from
TBL_CI_CIKIND_INFO tblcicikin3_
where
tblcicikin3_.KIND_ID in (
select
distinct tblcirelat4_.PRI_CISORT_ID
from
TBL_CI_RELATION_RULEINFO tblcirelat4_
where
tblcirelat4_.SEC_CISORT_ID=1002548
and tblcirelat4_.REL_ID=4
)
)
or tblcibasei0_.CI_CLASS in (
select
tblcicikin5_.KIND_CODE
from
TBL_CI_CIKIND_INFO tblcicikin5_
where
tblcicikin5_.KIND_ID in (
select
distinct tblcirelat6_.SEC_CISORT_ID
from
TBL_CI_RELATION_RULEINFO tblcirelat6_
where
tblcirelat6_.PRI_CISORT_ID=1002548
and tblcirelat6_.REL_ID=4
)
)
)
and 1=1
and tblcibasei0_.ID<>1
and (
tblcibasei0_.CI_SORTID in (
select
tblitsmsys7_.ID
from
TBL_ITSM_SYSTEM_CTIINFO tblitsmsys7_
where
tblitsmsys7_.DELFLAG=0
and tblitsmsys7_.CMFLAG=1
and (
tblitsmsys7_.ID in (
select
tblitsmsys8_.CTIID
from
TBL_ITSM_SYSTEM_GROUPRELSKILL tblitsmsys8_,
TBL_ITSM_GROUP_INFO tblitsmgro9_
where
tblitsmsys8_.GOURPID=tblitsmgro9_.ID
and tblitsmgro9_.EDIT_STATUS=0
and (
tblitsmsys8_.GOURPID in (
select
tblitsmgro10_.GROUP_ID
from
TBL_ITSM_GROUP_ENGINEER tblitsmgro10_,
TBL_ITSM_GROUP_INFO tblitsmgro11_
where
tblitsmgro10_.GROUP_ID=tblitsmgro11_.ID
and tblitsmgro10_.USER_ID=100001
and (
tblitsmgro11_.FLOWCODE in (
'999999999999' , 'CONFIGURE'
)
)
)
)
)
)
and length(tblitsmsys7_.CLASSCODE)=15
)
)
and 'filter'='filter'
order by
tblcibasei0_.CI_NO DESC limit 20
下面是执行过程：
以下是查询语句：
select
tblcibasei0_.ID as ID113_,
tblcibasei0_.U_COUNT as U2_113_,
tblcibasei0_.AFFECT_ARRANGE as AFFECT3_113_,
tblcibasei0_.APPROVE_STATUS as APPROVE4_113_,
tblcibasei0_.AUDIT_STATUS as AUDIT5_113_,
tblcibasei0_.AUDITREMARK as AUDITREM6_113_,
tblcibasei0_.AUDITRESULT as AUDITRES7_113_,
tblcibasei0_.AVAILABILITY as AVAILABI8_113_,
tblcibasei0_.BACKUP_TYPE as BACKUP9_113_,
tblcibasei0_.BL_CREATEDATE as BL10_113_,
tblcibasei0_.BL_CREATEUSERID as BL11_113_,
tblcibasei0_.BL_DELFLAG as BL12_113_,
tblcibasei0_.BL_RELCIID as BL13_113_,
tblcibasei0_.BL_VERSION as BL14_113_,
tblcibasei0_.CF01 as CF15_113_,
tblcibasei0_.CF02 as CF16_113_,
tblcibasei0_.CF03 as CF17_113_,
tblcibasei0_.CF04 as CF18_113_,
tblcibasei0_.CF05 as CF19_113_,
tblcibasei0_.CF06 as CF20_113_,
tblcibasei0_.CF07 as CF21_113_,
tblcibasei0_.CF08 as CF22_113_,
tblcibasei0_.CF09 as CF23_113_,
tblcibasei0_.CI_CLASS as CI24_113_,
tblcibasei0_.CI_INDEX_PROVIDER as CI25_113_,
tblcibasei0_.MFRID as MFRID113_,
tblcibasei0_.CI_NAME as CI26_113_,
tblcibasei0_.CI_NO as CI27_113_,
tblcibasei0_.CI_PROVIDER as CI28_113_,
tblcibasei0_.CI_RECORDTYPE as CI29_113_,
tblcibasei0_.CI_RUNSTATUS as CI30_113_,
tblcibasei0_.CI_SORTID as CI31_113_,
tblcibasei0_.CI_STATUS as CI32_113_,
tblcibasei0_.CM_FLAG as CM33_113_,
tblcibasei0_.CONFIDENTIALITY as CONFIDE34_113_,
tblcibasei0_.CREATDATE as CREATDATE113_,
tblcibasei0_.CREATEUSER as CREATEUSER113_,
tblcibasei0_.CTI_PARENT_INSTANCE_ID as CTI37_113_,
tblcibasei0_.DELETED_DATE as DELETED38_113_,
tblcibasei0_.DESCRIPTION as DESCRIP39_113_,
tblcibasei0_.DUTY_USER_ID as DUTY40_113_,
tblcibasei0_.EDIT_STATUS as EDIT41_113_,
tblcibasei0_.EFFECT_DEGREE as EFFECT42_113_,
tblcibasei0_.EFFECT_RANGE as EFFECT43_113_,
tblcibasei0_.FD01 as FD94_113_,
tblcibasei0_.FD02 as FD95_113_,
tblcibasei0_.FD03 as FD96_113_,
tblcibasei0_.FD04 as FD97_113_,
tblcibasei0_.FD05 as FD98_113_,
tblcibasei0_.FD06 as FD99_113_,
tblcibasei0_.FD07 as FD100_113_,
tblcibasei0_.FD08 as FD101_113_,
tblcibasei0_.FD09 as FD102_113_,
tblcibasei0_.FD10 as FD103_113_,
tblcibasei0_.FI01 as FI104_113_,
tblcibasei0_.FI02 as FI105_113_,
tblcibasei0_.FI03 as FI106_113_,
tblcibasei0_.FI04 as FI107_113_,
tblcibasei0_.FI05 as FI108_113_,
tblcibasei0_.FI06 as FI109_113_,
tblcibasei0_.FI07 as FI110_113_,
tblcibasei0_.FI08 as FI111_113_,
tblcibasei0_.FI09 as FI112_113_,
tblcibasei0_.FI10 as FI113_113_,
tblcibasei0_.GEOG_ID as GEOG114_113_,
tblcibasei0_.IMANAGE_IP_1 as IMANAGE115_113_,
tblcibasei0_.INSTALL_DATE as INSTALL116_113_,
tblcibasei0_.INSTANCEID as INSTANCEID113_,
tblcibasei0_.INTEGRATORSID as INTEGR118_113_,
tblcibasei0_.IS_BACK_RECOD as IS119_113_,
tblcibasei0_.LASTAUDITDATE as LASTAU120_113_,
tblcibasei0_.LASTAUDITUSER as LASTAU121_113_,
tblcibasei0_.LASTINSPECTDATE as LASTIN122_113_,
tblcibasei0_.LASTINSPECTUSER as LASTIN123_113_,
tblcibasei0_.LASTMODEFYUSER as LASTMO124_113_,
tblcibasei0_.LASTMODIFYDATE as LASTMO125_113_,
tblcibasei0_.MANAGE_MAIN_ID as MANAGE129_113_,
tblcibasei0_.MAINTAINID as MAINTAINID113_,
tblcibasei0_.MAINUSERINFO as MAINUS127_113_,
tblcibasei0_.MANAGE_IP as MANAGE128_113_,
tblcibasei0_.MANAGE_SPARE_ID as MANAGE130_113_,
tblcibasei0_.MANAGE_WINDOW as MANAGE131_113_,
tblcibasei0_.MANAGERID as MANAGERID113_,
tblcibasei0_.MANAGERGROUPID as MANAGE132_113_,
tblcibasei0_.MODEL as MODEL113_,
tblcibasei0_.MODELID as MODELID113_,
tblcibasei0_.NEXTAUDITDATE as NEXTAU137_113_,
tblcibasei0_.ONLINE_DATE as ONLINE138_113_,
tblcibasei0_. OUT_OF_FACT_DATE as column139_113_,
tblcibasei0_.PARENT_CIID as PARENT140_113_,
tblcibasei0_.PLACE as PLACE113_,
tblcibasei0_.POSITION_ID as POSITION142_113_,
tblcibasei0_.PRIORITY as PRIORITY113_,
tblcibasei0_.PURCHASE_DATE as PURCHASE144_113_,
tblcibasei0_.PURPOSE as PURPOSE113_,
tblcibasei0_.REMARK as REMARK113_,
tblcibasei0_.SCRAPPED_DATE as SCRAPPED147_113_,
tblcibasei0_.SERVER_LEVEL as SERVER148_113_,
tblcibasei0_.SHORTDESCRIPTION as SHORTD149_113_,
tblcibasei0_.SN as SN113_,
tblcibasei0_.SUPPLIESID as SUPPLIESID113_,
tblcibasei0_.SYSTEMMANAGER as SYSTEM152_113_,
tblcibasei0_.USE_DEPT as USE153_113_,
tblcibasei0_.VERSIONNUMBER as VERSIO154_113_
from
TBL_CI_BASE_INFO tblcibasei0_
where
'luceneKey'='luceneKey'
and (
tblcibasei0_.GEOG_ID is null
or tblcibasei0_.GEOG_ID in (
999999999999 , 100001
)
)
and (
tblcibasei0_.ID not in (
select
tblcirelat1_.PRI_CIID
from
TBL_CI_RELATION_INFO tblcirelat1_
where
tblcirelat1_.SEC_CIID=66
or tblcirelat1_.PRI_CIID=66
)
)
and tblcibasei0_.APPROVE_STATUS<>1
and tblcibasei0_.APPROVE_STATUS<>3
and (
tblcibasei0_.CI_STATUS is null
or tblcibasei0_.CI_STATUS not in (
select
tblcistatu2_.ID
from
TBL_CI_STATUS_DEFINE tblcistatu2_
where
tblcistatu2_.STATUS_CODE='Deleted'
)
)
and tblcibasei0_.CM_FLAG=1
and tblcibasei0_.EDIT_STATUS=0
and (
tblcibasei0_.IS_BACK_RECOD=0
or tblcibasei0_.IS_BACK_RECOD is null
)
and (
tblcibasei0_.CI_CLASS in (
select
tblcicikin3_.KIND_CODE
from
TBL_CI_CIKIND_INFO tblcicikin3_
where
tblcicikin3_.KIND_ID in (
select
distinct tblcirelat4_.PRI_CISORT_ID
from
TBL_CI_RELATION_RULEINFO tblcirelat4_
where
tblcirelat4_.SEC_CISORT_ID=1002548
and tblcirelat4_.REL_ID=4
)
)
or tblcibasei0_.CI_CLASS in (
select
tblcicikin5_.KIND_CODE
from
TBL_CI_CIKIND_INFO tblcicikin5_
where
tblcicikin5_.KIND_ID in (
select
distinct tblcirelat6_.SEC_CISORT_ID
from
TBL_CI_RELATION_RULEINFO tblcirelat6_
where
tblcirelat6_.PRI_CISORT_ID=1002548
and tblcirelat6_.REL_ID=4
)
)
)
and 1=1
and tblcibasei0_.ID<>1
and (
tblcibasei0_.CI_SORTID in (
select
tblitsmsys7_.ID
from
TBL_ITSM_SYSTEM_CTIINFO tblitsmsys7_
where
tblitsmsys7_.DELFLAG=0
and tblitsmsys7_.CMFLAG=1
and (
tblitsmsys7_.ID in (
select
tblitsmsys8_.CTIID
from
TBL_ITSM_SYSTEM_GROUPRELSKILL tblitsmsys8_,
TBL_ITSM_GROUP_INFO tblitsmgro9_
where
tblitsmsys8_.GOURPID=tblitsmgro9_.ID
and tblitsmgro9_.EDIT_STATUS=0
and (
tblitsmsys8_.GOURPID in (
select
tblitsmgro10_.GROUP_ID
from
TBL_ITSM_GROUP_ENGINEER tblitsmgro10_,
TBL_ITSM_GROUP_INFO tblitsmgro11_
where
tblitsmgro10_.GROUP_ID=tblitsmgro11_.ID
and tblitsmgro10_.USER_ID=100001
and (
tblitsmgro11_.FLOWCODE in (
'999999999999' , 'CONFIGURE'
)
)
)
)
)
)
and length(tblitsmsys7_.CLASSCODE)=15
)
)
and 'filter'='filter'
order by
tblcibasei0_.CI_NO DESC limit 20
下面是执行过程：
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货