笔记60-徐 读懂执行计划 P383

笔记60-徐  读懂执行计划 P383  

--读懂执行计划 P383  2012-12-29

--编译结束以后，就可以让SQL打出执行计划。执行完毕后，还可以让SQL打出
--按照这个执行计划运行的统计信息。通过这样的方法，用户可以研究为什麽
--一句指令会运行时间长。

--得到执行计划的方式有两种：
--1、一种是在指令的前面打开一些开关，让执行计划信息打在结果集里，这种
--方法比较适合在一个测试环境里对单个语句调优。这些开关最常用的有
SET SHOWPLAN_ALL ON

SET showplan_xml ON  --（是不是reuse了一个执行计划，SQL有没有觉得缺少索引），只能在XML
--的输出里看到

SET STATISTICS PROFILE ON


--2、另一种方法是使用SQL Trace里的事件跟踪来跟踪语句的执行计划。常用的事件有
--SHOWPLAN ALL、showplan STATISTICS profile、showplan xml STATISTICS PROFILE、、
--这种方法的好处是无须打开任何开关，可以用来直接跟踪应用程序的语句执行。缺点是
--他会把所有语句都抓下来。如果SQL比较忙的话，输出会很大，有可能影响性能，所以
--要慎用


--这两种方法收集的信息大部分是一样的。XML的方式会将结果以XML的结构返回，在
--SQL Trace里，这种结果可以用图形的方式显示执行计划以及其相关的信息。对执行
--计划比较简单的语句，这样的输出方式可能比较友善。但是对于复杂的执行计划，
--如果用图形的方式显示，一个屏幕很难全放得下，每一步的统计信息要鼠标点到
--才会显示出来，笔者认为反而不如文本输出格式易读性好。所以下面将集中讨论
--SHOWPLAN_ALL的方法和showplan STATISTICS profile方法。当然，有些信息
--（例如是不是reuse了一个执行计划，SQL有没有觉得缺少索引），只能在XML
--的输出里看到


--SHOWPLAN_ALL的方法和showplan STATISTICS profile方法都能够完整地输出执行计划
--为什麽要区别对待他们呢？他们有两大区别：

--1、输出执行计划是在语句执行前还是执行后
--如果打开set showplan_all on，再运行语句，SQL会在语句编译好或者找到可重用的执行计划
--后，就输出他，语句本身不会被执行。在SQL Trace里，showplan all事件发生在语句开始之前

--而set STATISTICS profile on和showplan STATISTICS profile发生在语句结束之后。也就是说
--，如果语句没做完就被用户停止掉，将不会得到执行计划输出


--2、是否包含每一步实际的返回行数
--SQL在生产执行计划的时候，会预估每一步返回行数，以算出预计的成本。但是预估的值
--不一定准确。STATISTICS profile的结果里，不但包含预估值，还会返回实际每一步的返回
--行数。比较这两行，就能看出SQL预估得是否准确。showplan all的方法由于没有真正去运行
--这句查询，只能得到预估返回行数，得不到实际返回行数

--所以如果用户能够等到要优化的语句做完，那么拿一份STATISTICS profile的结果就可以了，
--从这个结果里能看到调优需要的所有信息。但是STATISTICS profile的缺点是要真正去运行
--一遍语句。如果语句非常昂贵，要跑很久才能跑完（有时候甚至跑不完），那用户就不得不
--等待。有时候语句会对数据进行修改，例如update,insert,delete等，这不是生成数据库
--能让你随便测试的。在这些情况下，可能只能使用showplan all的方法，得到一个预估的
--执行计划。两种方法各有利弊，各有合适的情形


USE [AdventureWorks]
GO
SET SHOWPLAN_ALL ON
GO
SELECT
a.[SalesOrderID],a.[OrderDate],a.[CustomerID],
b.[SalesOrderDetailID],b.[ProductID],b.[OrderQty],
b.[UnitPrice]
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]=43659
GO


--只返回执行计划，没有结果集返回
--执行计划里只有“EsitmateRows”，没有“Rows”(实际行数)


SET SHOWPLAN_ALL OFF
GO

SET STATISTICS PROFILE ON
GO
SELECT
a.[SalesOrderID],a.[OrderDate],a.[CustomerID],
b.[SalesOrderDetailID],b.[ProductID],b.[OrderQty],
b.[UnitPrice]
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]=43659

GO

--先返回结果集，再返回执行计划
--执行计划里有“EsitmateRows”，也有“Rows”(实际行数)

