5
请教 MySql 多表 join 怎么优化好
source link: https://www.v2ex.com/t/807482
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
接到一个不确定+复杂的需求(对我而言),背景是: 现在后台有一个用户表 user ( id,orderId),会员订单表 order(id,type,expireTime),用户每日登录日志表 log(id,uid,createdAt);表的行数基本在 100 万(订单表 10 万)。 现在需要可能是:
- 找到最近 10 天登录的用户
- 或者,最近 10 天登录的会员用户(根据有没有订单 id )
- 或者,最近 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 获取最新登录一次的时间
现在我的问题是:
- 各位大佬遇到这种需求是不是这样的,如果需求增加或变了再调整 sql 的拼接(好像无法实现一个大的逻辑去嵌套小的条件适应需求变化?)
- 这里一共需要三个表 join,我造了 100 万行的数据测试,发现执行一次大概要 15 秒左右。查了资料,看到说不要用 join,或者根据让小表驱动大表,建索引等操作,这些都在试(学);不过还是问一下大佬有没有建议?
22 条回复 • 2021-10-15 01:01:36 +08:00
MozzieW 1 天前
MozzieW 1 天前
@nonoyang “其次 log.createdAt > '2021-10-01 00:00:00' 应该放在 where 里面”
这个原因是?我看了 join on 条件 where 都区别,log.createdAt 这个条件属于 join on 的表的,我理解应该放在这里;属于 user 表的才放在 where (我这个场景下)
这个原因是?我看了 join on 条件 where 都区别,log.createdAt 这个条件属于 join on 的表的,我理解应该放在这里;属于 user 表的才放在 where (我这个场景下)
jtwor 1 天前
按条件分别 with as 临时表,groupby 得出会员和用户 id 确定范围,再关联用户表拿用户信息。最大表 100w 需要 15 秒,如果索引问题看索引。不排除 100w 全击中了条件,数据太多返回太慢了,要分页拿。
MozzieW 1 天前
@cedoo22 实际上 user 表是有会员标志的,但是过期了没有更新,而且现在订单表也有问题(只保存最新的订单);后续判断还可能扩展,有些字段可以考虑放到一个 user 表,但多个表 join 无法避免了(需求还可能加入其他表的条件来判断)。
@jtwor 这个说的应该是让 user 表的结果行数先减少?我试了增加 where 条件过滤 user 表,时间的确降下来了( 4 秒),也在往这个方向试。最终结果不是给前端展示的,而是生成文件保存下来,所以分页了总时间还是在的
@zzfer 你说的有道理,不过需求就包括不同的条件,比如还可能要最近 10 天启动了 3 次以上的用户,这个就要关联了。因为给的是简化版,有些字段我的确已经放到了 user 表
@nonoyang ‘这个是筛选条件,并非关联条件。where 条件和是不是主表字段没关系’
我刚刚的说法应该不准确,应该是 where 过滤的是最终的字段( select 的结果字段),我理解放到 on,可以判断 join 的时候就直接过滤了,不需要把结果再放到最后 where 的时候再判断,只有应该快一点?
@jtwor 这个说的应该是让 user 表的结果行数先减少?我试了增加 where 条件过滤 user 表,时间的确降下来了( 4 秒),也在往这个方向试。最终结果不是给前端展示的,而是生成文件保存下来,所以分页了总时间还是在的
@zzfer 你说的有道理,不过需求就包括不同的条件,比如还可能要最近 10 天启动了 3 次以上的用户,这个就要关联了。因为给的是简化版,有些字段我的确已经放到了 user 表
@nonoyang ‘这个是筛选条件,并非关联条件。where 条件和是不是主表字段没关系’
我刚刚的说法应该不准确,应该是 where 过滤的是最终的字段( select 的结果字段),我理解放到 on,可以判断 join 的时候就直接过滤了,不需要把结果再放到最后 where 的时候再判断,只有应该快一点?
harde 1 天前 2
@MozzieW 如果是卖会员的业务,你的表结构有问题。
会员是一个标记,有效时间是 Master 值。(就是说不存在 既是会员又不是、既过期又不过期的场合)。
所以,会员标记(可有可无)和有效期应该放在 user 表。
订单表只用来“记录”订单数据。
订单完成后,更新会员有效期。
会员是一个标记,有效时间是 Master 值。(就是说不存在 既是会员又不是、既过期又不过期的场合)。
所以,会员标记(可有可无)和有效期应该放在 user 表。
订单表只用来“记录”订单数据。
订单完成后,更新会员有效期。
MozzieW 1 天前
@harde 懂你的意思。user 表有会员标记,但是没有过期时间,而且订单更新的时候也没有更新会员状态;更麻烦是订单表现在数据也不全(只保留了最新的订单);我们后面计划是先去改造订单表。但现在需求实现依赖已有的表结构,显得很奇怪。
感谢回复!
感谢回复!
MozzieW 1 天前
午休回来,感谢各位的回复,我早上也查资料并尝试,简单总结一下:
1. 可以的话,尽量是保存到到单表,用索引,这样最快;
2. 确定要用到 join 的,用小表驱动大表;这个还有个技巧,就是小表不仅指总行数小的表,如果可以用 where 筛选减少行数,时间也是会降低的。
3. 和 2 类似,join 的时候会生成临时表,用 on 过滤条件应该能减少临时表的行数?这个和 @nonoyang 说的不一样,我还没弄懂。
4. 我本意是问 join 有没有其他写法的,看到有些文章建议不用 join 而是把 sql 拆成多条语句,但那种业务场景和我的不一样(比如查一个用户有多少订单,这个是可以拆成两个 sql );我就没想到我的场景下可以怎么拆(或者说有没有其他不用 join 的写法),现在结论应该是我的场景是可以用 join 的(而且结果不是给前端实用,没有要求一定要几秒完成查询)。
1. 可以的话,尽量是保存到到单表,用索引,这样最快;
2. 确定要用到 join 的,用小表驱动大表;这个还有个技巧,就是小表不仅指总行数小的表,如果可以用 where 筛选减少行数,时间也是会降低的。
3. 和 2 类似,join 的时候会生成临时表,用 on 过滤条件应该能减少临时表的行数?这个和 @nonoyang 说的不一样,我还没弄懂。
4. 我本意是问 join 有没有其他写法的,看到有些文章建议不用 join 而是把 sql 拆成多条语句,但那种业务场景和我的不一样(比如查一个用户有多少订单,这个是可以拆成两个 sql );我就没想到我的场景下可以怎么拆(或者说有没有其他不用 join 的写法),现在结论应该是我的场景是可以用 join 的(而且结果不是给前端实用,没有要求一定要几秒完成查询)。
MozzieW 21 小时 49 分钟前
@lldld 试了,原来顺序是先 user 先 join 订单表 order 再 join 日志表 log,3.5 秒,顺序调整了一下,返回结果行数一致,时间变成 1.5 秒。
Good !!!
Good !!!
lldld 21 小时 3 分钟前
@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 ....
你原来的 sql 里面的 group by 是放在最后的, 应该不需要:
select ... from
(select DISTINCT uid from log where createdAt > '2021-10-01 00:00:00') as logged
join ....
MozzieW 19 小时 51 分钟前
@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 的行数多,从而导致时间增加。
你给的先 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 6 小时 10 分钟前
根据之前各位大佬提示,我的想法是,在用户表中增加最后一次登录时间 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 > 现在
再添加相应索引,应该能够一定的优化效果。
上面说到的是对新来的数据的处理,老的数据也可以通过登录日志表和订单表把新增的三个字段赋值上(洗数据),不知道这样能不能对你有一些帮助。
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 > 现在
再添加相应索引,应该能够一定的优化效果。
上面说到的是对新来的数据的处理,老的数据也可以通过登录日志表和订单表把新增的三个字段赋值上(洗数据),不知道这样能不能对你有一些帮助。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK