V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
hyd8323268
V2EX  ›  MySQL

mysql 近千万级数据表,在分页时有什么好的方案吗。

  •  
  •   hyd8323268 · 2020-04-12 20:53:21 +08:00 · 9831 次点击
    这是一个创建于 1689 天前的主题,其中的信息可能已经有所发展或是发生改变。

    单表 800w 条,myisam 引擎。 需要根据一个值为时间戳的字段进行排序,时间戳有重复值,普通索引。 刚开始是采用的:select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 但是特别慢,前几页还好,越到后面越慢。 百度了几个方案试了都感觉没什么特别大的变化。

    56 条回复    2021-06-17 20:25:35 +08:00
    gz911122
        1
    gz911122  
       2020-04-12 20:59:54 +08:00
    假设时间戳字段叫 ctime,
    那么直接 ctime > ? limit ? 就行
    jugelizi
        2
    jugelizi  
       2020-04-12 20:59:56 +08:00
    不要分页 page 传最后最大 ID
    odirus
        3
    odirus  
       2020-04-12 21:00:54 +08:00
    有主键吧?

    "select * from t limit 10000, 10" 变为 "select * from t where pk > ${上一页的最大值} limit 10" 这种方案试过没有呢?
    gz911122
        4
    gz911122  
       2020-04-12 21:01:31 +08:00
    @gz911122 order by ctime,id where ctime > 上次查询最后一条的 ctime id > 最后一条的 id limit 分页大小
    odirus
        5
    odirus  
       2020-04-12 21:43:20 +08:00   ❤️ 1
    如果使用最后一条记录 ID 来查询的话貌似要漏数据,假设数据如下
    ctime, id
    1, 8
    1, 9
    2, 10
    2, 11
    3, 12
    3, 13

    如果按照 order by ctime desc, id asc 的话查询结果是 ( 3, 12 )
    后续页面按照 "order by ctime desc, id asc where ctime < ${最后一条记录的 ctime} and id < ${最后一条记录的 id}" 的话查询结果是 ( 2, 10 )

    所以我建议查询方式是 order by ctime desc, id desc,第一页查询结果是 ( 3, 13 )
    后续页面按照 "order by ctime desc, id desc where ctime <= ${最后一条记录的 ctime} and id < ${最后一条记录的 id}" ,查询结果是 ( 3, 12 ),特别注意那个等号
    Aresxue
        6
    Aresxue  
       2020-04-12 21:58:43 +08:00
    查询语句可以带主键(趋势递增才可以), 比如原来的语句是 select * from table limit 1000,10(单页 10 条), 现在给改成
    1.select * from table where id >= (select id from table 1imit 1000,1) limit 10
    2.Select id from table limit 10000, 10;Select * from table where id in (123,345....);
    3.select * from table INNER JOIN (select id from table limit 10000,1) using (id);
    4.在代码里记录最后一条的 id, select * from table where id >= ? limit10;
    简单来说就是只扫描主键不扫描行记录, 减少一次 B+树中的搜索
    hooopo
        7
    hooopo  
       2020-04-12 22:14:25 +08:00
    现在还有人用 myisam ?
    mornone
        8
    mornone  
       2020-04-12 22:19:35 +08:00
    @Aresxue 这个方法是可行的,使用主键条件缩小查询范围
    eq06
        9
    eq06  
       2020-04-12 22:21:37 +08:00
    另建分页表,将分页和时间戳关联起来
    iffi
        10
    iffi  
       2020-04-12 22:24:29 +08:00   ❤️ 1
    大数据量下分页优化 - 延迟关联

    select id,name from user limit 500000,10
    优化后:
    select id,name from user u inner join (select id from user limit 500000,10)as tmp
    on tmp.id = u.id
    littlewing
        11
    littlewing  
       2020-04-12 22:37:42 +08:00
    有一个疑问,InnoDB 下,select * from t order by idx_1 limit 100, 10 这种语句,idx_1 是索引,扫描前 100 行的时候会回表扫描主键索引拿到所有列吗?还是只是扫描 idx_1 这个索引的前 100 行,然后后面从 101 行开始取 10 行再分别回表用 id 去主键索引拿到所有列的值?
    xiaoidea
        12
    xiaoidea  
       2020-04-13 00:07:04 +08:00
    @iffi 这是啥原理,mark 下明天试试
    soseek
        13
    soseek  
       2020-04-13 02:08:07 +08:00 via Android
    @xiaoidea 减少了数据体积了👀
    bigbigroll
        14
    bigbigroll  
       2020-04-13 08:14:08 +08:00 via Android
    @hooopo 可能数据库做了读写分离?
    ddup
        15
    ddup  
       2020-04-13 08:48:41 +08:00 via Android   ❤️ 1
    用分区表 根据时间范围建立分区表试试
    atonku
        16
    atonku  
       2020-04-13 08:50:09 +08:00
    全查出来,代码分
    ohao
        17
    ohao  
       2020-04-13 08:57:50 +08:00
    my.oschina.net/luanwu/blog/3138110
    可以参考这个

    上次这么多数据还是弄过期域名的时候 亿级的
    min max 的方式来分页 效率还可以的
    luckylvke
        18
    luckylvke  
       2020-04-13 08:58:34 +08:00
    @xiaoidea 只查主键索引减少回表,缩小范围后,内连出来(我猜的。。。
    hyd8323268
        19
    hyd8323268  
    OP
       2020-04-13 09:28:49 +08:00
    @iffi 最重要的问题是要排序,需要根据时间戳排序,并且时间戳是有重复的。
    用你优化后的 sql 中的子查询的话是不用回表的,但是如果得加排序。
    select id,name from user u inner join (select id from user order by 时间戳 desc,id asc limit 500000,10)as tmp
    on tmp.id = u.id 。这样的话覆盖索引是不生效的,特别慢。
    pumily
        20
    pumily  
       2020-04-13 09:35:28 +08:00
    我前几天刚遇到过,但是数据量还没楼主的那么大,但是感觉可以提供一个参考思路。
    https://blog.csdn.net/qq_41348754/article/details/105422383
    hosaos
        21
    hosaos  
       2020-04-13 09:40:10 +08:00
    @gz911122 按时间这样是不对的 时间重复的时候无法处理 可以按 id 来
    hyd8323268
        22
    hyd8323268  
    OP
       2020-04-13 10:03:25 +08:00
    @hosaos 我用 select id from 表名 where id < [上一页最小 id] order by [时间] desc,id asc limit 10; 这样查的话相反了,越到后面页越快,第一页大概需要 2s 左右,还在想有没有更好的办法呢。
    hyd8323268
        23
    hyd8323268  
    OP
       2020-04-13 10:08:50 +08:00
    @jugelizi 如果要支持直接跳转到某页的话,还得保证 id 连续性,才能计算准确起始 id
    cloudzhou
        24
    cloudzhou  
       2020-04-13 10:09:02 +08:00
    select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10;
    如果我没有猜错的话,走到了 主键 id 的索引,建立 create index xxx on table (时间戳 desc , id asc)
    然后 select 字段 from 表名 (force index xxx)
    hyd8323268
        25
    hyd8323268  
    OP
       2020-04-13 10:13:58 +08:00
    @cloudzhou 没走索引
    cloudzhou
        26
    cloudzhou  
       2020-04-13 10:15:57 +08:00
    @hyd8323268 那肯定要走索引啊
    b821025551b
        27
    b821025551b  
       2020-04-13 10:21:40 +08:00
    @xiaoidea #12
    @soseek #13

    offset 越大,主键索引访问越频繁,对磁盘 IO 占用越高,这种优化就是干掉频繁的主键索引;
    不过 @iffi #10,LZ 所提的是 MyISAM,这种优化只在 InnoDB 下有效。
    c4pt0r
        28
    c4pt0r  
       2020-04-13 10:24:45 +08:00
    其实用 tidb 会好一些...
    encro
        29
    encro  
       2020-04-13 10:31:43 +08:00
    基本是无解的:
    1,count 本来就慢,无解;
    2,采用>,<限制查询范围,如果需要精准就无解;
    3,采用按时间分区表,也许有用,试试看,估计效果不会很明显,写入性能下降;

    所以如果能业务限制采用 2,如果机器资源没有限制采用 3,如果需要实时 count 则需要采用缓存小范围 count
    cloudzhou
        30
    cloudzhou  
       2020-04-13 10:33:14 +08:00
    @c4pt0r 那是屠龙术,千万数据 mysql 还是可以的
    Jooooooooo
        31
    Jooooooooo  
       2020-04-13 10:38:28 +08:00
    带条件的大分页是无解问题

    只能妥协

    有种办法是用 id 作为游标去翻页, 比如现在这一页最大 id 是 A, 那么下一页就是 id>A
    barbery
        32
    barbery  
       2020-04-13 10:41:16 +08:00
    改成走游标就行啦
    brader
        33
    brader  
       2020-04-13 10:44:31 +08:00
    请问你是执行 select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 的时候慢,还是获取表的总行数的时候慢?可以提供你的具体分页需求吗?是只做下一页,还是需要做页码的?
    就我所知,千万级,select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 的效率还是能接受的
    dizun
        34
    dizun  
       2020-04-13 10:47:20 +08:00 via Android
    redis 吧。然后按照楼上说的用程序分。这种级别的用缓存我认为是最优。另外楼上也提到了 io 问题,注意备份数据库。
    hyd8323268
        35
    hyd8323268  
    OP
       2020-04-13 11:00:47 +08:00
    @brader 执行第一条 sql 时慢,myisam 是自动记录总条数的。下一页和跳转指定页都要有的。如果不加时间戳排序的话是走索引的,所以快,但是时间戳是有重复的所以走不了索引了。我目前只能考虑用 id 游标来解决,应用层稍微麻烦点。
    hyd8323268
        36
    hyd8323268  
    OP
       2020-04-13 11:09:36 +08:00
    @odirus 这种方法的话前几页会查询比较慢,第一页大概需要 2s,页码越大越快。总体来说还可以接受吧。而且,已经根据时间戳排序了,where 中就不需要再加时间戳了吧我觉得。跳转指定页也是个问题 .... 谢谢了
    chq3272991
        37
    chq3272991  
       2020-04-13 11:26:40 +08:00
    可以尝试下,底层基于日期分区,将大表分区为多个小分区,按日期查询的时候尽量能定位到其中一个分区
    iffi
        38
    iffi  
       2020-04-13 11:32:38 +08:00
    @xiaoidea 看一下 Clustered Indexes 和 Secondary Indexes 的区别就明白了
    iffi
        39
    iffi  
       2020-04-13 11:35:19 +08:00
    @hyd8323268 排序也可以走索引,你需要排序的字段要用到索引覆盖才行。explain 一下,看看 Extra 有没有用到 Using temporary 、Using filesort,最好用到 Using Index
    gz911122
        40
    gz911122  
       2020-04-13 11:46:09 +08:00
    @hosaos 可以的,你仔细看一下,重复也无所谓的
    iffi
        41
    iffi  
       2020-04-13 11:46:27 +08:00
    @#27 楼 确实,在 MyISAM 下,优化不明显。看看能不能在业务层面优化,优化思路无非就是不查、少查、走索引、少取
    lasuar
        42
    lasuar  
       2020-04-13 14:22:35 +08:00
    通过子查询优化
    select * from table where id>=(select id from table limit 10000,1) limit 100
    另外大表慎用 select *,一般都查询指定字段,把这些经常查询的字段和 where 的条件字段建立复合索引,避免回表,就最大化利用索引了。
    hyd8323268
        43
    hyd8323268  
    OP
       2020-04-13 15:45:35 +08:00
    @lasuar 重点是排序,我知道这些的
    brader
        44
    brader  
       2020-04-13 15:50:23 +08:00
    @hyd8323268 如果你需求一定要时间排序的话,我觉得你可以试试,给时间戳建立索引,然后使用微秒级时间戳,看下这样能不能避免时间戳太多重复的情况?这个就要看你业务了
    laoba
        45
    laoba  
       2020-04-13 16:09:01 +08:00
    select * from articles where id between (select id from articles limit 170000,1) and (select id from articles limit 170500,1)
    感觉还是这个靠谱
    xiaochun41
        46
    xiaochun41  
       2020-04-13 16:26:44 +08:00
    单从技术上讲,可能没有太好的解决方案。
    根据我个人的经验看,可以从业务的角度做一些优化或者妥协。
    比如:真的需要一页一页翻么?
    weiqk
        47
    weiqk  
       2020-04-13 17:34:03 +08:00 via Android
    不要脱离业务谈技术,结合实际业务才有解决办法,是什么类型的数据?日志还是财务数据
    hosaos
        48
    hosaos  
       2020-04-13 19:44:49 +08:00
    @gz911122 假设数据如下,分页 pageSize 也是 1,不针对 id,只针对时间是查不出 id 为 2 的数据的,第二页的条件不是 time<2019-07-12 15:14:42 limit 1?
    id time
    1 2019-07-12 15:14:42
    2 2019-07-12 15:14:42
    3 2019-07-12 15:14:42
    hyd8323268
        49
    hyd8323268  
    OP
       2020-04-13 20:27:48 +08:00
    @weiqk 日志类的,访问记录,也就在后台看看,目前没有删除功能。
    weiqk
        50
    weiqk  
       2020-04-14 05:04:38 +08:00 via Android
    @hyd8323268 上 es
    gz911122
        51
    gz911122  
       2020-04-14 09:53:12 +08:00
    @hosaos id 也要带上的,你看下我回复的#4 楼
    qyvlik
        52
    qyvlik  
       2020-04-14 09:58:53 +08:00   ❤️ 1
    先为表建好时间戳的索引,时间戳字段是否唯一,关系不大。只要不是说 800w 记录,时间戳的取值集合就百来个,这个时间戳就有建立索引的价值。

    1. 摈弃传统的精确分页,直接走瀑布流加载方案
    - 取第一页:select * from logs order by create_time desc limit 10
    - 可以在前端将 min(create_time) 取出,作为第二第三页的查询条件
    - select * from logs where create_time < pre_min_time order by create_time desc limit 10

    2. 仍然想使用精确分页
    1. 使用时间区间将要扫描的条数减少,例如 一天的日志数量
    2. 使用一些 sql 查询进行优化,例如 max, min 查找出对应的时间范围、id 范围

    3. 使用其他的查询方案,例如 es 、mongo 、redis

    ps: 如果一些索引效果不明显,考虑将 myisam 表数据导入新的 InnoDB 的表,在 InnoDB 表上建立索引。
    neverxian
        53
    neverxian  
       2020-04-14 11:10:20 +08:00
    看一看
    hosaos
        54
    hosaos  
       2020-04-15 09:18:58 +08:00
    @gz911122 id 带上就没问题了 但是这种时候 带上时间条件是多余的把 带 id 必须按 id 排序 否则按时间排序 id 不连续 没有意义
    gz911122
        55
    gz911122  
       2020-04-15 10:12:31 +08:00
    @hosaos 不多余,先时间排序再 id 排序, 有意义的. 是为了避免重复问题.
    tmackan
        56
    tmackan  
       2021-06-17 20:25:35 +08:00
    比如要导出全部账单的话
    1.传统分页的话,offset 可以算出来,这样可以精确分页,但是 offset 越大越慢,调用方第一次拿到 total 总数后,可以并发的请求后续分页数据
    2.游标的方式的话,利用 select * from table where id < cursor order by time desc limit 1000
    调用方通过串行的方式,每次用前一页的最小 ID 来作为下一页的 cursor
    优点是可以保证数据不重复,缺点是无法并行请求,只能串行每次获取游标

    折中的做法
    调用方自己计算 cursor 并且并行的请求,后台还是通过游标的方式处理
    但是数据重复的问题,需要调用方 去重
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3224 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 14:11 · PVG 22:11 · LAX 06:11 · JFK 09:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.