SET STATISTICS PROFILE OFF
GO



--有了执行计划，就要读懂他的含义。SQL的执行计划是按照树状结构输出的，分成
--若干个层次，下一层分支隶属于上一层子句。例如，上面这个查询，他的最底层
--子句是执行计划的第五行（在[SalesOrderDetail_test]上的index seek）和第六行
--（[SalesOrderDetail_test]上的clustered index seek）。在此之上，SQL将这两个
--结果集用嵌套循环的方式联接起来（执行计划第四行）。得到的结果，和执行计划
--第三行（在[SalesOrderDetail_test]上作clustered index seek）并列为一层。
--他们的上一层是嵌套循环（执行计划第二行），说明SQL是使用嵌套循环的方式，
--把两个结果集合并起来，最后得到了查询的最终结果

--现在来看看原先这句查询，如果是手动做的话，你会怎麽做：
SELECT
a.[SalesOrderID],a.[OrderDate],a.[CustomerID],
b.[SalesOrderDetailID],b.[ProductID],b.[OrderQty],
b.[UnitPrice]
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]=43659
GO

--查询的含义：是把[SalesOrderDetail_test]和[SalesOrderHeader_test]这两张表通过
--[SalesOrderID]这个共同的字段联接起来，选择其中[SalesOrderID]等于43659的记录
--把他们的[SalesOrderID]，[OrderDate][CustomerID]，[SalesOrderDetailID]，[ProductID]，
--[OrderQty]，字段值都返回回来

--因为[SalesOrderHeader_test]在[SalesOrderID]字段上有一个聚集索引。SQL可以直接找到
--[SalesOrderID]=43659的记录，然后把他的[SalesOrderID]，OrderDate,CustomerID值找到
--所以这里使用Clustered index seek是很自然的事情


--但是对于dbo.SalesOrderDetail_test这张表，由于在[SalesOrderID]字段上的索引是
--nonclustered的，而查询要返回的字段并不能完全被非聚集索引所包含，所以SQL先通过
--SalesOrderDetail_test_NCL这个非聚集索引找到[SalesOrderID]=43659的记录，然后
--再根据指针和聚集索引SalesOrderDetail_test_CL做Nested Loops的联接，把所有需要的
--字段值取出来


--在执行计划里，会出现很多运算符，比如Parallelism并行,Nested Loops循环嵌套连接
--,Merge Join合并连接

--当多表连接时(书签查找，索引之间的连接都算)，SQL Server会采用三类不同的连接方式:
--循环嵌套连接(Nested Loops Join),合并连接(Merge Join),散列连接(Hash Join)。
--这几种连接并不是哪种会比另一种更好，而是每种连接方式都会适应特定场景。

--之类。要读懂执行计划，了解哪些步骤比较花时间和资源，就要理解常见的运算。这些
--运算的含义，SQL的联机丛书里都有介绍。在这里只介绍几个最常见的，也是比较容易
--出问题的运算




--联接 Join
--联接是SQL语句里最常见的运算。一条语句会涉及若干个表格，这些表格是通过
--某些字段联接在一起的。SQL也要把结果集作联接，这就是平时说的Join

--SQL有三种Join方法：nested loops join,merge join ,hash join。这三种方法
--没有哪一种是永远最好的，但是都有其最合适的上下文。SQL会根据两个结果集所
--基于的表格结构，以及结果集大小，选择最合适的联接方法。当然，用户也可以
--在语句里指定join方法。SQL会尽力尊重你的选择。（有些查询按照指定的join方法）
--可能做不出执行计划，SQL会报错


--例如，下面这同一句查询，因为有不同的join hint，选择的联接也有不同

USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
GO
SELECT
COUNT(b.[SalesOrderID])
FROM [dbo].[SalesOrderHeader_test] a
INNER LOOP JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
GO

SELECT
COUNT(b.[SalesOrderID])
FROM [dbo].[SalesOrderHeader_test] a
INNER MERGE JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
GO

SELECT
COUNT(b.[SalesOrderID])
FROM [dbo].[SalesOrderHeader_test] a
INNER HASH JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660

GO



--下面就先来介绍这三种方法，然后将他们作一下比较

--nested loops join
--nested loops join是一种最基本的联接方法，被SQL广泛使用。他的算法是
--对于两张要被join在一起的表格，SQL选择一张做Outer table ,另外一张
--做inner table

for each row r1 IN the OUTER TABLE
begin
FOR each row r2 IN the INNER TABLE
  IF r1 joins WITH r2
  OUTPUT(r1,r2)
