SQL LIMIT vs. JDBC Statement setMaxRows.哪一个更好？

SQL 级限制

若要限制 SQL 查询结果集的大小，可以使用 SQL：008 语法：

SELECT title
FROM post
ORDER BY created_on DESC
OFFSET 50 ROWS
FETCH NEXT 50 ROWS ONLY

它适用于Oracle 12，SQL Server 2012或PostgreSQL 8.4或更高版本。

对于MySQL，您可以使用LIMIT和OFFSET子句：

SELECT title
FROM post
ORDER BY created_on DESC
LIMIT 50
OFFSET 50

使用 SQL 级分页的优点是数据库执行计划可以使用此信息。

因此，如果我们在列上有一个索引：created_on

CREATE INDEX idx_post_created_on ON post (created_on DESC)

我们执行以下使用该子句的查询：LIMIT

EXPLAIN ANALYZE
SELECT title
FROM post
ORDER BY created_on DESC
LIMIT 50

我们可以看到数据库引擎使用索引，因为优化器知道只有 50 条记录需要获取：

Execution plan:
Limit  (cost=0.28..25.35 rows=50 width=564)
       (actual time=0.038..0.051 rows=50 loops=1)
  ->  Index Scan using idx_post_created_on on post p  
      (cost=0.28..260.04 rows=518 width=564) 
      (actual time=0.037..0.049 rows=50 loops=1)
Planning time: 1.511 ms
Execution time: 0.148 ms

JDBC 语句最大行

根据 setMaxRows Javadoc：

如果超出限制，多余的行将以静默方式删除。

这并不是很令人放心！

因此，如果我们在PostgreSQL上执行以下查询：

try (PreparedStatement statement = connection
    .prepareStatement("""
        SELECT title
        FROM post
        ORDER BY created_on DESC
    """)
) {
    statement.setMaxRows(50);
    ResultSet resultSet = statement.executeQuery();
    int count = 0;
    while (resultSet.next()) {
        String title = resultSet.getString(1);
        count++;
    }
}

我们在PostgreSQL日志中得到以下执行计划：

Execution plan:
  Sort  (cost=65.53..66.83 rows=518 width=564) 
        (actual time=4.339..5.473 rows=5000 loops=1)
  Sort Key: created_on DESC
  Sort Method: quicksort  Memory: 896kB
  ->  Seq Scan on post p  (cost=0.00..42.18 rows=518 width=564) 
                          (actual time=0.041..1.833 rows=5000 loops=1)
Planning time: 1.840 ms
Execution time: 6.611 ms 

因为数据库优化器不知道我们只需要获取 50 条记录，所以它假定需要扫描所有 5000 行。如果查询需要获取大量记录，则全表扫描的成本实际上低于使用索引时，因此执行计划根本不会使用索引。

我在Oracle，SQL Server，PostgreSQL和MySQL上运行了这个测试，看起来Oracle和PostgreSQL优化器在生成执行计划时不使用该设置。maxRows

但是，在 SQL Server 和 MySQL 上，会考虑 JDBC 设置，并且执行计划等效于使用 或 的 SQL 查询。您可以自己运行测试，因为它们在我的高性能 Java 持久性 GitHub 存储库中可用。maxRowsTOPLIMIT

结论

尽管看起来是一个可移植的解决方案来限制 的大小，但如果数据库服务器优化器不使用 JDBC 属性，则 SQL 级分页的效率要高得多。setMaxRowsResultSetmaxRows

在大多数情况下，您希望使用该子句，但在一天结束时，两者都将实现您想要的。这个答案针对的是JDBC和PostgreSQL，但也适用于使用类似模型的其他语言和数据库。LIMIT

Statement.setMaxRows 的 JDBC 文档说

如果超出限制，多余的行将以静默方式删除。

即数据库服务器可能会返回更多行，但客户端将忽略它们。PostgreSQL JDBC 驱动程序在客户端和服务器端都有限制。对于客户端，请查看 AbstractJdbc2ResultSet 中 maxRows 的用法。对于服务器端，请查看QueryExecutorImpl中的maxRows。

服务器端，PostgreSQL LIMIT文档说：

查询优化器在生成查询计划时会考虑 LIMIT

因此，只要查询是明智的，它就会只加载完成查询所需的数据。