MySQL高级函数之：`FOUND_ROWS()`：其在`LIMIT`分页中获取总行数的应用与性能开销。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL高级函数之 `FOUND_ROWS()`：分页中的总行数获取与性能考量

各位同学，大家好。今天我们来深入探讨MySQL中一个非常有用的函数：FOUND_ROWS()。尤其是在处理LIMIT分页时，它能帮助我们高效地获取总行数。我们将从FOUND_ROWS()的基本原理开始，逐步分析它在分页中的应用，以及由此带来的性能影响，并探讨一些优化策略。

`FOUND_ROWS()` 函数的基本原理

FOUND_ROWS() 是一个MySQL函数，它返回的是上一个 SELECT 语句（不包括 SELECT SQL_CALC_FOUND_ROWS 子句）查询到的、符合 WHERE 条件的总行数。也就是说，这个函数依赖于上一次执行的SELECT语句。

关键要点：

FOUND_ROWS() 必须紧跟在使用了 LIMIT 子句的 SELECT 语句之后调用，才能发挥其作用。
FOUND_ROWS() 返回的是在没有 LIMIT 限制的情况下，SELECT 语句本来应该返回的总行数。
FOUND_ROWS() 仅适用于 SELECT 语句。

基本语法:

SELECT ... FROM ... WHERE ... LIMIT ...;
SELECT FOUND_ROWS();

例子:

假设我们有一个 users 表，包含 id 和 name 两列。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

INSERT INTO users (name) VALUES
('Alice'), ('Bob'), ('Charlie'), ('David'), ('Eve'),
('Frank'), ('Grace'), ('Henry'), ('Ivy'), ('Jack'),
('Kelly'), ('Liam'), ('Mia'), ('Noah'), ('Olivia');

现在，我们执行一个带 LIMIT 的查询：

SELECT * FROM users WHERE name LIKE 'A%' LIMIT 3;
SELECT FOUND_ROWS();

第一个 SELECT 语句返回：

+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+

第二个 SELECT FOUND_ROWS() 语句返回：

+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+

虽然 LIMIT 限制了只返回3行，但 FOUND_ROWS() 返回的是符合 WHERE name LIKE 'A%' 条件的总行数（不考虑 LIMIT）。

`FOUND_ROWS()` 在 `LIMIT` 分页中的应用

在Web应用中，分页功能非常常见。我们通常使用 LIMIT 子句来实现分页。但是，仅仅使用 LIMIT，我们只能获取当前页的数据，而无法得知总共有多少页。这时，FOUND_ROWS() 就能派上用场。

传统分页方案 (不使用 FOUND_ROWS()):

在不使用FOUND_ROWS()的情况下，我们需要执行两次查询：一次查询当前页的数据，另一次查询总行数。

-- 获取当前页数据 (例如，每页10条，获取第3页)
SELECT * FROM users LIMIT 20, 10;

-- 获取总行数
SELECT COUNT(*) FROM users;

这种方式需要执行两次查询，增加了数据库的负担。

使用 FOUND_ROWS() 的分页方案:

-- 获取当前页数据，并计算总行数
SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 20, 10;
SELECT FOUND_ROWS();

在这个方案中，我们在第一个 SELECT 语句中添加了 SQL_CALC_FOUND_ROWS 选项。这个选项告诉MySQL在执行查询时，计算符合条件的总行数，并将结果存储起来。然后，我们可以通过调用 FOUND_ROWS() 函数来获取这个总行数。

代码示例 (PHP):

<?php

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$page = isset($_GET['page']) ? intval($_GET['page']) : 1; // 获取当前页码
$pageSize = 10; // 每页显示的数量
$offset = ($page - 1) * $pageSize; // 计算偏移量

// 查询数据
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT $offset, $pageSize";
$result = $conn->query($sql);

$data = [];
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

// 获取总行数
$sql_total = "SELECT FOUND_ROWS()";
$result_total = $conn->query($sql_total);
$row_total = $result_total->fetch_row();
$totalRows = $row_total[0];