IF r1 did NOT JOIN
  OUTPUT(r1,NULL)
END

--以上面的查询例子为例，SQL选择了[SalesOrderHeader_test]作为outer table
--[SalesOrderDetail_test]作为inner table。首先SQL在[SalesOrderHeader_test]
--上使用聚集索引做一个seek，找出每一条 a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
--的记录。每找到一条记录，SQL都进入Inner table，找能够和他join返回数据
--的记录（a.[SalesOrderID]=b.[SalesOrderID]）。由于outer table[SalesOrderHeader_test]
--上有10000条a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660的记录，每一条
--SQL都要到inner table里去找能join的row，所以inner table [SalesOrderDetail_test]
--被扫描了10000次（注意比较“rows”和“executes”列的值）
--rows:50577  COUNT(b.[SalesOrderID])=50577
--executes:10000



--nested loops join是一种基本的联接方式。他不需要SQL为join建立另外的数据结构
--所以也比较节省内存空间，也无须用tempdb的空间。他适用join类型是非常广泛的
--有些联接是merge join和hash join做不了的，但nested loops join可以做。
--所以这种联接方式的优点是很明显的，但是他的缺点也很明显

--1、算法的复杂度等于inner table乘以outer table
--所以如果是两张表比较大,尤其是outer table比较大的情况，inner table会被扫描很多次。这时候
--的算法复杂度增加的非常快，总的资源消耗量也会增加得很快。所以nested loops join比较
--适合于两个比较小的结果集做联接，或者至少是outer table的结果集比较小

--像前面的例子，由于outer table [SalesOrderHeader_test]的数据集有10000条记录，所以
--inner table就会被扫描10000次。这是不太划算的。如果让SQL自己选择而不加join hint，
--SQL不会选择nested loops 的联接方式


--2、outer table的数据集最好能够事先排序好，以提高检索效率
--如果数据集能够事先排序好，做nested loops当然能够更快一些。当然如果没有排序，nested loops
--join 也能做得出来，就是cost会大大增加




--3、inner table上最好有一个索引，能够支持检索
--由于算法是拿着outer table里的一个值，在inner table里找到所有符合条件的记录，所以在inner table
--里找得快慢也能很大程度上影响整体的速度。如果进行检索的字段上有一个索引，查找的速度会大大
--加快，inner table数据集稍微大一点也没关系。否则就要每次做整个数据集的扫描，是很浪费资源的


--总之，nested loops join对数据集比较小的联接，效率是最高的，因此在SQL里使用得很广泛。当SQL
--发现能够选择一个很小的数据集作为outer table的时候，他往往会选择nested loops，大的数据集
--做outer table，性能会急剧下降。很多语句性能问题，都是由于这个造成的



--merge join
--刚才反复强调，nested loops join只适用于outer table数据集比较小的情况。如果数据集比较大，SQL
--怎麽做联接呢？还好他有其他两种联接方式，merge join和hash join

--merge join的前提条件是，两个数据集必须要先排序好。他的算法是：
GET FIRST row r1 FROM input1
GET FIRST row r2 FROM INPUT2
WHILE NOT at the END OF either INPUT
begin
IF r1 joins WITH r2
begin
OUTPUT(r1,r2)
GET NEXT row r2 FROM INPUT 2
END
ELSE
IF r1>r2 GET NEXT row r1 FROM INPUT 1
ELSE GET NEXT row2 FROM INPUT 2
END


--也就是说，从两边的数据集里各取一个值，比较一下。如果相等，就把这两行联接起来返回。
--如果不相等，那就把小的那个值丢掉，按顺序取下一个更大的。两边的数据集有一边遍历
--结束，整个join的过程就结束。所以整个算法的复杂度最大就是大的那个数据集里的记录
--数量，这个比起nested loops join两个数据集相乘的复杂度，的确是小了很多。所以
--在数据集很大的情况下，merge join的优势是非常明显的


--rows:50577  COUNT(b.[SalesOrderID])=50577
--executes:4

--但是merge join的局限性也很强，所以在实际的语句里，使用得并不是那么普遍。
--他的局限性主要有：

--1、做联接的两个数据集必须要事先按照join的字段排好序（不知道是不是要加order by如果没有索引的话）
--这个先决条件是merge join算法的基础，而对大的数据集排序本来就是一件比较复杂的事情。
--有些数据集是基于join的那个字段上的索引得到的，所以能够不费额外的资源就排好了顺序，
--这时候用merge join可能就比较合适

