Spark从SQL的解析、执行与调优到Sparksql的解析的史上最全介绍

从SQL的解析、执行与调优到Sparksql的解析与应用

SparkSQL总体流程介绍

在阐述Join实现之前，我们首先简单介绍SparkSQL的总体流程，一般地，我们有两种方式使用SparkSQL，一种是直接写sql语句，这个需要有元数据库支持，例如Hive等，另一种是通过Dataset/DataFrame编写Spark应用程序。如下图所示，sql语句被语法解析(SQL AST)成查询计划，或者我们通过Dataset/DataFrame提供的APIs组织成查询计划，查询计划分为两大类：逻辑计划和物理计划，这个阶段通常叫做逻辑计划，经过语法分析(Analyzer)、一系列查询优化(Optimizer)后得到优化后的逻辑计划，最后被映射成物理计划，转换成RDD执行。

执行

上图是对最简单的sql语句的重新标注：SELECT表示是一种具体的操作，即查询数据，”f1,f2,f3"表示返回的结果，tableX是数据源，condition部分是查询条件。
SQL表达式中的顺序与常见的处理逻辑其在表达的顺序上有差异。

SQL Server核心引擎:

    语言处理（Language Processing）涉及诸如解析查询文本以确保其在语法上有效，将表引用绑定到实际数据库对象，派生表达式类型，语义分析（例如检查查询是否不试图执行表）之类的事情。 并将GROUP BY引用绑定到适当的逻辑范围。
    查询优化器旨在找到匹配的逻辑语义查询的良好的物理执行计划，以及查询执行负责运行该计划完成。
    在此之下，存储引擎提供对物理存储的访问以及锁定和事务服务。最后，SQL-OS是提供线程，内存管理和调度服务的层。
    ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200922103153286.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1Nzg4OA==,size_16,color_FFFFFF,t_70#pic_center)

附：
SQL Server核心引擎深入研究
https://sqlperformance.com/author/paulwhitenzgmail-com

SQL语句在分析执行过程中会经历下图所示的几个步骤:
1. 语法解析；
2. 操作绑定；
3. 优化执行策略；
4. 交付执行；
对于上面显示的组件使用相同的颜色，查询处理的高级概述如下所示：

（一）语法解析

# 语法解析之后，会形成一棵语法树，如下图所示。树中的每个节点是执行的rule,整棵树称之为执行策略。

SELECT
p.Name,
Total = SUM(inv.Quantity)
FROM
Production.Product AS p,
Production.ProductInventory AS inv
WHERE
inv.ProductID = p.ProductID
AND p.Name LIKE N’[A-G]%’
GROUP BY
p.Name;

（二）操作绑定

传递给优化器的逻辑操作树如下所示：
这看起来与第一部分中看到的原始逻辑树非常相似，尽管基数估计已添加到每个主节点，并且树的布局已扩展到某种形式，使系统更易于使用。
除了简单的基数估计之外，请记住，每个节点还具有从与查询中的表相关联的熟悉的统计信息派生而来的统计信息对象（直方图和频率信息）。

（三）优化执行策略

    形成上述的执行策略树还只是第一步，因为这个执行策略可以进行优化，所谓的优化就是对树中节点进行合并或是进行顺序上的调整。
    以大家熟悉的join操作为例，在join后得到的结果集中过滤得到结果集，优化器会对该策略进行优化。
    SELonJN:该规则将合适的关系SELECT（行过滤器）合并到逻辑联接操作中。

下图给出一个join优化的示例。A JOIN B等同于B JOIN A，但是顺序的调整可能给执行的性能带来极大的影响，下图就是调整前后的对比图。
JoinCommute：探索了不同的逻辑联接顺序，利用了A JOIN B与B JOIN A内部联接等效的事实。尽管在逻辑上是等效的，但是一旦逻辑替代项被更高版本的实现规则转换为物理实现，则不同的连接顺序可能具有不同的性能特征。

再举一例，一般来说尽可能的先实施聚合操作(Aggregate)然后再join。
GbAggBeforeJoin：该规则探索在联接下推动聚合操作的可能性。

如之前所述，实现规则将逻辑树的一部分转换为物理选择：
该图显示了三个联接实现规则：JNtoNL（嵌套循环），JNtoHS（哈希联接），JNtoSM（排序合并）；
按组聚合：GbAggToStrm（流聚合）和GbAggToHS（散列聚合）的两种实现；
以及SelectToFilter，GetToScan和GetIdxToRng。

（四）交付执行

    传递给基于成本的优化的逻辑树，将该结构复制到每个逻辑节点,每个逻辑节点一组，如下所示：

最终执行计划的图形行成

1.语法解析之后生成一个执行策略树；
2.执行策略树可以优化，优化的过程就是对树中节点进行合并或者顺序调整。

调优

    在使用DBMS时经常对系统的性能有非常高的要求:不能占用过多的系统内存和CPU资源、要尽可能快的完成的数据库操作、要有尽可能高的系统吞吐量。如果系统 开发出来不能满足要求的所有性能指标，则必须对系统进行调整，这个工作被称为调优。
    绝对DBMS的性能的因素有两个因素:硬件和软件。
    使用频率的的CPU、使用多处理器、加大内存容量、增加Cache、提高网络速度等这些都是非常有效的硬件调优方式， 不过对硬件进行调优对系统性能的提高是有限的，如果有非常好的硬件条件但是如果编写的SQL质量非常差的话系统的性能并不会有明显的改善，而如果能对SQL语句进行充分的优化的话即使硬件条件稍差的话，系统性能的变化也是非常惊人的。

