sql的cout()统计数据太多不知道有没有办法提高查询速度

Question

select a.code as reportCode,b.id,f.NAME,b.title,b.CODE,to_char(b.START_DATE,'yyyy') as year,b.RGID,c.TITLE as groupName,count(e.VALUE) as dataNum
        from T_R_MT_UREPORT a
        right join (select * from T_R_MONITOR_TASK where is_del=0) b on a.mtid=b.id
        left outer join (select * from T_R_MT_REPORT_GROUP where IS_DEL=0) c on b.RGID=c.ID
        left outer join (select * from T_R_MT_RG_ITEM where IS_DEL=0 and TITLE in ('检测项目')) d on c.ID=d.RGID
        left outer join (select * from T_R_MT_REPORT_DATA where IS_DEL=0) e on d.ID=e.RGIID and b.ID=e.MTID
        left outer join (select * from T_B_ORGANIZATION where IS_DEL=0) f on b.OID=f.ID
        where
        a.IS_DEL=0
        and b.VALID_STAGE >= 6
        and c.IS_SUBMIT=1
        and f.VALID_STAGE IN (8,9)
        and e.CODE IN (
        SELECT y.CODE FROM T_R_MT_RG_ITEM z
        LEFT OUTER JOIN T_R_MT_REPORT_DATA y ON z.ID=y.RGIID
        WHERE z.RGID=c.ID AND z.TITLE IN ('报告编号') AND y.VALUE = a.code and y.mtid=b.id
        )
        group by a.code,f.NAME,b.id,b.TITLE,b.CODE,to_char(b.START_DATE,'yyyy'),b.RGID,c.TITLE
        having count(e.VALUE) > 0
        order by dataNum desc

Answer 1

这个代码是你自己写的?干嘛要用这么多的子查询呢，我这里经常交互访问的元组创建了索引，你可以用exist代替in，效率更高很多，这些还得结合你具体的执行计划来看，有的时候不是光改代码逻辑就看得到明显性能提升

CREATE INDEX idx_T_R_MT_UREPORT_IS_DEL ON T_R_MT_UREPORT (IS_DEL);
CREATE INDEX idx_T_R_MONITOR_TASK_is_del ON T_R_MONITOR_TASK (is_del);
CREATE INDEX idx_T_R_MONITOR_TASK_VALID_STAGE ON T_R_MONITOR_TASK (VALID_STAGE);
CREATE INDEX idx_T_R_MT_REPORT_GROUP_IS_DEL ON T_R_MT_REPORT_GROUP (IS_DEL);
CREATE INDEX idx_T_R_MT_RG_ITEM_IS_DEL ON T_R_MT_RG_ITEM (IS_DEL);
CREATE INDEX idx_T_R_MT_RG_ITEM_TITLE ON T_R_MT_RG_ITEM (TITLE);
CREATE INDEX idx_T_R_MT_REPORT_DATA_IS_DEL ON T_R_MT_REPORT_DATA (IS_DEL);
CREATE INDEX idx_T_B_ORGANIZATION_IS_DEL ON T_B_ORGANIZATION (IS_DEL);
CREATE INDEX idx_T_B_ORGANIZATION_VALID_STAGE ON T_B_ORGANIZATION (VALID_STAGE);

CREATE INDEX idx_T_R_MONITOR_TASK_id_VALID_STAGE ON T_R_MONITOR_TASK (id, VALID_STAGE);
CREATE INDEX idx_T_R_MT_REPORT_GROUP_ID_IS_SUBMIT ON T_R_MT_REPORT_GROUP (ID, IS_SUBMIT);
CREATE INDEX idx_T_R_MT_RG_ITEM_RGID_TITLE_ID ON T_R_MT_RG_ITEM (RGID, TITLE, ID);
CREATE INDEX idx_T_R_MT_REPORT_DATA_RGIID_MTID ON T_R_MT_REPORT_DATA (RGIID, MTID);

CREATE INDEX idx_T_R_MT_UREPORT_code ON T_R_MT_UREPORT (code);
CREATE INDEX idx_T_R_MONITOR_TASK_id_title_START_DATE_RGID_CODE ON T_R_MONITOR_TASK (id, title, START_DATE, RGID, CODE);
CREATE INDEX idx_T_R_MT_REPORT_GROUP_ID_TITLE ON T_R_MT_REPORT_GROUP (ID, TITLE);
CREATE INDEX idx_T_B_ORGANIZATION_NAME ON T_B_ORGANIZATION (NAME);