MYSQL同一条SQL语句查询在本机很快，在服务器很慢很慢

[qq_33758921]

同样的一条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

下面是执行过程：

[zjcxc]

explain 的分析结果也一样？

[致命的西瓜]

这么多IN 还有重复使用的表 优化一下吧

[zjcxc]

结构一样，数据量一样？

[qq_33758921]

引用 1 楼 zjcxc 的回复:

结构一样，数据量一样？

是一样的。

[ayzen1988]

本地没有网络宽带连接等因素影响