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

请教 MySql 多表 join 怎么优化好

  •  
  •   MozzieW · 6 天前 · 1921 次点击

    接到一个不确定+复杂的需求(对我而言),背景是: 现在后台有一个用户表 user ( id,orderId),会员订单表 order(id,type,expireTime),用户每日登录日志表 log(id,uid,createdAt);表的行数基本在 100 万(订单表 10 万)。 现在需要可能是:

    1. 找到最近 10 天登录的用户
    2. 或者,最近 10 天登录的会员用户(根据有没有订单 id )
    3. 或者,最近 10 天登录的过期会员用户 ...others 根据条件拿到用户 id 去做其他任务,就是条件可能不一样。
      根据我仅有的 MySql 知识和这两天的 Google,我试了一下实现是,
    select user.*, order.*, max(log.createdAt) from user
    join order on user.orderId = order.id -- 如果要过期用户,这里加一个 expireTime 判断
    join log on log.uid = user.id and log.createdAt > '2021-10-01 00:00:00'
    group by user.id -- 这是配合 join log on 获取最新登录一次的时间
    

    现在我的问题是:

    1. 各位大佬遇到这种需求是不是这样的,如果需求增加或变了再调整 sql 的拼接(好像无法实现一个大的逻辑去嵌套小的条件适应需求变化?)
    2. 这里一共需要三个表 join,我造了 100 万行的数据测试,发现执行一次大概要 15 秒左右。查了资料,看到说不要用 join,或者根据让小表驱动大表,建索引等操作,这些都在试(学);不过还是问一下大佬有没有建议?
    22 条回复    2021-10-15 01:01:36 +08:00
    harde
        1
    harde   6 天前
    1 、2 最简单来说,直接在 user 表加一个最近登录时间、会员标记,加索引。

    3 、过期会员没看懂,订单表有过期,会员怎么过期?
    Martin9
        2
    Martin9   6 天前   ❤️ 1
    在连接字段上面加索引,但是要确保索引生效,不然等于白加
    Martin9
        3
    Martin9   6 天前
    @harde #1 比如买了一个月会员,会员到期了
    nonoyang
        4
    nonoyang   6 天前
    你一个用户只有一个订单?而且你要的信息基本可以冗余在用户表上,其次 log.createdAt > '2021-10-01 00:00:00' 应该放在 where 里面
    MozzieW
        5
    MozzieW   6 天前
    @harde 订单里面我加一个 expireTime,超过这个时间就表示过期。比如买了一个月会员,一个月后就过期了
    @Martin9 这个我也看资料也是这样说的(连接字段上加索引),不过我试了一下,增加前后时间都差不多 15 秒
    @nonoyang 现在最新订单会覆盖会覆盖旧的订单(就是只有一个订单),我们也在计划修改这个,把所有历史订单都存下来
    MozzieW
        6
    MozzieW   6 天前
    @nonoyang “其次 log.createdAt > '2021-10-01 00:00:00' 应该放在 where 里面”
    这个原因是?我看了 join on 条件 where 都区别,log.createdAt 这个条件属于 join on 的表的,我理解应该放在这里;属于 user 表的才放在 where (我这个场景下)
    cedoo22
        7
    cedoo22   6 天前
    @MozzieW 会员过期 不是该在会员表 /用户表吗?
    jtwor
        8
    jtwor   6 天前
    按条件分别 with as 临时表,groupby 得出会员和用户 id 确定范围,再关联用户表拿用户信息。最大表 100w 需要 15 秒,如果索引问题看索引。不排除 100w 全击中了条件,数据太多返回太慢了,要分页拿。
    zzfer
        9
    zzfer   6 天前
    会员表里没有一个最后一次登录时间的字段?如果没有建议加一下这个字段,本身每日登录日志表的数据量就大,没必要关联这个表查询吧。
    nonoyang
        10
    nonoyang   6 天前   ❤️ 1
    @MozzieW 这个是筛选条件,并非关联条件。where 条件和是不是主表字段没关系
    MozzieW
        11
    MozzieW   6 天前
    @cedoo22 实际上 user 表是有会员标志的,但是过期了没有更新,而且现在订单表也有问题(只保存最新的订单);后续判断还可能扩展,有些字段可以考虑放到一个 user 表,但多个表 join 无法避免了(需求还可能加入其他表的条件来判断)。
    @jtwor 这个说的应该是让 user 表的结果行数先减少?我试了增加 where 条件过滤 user 表,时间的确降下来了( 4 秒),也在往这个方向试。最终结果不是给前端展示的,而是生成文件保存下来,所以分页了总时间还是在的

    @zzfer 你说的有道理,不过需求就包括不同的条件,比如还可能要最近 10 天启动了 3 次以上的用户,这个就要关联了。因为给的是简化版,有些字段我的确已经放到了 user 表

    @nonoyang ‘这个是筛选条件,并非关联条件。where 条件和是不是主表字段没关系’
    我刚刚的说法应该不准确,应该是 where 过滤的是最终的字段( select 的结果字段),我理解放到 on,可以判断 join 的时候就直接过滤了,不需要把结果再放到最后 where 的时候再判断,只有应该快一点?
    harde
        12
    harde   6 天前   ❤️ 2
    @MozzieW 如果是卖会员的业务,你的表结构有问题。

    会员是一个标记,有效时间是 Master 值。(就是说不存在 既是会员又不是、既过期又不过期的场合)。

    所以,会员标记(可有可无)和有效期应该放在 user 表。

    订单表只用来“记录”订单数据。

    订单完成后,更新会员有效期。
    MozzieW
        13
    MozzieW   6 天前
    @harde 懂你的意思。user 表有会员标记,但是没有过期时间,而且订单更新的时候也没有更新会员状态;更麻烦是订单表现在数据也不全(只保留了最新的订单);我们后面计划是先去改造订单表。但现在需求实现依赖已有的表结构,显得很奇怪。

    感谢回复!
    MozzieW
        14
    MozzieW   6 天前
    午休回来,感谢各位的回复,我早上也查资料并尝试,简单总结一下:
    1. 可以的话,尽量是保存到到单表,用索引,这样最快;
    2. 确定要用到 join 的,用小表驱动大表;这个还有个技巧,就是小表不仅指总行数小的表,如果可以用 where 筛选减少行数,时间也是会降低的。
    3. 和 2 类似,join 的时候会生成临时表,用 on 过滤条件应该能减少临时表的行数?这个和 @nonoyang 说的不一样,我还没弄懂。
    4. 我本意是问 join 有没有其他写法的,看到有些文章建议不用 join 而是把 sql 拆成多条语句,但那种业务场景和我的不一样(比如查一个用户有多少订单,这个是可以拆成两个 sql );我就没想到我的场景下可以怎么拆(或者说有没有其他不用 join 的写法),现在结论应该是我的场景是可以用 join 的(而且结果不是给前端实用,没有要求一定要几秒完成查询)。
    zhzy0077
        15
    zhzy0077   6 天前   ❤️ 1
    "找到最近 10 天", 你这最简单的实现方法就是半夜两三点跑一下当天的找个地方存着 excel 都行。也不用纠结多慢了
    zhzy0077
        16
    zhzy0077   6 天前
    楼上其实很多都是 tp 的优化方法 但是这个业务场景其实是个 ap 按批的逻辑去跑应该是最方便的
    MozzieW
        17
    MozzieW   6 天前
    @zhzy0077 大神大神,搜了一下 tp 和 ap,的确是这样的,我的场景下慢不是不能接受的,但是我一开始 sql 怎么写也不确定,问一下也是怕自己错误导致的那种慢
    lldld
        18
    lldld   5 天前   ❤️ 1
    用日志表 log 去 join user, 然后再 join order

    日志表先找到最近 10 天登录的用户, 这样数据少很多
    MozzieW
        19
    MozzieW   5 天前
    @lldld 试了,原来顺序是先 user 先 join 订单表 order 再 join 日志表 log,3.5 秒,顺序调整了一下,返回结果行数一致,时间变成 1.5 秒。
    Good !!!
    lldld
        20
    lldld   5 天前
    @MozzieW #19 还需要 1.5 秒吗, 这个数据量应该不需要吧.
    你原来的 sql 里面的 group by 是放在最后的, 应该不需要:

    select ... from
    (select DISTINCT uid from log where createdAt > '2021-10-01 00:00:00') as logged
    join ....
    MozzieW
        21
    MozzieW   5 天前
    @lldld 现在的测试数据里用户表 700 万条,订单表 10 万,日志表 150 万(后面这个表会增加比较快)。
    你给的先 select distinct 应该是有效的,但是和我另外用的 where 优化貌似冲突了。
    因为给的表和实际不一样(我去掉了多余的字段),而且我昨天发完贴优化的时候,试了一下最后用 where 过滤 user,这样才得到 3 秒的结果(因为这里最大的耗时是 join 导致的,而且最后的 where 需要在 join 之后计算,按照 join on where 的执行顺序我理解加 where 是会在 join 之后增加更多时间);实际是加了 where 之后结果集少了,同时时间也变少了,3 秒多;不加 where 需要大概 7 秒甚至更高;而你给的 select distinct 可以做到 5 秒,但是加上 where 后时间又涨回去了( 7 秒)。
    我猜测 where+join 可能是把 user 表的条件先做优化,减少 user 表参与 join 的行数;而用 log 做主表后这个 where 的优化没有了,而 logged 的行数已经比 user 通过 where 的行数多,从而导致时间增加。
    clancyliu
        22
    clancyliu   5 天前
    根据之前各位大佬提示,我的想法是,在用户表中增加最后一次登录时间 last_login_time,是否会员标致 vip_flag,会员过期时间 vip_expire_time, 每次登录更新 last_login_time, 下单就置 vip_flag 为 1,且更新 vip_expire_time 。你提到的三个问题,都能通过查一张表解决如下:
    1. select * from user where last_login_time >= 10 天前
    2. select * from user where vip_flag = 1 and last_login_time >= 10 天前
    3. select * from user where vip_flag = 1 and last_login_time >= 10 天前 and vip_expire_tima > 现在
    再添加相应索引,应该能够一定的优化效果。
    上面说到的是对新来的数据的处理,老的数据也可以通过登录日志表和订单表把新增的三个字段赋值上(洗数据),不知道这样能不能对你有一些帮助。
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1113 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 18ms · UTC 19:25 · PVG 03:25 · LAX 12:25 · JFK 15:25
    ♥ Do have faith in what you're doing.