（一）基本原则

    “二八原理”是一个普遍的真理，特别是在计算机的世界中表现的更加明显，那就是20%的代码的资源消耗占用了80%的总资源消耗。SQL语句也是一种代码，因此它也符合这个原理。在进行SQL调优的时候应该把主要精力放到这20%的最消耗系统资 源的SQL语句中，不要想把所有的SQL语句都调整到最优状态。
    优化不是“一劳永逸”的，必须随着情况的改变进行相应的调整。当数据库设计发生变化，包括更改表结构：字段和索引的增加、删除或改名等；业务逻辑发生变化：如查询方式、取值范围发生改变等等。在这种情况下，也必须对原有的优化进行调整，以适应效率上的需求。

（二）索引

    索引是数据库调优的最根本的优化方法，很多优化手法都是围绕索引展开的，可以说索引是一切优化手法的“内功”，而所有的优化手法都是由索引衍化出来的招式而已。根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型:
    聚簇索引:数据表的物理顺序与索引顺序相同;
    非聚簇索引:数据表的物理顺序与索引顺序不相同;

举例:
字典的目录就是一种索引，因为通过目录我们可以很快的定位到要检索的内容，而不用从头到尾把字典翻一遍。汉语字典一般都至少提供两种目录，一种是拼音目录，一种是偏旁部首目录。汉语字典是按照拼音的顺序排列的，因此拼音目录就是聚集索引，而偏旁部首目录则是非聚集索引。
应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时要需要每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个，而非聚集索引则可以创建多个。

    由于索引需要占据一定的存储空间，而且索引也会降低数据插入、更新和删除的速度，所以应该只创建必要的索引，一般是在检索的时候用的字段中创建索引。
    索引还会造成存储碎片的问题。当删除一条记录时将会导致对应的索引中的该记录的对应项为空，由于索引是采用B树结构存储的，所以对应的索引项并不会被删除， 经过一段时间的增删改操作后，数据库中就会出现大量的存储碎片，这和磁盘碎片、内存碎片产生原理是类似的，这些存储碎片不仅占用了存储空间，而且降低了数据库运行的速度。如果发现索引中存在过多的存储碎片的话就要进行“碎片整理”了，最方便的“碎片整理”手段就是重建索引，重建索引会将先前创建的索引删除然后重新创建索引，主流数据库管理系统都提供了重建索引的功能，比如REINDEX、REBUILD等，如果使用的数据库管理系统没有提供重建索引的功能，可以首先用DROP INDEX语句删除索引，然后用ALTER TABLE语句重新创建索引。

附：
B树：
在计算机科学中，B树（英语：B-tree）是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。B树，概括来说是一个一般化的二叉查找树（binary search tree）一个节点可以拥有2个以上的子节点。与自平衡二叉查找树不同，B树适用于读写相对大的数据块的存储系统，例如磁盘。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。这种数据结构常被应用在数据库和文件系统的实现上。

（三）表扫描和索引查找

    一般地，系统访问数据库中的数据，可以使用两种方法:全表扫描和索引查找。
    全表扫描，就是指系统必须在数据表中逐条检索表中的每条记录，以检查该记录是否匹配检索条件。全表扫描有可能会造成巨大的性能损失，当然也有可能不会影响性能，这取决于表中的数据量，如果表中有上千万条甚至上亿条记录的话，全表扫描的速度会非常慢，而如果表中只有几条、几十条记录的话表扫描的性能消耗就可以忽略不计了。 当表中数据量比较小的时候，使用全表扫描非常有用。但是随着表中数据量的增加，全表扫描会导致系统性能严重下降。
    如果表中有索引并且待匹配条件符合索引的要求的话，DBMS就不会执行全表扫描，而是直接到索引中查找，这将大大加快检索的速度。
    DBMS中都有查询优化器，它会根据分布的统计信息生成该查询语句的优化执行规划，以提高访问数据的效率为目标，确定是使用全表扫描还是使用索引查找。
    是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：
    1.对于原始排序的表 
    仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。
    2.对于未排序的表
    仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

注意：并不是表中存在索引在进行检索的时候就会使用索引查找，如果使用不当检索的过程仍然会是采用全表扫描，这样索引就起不到效果了。

（四）优化手法

    下面将会列出了一些常用的优化手法，注意这些优化手法只是一些常规条件下的优化手法，具体的优化效果是与使用的 DBMS以及数据的特点密切相关的，需要根据具体情况来使用不同的优化手法，如果使用不当的话有可能会适得其反。

1.创建必要的索引

    经常需要进行检索的字段上创建索引，比如经常要按照图书名称进行检索，那么就应该在图书名称字段上创建索引，如果经常要按照员工部门和员工岗位级别进行检索，那么就应该在员工部门和员工岗位级别这两个字段上创建索引。创建索引给检索带来的性能提升往往是巨大的，因此在发现检索速度过慢的时候应该首先想到的就是创建索引。

建立索引常用的规则如下：
1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
注意：使用索引是优化的核心手段，使用基于函数的索引、分区索引、位图索引等都可以优化SQL查询。
查询举例：

