inner join 优化

 

inner join 优化

数据库中，像table a inner join table b 、table a left join  table b 像这样的SQL语句是如何工作的？也就是说SQL Server 使用什么算法实现两个表的join操作？

SQL Server 2000有三种方式：nested loop 嵌套循环、merge合并、hash。Oracle也是使用这三种方式。

1、nested loop

示例sql：select * from table A inner join table B on A.col1=B.col1 and ...

table A 中没有建立任何索引，table B中在col1上建立一个主键（聚簇索引）

使用nested loop 的条件是：a) outer input 的记录数不大，最好在1000~2000以下，一般超过3000就很难说了，基本不大会选择nested loop；b) 作为inner put 的表中，有可以这样的索引。在三种join种，nested loop 是消耗内存与cpu最少的方式。

2、merge

第一个步骤是确保两个关联表都按关联的字段进行排序。如果关联的字段有可用的索引，直接使用merger join 操作；否则SQL Server 需要先对关联的表按照关联字段进行一次排序（就是在merge join 前的两个输入上，可能都需要两个sorted操作 ，再进行merge join ）

3、hash

hash算法主要是用于大数据量的搜索，为了避免像merge join 一样在全部的字段中进行搜索匹配，通过合适的hash函数，先要给要搜索的数据根据hash key建立hash值作为索引，在搜索时，先通过hash值定位到一个较小的范围之内，然后在这个范围中搜索匹配的结果，提高效率。

SQL Server 将数据量较小的表作为build input，尽量使根据build input构造hash table 能够完全放在内存中，这样probe阶段的匹配操作都放在了内存，因此这种hash join 也被成为In-Memory hash join。如果build input 非常大，构建hash table 无法在内存中容纳时，SQL Server分别将build input 和probe input 切分成多个分区。

 

三种join 方法，都是拥有两个输入。优化的基本原则是：

①　尽量避免大数据量的hash join ，尽量使其转化为高效的merge join 和nested looped。表结构设计、索引调整、SQL优化以及业务设计优化

②　尽量减少两个输入端数据量。