首页 注册 登录
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
广告
stevenkang
V2EX  ›  MySQL

苦逼，下班了，遇到一个 SQL 查询慢的问题，和同事讨论很久，结果用代码解决了

  •   
  •   stevenkang · 2020-04-01 20:04:39 +08:00 · 10266 次点击
    这是一个创建于 1704 天前的主题，其中的信息可能已经有所发展或是发生改变。

    情况是这样的，mysql 5.7.14-log，有两个表，A 表 2000w 条数据，B 表 1000w 条数据。

    现有需求：select a.x, b.x from A a left join B b on a.bid = b.id where a.time >= xxx and b.time <= xxx limit 0, 100

    a.bid 、a.time 有索引的情况下，查询一次 5000ms 左右，查询频繁了 10000ms+ 导致接口超时。

    和同事讨论了很久，各种 sql 子查询什么的都很慢。最后想到用 in 的方式程序拼接 sql 语句。

    于是用了下面的流程：

    select a.x, a.bid from A a where a.time >= xxx and b.time <= xxx limit 0, 100

    程序将上面查询的 a.bid 拼装为 sql： select b.id, b.x from B b where b.id in (xx1, xx2...xx100)

    然后程序中用 b.id 作为 key，b.x 作为 value 建立映射

    再次循环查询 A 的数据，将 b.x 通过程序设置进去，然后统一返回。

    目前这种方案查询下来 50ms，不知道有什么隐患没。

    第 1 条附言  ·  2020-04-02 16:06:53 +08:00
    抱歉各位，之前笔误 a.time 打成了 b.time，需求是按照 a.time 进行时间范围筛选（范围不超过一天）

    执行计划如图：
    https://i.loli.net/2020/04/02/NzGIJ5w4HLv9dlM.png


    idx_con_ext 为聚合索引，b.id + 另外一个字段，b.id 索引在前，单独查 b.id 的时候索引有效
  • SQL
  • 查询
  • Time
  • select
    57 条回复  •  2020-04-03 10:01:40 +08:00
    TZ
        1
    TZ  
       2020-04-01 20:30:24 +08:00
    你单查 a 表还能检索 b.time ？ a.bid 、a.time 联合索引一下试试？
    lurenw
        2
    lurenw  
       2020-04-01 20:42:09 +08:00
    当 a 表中的 bid 在 b 表里不存在的时候, 不就查不够 100 个了么
    InternetExplorer
        3
    InternetExplorer  
       2020-04-01 20:42:16 +08:00
    应该没啥隐患，就是以后把这两个表放到两个数据库都不用改 SQL
    stevenkang
        4
    stevenkang  
    OP
       2020-04-01 20:43:02 +08:00
    @TZ 打错了，应该是用 a.time 进行时间范围筛选，a.bid 、a.time 有单独的索引，聚合索引我试试。之前的 sql 通过 explain 查看索引用上了，可查询出来就是慢。。。
    fortunezhang
        5
    fortunezhang  
       2020-04-01 20:53:27 +08:00
    同意二楼的说法。同时分页也是一个问题。
    ElmerZhang
        6
    ElmerZhang  
       2020-04-01 20:58:38 +08:00
    如果你前后 SQL 里的 b.time 都是打错了，应该是 a.time 的话，那么拆开写完全没问题。甚至很多大厂的规范中会要求必须拆开写，理由如 #3 所讲，即使未来 a 表和 b 表拆到两个库中，SQL 都不用改。
    ElmerZhang
        7
    ElmerZhang  
       2020-04-01 21:00:33 +08:00
    @lurenw @fortunezhang 楼主原 SQL 中就是 left join，是允许 b 表里不存在的。
    TZ
        8
    TZ  
       2020-04-01 21:00:51 +08:00
    @fortunezhang 想多了，没啥问题，left join 有关联行就塞，没关联行就不塞，又不是 inner join 。分 2 次写更好，联合索引都不用建，只是多一次网络请求，业务逻辑层做关联，减少关联查询 mysql 负担
    kawowa
        9
    kawowa  
       2020-04-01 21:01:19 +08:00 via Android
    select b.id, b.x from B b where b.id in (
    select a.bid id from A a where a.time >= xxx and a.time <= xxx limit 0, 100
    )
    不知道 MySQL 能不能这样用？
    TZ
        10
    TZ  
       2020-04-01 21:02:51 +08:00   ❤️ 1
    @kawowa in 子查询不走索引
    fortunezhang
        11
    fortunezhang  
       2020-04-01 21:12:46 +08:00
    @TZ 学到了
    fortunezhang
        12
    fortunezhang  
       2020-04-01 21:12:59 +08:00
    @ElmerZhang 疏忽了。尴尬
    areless
        13
    areless  
       2020-04-01 21:53:27 +08:00 via Android
    EXISTS 试试
    cgh
        14
    cgh  
       2020-04-01 21:59:42 +08:00 via Android
    可以做子查询先查出符合条件的 100 条 a. id 和 b. id 再连接 a,b 两个表查询，减少回表次数。
    leon0903
        15
    leon0903  
       2020-04-01 22:01:39 +08:00
    其实我心中一直有一个疑问， 就是原来的复合 sql 拆分为单表之后，分页是要怎么做？ 很多人懂不懂就说不要复杂查询 要分单表 但是这其中的分页怎么处理却从来没提过。 难道都是不分页的么。。。。
    djoiwhud
        16
    djoiwhud  
       2020-04-01 22:24:21 +08:00
    Navicat 分析看看执行过程呢。不一定是索引执行的，有可能全表扫描了，你不知道而已。
    ffeii
        17
    ffeii  
       2020-04-01 23:23:18 +08:00 via iPhone
    我经常用的两种方式
    1 、子查询：
    select a.x, (select x from b where id=a.bid) from A where a.time >= xxx and a.time <= xxx limit 0, 100
    2 、禁止表关联，禁止 for 循环中查询：
    查 a 表，得到分页 list
    遍历得到 a.bid 的 set
    in b 表，得到 list，转 map
    再遍历
    weizhen199
        18
    weizhen199  
       2020-04-01 23:42:36 +08:00 via Android
    mysql 用的不多，但是最好贴一个执行计划看看，还有 in 子查询 mysql 不走索引？
    stabc
        19
    stabc  
       2020-04-01 23:50:04 +08:00
    最近用 nosql 用多了，习惯设计表是就灵活一些，不怕重复。你这个案例，我会在 a 表新加一个 b_time 字段方便查询优化。
    codelover2016
        20
    codelover2016  
       2020-04-02 00:10:34 +08:00
    @leon0903 看情况，拆表的情况下，分页是很蛋疼的。我这边的方案是，做一个逻辑视图来解决分页问题，它甚至可能是个内存分页，查到数据 Id 之后再去查数据。
    不过实际应用中，我这里做了是分区表或者做统计表解决。
    sagaxu
        21
    sagaxu  
       2020-04-02 00:59:38 +08:00 via Android
    @weizhen199 in subquery 情况比较复杂，跟 mysql 版本还有关系
    JamesR
        22
    JamesR  
       2020-04-02 05:53:23 +08:00
    数据库尽量别用 left join，慢。
    horkooo
        23
    horkooo  
       2020-04-02 08:13:21 +08:00 via Android
    类似这样查询慢，我一般分开不用 join，中间引用 redis 缓存
    xuanbg
        24
    xuanbg  
       2020-04-02 08:29:21 +08:00
    select a.x, a.bid from A a where a.time >= xxx and b.time <= xxx limit 0, 100 这个语句能执行？里面 b.time 哪里来？

    如果 b.time 是 a.time 的笔误，那你这个结果能等价？除非你原先的 sql 里面 b.time 也是 a.time
    raysonlu
        25
    raysonlu  
       2020-04-02 09:23:04 +08:00
    存在隐患，查 b 表的时候，in 部分拼接过长，会导致 sql 语句过长，不过也得看实际情况是否会绝对不超出。
    calmzhu
        26
    calmzhu  
       2020-04-02 09:25:39 +08:00 via Android
    不确定这个版本 MySQL 引擎中 left join 跟 where 的执行数据。

    推测执行过程可能是先对整表执行了 2000w left join 1000w right.的查询。然后才做的 where 过滤。自然是极慢而非必须的。

    合理的流程其实你已经用代码实现了。先对左右表过滤得到两个临时表。然后用这个临时表去 left join 拿数据

    这过程同样可以用 SQL 实现 https://blog.csdn.net/guochunyang/article/details/79236446
    raysonlu
        27
    raysonlu  
       2020-04-02 09:30:55 +08:00
    类似的问题，这个方法不知是否也有帮助：
    select a.x, b.x from (select * from A where a.time >= xxx) a left join B b on a.bid = b.id and b.time <= xxx limit 0, 100
    stevenkang
        28
    stevenkang  
    OP
       2020-04-02 09:43:07 +08:00
    @kawowa 之前试过这种方式，但提示 mysql 版本不支持
    @calmzhu 感谢，我研究一下这个 SQL 实现方式
    @leon0903 分页主要是用 A 表的数据，B 表的数据没有也行，B 表不足 100 条也不影响业务需求
    zivyou
        29
    zivyou  
       2020-04-02 09:43:21 +08:00
    学习了。
    有个小问题要请教下各位大佬(和 lz 问题无关): select b.id, b.x from B b where b.id in (xx1, xx2...xx100) ，in 后面的列表如果很长有影响吗? 最长可以有多长?
    yufpga
        30
    yufpga  
       2020-04-02 09:45:06 +08:00
    a.time 加了索引也没用，innodb 非主键的索引是非聚簇索引, 不支持范围查询的，用这种>, <的应该是不会走索引的, 还是得全表扫描，select a.x, a.bid from A a where a.time >= xxx and b.time <= xxx limit 0, 100 这条 sql 应该是全表扫描的，explain 一下看看就知道了。就单表来说，两张表都不算小，分开写比较合理。之所以原查询慢，是因为两张表的数据 join 在一起太多了，而 innodb 的 buffer pool 远远不够，频繁读取磁盘数据到 buffer pool 这个过程很慢。
    gavinjou818
        31
    gavinjou818  
       2020-04-02 10:02:38 +08:00
    @yufpga 我个人觉得不一定，>,<走不走索引还是得看执行计划，比较赞成是因为 join 问题。我记得好像 sql 真实执行是 from..on..join 开始，感觉这两个表太大，导致的太慢。
    asd123456cxz
        32
    asd123456cxz  
       2020-04-02 10:17:56 +08:00
    @yufpga #30 <>是可以走索引的，叶子节点双链表，in 或者 between 的话要看优化器的想法。确实很有可能是 bufferpool 的问题
    m1ch3ng
        33
    m1ch3ng  
       2020-04-02 10:21:04 +08:00
    a 表 x,bid 和 time 用覆盖索引，可以达到 index 级别
    ![image.png]( )
    starcraft
        34
    starcraft  
       2020-04-02 11:09:08 +08:00
    @m1ch3ng 这个确实算正确解法。但 lz 这个 x，可能代表的是若干个其他字段吧？
    TZ
        35
    TZ  
       2020-04-02 11:11:42 +08:00
    @m1ch3ng 终于回答了我 1 楼的问题了
    TZ
        36
    TZ  
       2020-04-02 11:22:39 +08:00
    @m1ch3ng 额，你这单表啊，能不能像楼主一样 left join 试下
    liprais
        37
    liprais  
       2020-04-02 11:31:38 +08:00
    select a.x, b.x from A a left join B b on a.bid = b.id where a.time >= xxx and b.time <= xxx limit 0, 100
    你们讨论了半天都没发现这里的 left join 其实是 inner join 么.........
    js8510
        38
    js8510  
       2020-04-02 11:44:59 +08:00
    我觉得
    （ 1 ）要考虑 Atomic 的问题吧。 你要把你的 sql 放到一个 atomic block 里面。 另外，如果有 ORM layer 的话，封装的好，我觉得这样做问题不大。
    （ 2 ）另外你的 sql 最后会多长，sql 长度应该是有限制的。要看下 mysql 的限制是多少， 最好封装的时候有个 enforce

    但是最好还是 分析下为什么。比如能不能看到 sql DB 的 log. 查下 sql server 的 iostate 看看慢在哪。如果作为 temporary solution, 我觉得可以。如果是大型服务 hot code path, 这个无端的增加复杂度。以后看起来很麻烦。
    m1ch3ng
        39
    m1ch3ng  
       2020-04-02 12:14:46 +08:00
    @TZ #36
    TZ
        40
    TZ  
       2020-04-02 12:15:30 +08:00
    嗯，本机试了下，联合索引没用，除非 where 语句加上 a.bid=""才能用上。关键还是这个 a.time 的范围查询数据量太大导致的，关联查询不会给你想 limit 100 行数据后再进行 left join b 表，而是先范围查询的全部数据然后去关联查询 b 表数据最后再进行 limit 。https://imgur.com/cuahrVu
    m1ch3ng
        41
    m1ch3ng  
       2020-04-02 12:20:04 +08:00
    单独查 a 是 index 级别，单独查 b 是 range 级别。个人感觉 left join 好一点，因为连接查询是 eq_ref 级别，比单独查 b 的 range 级别好，而且少了一次请求
    reus
        42
    reus  
       2020-04-02 15:25:15 +08:00
    恭喜你，你发明了 hash join 。

    msyql 8 有 hash join 了，不要用 5 了。
    l00t
        43
    l00t  
       2020-04-02 15:30:37 +08:00
    你这语句就是错的……

    还是贴执行计划吧
    krixaar
        44
    krixaar  
       2020-04-02 15:47:08 +08:00
    如果需求是 A 表取 100 条，B 表如果有就带上，没有就没有，那么第一条语句 where b.time <= xxx 限定 b.time 必须有值，left join 就没有 left 的效果了吧？
    stevenkang
        45
    stevenkang  
    OP
       2020-04-02 16:11:12 +08:00
    @reus 老系统，一线码农，没有办法让用 mysql 8

    @l00t 抱歉之前笔误 a.time 打成了 b.time，已经附加了执行计划

    @m1ch3ng 之前也是用的 left join 直接查，索引也加上了，测试环境没问题，结果上了生产查询很慢
    @TZ a.time 的范围不会超过一天，数据大概 7w 左右
    themostlazyman
        46
    themostlazyman  
       2020-04-02 16:13:34 +08:00
    on 后面的连接条件可以把 where 的晒选提前：SELECT a.x, b.x FROM A a LEFT JOIN B b ON a.bid = b.id AND a.time >= xxx AND b.time <= xxx limit 0, 100
    kim01
        47
    kim01  
       2020-04-02 16:44:57 +08:00
    对于这种有数据量的表，单表读取不香吗，尽量单表操作然后再组合数据，就是以后拆分也简单快捷不好吗。。
    themostlazyman
        48
    themostlazyman  
       2020-04-02 17:43:24 +08:00
    @themostlazyman
    再加个 WHERE a.time >= xxx
    TZ
        49
    TZ  
       2020-04-02 18:28:16 +08:00
    你的执行计划太诡异了，你 ref 是 func，难道你还用了函数？
    TZ
        50
    TZ  
       2020-04-02 18:37:41 +08:00
    我就算模拟了你这种 func，我 a 表 800 万，b 表 400 万，查询也才 100ms 左右。
    https://i.loli.net/2020/04/02/ATPIOFkZlWip6gs.png
    RipL
        51
    RipL  
       2020-04-02 19:14:12 +08:00
    @TZ in 不走索引这个不一定吧
    RipL
        52
    RipL  
       2020-04-02 19:20:37 +08:00
    @TZ 你这个用了索引覆盖 还用了 mrr
    RipL
        53
    RipL  
       2020-04-02 19:21:25 +08:00
    @raysonlu 这个可以试试
    TZ
        54
    TZ  
       2020-04-02 19:39:52 +08:00
    @RipL 嗯，不一定。哈哈哈哈，因为我这个数据里面的时间是跟 id 单调递增的，反而关了 mrr 性能更好。索引覆盖？我的 a.x，b.x 怎么被覆盖的。
    https://i.loli.net/2020/04/02/S7OLH231A6tQGNs.png
    RipL
        55
    RipL  
       2020-04-02 20:46:44 +08:00
    @TZ 逃~ 看错了，是索引下推。看楼主的第二个索引比较长，200 多。
    stevenkang
        56
    stevenkang  
    OP
       2020-04-03 09:51:52 +08:00
    @TZ 查询的时候只是简单的 left join，没有用到函数，检查了一下字段，发现 a.bid 长度为 255，b.id 长度为 50，不知道是否因为这个原因导致的索引 ref 为 func
    zwj2885
        57
    zwj2885  
       2020-04-03 10:01:40 +08:00
    搞大数据搞的习惯，如果业务场景是实时查询的，那就把表做分区，或者像你这样，把计算过程放到 java 中。如果是实时要求高，就放到 kafka 这类里面进行计算。如果离线分析就好办了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2593 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 06:26 · PVG 14:26 · LAX 22:26 · JFK 01:26
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.