SELECT
p.Name,
Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
inv.ProductID = p.ProductID
WHERE
p.Name LIKE N’[A-G]%’
GROUP BY
p.Name

#查询使用的时间成本为0.0295s;

#强制索引查询：

SELECT
p.Name,
Total = SUM(inv.Quantity)
FROM Production.Product AS p WITH (INDEX([AK_Product_Name]))
JOIN Production.ProductInventory AS inv ON
inv.ProductID = p.ProductID
WHERE
p.Name LIKE N’[A-G]%’
GROUP BY
p.Name

#查询使用的时间成本为 0.0316s;
# 索引搜索的结果按Name（索引键）而不是排序ProductID，因此需要进行排序。
# 看起来这种新排序比扫描节省的开销要多一些,由于测试样例数据库，不是大型数据库，所以这些数字相当小，但是这些差异在实际系统中可能很重要。

#强制索引查找和哈希联接（将合并联接设置成哈希联接）：

SELECT
p.Name,
Total = SUM(inv.Quantity)
FROM Production.Product AS p WITH (INDEX([AK_Product_Name]))
JOIN Production.ProductInventory AS inv ON
inv.ProductID = p.ProductID
WHERE
p.Name LIKE N’[A-G]%’
GROUP BY
p.Name
OPTION (HASH JOIN, RECOMPILE);

#查询使用的时间成本为 0.0348s;
# 哈希联接具有很高的启动成本（并且需要内存工作空间授权）。我们可以尝试其他方法进行优化，但是在这种情况下，优化器显然是正确的。上面的手动探索表明，优化器通常确实会很快找到一个好的计划（有时甚至可能找到最佳的计划）。

2.使用预编译查询

    程序中通常是根据用户的输入来动态执行SQL语句，这时应该尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL执行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，这样以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大提高执行的速度。
    使用PreparedStatement对象操作sql语句，PreparedStatement 接口继承 Statement 接口，效率高于 Statement，支持动态绑定参数，同时具备 SQL 语句预编译能力，可防止出现 SQL 注入问题。

语法：
PREPARE statement_name FROM sql_text /定义/
EXECUTE statement_name [USING variable [,variable…]] /执行预处理语句/
DEALLOCATE PREPARE statement_name /删除定义/
示例
mysql> PREPARE prod FROM “INSERT INTO table1 VALUES(?,?)”;
mysql> SET @p=‘1’;
mysql> SET @q=‘2’;
mysql> EXECUTE prod USING @p,@q;
mysql> SET @name=‘3’;
mysql> EXECUTE prod USING @p,@name;
mysql> DEALLOCATE PREPARE prod;
附：
SQL注入（SQL injection）：
也称SQL注入或SQL注码，是发生于应用程序与数据库层的安全漏洞。简而言之，是在输入的字符串之中注入SQL指令，在设计不良的程序当中忽略了字符检查，那么这些注入进去的恶意指令就会被数据库服务器误认为是正常的SQL指令而运行，因此遭到破坏或是入侵。

参数化查询（parameterized query）：
指在设计与数据库链接并访问数据时，在需要填入数值或数据的地方，使用参数（parameter）来给值，这个方法当前已被视为最有效可预防SQL注入攻击的攻击手法的防御方式。除了安全因素，相比起拼接字符串的SQL语句，参数化的查询往往有性能优势。因为参数化的查询能让不同的数据通过参数到达数据库，从而公用同一条SQL语句。大多数数据库会缓存解释SQL语句产生的字节码而省下重复解析的开销。如果采取拼接字符串的SQL语句，则会由于操作数据是SQL语句的一部分而非参数的一部分，而反复大量解释SQL语句产生不必要的开销。

3.调整 WHERE 子句中的连接顺序

# DBMS一般采用自下而上的顺序解析WHERE子句，根据这个原理,表连接最好写在其他WHERE条件之前，那些可以过滤掉最大数量记录。

SELECT
*
FROM
T_Person
WHERE
FSalary > 50000
AND FPosition= ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM T_Manager WHERE FManagerId=2);

# 将子查询的条件放到最前面，下面的SQL语句性能比较好:

SELECT
*
FROM
T_Person
WHERE
25 < (SELECT COUNT(*) FROM T_Manager WHERE FManagerId=2)
AND FSalary > 50000
AND FPosition= ‘MANAGER’ ;

4.SELECT 语句中避免使用’*’

    SELECT *比较简单，但是除非确实需要检索所有的列，否则将会检索出不需要的列，这会增加网络的负载和服务器的资源消耗;即使确实需要检索所有列，也不要使用SELECT *，因为这是一个非常低效的方法，DBMS在解析的过程中，会将*依次转换成所有的列名，这意味着将耗费更多的时间。

5.尽量将多条 SQL 语句压缩到一句 SQL 中

    每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。
    用Case语句合并多重扫描。

6.用 Where 子句替换 HAVING 子句

    避免使用HAVING子句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此而外，应该将条件写在WHERE子句中。

7.使用表的别名

    当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少那些由列名歧义引起的语法错误。

8.用 EXISTS 替代 IN

