mysql、mssql及oracle分页查询方法详解
<p>本文实例讲述了mysql、mssql及oracle分页查询方法。分享给大家供大家参考。具体分析如下：</p>
<p>
分页查询在web开发中是最常见的一种技术，最近在通过查资料，有一点自己的心得</p>
<p>
<strong>一、mysql中的分页查询</strong></p>
<p>
注：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_707282">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">m=(pageNum-1)*pageSize;n= pageSize;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
pageNum是要查询的页码，pageSize是每次查询的数据量，</p>
<p>
方法一：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_528870">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">id limit m, n;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
该语句的意思为，查询m+n条记录，去掉前m条，返回后n条记录。无疑该查询能够实现分页功能，但是如果m的值越大，查询的性能会越低（越后面的页数，查询性能越低），因为MySQL同样需要扫描过m+n条记录。</p>
<p>
方法二：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_461631">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql keyword">where</code> <code class="sql plain">id > #max_id# </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">id limit n;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
该查询每次会返回n条记录，却无需像方式1扫描过m条记录，在大数据量的分页情况下，性能可以明显好于方式1，但该分页查询必须要每次查询时拿到上一次查询（上一页）的一个最大id（或最小id）。该查询的问题就在于，我们有时没有办法拿到上一次查询（上一页）的最大id（或最小id），比如当前在第3页，需要查询第5页的数据，该查询方法便爱莫能助了。</p>
<p>
方法三：</p>
<p>
为了避免能够实现方式二不能实现的查询，就同样需要使用到limit m, n子句，为了性能，就需要将m的值尽力的小，比如当前在第3页，需要查询第5页，每页10条数据，当前第3页的最大id为#max_id#：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_333539">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql keyword">where</code> <code class="sql plain">id > #max_id# </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">id limit 20,10;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
其实该查询方式是部分解决了方式二的问题，但如果当前在第2页，需要查询第100页或1000页，性能仍然会较差。</p>
<p>
方法四：</p>
<div class="codetitle">
<span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code25436">
select * from table as a inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id;</div>
<br>
该查询同方式一 一样，m的值可能很大，但由于内部的子查询只扫描了字段id，而不是整张表，所以性能要强于方式一查询，并且该查询能够解决方式二和方式三不能解决的问题。
<p>
</p>
<p>
方式五：</p>
<div class="codetitle">
<span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code17150">
select * from table where id > (select id from table order by id limit m, 1) limit n;</div>
<p>
</p>
<p>
该查询方式同方式四，同样通过子查询扫描字段id，效果同方式四。至于性能的话，方式五的性能会略好于方式四，因为方式5不需要在进行表的关联，而是一个简单的比较。</p>
<p>
<strong>二、Sql Server分页查询</strong></p>
<p>
方法一：</p>
<p>
适用于 SQL Server 2000/2005</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_568000">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 *</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">FROM</code> <code class="sql plain">table1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql keyword">WHERE</code> <code class="sql plain">id </code><code class="sql color1">NOT</code> <code class="sql color1">IN</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小*(页数-1) id </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
方法二：</p>
<p>
适用于 SQL Server 2000/2005</p>
<p>
--顺序写法：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_94534">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 *</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">table1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">id >=</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">SELECT</code> <code class="sql color2">ISNULL</code><code class="sql plain">(</code><code class="sql keyword">MAX</code><code class="sql plain">(id),0) </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">FROM</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">(</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小*(页数-1)+1 id </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">) A</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
--降序写法：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_975533">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 *</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">table1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">id <=</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">SELECT</code> <code class="sql color2">ISNULL</code><code class="sql plain">(</code><code class="sql keyword">MIN</code><code class="sql plain">(id),0) </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">FROM</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">(</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小*(页数-1)+1 id </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id </code><code class="sql keyword">Desc</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">) A</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id </code><code class="sql keyword">Desc</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
方法三：</p>
<p>
适用于 SQL Server 2005</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_309113">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 * </code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql plain">ROW_NUMBER() OVER (</code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id) </code><code class="sql keyword">AS</code> <code class="sql plain">RowNumber,* </code><code class="sql keyword">FROM</code> <code class="sql plain">table1</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">) A</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">WHERE</code> <code class="sql plain">RowNumber > 页大小*(页数-1)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。</p>
<p>
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。<br>
建议优化的时候，加上主键和索引，查询效率会提高。</p>
<p>
通过SQL 查询分析器，显示比较：我的结论是:</p>
<p>
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句<br>
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句<br>
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用</p>
<p>
<strong>三、oracle分页查询</strong></p>
<p>
方法一：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_763541">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">( </code><code class="sql keyword">SELECT</code> <code class="sql plain">A.*, ROWNUM RN </code><code class="sql keyword">FROM</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">tab) A </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql keyword">WHERE</code> <code class="sql plain">ROWNUM <= 40 ) </code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">WHERE</code> <code class="sql plain">RN >= 21;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这个分页比下面的执行时间少，效率高。当数据量较大时oracle会自动优化！</p>
<p>
方法二：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_207614">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">c.*,rownum rn </code><code class="sql keyword">from</code> <code class="sql plain">tab c) </code><code class="sql keyword">where</code> <code class="sql plain">rn </code><code class="sql color1">between</code> <code class="sql plain">21 </code><code class="sql color1">and</code> <code class="sql plain">40</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。</p>
<p>
这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。</p>
<p>
对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。</p>
<p>
而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层</p>
<p>
（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。</p>
<p>
上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。</p>
<p>
希望本文所述对大家的数据库程序设计有所帮助。</p>
頁:
[1]