SELECT TI.HALLNAME,TI.SMALT_CODE,TI.PAYMONEYDATE,TI.AMOUNT,TI.AUDITSTATE,TI.HALLCODE,TI.GATHERTYPE,TI.MONEYTYPE,TI.CORP_CODE,TI.ID,TI.REMARK FROM BT_AUDIT_HALL_TURN_IN TI INNER JOIN BT_AUDIT_OFFICE_INFO INFO ON INFO.OFFICE_ID = TI.HALLCO...
显示全部SELECT TI.HALLNAME,TI.SMALT_CODE,TI.PAYMONEYDATE,TI.AMOUNT,TI.AUDITSTATE,TI.HALLCODE,TI.GATHERTYPE,TI.MONEYTYPE,TI.CORP_CODE,TI.ID,TI.REMARK
FROM BT_AUDIT_HALL_TURN_IN TI
INNER JOIN BT_AUDIT_OFFICE_INFO INFO ON INFO.OFFICE_ID = TI.HALLCODE
WHERE (INFO.CORP_ID IS NOT NULL AND INFO.CORP_ID <> 0 AND TI.TRADE_TYPE_CODE IN ('OrgIn','DeptIn') AND INFO.VALID_SIGN='1'
and ti.paymoneydate >= '2014-09-01 ' and ti.paymoneydate <= '2015-09-30 ' and ti.auditstate = 0
and exists (select 1 from (select bankacc as smc from BT_USER_BANKACC where usercode='whliuxz'
union
select smalt_code as smc from BT_AUDIT_OFFICE_SMALT where bank_account
in (select bankacc from BT_USER_BANKACC where usercode='whliuxz')) t2
where TI.SMALT_CODE=t2.smc))
ORDER BY TI.SMALT_CODE,TI.PAYMONEYDATE,AMOUNT ASC
上面那条查询语句查询速度很慢9W条数据用了8秒左右,其中paymoneydate、AMOUNT 、SMALT_CODE这三个字段都是有索引的。我发现最后的order by语句不走索引,这是为什么? 还有我这条SQL语句怎么优化才能使查询速度提高? 谢谢!
附件:
explain (2).txt (13.49 KB)
noExists.txt (5.67 KB)
hasExists.txt (20.28 KB)
收起