// 计算总页数
$totalPages = ceil($totalRows / $pageSize);

echo "当前页: " . $page . "<br>";
echo "总页数: " . $totalPages . "<br>";

echo "<table>";
echo "<tr><th>ID</th><th>Name</th></tr>";
foreach ($data as $row) {
    echo "<tr><td>" . $row['id'] . "</td><td>" . $row['name'] . "</td></tr>";
}
echo "</table>";

$conn->close();

?>

这个PHP示例展示了如何使用 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 来实现分页功能。

`FOUND_ROWS()` 的性能开销

虽然 FOUND_ROWS() 可以简化分页逻辑，但它也带来了一定的性能开销。当使用 SQL_CALC_FOUND_ROWS 时，MySQL 需要做更多的工作：

计算总行数： 即使 LIMIT 限制了返回的行数，MySQL 仍然需要扫描整个表（或者使用索引）来计算符合 WHERE 条件的总行数。这就意味着，即使你只需要显示第一页的10条数据，MySQL 也会计算整个表有多少条数据符合你的条件。
存储结果： MySQL 需要将计算得到的总行数存储起来，以便后续的 FOUND_ROWS() 函数可以访问。

性能影响的程度取决于以下因素：

表的大小： 如果表非常大，计算总行数可能需要很长时间。
WHERE 子句的复杂性： 复杂的 WHERE 子句会增加计算总行数的开销。
索引的使用： 如果 WHERE 子句能够利用索引，可以显著提高计算总行数的效率。

性能测试:

为了更直观地了解 SQL_CALC_FOUND_ROWS 的性能开销，我们可以进行一些简单的性能测试。以下是一个使用 SQL_CALC_FOUND_ROWS 和不使用 SQL_CALC_FOUND_ROWS 的两个查询的耗时对比。

-- 创建一个较大的表
CREATE TABLE large_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入100万条数据
INSERT INTO large_table (data)
SELECT LPAD(RAND() * 100000, 255, '0'), NOW() FROM (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) a, (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) b, (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) c, (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) d, (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) e, (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) f;

-- 测试1: 使用 SQL_CALC_FOUND_ROWS
SET @start = NOW();
SELECT SQL_CALC_FOUND_ROWS * FROM large_table WHERE data LIKE '00%' LIMIT 10;
SELECT FOUND_ROWS();
SET @end = NOW();
SELECT TIMEDIFF(@end, @start);

-- 测试2: 不使用 SQL_CALC_FOUND_ROWS (两次查询)
SET @start = NOW();
SELECT * FROM large_table WHERE data LIKE '00%' LIMIT 10;
SELECT COUNT(*) FROM large_table WHERE data LIKE '00%';
SET @end = NOW();
SELECT TIMEDIFF(@end, @start);

执行以上测试代码，可以对比两种方式的耗时。在大数据量的表中，使用SQL_CALC_FOUND_ROWS 往往会比两次查询更慢。

表格总结:

方法	优点	缺点
`SQL_CALC_FOUND_ROWS` + `FOUND_ROWS()`	简化代码，只需一次查询	性能开销较高，尤其是在大表和复杂 `WHERE` 子句的情况下
两次查询 (COUNT + LIMIT)	在某些情况下性能更好，尤其是在大表和复杂 `WHERE` 子句的情况下。	代码稍显复杂，需要执行两次查询

优化 `FOUND_ROWS()` 的性能

虽然 FOUND_ROWS() 有性能开销，但我们可以采取一些措施来优化其性能：

