分页函数优化注意点(一):关注业务数据
来源:互联网 发布:c语言web攻击脚本 编辑:程序博客网 时间:2024/06/07 00:52
前些日子优化过一个分页函数,回头整理的时候发现了一个问题。让我做了一周多的实验才找到原因:
相关表的信息:SUBACC_FUND_INFO表90w+条数据 PARTY_INFO表11条数据 SYS_SEQ_ID是主键。TRAN_TIME的选择性也非常高。PARTY_ID是表PARTY_INFO的主键
实验一:
DROP INDEX IDX_SUBACC_FUND_INFO;CREATE INDEX IDX_SUBACC_FUND_INFO ON SUBACC_FUND_INFO(TRAN_TIME DESC,SYS_SEQ_ID DESC) ONLINE;SELECT * FROM (SELECT /*+ GATHER_PLAN_STATISTICS INDEX(C,IDX_SUBACC_FUND_INFO) USE_NL(C,PI) LEADING(C) */ C.*, PI.CN_NAME FROM SUBACC_FUND_INFO C, PARTY_INFO PI WHERE PI.PARTY_ID = C.PARTY_ID ORDER BY C.TRAN_TIME DESC, C.SYS_SEQ_ID DESC) WHERE ROWNUM <= 20;SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL,NULL,'ALLSTATS LAST'));-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-----------------------------------------------------------------------------------------------------------------|* 1 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 30 || 2 | VIEW | | 1 | 20676 | 20 |00:00:00.01 | 30 || 3 | NESTED LOOPS | | 1 | 20676 | 20 |00:00:00.01 | 30 || 4 | TABLE ACCESS BY INDEX ROWID| SUBACC_FUND_INFO | 1 | 914K| 20 |00:00:00.01 | 25 || 5 | INDEX FULL SCAN | IDX_SUBACC_FUND_INFO | 1 | 914K| 20 |00:00:00.01 | 5 ||* 6 | INDEX RANGE SCAN | IDX_PARTY_INFO | 20 | 1 | 20 |00:00:00.01 | 5 |-----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=20) 6 - access("PI"."PARTY_ID"="C"."PARTY_ID")
实验二:修改order by顺序和组合索引的顺序
DROP INDEX IDX_SUBACC_FUND_INFO;CREATE INDEX IDX_SUBACC_FUND_INFO ON SUBACC_FUND_INFO(SYS_SEQ_ID DESC,TRAN_TIME DESC) ONLINE;SELECT * FROM (SELECT /*+ GATHER_PLAN_STATISTICS INDEX(C,IDX_SUBACC_FUND_INFO) USE_NL(C,PI) LEADING(C) */ C.*, PI.CN_NAME FROM SUBACC_FUND_INFO C, PARTY_INFO PI WHERE PI.PARTY_ID = C.PARTY_ID ORDER BY C.SYS_SEQ_ID DESC, C.TRAN_TIME DESC) WHERE ROWNUM <= 20;SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL,NULL,'ALLSTATS LAST'));-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-----------------------------------------------------------------------------------------------------------------|* 1 | COUNT STOPKEY | | 1 | | 20 |00:00:05.98 | 782K|| 2 | VIEW | | 1 | 20676 | 20 |00:00:05.98 | 782K|| 3 | NESTED LOOPS | | 1 | 20676 | 20 |00:00:05.98 | 782K|| 4 | TABLE ACCESS BY INDEX ROWID| SUBACC_FUND_INFO | 1 | 914K| 852K|00:00:03.41 | 782K|| 5 | INDEX FULL SCAN | IDX_SUBACC_FUND_INFO | 1 | 914K| 852K|00:00:00.01 | 5846 ||* 6 | INDEX RANGE SCAN | IDX_PARTY_INFO | 852K| 1 | 20 |00:00:02.03 | 6 |-----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=20) 6 - access("PI"."PARTY_ID"="C"."PARTY_ID")在预估的PLAN里面两种执行计划一模一样,在真实的执行计划中返回的真实行数A-Rows却是千差万别
由上面的SQL语句及创建索引的语句可以看出来无非就是ORDER BY两个字段的顺序变了,但是和索引保持一致
这种问题,我以前从未遇到,为了解决问题,我做了如下实验来猜想我哪一个环节出了问题
1.看字段名称和类型是否有影响。(类型设一致。名称互换。结果:无影响)
2.看字段的长度是否有影响。(长度设置成一样、长度互换。结果:无影响)
3.看字段内容的长度是否有影响。(字段长度设置成一样、字段长度互换。结果:无影响)
4.去掉关联表,只对一个表分页。(结果:有影响)实验如下 :
DROP INDEX IDX_SUBACC_FUND_INFO;CREATE INDEX IDX_SUBACC_FUND_INFO ON SUBACC_FUND_INFO(TRAN_TIME DESC,SYS_SEQ_ID DESC) ONLINE;SELECT * FROM (SELECT * FROM SUBACC_FUND_INFO C ORDER BY C.TRAN_TIME DESC, C.SYS_SEQ_ID DESC) WHERE ROWNUM <= 20;--结果:0.1S响应,逻辑读24DROP INDEX IDX_SUBACC_FUND_INFO;CREATE INDEX IDX_SUBACC_FUND_INFO ON SUBACC_FUND_INFO(SYS_SEQ_ID DESC,TRAN_TIME DESC) ONLINE;SELECT * FROM (SELECT * FROM SUBACC_FUND_INFO C ORDER BY C.SYS_SEQ_ID DESC,C.TRAN_TIME DESC) WHERE ROWNUM <= 20;--结果:0.1S响应,逻辑读24
由此得出。可能是关联表PARTY_INFO的原因:
SELECT A.PARTY_ID,A.EN_NAME,A.CN_NAME,A.ADDRESS FROM PARTY_INFO A
可以看出上面的数据很乱。根本就是手工输入进去的测试数据,也就是说有很多数据很可能都匹配不到结果
所以我猜测实验2里面,排序之后的前N行(N<=1000)都匹配不到数据。
我们看真实的plan里面返回852K就,大胆的猜测一下:可能索引返回852K行的时候才在PI表中匹配到数据
对实验一验证:
SELECT CC.PARTY_ID, SYS_SEQ_ID, TRAN_TIME, PI.PARTY_ID,NUM1 "行数" FROM (SELECT PARTY_ID, SYS_SEQ_ID, TRAN_TIME, ROW_NUMBER() OVER(ORDER BY C.TRAN_TIME DESC, C.SYS_SEQ_ID DESC) NUM1 FROM SUBACC_FUND_INFO C) CC, PARTY_INFO PI WHERE PI.PARTY_ID = CC.PARTY_ID;
对实验二的验证:
SELECT CC.PARTY_ID, SYS_SEQ_ID, TRAN_TIME, PI.PARTY_ID, NUM1 "行数" FROM (SELECT PARTY_ID, SYS_SEQ_ID, TRAN_TIME, ROW_NUMBER() OVER(ORDER BY C.SYS_SEQ_ID DESC, C.TRAN_TIME DESC) NUM1 FROM SUBACC_FUND_INFO C) CC, PARTY_INFO PI WHERE PI.PARTY_ID = CC.PARTY_ID;
由上可得:实验1索引返回数据的前20条都在参数表PI中能够匹配到,扫描20条数据就停止。所以能够很快响应
实验2索引返回的数据前20条都没有能在参数表PI中匹配到,所以只能往下继续扫描第21,22,23,……一直到852824行的时候才开始匹配到结果
也就是说实验2的分页是从第852824行开始取20条数据返回。这就是坑爹的原因!!!!
我们可能更多的是去尝试怎么优化,而很少去关注业务数据。所以有时候我们还是需要稍微的关注一下业务数据!
其实正常情况下是不会出现这种问题的,因为业务的明细数据去关联参数表(维度表),都是100%可以匹配上的。这是最坑爹的地方就是测试环境的数据有问题
但是有种类似情况的分页语句优化还是需要引起注意:
比如:分页函数里面的表关联的结果返回只有19条数据,而rownum <= 20。这种情况比上述情况还要恶劣,因为不够20行就会一直扫描,直到扫描驱动表所有的行
这种结果在多表关联的分页函数里面还是会出现的。这种优化方法也很简单就是改SQL:rownum <= 20 改成 rownum <= 19 。搞定!!!
- 分页函数优化注意点(一):关注业务数据
- 数据库分页注意点
- shell注意点(一)
- listview实现分页加载数据的注意点
- javascript(注意点) 函数
- 性能优化注意点
- 优化tableview注意点
- 复杂业务逻辑注意的几个点
- Objective-C之注意点(一)
- 基础c注意点(一)
- JS match() 方法注意点 (一)
- C语言学习注意点(一)
- PHP中常用函数以及若干注意点的说明(一)
- 数据迁移注意点
- 数据中的注意点
- 面试注意点和面试官最关注的点
- php 函数注意点(1)
- javascript(注意点) 函数 filter、sort
- UVA 10791
- pinvmap
- 项目报错:Cannot find class file for javax/servlet/ServletException
- 关于linux中交叉编译器的配置
- pinvred
- 分页函数优化注意点(一):关注业务数据
- c语言常见函数自编写
- 有一行电文,已按下面规律译成密码
- 单片机汇编指令中RETI和RET的区别
- 链表
- django的安装
- 华为机试—整形数组异集
- shell学习三十天----break,continue,shift,getopts
- 在Linux-0.11中实现基于内核栈切换的进程切换