SQL优化:慎用标量子查询,改用left join提升查询效率

一、项目实例问题

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_id 
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 标量子查询优化案例  —  https://www.modb.pro/db/41963

二、标量子查询的问题

  标量子查询、聚合标量子查询、行转列标量子查询、带top的标量子查询如何转成left join。

  之所以要转换,主要是因为标量子查询虽然写法上比较直观,容易理解,不用想就知道怎么写,但是存在:代码重复、多次访问同一个表 问题,所以效率比较低。

1、标量子查询的模板

  按标量子查询方式,写出来的sql,都类似下面的代码:

select tb.col1,
       tb.col2,
       --下面的代码是重复的,表和连接条件都类似,只是最后显示的字段不同
       (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次,效率提高不少。

  所以,要尽量少用标量子查询的写法。

SQL优化:慎用标量子查询,改用left join提升查询效率

上一篇:spring-JdbcTemplate


下一篇:mysql常用函数、事务、索引