优化分页查询

发表于 更新于 分类于
本文字数： 4.2k 阅读时长 ≈ 4 分钟
海量数据分页查询时如何针对性优化。

优化分页查询

一. 分页查询

1.1 使用场景

当数据量较大时，不需要一次性全部展示给用户；又或者出于性能考虑需要对数据做分批处理。这类场景需要对查询做分页处理，将数据按页做切割依次返回单页结果。

1.2 不同数据库的常见分页查询写法

Oracle：

1
2
3
4
5
6
select RST2.* from (
    select RST1.*, rownum RN from (             
        select xxxx from table_xxx where XXXX
        order by XXXX         
    ) RST1 
where rownum <= (0 + 1) * 10000) RST2 where RST2.RN > 0 * 10000;

注意：如果rownum和order by写在同一层，sql会先生成rownum后执行order by子句，因而导致排序后结果不对。<=写在内层在数据量大时要比外层效率高，因为在CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。

MySQL：

1
SELECT xxxx FROM table_xxx LIMIT M,N

1.3 分页为何要加排序？什么是稳定分页？

当表数据量为百万或者千万级时，随着偏移量增大，分页查询执行速度越来越慢，可以分析后发现主要由于Order By导致。那么分页为什么要加排序呢？

首先，如果分页查询使用的场景对数据一致/重复度不敏感的话，Order By不是必须的。但如果对此有要求就一定要添加排序操作。因为数据库提供的分页操作本身是不稳定的，不能保证查询按行顺序返回，除非排序能够保证唯一性，当ORDER BY的字段值相同时，数据的排序就会变得随机。

  • 如果没有指定ORDER BY语句，则数据库不保证以特定顺序返回结果。 有些人认为，如果没有指定order by子句，行总是以聚簇索引顺序或物理磁盘顺序返回。 然而，这是不正确的，因为在查询处理期间可以改变行顺序的许多因素，例如并行的HASH连接是更改行顺序的操作符的一个很好的例子。
  • 如果指定ORDER BY语句，数据库将对行进行排序，并按请求的顺序返回。 但是，如果该顺序不是确定性的，即可能有重复的值，则在每个具有相同值的组中，由于与上述相同的原因，该顺序是“随机的”。确保确定性顺序的唯一方法是在ORDER BY子句中包含保证的唯一列或列组（例如主键）。

但即使加上Order By，数据库的分页方法也不算稳定的数据库分页，因为无法应对分页查询时插入新行的场景。有一种实现稳定分页的方法，叫做键集分页。主要思想不是通过告诉数据库要跳过多少行来跳过 “看过的行”，并希望在这期间没有增加任何行，而是使用一个独特的标识来确定哪些行已经被看过哪些没有。

1
2
3
4
5
SELECT xxx
  FROM xxx
 WHERE xxx
   AND id < ?last_seen_id
 ORDER BY id DESC

无论如何都需要一个建立明确顺序的ORDER BY。从而使用这些列来确定你之前收到数据的地方。

二. Oracle优化

首先是分页内的查询SQL先尽量优化到最优：

  • 正确的构建索引。
  • 减少表之间的关联。
  • 简化查询的字段。
  • 等等。

Oracle有多种方案可以实现类似分页的效果：

  • 偏移量：适用于Oracle12c或更高版本的数据库

    1
    2
    3
    4
    SELECT fieldA, fieldB 
    FROM table 
    ORDER BY fieldA 
    OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY

  • 函数：如果为Oracle11g或更低版本的数据库，请使用分析功能 RowNumber() 

    1
    2
    3
    4
    5
    6
    7
    SELECT fieldA, fieldB
    FROM (
        SELECT fieldA, fieldB,
            row_number() over (order by fieldA) rowRank
        FROM table_name
    )
    WHERE rowRank BETWEEN 5 AND 14;

  • RowNum：适用于Oracle11g或更低版本的数据库

    1
    2
    3
    4
    5
    6
    7
    SELECT T.* FROM ( 
    SELECT T.*, rowNum as rowIndex
    FROM (
        SELECT fieldA, fieldB,
        FROM table_name
    )T)T
    WHERE rowIndex > 0 AND rowIndex <= 20;

