索引的第5个优点是可以加快表连接的速度。
要说明这个问题,首先要降到表连接的3种算法,分别是:hash,nested loop,merge ,这里主要讲hash,nested loop这2种算法。
现在有A、B两个表,A表数据量1000w,B表数据2000w,关联字段是ID。
对nested loop算法的影响
(1)无其他过滤条件,select A.*,B.*
nested loop算法实现两个表join时,如果关联的条件只是 ID字段,那么在关联时,遍历数据的次数=1000w*2000w=超级大的数字,具体就不写了。。。
不管建不建索引,速度都会非常慢。
(2)无其他过滤条件,但是select A.ID,B.ID
在这个前提下,如果创建索引,会加快连接速度。
但是仍然会很慢,因为在访问全表数据的情况下,用hash算法,可以显著提高查询的性能,创建索引虽然对nested loop有用,但这个时候已经不是仅仅是索引的问题了,而是应该采用合适的关联算法的问题了。
(3)有其他过滤条件,且最后只返回少量数据
这种情况下,由于只返回少量数据,索引的作用才正在显现,因为索引的适用场景,就是返回少量的数据,这里举个不同的例子,范围查找,比如:
select A.*,B.*
FROM A JOIN B ON A.ID = B.ID
WHERE A.ID >= 135 and A.ID <=160
加入只返回20条数据,那么整个执行过程就是,先用过滤条件 A.ID >= 135 and A.ID <=160 在A的索引种查找符合要求的数据,从1000w条数据中,过滤出20条数据(ID不一定是主键)。
然后,对于在A表中找到的每一条记录,遍历1次B表的索引,因为在A表中找到20条记录,所以一共遍历20次,每一次遍历,都是在B表的索引种查找,速度极快。
当然,这里sql server也有可能做谓词推导,就这种情况来说,谓词推导的影响不是很大,不会像对hash算法的影响那么大。