深分页问题的优化

发表于 更新于 分类于

深分页存在的问题

1
2
3
select *
from tname
limit 1000000, 10

当limit偏移量很大，MySQL就需要扫描大量数据才能找到指定页的数据，limit越大，查询耗时就越久。

还有一个问题：当 order by 排序字段存在重复值，那么在分页查询中可能会出现重复数据的情况。为了避免这种情况，可以在SQL查询语句中使用唯一的排序字段来进行排序，或者在SQL查询语句中增加一个唯一的排序条件，例如使用id字段进行排序，这样可以确保每条记录在排序后都是唯一的。

解决方案

  1. SQL优化 - 子查询 + 索引
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 优化前
    select *
    from tname
    limit 1000000, 10

    -- 优化后
    select a.*
    from tname a
    inner join (
        select id
        from tname
        order by create_time desc
        limit 1000000, 10
    ) b on a.id = b.id;
  • 注意其中create_time字段需要加索引，否则需要排序的数据量一大就容易触发filesort。
  • 如果排序有多个字段，需要加上联合索引

  1. SQL优化 - where id > x
    将上一页的查询结果中的最大id作为下一页查询的where条件，这样可以大幅减少扫描行数，提高查询性能。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select a.*
    from tname a
    inner join (
        select id
        from tname
        where status = 0
        and id > #{id}
        order by create_time desc
        limit 10
    ) b on a.id = b.id;

  2. 业务限制
    只允许查询指定时间范围内的数据。

  3. 限制最大页码数