mysql分页是物理分页_学习MySQL：什么是分页

最新推荐文章于 2024-08-03 23:30:14 发布

culuo4781

最新推荐文章于 2024-08-03 23:30:14 发布

阅读量2.3k

点赞数

文章标签：数据库 java mysql python sql

原文链接：https://www.sqlshack.com/learn-mysql-what-is-pagination/

版权

mysql分页是物理分页

In this article, I am going to explain that in MySQL, what is pagination and how we can implement it. When we are populating a large dataset from the MySQL database, it is not easy to read all the records on the same page. Using pagination, we can divide the result-set into multiple pages, and that increases the readability of the result set. In this article, I am going to explain:

在本文中，我将解释在MySQL中什么是分页以及如何实现它。当我们从MySQL数据库填充大型数据集时，要读取同一页面上的所有记录并不容易。使用分页，我们可以将结果集分为多个页面，这可以提高结果集的可读性。在本文中，我将解释：

How to implement pagination with example
如何通过示例实现分页
OFFSET using Page Number 偏移量
The performance overheads and how to deal with it
性能开销以及如何处理

For the demonstration, I have installed MySQL on my workstation. You can walk through one of my previous articles, How to install MySQL database server 8.0.19 on Windows 10, that explains how we can install MySQL on window 10. I have imported a sample database named sakila in the MySQL database server. You can add the sample database while installing the MySQL database server.

为了演示，我在工作站上安装了MySQL。您可以浏览我以前的文章之一，如何在Windows 10上安装MySQL数据库服务器8.0.19 ，它说明了如何在窗口10上安装MySQL。我已经在MySQL数据库服务器中导入了一个名为sakila的示例数据库。您可以在安装MySQL数据库服务器时添加示例数据库。

如何实现分页 (How to implement pagination)

We can implement pagination by using the LIMIT clause in the SELECT query. Following is the syntax:

我们可以通过在SELECT查询中使用LIMIT子句来实现分页。以下是语法：

Select * from <table_name> LIMIT value_1, OFFSET value_2

LIMIT条款 (LIMIT Clause)

In syntax, the first argument is the LIMIT clause. This LIMIT clause is used to restrict the number of rows to be returned by the SQL Query. It is like the TOP clause in SQL Server.

在语法上，第一个参数是LIMIT子句。此LIMIT子句用于限制SQL查询返回的行数。就像SQL Server中的TOP子句。

For example, I want to return the top 20 actors. The output must be sorted based on the actor_id column, and the order must be ascending. Following is the query:

例如，我想返回前20名演员。必须根据actor_id列对输出进行排序，并且顺序必须升序。以下是查询：

select * from actor order by actor_id asc LIMIT 20

Below is the output of the query:

以下是查询的输出：

抵消条款 (OFFSET Clause)

If you notice the syntax again, the second argument is OFFSET. It is used to view a specific number of rows; for example, in a query output, you want to see the records between 10 and 20, then you can use OFFSET. It populates all the records of the table, and it discards the previous records that are defined in the OFFSET clause.

如果再次注意到语法，则第二个参数为OFFSET 。它用于查看特定数量的行；例如，在查询输出中，您想要查看10到20之间的记录，则可以使用OFFSET 。它填充表的所有记录，并丢弃在OFFSET子句中定义的先前记录。

For example, we want to display the top 20 names of actor and the value of the actor_id column must start from 11 then you can write the query as below:

例如，我们要显示actor的前20名，并且actor_id列的值必须从11开始，然后可以编写如下查询：

select * from actor LIMIT 20 OFFSET 10;

Following is the output:

以下是输出：

As you can see in the above image, the value of the actor_id column starts from 10, and it has returned a total of 20 records.

如上图所示， actor_id列的值从10开始，它总共返回了20条记录。

如何使用页码计算偏移量 (How to calculate the OFFSET using Page Number)

To calculate the number of the pages, you can hardcode the value of LIMIT and OFFSET, or you can pass the value of page number in the variables. We can calculate the value of OFFSET using the value of page number. It is a little bit more secure because we are passing the value of page number, and someone cannot create a page that has all the records by manipulating the value of the input variable.

