这两种 sql 写法有什么区别么？把查询结果当做临时 table 来查会更快么？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1602 天前的主题，其中的信息可能已经有所发展或是发生改变。

select a.columnA1,b.columnB1,b.columnB2
from tableA a
left join tableB b on a.xx = b.xx
select a.columnA1,b.columnB1,b.columnB2
from tableA a
left join (select columnB1,columnB2 from tableB) b on a.xx = b.xx

13 条回复 • 2020-07-06 17:39:41 +08:00

liprais

2020-07-06 15:13:45 +08:00

看执行计划就知道了
很大可能是没有的

binux

2020-07-06 15:19:16 +08:00 via Android

我觉得是 plan 是一样的

luxinfl

2020-07-06 15:19:39 +08:00

@liprais 用了 explain，确实没区别，但是我看项目里面好多第二种写法的，不知道为什么。

luxinfl

2020-07-06 15:21:07 +08:00

@binux 感觉写的人以为 select a.coulmnA1 要比 select * 要快，所以以为连接的时候也是 select 出来最快？？

fangcan

2020-07-06 15:27:10 +08:00

应该是一样的

liprais

2020-07-06 15:33:18 +08:00

@luxinfl
有个逻辑优化规则叫做 ColumnPruning,会把上面的改成下面的
https://github.com/apache/spark/blob/master/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/ColumnPruningSuite.scala
^这是 spark sql 里面这个规则的测试用例
mysql 应该也有类似的，所以这俩应该是没啥大的区别
如果数据库没有这个优化，然后这个表里面有个特别大的字段 blob 之类的，这两种写法可能会有一些性能上的区别，因为 build join row 的时候没把不需要的字段去掉。

haozxuan001

2020-07-06 16:07:52 +08:00

第二个虚表（临时表）是不能使用索引的，我推测你的 sql 应该还有 where 条件，我实际测试下来计划如下

```
1,PRIMARY,t,const,PRIMARY,PRIMARY,4,const,1,Using index
1,PRIMARY,<derived2>,ref,<auto_key0>,<auto_key0>,8,"const,const",10,
2,DERIVED,t_comment,ALL,,,,,301841,

```

haozxuan001

2020-07-06 16:08:52 +08:00

对于简单查询或者频率较低时，影响不大。高频扫全表，DB 压力会很大；
PS：
sql
explain
select t.id, tc.description, tc.who_said
from t_ticket t
left join (select ticket_id, description, who_said from t_comment) tc on t.id = tc.ticket_id
where t.id = 42498
and tc.who_said = 2;

luxinfl

2020-07-06 16:23:11 +08:00

@haozxuan001 我也试了一下，用虚表的话，会多一条用到索引的执行计划，其他两个 sql 是一样的，这样看的话，其实直接关联表的速度更快？

- SIMPLE a ALL 187
- SIMPLE b ALL 75 Using where; Using join buffer (Block Nested Loop)

-

- PRIMARY a ALL 187
- PRIMARY <derived2> ref <auto_key0> <auto_key0> 93 cgbs.a.CRDT_APPLY_SRL_NO 7
- DERIVED zl_use_credit_business ALL 75

luxinfl

2020-07-06 16:26:51 +08:00

@liprais 我看两条 sql 的执行计划是不一样的

haozxuan001

2020-07-06 17:21:40 +08:00

@luxinfl 理论上我在写 sql 的时候，会做 explain，尽可能减少 ALL，尽可能减少虚表这些，因为这些都是没办法用到索引的，没有用到索引的查询，会在后续系统庞大后带来很多问题。

hellofiora

2020-07-06 17:38:30 +08:00

第二个如果是 right，和虚拟表里面有查询条件的话，我觉得才正常

hellofiora

2020-07-06 17:39:41 +08:00

譬如
select * from table1 a right join (select b.c1,b.c2 from table2 b where b.c1=*******) b on a.c1=b.c1