--例如，范围查询，两个数据集都是根据在[SalesOrderID]字段的索引上seek出来的，所以
--不需要再做排序


--2、merge join只能做以“值相等”为条件的联接，而且如果数据集可能有重复的数据，merge join
--要采用many-to-many这种很浪费资源的联接方式

--在SQL扫描数据集时，如果数据集1有两个或者多个记录值相等，SQL必须得把数据集2里扫描过的
--数据暂时建立一个数据结构存放起来，万一数据集1里下一个记录还是这个值，那还有用。
--这个临时数据结构被称为“worktable”，会被放在tempdb或者内存里。这样做很耗资源，所以
--在上面的执行计划里，merge join的两个子句的subtree cost分别为0.202和0.109。但是
--many-to-many的join子句subtree cost是5.051。也就是说，join自己的cost是4.74
--（5.051-0.202-0.109=4.74）。这是一个不小的cost

--如果把SalesOrderHeader_test_CL(聚集索引)，改成一个唯一unique的聚集索引
--SQL就知道数据集1（dbo.SalesOrderHeader_test）的值不会重复，也就不需要
--做many-to-many join
--执行计划果然发生变化，预估的cost降低了一个数量级

--上面这两个限制（排序，唯一（可以不唯一）），影响了merge join的使用范围。
--但是merge join的一个独特好处是，返回的数据集也是按照顺序排好的


--这里顺便提一下结果集的顺序问题。有些用户会抱怨，同一个查询的结果集为什麽有时候是
--按他想要的顺序排列，有时候又不是。或者是在SQL2000里是这个顺序，到了SQL2005/2008
--又是那个顺序。讲完了merge join大家就能够理解，同样做join，merge就能够按顺序返回
--但是nested loops 就不能。只要语句里没有指定“order by”,SQL选取哪一种join并不需要
--考虑结果集是否按顺序返回的。他更多考虑的是哪一种join算法代价最小。如果数据量和
--数据分布让SQL觉得nested loops划算，他就转用nested loops。结果集就不按顺序返回了
--但是SQL并没有做错什么

--一句话，如果你想要结果集按照某个顺序返回，就要明确地用“order by”指定。如果你没有
--指定，哪怕一模一样的查询，结果集顺序这一次和上一次不一样是很正常的。因为数据
--发生变化，或者参数不同，SQL很可能就会选择不同的执行计划





--hash join
--顾名思义，hash join就是利用哈希算法做匹配的联接算法。哈希算法分成两步：
--“build”和“probe”。在“build”阶段，SQL选择两个要做join的数据集中的一个，
--根据记录的值建立在一张在内存中的hash表。然后在“probe”阶段，SQL选择
--另外一个数据集，将里面的记录值依次带入，找出符号条件，返回可以做联接
--的行。具体算法是：

FOR each row r1 IN the build TABLE
begin
calculate HASH VALUE ON r1 JOIN KEY(s)
INSERT r1 INTO the appropriate HASH bucket
END
FOR each row r2 IN the probe TABLE
begin
calculate HASH VALUE ON r2 JOIN KEY(s)
FOR each row r1 IN the CORRESPONDING HASH bucket
  IF r1 joins WITH r2
   OUTPUT (r1,r2)

END

--和其他两种join算法比，hash join的优点是很明显的

--1、这对于数据集比较大的join，其复杂度能够控制在合理的范围以内。虽然比merge join
--要高一些，但是比nested loops要简单许多


--2、他不需要数据集事先按照什么顺序排序，也不要求上面有索引
--因为联接使用的是哈希算法，对输入没有限制，不需要SQL像为merge join一样，事先
--准备好一个排过序的输入。由于做hash join总是要把两边的数据集都要扫描一遍
--所以有没有索引其实帮助也不大。没有索引，对性能也不会有太大影响




--3、可以比较容易地升级成使用多处理器的并行执行计划
--因为算法没有要求代入的数据有任何次序，所以用多个CPU并行完成是比较容易的
--总之，hash join是一种适合于要join的数据集比较大，上面没有合适索引的情况。
--像刚才的那个例子，是一个10000条记录的数据集和一个50577条记录的数据集之间
--的联接。使用nested loops要循环10000次，代价比较高。SQL预估出来的cost是2.233
--使用merge join时，虽然两个数据集都是排序好的，但是由于可能有重复的值，
--SQL只好使用many-to-many的join方式，cost也很高，预估是5.882。使用hash join
--预估的cost是0.727，比前两个都小。所以如果不代入join hint的话，SQL默认
--会对这句话使用hash join

