Oracle,SQl,MySql实现分页查询

通过SQL 查询分析器，显示比较：我的结论是: 分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句 分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用 SELECT u.username FROM `user` u join contact_info c on (u.id=c.user_id) and c.address='123'; ### Oracle、SQL、MySQL 实现分页查询方法详解 在数据库操作中，分页查询是一项非常重要的技术，尤其是在处理大量数据时。本文将基于提供的文件信息，深入探讨三种不同的分页查询方法及其在Oracle、SQL Server（这里用SQL代替）、MySQL中的实现方式，并对它们的性能进行对比分析。 #### 分页方案一：利用Not In和SELECT TOP分页 **基本原理** 此方法的基本思路是首先获取前一页的数据集，然后从总数据集中排除这些数据，从而达到分页的效果。这种方法需要拼接SQL语句。 **Oracle实现** Oracle 不支持 `SELECT TOP` 语法，因此需要采用其他方式来实现类似的功能。可以使用 `ROWNUM` 来限制返回的行数。 ```sql SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM TABLE_NAME ) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 ``` **SQL Server实现** SQL Server 支持 `SELECT TOP`，因此可以直接使用。 ```sql SELECT TOP 20 * FROM TestTable WHERE ID NOT IN (SELECT TOP 20 ID FROM TestTable ORDER BY ID) ORDER BY ID ``` **MySQL实现** MySQL 也支持 `LIMIT` 和 `OFFSET`，可以通过这种方式实现分页。 ```sql SELECT * FROM TestTable WHERE ID NOT IN (SELECT ID FROM TestTable LIMIT 20 OFFSET 20) ORDER BY ID ``` **性能分析** 这种方法在数据量较小的情况下表现尚可，但随着数据量的增长，性能会逐渐下降。这是因为每次查询都需要执行两次，一次获取前一页的数据，另一次则是获取当前页的数据，这会增加服务器负担。 #### 分页方案二：利用ID大于某值和SELECT TOP分页 **基本原理** 这种方法的核心在于根据ID来定位数据的位置，只需要执行一次查询即可获取所需的数据，因此效率较高。 **Oracle实现** Oracle 中同样可以使用 `ROWNUM` 结合 `ORDER BY` 来实现。 ```sql SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM TABLE_NAME ORDER BY ID ) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 ``` **SQL Server实现** SQL Server 中直接使用 `SELECT TOP` 配合 `ORDER BY` 即可。 ```sql SELECT TOP 20 * FROM TestTable WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP 20 ID FROM TestTable ORDER BY ID) AS T) ORDER BY ID ``` **MySQL实现** MySQL 可以直接使用 `LIMIT` 和 `OFFSET`。 ```sql SELECT * FROM TestTable WHERE ID > (SELECT MAX(ID) FROM (SELECT ID FROM TestTable LIMIT 20 OFFSET 20) AS T) ORDER BY ID ``` **性能分析** 这种方法的性能较好，因为它只需要执行一次查询即可完成分页操作。但由于需要计算最大ID值，如果表中数据量很大，这个计算可能会成为瓶颈。 #### 分页方案三：利用SQL的游标存储过程分页 **基本原理** 这种方法是通过创建一个存储过程来实现分页，通常涉及到游标的使用。虽然通用性好，但效率较低。 **Oracle实现** 可以创建一个存储过程来实现分页查询。 ```sql CREATE OR REPLACE PROCEDURE pagenation ( @sqlstr VARCHAR2, @currentpage NUMBER, @pagesize NUMBER ) IS @P1 NUMBER; @rowcount NUMBER; BEGIN EXECUTE IMMEDIATE 'OPEN @P1 FOR ' || @sqlstr INTO @P1; SELECT CEIL(@rowcount / @pagesize) AS total_pages FROM DUAL; SET @currentpage = (@currentpage - 1) * @pagesize + 1; FETCH @P1 INTO @currentpage, @pagesize; CLOSE @P1; END; ``` **SQL Server实现** SQL Server 中也可以通过存储过程来实现。 ```sql CREATE PROCEDURE dbo.pagenation @sqlstr NVARCHAR(4000), @currentpage INT, @pagesize INT AS BEGIN SET NOCOUNT ON; DECLARE @P1 INT, -- @P1 is the cursor ID @rowcount INT; EXEC sp_cursoropen @P1 OUTPUT, @sqlstr, @scroll_opt = 1, @cc_opt = 1, @rowcount = @rowcount OUTPUT; SELECT CEILING(1.0 * @rowcount / @pagesize) AS total_pages; SET @currentpage = (@currentpage - 1) * @pagesize + 1; EXEC sp_cursorfetch @P1, 16, @currentpage, @pagesize; EXEC sp_cursorclose @P1; SET NOCOUNT OFF; END ``` **MySQL实现** MySQL 中同样可以通过存储过程来实现。 ```sql DELIMITER // CREATE PROCEDURE pagenation(IN @sqlstr VARCHAR(4000), IN @currentpage INT, IN @pagesize INT) BEGIN DECLARE @P1 INT; DECLARE @rowcount INT; OPEN CURSOR FOR PREPARE stmt FROM @sqlstr; FETCH CURSOR stmt INTO @rowcount; SELECT CEILING(@rowcount / @pagesize) AS total_pages; SET @currentpage = (@currentpage - 1) * @pagesize + 1; FETCH CURSOR stmt INTO @currentpage, @pagesize; CLOSE CURSOR; END // DELIMITER ; ``` **性能分析** 虽然这种方法的通用性较好，但由于涉及到了游标的使用，导致性能较差。特别是当数据量较大时，性能问题更加明显。 ### 总结 不同的分页查询方法各有优缺点： - **分页方案二** 的性能最好，适用于大部分场景，但需要注意数据量过大时可能存在的瓶颈问题。 - **分页方案一** 的性能次之，适用于数据量不是特别大的情况。 - **分页方案三** 虽然通用性好，但在性能方面相对较差。 实际应用中应根据具体情况选择合适的分页方法，以确保系统的高效运行。