遇到场景
下午上班,DBA发了一个慢sql相关的记录过来
这个执行时间都太夸张了,都是执行相同的sql,选了执行时间最长的那条记录,获取对应参数.
sql
select g.id,g.groupid as groupid,g.type as type,g.name as name,gur.usertype as usertype,count(gur.usertype) as count from t_group g left join t_group_user_relation gur on g.groupid = gur.groupid where g.parentid = 20009848940 and g.type in ('1', '2', '23', '18') and g.status <> 'd' and gur.status <> 'd' and g.groupid in ( select groupid from t_group_user_relation where userid = '20011738850' and status <> 'd' ) group by g.groupid,g.type,g.name,gur.usertype,g.id
分析
判断是否是数据库堵死
先查看了对应平台的数据库io,比较平稳,使用率很低,用自己的查询权限执行了一下,跑了十几秒根本听不下来,立马手动强制终止,初步判断应该是sql问题.
怀疑是否是查询的数据量太大
因为自己对业务十分了解,就单独的查询了其中一个表中相关数据,发现不超过100条,这个是联表查询,过滤条件更多,应该查询的会更少,排除不是数据量问题.
拆分尝试
这个sql语句整体没多少难度,唯一要验证的是g.groupid in ( select groupid from t_group_user_relation where userid = '20011738850' and status <> 'd' ) 这一句,抱着试一试的态度,单独查询了子查询的结果集,查出的结果只有5个,将查出的结果集一个个手写到in 中,查询了一下,发现不到一秒就查询了出来.正中靶心,应该就是in 中子查询导致的.
查找资料证实
通过查找网上的相关资料证实,pgsql在in中子查询的话,是将除这个子查询外的结果集再一条一条进行查询;打个比方,比如除这个in之外的查询结果集是100,in中的子查询结果是5,就是说要查询500次,是查询500次,不是比对和过滤,这性能当然拉跨的吓人.
优化
WITH groupids AS (select groupid from t_group_user_relation where userid = '20011738850' and status <> 'd' ) select g.id,g.groupid as groupid,g.type as type,g.name as name,gur.usertype as usertype,count(gur.usertype) as count from t_group g left join t_group_user_relation gur on g.groupid = gur.groupid where g.parentid = 20009848940 and g.type in ('1', '2', '23', '18') and g.status <> 'd' and gur.status <> 'd' and g.groupid in (select * from groupids ) group by g.groupid,g.type,g.name,gur.usertype,g.id
经过DBA线上验证,执行效果为0.06s,这性能真是质的飞越.
