分页查询的性能

最新推荐文章于 2024-01-07 02:07:08 发布
最新推荐文章于 2024-01-07 02:07:08 发布
阅读量168 收藏
点赞数
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4  

分页查询中使用order by子句中的索引和nested loop关联方式，可用COUNT STOPKEY只检索出每页的记录数据，不需要检索所有的记录后再排序，即省去SORT ORDER BY STOPKEY操作，大大提高执行效率。

 

 

1.                分页查询一般是有order by子句的如以下的语句：

SELECT *

  FROM (SELECT t1.*, rownum rnum

          FROM (SELECT --/*+ ordered use_hash(s c) use_hash(s w) use_hash(c m) index(s sample_draft_gmid) */--index(s SAMPLE_D_MDF_STS_CID_IND)

                 s.ROWID AS srid, c.ROWID AS crid

                  FROM company_draft c, member m, sample_draft s --,window_product w

                 WHERE s.company_id = c.id

                   AND m.company_id = c.id

                   AND (c.IS_VALIDATE = 'enabled' OR

                       (c.IS_VALIDATE = 'disabled' AND

                       c.charge_type = 'tbpaid'))

                -- and      s.id = w.product_id

                 ORDER BY s.gmt_modified DESC, s.id) t1

         WHERE ROWNUM <= 30)

 WHERE rnum >= 20

 

 

2.                可在order by 字段上建立相应的索引：

create index sample_draft_gmid on sample_draft (gmt_modified DESC,id) tablespace olps_data;

 

 

3.                3. 此时运行速度很快，因为执行计划中有COUNT STOPKEY  ，表明优化器通过扫描索引SAMPLE_DRAFT_GMID，因为在索引中已经对order by子句的字段进行了排序，则在|   3 |  所生成的 VIEW中，只存在30条符合条件的记录 ，对于其它剩下的记录不再进行其中的关联操作。这样在生成view时，大大减少了关联的数据量

 

----------------------------------------------------------------------------------------

| Id  | Operation                        |  Name               | Rows  | Bytes | Cost  |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                     | 30000 |   791K|  4414K|

|*  1 |  VIEW                            |                     | 30000 |   791K|  4414K|

|*  2 |   COUNT STOPKEY                  |                     |       |       |       |

|   3 |    VIEW                          |                     |  2196K|    29M|  4414K|

|   4 |     NESTED LOOPS                 |                     |  2196K|   117M|  4393K|

|   5 |      NESTED LOOPS                |                     |  2196K|   113M|  2197K|

|   6 |       TABLE ACCESS BY INDEX ROWID| SAMPLE_DRAFT        |  2196K|    58M|   826 |

|   7 |        INDEX FULL SCAN           | SAMPLE_DRAFT_GMID   |  2196K|       |    26 |

|*  8 |       TABLE ACCESS BY INDEX ROWID| COMPANY_DRAFT       |     1 |    26 |     1 |

|*  9 |        INDEX RANGE SCAN          | COMPANY_DRAFT_PK    |     2 |       |       |

|* 10 |      INDEX RANGE SCAN            | MEMBER_COMPANY_IND  |     1 |     2 |     1 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("from$_subquery$_001"."RNUM">=20000)

   2 - filter(ROWNUM<=30000)

   8 - filter("C"."IS_VALIDATE"='enabled' OR "C"."IS_VALIDATE"='disabled' AND

              "C"."CHARGE_TYPE"='tbpaid')

   9 - access("S"."COMPANY_ID"="C"."ID")

  10 - access("M"."COMPANY_ID"="C"."ID")

       filter("M"."COMPANY_ID" IS NOT NULL)

 

Note: cpu costing is off

 

4.                而以下语句却不同：没有用到索引SAMPLE_DRAFT_GMID，而只是用到了索引SAMPLE_DRAFT_CID_IND，此索引建立在字段GMT_MODIFIED, STATUS, COMPANY_ID上的，与order by的排序无关。此时需将所有数据关联后排序，使用了SORT ORDER BY STOPKEY后再使用COUNT STOPKEY。

