mysql的这个sql怎么优化

这个只是报表统计中的一个sql，运行时间有8秒之多

 SELECT DISTINCT GDLS.GDLS_GONGDAN_RWGDID_FK      GDID,
                GDLS.GDLS_CAOZUODANWEI_DEMEID_FK HECHAQIYE,
                GDLS.GDLS_CAOZUODANWEI_DEMEID_FN HECHAQIYEFN
  FROM CIVE_T_D_RENWUGONGDAN RWGD
  JOIN cive_t_d_gongdanlishi GDLS
    ON (RWGD.RWGD_ID = GDLS.GDLS_GONGDAN_RWGDID_FK)
 WHERE RWGD.RWGD_GONGDANZHUANGTAI <> '01'
   AND GDLS.GDLS_ISDELETED = 0
   AND GDLS.GDLS_ISDISABLED = 0
   AND RWGD.RWGD_ISDELETED = 0
   AND RWGD.RWGD_ISDISABLED = 0
   AND RWGD_GONGDANZHUANGTAI <> '05'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '08'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '17'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '09'
   AND GDLS_CREATETIME >= '2017-06-07 00:00:00'
   AND GDLS_CREATETIME <= '2017-07-07 23:59:59'

以下是数量分布：

 #77038
SELECT COUNT(1) FROM CIVE_T_D_RENWUGONGDAN;
#184647
SELECT COUNT(1) FROM cive_t_d_gongdanlishi;
#31922
SELECT COUNT(1) 
  FROM cive_t_d_gongdanlishi GDLS
 WHERE GDLS.GDLS_ISDELETED = 0
   AND GDLS.GDLS_ISDISABLED = 0
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '08'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '17'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '09'
   AND GDLS_CREATETIME >= '2017-06-07 00:00:00'
   AND GDLS_CREATETIME <= '2017-07-07 23:59:59';
#70669
SELECT COUNT(1)
  FROM CIVE_T_D_RENWUGONGDAN RWGD
 WHERE RWGD.RWGD_GONGDANZHUANGTAI <> '01'
   AND RWGD.RWGD_ISDELETED = 0
   AND RWGD.RWGD_ISDISABLED = 0
   AND RWGD_GONGDANZHUANGTAI <> '05';

这个是执行计划：

 EXPLAIN SELECT DISTINCT GDLS.GDLS_GONGDAN_RWGDID_FK      GDID,
                GDLS.GDLS_CAOZUODANWEI_DEMEID_FK HECHAQIYE,
                GDLS.GDLS_CAOZUODANWEI_DEMEID_FN HECHAQIYEFN
  FROM CIVE_T_D_RENWUGONGDAN RWGD
  JOIN cive_t_d_gongdanlishi GDLS
    ON (RWGD.RWGD_ID = GDLS.GDLS_GONGDAN_RWGDID_FK)
 WHERE RWGD.RWGD_GONGDANZHUANGTAI <> '01'
   AND GDLS.GDLS_ISDELETED = 0
   AND GDLS.GDLS_ISDISABLED = 0
   AND RWGD.RWGD_ISDELETED = 0
   AND RWGD.RWGD_ISDISABLED = 0
   AND RWGD_GONGDANZHUANGTAI <> '05'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '08'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '17'
   AND GDLS.GDLS_CHULIHOUZHUANGTAI <> '09'
   AND GDLS_CREATETIME >= '2017-06-07 00:00:00'
   AND GDLS_CREATETIME <= '2017-07-07 23:59:59';

WHERE 子句 ，条件判断 太多了啊

你可以试试，先查询GDLS的表数据，减少JOIN的数据量。例如join (select * from GDLS where [所有与GDLS先关的条件])

加索引，cive_t_d_gongdanlishi 字段 GDLS_ISDELETED 和 GDLS_ISDISABLED ， CIVE_T_D_RENWUGONGDAN 字段 RWGD_ISDELETED 和 RWGD_ISDISABLED

RWGD.RWGD_ID 如果是主键就不用管了，否则加索引， GDLS.GDLS_GONGDAN_RWGDID_FK 加索引