查询优化（3）

我们继续查询优化（2）的讨论，本文将聚焦索引

创建有用的索引

创建合适的索引是获得高性能数据访问的最重要的工作之一。

谨慎选择聚集索引

当进行范围查询或需要根据键值进行排序时，聚集索引显得特别有用。每张表只能有一个聚集索引，其决定了表中数据的顺序。几乎每张表都应该有一个聚集索引。

默认情况下，聚集索引会创建在表的主键列上，但这不一定是最好的选择。主键从逻辑概念上讲是为了保证数据的唯一性，使用非聚集索引能高效地定位到一条需要的数据行，同时保证主键的唯一性。当我们在创建主键约束时，通过增加关键字NONCLUSTERED可以省下聚集索引，这样我们可以把聚集索引用到获益更大地方。

使非聚集索引具有高选择性

查询优化器相对非聚集索引更喜欢聚集索引，因为聚集索引包含了所有的数据页。聚集索引的叶节点就是数据本身，而非聚集索引的叶节点还是索引页，会包含一个聚集索引键或RID。当一个查询使用一个不能完全覆盖该查询的非聚集索引时，SQL Server就需要进行Lookup。Lookup是非常昂贵的，所以SQL Server只有当查询的选择性非常高时，才会使用非聚集索引。

对关键或常用查询的where子句中的列加索引通常会有好处，但这取决于索引的选择性。举例来说，如果一个查询包含子句where last_name = ‘Stankowski’，在last_name列上的索引可能会非常有用，它将排除99.9%的数据行。另一方面，一个在where sex = ‘M’上的索引可能没什么用，因为它只能排除大约一半的数据行，使用此索引会造成大量Lookup，所产生的I/O操作比简单的全表扫描还多。所以非聚集索引对于数据分布较为集中的列不是很有用。

把选择性想象成符合条件行的百分比（符合条件的行数/总共行数）。当这个百分比很低，这个索引是高选择性的，会非常有用。使用这个索引会排除表中大多数行，大大减少工作量。对于一个非聚集索引，当这个百分比小于等于5%时，其最有用；当这个百分比超过5%时，其很可能不会被使用，SQL Server会选择其他索引或表扫描。SQL Server为每个索引的索引键维护了一份统计值，查询优化器会根据这些统计值估计索引的选择性来为查询选用索引。

为关键事务裁剪索引

索引通过增加数据修改时的附加工作来获得高效的数据查询。要决定一个合理的索引数量，我们必须考虑目标数据库中数据修改的频率和数据查询的频率。如果我们的系统是一个只有少量修改的报表或决策支持系统，为查询增加尽可能多的有用索引是合理的。如果我们的系统主要支持在线事务处理（OLTP），在表上的索引就不应太多。

为查询寻找（完全）覆盖索引，使用覆盖索引比聚集索引更高效。如果一个索引的键包含一个查询所需要的所有信息，我们称这个索引覆盖了目标查询。举例来说，如果我们有一个查询SELECT emp_name, emp_sex FROM employee WHERE emp_name LIKE ‘Sm%’，如果有以emp_name，emp_sex为键的非聚集索引，则此索引覆盖了上述查询。对于覆盖索引，查询不需要进行Lookup，而且因为其键值包含比聚集索引少的列，所以索引树比聚集索引的索引树要小，这意味着更少的I/O操作。但需要注意的是，不要把太多的列放入覆盖索引中，包含的列越多其性能优势就越不明显。

对于唯一索引（无论是非聚集或聚集索引）有这最高的选择性，所以对于出现在where子句中的主键或唯一性约束的列加非聚集索引是一个好主意。

索引对于查询很重要，同样对于数据修改也很重要。索引可以提高查询的性能，其还能提高UPDATE或DELETE的定位速度。一般UPDATE或DELETE会作用在某一行数据上，我们应该确保在搜索条件列上有一个唯一索引，如果没有合适的索引，SQL Server会进行表扫描来寻找符合条件的行。更新索引列会影响更新策略的选择。举例来说，更新一个表中聚集索引所包含的列，SQL Server不能简单的在相应行进行update，而需要先进行delete操作再进行insert操作。所以在决定在哪些列上加索引，特别是哪些列作为聚集索引时，需要考虑索引对于update操作的影响。

当需要在一个长字符字段上加索引时，SQL Server提供了一个函数可作为替代方案。CHECKSUM函数在一个行或一个列上计算校验和，其结果总是一个4字节整数。我们可以增加一个计算列来放置长字符字段的校验和，再在此计算列上创建索引。对于唯一的输入CHECKSUM函数的返回值不能保证是唯一的，所以我们的查询条件还需要包括字符字段。举例来说，假设在pubs库中的titles表记录数超过了1百万条。我们需要对书名列进行快速查找，但由于title列的字符宽度为80，并不是一个理想的索引列。（事实上，在titles表的title列上有一个索引，由于这不是一个好主意，我们将去掉这个索引。）所以我们将使用CHECKSUM函数创建计算列并添加索引：

USE pubs
GO
DROP INDEX titles.titleind
GO
ALTER TABLE titles
ADD hash_title AS CHECKSUM(title)
GO
CREATE INDEX hash_index ON titles(hash_title)
GO

首先，我们直接查找一个title（注意：在做一下实验前，先制造尽可能多的假数据插入titles表，我插入了10000条数据）

SELECT * FROM titles
WHERE title = 'Cooking with Computers: Surreptitious Balance Sheets'

查看以上查询的执行计划，我们可以看到查询优化器使用了聚集索引扫描，这意味着整个表被扫描了。我们加入hash_title列进行查询：

SELECT * FROM titles
WHERE title = 'Cooking with Computers: Surreptitious Balance Sheets'
AND hash_title = CHECKSUM('Cooking with Computers: Surreptitious Balance Sheets')

执行计划显示SQL Server在计算列hash_title上进行了索引查找。

 

--来不及写了，做一下下期预告

下次会继续本篇的内容，讨论以下几个方面的内容

注意列的顺序

在Join中使用的索引列

在需要的时候Create或Drop索引