SQL Server 存在三种 Join 策略：Hash Join，Merge Join，Nested Loop Join。

Hash Join：用来处理没有排过序/没有索引的数据，它在内存中把 Join 两边数据（的关联key）分别建立一个哈希表。例如有以下的查询语句，关联的两张表没有建立索引，执行计划将显示为Hash Join。

  1. SELECT
  2. sh.*
  3. FROM
  4. SalesOrdHeaderDemo AS sh
  5. JOIN
  6. SalesOrdDetailDemo AS sd
  7. ON
  8. sh.SalesOrderID=sd.SalesOrderID
  9. GO

Merge Join：用来处理有索引的数据，它比Hash Join轻量化。我们为前面两张表的关联列建立索引，然后再次上面的查询，执行计划将变更为Merge Join

  1. CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON SalesOrdHeaderDemo (SalesOrderID)
  2. GO
  3. CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)
  4. GO

Nested Loop Join：在满足Merge
Join的基础上，如果某一边的数据较少，那么SQL Server
会把数据较少的那个作为外部循环，另一个作为内部循环来完成Join处理。继续前面的例子为查询语句加上WHERE语句来减少 Join
一边的数据量，执行计划显示为Nested Loop Join。

  1. SELECT
  2. sh.*
  3. FROM
  4. SalesOrdHeaderDemo AS sh
  5. JOIN
  6. SalesOrdDetailDemo AS sd
  7. ON
  8. sh.SalesOrderID=sd.SalesOrderID
  9. WHERE
  10. sh.SalesOrderID=43659

执行计划中的（table/index scan）的改进

在许多场合我们需要在一张包含许多数据的表中提取出一小部分数据，此时应当避免Scan，因为扫描处理会遍历每一行，这是相当耗时耗力的。下面我们来看一个例子：

  1. SELECT
  2. sh.SalesOrderID
  3. FROM
  4. SalesOrdHeaderDemo AS sh
  5. JOIN
  6. SalesOrdDetailDemo AS sd
  7. ON
  8. sh.SalesOrderID=sd.SalesOrderID
  9. WHERE
  10. sh.OrderDate='2005-07-01 00:00:00.000'
  11. GO

图中的红圈标出了table scan，并且执行计划也智能得建议建立索引。我们先尝试在SalesOrdHeader 表上建立一个索引：

  1. CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON SalesOrdHeaderDemo (SalesOrderID)
  2. GO

然后再次执行相同的查询语句，执行计划变成以下的模样：

table scan 变为了 Index Scan，继续给另一张表也加上索引：

  1. CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)
  2. GO

执行计划发生以下的变化：

虽然不能说 Scan 比 Seek 差，但绝大多数的场合（尤其是在许多数据中查找少量数据时）Seek
是更好的选择。举例来说如果你有一个上亿条数据的表，你要取其中的100条，那么你应当保证其采用
Seek，但如果你需要取出其中绝大多数（比如95%）的数据时，Scan 可能更好。（有较权威的文章给出了这个阀值为30%，即取出超过30%数据时
scan 更高效；反之则 Seek 更好）

另外你可能注意到两张表上都建立了索引但一张表在执行计划中表现为 Clustered index
scan，而另一张表现为 Clustered index seek，我们期待的不是两个 Clustered index seek
吗？这是因为前一张表没有断言（predicate），而后一张表通过 ON 关键字对SalesOrderID 进行了断言限制。

执行计划中的 Key Lookup

为了后续的示例，我们先在同一张表上建立两个不同的索引：

  1. CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)
  2. GO
  3. CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)
  4. GO

执行以下的查询：

  1. SELECT
  2. ModifiedDate
  3. FROM SalesOrdDetailDemo
  4. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  5. GO

执行计划如下图，他利用了我们先前建立在 ModifiedDate 字段上的 Non-Clustered Index，生成为一个Index Seek 处理。

我们改造一下查询语句，SELECT 中多加两个字段：

  1. SELECT
  2. ModifiedDate,
  3. SalesOrderID,
  4. SalesOrderDetailID
  5. FROM SalesOrdDetailDemo
  6. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  7. GO

执行计划如下图，基本没变：

上面选出的字段不是属于 Non-Clustered Index 就是属于 Clustered Index，如果再增加几个其他的字段呢？

  1. SELECT
  2. ModifiedDate,
  3. SalesOrderID,
  4. SalesOrderDetailID,
  5. ProductID,
  6. UnitPrice
  7. FROM SalesOrdDetailDemo
  8. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  9. GO