SELECT *

  FROM (SELECT t1.*, rownum rnum

          FROM (SELECT /*+ ordered */

          --/*+ ordered use_hash(s c) use_hash(s w) use_hash(c m) index(s sample_draft_gmid) */--index(s SAMPLE_D_MDF_STS_CID_IND)

                 s.ROWID AS srid, c.ROWID AS crid

                  FROM company_draft c, member m, sample_draft s --,window_product w

                 WHERE s.company_id = c.id

                   AND m.company_id = c.id

                   AND (c.IS_VALIDATE = 'enabled' OR

                       (c.IS_VALIDATE = 'disabled' AND

                       c.charge_type = 'tbpaid'))

                -- and      s.id = w.product_id

                 ORDER BY s.gmt_modified DESC, s.id) t1

         WHERE ROWNUM <= 30)

 WHERE rnum >= 20

 

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------

| Id  | Operation                       |  Name                 | Rows  | Bytes |TempSpc| Cost  |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |                       |    30 |   810 |       |   471K|

|*  1 |  VIEW                           |                       |    30 |   810 |       |   471K|

|*  2 |   COUNT STOPKEY                 |                       |       |       |       |       |

|   3 |    VIEW                         |                       |  2196K|    29M|       |   471K|

|*  4 |     SORT ORDER BY STOPKEY       |                       |  2196K|   117M|   286M|   471K|

|   5 |      TABLE ACCESS BY INDEX ROWID| SAMPLE_DRAFT          |   117 |  3276 |       |    23 |

|   6 |       NESTED LOOPS              |                       |  2196K|   117M|       |   451K|

|   7 |        NESTED LOOPS             |                       | 18785 |   513K|       | 19026 |

|*  8 |         TABLE ACCESS FULL       | COMPANY_DRAFT         | 18783 |   476K|       |   243 |

|*  9 |         INDEX RANGE SCAN        | MEMBER_COMPANY_IND    |     1 |     2 |       |     1 |

|* 10 |        INDEX RANGE SCAN         | SAMPLE_DRAFT_CID_IND  |  1551 |       |       |     2 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("from$_subquery$_001"."RNUM">=20)

   2 - filter(ROWNUM<=30)

   4 - filter(ROWNUM<=30)

   8 - filter("C"."IS_VALIDATE"='enabled' OR "C"."IS_VALIDATE"='disabled' AND

              "C"."CHARGE_TYPE"='tbpaid')

   9 - access("M"."COMPANY_ID"="C"."ID")

       filter("M"."COMPANY_ID" IS NOT NULL)

  10 - access("S"."COMPANY_ID"="C"."ID")

 

Note: cpu costing is off

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

     730389  consistent gets

       6309  physical reads

          0  redo size

        926  bytes sent via SQL*Net to client

        504  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         11  rows processed

 

5. 经测试 ，使用hash join无法达到以上效果，即使强制使用索引，且SAMPLE_DRAFT表为driver 表，。

 

SELECT *

  FROM (SELECT t1.*, rownum rnum

          FROM (SELECT /*+ ordered  use_hash(s c) index(s sample_draft_gmid)  */

          --/*+ ordered use_hash(s c) use_hash(s w) use_hash(c m) index(s sample_draft_gmid) */--index(s SAMPLE_D_MDF_STS_CID_IND)

                 s.ROWID AS srid, c.ROWID AS crid

                  FROM sample_draft s,company_draft c--, member m -- ,window_product w

                 WHERE s.company_id = c.id

                   --AND m.company_id = c.id

                   AND (c.IS_VALIDATE = 'enabled' OR

                       (c.IS_VALIDATE = 'disabled' AND

                       c.charge_type = 'tbpaid'))

                 --and      s.id = w.product_id

                 ORDER BY s.gmt_modified DESC, s.id) t1

         WHERE ROWNUM <= 30)

 WHERE rnum >= 20

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                        |  Name              | Rows  | Bytes |TempSpc| Cost  |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                    |    30 |   810 |       | 22193 |