--但是，hash join并不是一种最优的join算法，只是SQL在输入不优化（join的数据集
--比较大，或上面没有合适索引）的时候的一种不得已的选择。这是因为hash join
--是一种最耗资源的join算法。他在做join之前，要先在内存里建立一张hash表
--建立的过程需要CPU资源，hash表需要用内存或tempdb存放。而join的过程也要
--使用CPU资源来计算（“probe”）。如果同时有很多用户在用hash算法做join，对
--SQL的整体负担是比较重的。从降低SQL整体负荷的角度考虑，还是要尽量降低
--join输入的数据集的大小，配合合适的索引，引导SQL尽量使用nested loops join
--或者merge join


--三种join方式比较
--                                        nested loops join       
--最适合于                           相对较小的两个数据集，inner table在做join的字段上有一个索引
--并发性                              能够支持大量的并发用户同时运行                     
--join时是否两个字段相等              不要
--是否使用内存资源                     不使用
--是否使用tempdb                       不使用
--输入数据集是否排序                   不要
--希望输入数据集排序否                 希望outer input是排序的

--                                        merge join       
--最适合于                           输入数据集大小中等或较大，并且在join字段上索引帮助排序，或语句要求返回一个排好序的结果集
--并发性                             有索引支持的many-to-many的join并发性较好，many-to-many的就差了                   
--join时是否两个字段相等              要（除非是full outer join）
--是否使用内存资源                     不使用(如果要为merge join做排序，可能要使用)
--是否使用tempdb                       many-to-many join要使用
--输入数据集是否排序                   要
--希望输入数据集排序否                 是的

--                                       hash join       
--最适合于                           输入数据集较大。尤其适合于data warehouse数据仓库环境下的那些复杂的查询语句
--并发性                              最好同时只有少数用户在同时运行                   
--join时是否两个字段相等              要
--是否使用内存资源                     使用
--是否使用tempdb                       使用
--输入数据集是否排序                   不要
--希望输入数据集排序否                 不要



---在SQL做联接的时候，会按照输入数据集所基于的表格的结构，衡量可能利用的索引，
--也根据统计信息，预估两个输入数据集的大小，选择使用三种join方式其中的一种。
--如果选得不对，可能会造成join的速度非常慢





-------------------其他常见的运算操作-------------------
--除了读取数据的运算操作（table scan/seek、clustered index scan/seek、index scan/seek、bookmark lookup等）
--以及join运算，SQL常见的其他运算有：

--Aggregation 聚合 ETL Extraction-Transformation-Loading的缩写，中文名称为数据提取、转换和加载
--主要用来计算sum()、count()、max()、min()、avg()、distinct()等运算函数。aggregation也分两种
--Stream Aggregation(将数据集排成一个队列以后做运算)  和Hash Aggregation(类似于hash join，
--需要SQL先建立hash表，然后才能做运算)。例如：

SET STATISTICS PROFILE ON
GO

SELECT  MAX([SalesOrderDetailID]) FROM [dbo].[SalesOrderDetail_test]
--stream aggregation
GO

SELECT [SalesOrderID],COUNT([SalesOrderDetailID]) --[SalesOrderDetailID] group by的字段要做聚合
from [dbo].[SalesOrderDetail_test]
GROUP BY [SalesOrderID]
--stream aggregation
GO


SET STATISTICS PROFILE ON
GO
SELECT [CustomerID],COUNT(*)
FROM [dbo].[SalesOrderHeader_test]
GROUP BY [CustomerID]
--hash aggregation
GO


--Union
--Union就是把两个数据集合并起来。但是要注意的是，Union有两种：Union ALL和Union
--这两种的复杂度是不一样的

--Union ALL很简单，就是合并两个集合，不管里面是否有重复的数据。例如：
SET STATISTICS PROFILE ON
GO

