想成为一名出色的架构师,数据库知识必不可少,拿起半年前买的一本数据库书<SQL Server 2005技术内幕:查询、调整和优化 >,发现还是崭新的,真是惭愧。最近工作需要,牵扯到部分数据库的设计,所以狠下心来专心的看一遍。
本文没有任何原创内容,均来至书中个人认为比较重要的部分,对自己的学习有所总结,也可以造福像我一样渴望将自己从数据库菜鸟成为大虾的博友。
闲话少说,切入正题,扫描和查找时从表和索引中读取数据的迭代器,作为 SQL Sever 中所支持的最基本的迭代器,几乎会在每一个查询计划中出现。一定要区分扫描和查找之间的不同,扫描是用来处理整个表和索引的全部分支;查找时在谓词的基础上有效的返回索引中的一个或多个范围中的行。
首先是关于扫描的示例,如下所示:
RequiredDate 列是没有索引的。所以 SQL Server 需要读取 Orders 表中的每一行,在 RequiredDate 中评估此谓词,如果这一行符合条件,则返回此行。
因为扫描操作会涉及表中的每一行而不管其是否符合条件,所以扫描的成本与表中的行数成正比。因此当表很小或对谓词来说有很多行符合条件时,扫描时有效的。然而,当表很大并且当大部分的行都不符合条件时,扫描就要涉及很多不必要的页和行,并且执行大量的 I/O 操作。
下面来看一个索引查找的例子。其谓词在 OrderDate 列,而这一列有一个索引。
这时SQL Server就 可以使用这个索引直接搜索满足此谓词的行。这样,就可以将这个谓词看做是一个查找谓词。索引确保了查找操作符只返回符合条件的行。由于只在符合条件的行及 包含这些行的页中进行,所以查找的效率与符合条件的行数、页数成正比,而与表中的总行数无关。因此查找效率取决于是否选择了合适的查找谓词。即如果一个查 找谓词能够过滤表中的大部分内容,那么查找效率会大大提高。
在 SQL Server中,扫描与查找的区别类似于堆扫描、聚集索引扫描与非聚集索引扫描之间的区别。
SQL Server在执行索引查找前,需要确定查询中索引关键字是否适合计算谓词。推荐使用可以作为索引查找基础的可查找谓词。同时 SQL Server还需要确定索引中是否包含或覆盖了查找中所涉及的列的集合。下面将详细介绍什么样的谓词可查找,什么样的谓词不可查找,以及索引所覆盖的列。
(一) 单列索引
确定一个谓词是否能在单列索引中进行查找非常简单。 SQL Server可以使用单列索引来响应大部分的简单比较,包括等价于不等价(大于、小于等),复杂一些的表达式,例如列中的函数和带有引导通配符的 Like谓词,通常会让 SQL Server避免使用索引查找。
例如,假设在列 Coll上有一个单列索引,可以使用这个索引查找下面这些谓词:
Ø [Coll] = 3.14
Ø [Coll] > 100
Ø [Coll] between 0 and 99
Ø [Coll] like ‘abc%’
Ø [Coll] in (2,3,5,7)
但是,不能使用这个索引对下列这些谓词进行查找:
Ø [Coll] Like ‘%like’
Ø [Coll] + 1 = 9
(二) 复合索引
复合或多列索引相对来说要复杂一些。复合索引关键字的顺序很重要。它决定了索引的排序方式,并影响 SQL Server使用此索引计算的查找谓词集。
排序的理解就像电话薄。电话薄就像是带有关键字(姓、名)的索引,按照姓氏来排序。如果知道某人的姓氏,就可以很容易的找到这个人。然而,如果只知道这个人的名字不知道姓氏,想要找到这个名字的人员名单就很困难,因为需要一个以名字来排序的电话薄。
同理,如果两列上存在一个索引,当第一列包含等价谓词时,就只能使用索引去满足第二列上的谓词。甚至在无法使用索引去满足第二列的谓词时,或许可以在第一列中使用。这时,为第二列引入剩余谓词。这个谓词的计算方式与其他扫描谓词类似。
例如,假设在列 Col1和 Col2上有一个两列索引。可以使用这个索引查找单列索引上的任何谓词。同时,也可以用它来查找其他谓词:
Ø [Col1] = 3.14 and [Col2] = ‘pi’
Ø [Coll] = ‘xyzzy’ and [Col2] <= 0
在下面这个例子中,使用索引满足列 Col1上的谓词。列 Col2需要一个剩余的谓词。
Ø [Col1] > 100 and [Col2] > ‘pi’
Ø [Col1] like ‘abc%’ and [Col2] = 2
在无法使用这个索引去查找下一个谓词集,甚至无法查找 Col1列时,须使用其他索引(即 Col2为引导时的索引),或者带有谓词的扫描。
Ø [Col2] = 0
Ø [Col1] + 1 = 9 and [Col2] between 1 and 9
(三) 确定关键字
在大多情况下,索引关键字是在 Create Index语句中声明的列的集合。然而,在带有聚集索引的表中创建非唯一的非聚集索引时,如果没有明确表示它们是非聚集索引关键字的一部分,那么聚集索引的关键字会被附加到非聚集索引的关键字中。这时,就可以在这些固有的关键字中进行查找了。
(四) 已覆盖的列
表中的堆或聚集索引包含了表中所有的列。另一方面,非聚集索引只包含表中列的子集。通过限制存储在非聚集索引中的列集, SQL Server可以在每页中存储更多的行。由于减少了 I/O的数量及所涉及的页的数量,这将节省磁盘空间并提高查找及扫描效率。然而,一个索引的扫描或查找操作只能返回被索引覆盖的列。
每个非聚集索引都会覆盖创建时所指定的关键列。同时,如果基本表是个聚集索引,那么这个表中每个非聚集索引都会覆盖聚集索引关键字,而不管它们是否为非聚集索引关键列的一部分。在 SQL Server2005中,还可以在非聚集索引中使用 Create Index语句的 Include子句添加其他非关键列。需要注意的是,与索引关键字不同,排序与索引包含的列无关。