个只是报表统计中的一个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';
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';
#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
怎么又条件 反而数据更多
他的写法应该是#31922 对应下面的查询,
单表是#184647
SELECT COUNT(1) FROM cive_t_d_gongdanlishi;mysql不知道能不能把单表条件先做, 如果不能把where里的单表条件放on里
他的写法应该是#31922 对应下面的查询,
单表是#184647
SELECT COUNT(1) FROM cive_t_d_gongdanlishi;mysql不知道能不能把单表条件先做, 如果不能把where里的单表条件放on里对的,数量在select语句上面