SELECT DISTINCT [ProductID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=776
UNION ALL
SELECT DISTINCT [ProductID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=776


--他的DISTINCT() 函数是用hash match做出的aggregation。两个结果集用一个简单的“concatenation”
--相连，无须做其他工作

--但是Union就不同了。他不但要合并两个数据集，还要把其中重合的数据删除。
--所以SQL要把合并以后的结果集进行排序。这是比较昂贵的
--例如：
SET STATISTICS PROFILE ON
GO

SELECT DISTINCT([ProductID]),[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=776
UNION
SELECT DISTINCT([ProductID]),[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=776


--经过Concatenation后的结果集有8条，其中4条是重复的数据。SQL又做了一个
--DISTINCT Order By，把这4条重复的数据去掉

--SQL有时候也用hash或者merge join的方式，达到同样合并数据的目的



--Parallelism
--当SQL发现某个要处理的数据集比较大，cost比较高时，如果SQL服务器有多颗
--CPU，SQL就会尝试使用并行的方法，把一个数据集分成若干个，由若干个线程
--同时处理，以提高总体效率。这就是通常说的“并行执行计划”

--例如图12-42 ，SQL就按照某个字段的值，奇数的交给线程1做，偶数的交给线程2做


--还是看这条语句
SET STATISTICS PROFILE ON
GO
SELECT DISTINCT [ProductID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=776
GO

--SQL用两个线程运行了这个查询。在Parallelism之前（第五行和第四行），
--SQL已经为distinct函数作了一次使用sort的aggregation。并行运行的
--每个线程各返回了4条记录，总共8条。当并发运行得到的结果合并时，
--SQL还要再做一次Distinct Order By，以从这8条记录中再找出distinct
--的结果。所以在第二行，结果数从8变成了4



--SQL里还有其他操作。如果有兴趣，可以参见《inside microsoft sqlserver2005
--query tunning and optimization》一书的第三章

 http://mysql.taobao.org/monthly/2021/01/05/
Hash Join 算法
Hash join 是利用 hash 函数来实现和加速数据库中 join 操作的一类算法。其主要优势来源于 hash 函数可以只通过一次运算就将任意键值映射到固定大小、固定值域的 hash 值。在实践中，针对等值 join 所需的等值比较，一般数据库系统会仔细选择和优化 hash 函数或函数簇，使其能够快速缩小需要和一个键值进行等值比较的其它键值的数量或范围，从而实现了通过减少计算量、内外存访问量等手段来降低 join 算法的执行开销。

Simple Hash Join
经典的 hash join 算法（又称 Simple Hash Join，SHJ）包括两步：

Build：选择两个输入 relation 中 cardinality 较小的一个（一般称其为 build relation），使用一个或一簇 hash 函数将其中的每一条记录的主键 key 值计算为一个 hash 值，然后根据 hash 值将该记录插入到一张表中，这张表就叫做 hash 表；
Probe：选择另一个 cardinality 较大的 relation （一般称为 probe relation），针对其中的每一条记录，使用和 build 中相同的 hash 函数，计算出相应的 hash 值，然后根据 hash 值在 hash 表中寻找到需要比较的记录，一一比较，得到最终结果。 经典的 SHJ 算法步骤简单直接，在过去的很长一段时间内是首选的 hash join 算法，但也需要拥抱变化。进入 21 世纪后，随着现代处理器的发展逐渐遇到了 frequency wall, memory wall 等瓶颈，处理器从高频单核架构逐渐演化为较低频的多核架构。此外，随着内存容量的不断增高，纯内存的分析型数据库查询逐渐流行，join 算法的执行开销瓶颈从 I/O 逐渐变为 CPU 和内存开销。因为 SHJ 算法中往 hash 表中插入记录和从 hash 表中读取记录往往都有大量的随机内存读写，而随机访问的代价在各个软硬件层面上都比顺序访问高，SHJ 算法在多核 CPU 处理器上逐渐尽显疲态。
Partitioned Hash Join
Partitioned Hash Join （分区 hash join，PHJ）算法在多核架构上逐渐取得了性能上的优势。和 SHJ 算法相比，PHJ 算法共用了完全相同的 build 和 probe 两个步骤，但是 PHJ 在 build 之前会先对输入 relation 进行 partition，然后针对每个 partition 应用 SHJ 算法。在这个算法过程中，我们有很多不同的选择：

只对单个 relation 进行分区，还是对两个 relation 同时进行分区？
对于一个 relation，将其分出多少个区来？
给定分区总数，我们是一次性全部分配完成，还是逐级完成（比如从一个国家先分出省，再分出城市，最后分出区县）？ 除此以外，在决定了上述 3 个问题以后，在算法的执行层次，也有很多优化空间可以研究（比如多并发、向量化、NUMA-aware、FPGA/GPU 加速等）。所以 hash join 算法的设计和实现在过去的很多年间都是系统界研究的热点。目前我们已经拥有众多详实和成熟的研究成果。