前言
昨天敲着敲着代码,小杨哥跑过来给我说,快看他们大会议室演示报错了,还是一堆错了。完了啊在演示的时候报错!!!接下来我们分析一下是什么原因吧。
SQL 环境为人大金仓(内核postgresql)
问题分析
查看日志: 从日志打印看明显的是sql报错了
这个sql
明显是分页查询插件在统计分页条数,日志第一行最后一个单词是distinct
关键字,distinct
我是从来没有用到过分页中过,这个distinct
会根据后面的所有字段去重,性能太差导致连接超时。
小杨哥在这时说,人大金仓
distinct
只会对第一个字段去重处理,其他字段随机取一条。我当时就否定了他这个观点,有点经验的人也不会这么设计distinct吧,你这不是违反sql规范吗。实际上小杨哥说的那个语法应该是这个DISTINCT ON (column1, column2, ...) column1, column2, ...
我也马上把我结论告知了相关同事,经过测试确实也是因为这个distinct
导致请求时长超时。
解决方案
问题已经定位到了,就是distinct
后面的字段太多了。用distinct
主要目的就是解决 在1对多的连表查询时候,根据主表数据去重。
- 方式1:使用
distinct on ('id')
指定重复列 - 方式2:使用
group by
取代distinct
总结
-
在 语义相同时 ,
DISTINCT
和GROUP BY
性能会基于不同的数据结构以及查询语句表现的性能也不一样。 在实际开发中,可以调试两个语法,分别的耗时,然后再做出选择。(简单的SQL 一般是distinct
性能略优) -
在分页列表查询优化中,我还可以考虑去单独优化
count sql
开发中大多时候我们写分页查询,都是不需要管统计条数这个sql的,因为插件自动帮我们拼装执行了。有些时候需要优化的话,我们可以考虑覆盖统计sql,不动service
代码。一般的分页插件在mapper
中定义一个命名规则为queryname +_COUNT
的mapper
就行了,分页插件在执行queryname
查询的的时候,就会优化查询是否有queryname_COOUNT
的统计条数SQL
,有就使用自定义的。自定义的统计sql可以减少查询字段、在不影响统计结果的情况减少连表查询,来提升统计的效率
扩展知识 (distinct 和 group by 区别)
-
DISTINCT:
DISTINCT
用于从查询结果中去除重复的行,返回唯一的值。distinct on ('colunm ..')
指定需要去重的列,可以返回不参与去重的列,没有参与去重的列随机返回一条数据。 -
GROUP BY:
GROUP BY
用于将结果集按一个或多个列进行分组,并通常与聚合函数(如COUNT
、SUM
等)一起使用。不能查询没有参与分组的字段。
性能测试
查询相同的sql
使用日志中的sql作为测试,使用distinct
和distinct on
和group by
测试一下执行时间 。用时最短的是 distinct on
,因为只根据一个id进行去重;第二是group by ,毕竟分组的字段太多了差不多30个;distinct (去重30个字段)时间就直接指数级的增长了。(某些复杂的情况,group by 可能会优化分组字段和排序字段)
详情见:我另外一篇文章:【group by 比 distinct 性能好?】
distinct 原SQL耗时 14.2s
(distinct后只跟一个字段的话就是 0.2s)
distinct on (id): 0.7s
改成group by 耗时 1.9s
(如果只查一个id的话就0.2s)
·