V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
littlemoney
V2EX  ›  问与答

关于 mysql 大数量分页的一个问题

  •  
  •   littlemoney · 2020-01-08 11:32:58 +08:00 · 1178 次点击
    这是一个创建于 527 天前的主题,其中的信息可能已经有所发展或是发生改变。

    select xxx from xx where date_create > 'xx' and date_create < 'xx'; -- 总数据量约 100w

    此时加入分页参数

    select xxx from xx where date_create > 'xx' and date_create < 'xx' limit index,1000

    现在发现,当 index 大于 30w 的时候,查询速度直线下降[小于 30w 的时候,只要 1 秒,大于 30 万,需要 40 多秒],似乎以 30 万为分界点?

    explain 看了下,走上的索引都是 date_create

    求教下,这是什么原因导致查询差距巨大呢?

    是 mysql 认为 limit 的起点过大?不如全表扫描?

    7 条回复    2020-01-08 14:34:45 +08:00
    xsm1890
        1
    xsm1890   2020-01-08 11:51:09 +08:00   ❤️ 1
    随着表的增长,where 条件出来的数据太多,大于 15%,使得索引失效(会导致 CBO 计算走索引花费大于走全表);
    可以强制索引 force index(index_name)
    littlemoney
        2
    littlemoney   2020-01-08 12:01:03 +08:00
    @xsm1890 #1 感谢,我试试
    junan0708
        3
    junan0708   2020-01-08 12:26:11 +08:00 via Android
    获取一下 ID,where 条件加上 id
    littlemoney
        4
    littlemoney   2020-01-08 12:26:50 +08:00
    @junan0708 #3 id 是 uuid = =
    opengps
        5
    opengps   2020-01-08 12:32:32 +08:00 via Android
    索引在数据量大的时候本身也很大
    Raymon111111
        6
    Raymon111111   2020-01-08 12:34:59 +08:00   ❤️ 1
    带业务条件的大分页是无法直接解决的, 因为当你 limit 30w, 30w+1000 的时候, 这个语义本身是想拿到 "第" 30w 条数据, 那就得找出前 30w 的数据并跳过, 而如果 where 又带条件的话, 只能一条一条看是不是满足条件. 要明白就算有索引的情况下 db 也无法知道 "第" 30w 条数据在什么位置

    这种一般有好多种妥协的方案

    一种是找到一个自增的字段, 然后用这个自增的字段不断去捞后面 1000 条数据, 比如 id, select * from table where id>#{id1} limit 1000 order by id, 这样捞出来 1000 条数据都是比 id1 要大的数据, 然后找到这 1000 条数据里 id 最大的那个重新作为 id1 继续查询, 直到查不出数据为止
    petelin
        7
    petelin   2020-01-08 14:34:45 +08:00 via iPhone
    就像楼上大佬说的

    我看你这个查询条件有没有可能创建一个 sum count
    字段 然后直接定位到那条 limit start 的索引
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1410 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 00:34 · PVG 08:34 · LAX 17:34 · JFK 20:34
    ♥ Do have faith in what you're doing.