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';

图片说明

4个回答

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

u011214505
醉君惜红颜 首先这个where是不能动的,其次where条件不是会降低结果集的大小吗?
2 年多之前 回复

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

weixin_38762114
我师父是霍元甲 另外,你在过滤的时候,把区间条件放到前面试试
2 年多之前 回复
weixin_38762114
我师父是霍元甲 回复醉君惜红颜: 他有没有用到这个外键的索引,我也不知道,我也不是专业的...
2 年多之前 回复
u011214505
醉君惜红颜 这个我倒是试过,可以有提升,但是如果选择的时间范围比较大的话,会不会更慢,因为用不上GDLS.GDLS_GONGDAN_RWGDID_FK索引了。。。
2 年多之前 回复

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

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

u011214505
醉君惜红颜 回复學诚: 按理说这个量级应该不是那么庞大,怎么会这么慢呢。。。
2 年多之前 回复
chenxuecheng1984
學诚 回复醉君惜红颜: 那作用基本不大了。
2 年多之前 回复
u011214505
醉君惜红颜 GDLS_GONGDAN_RWGDID_FK这个已经是索引了,GDLS_ISDELETED , GDLS_ISDISABLED,RWGD_ISDELETED , RWGD_ISDISABLED基本上80%都是等于0,加索引有用吗?
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问