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 · 10222 次点击
    这是一个创建于 1682 天前的主题,其中的信息可能已经有所发展或是发生改变。

    情况是这样的,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 的时候索引有效
    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   ·   实用小工具   ·   2572 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 15:10 · PVG 23:10 · LAX 07:10 · JFK 10:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.