SQL Server 索引基础知识(7)----Indexing for AND

作者：郭红俊

时间：January 18, 2008

我们通过一个实例来看 有 And 操作符时候的最常见的一种情况。我们有下面一个表，

CREATE TABLE [dbo].[member](
    [member_no] [dbo].[numeric_id] IDENTITY(1,1) NOT NULL,
    [lastname] [dbo].[shortstring] NOT NULL,
    [firstname] [dbo].[shortstring] NOT NULL,
    [middleinitial] [dbo].[letter] NULL,
    [street] [dbo].[shortstring] NOT NULL,
    [city] [dbo].[shortstring] NOT NULL,
    [state_prov] [dbo].[statecode] NOT NULL,
    [country] [dbo].[countrycode] NOT NULL,
    [mail_code] [dbo].[mailcode] NOT NULL,
    [phone_no] [dbo].[phonenumber] NULL,
    [photograph] [image] NULL,
    [issue_dt] [datetime] NOT NULL   DEFAULT (getdate()),
    [expr_dt] [datetime] NOT NULL  DEFAULT (dateadd(year,1,getdate())),
    [region_no] [dbo].[numeric_id] NOT NULL,
    [corp_no] [dbo].[numeric_id] NULL,
    [prev_balance] [money] NULL   DEFAULT (0),
    [curr_balance] [money] NULL  DEFAULT (0),
    [member_code] [dbo].[status_code] NOT NULL   DEFAULT ('  ')
)

这个表具备下面的四个索引：

索引名

细节

索引的列

member_corporation_link

nonclustered located on PRIMARY

corp_no

member_ident

clustered, unique, primary key located on PRIMARY

member_no

member_region_link

nonclustered located on PRIMARY

region_no

MemberFirstName

nonclustered located on PRIMARY

firstname

当我们执行下面的 SQL查询时候，

SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'K%'    
        AND m.Region_No > 6    
        AND m.Member_No < 5000
go
SQL Server 会根据索引方式，优化成下面方式来执行。
select a.Member_No,a.FirstName,b.Region_No
from
(select m.Member_No, m.FirstName from dbo.Member AS m 
where m.FirstName LIKE 'K%' and m.Member_No < 5000) a , 
-- 这个查询可以直接使用 MemberFirstName 非聚集索引，而且这个非聚集索引覆盖了所有查询列
-- 实际执行时，只需要 逻辑读取 3 次
 
(SELECT m.Member_No, m.Region_No from dbo.Member AS m
where m.Region_No > 6) b
 
-- 这个查询可以直接使用 member_region_link 非聚集索引，而且这个非聚集索引覆盖了所有查询列
-- 实际执行时，只需要 逻辑读取 10 次
 
where a.Member_No = b.Member_No

不信，你可以看这两个 SQL 的执行计划，以及逻辑读信息，都是一样的。

其实上面的 SQL，如果优化成下面的方式，实际的逻辑读消耗也是一样的。为何SQL Server 不会优化成下面的方式。是因为 and 操作符优化的另外一个原则。

1/26 的数据和 1/6 的数据找交集的速度要比 1/52 的数据和 1/3 的数据找交集速度要慢。

select a.Member_No,a.FirstName,b.Region_No
from
(select m.Member_No, m.FirstName from dbo.Member AS m
where m.FirstName LIKE 'K%'    
-- 1/26 数据
) a,
 
(SELECT m.Member_No, m.Region_No from dbo.Member AS m
where m.Region_No > 6 and m.Member_No < 5000
-- 1/3 * 1/ 2 数据
) b
where a.Member_No = b.Member_No

当然，我们要学习 SQL 如何优化的话，就会用到查询语句中的一个功能，指定查询使用哪个索引来进行。

比如下面的查询语句：

SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (INDEX (0))
WHERE m.FirstName LIKE 'K%'        
        AND m.Region_No > 6        
        AND m.Member_No < 5000
go
 
SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (INDEX (1))
WHERE m.FirstName LIKE 'K%'        
        AND m.Region_No > 6        
        AND m.Member_No < 5000
go
SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (INDEX (MemberCovering3))
WHERE m.FirstName LIKE 'K%'        
        AND m.Region_No > 6        
        AND m.Member_No < 5000
go
SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (INDEX (MemberFirstName, member_region_link))
WHERE m.FirstName LIKE 'K%'        
        AND m.Region_No > 6        
        AND m.Member_No < 5000
go

这里 Index 计算符可以是 0 ，1， 指定的一个或者多个索引名字。对于 0 ，1 的意义如下：

如果存在聚集索引，则 INDEX(0) 强制执行聚集索引扫描，INDEX(1) 强制执行聚集索引扫描或查找（使用性能最高的一种）。
如果不存在聚集索引，则 INDEX(0) 强制执行表扫描，INDEX(1) 被解释为错误。

总结知识点：

• 简单来说，我们可以这么理解： SQL Server 对于每一条查询语句。会根据实际索引情况（sysindexes 系统表中存储这些信息），分析每种组合可能的成本。然后选择它认为成本最小的一种。作为它实际执行的计划。
• 成本代价计算的一个主要组成部分是逻辑 I/O的数量，特别是对于单表的查询。
• AND 操作要满足所有条件，这样，经常会要求对几个数据集作交集。数据集越小，数据集的交集计算越节省成本。