老生常谈 关于 子查询的应用

[wuhao1]

select uid,(select wechat from member_a where tuid=m.tuid)wechat, (select name from admin_user where id =(select opuid from link where id=ldid))fzr ,no,ctime,(select ctime from member where id=uid)uctime, ldid,adid,rmb,(select count(id) from money_o where uid=m.uid and ctime < m.ctime)readin, (select count(id) from money_o where uid=m.uid and ctime > m.ctime)readout from money m where
ldid in(select id from link where qd=666 and ctime>$tms) and status=1 and ctime>=$czs having ctime> uctime and (ctime-uctime<480);

在后端程序中少不了要和 sql 接触，一部分人对子查询很抗拒认为 把很多逻辑都隐藏到 sql 中了不利于程序的可读性，然而 你同意吗？
上述 sql 使用了很多子查询，查询效率也并不会低，不过肯定可以省很多代码，因为很多数据如果不用子查询，那么就要多次链接数据库去做查询，然后组合数据。
愚见：适当的使用子查询其实是会更高效的！

[WashFreshFresh]

用 jpa 的时候基本都是单表查询，用 mybatis 的时候为了偷懒少写 sql 就 sql 一把梭了，因为不想写多个 xml ；我的体验是，sql 写的少代码写的多除了增强代码可读性，还能增强记忆，业务会记得比较清楚； sql 一把梭的真的今天写完明天就忘记业务细节了。

[ldx78203199]

业务上谁写这种 sql ，直接开喷， 优化+维护成本太高，如果业务场景真有这么高建议上 ES

[liangdi]

我一直做 toB 的系统，所以肯定是代码逻辑优先，基本不会这么用，要么是 db 自己做视图

[sagaxu]

管理后台读从库，子查询，多表连随便造。但在非管理后台，使用较复杂的子查询或多表相连，有两个很纠结的点

1. 缓存机制不好设计，复杂程度远超单表查询
2. DB 采样统计失真，查询计划选择了错误的索引可能炸库

[encounter2017]

我觉得还是看业务场景，比如说我这里有一个业务场景就是需要查询树状结点下面的所有子节点信息，这种情况下用 recursive cte 查询就很方便，一次就能查完，IO 都在数据库做了，不然的话还得多次查询数据库.

一个简单的示例
```
CREATE TABLE binary_tree (
id INT PRIMARY KEY,
value VARCHAR(255),
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES binary_tree(id)
);

INSERT INTO binary_tree (id, value, parent_id) VALUES
(1, 'Root', NULL),
(2, 'Left Child', 1),
(3, 'Right Child', 1),
(4, 'Left Grandchild', 2),
(5, 'Right Grandchild', 2),
(6, 'Another Left Grandchild', 3),
(7, 'Another Right Grandchild', 3);

WITH RECURSIVE tree_cte AS (
-- Base case: select the root node
SELECT id, value, parent_id, 0 AS level, CAST(id AS text) AS path
FROM binary_tree
WHERE parent_id IS NULL

UNION ALL

-- Recursive case: select child nodes
SELECT c.id, c.value, c.parent_id, p.level + 1, CONCAT(p.path, ',', CAST(c.id AS text))
FROM binary_tree c
JOIN tree_cte p ON c.parent_id = p.id
)
SELECT id, value, parent_id, level, path
FROM tree_cte
ORDER BY path;

1,Root,,0,1
2,Left Child,1,1,"1,2"
4,Left Grandchild,2,2,"1,2,4"
5,Right Grandchild,2,2,"1,2,5"
3,Right Child,1,1,"1,3"
6,Another Left Grandchild,3,2,"1,3,6"
7,Another Right Grandchild,3,2,"1,3,7"

```

[WIN2333]

这种确实适合代码逻辑，因为一眼就知道干了啥，sql 有心智负担。更值得讨论的是 join ，总有人跳出来说 join 怎么怎么不好，巴拉巴拉，有些一条 sql join 一下干出来的非得用代码实现。。。。

[cccvno1]

任何一个子查询的表数据出了问题，这个查询直接就废了

[wuhao1]

来分析下 如果没有用子查询 或者 连接查询 join
某些情况 join 比子查询可能效率更高
select uid,

(select wechat from member_a where tuid=m.tuid)wechat,

(select name from admin_user where id =(select opuid from link where id=ldid))fzr

,no,ctime,

(select ctime from member where id=uid)uctime,

ldid,adid,rmb,

(select count(id) from money_o where uid=m.uid and ctime < m.ctime)readin,

(select count(id) from money_o where uid=m.uid and ctime > m.ctime)readout

from money m where

ldid in(select id from link where qd=666 and ctime>$tms) and status=1 and ctime>=$czs having ctime> uctime and (ctime-uctime<480);

如果采用 程序 来集合数据那么可能组要查询 4 次左右 再把数据组合 效率上是否没有一次查询高？

@cccvno1 如果就按程序逻辑来组合数据其实也是一样的，如果其中一个子查询有异常 修复这个异常就好了
@encounter2017 确实某些场景下 子查询或者 join 可能会更高效
@sagaxu 同意，复杂的需求 往往在管理后台。
@ldx78203199 sql 也可以写注释的
@liangdi 某些人可能连视图也不会让你用， 因为也隐藏了一些逻辑，降低了可读性。
@WashFreshFresh 子查询 和 分开查 SQL 并不少

[8355]

