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

想听听大家对分页 sql 的看法

  •  
  •   zazalu · 2019-08-28 18:07:16 +08:00 · 5564 次点击
    这是一个创建于 1674 天前的主题,其中的信息可能已经有所发展或是发生改变。

    开发至今一直写 crud 的菜鸟, 想优化下分页 sql, 从网上得知几种方式经过我自己的测试觉得效果不是很明显(不知道是不是我测试存在问题.)

    自己的练习系统一直用的 limit ${pager.start}, ${pager.size}这样的写法, 但是好像数据超过百万后,就很难在 1s 以下完成查询了..

    特别想知道大佬们在维护的实际项目的分页都是怎么做的呢?

    18 条回复    2019-08-29 14:32:59 +08:00
    w4n9hu1
        1
    w4n9hu1  
       2019-08-28 18:15:49 +08:00
    昨天看了一个思路,查询带上 sortname 的 maxvalue,分页查询 sql 加上>maxvalue。
    ikaiguang
        2
    ikaiguang  
       2019-08-28 18:17:09 +08:00
    jaskle
        3
    jaskle  
       2019-08-28 18:26:48 +08:00 via Android
    性能最好的分页是>上一页最后一个 id limit x,可以参考一下 GitHub 的 release 分页,看看 url。
    但是这种分页不能任意跳页,适合手机动态加载列表
    1762628386
        4
    1762628386  
       2019-08-28 18:29:19 +08:00   ❤️ 6
    指导思想:增加有效可利用的索引,其目的是减少每次查询时的系统负载。

    0.用固定页码做索引: 前提条件是页码总数只增不减少, pageTotal%pageSize 例如第 1-100 条数据的索引就是 index_page_1,以此类推.
    1.每次携带最大的主键 id,使其查询时带上 id :例如: where id>{maxId} limit 10
    2.优化:根据现有业务按列分表,将用于排序的列数做最小拆分,尽量不要有大规模字符串,查询后用 in(ids)方式聚合
    3.增加一个扩展表,用于分页: pagination(id,page_number) 原理同第 0 条
    Takamine
        5
    Takamine  
       2019-08-28 18:29:26 +08:00 via Android   ❤️ 1
    一般就用插件,比如 pagehelper。
    jaskle
        6
    jaskle  
       2019-08-28 18:31:28 +08:00 via Android
    以前公司用游标分页体验非常好他就是数据库查询完以后连接不断,然后前后移动来完成,查询和翻页巨快。
    就是有点儿浪费数据库连接。同时在线人数多了就撑不住。
    Varobjs
        7
    Varobjs  
       2019-08-28 20:17:29 +08:00 via Android
    4 楼给出了解决办法
    就是加 主键 ID,每次限制在 ID 一定范围内再分页
    五百万数据也很快分页查完
    tonghuashuai
        8
    tonghuashuai  
       2019-08-28 22:37:42 +08:00 via iPhone
    4 楼正解
    cuvii
        9
    cuvii  
       2019-08-29 08:58:19 +08:00
    同菜鸟,直接用 limit 加 offset 来查询,不过这样有点跑题,并不能分出页码
    avenger
        10
    avenger  
       2019-08-29 09:18:02 +08:00
    这样试试速度

    ```
    result = SELECT id FROM table WHERE xxx limit x,y;
    SELECT * FROM table WHERE id IN(result)
    ```
    zazalu
        11
    zazalu  
    OP
       2019-08-29 10:52:14 +08:00
    @avenger 这种我测试过, 提升速度不太明显 耗时主要还是 limit 那里的问题. 4 楼给出的答案非常不错
    zazalu
        12
    zazalu  
    OP
       2019-08-29 10:59:14 +08:00
    @1762628386 非常感谢, 一开始没看懂, 现在明白了. 我这边写点小总结,可以帮忙看看对不对吗?

    分页优化思路:
    1. [牺牲空间法]:
    额外维护`page`的列或者 pagination(id, page)的表, 这样便可以直接使用如下的 sql 来让提速
    ```
    SELECT xxxx
    FROM xxx
    WHERE page = 29 -- 查询第 29 页数据
    ```
    但是这种方式有个明显缺点, 你需要去维护这些属性, 对于有些可以任意修改 pageSize 或者表的行数据是支持删除的系统, 不适用

    2. [键集分页]

    也就是所谓的每次携带上一页的最大 id, 通过`where id >= xxx`这样去直接缩小扫描范围,从而提速

    3. [切分优化]

    感觉这个有点难, 非常依赖实际情况, 我先待定, 不总结...
    zazalu
        13
    zazalu  
    OP
       2019-08-29 11:00:13 +08:00
    此外, 我个人测试了下网上一些, 比如使用 join, 或者反转法 . 基本都是没啥大提升的, 建议大家不要盲目相信
    leafre
        14
    leafre  
       2019-08-29 11:07:06 +08:00
    pagehelper
    1762628386
        15
    1762628386  
       2019-08-29 11:48:36 +08:00
    @zazalu 有个最简单的:利用 mysql 的索引覆盖 (using index),直接从索引中查询并返回结果
    例如:
    表:user(id,gender,nickname,cityId)
    索引:index(cityId);
    查询:where cityId=1 的所有用户并分页
    查询语句 select id,cityId from user where cityId=1
    只要 where 条件的列和索引的列一致,就能直接从索引中返回数据,还是很快的,但查找的行数(rows)还是很多
    zazalu
        16
    zazalu  
    OP
       2019-08-29 13:58:46 +08:00 via Android
    zazalu
        17
    zazalu  
    OP
       2019-08-29 14:01:04 +08:00 via Android
    @1762628386 我人晕了 晚上研究下。。。
    1762628386
        18
    1762628386  
       2019-08-29 14:32:59 +08:00
    @zazalu 就想办法减少 explan 时的 rows 就行了,空间换时间
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5303 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 09:05 · PVG 17:05 · LAX 02:05 · JFK 05:05
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.