不同数据库分页查询的实现

分页查询是数据库查询中经常用到的一项操作，对查询出来的结果进行分页查询可以方便浏览。那么Oracle、SQL Server、MySQL是如何实现查询的呢？本文我们就来介绍这一部分内容。

1. SQL Server 分页查询

实例：一张表 tbl_FlightsDetail，有300多W记录，主键 FlightsDetailID(Guid)，要求按照FlightsDetailID排序 取 3000001 到3000010 之间的10条记录，也是百万级。

方法1 定位法 (利用ID大于多少)

select top 10 * from tbl_FlightsDetail where FlightsDetailID>(
       select max(FlightsDetailID) from ( 
              select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
       ) as t
) order by FlightsDetailID

先查出 top 300000，再聚合取这个集合中最大的Id1，再过滤 id大于id1的集合（上图中使用到索引），再取top 10 条。

方法2 (利用Not In)

select top 10* from tbl_FlightsDetail where FlightsDetailID not in (
       select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
) order by FlightsDetailID

和方法一类似，只是过滤where条件不一样，这里用到的是not in，上图中没有用到索引，耗时8秒。如果 FlightsDetailID不是索引的话，方法1和该方法将差不多。

其他的查询方法以及介绍参照：Sql Server 数据分页

2. Oracle数据库分页查询

从数据库表中第M条记录开始检索N条记录

SELECT *  
 
FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2  
 
where t2.r >= M 

例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：

SELECT *  
 
FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2  
 
Where t2.R >= 10 

参考文档： mysql、sql server、oracle数据库分页查询及分析（操作手册）

ORACLE分页查询SQL语法——最高效的分页

oracle分页查询的效率分析

3. MySQL分页查询

方法1: 直接使用数据库提供的SQL语句
语句样式: SELECT * FROM 表名称 LIMIT M,N；（从M位置处取N条数据）
适应场景: 适用于数据量较少的情况(元组百/千级)
原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

方法2: 建立主键或唯一索引, 利用索引(假设每页10条)
语句样式:  SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M；（从pageNum*10处取M条数据）
适应场景: 适用于数据量多的情况(元组数上万)
原因: 索引扫描,速度会很快. 有朋友提出: 因为数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况，只能方法3

方法3: 基于索引再排序
语句样式: SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M；
适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一所以,使得ORDER BY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)
原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC,期待...).

方法4: （第一个问号表示pageNum，第二个？表示每页元组数）
语句样式:  PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M
适应场景: 大数据量
原因: 索引扫描,速度会很快. prepare语句又比一般的查询语句快一点。

方法5: 利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描

比如: 读第1000到1019行元组(pk是主键/唯一键).

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20；

方法6: 利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组. 道理同方法5

如(id是主键/唯一键,蓝色字体时变量):

利用子查询示例:

SELECT * FROM your_table WHERE id <= 
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc LIMIT $pagesize

利用连接示例:

SELECT * FROM your_table AS t1 
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

 

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。

对limit分页问题的性能优化方法

MySQL可以利用表的覆盖索引来加速分页查询。

利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒！

另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id；（测试出来查不到相应的ID，而且时间也不快，查询索引ID的查询语句中是否需要加一个ORDER BY ID ASC）
查询时间也很短！

参考文章：MySQL大数据量分页查询方法及其优化