确保 WHERE 子句使用索引： 如果 WHERE 子句能够有效地利用索引，可以显著提高计算总行数的效率。可以通过 EXPLAIN 命令来检查查询是否使用了索引。
避免在复杂的查询中使用 SQL_CALC_FOUND_ROWS： 如果查询非常复杂，或者表非常大，可以考虑使用两次查询的方式来获取总行数。
考虑使用缓存： 如果总行数不经常变化，可以考虑将总行数缓存起来，避免每次都重新计算。可以使用 Redis、Memcached 等缓存系统。
估算总行数： 在某些情况下，我们不需要精确的总行数，只需要一个大概的估计值。这时，可以使用 SQL_CALC_FOUND_ROWS，但是限制扫描的行数。
```
SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE name LIKE 'A%' LIMIT 1000;
SELECT FOUND_ROWS();
```
如果 FOUND_ROWS() 返回的值等于 1000，则说明总行数可能大于 1000。如果返回的值小于 1000，则说明总行数就是 FOUND_ROWS() 返回的值。这种方法可以避免扫描整个表。
程序逻辑优化: 某些场景下，可以根据业务需求优化分页逻辑，例如：
- 如果用户只关心是否有下一页，而不在意具体的总页数，则可以查询 LIMIT N+1 条数据，如果返回 N+1 条数据，则表示有下一页，否则没有。这样可以避免计算总行数。
- 使用游标分页，避免使用 OFFSET，提高查询效率。

更现代的分页方法：Seek Method (或 Key-Set Pagination)

传统的基于 LIMIT 和 OFFSET 的分页方式，随着 OFFSET 的增大，性能会显著下降。这是因为 MySQL 需要扫描 OFFSET 数量的行，然后丢弃它们。

Seek Method 是一种更高效的分页方式，它通过使用表中的唯一索引（通常是主键）来定位下一页的数据。

原理:

Seek Method 不是通过 OFFSET 来跳过行，而是记住上一页的最后一条记录的 ID (或者其他唯一索引字段)，然后使用 WHERE 子句来查询 ID 大于该值的记录。

代码示例:

-- 获取第一页 (例如，每页10条)
SELECT * FROM users ORDER BY id ASC LIMIT 10;

-- 假设第一页最后一条记录的 ID 是 10

-- 获取第二页
SELECT * FROM users WHERE id > 10 ORDER BY id ASC LIMIT 10;

-- 假设第二页最后一条记录的 ID 是 20

-- 获取第三页
SELECT * FROM users WHERE id > 20 ORDER BY id ASC LIMIT 10;

优点:

性能稳定，不受 OFFSET 大小的影响。
适用于大数据量表。

缺点:

无法直接跳转到任意页码。
需要一个唯一索引字段。
数据必须按照索引字段排序。

何时使用 Seek Method:

当需要处理大数据量表的分页时。
当只需要顺序浏览数据，不需要跳转到任意页码时。

Seek Method 是一种非常有效的替代方案，可以避免 SQL_CALC_FOUND_ROWS 的性能问题。结合业务场景，选择合适的分页方案，才能达到最优的性能。

选择合适的方法

在选择是否使用FOUND_ROWS()时，我们需要综合考虑以下几个因素：

表的大小: 对于小表，FOUND_ROWS() 的性能开销可以忽略不计。
WHERE 子句的复杂性: 对于复杂的 WHERE 子句，FOUND_ROWS() 的性能开销会增加。
是否需要精确的总行数: 如果只需要一个大概的估计值，可以使用估算总行数的方法。
是否需要跳转到任意页码: 如果需要跳转到任意页码，Seek Method 就不适用。

总的来说，没有一种通用的最佳方案。我们需要根据具体的业务场景，选择最适合的方法。

总结：掌握技巧，优化分页性能

今天我们深入探讨了MySQL中的FOUND_ROWS()函数，以及其在LIMIT分页中的应用和性能开销。了解FOUND_ROWS()的工作原理，权衡其优缺点，并结合索引优化和Seek Method等技术，可以帮助我们构建更高效的分页系统。选择最适合的方法，才能达到最优的性能。

MySQL高级函数之 FOUND_ROWS()：分页中的总行数获取与性能考量

FOUND_ROWS() 函数的基本原理

FOUND_ROWS() 在 LIMIT 分页中的应用

FOUND_ROWS() 的性能开销

优化 FOUND_ROWS() 的性能