侧边栏壁纸
博主头像
Devlive 开源社区博主等级

行动起来,活在当下

  • 累计撰写 117 篇文章
  • 累计创建 32 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

构建一个优秀的SQL及优化方案

我是管理员哦
2024-01-29 / 0 评论 / 0 点赞 / 7 阅读 / 7293 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2024-01-29,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

必要的查询字段


由于存储的特性,选择需要的字段可加快字段的读取、减少数据量。避免采用*读取所有字段。

一般再CTE模式中这种风险不是很明显

  • 为什么要避免使用*?它带来的风险?

    • 它会增加查询分析SQL的成本(在数据库需要解析更多的对象、字段、权限、属性等相关内容,在复杂SQL语句,硬解析较多的情况下,会对服务造成沉重的负担)
    • 网络以及IO开销增长(*查询时会误带上非必要的数据,如log、IconMD5等之类的无用且超大文本字段,数据传输会几何增涨。如果服务和应用程序不在同一台机器,这种开销会急剧增长,并且使用它会杜绝索引的覆盖性)
      正确的使用方式

正确的SQL:

SELECT id, name FROM tableA

错误的SQL:

SELECT * FROM tableA

必要的筛选条件


对比大部分的查询SQL来说并非是查询全量数据,一般都是N天(它一般是数据的分区)的数据量级,并且如果多种类型的数据保存再一张表中,需要特意增加该类型的筛选方式。(比如tableA中保存的是应用相关的数据,其中有个字段type用于区分业务线,一般查询的情况下是查询某type的数据,并非是全量)

分区标识:

ymd是分区字段,visit_time是具体访问时间

正确的SQL:

SELECT id, name FROM tableA WHERE ymd = XXXX

错误的SQL:

SELECT id, name FROM tableA WHERE visit_time = XXXX

GROUP BY合理分配


GROUP BY中的某些字段维度如果顺序不合理将对查询带来很大的挑战,他将会降低整体的查询效率。一般的原则是将GROUP BY语句中字段按照每个字段distinct数据多少进行降序排列。

uid是用户id,gender是性别 同样distinct数据以后uid数据要远比比gender数据大很多

正确的SQL:

SELECT ... GROUP BY uid, gender

错误的SQL:

SELECT ... GROUP BY gender, uid

ORDER BY多使用LIMIT


ORDER BY需要扫描数据到单个worker节点进行排序,导致单个worker需要大量内存。如果是查询Top N或者Bottom N,使用limit可减少排序计算和内存压力。

尽量将排序的字段减少,它将能加快计算.

正确的SQL:

SELECT ... ORDER BY time LIMIT 100

错误的SQL:

SELECT ... 

使用近似聚合函数


大部分查询引擎或者分布式数据库(Presto, ClickHouse, Druid等)有一些近似聚合函数,对于允许有少量误差的查询场景,使用这些函数对查询性能有大幅提升。比如使用approx_distinct() 函数比count(distinct x)有大概2.3%的误差。

SELECT approx_distinct(uid) FROM tableA

非要精确去重,请用count group by语句代替

SELECT uid FROM tableA GROUP BY uid

当然对于一些特殊的引擎会有特殊的的优化函数,比如ClickHouse中有uniqExact, groupBitmap等。

避免使用多LIKE语句


如果是使用的Presto的话,一定要使用regexp_like,这是因为Presto查询优化器没有对多个like语句进行优化,使用regexp_like对性能有较大提升

正确的SQL:

SELECT ... FROM tableA WHERE regexp_like(method, 'GET|POST|PUT|DELETE')

错误的SQL:

SELECT ... FROM tableA WHERE method LIKE '%GET%' OR method LIKE '%POST%' OR method LIKE '%PUT%' OR method LIKE '%DELETE%'

JOIN避免大表再右侧


JOIN的默认算法是broadcast join(Presto),即将join左边的表分割到多个worker,然后将join右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大,则可能会报内存溢出错误。

正确的SQL:

SELECT ... FROM tableL l JOIN tableS s ON l.id = s.id

错误的SQL:

SELECT ... FROM tableS s JOIN tableL l ON l.id = s.id

