开发者社区> 问答> 正文

mysql数据库中千万级数据分页的问题

讨论一下mysql数据库中千万级数据如何分页?

展开
收起
落地花开啦 2016-02-12 17:08:16 3831 0
2 条回答
写回答
取消 提交回答
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.

    PostgreSQL 分页优化的例子:
    某一个SQL, 问我为什么只改了一个条件, 查询速度居然从毫秒就慢到几十秒了,
    如下 :

    SELECT *                                                                                
      FROM tbl
      where create_time>='2014-02-08' and create_time<'2014-02-11'
      and x=3
      and id != '123'
      and id != '321'
      and y > 0 order by create_time limit 1 offset 0;

    运行结果100毫秒左右.
    执行计划 :

    Limit  (cost=0.56..506.19 rows=1 width=1038)
       ->  Index Scan using idx on tbl  (cost=0.56..2381495.60 rows=4710 width=1038)
             Index Cond: ((create_time >= '2014-02-08 00:00:00'::timestamp without time zone) AND (create_time < '2014-02-11 00:00:00'::timestamp without time zone))
             Filter: (((id)::text <> '123'::text) AND ((id)::text <> '321'::text) AND (y > 0) AND (x = 3))

    改成如下 :

    SELECT *                                                                                
      FROM tbl
      where create_time>='2014-02-08' and create_time<'2014-02-11'
      and x=3
      and id != '123'
      and id != '321'
      and y > 0 order by create_time limit 1 offset 10;

    运行几十秒.
    执行计划如下 :

    Limit  (cost=5056.98..5562.62 rows=1 width=1038)
       ->  Index Scan using idx on tbl  (cost=0.56..2382076.78 rows=4711 width=1038)
             Index Cond: ((create_time >= '2014-02-08 00:00:00'::timestamp without time zone) AND (create_time < '2014-02-11 00:00:00'::timestamp without time zone))
             Filter: (((id)::text <> '11622'::text) AND ((id)::text <> '13042'::text) AND (y > 0) AND (x = 3))

    我们看到两个SQL执行计划是一样的, 但是走索引扫描的记录却千差万别. 第二个SQL扫描了多少行呢?
    我们来看看第二个查询得到的create_time值是多少:

    select create_time from tbl 
      where create_time>='2014-02-08' and create_time<'2014-02-11'
      and x=3
      and id != '123'
      and id != '321'
      and y > 0 order by create_time limit 1 offset 10;

    结果 :

    '2014-02-08 18:38:35.79'

    那么它扫描了多少行(或者说多少个数据块)呢? 通过explain verbose可以输出.
    当然使用以下查询也可以估算出来 :

    select count(*) from tbl where create_time<='2014-02-08 18:38:35.79' and create_time>='2014-02-08';
      count  
    ---------
     1448081
    (1 row)

    也就是说本例的SQL中的WHERE条件的数据在create_time这个字段顺序上的分布比较零散, 并且数据量比较庞大.
    所以offset 10后, 走create_time这个索引自然就慢了.
    仔细的了解了一下开发人员的需求, 是要做类似翻页的需求.

    优化方法1, 在不新增任何索引的前提下, 还是走create_time这个索引, 减少重复扫描的数据.
    需要得到每次取到的最大的create_time值, 以及可以标示这条记录的唯一ID.
    下次取的时候, 不要使用offset 下一页, 而是加上这两个条件.
    例如 :

    select create_time from tbl 
      where create_time>='2014-02-08' and create_time<'2014-02-11'
      and x=3
      and id != '123'
      and id != '321'
      and pk not in (?)  -- 这个ID是上次取到的create_time最大的值的所有记录的pk值.
      and y > 0 
      and create_time >= '2014-02-08 18:38:35.79'  -- 这个时间是上次取到的数据的最大的时间值.
      order by create_time limit ? offset 0;

    通过这种方法, 可以减少limit x offset y这种方法取后面的分页数据带来的大量数据块离散扫描.
    另一些关于分页优化的例子 :
    http://blog.163.com/digoal@126/blog/static/163877040201111694355822/
    http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/

    2019-07-17 18:41:32
    赞同 展开评论 打赏
  • 喜欢技术,喜欢努力的人

    分表,很少有单表,主要原因在于limit翻页的时候越往后翻越慢,但是一般订单类都是翻最新的

    2019-07-17 18:41:32
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
2022 DTCC-阿里云一站式数据库上云最佳实践 立即下载
云时代的数据库技术趋势 立即下载
超大型金融机构国产数据库全面迁移成功实践 立即下载

相关镜像