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
Aluhao
V2EX  ›  MySQL

单表三千万数据分页优化方案

  •  
  •   Aluhao · 2017-08-09 15:08:17 +08:00 · 7988 次点击
    这是一个创建于 2424 天前的主题,其中的信息可能已经有所发展或是发生改变。
    直接用变通的查询方式,查询时间高的吓人,因为有些数据查询是不连贯的,不能用大于多少或等于多少来查询;
    [sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 199980, 20;
    [time] => 0.438656
    [sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 1999980, 20;
    [time] => 26.835051
    [sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 19999980, 20;
    [time] => 31.343988
    [sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 29999980, 20;
    [time] => 32.138655

    现在的做法是通过先查询取出 id
    SELECT id FROM `article_comments` LIMIT 19999980, 20;

    然后用 id 去取数据
    SELECT * FROM `article_comments` WHERE id IN('1','2'....);
    虽然这样优化了很多,但是也不是很理想,如果取的 ID 间隔大,也会进行全表扫描;
    看 V2 能人很多,不知道还有没更优化的方法?
    36 条回复    2017-08-30 06:38:14 +08:00
    af463419014
        1
    af463419014  
       2017-08-09 15:20:36 +08:00   ❤️ 3
    数据库查询规范第一条 : 绝对不允许使用 select *
    yzongyue
        2
    yzongyue  
       2017-08-09 15:27:05 +08:00
    在实际的项目中会有这种需求么
    > SELECT id FROM `article_comments` LIMIT 19999980, 20;
    连个 where article_id = xxx 都没有
    Aluhao
        3
    Aluhao  
    OP
       2017-08-09 15:30:32 +08:00
    @yzongyue 上面只是一个显示所有留言的列表,等同于留言管理吧,加判断也是有需求的;
    cxh116
        4
    cxh116  
       2017-08-09 15:34:03 +08:00
    改分页查询条件吧, limit offset 几十万,能不慢.
    用当前页最大评价 id ,下一页查询大于此 id 即可.

    https://juejin.im/entry/5865c81fac502e006d5e72de
    Aluhao
        5
    Aluhao  
    OP
       2017-08-09 15:36:41 +08:00
    @cxh116 这方法不适用有些场景的,如,需要查询用户回复的留言,这个留言 ID 非顺序的,很乱的,在几千万条数据中断断的出现几千条;
    w0000
        6
    w0000  
       2017-08-09 15:40:14 +08:00
    @Aluhao 这个 id 是建了索引的么,这么慢吗
    Aluhao
        7
    Aluhao  
    OP
       2017-08-09 15:41:18 +08:00
    @w0000 肯定建了索引了,因为数据太多了,单表三千多万,如果只是几十万就很快。
    XiaoFaye
        8
    XiaoFaye  
       2017-08-09 15:45:06 +08:00   ❤️ 1
    论一定时间后关闭评论的重要性
    nullcc
        9
    nullcc  
       2017-08-09 15:46:32 +08:00   ❤️ 1
    采用 limit offset 这种方式,如果表数据量一大就会坑爹,如果索引没建好,一个很大的 offset 需要让数据库先扫描前面的很多数据,到后期效率和全表扫描也没什么区别了。
    你可以考虑在 article_comments 的创建日期上创建聚集索引,文章评论满足“既不能绝大多数都相同,又不能只有极少数相同”的条件,比较适合在这种列上创建聚集索引。查询的时候可以根据日期先做筛选,然后再跟上你的其他查询条件。
    Aluhao
        10
    Aluhao  
    OP
       2017-08-09 15:46:37 +08:00
    @XiaoFaye 这个不太好吧
    nullcc
        11
    nullcc  
       2017-08-09 15:48:41 +08:00
    补充下,文章评论的创建日期满足“既不能绝大多数都相同,又不能只有极少数相同”的条件,上面笔误。
    Sunshow
        12
    Sunshow  
       2017-08-09 15:54:20 +08:00   ❤️ 1
    把翻页都通过 id > 的条件转换成取第一页的请求
    sujin190
        13
    sujin190  
       2017-08-09 15:55:25 +08:00   ❤️ 1
    大量数据要翻页一般不使用页码,而是使用游标,游标那么久可以包含很多信息了,当前页码,翻页方向,当前 id 值,每页条数等等,使用的方式 where id>last_id offset per_page_count,这样就可以充分使用索引来翻页了
    Immortal
        14
    Immortal  
       2017-08-09 15:59:28 +08:00
    @Aluhao 上面说的根据 id 大于的方式查询 我想了下没问题吧 虽然是断断续续的 limit 后数量还是准确的吧?
    daimazha
        15
    daimazha  
       2017-08-09 16:16:38 +08:00
    @Immortal #14 没问题, 而且这么多数据 可以考虑分表了。
    vipvideoshare
        16
    vipvideoshare  
       2017-08-09 16:18:47 +08:00   ❤️ 1
    试试给 id 加反向 index
    ```
    CREATE INDEX id_desc_index ON article_comments (id DESC)
    ```
    Immortal
        17
    Immortal  
       2017-08-09 16:20:12 +08:00
    @daimazha 是的 如果用户 id 顺序 可以直接取模分表 也简单的
    zhaopengme
        18
    zhaopengme  
       2017-08-09 16:27:49 +08:00   ❤️ 1
    肯定是根据业务场景来优化了,单纯的用 sql 么有意义.
    eg.
    1. 比如把数据分为常用数据和历史数据
    2. 比如做数据统计,增加中间表
    3. 数据快速处理,使用数据库编程,游标很有效率
    等等等,具体问题具体对象.
    gouchaoer
        19
    gouchaoer  
       2017-08-09 16:38:39 +08:00   ❤️ 1
    select * 没有问题,limit 那种写法不可以,你需要这样给 article_comments 弄个自增的 int 健,然后 select * frome table where id_inc > 10000 and id_inc<10100;
    suconghou
        20
    suconghou  
       2017-08-09 17:43:22 +08:00
    ID 加索引了吧,可以这样 分两次查询 select id from mytable order by id limit 13456901,1; 先查出分页的首页的 ID

    得到 ID x

    然后 select * from mytable order by id where id > x limit 10;
    得到数据.

    由于 ID 有索引,整体时间就在于第一条 SQL 的时间. 性能能提升 2-3 倍.
    bolink5
        21
    bolink5  
       2017-08-09 20:24:47 +08:00
    有一家比较大的公司做法是,把主键 id 放在 property 文件中,作用是存放 id
    sql 是 select * from xx where id>'' limit yy 做完了后 更新 id 到文件中
    id 的效率是最高的
    wsc449
        22
    wsc449  
       2017-08-09 22:57:49 +08:00
    给出业务场景才有优化方案
    dexterzzz
        23
    dexterzzz  
       2017-08-09 23:23:47 +08:00
    有列存储索引的话,不叫个事
    sagaxu
        24
    sagaxu  
       2017-08-09 23:57:34 +08:00 via Android
    先确保内存够大,MySQL 配置够大,index 能全部载入内存,并且还有富余。哦,MySQL 要用 5.6 以上版本,引擎用 innodb,然后再来解决 late row lookup 的事情。
    syncher
        25
    syncher  
       2017-08-10 00:03:23 +08:00 via Android
    1. 大数据下分页舍弃精度换效率,一般都用 where id > 199980。
    2. 可以试试可以延迟关联,
    select * from tableName inner join (select id from tableName limit 199980,20) as tmp using(id);
    3. 如果一定要追求精度建议数据不进行物理删除,删除时做逻辑删除,这样 id 就不会缺失。
    syncher
        26
    syncher  
       2017-08-10 00:32:58 +08:00   ❤️ 1
    > 现在的做法是通过先查询取出 id
    SELECT id FROM `article_comments` LIMIT 19999980, 20;

    另外,如果这条 SQL 查询速度很慢那么是不是因为你使用的是 InnoDB 引擎,InnoDB 下默认为聚集索引,聚集索 ID 和数据存一起,所以通过 id 查询很慢,如果是这样可以考虑在其他列和 id 列上建立联合索引,查询时使用索引覆盖。

    ALTER TABLE article_comments ADD INDEX id_c1(id,c1);

    SELECT id FROM `article_comments` ORDER BY id,c1 LIMIT 19999980, 20;
    AsterOcclu
        27
    AsterOcclu  
       2017-08-10 01:20:08 +08:00   ❤️ 1
    你可以参考一下这篇文章: https://github.com/zhangyachen/zhangyachen.github.io/issues/117
    可以通过 inner join 的方法来解决
    Romanticlizhi
        28
    Romanticlizhi  
       2017-08-10 09:00:44 +08:00
    in 在大数据量的时候本来就不建议使用,而且再纠正一点,并不是所有情况下都是用索引扫描最快,当你需要查询的数据超过总数据一定百分比,使用全表扫描肯定是比索引扫描快的。
    Aluhao
        29
    Aluhao  
    OP
       2017-08-10 09:28:28 +08:00
    @Romanticlizhi 经过测试用 IN()取值很快呀。
    liuanxin
        30
    liuanxin  
       2017-08-10 10:27:19 +08:00
    你开下查询分析看下时间是消耗在哪里了

    SET profiling=1;
    SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 1999980, 20;
    SHOW PROFILE;
    SET profiling=0;

    in 里面如果放的是实际的值而不是 in (select xxx ...) 这种, 性能是很快的.
    syncher
        31
    syncher  
       2017-08-10 11:53:01 +08:00 via Android
    等优化好了期待楼主给个最终方案供大家学习
    luw2007
        32
    luw2007  
       2017-08-10 16:16:45 +08:00
    id 保证连续。最好可以外部生成。
    多维度的数据索引直接放在 redis 的 sorted_set 中。 用空间换时间,提前放好所有查询用到的维度。
    按照类别,按照用户,等等。
    目前线上一个 UGC 自增 id 马上到 1 亿,
    stabc
        33
    stabc  
       2017-08-10 17:30:11 +08:00
    >LIMIT 29999980, 20
    这种查询相当于读取数百万条数据,肯定慢。

    >因为有些数据查询是不连贯的
    差值不大的话,建议还是用“大于号小于号”先取出一部分数据(比如范伟控制在 100 ),然后因为差值可能只拿到了 60 条,然后从这 60 条取前 20 条。 这样做可以把每次查询都控制在毫秒级别。不过需要每次分页时带入一个参数,用来记录上次取到的最大 id。
    stabc
        34
    stabc  
       2017-08-10 17:32:45 +08:00
    如果是条件稍微复杂的查询,也可以通过上次记录的 id 值,估算出 10000 条记录区间,然后在这 10000 条里面做查询。也比直接查快上很多。
    opengps
        35
    opengps  
       2017-08-30 06:35:28 +08:00 via Android
    对表进行表分区,这样查询就成了查询多个小表再组合结果的效果
    opengps
        36
    opengps  
       2017-08-30 06:38:14 +08:00 via Android
    忘了说了,我的坐标表最多时候 10 亿行数据,无 id 设计,聚集索引用写入时间倒序(优化写入速度)。然后只给设备码和 GPS 时间列建立索引(优化读取速度)。用的时候这张表也规定只使用这么单一的查询条件
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5341 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 39ms · UTC 08:09 · PVG 16:09 · LAX 01:09 · JFK 04:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.