只能接受 where 条件用子查询，类似 in 的范围比较大或者查询条件在子查询表中。
这种 sql 完全无法优化，部分小表或着纯粹的关联查询在数据组装中批量查询可以直接读缓存，这么看起来真的是糟糕的不得了。。。

[ldx78203199]

我觉得还是要定一个业务场景的背景，不然这种问题是不会有绝对答案，都会说出各自的道理。目前我所在的厂 这种业务代码 CR 不可能+2 过得了

[ZZ74]

我无所谓
SQL 也是一种编程语言，大部分场景下 可以用 sql （存储过程），也可以用常见的编程语言+sql 实现。效率上也没多大区别。

[wxf666]

@ldx78203199 #2

所以数据库的推荐用法，只能是当成 KV 数据库，得到数据后，再手动组装？



@sagaxu #4

不缓存应该也没事吧？

现在千元消费级固态，都能做到 100W 随机读写 / 秒（ 4200 x 1024 / 4 = 107.5W ），

换句话说，数据库就算不用内存缓存，读写数据全走固态 IO ，应该也能做到几十万并发？

是这样算吗？

[ldx78203199]

1. 你没经历过高并发 体会不到，这里面相关资料往上很多
2. 上面少说了一点，复杂度在业务代码中计算的成本是最低的，到数据库在运算成本是非常大
3. 可以举个最简单例子：假设你的 sql 一个语句是 1m ，不考虑 CPU 、内存、网络以及数据库的查询复杂度和缓存命中率等因素，SSD 的读写速度是 1000 MB/s ，每秒最多可以处理 1000 MB / 1 MB = 1000 次 I/O 。

[sagaxu]

@wxf666

1. 关系型 DB 不光有 IO 开销，它要解析 SQL ，要制定查询计划，这些都会耗费 CPU
2. throughput 和 latency 是不同维度的东西，再快的 SSD latency 也在 10us 级，比内存慢 100 倍

并发数上去之后，最长响应时间，MySQL 比 Redis 高一到两个数量级

[RandomJoke]

case by case ，没有所谓的银弹。对于团队而言，sql 太过复杂就是有些影响阅读和理解业务，性能这种东西毕竟看场景，在业务上符合就行。反而阅读和团队维护更重要吧

[wxf666]

@sagaxu #14


1. 执行计划，一般都会被缓存的吧。。除非有成千上万种不同的 SQL ？


2. 一个请求多 10us ，应该也能接受吧。。

如果说，一个请求要多个数据，可以让它们同时进行呀。。

NVMe SSD 不是支持 64K 队列，每队列 64K 深度吗。。

每批次提交几万个随机 IO 请求，都行呀。。

[sagaxu]

@wxf666 执行计划一般不会缓存，每个 sql 都要重新解析，准备，优化。只有少数情况会缓存。

一次 IO 请求 10us ，但一次请求可能有 10 次甚至 100 次，1000 次 IO 。即使根据 ID 请求一条数据，也可能产生多次串行的 IO 。

传统 db 负载多样化，比如有查询在扫表时，IO 负载就会高很多，造成简单查询的性能抖动，P90 延迟很低，P99 可能就很高，完全不可控。

[wxf666]

@sagaxu #17

1. 如果解析每个 SQL 很耗时，为何不缓存执行计划呢？

难道相同 SQL ，还会因为参数不同（ SELECT ... WHERE id = ?，参数是 123 或 456 ），执行计划大相径庭？


2. 如果是四层 B+ 树（足够容纳 80 亿行了，缓存前两层代价 10+ MB 内存），算上索引，也才 4 ~ 5 次 IO ，40 ~ 50 us ，应该也不多呀。。

如果要同时请求 64000 个数据，也只要放队列里，提交给 SSD ，就能一次性拿完了呀。。

如果是扫表，在遍历 B+ 树时，就知道会分布在哪些枝干、叶子上。也是放队列里，一次性就能拿完了呀。。

而且一个叶子就有 16 条数据（假设 1K / 条），扫百万条数据，只需 62500 个叶子节点，最差情况也是 62500 个第三层枝干节点，两次队列 20 us （前两层已缓存），应该就能拿到了？


当然，以上是我设想的，理想数据库应有的性能。。达不到，业界就不应该说《数据库没有提升空间了》。。

[wxf666]

@sagaxu #17

说错了，扫百万条数据，有 62500 个叶子，78 个第三层枝干（假设主键 20 字节 / 个，每枝干能有 800 主键）

[sagaxu]

@wxf666 不解析成 AST 怎么知道两条 SQL 只是参数不同？像 prepared statement 那样将 SQL 和参数分离，这样才好缓存。很多年前 MySQL 有过 HandlerSocket 这样绕过解析 SQL 过程的插件，只支持一些简单查询，无磁盘 IO 时查询性能直接提升好几倍。

clustered index 定位走一遍索引，普通索引，需要先走一遍索引拿到主键，再走一遍主键的索引，典型的例子比如根据身份证号码查找用户。前两层都缓存的时候，也有 4 次串行的 IO 。这里的 IO 请求只是提交到文件系统，它不能绕过文件系统自己提交到 ssd 队列。文件系统也会有磁盘碎片，可能导致 db 的 1 次 IO
产生大于 1 次的磁盘 IO 。

高端 ssd 性能是很高，但经过文件系统就有一些损耗，不同文件系统，甚至同一文件系统配置不同参数，跑 db 性能评分可以差距很大。在高并发(1K 以上)时容易有剧烈的性能抖动。

DB 设计和实现时，有相当多的地方需要取舍和平衡，你想象中为某个场景做的优化，换个场景可能就是劣化。