# 在查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用EXISTS而不是IN通常将提高查询的效率，因为IN子句将执行一个子查询内部的排序和合并。IN是在内存里遍历比较，而EXISTS 需要查询数据库，所以当表数据量较大时，EXISTS效率优于IN。
# 假设有A、B表，IN()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。
# 假设有A、B表，EXISTS()会执行A.length次，它并不缓存EXISTS()结果集，因为EXISTS()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。

SELECT
*
FROM
T_Employee
WHERE
FNumber> 0
AND FDEPTNO IN (SELECT FNumber FROM T_Department WHERE FMangerName = ‘Tome’)

SELECT
*
FROM
T_Employee
WHERE
FNumber > 0
AND EXISTS (SELECT 1 FROM T_Department WHERE T_Department. FDEPTNO = EMP.FNumber
AND FMangerName = ‘Tome’)

9.用表连接替换 EXISTS

# 通常来说，表连接的方式比EXISTS更有效率，因此如果可能的话尽量使用表连接替换EXISTS。

SELECT
FName
FROM
T_Employee
WHERE
EXISTS(SELECT 1 FROM T_Department WHERE T_Employee.FDepartNo= FNumber AND FKind=‘A’);

SELECT
FName
FROM
T_Department, T_Employee
WHERE
T_Employee. FDepartNo = T_Departmen. FNumber
AND FKind = ‘A’ ;

10. 避免在索引列上使用计算

# 在WHERE子句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表扫描。任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。

SELECT
*
FROM
T_Employee
WHERE
FSalary * 12 >25000;
# 由于在大于号左边的是 FSalary 与 12 的成绩表达式，这样 DBMS 的优化器将不会 使用字段 FSalary 的索引，因为 DBMS 必须对 T_Employee 表进行全表扫描，从而计算 FSalary * 12 的值，然后与 25000 进行比较。将上面的 SQL 语句修改为下面的等价写法 后 DBMS 将会使用索引查找，从而大大提高了效率：
SELECT
*
FROM
T_Employee
WHERE
FSalary >25000/12;

# 同样的，不能在索引列上使用函数，因为函数也是一种计算，会造成全表扫描。

SELECT
*
FROM
T_Example
WHERE
ABS(FAmount)=300

SELECT
*
FROM
T_Example
WHERE
FAmount=300 OR FAmount=-300

11.用 UNION ALL 替换 UNION

    当SQL 语句需要 UNION 两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用 UNION 这两个结果集同样会尝试进行合并，然后在输出最终结果前进行排序。 因此，如果检索结果中不会有重复的记录的话，应该用 UNION ALL 替代 UNION，这 样效率就会因此得到提高。

12.避免隐式类型转换造成的全表扫描

# 假设T_Person 表的字符串类型字段 FLevel 为人员的级别，在 FAge 字段上建有索引。 我们执行下面的 SQL 语句用于检索所有级别等于 10 的员工：

SELECT
FId,FAge,FName
FROM
T_Person
WHERE
FAge=10
# 在这个 SQL 语句中，将字符串类型字段 FLevel 与数值 10 进行比较，由于在大部分数据库中隐式转换类型中数值类型的优先级高于字符串类型，因此 DBMS 会对 FAge字段进行隐式类型转换，相当于执行了下面的 SQL 语句：
SELECT
FId,FAge,FName
FROM
T_Person
WHERE
TO_NUMBER(FAge)=10
# 由于在索引字段上进行了计算，所以造成了索引失效而使用全表扫描。因此应将 SQL 语句做如下修改：
SELECT
FId,FAge,FName
FROM
T_Person
WHERE
FAge=‘10’

13.like子句尽量前端匹配

因为 LIKE 参数使用的非常频繁，因此如果能够对 LIKE 子句使用索引，将很高的提高查询的效率。使用 LIKE 运算符的时候，“a%“将会使用索引，而"a%c"和”%c"则会使用全表扫描，因此"a%c"和”%c"不能被有效的评估匹配的数量。

select
*
from
city
where
name like ‘%S%’
# 以上查询的执行计划用了全表扫描（TABLE ACCESS FULL）

select
*
from
city
where
name like ‘S%’
# 那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。这意味着优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用like ‘S%’。

14.防止检索范围过宽

# 如果 DBMS 优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描。增加查询的范围限制，避免全范围的搜索。 下面是几种可能造成检索范围过宽的情况： 
# 使用 IS NOT NULL 或者不等于判断，可能造成优化器假设匹配的记录数太多。 
# 尽量去掉 "<>"，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为"OR"方式。

#以下查询表record 中时间ActionTime小于2001年3月1日的数据：

select
*
from
record
where
ActionTime < to_date (‘20010301’ ,‘yyyymm’)
# 查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。修改如下：

select
*
from
record
where
ActionTime < to_date (‘20010301’ ,‘yyyymm’)
and ActionTime > to_date (‘20010101’ ,‘yyyymm’)
# 后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把’20010301’换成一个变量，根据取值的机率，可以有一半以上的机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

Spark SQL

一、定义

    Spark SQL是Spark用来处理结构化数据的一个模块，它提供了一个编程抽象叫做DataFrame并且作为分布式SQL查询引擎的作用。
    相比于Spark RDD API，Spark SQL包含了对结构化数据和在其上运算的更多信息，Spark SQL使用这些信息进行了额外的优化，使对结构化数据的操作更加高效和方便。

二、历史

三、特点

（一）易整合

将sql查询与spark程序无缝混合，可以使用java、scala、python、R等语言的API操作。