如果tableL和tableS都比较大怎么办?为了防止内存报错

使用分布式JOIN(distributed-joins-enabled)
在每次查询开始使用distributed_join的session选项(这个我们没有开启,用户可查询时自行开启)

-- set session distributed_join = 'true'
SELECT ... FROM tableL JOIN l tableS s ON l.id = s.id

核心点就是使用分布式JOIN,Presto的这种配置类型会将左表和右表同时以join key的hash value为分区字段进行分区. 所以即使右表也是大表,也会被拆分.

缺点是会增加很多网络数据传输, 所以会比broadcast join的效率慢。

使用Rank函数代替row_number函数来获取Top N


在进行一些分组排序场景时,使用rank函数性能比row_number函数性能更好。

正确的SQL:

SELECT checksum(rnk)
FROM (
  SELECT rank() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY time DESC) AS rnk
  FROM tableA
) t
WHERE rnk = 1

错误的SQL:

SELECT checksum(rnk)
FROM (
  SELECT row_number() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY time DESC) AS rnk
  FROM tableA
) t
WHERE rnk = 1

多使用CTE语法树


CTE的四个好处:

  • 可以定义递归公用表表达式(CTE)
  • 当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁
  • GROUP BY语句可以直接作用于子查询所得的标量列
  • 可以在一个语句中多次引用公用表表达式(CTE)

CTE就是我们熟悉的WITH语法数,不过有部分数据库是不支持的,比如MySQL5的版本支持的不是很友好。

WITH cte1 AS (
    SELECT a1, a2, a3 
    FROM Table_1 
    WHERE a3 between 20180101 and 20180131
),              
cte2 AS (
    SELECT b1, b2, b3
    FROM Table_2
    WHERE b3 between 20180101 and 20180131
)               
SELECT 
    cte1.a1, cte1.a2, 
    cte2.b1, cte2.b2
FROM cte1
    JOIN cte2
    ON cte1.a3 = cte2.b3; 

使用UNION ALL代替UNION


和distinct的原因类似, UNION有去重的功能, 所以会引发内存使用的问题.

如果你只是拼接两个或者多个SQL查询的结果, 尽量考虑用UNION ALL。

避免字段的特殊处理


比如我们有个timeA字段的格式为yyyyMMdd,timeB字段的格式为yyyy-MM-dd,如果查询需要使用tableB的格式的话,不用通过函数去对timeA做处理转换timeB字段,处理的话系统会有大部分的CPU消耗处理每条数据转换,严重影响查询的效率。

不要在大结果集上构造虚拟列


正确的SQL:

WITH t AS (SELECT id ,pv, uv rate FROM tableA) SELECT id ,pv, uv , pv/uv rate FROM t

错误的SQL:

SELECT id ,pv, uv , pv/uv rate FROM tableA

虚拟列非常消耗资源浪费性能,拿到pv uv后在CTE构建的临时表中做比率计算。

不要在唯一列或大基数列上进行分组或去重操作


正确的SQL:

SELECT id  from tableA

错误的SQL:

SELECT id, count(1) cn from tableA group by id

基数太大会消耗过多的io和内存。

不要使用OR做条件连接


在WHERE子句中使用OR来连接条件,将导致引擎放弃使用索引而进行全表扫描。

正确的SQL:

select id from t where num = 10
union all
select id from t where num = 20

错误的SQL:

select id from t where num = 10 or num = 20

避免再where子句中对字段进行表达式操作


使用后将导致引擎放弃使用索引而进行全表扫描。

正确的SQL:

select id from t where num=100*2

错误的SQL:

select id from t where num/2=100

避免在where子句中对字段进行函数操作


使用后将导致引擎放弃使用索引而进行全表扫描。

正确的SQL:

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

错误的SQL:

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′

多使用EXISTS代替IN


正确的SQL:

select num from a where exists(select 1 from b where num=a.num)

错误的SQL:

select num from a where num in(select num from b)

多使用explain分析sql执行过程


EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了

EXPLAIN SELECT id, name from tableA WHERE id < 300;

不同的SQL引擎有不同的分析结果。

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin

评论区