特殊分页语句的改写优化

最新推荐文章于 2022-10-26 10:52:40 发布
最新推荐文章于 2022-10-26 10:52:40 发布
阅读量865 收藏 2
点赞数 1
分类专栏： 【SQL TUNING】 24号信仰の优化专栏
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Skybig1988/article/details/46670055
版权

甲方某一个客户反应一个报表页面查询点击之后需要等很久（半分钟）才能显示数据，点击页面生成的SQL如下：

 

SELECT ROWNUM R, EE.*
  FROM (SELECT E.*
          FROM (SELECT C.*, PI.CN_NAME
                  FROM CCCCCC_FFFF_INFO C, DDDDD_INFO PI
                 WHERE PI.PARTY_ID = C.PARTY_ID
                UNION ALL
                SELECT C.*, PI.CN_NAME
                  FROM H_CCCCCC_FFFF_INFO C, DDDDD_INFO PI
                 WHERE PI.PARTY_ID = C.PARTY_ID) E
         ORDER BY E.TRAN_TIME DESC, E.SYS_SEQ_ID DESC) EE
 WHERE ROWNUM <= 20

PLAN如下：

 

Plan hash value: 1127495981
 
------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |    20 | 75520 |       |   718K  (1)| 02:23:43 |
|*  1 |  COUNT STOPKEY          |                    |       |       |       |            |          |
|   2 |   VIEW                  |                    |   909K|  3276M|       |   718K  (1)| 02:23:43 |
|*  3 |    SORT ORDER BY STOPKEY|                    |   909K|  3276M|  7107M|   718K  (1)| 02:23:43 |
|   4 |     VIEW                |                    |   909K|  3276M|       |    27   (4)| 00:00:01 |
|   5 |      UNION-ALL          |                    |       |       |       |            |          |
|*  6 |       HASH JOIN         |                    |   909K|   677M|       |    14   (8)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| DDDDD_INFO         |   482 |  9158 |       |    11   (0)| 00:00:01 |
|   8 |        TABLE ACCESS FULL| CCCCCC_FFFF_INFO   |    21 |  8001 |       |     2   (0)| 00:00:01 |
|*  9 |       HASH JOIN         |                    |   117 | 88101 |       |    14   (8)| 00:00:01 |
|  10 |        TABLE ACCESS FULL| DDDDD_INFO         |   482 |  9158 |       |    11   (0)| 00:00:01 |
|  11 |        TABLE ACCESS FULL| H_CCCCCC_FFFF_INFO |   117 | 42939 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   6 - access("PI"."PARTY_ID"="C"."PARTY_ID")
   9 - access("PI"."PARTY_ID"="C"."PARTY_ID")

为了后面能够更直观的对比：我这里顺便把SQL真实的PLAN取出来。如下：

 

 

Plan hash value: 1127495981

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY          |                    |      1 |        |     20 |00:00:01.14 |   48469 |       |       |          |
|   2 |   VIEW                  |                    |      1 |  20679 |     20 |00:00:01.14 |   48469 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|                    |      1 |  20679 |     20 |00:00:01.14 |   48469 | 55296 | 55296 |49152  (0)|
|   4 |     VIEW                |                    |      1 |  20679 |   1457 |00:00:01.06 |   48469 |       |       |          |
|   5 |      UNION-ALL          |                    |      1 |        |   1457 |00:00:01.05 |   48469 |       |       |          |
|*  6 |       HASH JOIN         |                    |      1 |  20676 |   1457 |00:00:01.05 |   48407 |  1023K|  1023K| 1120K (0)|
|   7 |        TABLE ACCESS FULL| DDDDD_INFO         |      1 |     11 |     11 |00:00:00.01 |      46 |       |       |          |
|   8 |        TABLE ACCESS FULL| CCCCCC_FFFF_INFO   |      1 |    885 |    918K|00:00:00.92 |   48361 |       |       |          |
|*  9 |       HASH JOIN         |                    |      1 |      3 |      0 |00:00:00.01 |      62 |  1023K|  1023K| 1120K (0)|
|  10 |        TABLE ACCESS FULL| DDDDD_INFO         |      1 |     11 |     11 |00:00:00.01 |      46 |       |       |          |
|  11 |        TABLE ACCESS FULL| H_CCCCCC_FFFF_INFO |      1 |    117 |    117 |00:00:00.01 |      16 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   6 - access("PI"."PARTY_ID"="C"."PARTY_ID")
   9 - access("PI"."PARTY_ID"="C"."PARTY_ID")

