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

必要的查询字段

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

一般再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引擎有不同的分析结果。