（二）统一的数据访问

以相同的方式连接到任何数据源。

（三）兼容Hive

支持hiveSQL的语法。

（四）标准的数据连接

可以使用行业标准的JDBC或ODBC连接。

四、DataFrame和DataSet

（一）DataFrame

1.定义

    DataFrame的前身是SchemaRDD，从Spark 1.3.0开始SchemaRDD更名为DataFrame。与SchemaRDD的主要区别是：DataFrame不再直接继承自RDD，而是自己实现了RDD的绝大多数功能。你仍旧可以在DataFrame上调用rdd方法将其转换为一个RDD。
    在Spark中，DataFrame是一种以RDD为基础的分布式数据集，类似于传统数据库的二维表格，DataFrame带有Schema元信息，即DataFrame所表示的二维表数据集的每一列都带有名称和类型，但底层做了更多的优化。DataFrame可以从很多数据源构建，比如：已经存在的RDD、结构化文件、外部数据库、Hive表。

2.DataFrame与RDD的区别

    RDD可看作是分布式的对象的集合，Spark并不知道对象的详细模式信息，DataFrame可看作是分布式的Row对象的集合，其提供了由列组成的详细模式信息，使得Spark SQL可以进行某些形式的执行优化。
    DataFrame还配套了新的操作数据的方法，DataFrame API（如df.select())和SQL。

此外DataFrame还引入了off-heap,意味着JVM堆以外的内存, 这些内存直接受操作系统管理（而不是JVM）。Spark能够以二进制的形式序列化数据(不包括结构)到off-heap中, 当要操作数据时, 就直接操作off-heap内存. 由于Spark理解schema, 所以知道该如何操作。
RDD是分布式的Java对象的集合。DataFrame是分布式的Row对象的集合。DataFrame除了提供了比RDD更丰富的算子以外，更重要的特点是提升执行效率、减少数据读取以及执行计划的优化。有了DataFrame这个高一层的抽象后，我们处理数据更加简单了，甚至可以用SQL来处理数据了，对开发者来说，易用性有了很大的提升。不仅如此，通过DataFrame API或SQL处理数据，会自动经过Spark 优化器（Catalyst）的优化，即使你写的程序或SQL不高效，也可以运行的很快。

3.DataFrame与RDD的优缺点

（1）RDD
优点:
（1）编译时类型安全，编译时就能检查出类型错误；
（2）面向对象的编程风格，直接通过对象调用方法的形式来操作数据；
缺点:
（1）序列化和反序列化的性能开销，无论是集群间的通信, 还是IO操作都需要对对象的结构和数据进行序列化和反序列化；
（2）GC的性能开销，频繁的创建和销毁对象, 势必会增加GC；
（2）DataFrame
优点：
（1）DataFrame通过引入schema和off-heap（不在堆里面的内存，指的是除了不在堆的内存，使用操作系统上的内存），解决了RDD的缺点, Spark通过schame就能够读懂数据, 因此在通信和IO时就只需要序列化和反序列化数据, 而结构的部分就可以省略了；
（2）通过off-heap引入，可以快速的操作数据，避免大量的GC；
缺点：
（1）DataFrame不是类型安全的；
（2）API也不是面向对象风格的。

（二）DataSet

1.定义

    DataSet是分布式的数据集合，Dataset提供了强类型支持，也是在RDD的每行数据加了类型约束。DataSet是在Spark1.6中添加的新的接口。它集中了RDD的优点（强类型和可以用强大lambda函数）以及使用了Spark SQL优化的执行引擎。DataSet可以通过JVM的对象进行构建，可以用函数式的转换（map/flatmap/filter）进行多种操作。

2.DataSet与RDD的区别

（1）RDD

（2）DataFrame

（3）DataSet

DataSet包含了DataFrame的功能，Spark2.0中两者统一，DataFrame表示为DataSet[Row]，即DataSet的子集。相比DataFrame，Dataset提供了编译时类型检查:
（1）DataSet可以在编译时检查类型;
（2）并且是面向对象的编程接口。
3.DataSet相关操作

（三）对比

1.发展

2.关系

3.共性

 1、RDD、DataFrame、Dataset全都是spark平台下的分布式弹性数据集，为处理超大型数据提供便利;
 2、三者都有惰性机制，在进行创建、转换，如map方法时，不会立即执行，只有在遇到Action如foreach时，三者才会开始遍历运算，极端情况下，如果代码里面有创建、转换，但是后面没有在Action中使用对应的结果，在执行时会被直接跳过;
 3、三者都会根据spark的内存情况自动缓存运算，这样即使数据量很大，也不用担心会内存溢出;
 4、三者都有partition的概念;
 5、三者有许多共同的函数，如filter，排序等;
 6、DataFrame和Dataset均可使用模式匹配获取各个字段的值和类型;

4.区别

RDD:

     1、RDD一般和spark mlib同时使用;
     2、RDD不支持sparksql操作;

DataFrame:

   1、与RDD和Dataset不同，DataFrame每一行的类型固定为Row，只有通过解析才能获取各个字段的值;
   2、DataFrame与Dataset一般不与spark mlib同时使用;
   3、DataFrame与Dataset均支持sparksql的操作，还能注册临时表/视图；
   4、DataFrame与Dataset支持一些特别方便的保存方式，比如保存成csv；

