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

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

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

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

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

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

我觉得还是看业务场景，比如说我这里有一个业务场景就是需要查询树状结点下面的所有子节点信息，这种情况下用 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"

```

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

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

来分析下 如果没有用子查询 或者 连接查询 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 并不少

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

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

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

@ldx78203199 #2

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



@sagaxu #4

不缓存应该也没事吧？

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

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

是这样算吗？

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

@wxf666

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

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

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

@sagaxu #14


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


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

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

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

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

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

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

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

@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 （前两层已缓存），应该就能拿到了？


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

@sagaxu #17

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

@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 设计和实现时，有相当多的地方需要取舍和平衡，你想象中为某个场景做的优化，换个场景可能就是劣化。

@sagaxu #20

1. 肯定是 SQL 和 参数分离的呀，现在还有人手动拼接 SQL 和参数的吗。。

分离后，直接查 SQL 的 hash ，就知道是否被解析过，执行计划是否在缓存中了呀。。


2. 是的，考虑到索引主键可能较大，B+ 树层级较高，所以上个回复才说了《(2 + 2) ~ (2 + 3) 次 IO 》。


3. 如果文件系统对读写影响很大，数据库为何不自己接管硬盘分区读写呢？

毕竟，它应该也没用到文件系统啥高级功能吧。。

Q: 还是说，分区大小固定死了，不能灵活调整？
A: 啥公司这么拮据啊，那么关注性能，腾整个盘存数据，都觉得物未尽其用吗。。

Q: 还是说，一分区对应一个表，不确定每个表要多大空间？
A: 所有表在一个分区中，应该没啥问题吧。。


4. 很多公司，不都把数据库，当 kv 库用吗？

上面说的，读写一条数据、并发读写多条数据、扫表等，能覆盖大部分场景了？

把这些做到极致，应该就是很多公司心目中，天底下最好的数据库了吧。。


5. 大佬觉得，是其他什么功能，阻碍了数据库，导致其连固态速度都跟不上呢？（要跟上，就应该做到 100W 随机读写）

是加锁吗？每次读写，都要遍历锁列表？还是这个锁实现，是 B+ 树节点上的？粒度有点大？

还是啥功能呢。。

@wxf666 有些框架内部是拼 SQL+参数转义，有些开发把一定不会有注入的问题的 SQL 直接把参数写进去。尤其是 PHP 这类支持字符串模板的语言。

像 MySQL 的语句缓存上限，8.0 之前是 100 万(默认值才 1 万多)，如果有 2K 个连接，平均每个连接只有 500 个，如果被先起的连接占用完，后面起的连接就完全没有缓存了。cache 还是 per session 的，A 连接的 cache ，B 连接内还用不了。

数据库为何不自己接管硬盘分区读写呢？的确有数据库这么做，如 Oracle 和 DB2 就可以用裸设备，但一般公司用不起啊。

“很多公司，不都把数据库，当 kv 库用吗？”。可能只有互联网公司喜欢这么用，简单的业务需求，极高的并发量。传统公司业务往往是相反的，极低的并发量，超复杂的业务需求，一条 SQL 打印出来超过十张 A4 纸也不稀奇。

现在针对不同的业务场景做优化，发展出了数十种不同类型的库，但没有一种库能覆盖大部分场景且优化做到极致。


传统 DB 和 SSD 之间的性能落差，最大原因可能是 DB 要满足 ACID 时带来的额外开销，比如各种 log 放大 IO ，比如 InnoDB 为了可重复读增加的 gap lock 就会阻塞那个区间的插入导致不能并发，DB 自身实现也会导致一些读取写入放大。新兴 DB 为了获得更高的性能，往往要在 ACID 上做一些弱化和舍弃。

@sagaxu #22

1. 那这就是偷懒，所需要付出的代价了。。


2. 为啥不所有会话共享一个语句缓存池呢。。肉眼可见，能减少 90% 内存需求呀。。


3. 是 MySQL 等不想接管硬盘吗？原理上应该差不多呀。。都是 页偏移 x 页大小 定位？


4. 传统公司应该不咋用 MySQL 吧？

两年前，我在 [另一个帖子]( /t/889443#reply21 ) 测试过树状数据的获取，这货多个测试中，比 SQLite 都慢 2 ~ 10 倍。。

不敢想其他更复杂的请求，MySQL 会拉跨成啥样了。。


5. 这些 log 都是顺序写，不咋读？（恢复数据时读？）感觉完全可以放到另一个盘。。


6. 按理说，阻塞并发写，应该不影响并发读呀（只要不是当前读）。。可 MySQL 的读性能，就如第 4 点所说。。

前几天，[另一个帖子]( /t/1075881#reply65 )测试，SQLite 在写事务时，其他线程读事务，应该能吃满 IO 性能。具体是，单表 1.3 亿 100 GB 数据，还能 4.7W 读事务 / 秒。

但也如你所说，此时 ACID 弱化 / 舍弃，如持久性不完全，断电时可能会丢失几秒钟数据。另外，写事务是串行化隔离，读事务是快照隔离。


7. 好奇行锁、间隙锁，实现原理是啥呢。。我在 21 楼瞎猜，是锁列表？或者 B+ 树节点上锁？

要是 SQLite 加上多事务并行写，并且在同进程内，免去网络开销，这得有多爽啊。。

其实 是 一条 sql 包含了 4~5 个 子查询 那么 一次性执行完毕

如果 一条一条的执行， 也会频繁调用数据库可能会执行 6 次查询
加上网络 io 。 子查询不一定会慢的，利用好子查询更有利于性能的提升。
当然 我所说的是适当的使用子查询更有利于性能的提升。