场景:
业务上需要做一个查询, 因为是 web 及时响应, 所以对响应时间有要求, 原业务场景是需要从无库存订单中剔除绑定闲置库存, 因单条 sql 查询实现复杂, 故考虑用差集方式:
- select a.col1, a.col2
- from a
- where a.id = ?
- and not exists (
- select b.id
- from b left join c on b.id = c.id
- where b.id = ?
- and b.id = a.id)
- order by a.id
数据量: a,b,c 皆在百万数据量级, 排除其他非必要过滤字段, id 皆有 btree 索引
运行: 2s 左右
环境: 阿里云 (最基本线上服务性能, 数据库运行状态保持在 10 个以上链接)
分析:
原 sql 其实用的是 not in, 参考了文章 1, 在同等数据量时 not in 会走多次全表查询 (因为!= 无对应索引), 而 not exists 会走子查询索引, 所以 not exists 更快. 故先用 not exists 替换了 not in(语法有差异, 替换时需要做 b.id = a.id 的关联). 在参考文章 2 以后, 尝试用 left join 进行优化 (其中关于 MySQL 子查询优化器说法待考量, 后分析文章 3), 改成如下方式:
- select a.col1, a.col2
- from a
- left join ( select b.id
- from b left join c on b.id = c.id
- where b.id = ?
- ) as r on a.id = r.id
- where b.id = ?
- and r.id is null
- order by a.id
思路便是先将子查询符合的行通过 left join 查询到, 然后通过 is null 条件得到剩下的部分 (即满足需求的记录). 最终运行时间在 0.7s.
- ref:
- 1. https://www.cnblogs.com/beijingstruggle/p/5885137.html
- 2. https://blog.csdn.net/zyz511919766/article/details/49335647
- 3. https://www.cnblogs.com/wxw16/p/6105624.html?utm_source=itdadao&utm_medium=referral
来源: http://www.bubuko.com/infodetail-2865404.html