数据量很大，如何优化sql

被窝里的小懒猫 2017-11-30 03:36:11
有张流水历史表，数据量大概有一亿条，需要和流水表、流水月表联合查询，目前历史表我是按分区来查，但是查询时间还是超过1分钟了，有什么其他优化sql的方法吗，需要在50s内查询出
...全文
605 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
滴滴ii 2019-12-02
  • 打赏
  • 举报
 回复
继续分区..
「已注销」 2017-12-01
  • 打赏
  • 举报
 回复
楼主你这个随机取十条的，怎么不把union all里面的直接row num<11再合并然后再取十条？select后面不要有任何的rownum ，时间字段老老实实的用，别做任何转换，正常来说不超过30s
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
如果还不行，就把<> 改成instr形式 

select * from (
select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,
to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ a, CM_BUSINESS_CODE b    
where a.BUSINESS_CODE=b.BUSINESS_CODE 
 and a.TRAN_AMT is not null and instr(a.PRD_NO,'TELLER') = 0
 and a.CARD_NO='6223635003000078888' 
AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94')
AND a.TRAN_DATE>=to_date('20170801','yyyymmdd') 
AND a.TRAN_DATE<=to_date('20170831','yyyymmdd')         
UNION ALL   
select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ_MONTH a, CM_BUSINESS_CODE b    
where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and instr(a.PRD_NO,'TELLER') = 0 and a.CARD_NO='6223635003000078888' 
AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94')
AND a.TRAN_DATE >= to_date('20170801','yyyymmdd') AND a.TRAN_DATE<=to_date('20170831','yyyymmdd')        
 UNION ALL  
select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS 
from (SELECT * FROM CM_TRAN_SEQ_HIST partition(CTSH_PART_201708)
where TRAN_DATE >= to_date('20170801','yyyymmdd') 
AND TRAN_DATE <= to_date('20170831','yyyymmdd')  
and TRAN_AMT is not null 
and instr(a.PRD_NO,'TELLER') = 0 
AND substr(POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') 
and CARD_NO = '6223635003000078888' 
) a, 
CM_BUSINESS_CODE b    
where a.BUSINESS_CODE=b.BUSINESS_CODE    
) where rownum <= 10
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
最终优化SQL如下： 

select * from (
select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,
to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ a, CM_BUSINESS_CODE b    
where a.BUSINESS_CODE=b.BUSINESS_CODE 
 and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER'   
 and a.CARD_NO='6223635003000078888' 
AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94')
AND a.TRAN_DATE>=to_date('20170801','yyyymmdd') 
AND a.TRAN_DATE<=to_date('20170831','yyyymmdd')         
UNION ALL   
select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ_MONTH a, CM_BUSINESS_CODE b    
where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER'  and a.CARD_NO='6223635003000078888' 
AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94')
AND a.TRAN_DATE >= to_date('20170801','yyyymmdd') AND a.TRAN_DATE<=to_date('20170831','yyyymmdd')        
 UNION ALL  
select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS 
from (SELECT * FROM CM_TRAN_SEQ_HIST partition(CTSH_PART_201708)
where TRAN_DATE >= to_date('20170801','yyyymmdd') 
AND TRAN_DATE <= to_date('20170831','yyyymmdd')  
and TRAN_AMT is not null 
and PRD_NO <> 'TELLER'  
AND substr(POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') 
and CARD_NO = '6223635003000078888' 
) a, 
CM_BUSINESS_CODE b    
where a.BUSINESS_CODE=b.BUSINESS_CODE    
) where rownum <= 10
被窝里的小懒猫 2017-11-30
  • 打赏
  • 举报
 回复
引用 10 楼 qq646748739 的回复:
CARD_NO 是哪张表的字段啊，别名都不加上，怎么优化？
不还意思啊，没有一直关注回复。CARD_NO是a表的，刚刚也加上了试过。
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
既然不愿意说就算了，代码暂时优化如下：


select * from (

select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,

to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ a, CM_BUSINESS_CODE b    

where a.BUSINESS_CODE=b.BUSINESS_CODE 

 and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER'   

 and CARD_NO='6223635003000078888' 

AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94')

AND a.TRAN_DATE>=to_date('20170801','yyyymmdd') 

AND a.TRAN_DATE<=to_date('20170831','yyyymmdd')         

UNION ALL   

select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ_MONTH a, CM_BUSINESS_CODE b    

where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER'       and CARD_NO='6223635003000078888' 

AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94')

AND a.TRAN_DATE >= to_date('20170801','yyyymmdd') AND a.TRAN_DATE<=to_date('20170831','yyyymmdd')        

 UNION ALL  

select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS 

from (SELECT * FROM CM_TRAN_SEQ_HIST partition(CTSH_PART_201708)

where a.TRAN_DATE >=to_date('20170801','yyyymmdd') 

AND a.TRAN_DATE <= to_date('20170831','yyyymmdd')  

and a.TRAN_AMT is not null 

and a.PRD_NO <> 'TELLER'  

AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') 

) a, 

CM_BUSINESS_CODE b    

where a.BUSINESS_CODE=b.BUSINESS_CODE    

and CARD_NO='6223635003000078888' 

) where rownum <= 10
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
引用 10 楼 qq646748739 的回复:
CARD_NO 是哪张表的字段啊，别名都不加上，怎么优化？

???
被窝里的小懒猫 2017-11-30
  • 打赏
  • 举报
 回复
引用 9 楼 qq646748739 的回复:
日期字段改了没？内层排序去掉了么？ SQL语句重新发下
select * from ( SELECT rownum rn,SEQ_NO,CCY,TRAN_TYPE,TRAN_AMT,TRAN_TIME,VOUCHERNUM,TRAN_STATUS FROM( select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ a, CM_BUSINESS_CODE b where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER' and CARD_NO='6223635003000078888' AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') AND a.TRAN_DATE>=to_date('20170801','yyyymmdd') AND a.TRAN_DATE<=to_date('20170831','yyyymmdd') UNION ALL select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ_MONTH a, CM_BUSINESS_CODE b where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER' and CARD_NO='6223635003000078888' AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') AND a.TRAN_DATE>=to_date('20170801','yyyymmdd') AND a.TRAN_DATE<=to_date('20170831','yyyymmdd') UNION ALL select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from (SELECT * FROM CM_TRAN_SEQ_HIST partition(CTSH_PART_201708) a, CM_BUSINESS_CODE b where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER' and CARD_NO='6223635003000078888' AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') ) ) where rn between '1' and '10'
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
CARD_NO 是哪张表的字段啊，别名都不加上，怎么优化？
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
日期字段改了没？内层排序去掉了么？
SQL语句重新发下
被窝里的小懒猫 2017-11-30
  • 打赏
  • 举报
 回复