|*  1 |  VIEW                            |                    |    30 |   810 |       | 22193 |

|*  2 |   COUNT STOPKEY                  |                    |       |       |       |       |

|   3 |    VIEW                          |                    |  2196K|    29M|       | 22193 |

|*  4 |     SORT ORDER BY STOPKEY        |                    |  2196K|   113M|   286M| 22193 |

|*  5 |      HASH JOIN                   |                    |  2196K|   113M|    83M|  2415 |

|   6 |       TABLE ACCESS BY INDEX ROWID| SAMPLE_DRAFT       |  2196K|    58M|       |   826 |

|   7 |        INDEX FULL SCAN           | SAMPLE_DRAFT_GMID  |  2196K|       |       |    26 |

|*  8 |       TABLE ACCESS FULL          | COMPANY_DRAFT      | 18783 |   476K|       |   243 |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("from$_subquery$_001"."RNUM">=20)

   2 - filter(ROWNUM<=30)

   4 - filter(ROWNUM<=30)

   5 - access("S"."COMPANY_ID"="C"."ID")

   8 - filter("C"."IS_VALIDATE"='enabled' OR "C"."IS_VALIDATE"='disabled' AND

              "C"."CHARGE_TYPE"='tbpaid')

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

    1259883  consistent gets

      11687  physical reads

          0  redo size

        926  bytes sent via SQL*Net to client

        504  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         11  rows processed

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30821/viewspace-610753/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30821/viewspace-610753/