乖乖，执行计划一下多了两个处理（Key Lookup, Nested Loop）：


Key Lookup 是一个繁重的处理，我们可以使用关键字 WITH 来指定使用 Clustered Index，以此回避Key Lookup。

  1. SELECT
  2. ModifiedDate,
  3. SalesOrderID,
  4. SalesOrderDetailID,
  5. ProductID,
  6. UnitPrice
  7. FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)
  8. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  9. GO

执行计划应声而变成为一个 Clustered Index Scan：

前文提过 Scan 似乎也不是一个很好的处理，那么矮子里拔高个，使用 SET STATISTICS IO ON 来比较一下：

  1. SET STATISTICS IO ON
  2. GO
  3. SELECT
  4. ModifiedDate,
  5. SalesOrderID,
  6. SalesOrderDetailID,
  7. ProductID,
  8. UnitPrice
  9. FROM SalesOrdDetailDemo
  10. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  11. GO
  12. SELECT
  13. ModifiedDate,
  14. SalesOrderID,
  15. SalesOrderDetailID,
  16. ProductID,
  17. UnitPrice
  18. FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)
  19. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  20. GO
  21. SELECT
  22. ModifiedDate,
  23. SalesOrderID,
  24. SalesOrderDetailID,
  25. ProductID,
  26. UnitPrice
  27. FROM SalesOrdDetailDemo WITH(INDEX=idx_non_clust_SalesOrdDetailDemo_ModifiedDate)
  28. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  29. GO


比较下来，采用了 clustered index 的查询表现最差，另外 SET STATISTICS IO 输出的数据中clustered index 的查询在 logical reads 上花费了更多的时间。

看起来采用 non-clustered index + Key Lookup 执行计划表现还不错，但如果能回避 Key Lookup 就完美了，我们来把 non-clustered index 修改一下，用 INCLUDE 关键字在索引中包含其他的字段：

  1. DROP INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo
  2. GO
  3. CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)
  4. INCLUDE
  5. (
  6. ProductID,
  7. UnitPrice
  8. )
  9. GO
  10. -- 清下缓存，仅用于开发环境！
  11. DBCC FREEPROCCACHE
  12. DBCC DROPCLEANBUFFERS
  13. GO

再次执行之前的查询：

  1. SELECT
  2. ModifiedDate,
  3. SalesOrderID,
  4. SalesOrderDetailID,
  5. ProductID,
  6. UnitPrice
  7. FROM SalesOrdDetailDemo
  8. WHERE ModifiedDate='2005-07-01 00:00:00.000'
  9. GO

这下完美了，因为我们的查询字段都包含在索引中，所以执行计划最终被优化为 Index Seek。

最新文章

  1. IOS中CoreData浅析
  2. JS获取阴历阳历和星期
  3. MyBatis Sql语句中的转义字符
  4. [网络技术][转]PPTP连接过程
  5. jQuery弹出层---artDialog 文档
  6. Swift - 使用UI Dynamics给UIKit组件添加重力和碰撞行为
  7. A Game of Thrones(19) - Jon
  8. C# xml增删查改
  9. Android开发——使用LitePal开源数据库框架
  10. H5外包团队 H5开发微信APP的优势有哪些
  11. svn 删除svn项目命令
  12. Android四大组件之Service --- 活动与服务的绑定
  13. Arch Linux 硬盘引导-联网安装
  14. GIT教程的好文章
  15. kafka删除topic详解
  16. 《HTTP 权威指南》笔记:第十五章 实体与编码
  17. HDU 2032 杨辉三角
  18. 【单调栈】hdu 6319 杭电多校Problem A. Ascending Rating
  19. 戴尔poweredge r730服务器配置及系统安装详解教程
  20. iis 中https修改主机名方法

热门文章

  1. C/C++获取CPU等硬件信息&&屏幕截图
  2. C/C++使用Socket通信UDP
  3. MySQL备份恢复之Xtrabackup
  4. mysql数据库和数据表的简单操作
  5. less学习三---父选择器
  6. you don't have permission to access forbidden
  7. Python的Bottle框架中实现最基本的get和post的方法的教程
  8. apache使用.htaccess文件中RewriteRule重定向后,URL中的加号无法解析
  9. Java应用：经纬度匹配（geohash加密）
  10. jmeter 插件安装