Dataset:

    1、Dataset和DataFrame拥有完全相同的成员函数，区别只是每一行的数据类型不同；
    2、DataFrame也可以叫Dataset[Row],每一行的类型是Row，不解析，每一行究竟有哪些字段，各个字段又是什么类型都无从得知，只能用getAS方法或者共性中的第七条提到的模式匹配拿出特定字段；而Dataset中，每一行是什么类型是不一定的，在自定义了case
   class之后可以很自由的获得每一行的信息；Dataset在需要访问列中的某个字段时是非常方便的，然而，如果要写一些适配性很强的函数时，如果使用Dataset，行的类型又不确定，可能是各种case
   class，无法实现适配，这时候用DataFrame即Dataset[Row]就能比较好的解决问题。

5.转化

五、执行

    一般地，有两种方式使用SparkSQL，一种是直接写sql语句，这个需要有元数据库支持，例如Hive等，另一种是通过Dataset/DataFrame编写Spark应用程序。如下图所示，sql语句被语法解析(SQL AST)成查询计划，或者我们通过Dataset/DataFrame提供的APIs组织成查询计划，查询计划分为两大类：逻辑计划和物理计划，这个阶段通常叫做逻辑计划，经过语法分析(Analyzer)、一系列查询优化(Optimizer)后得到优化后的逻辑计划，最后被映射成物理计划，转换成RDD执行。

（一）实现

SQL执行流程：

    1. 语法解析；
    2. 操作绑定；
    3. 优化执行策略；
    4. 交付执行；

有了上述内容的铺垫，已经意识到Spark如果要很好的支持sql，势必也要完成，解析，优化，执行的三大过程。
整个SQL部分的代码，其大致分类如下图所示:
1. SqlParser生成LogicPlan Tree；
2. Analyzer和Optimizer将各种rule作用于LogicalPlan Tree；
3. 最终优化生成的LogicalPlan生成Spark RDD；
4. 最后将生成的RDD交由Spark执行；

1.生成LogicalPlan

# 在sql中引入了一种新的RDD，即SchemaRDD,且看SchemaRDD的构造函数:

class SchemaRDD(
@transient val sqlContext: SQLContext,
@transient protected[spark] val logicalPlan: LogicalPlan)
# 构造函数中总共两入参一为SparkContext,另一个LogicalPlan。LogicalPlan又是如何生成的呢？
# 要回答这个问题，不得不回到整个问题的入口点sql函数，sql函数的定义如下:

def sql(sqlText: String): SchemaRDD = {
val result = new SchemaRDD(this, parseSql(sqlText))

result.queryExecution.toRdd
result

}
# parseSql(sqlText)负责生成LogicalPlan,parseSql就是SqlParser的一个实例。
# SqlParser这一部分的代码要理解起来关键是要搞清楚StandardTokenParsers的调用规则。由于apply函数可以不被显示调用，所以parseSql(sqlText)一句其实会隐式的调用SqlParser中的apply函数。

def apply(input: String): LogicalPlan = {
phrase(query)(new lexical.Scanner(input)) match {
case Success(r, x) => r
case x => sys.error(x.toString)
}
}
# phrase(query)(new lexical.Scanner(input))：翻译过来就是如果输入的input字符串符合Lexical中定义的规则，则继续使用query处理。
# 看一下query的定义：

protected lazy val query: Parser[LogicalPlan] =
select * (
UNION ~ ALL ^^^ { (q1: LogicalPlan, q2: LogicalPlan) => Union(q1, q2) } |
UNION ~ opt(DISTINCT) ^^^ { (q1: LogicalPlan, q2: LogicalPlan) => Distinct(Union(q1, q2)) }
) | insert

# 到了这里终于看到有LogicalPlan了，也就是说将普通的string转换成LogicalPlan在这里发生了。

2.QueryExecution

# 第一阶段，将string转换成为logicalplan tree，第二阶段将各种规则作用于LogicalPlan。
# 在第一阶段中展示的代码，sql函数中的"result.queryExecution.toRdd"会触发优化规则,此处的queryExecution就是QueryExecution。QueryExecution是一个抽象类(scala的一个语法糖:scala隐含的创建了一个QueryExecution的子类并初始化):

protected[sql] def executePlan(plan: LogicalPlan): this.QueryExecution =
new this.QueryExecution { val logical = plan }

QueryExecution：
protected abstract class QueryExecution {
def logical: LogicalPlan

lazy val analyzed = analyzer(logical)
lazy val optimizedPlan = optimizer(analyzed)
lazy val sparkPlan = planner(optimizedPlan).next()
lazy val executedPlan: SparkPlan = prepareForExecution(sparkPlan)

/** Internal version of the RDD. Avoids copies and has no schema */
lazy val toRdd: RDD[Row] = executedPlan.execute()

protected def stringOrError[A](f: => A): String =
  try f.toString catch { case e: Throwable => e.toString }

def simpleString: String = stringOrError(executedPlan)

override def toString: String =
  s"""== Logical Plan ==
     |${stringOrError(analyzed)}
     |== Optimized Logical Plan ==
     |${stringOrError(optimizedPlan)}
     |== Physical Plan ==
     |${stringOrError(executedPlan)}
  """.stripMargin.trim

def debugExec() = DebugQuery(executedPlan).execute().collect()

}

