必要的查询字段
—
由于存储的特性，选择需要的字段可加快字段的读取、减少数据量。避免采用*读取所有字段。
- 为什么要避免使用*？它带来的风险？
- 它会增加查询分析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引擎有不同的分析结果。