逻辑读4.8w，响应时间是 3s

 

 

 

先总结一下分页语句的特点及优化技巧：

1.分页语句一般情况下服务于页面请求，对于响应时间要求高，一旦响应时间过长，客户不明所以的可能会反复点击。对数据库造成类似于“阻塞”的等待

2.分页语句里面必须含有order by，如果没有order by可以说分页是没有意义的

3.分页语句的主要消耗在order by，优化分页语句主要目的就是消除排序带来的性能问题

4.因为索引在创建的时候已经排序，所以可以通过在order by的列上面建索引的方式来抵消排序

5.建索引的时候升序或者降序 最好和order by后面的升序或者降序保持一致

6.如果SQL语句还有额外的过滤，建组合索引的时候应该把过滤列放在第一位

综上所述：分页语句优化的精髓就是通过建索引抵消掉排序带来的性能开销。

但是！！上述分页SQL是取两个表关联的结果union all，然后去order by这个结果集（所以plan里面占用到很大的Temp）

要消除这个结果集的order by，建索引显然是不行的。

针对这个语句 我提供了三个解决方案：

1、利用数据库对象优化：将这俩表CCCCCC_FFFF_INFO，H_CCCCCC_FFFF_INFO UNION ALL的结果集做成一个物化视图（视图需要维护成本大），在物化视图上面建索引去消除排序。

2、通过业务层处理优化：通过了解业务H_xxx表是历史表，CCCCCC_FFFF_INFO的当天的交易流水表。可以通过将CCCCCC_FFFF_INFO实时或者定时同步到历史表。原SQL中union all当天流水表的部分就可以去掉。这个就变成了我们可优化的分页框架

3、SQL层等价改写：将上述SQL先进行等价改写，改写成常规可优化的分页语句，再通过建索引优化。

 

通过讨论觉得第二种方法最妥当，然而需要改动的地方很多。没有上版口令无法实现，眼下解决问题可以先通过第三种方法临时解决，等到上版的时候再按照第二种方法处理！  于是SQL等价改写如下：

 

 

SELECT ROWNUM R,F.* 
   FROM (
SELECT E.*
   FROM (SELECT *
           FROM (SELECT /*+ INDEX(C,IDX_CCCCCC_FFFF_INFO) USE_NL(C,PI) LEADING(C) */C.*, PI.CN_NAME
                   FROM CCCCCC_FFFF_INFO C, DDDDD_INFO PI
                  WHERE PI.PARTY_ID = C.PARTY_ID
                  ORDER BY C.TRAN_TIME DESC,C.SYS_SEQ_ID DESC )
          WHERE ROWNUM <= 20
         UNION 
         SELECT *
           FROM (SELECT /*+ INDEX(C,IDX_H_CCCCCC_FFFF_INFO) USE_NL(C,PI) LEADING(C)*/C.*, PI.CN_NAME
                   FROM H_CCCCCC_FFFF_INFO C, DDDDD_INFO PI
                  WHERE PI.PARTY_ID = C.PARTY_ID
                  ORDER BY C.TRAN_TIME DESC,C.SYS_SEQ_ID DESC)
          WHERE ROWNUM <= 20) E  
          ORDER BY E.TRAN_TIME DESC, E.SYS_SEQ_ID DESC ) F
  WHERE ROWNUM <= 20

原SQL是把结果集排序取前20行数据，这里是对两个表分别排序取前20行(合并起来就是40行)，再对这40行取前20行 。注意一点：这个改写不是SQL语句上的等价，在业务上也不等价，只是在数据可行的情况下得到的结果等价！！！现在SQL变成了我们所熟悉的分页语句格式，可以利用传统的手段进行优化----建索引

1.分别在CCCCCC_FFFF_INFO和表H_CCCCCC_FFFF_INFO的(TRAN_TIME DESC,SYS_SEQ_ID DESC)列上面建立组合索引

  索引已经排过序，所以这时候只需要通过扫描索引（无论走那种访问路径都行）取20条数据后停止，然后回表20次即可