高版本数据库建议使用方案1，否则可以采用方案三。

OFFSET n ROWS FETCH NEXT m ROWS ONLY 或 rownum 语法上未要求一定配套Order By，但语义上还是要加上。如果不添加ORDER BY子句，数据库可能以任何顺序返回结果。因此需要在ORDER BY子句中建立一个明确的行的顺序（以便没有行与另一行是对等的）。实际使用中都应该在ORDER BY子句中包含一些唯一键，如 ORDER BY time_stamp DESC, id DESC 。

对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页·的数据，越靠后面的页数访问几率越小）。

当数据量很大时就要考虑分库分表或者分区，以及历史数据归档等。

三. MySQL优化

3.1 语法

1
2
3
4
5
6
7
8
9
SELECT * FROM table LIMIT [offset,] rows | `rows OFFSET offset ` 
(LIMIT offset, `length`)

SELECT XXX
FROM XXX
where XXX
order by id asc 
LIMIT 2000 OFFSET 50000
-- LIMIT 2000,50000

  • 参数1：指定返回记录行的偏移量。

  • 参数2：指定返回记录行的最大数目。

  • 只提供一个参数 limit 5000 表示最大行数。

对于数据规模不算大的场景，只需要注意 WHERE 和 Order By 上使用组合索引。

1
2
SELECT * FROM table_XX WHERE XXX_A = 1 ORDER BY id LIMIT 50, 10
-- 创建字段为(XXX_A, id)的组合索引

当数据量越大，偏移量也就越大，LIMIT执行速度会缓慢下降：

1
SELECT * FROM table_XX WHERE XXX_A = 1 ORDER BY id LIMIT 100000, 10

这条语句将会扫描10010条数据，如果数据量很大，每次取10条数据都得扫描远远超过10条的数据，甚至筛选上百万条数据后然后选择10条。

3.2 优化

分页有两类情况：

  1. 固定一页一页顺序查询。
  2. 跳转页面只查指定页数据。

固定按页查询，可以记住每次分页的最大最小值

1
2
3
4
-- 查询下一页
select * from message where id > 100000 order by id asc limit 10;
-- 查询上一页
select * from message where id < 99990 order by id desc limit 10;

查询指定页：

1
2
3
4
5
-- 从前向后跳
SELECT * FROM message WHERE id > 100000 ORDER BY id ASC LIMIT 10,10;

-- 从后向前条
SELECT * FROM message WHERE id < 99990 ORDER BY id DESC LIMIT 20,10;

缺点：必须有一个数值型递增序列字段。

还可以通过子查询的方式来优化，仅限于主键排序：

1
2
SELECT * FROM table_XX WHERE id >= 
(SELECT id FROM table_XX WHERE XXX_A = 1 ORDER BY id LIMIT 100000, 1) LIMIT 10

或者使用JOIN分页的方式：

1
2
3
SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。

可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。

当数据规模达到百万或千万级，LIMIT分页会存在较大性能问题。

1
2
3
4
5
6
-- 平均用时6.6秒 
SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

-- 平均用时0.6秒 
SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM  
`cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

因为要取出所有字段内容，第一种需要跨越大量数据块并取出，而第二种基本通过直接根据索引字段定位后，才取出相应内容，效率自然大大提升。对limit的优化，不是直接使用limit，而是首先获取到offset的id，然后直接使用limit size来获取数据。

可以看出，越往后分页，LIMIT语句的偏移量就会越大，两者速度差距也会越明显。实际应用中，可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。

总结优化步骤：

  1. 使用索引字段或主键进行排序。

    1
    select film_id, description from film order by film_id limit 50,5;

  2. 记录上次返回的主键，在下次查询时使用主键过滤。

    1
    2
    3
    4
    select film_id, description from film 
    where film_id > 55 and film_id <= 60
    order by film_id 
    limit 1,5;

参考：

🔗 《We need tool support for keyset pagination (use-the-index-luke.com)》