请教 MySql 多表 join 怎么优化好

[MozzieW]

接到一个不确定+复杂的需求(对我而言），背景是： 现在后台有一个用户表 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，或者根据让小表驱动大表，建索引等操作，这些都在试（学）；不过还是问一下大佬有没有建议？

[harde]

1 、2 最简单来说，直接在 user 表加一个最近登录时间、会员标记，加索引。

3 、过期会员没看懂，订单表有过期，会员怎么过期？

[Martin9]

在连接字段上面加索引，但是要确保索引生效，不然等于白加

[Martin9]

@harde #1 比如买了一个月会员，会员到期了

[nonoyang]

你一个用户只有一个订单？而且你要的信息基本可以冗余在用户表上，其次 log.createdAt > '2021-10-01 00:00:00' 应该放在 where 里面

[MozzieW]

@harde 订单里面我加一个 expireTime，超过这个时间就表示过期。比如买了一个月会员，一个月后就过期了
@Martin9 这个我也看资料也是这样说的（连接字段上加索引），不过我试了一下，增加前后时间都差不多 15 秒
@nonoyang 现在最新订单会覆盖会覆盖旧的订单（就是只有一个订单），我们也在计划修改这个，把所有历史订单都存下来

[MozzieW]

@nonoyang “其次 log.createdAt > '2021-10-01 00:00:00' 应该放在 where 里面”
这个原因是？我看了 join on 条件 where 都区别，log.createdAt 这个条件属于 join on 的表的，我理解应该放在这里；属于 user 表的才放在 where （我这个场景下）

[cedoo22]

@MozzieW 会员过期 不是该在会员表 /用户表吗？

[jtwor]

按条件分别 with as 临时表,groupby 得出会员和用户 id 确定范围，再关联用户表拿用户信息。最大表 100w 需要 15 秒，如果索引问题看索引。不排除 100w 全击中了条件，数据太多返回太慢了，要分页拿。

[zzfer]

会员表里没有一个最后一次登录时间的字段？如果没有建议加一下这个字段，本身每日登录日志表的数据量就大，没必要关联这个表查询吧。

[nonoyang]

@MozzieW 这个是筛选条件，并非关联条件。where 条件和是不是主表字段没关系

[MozzieW]

@cedoo22 实际上 user 表是有会员标志的，但是过期了没有更新，而且现在订单表也有问题（只保存最新的订单）；后续判断还可能扩展，有些字段可以考虑放到一个 user 表，但多个表 join 无法避免了（需求还可能加入其他表的条件来判断）。
@jtwor 这个说的应该是让 user 表的结果行数先减少？我试了增加 where 条件过滤 user 表，时间的确降下来了（ 4 秒），也在往这个方向试。最终结果不是给前端展示的，而是生成文件保存下来，所以分页了总时间还是在的

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

@nonoyang ‘这个是筛选条件，并非关联条件。where 条件和是不是主表字段没关系’
我刚刚的说法应该不准确，应该是 where 过滤的是最终的字段（ select 的结果字段），我理解放到 on，可以判断 join 的时候就直接过滤了，不需要把结果再放到最后 where 的时候再判断，只有应该快一点？

[harde]

@MozzieW 如果是卖会员的业务，你的表结构有问题。

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

所以，会员标记（可有可无）和有效期应该放在 user 表。

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

订单完成后，更新会员有效期。

[MozzieW]

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

感谢回复！

[MozzieW]

午休回来，感谢各位的回复，我早上也查资料并尝试，简单总结一下：
1. 可以的话，尽量是保存到到单表，用索引，这样最快；
2. 确定要用到 join 的，用小表驱动大表；这个还有个技巧，就是小表不仅指总行数小的表，如果可以用 where 筛选减少行数，时间也是会降低的。
3. 和 2 类似，join 的时候会生成临时表，用 on 过滤条件应该能减少临时表的行数？这个和 @nonoyang 说的不一样，我还没弄懂。
4. 我本意是问 join 有没有其他写法的，看到有些文章建议不用 join 而是把 sql 拆成多条语句，但那种业务场景和我的不一样（比如查一个用户有多少订单，这个是可以拆成两个 sql ）；我就没想到我的场景下可以怎么拆（或者说有没有其他不用 join 的写法），现在结论应该是我的场景是可以用 join 的（而且结果不是给前端实用，没有要求一定要几秒完成查询）。

[zhzy0077]

"找到最近 10 天", 你这最简单的实现方法就是半夜两三点跑一下当天的找个地方存着 excel 都行。也不用纠结多慢了

[zhzy0077]

楼上其实很多都是 tp 的优化方法 但是这个业务场景其实是个 ap 按批的逻辑去跑应该是最方便的

[MozzieW]

@zhzy0077 大神大神，搜了一下 tp 和 ap，的确是这样的，我的场景下慢不是不能接受的，但是我一开始 sql 怎么写也不确定，问一下也是怕自己错误导致的那种慢

[lldld]

用日志表 log 去 join user, 然后再 join order

日志表先找到最近 10 天登录的用户, 这样数据少很多

[MozzieW]

@lldld 试了，原来顺序是先 user 先 join 订单表 order 再 join 日志表 log，3.5 秒，顺序调整了一下，返回结果行数一致，时间变成 1.5 秒。
Good ！！！

[lldld]

@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 ....