2.在DDDDD_INFO表(PARTY_ID,CN_NAME)列建组合索引，选择性高的列放在前面，这个表作为NL的被驱动表，

  连接列必须包含在索引里面

 

 

Plan hash value: 2692565397
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    20 | 75520 |       |  1612K  (1)| 05:22:30 |
|*  1 |  COUNT STOPKEY                       |                        |       |       |       |            |          |
|   2 |   VIEW                               |                        |    40 |   147K|       |  1612K  (1)| 05:22:30 |
|*  3 |    SORT ORDER BY STOPKEY             |                        |    40 |   147K|       |  1612K  (1)| 05:22:30 |
|   4 |     VIEW                             |                        |    40 |   147K|       |  1612K  (1)| 05:22:30 |
|   5 |      SORT UNIQUE                     |                        |    40 |   147K|  7106M|  1612K  (1)| 05:22:30 |
|   6 |       UNION-ALL                      |                        |       |       |       |            |          |
|*  7 |        COUNT STOPKEY                 |                        |       |       |       |            |          |
|   8 |         VIEW                         |                        |   909K|  3275M|       |   893K  (1)| 02:58:47 |
|   9 |          NESTED LOOPS                |                        |   909K|   346M|       |   815K  (1)| 02:43:12 |
|  10 |           TABLE ACCESS BY INDEX ROWID| CCCCCC_FFFF_INFO       |   917K|   333M|       |   814K  (1)| 02:42:49 |
|  11 |            INDEX FULL SCAN           | IDX_CCCCCC_FFFF_INFO   |   917K|       |       |  6322   (2)| 00:01:16 |
|* 12 |           INDEX RANGE SCAN           | IDX_DDDDD_INFO         |     1 |    19 |       |     1   (0)| 00:00:01 |
|* 13 |        COUNT STOPKEY                 |                        |       |       |       |            |          |
|  14 |         VIEW                         |                        |   117 |   431K|       |   107   (2)| 00:00:02 |
|  15 |          NESTED LOOPS                |                        |   117 | 45162 |       |   106   (1)| 00:00:02 |
|  16 |           TABLE ACCESS BY INDEX ROWID| H_CCCCCC_FFFF_INFO     |   117 | 42939 |       |   104   (0)| 00:00:02 |
|  17 |            INDEX FULL SCAN           | IDX_H_CCCCCC_FFFF_INFO |   117 |       |       |     1   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN           | IDX_DDDDD_INFO         |     1 |    19 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   7 - filter(ROWNUM<=20)
  12 - access("PI"."PARTY_ID"="C"."PARTY_ID")
  13 - filter(ROWNUM<=20)
  18 - access("PI"."PARTY_ID"="C"."PARTY_ID")

由上面的执行计划我们可以看出指标指标似乎都有所增大。

这里我需要解释一下：cost，time等不能作为优化的关注点，因为本身就是个估算值，没有参考价值

这里造成cost巨大的原因：看第10和11步骤INDEX FULL SCAN估算rows返回90w条，然后回表90w

其实对于分页语句只需要返回20条，然后回表20就行，所以一起看一下下面真实的执行计划：

 

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                       |                        |      1 |        |     20 |00:00:00.01 |     136 |       |       |          |
|   2 |   VIEW                               |                        |      1 |     23 |     20 |00:00:00.01 |     136 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY             |                        |      1 |     23 |     20 |00:00:00.01 |     136 | 18432 | 18432 |16384  (0)|
|   4 |     VIEW                             |                        |      1 |     23 |     20 |00:00:00.01 |     136 |       |       |          |
|   5 |      SORT UNIQUE                     |                        |      1 |     23 |     20 |00:00:00.01 |     136 | 18432 | 18432 |16384  (0)|
|   6 |       UNION-ALL                      |                        |      1 |        |     20 |00:00:00.01 |     136 |       |       |          |
|*  7 |        COUNT STOPKEY                 |                        |      1 |        |     20 |00:00:00.01 |      25 |       |       |          |
|   8 |         VIEW                         |                        |      1 |  20676 |     20 |00:00:00.01 |      25 |       |       |          |
|   9 |          NESTED LOOPS                |                        |      1 |  20676 |     20 |00:00:00.01 |      25 |       |       |          |
|  10 |           TABLE ACCESS BY INDEX ROWID| CCCCCC_FFFF_INFO       |      1 |    914K|     20 |00:00:00.01 |      23 |       |       |          |
|  11 |            INDEX FULL SCAN           | IDX_CCCCCC_FFFF_INFO   |      1 |    914K|     20 |00:00:00.01 |       3 |       |       |          |
|* 12 |           INDEX RANGE SCAN           | IDX_DDDDD_INFO         |     20 |      1 |     20 |00:00:00.01 |       2 |       |       |          |
|* 13 |        COUNT STOPKEY                 |                        |      1 |        |      0 |00:00:00.01 |     111 |       |       |          |
|  14 |         VIEW                         |                        |      1 |      3 |      0 |00:00:00.01 |     111 |       |       |          |
|  15 |          NESTED LOOPS                |                        |      1 |      3 |      0 |00:00:00.01 |     111 |       |       |          |
|  16 |           TABLE ACCESS BY INDEX ROWID| H_CCCCCC_FFFF_INFO     |      1 |    117 |    117 |00:00:00.01 |     109 |       |       |          |
|  17 |            INDEX FULL SCAN           | IDX_H_CCCCCC_FFFF_INFO |      1 |    117 |    117 |00:00:00.01 |       1 |       |       |          |
|* 18 |           INDEX RANGE SCAN           | IDX_DDDDD_INFO         |    117 |      1 |      0 |00:00:00.01 |       2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------

逻辑读从4.8w+降到136（其实正常的话逻辑读应该在50左右的。因为测试环境的“特殊原因”导致了这个情况）。响应时间由3s降到0.2S
如果仔细的人可能会看到第17步，这里分页函数应该返回20条数据。为什么会返回117条。这就是上面所说测试环境的“特殊原因”导致的。
这个疑问我这里不做解释，留给大家自己思考，结合上面的SQL和分页函数的特点和原理！


测试环境的这个特殊情况有时在生产环境也可能出现。如果优化分页函数感觉效果不理想甚至响应时间反而变长，就需要特别注意了！

 

 

 

 

 

 

