分类: 数据库开发技术
2009-01-31 00:13:35
我们通过一个实例来看 有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) 被解释为错误。
总结知识点:
参考资料
本文演示代码下载地址: