SQLServer里很少用到列的索引技巧--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址很多人都不止一次的遇到过这种令人迷惑的情形：简单的查询性能却很低：
SELECT col1, col2, col3 FROM aLargeTable WHERE flag = 1
表面看来，这似乎是一个很容易解决的事情。然而，仔细看看，令人讨厌的是，很可能flag列是只有两个值组成BIT列或者是由很少不同值组成的其他类型的列。这意味着按照通常的做法索引不会起作用，因为索引不会为数据库引擎提供足够的好处从而不被选择，当选择行时优化器甚至都不可能用它。顺便说一句，这是正确的行为，因为使用索引去查找很多独立的行将比表扫描还慢。
那么，该怎么办呢？这儿可能有几个可能，其中一些会有帮助，而另一些则不然。下面是使用SQLServer2005对一些技巧的性能效果的分析。 为了模拟这个环境，需要下面特征的例子：
- 大量的行
- 一些很少用来选择的列，如BIT列或者只有几种不同值的CHAR列
- 需要使用这些列来做选择条件
我将使用一个假定的邮件列表来模拟这个环境。该邮件列表由人名、地址和两个BIT的flag列来表示地址是否可用、是否可以接收邮件。需要优化的查询是选择这两列的值为1/True的每一行。
创建示例数据示例表的创建如下：
CREATE TABLE baseTable (
aKey int identity( 10000000,1 ) PRIMARY KEY,
fname VARCHAR(50),
lname VARCHAR(100),
validAddr BIT,
getsMail BIT,
state CHAR(2)
) 下面的代码产生测试数据：
declare @i int
set @i = 0
while @i < 10 — Will yield 10 * 500,000 rows
begin
insert into basetable
select top 500000 f.[name], l.[name], va.val, gm.val, s.code
from
( select top 500 [name] from sampDataFNames order by newid() ) as f,
( select top 500 [name] from sampDataLNames order by newid() ) as l,
( select 0 val union all select 1 val ) as va,
( select 0 val union all select 1 val ) as gm,
( select top 5 code from states order by newid() ) as s
order by newid()
set @i = @i + 1
end 当flag列为1的数据达到25％时就不再产生数据，可用下面的语句检查数据分布情况：
Select
( select count(*) from basetable ) as Total,
( select ( select count(*) from baseTable where validAddr = 1 )* 100.0
/ ( select count(*) from basetable ) ) as PercentValidAddr,
( select ( select count(*) from baseTable where getsMail = 1 )* 100.0
/ ( select count(*) from basetable ) ) as PercentGetsMail,
( select ( select count(*) from baseTable where getsMail = 1 AND validAddr = 1 )* 100.0
/ ( select count(*) from basetable ) ) as PercentBoth 这里会有5百万行足够的数据，上面的统计结果分布在50%、50%、25%。
估算查询性能基线优化的第一步是建立查询基线。为此，我在管理工具里使用显示查询执行计划，如下：
set statistics io on
GO
select fname, lname from baseTable where validAddr = 1 and getsMail = 1 正如预料的那样，该结果进行了聚集索引扫描，执行计划显示成本为25.4，读操作为21561。数据库检查整个表以找出25％匹配条件的记录。看上去这里好像有些额外的工作，但避免了这些额外的工作。
索引那么常规索引又如何呢？猜想它不会有帮助，因为它不会被选择，但是真正测试一下这个假定是重要的：
create nonclustered index IX_Flags on baseTable( validAddr, getsMail )
GO
select fname, lname from baseTable where validAddr = 1 and getsMail = 1 这里没有任何优势。SQLServer仍然同样的成本扫描表；正如我们猜想的，索引没有使用，因为使用索引实际上查找了太多的行。
drop index baseTable.IX_Flags 接下来，创建一个覆盖索引反而是可能的，为所需的数据排序让它们定位在一个范围内。有一个吸引力很少的挑战：索引实际上和表自身一样大，所以需要双倍或接近双倍的存储空间。正确排序的索引将分组我们需要的记录，但存储它们只需25％的空间。如果使用覆盖索引，理论上仅仅需要索引行的一个子集而不是所有行。但常规索引是不可能的。
索引视图最好的解决方法是将需要的行从表中不需要的行里独立出来－－这样，甚至在查询执行之前，‘预读’的行正好匹配条件。如果使用索引视图在仅需要的行的键上建索引，来替代基表会怎样呢？该方法让键的子集匹配条件，而且它们总是准备好的，再连接基表得到正确的结果集。似乎视图比覆盖索引更小，因为仅需一些键。
create view keysOnly with schemabinding
as ( select akey
from dbo.baseTable
where validAddr = 1 and getsMail = 1 )
GO
create unique clustered index IX_keysOnly on keysOnly( aKey ) 这样做有好有坏。现在下面的查询计划成本为2.87，读2019，这比基线有了很大的改进，因为视图里被选择的键总是有效的。然而它取到的仅仅是一些键：
select aKey from keysOnly with( noexpand ) 问题是需要从基表中取得数据，而不是键。我们真正需要的信息如下：
select fname, lname from basetable t where validAddr = 1 and getsMail = 1 为了使用索引视图得到数据，需要连接视图和基表来产生数据，这需要一些开销：
select fname, lname
from keysOnly v with( noexpand )
inner join basetable t on v.akey = t.akey 现在看看查询计划成本为36。这是因为SQLServer不得不扫描连接的两个对象，且连接实际上比简单扫描基表有更多的开销。连接自身需要一个表扫描去定位匹配的行。通过存储正确的键而得到的任何好处被从基表中取得剩余数据的工作抵消。
由这个结果可以清楚地知道，索引视图需要为查询包含结果而不仅仅是键。所以，实际上我将创建一个从基表复制所有需要数据的索引视图，这些行匹配我需要选择的条件。这有点象覆盖索引，除了它仅从基表存储匹配的行而不是所有行之外。为了下面另外的测试，我也将包括地址列。显然为此需要额外的存储空间：
create view completeSubset with schemabinding
as ( select akey, fname, lname, state
from dbo.baseTable
where validAddr = 1 and getsMail = 1 )
GO
create unique clustered index IX_completeSubset on completeSubset( aKey )
GO
select fname, lname from completeSubset with( noexpand ) 这个select语句现在的查询计划成本是4.9，读是4843，大约是原来成本的1/5。性能有显著提高。给查询添加另外的条件仍然能显示相同的好处。让我们查查地址为Montana的所有记录：
从基表中查询:
select fname, lname, state
from dbo.baseTable
where validAddr = 1 and getsMail = 1
and state = 'MT' 查询计划成本为21.57 从索引视图中查询:
select fname, lname, state
from completeSubset with( noexpand )
where state = 'MT' 查询计划成本为5.05
对Update和Insert的影响这个技术在改变基表数据时会有一些成本，所以评估这个成本是多少、是否有害是重要的。示例数据里地址为MT的大概有15万行，所以至少要了解更新的性能，我在这些行上执行一些更新以比较执行计划。这不是一个对Update和Insert的全面测试，仅是一个简短的测试。
首先，对索引视图来说，改变基表不得不也影响到视图的数据。例如如下的语句：
update baseTable set getsMail = 0 where state = 'MT' 一个更新不仅针对基表也针对视图的内容，这些会显示在执行计划的节点‘clustered index update’上。看上去似乎是临时的，但这就是所发生的：视图数据是永久性的，它有自己的聚集索引，所以聚集索引不得不随着表自身而更新。成本是28.79。
接下来删除视图，执行下列语句：
drop view completeSubset
GO
update baseTable set getsMail = 1 where state = 'MT' 没有索引视图的更新成本为24.70，比有索引视图时稍微少一点，正如所料。但这种不同与前面的选择之间的比较看似不一样。对于一个假定读操作多过写操作的系统（许多系统这个因素是十个里的一个因素），这是值得的。粗略估计一下上面估计的成本，理解它是容易的：假定在我们的系统上读是更新的5倍，我们的测试在读上提升了4倍，4乘5就是20多倍在读性能上的提升，而在更新上只有0.86倍的性能丢失。
结论当它们索引的数据没有被选择时，常规索引没有太多帮助。然而如果你有适当的存储空间且数据缓存也可用，且对于insert/update性能问题也不是很关心，那么在这种类型的数据上通过非正规化做成索引视图会对select的性能有极大提高。这种非正规化也是安全的－－不象非正规化表的数据－－数据库引擎自身维护数据一致性。所以不用担心，索引视图里的数据将同基表里的数据同步，不用额外的支持或者编码来保持视图数据更新。
这个技术是对大数据量表的一个好的选择，通常从这些行中基于那些很少用来选择的列来获取数据，但是结果集期望比表自身小。例如，如果选择结果由基表的80％组成，那么这个技术作用不是很大，但如果只选择25％的数据，它将是有用的。这需要在存储空间之间做个平衡，通过仅从基表中存储匹配的行，这样的空间要求比覆盖索引要小。也有insert/update的性能问题，但在许多系统上，select的需求远远远超过insert/update的需求。