24号信仰
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
特殊分页处理
zjcxc(邹建)的Blog - SQL Server
12-22 4440
原帖地址:http://community.csdn.net/Expert/topic/3662/3662135.xml?temp=.4289972--测试数据create table tb(ID int primary key,grade varchar(10),uptime datetime)insert tb select 1 ,a,2004-12-11union all sel
oracle rownum用法排序,oracle rownum对排序的影响
weixin_27134495的博客
04-05 913
select *from (select c.*, rownum rn from content c)where rn >= 1and rn <= 52. 但是如果, 加上order by addtime 排序则数据显示不正确select *from (select c.*, rownum rn from content c order by addtime)where rn >...
深入剖析-关于分页语句的性能优化
Enmotech的博客
12-20 368
分页语句是数据库开发和应用场景比较常见的需求，即按照特定的where条件进行过滤，然后在按照一个或者多个条件进行排序（如果不进行排序无法确执行时候无法返回相同的结果），最后取其中的前十行或者几十行。一般分页语句消耗资源的地方有两点：1、返回where条件过滤的结果集；2、是对这个结果集进行排序，如果表过大同时对返回的结果集排序势必导致性能严重下降，针对分页语句性能低下的原因。优化分页语句的核心思想
mysql分页sql语句优化,MySQL性能优化之分页查询优化
weixin_33622153的博客
03-17 307
MySQL分页查询原理MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。【阿里巴巴JAVA开发手册】// 查询样例，查询不同的条数效率完全不一样select * from employees limit 10000,10; ## 耗时：0...
mysql原生分页语句_mysql原生分页
weixin_34431283的博客
02-03 598
select * from table limit (pageNo-1)*pageSize, pageSize;一：分页需求：客户端通过传递start(页码)，limit(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：比如：...
MySQL大数据量分页SQL语句优化
u013810758的专栏
06-05 1452
分页程序原理很简单，这里就不多说了，本篇文章主要说的是在数据表记录量比较大的情况下，如何将分页SQL做到更优化，让MySQL执行的更快的方法。 一般的情况下，我们的分页SQL语句是这样的： 查看代码打印 1 SELECT * FROM table ORDER BY id LIMIT 1000,10; 以上SQL语句在原理上和
mysql uuid分页优化_MySQL性能优化之分页查询优化
weixin_28811007的博客
02-04 894
MySQL分页查询原理MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。【阿里巴巴JAVA开发手册】// 查询样例，查询不同的条数效率完全不一样select * from employees limit 10000,10; ## 耗时：0...
多数据分页列表sql优化
最新发布
qq_39303936的博客
10-26 507
子查询优化、延迟关联优化、限定id优化 sql 分页语句
Mysql百万级分页优化技巧
12-16
在进行分页优化时，一定要结合`EXPLAIN`命令来分析查询计划，了解MySQL如何执行查询，并根据分析结果调整查询语句。通过这些方法，可以在不影响用户体验的情况下，有效地处理百万级甚至千万级的分页查询。
对MySQL子查询的简单改写优化
12-15
7. **分析和调整查询计划**：使用EXPLAIN分析查询执行计划，找出潜在的瓶颈，并调整查询语句或优化器设置。 8. **使用物化视图**：如果特定的子查询频繁使用且结果不变，可以创建物化视图，以提高查询效率。 通过...
Oracle查询优化改写-技巧与案例
10-23
书中的改写技巧可能涉及到如何将游标改写为集合操作，或者使用 bulk collect 和 for all 语句提高效率。 8. **存储过程和函数优化**：优化存储过程和函数，包括减少调用次数，使用绑定变量，以及避免在过程中进行大...
Mybatis全面分页插件
09-01
然后，我们可以根据数据库方言（dialect）来构造分页SQL，例如在MySQL中，我们需要将原始的SELECT语句改写为包含LIMIT和OFFSET子句的分页SQL；在Oracle中，可能需要用到ROWNUM伪列来实现分页。 同时，为了获取总...
mysql数据库优化方案
04-16
- **先分页再 JOIN**：对于包含大量数据的多表 JOIN 查询，先进行分页可以显著提高性能。 10. **COUNT(*) 使用** - **避免无条件 COUNT(*)**：不带任何条件的 `COUNT(*)` 查询会导致全表扫描，且通常没有实际业务...
一次关于分页语句的改写优化
有教无类的专栏
11-21 1412
一网友在群内发来语句（语句内容已做处理） SELECT * FROM (SELECT /*这儿有d0的30多列*/ d0.* FROM d0 d0 WHERE d0.document_id IN (SELECT d1.document_id FROM d0
谓词推入的优化案例
热门推荐
24号信仰
08-07 1万+
案例很简单，主要就是长…… Sql文本： select count(*) from VIEW_SB_UUUUUUUUUUUUUUUUU where 1 = 1 and (KKKKID in (SELECT orgno FROM PP_OOO WHERE PARENTORGNO = '110000078...
一次'诡异'的执行SQL报错ORA-03113的问题处理
24号信仰
10-22 3960
银行某系统存储过程报错，重新调起仍报错，而前几天这个一直正常。通过应用日志定位到报错信息， ORA-03113: 通信通道的文件结尾 进程 ID: 36503726 会话 ID: 586 序列号: 65345 遇到这种错误，习惯性在网上找答案。然而网上那些关于这个经典错误的经典案例无一能和我的错误匹配 这时候我会习惯性的"看看SQL" 从存储过程里面定位到这个导致报错的SQL，单独拿出来跑...
用MERGE改写UPDATE的优化
24号信仰
05-02 3381
某系统负责人反应一个新上线的存储过程跑了 10个小时没出结果，中间卡在了一个update语句上面： ETL日志看到这个流程2017-04-11 23:59:43开始跑 ，到第二天10点还没有跑完 开发告诉我他们存储过程里面新上线了一条update语句，在测试环境测试可以跑出结果：sql如下 UPDATE UUU_UIU_ITTTTTTTTT S SET S.ASSET_LOS...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值