要计算页数，可以对LIMIT和OFFSET的值进行硬编码，也可以在变量中传递页码的值。我们可以使用页码的值来计算OFFSET的值。因为我们正在传递页码的值，而且有人无法通过操纵输入变量的值来创建包含所有记录的页，所以它更加安全。

The following is the formula:

公式如下：

Records_Per_page= 10;
offset_value = (page_Number-1) * Records_Per_page;

Records_Per_page = 10;
offset_value =（page_Number-1）* Records_Per_page;

For example, you want to display the name of 30 actors in 3 pages. Therefore, the formula must be as shown below:

例如，您要在3页中显示30个演员的名称。因此，公式必须如下所示：

Records_Per_page= 10;
offset_value_1 = (1-1) * 10;
offset_value_2 = (2-1) * 10;
offset_value_3 = (3-1) * 10;

Records_Per_page = 10;
offset_value_1 =（1-1）* 10;
offset_value_2 =（2-1）* 10;
offset_value_3 =（3-1）* 10;

The value of Records_per_pages is used in the LIMIT clause and the values of offset_value_1, offset_value_2, offset_value_3 is used in the OFFSET clause.

Records_per_pages的值LIMIT子句中使用，并且offset_value_1，offset_value_2的值，offset_value_3是偏移子句中使用。

The query should be written as follows:

该查询应编写如下：

select * from actor LIMIT 10 OFFSET 0;
select * from actor LIMIT 10 OFFSET 10;
select * from actor LIMIT 10 OFFSET 20;

Following is the output of the first query:

以下是第一个查询的输出：

Following is the output of the second query:

以下是第二个查询的输出：

Following is the output of the third query:

以下是第三个查询的输出：

性能开销 (The performance overheads)

The pagination is very useful when you want to design an application that displays a large dataset into multiple pages. While designing the application, we populate the entire dataset from the database server and then perform pagination on the application/web server. Now, when you perform this on the database server, you can achieve performance improvement. But sometimes it becomes an additional overhead on the database server. Especially when you are sharing the same database server between multiple client databases, to avoid the performance issue, we can use the following alternative.

当您要设计一个将大型数据集显示为多个页面的应用程序时，分页非常有用。在设计应用程序时，我们从数据库服务器填充整个数据集，然后在应用程序/ Web服务器上执行分页。现在，当您在数据库服务器上执行此操作时，可以提高性能。但是有时这会成为数据库服务器上的额外开销。特别是在多个客户端数据库之间共享同一数据库服务器时，为避免性能问题，我们可以使用以下替代方法。

使用ORDER BY实现分页（避免丢弃记录） (Implement the paging using ORDER BY (avoid discarding the records))

As mentioned, the pagination populates all the records of the table, displays the number of records specified in the LIMIT clause, and discards all the previous records specified in the OFFSET clause. If the table contains millions of records, then the performance of the query can be reduced significantly. To avoid that, we can specify the range in the WHERE clause and limit the records using the LIMIT clause. For example, we want to populate the name of the top ten actors whose actor_id is greater than 10. The query should be written as follows:

如前所述，分页将填充表的所有记录，显示LIMIT子句中指定的记录数，并丢弃OFFSET子句中指定的所有先前记录。如果表包含数百万条记录，则查询的性能可能会大大降低。为了避免这种情况，我们可以在WHERE子句中指定范围，并使用LIMIT子句限制记录。例如，我们要填充actor_id大于10的前十个actor的名称。该查询应编写如下：

select * from actor where actor_id > 10 LIMIT 10;

Below is the output:

以下是输出：

摘要 (Summary)

The pagination is a very interesting concept in MySQL and can be achieved easily by LIMIT and OFFSET clauses. It is very useful when you want to display the large recordset on multiple pages. In this article, I have explained about the pagination and how we can implement it, how we can calculate the OFFSET using page number, and the number of records per page. I have also explained how it can impact the performance and the possible alternative to it.

分页是MySQL中一个非常有趣的概念，可以通过LIMIT和OFFSET子句轻松实现。当您要在多个页面上显示大型记录集时，此功能非常有用。在本文中，我已经解释了分页以及如何实现它，如何使用页码计算偏移量 ，以及每页的记录数。我还解释了它如何影响性能以及可能的替代方案。