索引的第3个优点是可以加快表连接的速度。
要说明这个问题,首先要降到表连接的3种算法,分别是:hash,nested loop,merge ,这里主要讲hash,nested loop这2种算法。
现在有A、B两个表,A表数据量1000w,B表数据2000w,关联字段是ID。
对hash算法的影响
(1)无其他过滤条件,select A.*,B.*
hash算法实现两个表join时,如果关联的条件只是 ID字段,那么在关联时,遍历数据的次数=1000w+2000w=3000w。
那么就算给这2个表,分别创建索引,也不会加快连接的速度。
(2)无其他过滤条件,但是select A.ID,B.ID
在这个前提下,如果创建索引,会加快连接速度。
因为这个时候虽然需要访问表里所有的数据,但是可以直接用索引的数据,进行关联,因为只需要返回两个表的ID字段的值。
而相对于表来说,索引中只包含了ID字段的值,所以占用的磁盘空间,相对于表来说,会小很多,所以扫描整个索引所需要的时间,远小于扫描全表的时间,本质上就是访问的数据页数少了,IO次数少了,所需要的时间就少了。
(3)有其他过滤条件,且最后只返回少量数据
这种情况下,由于只返回少量数据,索引的作用才正在显现,因为索引的适用场景,就是返回少量的数据,比如:
select A.*,B.*
FROM A JOIN B ON A.ID = B.ID
WHERE A.ID = 12345678
只返回10条数据,那么整个执行过程就是,先用过滤条件 A.ID = 12345678 在A的索引种查找符合要求的数据,从1000w条数据中,过滤出10条数据(ID不一定是主键)。
然后,这里sql server应该会做一个谓词的推导:
A.ID = 12345678,然后 A.ID = B.ID,所以 B.ID = 12345678。
于是直接在B表的索引种查找 12345678,可能是1条,最后关联一下就是10条数据。
那么,如果这里sql server不做这个谓词的推导,结果又会如何呢?
只能是,对于已经找到的10条数据,通过访问B表索引的数据,进行探查,总共遍历次数= 10 + 2000w=2010w,速度也不会快到哪儿去。