引用 7 楼 qq646748739 的回复:
字段前面把表的表名加上，不加也影响性能。
按你说的方法改了，没有太大优化，1s内的差距。
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
字段前面把表的表名加上，不加也影响性能。
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
以下语句非常影响性能


AND to_char(TRAN_DATE, 'yyyymmdd') >= '20170801'

AND to_char(TRAN_DATE, 'yyyymmdd') <= '20170831'

需要改成:


AND TRAN_DATE >= to_date('20170801','yyyymmdd')

AND TRAN_DATE <= to_date('20170831','yyyymmdd')


还要去掉内存的order by
被窝里的小懒猫 2017-11-30
  • 打赏
  • 举报
 回复
引用 3 楼 qq646748739 的回复:
查询的时候在分区表后面加上partition(时间分区名称)。
这个也加了，楼上是sql
被窝里的小懒猫 2017-11-30
  • 打赏
  • 举报
 回复
select * from ( SELECT rownum rn,SEQ_NO,CCY,TRAN_TYPE,TRAN_AMT,TRAN_TIME,VOUCHERNUM,TRAN_STATUS FROM( select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ a, CM_BUSINESS_CODE b where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER' and CARD_NO='6223635003000078888' AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') AND to_char(TRAN_DATE,'yyyymmdd')>='20170801' AND to_char(TRAN_DATE,'yyyymmdd')<='20170831' UNION ALL select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from CM_TRAN_SEQ_MONTH a, CM_BUSINESS_CODE b where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER' and CARD_NO='6223635003000078888' AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') AND to_char(TRAN_DATE,'yyyymmdd')>='20170801' AND to_char(TRAN_DATE,'yyyymmdd')<='20170831' UNION ALL select a.TRAN_SEQ as SEQ_NO,a.CCY,b.BUSINESS_NAME as TRAN_TYPE,a.TRAN_AMT,to_char(a.TRAN_SYS_DATE,'yyyy-mm-dd hh24:mi:ss') TRAN_TIME,a.VOUCHERNUM,a.TRAN_STATUS from (SELECT * FROM CM_TRAN_SEQ_HIST partition(CTSH_PART_201708) WHERE to_char(TRAN_DATE,'yyyymmdd')>='20170801' AND to_char(TRAN_DATE,'yyyymmdd')<='20170831') a, CM_BUSINESS_CODE b where a.BUSINESS_CODE=b.BUSINESS_CODE and a.TRAN_AMT is not null and a.PRD_NO <>'TELLER' and CARD_NO='6223635003000078888' AND substr(a.POS_ENTRY_MODE_CODE,1,2) in ('03','04','93','94') order by 1 desc ) ) where rn between '1' and '10'
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
查询的时候在分区表后面加上partition(时间分区名称)。
被窝里的小懒猫 2017-11-30
  • 打赏
  • 举报
 回复
有建索引，有个时间字段上建了，这个条件是放在最后查的
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
分区上有建本地索引吗？
SQL优化怎么做？大量数据实战分析
如果你对sql优化不了解，请认真看完这篇文章，并跟着文章动手操作一下，这篇文章讲解了所有方面的优化技巧 一：基础数据准备 二：五百万数据插入 上面插入几条测试数据，在使用索引时还需要插入更多的数据作为...
如果数据量特别大的时候应该如何优化sql语句
2.数据量过大，如果你cdb_members的记录很多，远远大于500条，可以考虑改变程序，先从此表里面获取500条数据，然后在循环里面每条数据库关联获取其它表的信息，这样就不需要先对五个表做链接。尽量不适用联合查询，...
mysql 多表关联查询&大数据量下 sql优化
大数据量下，可能出现没有使用最优索引或者最优驱动表。 2.方案： 使用 强制索引 force index，示例 SELECT * FROM TABLE1 FORCE INDEX (IDX_NAME) JOIN TABLE2 使用 强制链接顺序，示例 SELECT * FROM TABLE1...
sql优化的几种方式
由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行...
大数据计算，如何优化SQL?
很多大数据计算都是用SQL实现的，跑得慢时就要去优化SQL，但常常碰到让人干瞪眼的情况。
Oracle

17,090

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章