# 三大步
lazy val analyzed = analyzer(logical)
lazy val optimizedPlan = optimizer(analyzed)
lazy val sparkPlan = planner(optimizedPlan).next()
# 无论analyzer还是optimizer，它们都是RuleExecutor的子类

# RuleExecutor的默认处理函数是apply,对所有的子类都是一样的，RuleExecutor的apply函数定义:

def apply(plan: TreeType): TreeType = {
var curPlan = plan

batches.foreach { batch =>
  val batchStartPlan = curPlan
  var iteration = 1
  var lastPlan = curPlan
  var continue = true

  // Run until fix point (or the max number of iterations as specified in the strategy.
  while (continue) {
    curPlan = batch.rules.foldLeft(curPlan) {
      case (plan, rule) =>
        val result = rule(plan)
        if (!result.fastEquals(plan)) {
          logger.trace(
            s"""
              |=== Applying Rule ${rule.ruleName} ===
              |${sideBySide(plan.treeString, result.treeString).mkString("\n")}
            """.stripMargin)
        }

        result
    }
    iteration += 1
    if (iteration > batch.strategy.maxIterations) {
      logger.info(s"Max iterations ($iteration) reached for batch ${batch.name}")
      continue = false
    }

    if (curPlan.fastEquals(lastPlan)) {
      logger.trace(s"Fixed point reached for batch ${batch.name} after $iteration iterations.")
      continue = false
    }
    lastPlan = curPlan
  }

  if (!batchStartPlan.fastEquals(curPlan)) {
    logger.debug(
      s"""
      |=== Result of Batch ${batch.name} ===
      |${sideBySide(plan.treeString, curPlan.treeString).mkString("\n")}
    """.stripMargin)
  } else {
    logger.trace(s"Batch ${batch.name} has no effect.")
  }
}

curPlan

}

# 对于RuleExecutor的子类来说，最主要的是定义自己的batches,来看analyzer中的batches是如何定义的

val batches: Seq[Batch] = Seq(
Batch(“MultiInstanceRelations”, Once,
NewRelationInstances),
Batch(“CaseInsensitiveAttributeReferences”, Once,
(if (caseSensitive) Nil else LowercaseAttributeReferences :: Nil) : *),
Batch(“Resolution”, fixedPoint,
ResolveReferences ::
ResolveRelations ::
NewRelationInstances ::
ImplicitGenerate ::
StarExpansion ::
ResolveFunctions ::
GlobalAggregates ::
typeCoercionRules :*),
Batch(“AnalysisOperators”, fixedPoint,
EliminateAnalysisOperators)
)

# batch中定义了一系列的规则， ::表示cons的意思，即连接生成一个list（语法糖）。
# Batch构造函数中需要指定一系列的Rule,像ResolveReferences就是Rule。

3.LogicalPlan转换成Physical Plan

# 在阶段3最主要的代码就两行

lazy val executePlan: SparkPlan = prepareForExecution(sparkPlan)
lazy val toRdd: RDD[Row] = executedPlan.execute()

# 与LogicalPlan不同，SparkPlan最重要的区别就是有execute函数
# 针对Sparkplan的具体实现，又要分成UnaryNode, LeafNode和BinaryNode，简要来说即单目运算符操作，叶子结点，双目运算符操作。每个子类的具体实现可以参考源码。

4.触发RDD执行

# RDD被触发真正执行的过程:

teenagers.map(p => “name:”+p(0)).foreach(println)

（二）Join

1.基本要素

如下图所示，Join大致包括三个要素：
1.Join方式;
2.Join条件;
3.过滤条件（其中过滤条件也可以通过AND语句放在Join条件中）。

2.类型

Spark支持所有类型的Join，包括：
1.inner join;
2.left outer join;
3.right outer join;
4.full outer join;
5.left semi join;
6.left anti join;

3.基本实现流程

    总体上来说，Join的基本实现流程如下图所示，Spark将参与Join的两张表抽象为流式遍历表(streamIter)和查找表(buildIter)，通常streamIter为大表，buildIter为小表，我们不用担心哪个表为streamIter，哪个表为buildIter，这个spark会根据join语句自动帮我们完成。 
    在实际计算时，spark会基于streamIter来遍历，每次取出streamIter中的一条记录rowA，根据Join条件计算keyA，然后根据该keyA去buildIter中查找所有满足Join条件(keyB==keyA)的记录rowBs，并将rowBs中每条记录分别与rowA join得到join后的记录，最后根据过滤条件得到最终join的记录。 

    从上述计算过程中不难发现，对于每条来自streamIter的记录，都要去buildIter中查找匹配的记录，所以buildIter一定要是查找性能较优的数据结构。

spark提供了三种join实现：

    1.sort merge join;
    2.broadcast join;
    3.hash join。

