The meaning and method of paging technology principles and implementation (1)_MySQL

What is paging technology
Paging is a technology that displays all data to the user in segments. What the user sees each time is not all the data, but a part of it. If the content they want for self-study is not found in it, the user can Convert the visible content by specifying page numbers or turning pages until you find the content you want. In fact, this is very similar to when we read a book.
The following page shows two common paging methods:

The meaning of pagination
Paging is indeed effective, but it will definitely increase the complexity of the system, but can it be avoided? If the amount of data is small, of course it is possible. But for enterprise information systems, the amount of data will not be limited to a small range. If you desperately select * from a certain table and then throw the returned data to the client, even if the client can tolerate thousands of dazzling tabular data, the busy network and the nervous server will Put forward their silent protests, and sometimes even end with an outright strike. This ending is a bit like the story between an ancient tyrant and his intolerable subjects.
The programmer is not a tyrant. He hopes that the program will make life better rather than worse. Considering the fact that most enterprise information systems have three-layer or even more-layer architectures, programmers should adopt the form of paging when displaying data to customers. .if he doesn't want to be inundated with complaints or woken up by phone calls in the middle of the night.

The entire process from request initiation to data return
Now that you have made up your mind to paginate, before you start writing the code, let us recall that in a typical three-tier architecture, the entire process from request initiation to return data is as follows:

Where to paginate
From the above picture, we can observe that after the SQL statement is processed, the database, WebApplication and Browser can all perform paging. So where is the best place to do paging?
The criterion for judgment is speed. Obviously, there is a network between the database server, Web application server and client. If the amount of data transmitted by the network is smaller, the client will get a response faster. And generally speaking, the database server and Web The processing power of the application server is generally much stronger than that of the client. From these two points, the solution of paging on the client is the most undesirable.
Secondly, there are only two methods left: paging on the Web server side and paging on the database side. If you choose to paging on the Web server side, most of the filtered data will still be transmitted to the Web application server. This is not as good as Perform paging directly on the database side.
Therefore, a better paging practice should be to retrieve only page-sized blocks of data from the database each time the page is turned. In this way, although the database needs to be queried every time the page is turned, the number of records queried is very small, and the amount of data transmitted over the network is not large. If a connection pool is used, the most time-consuming process of establishing a database connection can be skipped. On the database side, there are various mature optimization technologies used to improve query speed, which are much more effective than caching at the application server layer.

Paging SQL statement
If we access the database through JDBC, then it is necessary to use different SQL paging statements according to the database type. For the MySQL database, we can use the limit statement for paging. For the Oracle database, we can use the rownum method. Pagination.
(1) MySql’s Limit m,n statement
Among the two parameters after Limit, parameter m is the starting index, which starts from 0; parameter n is the number of records returned. If we need paging, just specify these two values
(2) rownum of Oracle database
In Oracle database, the paging method is not as simple as MySql. It needs to rely on rownum to achieve it.
Rownum represents the row number of a record. It is worth noting that it is assigned after each row is obtained. Therefore, if you want to specify the range of rownum to obtain paging data, it cannot be done in a layer of query statements. To do paging, you have to do it again. Query.

 SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。
上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。
选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

 SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

 对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。
 这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
 而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。
 上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

精彩专题分享：

SQL Server分页技术总结：http://www.bitsCN.com/Special/604.htm

ASP.NET分页功能操作：http://www.bitsCN.com/Special/149.htm

javascript分页功能操作：http://www.bitsCN.com/Special/469.htm

jquery分页功能操作：http://www.bitsCN.com/Special/465.htm

php分页功能操作：http://www.bitsCN.com/Special/236.htm

 其实分页技术还有很多其它技术，比如：Struts、hibernate等等都可实现。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。