MSSqlServer 2012 offset and fetch实现分页方法分析() 有需要的朋友可参考一下
准备工作,建立测试表:Article_Detail,主要是用来存放一些文章信息,测试的时间,都是从网易上面转载的新闻,同时,测试表数据字段类型是比较均匀的,为了更好的测试,表结构如下图:
内容:
数据量:129,991 条记录
语法分析
1. NTILE() 的分页方法
NTILE() 方法可以用来分页,但是应用场景十分的狭窄,并且性能差劲,和 Row_Number() 与 offset fetch 分页比起来没有任何优势,也只有在只读表上面分页的话,还是比较合适的;虽然不好用,但是还能来分页的,所以只简单的介绍下。
语法:
NTILE (integer_expression) <span style="color: #0000ff">OVER</span> ( <span style="color: #ff0000">[</span><span style="color: #ff0000"> <partition_by_clause> </partition_by_clause></span><span style="color: #ff0000">]</span> <span style="color: #808080"> order_by_clause <span style="color: #808080">></span> )</span>
将有序分区中的行分发到指定数目的组中。 各个组有编号,编号从一开始。 对于每一个行,NTILE 将返回此行所属的组的编号。
测试中用到的 Sql 语句 :
试中用到的 Sql 语句 :
代码如下 | 复制代码 |
set statistics time on set statistics io on set statistics profile on; with #pager as ( select ID,Title,NTILE(8666) OVER(Order By ID) as pageid from Article_Detail ) select ID,Title from #pager where pageid=50 |
set statistics profile on;
按 Ctrl+C 复制代码
其中上述数字中的 8666 是根据 RowCount / Pagesize 计算出来的,不过多介绍,可以自行参考 MSDN的
2. ROW_NUMBER() 的分页方法
在 Sql Server 2000 之后的版本中,ROW_NUMBER() 这种分页方式一直都是很不错的,比起之前的游标分页,性能好了很多,因为 ROW_NUMBER() 并不会引起全表扫表,但是,语法比较复杂,并且,随着页码的增加,性能也越来越差。
语法 :
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
测试中用到的 Sql 语句:
代码如下 | 复制代码 |
dbcc freeproccache dbcc dropcleanbuffers set statistics time on set statistics io on set statistics profile on; with #pager as ( select ID,Title,ROW_NUMBER() OVER(Order By ID) as rowid from Article_Detail ) select ID,Title from #pager where rowid between (15 * (50-1)+1) and 15 * 50 set statistics profile off; 3. Offset and Fetch 的分页方法 |
语法:
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
从语法可以看出来 两个方法 后面不但能接 intege 类型的参数,还能接 表达式的,比如 1*2 +3 之类的,同时, Row 或者 Rows 是不区分大小写和单复数的哦
在看测试用的 Sql 语句,真的是简洁的不能再简洁了,看两遍都能记住的语法,分页可以如此的简洁:
代码如下 | 复制代码 |
dbcc freeproccache dbcc dropcleanbuffers set statistics time on set statistics io on set statistics profile on; select ID,Title from Article_Detail order by id OFFSET (15 * (50-1)) ROW FETCH NEXT 15 rows only set statistics profile off; |
一句就搞定!
性能比较
1. NTILE() 的执行计划
从执行计划中,就可以看出来,进行了一次全表扫表,两次 Nested Loops ,还有无数其他运算,就一次全表扫表,就知道性能之差了
2. ROW_NUMBER() 的执行计划
从执行计划中可以看出来, 聚集索引扫描占用了100% 的资源,但是通过 EstimateRows = 100 和 Rows = 750 可以看出来,并没有进行全表扫描,并且IO 操作很小,所以性能还是很不错的
3. Offset and Fetch 的 执行计划
执行计划只有3行,并且占用资源 100% 的IO 操作 ,EstimateRows = 100 和 Rows = 750 是和 ROW_NUMBER() 完全一样的,但是其他的一些操作却少了很多,也就是说,并没有全表扫描,并降低了CPU 的消耗。
综合比较:
在 Sql Server 2012 里面,分页方法中,Offset and Fetch 同 ROW_NUMBER() 比较起来,无论是性能还是语法,都是有优势的。
但是性能方面,优势并不是太大,两者 的 IO 消耗完全相同,只是 在 CPU 方面,Offset and Fetch 方面要好一些,但是不明显。如果对于一个 每秒都要处理成千上万条的分页Sql语句的DB 来说,Offset and Fetch 在CPU 方面的优势会比较明显的,否则,性能的提升并不明显。
语法方面 Offset and Fetch 则是十分的简洁,一句搞定,比起 Row_Number() 好了太多 ~

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft