必要的查询字段

—

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

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