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

[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 使用了很多子查询，查询效率也并不会低，不过肯定可以省很多代码，因为很多数据如果不用子查询，那么就要多次链接数据库去做查询，然后组合数据。
愚见：适当的使用子查询其实是会更高效的！

[wxf666]

@sagaxu #20

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

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


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


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

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

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

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


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

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

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


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

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

还是啥功能呢。。

[sagaxu]

@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 上做一些弱化和舍弃。

[wxf666]

@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 加上多事务并行写，并且在同进程内，免去网络开销，这得有多爽啊。。

[wuhao1]

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

[wuhao1]

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