SQL优化：慎⽤标量⼦查询，改⽤leftjoin提升查询效率
⼀、项⽬实例问题
1、问题背景
  某个需求做了之后，注意到有个接⼝返回数据特别慢，特别是使⽤下⾯的 3 个字段排序时就直接卡死，肯定是 sql 性能写法问题，所以决定研究⼀下查看究竟。
  其实需求挺简单，有⼏个字段排序，前端需要展⽰那些字段，然后之前的后端写的 sql 如下，仅提取主要问题点，其实就是需要拿到starCount、commentCount、totalReward ⽤来前端展⽰，⽽这三个字段呢，⼜需要从另外三个表⾥去分别计数，所以不考虑 sql 性能优化的话，就很容易想到了这种错误的写法。
k.tags,
v.views,
(select coalesce(count(rid),0) from table1 where aa ='kl'and rid = k.id) starCount,
(select coalesce(count(id),0) from table2 where aa ='kl'and rid = k.id::varchar) commentCount,
(select coalesce(count(id),0) from table3 where aa ='kl'and rid = k.id::varchar) totalReward
from table4 k left join table5 v on k.id = v.kl_id
2、优化⽅案
  主要优化后的 sql 如下：使⽤ left join 替代标量⼦查询
k.tags,
v.views,
coalesce (s.count,0) starCount,
coalesce (m.count,0) commentCount,
coalesce (p.count,0)  totalReward
from table4 k left join table5 v on k.id = v.kl_idsql容易学吗
left join (select rid,count(rid) from table1 where aa ='kl'group by rid) s on k.id = s.rid
left join (select rid,count(rid) from table2 where aa ='kl'group by rid) m on m.rid = k.id::varchar
left join (select rid,count(rid) from table3 where aa ='kl'group by rid) p on p.rid = k.id::varchar
order by totalReward desc
  优化前⽐如我有10万篇⽂章，那就要执⾏10万次(select coalesce(count(rid),0) from table1 where aa = 'kl' and rid = k.id) starCount。
  优化后，仅需⼀次两表之间的匹配，即使是全表也是1次匹配，分组后也是1次匹配，数据量少是会提⾼效率但是顶多0.00⼏的提⾼，关键是left join。提⾼了n倍之前order by直接执⾏失败time out，优化之后是0.4s左右。
3、分析原因 - 为什么会想到错误的写法
  以前我确实很少看到第⼀种那种标量⼦查询的写法，所以很纳闷为什么会这样写。⼀般不都是⽤ left join 吗？后来了解到可能情况不⼀样：
（1）平常我们使⽤多表关联都会想到 left join，因为我们会⽤到关联表的多个字段或某个字段，需要将其查出来，所以很容易想到 left join。
（2）⽽这种情况只需要使⽤其他表的⼀个计数的值，没有使⽤表⾥的任何字段，没学过 sql 优化的，很难想到⽤ left join。
  ⽽很多⼈使⽤标量⼦查询⽽不⾃知执⾏效率差，往往是因为数据量⽐较⼩，并没有发现不妥，⼀旦数据量⼤了之后，就会越来越慢。只有经过⼤数据量的考验，才能写出来优质的 sql。
  墨天轮平台有个标量⼦查询的优化案例可以看下：Oracle 标量⼦查询优化案例  —  db.pro/db/41963
⼆、标量⼦查询的问题
  标量⼦查询、聚合标量⼦查询、⾏转列标量⼦查询、带top的标量⼦查询如何转成left join。
  之所以要转换，主要是因为标量⼦查询虽然写法上⽐较直观，容易理解，不⽤想就知道怎么写，但是存在：代码重复、多次访问同⼀个表问题，所以效率⽐较低。
1、标量⼦查询的模板
  按标量⼦查询⽅式，写出来的sql，都类似下⾯的代码：
l1,
--下⾯的代码是重复的，表和连接条件都类似，只是最后显⽰的字段不同
(select x1 from t where t.id = tb.id) as x1,
(select x2 from t where t.id = tb.id) as x2,
(select x3 from t where t.id = tb.id) as x3,
(select x4 from t where t.id = tb.id) as x4,
...
from tb
  可以看到，其中x1、x2、x3、x4等列，⼤部分代码都是重复的。当然，代码重复本⾝并没有太⼤的问题，最多就是复制粘贴，拷贝多次，然后把字段名改改，就⾏了。
2、标量⼦查询的执⾏过程
  上⾯的sql经过sql server的优化，⽣成执⾏计划，执⾏过程类似如下的过程：
（1）从tb表中取⼀条数据，⽤其中的id值，第1次和t表中的id值进⾏⽐较，如果相等，就返回t表的x1字段的值。
（2）从tb表中取⼀条数据，⽤其中的id值，第2次和t表中的id值进⾏⽐较，如果相等，就返回t表的x2字段的值。
（3）从tb表中取⼀条数据，⽤其中的id值，第3次和t表中的id值进⾏⽐较，如果相等，就返回t表的x3字段的值。
（4）从tb表中取⼀条数据，⽤其中的id值，第4次和t表中的id值进⾏⽐较，如果相等，就返回t表的x4字段的值。
（5）按照上述过程遍历整个tb表的每⼀条数据。
  从上⾯的过程可以看出，⼀共访问了t表4次，做了很多⽆⽤功。
  如果改成left join的⽅式，只需要访问1次t表，少访问3次，效率提⾼不少。
  所以，要尽量少⽤标量⼦查询的写法。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。