（1）sort merge join

    要让两条记录能join到一起，首先需要将具有相同key的记录在同一个分区，所以通常来说，需要做一次shuffle，map阶段根据join条件确定每条记录的key，基于该key做shuffle write，将可能join到一起的记录分到同一个分区中，这样在shuffle read阶段就可以将两个表中具有相同key的记录拉到同一个分区处理。前面我们也提到，对于buildIter一定要是查找性能较优的数据结构，通常我们能想到hash表，但是对于一张较大的表来说，不可能将所有记录全部放到hash表中，另外也可以对buildIter先排序，查找时按顺序查找，查找代价也是可以接受的，我们知道，spark shuffle阶段天然就支持排序，这个是非常好实现的，下面是sort merge join示意图。
    在shuffle read阶段，分别对streamIter和buildIter进行merge sort，在遍历streamIter时，对于每条记录，都采用顺序查找的方式从buildIter查找对应的记录，由于两个表都是排序的，每次处理完streamIter的一条记录后，对于streamIter的下一条记录，只需从buildIter中上一次查找结束的位置开始查找，所以说每次在buildIter中查找不必重头开始，整体上来说，查找性能还是较优的。

（2）broadcast join

    为了能具有相同key的记录分到同一个分区，我们通常是做shuffle，那么如果buildIter是一个非常小的表，那么其实就没有必要大动干戈做shuffle了，直接将buildIter广播到每个计算节点，然后将buildIter放到hash表中，如下图所示。
    从上图可以看到，不用做shuffle，可以直接在一个map中完成，通常这种join也称之为map join。那么问题来了，什么时候会用broadcast join实现呢？这个不用我们担心，spark sql自动帮我们完成，当buildIter的估计大小不超过参数spark.sql.autoBroadcastJoinThreshold设定的值(默认10M)，那么就会自动采用broadcast join，否则采用sort merge join。

（3）hash join

    除了上面两种join实现方式外，spark还提供了hash join实现方式，在shuffle read阶段不对记录排序，反正来自两格表的具有相同key的记录会在同一个分区，只是在分区内不排序，将来自buildIter的记录放到hash表中，以便查找，如下图所示。
    不难发现，要将来自`buildIter`的记录放到hash表中，那么每个分区来自buildIter的记录不能太大，否则就存不下，默认情况下hash join的实现是关闭状态，如果要使用hash join，必须满足以下四个条件： 
    1.buildIter总体估计大小超过spark.sql.autoBroadcastJoinThreshold设定的值(默认10M)，即不满足broadcast join条件;
    2.开启尝试使用hash join的开关，spark.sql.join.preferSortMergeJoin=false;
    3.每个分区的平均大小不超过spark.sql.autoBroadcastJoinThreshold设定的值(默认10M)，即shuffle read阶段每个分区来自buildIter的记录要能放到内存中;
    4.streamIter的大小是buildIter三倍以上;
    综上来看，使用hash join的条件其实是很苛刻的，在大多数实际场景中，即使能使用hash join，但是使用sort merge join也不会比hash join差很多，所以尽量使用sort merge join

下面我们分别阐述不同Join方式的实现流程。

（4）inner join

inner join是一定要找到左右表中满足join条件的记录，我们在写sql语句或者使用DataFrmae时，可以不用关心哪个是左表，哪个是右表，在spark sql查询优化阶段，spark会自动将大表设为左表，即streamIter，将小表设为右表，即buildIter。这样对小表的查找相对更优。其基本实现流程如下图所示，在查找阶段，如果右表不存在满足join条件的记录，则跳过。

（5）left outer join

left outer join是以左表为准，在右表中查找匹配的记录，如果查找失败，则返回一个所有字段都为null的记录。我们在写sql语句或者使用DataFrmae时，一般让大表在左边，小表在右边。其基本实现流程如下图所示。

（6）right outer join

right outer join是以右表为准，在左表中查找匹配的记录，如果查找失败，则返回一个所有字段都为null的记录。所以说，右表是streamIter，左表是buildIter，我们在写sql语句或者使用DataFrmae时，一般让大表在右边，小表在左边。其基本实现流程如下图所示。

（7）full outer join

full outer join相对来说要复杂一点，总体上来看既要做left outer join，又要做right outer join，但是又不能简单地先left outer join，再right outer join，最后union得到最终结果，因为这样最终结果中就存在两份inner join的结果了。因为既然完成left outer join又要完成right outer join，所以full outer join仅采用sort merge join实现，左边和右表既要作为streamIter，又要作为buildIter，其基本实现流程如下图所示。

由于左表和右表已经排好序，首先分别顺序取出左表和右表中的一条记录，比较key，如果key相等，则joinrowA和rowB，并将rowA和rowB分别更新到左表和右表的下一条记录；如果keyA<keyB，则说明右表中没有与左表rowA对应的记录，那么joinrowA与nullRow，紧接着，rowA更新到左表的下一条记录；如果keyA>keyB，则说明左表中没有与右表rowB对应的记录，那么joinnullRow与rowB，紧接着，rowB更新到右表的下一条记录。如此循环遍历直到左表和右表的记录全部处理完。

（8）left semi join

left semi join是以左表为准，在右表中查找匹配的记录，如果查找成功，则仅返回左边的记录，否则返回null，其基本实现流程如下图所示。

（9）left anti join

left anti join与left semi join相反，是以左表为准，在右表中查找匹配的记录，如果查找成功，则返回null，否则仅返回左边的记录，其基本实现流程如下图所示。

总结

Join是数据库查询中一个非常重要的语法特性，在数据库领域可以说是“得join者的天下”，SparkSQL作为一种分布式数据仓库系统，给我们提供了全面的join支持，并在内部实现上无声无息地做了很多优化，了解join的实现将有助于我们更深刻的了解我们的应用程序的运行轨迹。