java - SQL LIMIT与JDBC语句setMaxRows。哪一个更好？

我想选择给定查询的前10条记录。因此，我可以使用以下选项之一:

使用JDBC Statement.setMaxRows() 方法

在SQL查询

中使用LIMIT和OFFSET

这两种选择的优缺点是什么？

最佳答案

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语句maxRows
根据 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行都需要扫描。如果查询需要获取大量记录，则全表扫描的成本实际上比使用索引的成本低，因此执行计划将完全不使用索引。

结论
尽管setMaxRows似乎是限制ResultSet大小的可移植解决方案，但是如果数据库服务器优化器不使用JDBC maxRows属性，则SQL级别的分页会更加有效。