ciwannian7873
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
HBase分页查询性能测试ppt
07-24
里面包括了特定场合下，HBase随机查询的一些性能指标参考以及调优策略
mysql千万级数据分页查询性能优化
01-19
当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条）， 如下： select * from order limit 10, 20 0.016秒 select * from order limit 100, 20 0.016秒 ...
分页查询性能比较
coo3678的博客
06-05 127
今天做了分页查询的性能测试,发现写一个好的分页查询语句,性能查询的差别是巨大! 建立测试表 create table test as select * from dba_objects 执行如下分页查询语...
分页查询性能优化
六神的博客
12-20 387
最常见的分页采用的是skip+limit这种组合方式，这种方式对付小数据倒也可以，但是对付上几百上千万的大数据，只能力不从心，skip如果跳过大量的数据会很慢，并且会越查越慢，针对这一情况，可以通过条件查询+排序+限制返回记录，即 边查询，边排序，排序之后，抽取上一页中的最后一条记录，作为当前分页的查询条件，从而避免了skip效率低下的问题。  ...
MySQL分页查询的性能优化
坤三岁的博客
06-07 895
MySQL limit分页查询的性能优化 Mysql的分页查询十分简单，但是当数据量大的时候一般的分页就吃不消了。 传统分页查询：SELECT c1,c2,cn… FROM table LIMIT n,m MySQL的limit工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。 推荐分页查询方法： 1、尽量给出查询的大致范围 SELECT c1,c2,cn...FROM table WHERE id>=20000LIMIT10...
Oracle分页查询性能优化代码详解
01-19
对于数据库中表的数据的 Web 显示，如果没有展示顺序的需要，而且因为满足条件的记录如此之多，就不得不对数据进行分页处理。常常用户并不是对所有数据都感兴趣的，或者大部分情况下，他们只看前几页。 通常有以下...
[数据库]提升分页查询性能
大概关于
07-10 513
问题描述 分页是数据库常见操作，也是很简单的操作： select * from Goods limit 100, 10; 如上，是查询第 100～110 条数据。 而数据量非常大时，这种查询语句就…… mysql> select * from Goods limit 230000, 3; +--------+-------+--------------+---------------------+ | id | title | subtitle | createdAt
数据库分页优化：提升查询性能
最新发布
禅与计算机程序设计艺术
01-07 409
1.背景介绍 数据库分页优化是一项重要的数据库性能优化技术，它主要用于提升查询性能。在现代数据库系统中，数据量越来越大，查询性能对于系统的运行尤为关键。因此，了解和掌握数据库分页优化技术至关重要。 在这篇文章中，我们将从以下几个方面进行阐述： 背景介绍 核心概念与联系 核心算法原理和具体操作步骤以及数学模型公式详细讲解 具体代码实例和详细解释说明 未来发展趋势与挑战 附录常见问题与解答 ...
实用MySQL分页查询的性能优化
追梦1819
05-11 182
作者：追梦1819 原文：https://www.cnblogs.com/yanfei1819/p/14754210.html 版权声明：本文为博主原创文章，转载请附上博文链接！ 传统分页查询： # 第一个参数n指定第一个返回记录行的偏移量，第二个参数m指定返回记录行的最大数目。 SELECT c1,c2,cn… FROM table LIMIT n,m; MySQL 的 limit 工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。.
分页查询limit　性能优化
mqsyoung
05-03 1530
分页查询在系统中是必不可少的sql 语句：　select * from tabel limit 偏移量　, 条数 大部分人写分页查询都是简单的使用limit 如： select * from tabel limit 10 , 20 但是如果表示百万级数据呢？ 　 偏移量　＝　　（页数－１）　＊　条数 现在测试一下54 万的表： limit 0 ,10 – 0.007　秒 ...
MySQL分页查询效率
zhangguan96的博客
09-15 743
MySQL分页查询 mysql的分页查询需要用到关键字 limit ，limit其实是mysql给的一个函数，就像是count（*）求和一样的函数，可以传入参数使用。 LIMIT 可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1) 使用limit进行分页查询 第一种情况，只传一个参数： SELECT * FROM
Sql 分页查询效率分析
xuewuzhijing10的专栏
11-05 978
选取了2中效率比较高的方式比较效率：row_Number() 、offset fetch 表test中有1000条数据，2个字段：field1(int)，field2(nvarchar) --1000条数据，查询500次第1-10行，39s --1000条数据，查询500次第500-550行，87s --1000条数据，查询500次第150-160行，88s DECLARE @uId
oracle分页查询的效率分析
山鹰的专栏
06-26 4151
ROWNUM 可能都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。 ROWNUM是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。 ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，那么ROWNUM会递加，下一条记录的R
【转载】分页查询高性能实现
11-11 202
一、【转载】你知道MySQL的Limit有性能问题吗 原文链接：你知道MySQL的Limit有性能问题吗_lonely_bin的博客-CSDN博客_limit性能问题 MySQL的分页查询通常通过limit来实现。 MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。 为了与PostgreSQL兼容，limit也支持limit # offset #。 问题： 对于小的偏移量，.
分页查询优化
weixin_30677475的博客
08-10 567
一、背景 直接从数据库查询所有数据，耗时、耗内存，改为分页查询部分数据 二、常规查询 1. mysql: limit从第m条数据开始，查询n条数据 select * from table limit m,n 2. sql server: top+子查询 3. 原理：从数据库的第一条记录开始扫描 越往后，即m越大，查询越慢 查询的数据越多，即n越大，查询越慢 ...
mysql分页查询和提高效率
qq_39027486的博客
08-31 1434
mysql分页查询和提高效率 1、在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 2、LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数...
【基础知识思考整理 】Mysql高效率的分页查询
aganlengzi的专栏
10-17 2306
在工程实践中，数据库表中的数据量一般不超过 100W行。当我们想要查询数据表中满足条件的第 n个100 行数据的时候，怎样的查询方法才能保证较高的性能呢？首先想到的，一般分页都是用 “limit 起始, 行数” 这样的方式。 上面的含义是：即使我们只要取得从第 2个开始的3 行数据，也要扫描前面的 5行并且丢弃前面的2行最终返回后面的 3行。 可以想象，当起始非常大而行数非常小的时候，我们的
clickhouse 分页查询性能
05-12
ClickHouse 是一款面向列式存储的 OLAP 数据库，因此在处理大数据量的分页查询时，性能非常出色。以下几个方面可以帮助提高 ClickHouse 的分页查询性能： 1. 使用 LIMIT 和 OFFSET：ClickHouse 支持使用 LIMIT 和 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值