学步园

全文索引的使用方法(一) 收藏

 /***************************************************

          作者：herowang(让你望见影子的墙）

    日期：2009.8.11

          注：    转载请保留此信息

    更多内容，请访问我的博客：blog.csdn.net/herowang

****************************************************/

view plaincopy to clipboardprint?
/*建立测试环境*/ 
 
if object_id('tb') is not null 
 
   drop table tb  
 
go  
 
create table tb  
 
(id int identity(1,1),  
 
 title varchar(200),   
 
 detail varchar(1000),  
 
 constraint pk_id primary key(id) –在建立全文索引时需要使用  
 
 )  
 
insert into tb  
 
select '火箭即将签下新秀射手','　据悉，巴丁格与火箭队的合同谈判是于昨天完成的，巴丁格将得到与泰勒一样的合同。此前媒体曝光泰勒的合同为期四年，总价值万美元，其中前两年为保障性合同。巴丁格预计会在接下来几天内正式宣布签约加盟火箭。'   
 
union all  
 
select '韦弗被曝已与希腊豪门签约','据国际篮球网报道，前火箭队球员范-韦弗已经与希腊豪门奥林匹亚科斯队签订了合同。韦弗得到一份为期两年，总价值万美元的合同。' 
 
union all  
 
select '马刺豪掷千金为对抗湖人','马刺队在今夏休赛期补充了几员大将，主教练格雷格-波波维奇日前在接受Yahoo!体育采访时透露，马刺队不惜缴纳奢侈税构建豪华阵容就是为了对抗湖人队，争取拿到第五个总冠军。' 
 
union all  
 
select '华莱士未曾想过离开汽车城','此前本-华莱士已经同意重返底特律活塞，并且以老将底薪和活塞签下一份年万美元的合同，而据《每日先驱报》专栏作家米克-麦格劳透露，这位当年叱咤NBA赛场的内线防守悍将甚至从来就没有考虑过要离开活塞队。' 
 
union all  
 
select '米勒竟好横刀夺爱追求人妻','对于那些没看过雷吉·米勒在步行者创造“米勒时间”的“后”们，应该怎么介绍这位前NBA球星呢？难道从前天洛杉矶马里布海滩上空那架飞机拉的横幅说起？恐怕没有哪位家长愿意这么做。' 
 
union all  
 
select '姚明：没把上海当投资项目乐得生意做了好人当了','“姚蜜”说：不缺广告效应的姚明收购濒临绝境的上海东方篮球俱乐部，说明他是真的想为曾经的母队做点事情。' 
 
union all  
 
select '火箭不敌奇才终结年纪录','此役姚麦组合状态糟糕，姚明投中得到分个篮板次盖帽，麦迪投中拿下分个篮板次助攻，两人联手竟不如得到分个篮板次助攻次盖帽的贾米森。' 
 
   
 
第一步：启用数据库的全文索引  
 
sp_fulltext_database enable   --启用数据库的全文索引  
 
go  
 
第二步：建立全文目录  
 
create fulltext catalog tb_fulltext    
 
in path N'D:/Program Files/Microsoft SQL Server2005/MSSQL.1/MSSQL/FTData' 
 
with accent_sensitivity =on  --区分重音  
 
authorization dbo;--全文目录的所有者  
 
第三步：建立全文索引  
 
create fulltext index on tb  
 
(title,detail)  
 
key index pk_id  --指定索引列，为了提高性能，最好使用聚集索引  
 
on tb_fulltext  
 
with change_tracking auto  --在关联的表中修改了数据时，自动更新全文索引。  
 
   
 
第四步：查询示例：  
 
select * from tb  
 
where contains((title,detail),'姚明') 
/*建立测试环境*/

if object_id('tb') is not null

   drop table tb

go

create table tb

(id int identity(1,1),

 title varchar(200),

 detail varchar(1000),

 constraint pk_id primary key(id) –在建立全文索引时需要使用

 )

insert into tb

select '火箭即将签下新秀射手','　据悉，巴丁格与火箭队的合同谈判是于昨天完成的，巴丁格将得到与泰勒一样的合同。此前媒体曝光泰勒的合同为期四年，总价值万美元，其中前两年为保障性合同。巴丁格预计会在接下来几天内正式宣布签约加盟火箭。'

union all

select '韦弗被曝已与希腊豪门签约','据国际篮球网报道，前火箭队球员范-韦弗已经与希腊豪门奥林匹亚科斯队签订了合同。韦弗得到一份为期两年，总价值万美元的合同。'

union all

select '马刺豪掷千金为对抗湖人','马刺队在今夏休赛期补充了几员大将，主教练格雷格-波波维奇日前在接受Yahoo!体育采访时透露，马刺队不惜缴纳奢侈税构建豪华阵容就是为了对抗湖人队，争取拿到第五个总冠军。'

union all

select '华莱士未曾想过离开汽车城','此前本-华莱士已经同意重返底特律活塞，并且以老将底薪和活塞签下一份年万美元的合同，而据《每日先驱报》专栏作家米克-麦格劳透露，这位当年叱咤NBA赛场的内线防守悍将甚至从来就没有考虑过要离开活塞队。'

union all

select '米勒竟好横刀夺爱追求人妻','对于那些没看过雷吉·米勒在步行者创造“米勒时间”的“后”们，应该怎么介绍这位前NBA球星呢？难道从前天洛杉矶马里布海滩上空那架飞机拉的横幅说起？恐怕没有哪位家长愿意这么做。'

union all

select '姚明：没把上海当投资项目乐得生意做了好人当了','“姚蜜”说：不缺广告效应的姚明收购濒临绝境的上海东方篮球俱乐部，说明他是真的想为曾经的母队做点事情。'

union all

select '火箭不敌奇才终结年纪录','此役姚麦组合状态糟糕，姚明投中得到分个篮板次盖帽，麦迪投中拿下分个篮板次助攻，两人联手竟不如得到分个篮板次助攻次盖帽的贾米森。'

第一步：启用数据库的全文索引

sp_fulltext_database enable   --启用数据库的全文索引

go

第二步：建立全文目录

create fulltext catalog tb_fulltext 

in path N'D:/Program Files/Microsoft SQL Server2005/MSSQL.1/MSSQL/FTData'

with accent_sensitivity =on  --区分重音

authorization dbo;--全文目录的所有者

第三步：建立全文索引

create fulltext index on tb

(title,detail)

key index pk_id  --指定索引列，为了提高性能，最好使用聚集索引

on tb_fulltext

with change_tracking auto  --在关联的表中修改了数据时，自动更新全文索引。

第四步：查询示例：

select * from tb

where contains((title,detail),'姚明')

查询的语法：

1、  搜索特定词：contains(detail,'姚明')

2、  搜索特定短语：用““将短语包含在双引号内，contains(detail,'姚明 上海')

3、  从多个列中搜索词和短语：contains((title,detail),'姚明 上海')

4、搜索以指定文本开头的词或短语：contains(detail,'“姚明*“')

如果文本和星号不包含在双引号内，则全文搜索会将星号看做是一个字符。如果搜索的是短语，则该短语内的每个词都被看做是一个前缀。contains(detail,'“姚明 上海*“')则将返回第一个词以姚明开头第二个词以上海开头的结果

5、  搜索特定词的变形：contains(detail,'formsof(inflectional,ride)')

将返回表中含有ride，rides，riding，ridden的行

6、搜索与另一个词或短语临近的词或者短语：contains(detail,'姚明 near 上海')

7、使用加权值的词或短语：contains(description,’isabout(performance weight(.8),comfortable weight(.4),smooth weight(.2))’)

Weight为每个词或短语指定一个0.0~1.0之间的加权值

8、使用多个搜索条件： contains(detail,'”姚明” or “上海”')

                     contains(detail,'”姚明” and “上海”')

                       contains(detail,'”姚明” and not  “上海”')

9、在contains中还可以使用变量

10、搜索同义词         contains(title,'formsof(thesaurus,上)')

全文索引的使用(二)--使用同义词库 收藏

/***************************************************

          作者：herowang(让你望见影子的墙）

    日期：2009.8.11

          注：    转载请保留此信息

    更多内容，请访问我的博客：blog.csdn.net/herowang

****************************************************/

在上例中，执行

select * from tb

where contains(title,'上海')

结果：

id     title  detail

6     姚明：没把上海当投资项目 乐得生意做了好人当了     “姚蜜”说：不缺广告效应的姚明收购濒临绝境的上海东方篮球俱乐部，说明他是真的想为曾经的母队做点事情。

但是执行：

select * from tb

where contains(title,'上')

结果为空集。

原因：在进行全文索引查询的时候，“上”为简体中文的干扰词，即查询的时候会忽略掉；另外建立全文索引的时候一般会以一个词组作为一个索引项，而不是单个词。

如果还要进行此查询，想查出第一个查询的结果，可以修改全文索引的同义词库。

干扰词与同义词文件存在的路径：

D:/Program Files/Microsoft SQL Server2005/MSSQL.1/MSSQL/FTData，每一个文件名应该很好辨别。

简单介绍下修改同义词库的方法：

1、  使用记事本打开tschs.xml，这是简体中文的同义词库

view plaincopy to clipboardprint?
<XML ID="Microsoft Search Thesaurus">  
 
<!--  Commented out 
 
    <thesaurus xmlns="x-schema:tsSchema.xml">  
 
       <diacritics_sensitive>0</diacritics_sensitive>  
 
        <expansion>  
 
            _{Internet Explorer}  
 
            _IE  
 
            _IE5  
 
        </expansion>  
 
        <replacement>  
 
            <pat>NT5</pat>  
 
            <pat>W2K</pat>  
 
            _{Windows 2000}  
 
        </replacement>  
 
        <expansion>  
 
            _run  
 
            _jog  
 
        </expansion>  
 
    </thesaurus>  
 
-->  
 
</XML> 
<XML ID="Microsoft Search Thesaurus">

<!--  Commented out

    <thesaurus xmlns="x-schema:tsSchema.xml">

       <diacritics_sensitive>0</diacritics_sensitive>

        <expansion>

            _{Internet Explorer}

            _IE

            _IE5

        </expansion>

        <replacement>

            <pat>NT5</pat>

            <pat>W2K</pat>

            _{Windows 2000}

        </replacement>

        <expansion>

            _run

            _jog

        </expansion>

    </thesaurus>

-->

</XML>

 解释：

<replacement>

<pat>NT5</pat>

   <pat>W2K</pat>

   _{Windows 2000}

</replacement>

为替代词，即查询W2K时，会自动替换为Windows 2000进行查询

<expansion>

   _run

   _jog

</expansion>

为同义词库，即查询run的时候也会查询jog

在本例中添加：

<expansion>

_上

   _上海

</expansion>

2、  去掉开始于末尾的注释行：

    <!--  Commented out

-->

3、最终形成的结果为：

   view plaincopy to clipboardprint?
<XML ID="Microsoft Search Thesaurus">  
 
    <thesaurus xmlns="x-schema:tsSchema.xml">  
 
       <diacritics_sensitive>0</diacritics_sensitive>  
 
        <expansion>  
 
            _{Internet Explorer}  
 
            _IE  
 
            _IE5  
 
        </expansion>  
 
        <replacement>  
 
            <pat>NT5</pat>  
 
            <pat>W2K</pat>  
 
            _{Windows 2000}  
 
        </replacement>  
 
        <expansion>  
 
            _run  
 
            _jog  
 
        </expansion>  
 
       <expansion>  
 
            _上  
 
            _上海  
 
        </expansion>  
 
    </thesaurus>  
 
</XML> 
<XML ID="Microsoft Search Thesaurus">

    <thesaurus xmlns="x-schema:tsSchema.xml">

       <diacritics_sensitive>0</diacritics_sensitive>

        <expansion>

            _{Internet Explorer}

            _IE

            _IE5

        </expansion>

        <replacement>

            <pat>NT5</pat>

            <pat>W2K</pat>

            _{Windows 2000}

        </replacement>

        <expansion>

            _run

            _jog

        </expansion>

       <expansion>

            _上

            _上海

        </expansion>

    </thesaurus>

</XML>

4、  执行查询语句：

select * from tb

where contains(title,'formsof(thesaurus,上)')

即可看到查询结果与contains(title,'上海')相同

写这个proc有两个原因:
1.是系统的sp_helpindex不能显示include列
2.在做发布数据库时，可以只发布数据，然后脚建索引的脚本生成T-sql，到订阅端去执行.

SQL code

 
create proc p_helpindex 
@tbname sysname ='' ,@type char(1) = '1'
as
--生成索引信息及索引创建脚本 
--author : perfectaction
--@tbname 表名，空返回所有表索引
--@type   是否显示聚集索引,1显示聚集索引,2不显示聚集索引
--调用：p_helpindex 'dbo.customers','1'
with t as (
select rank() over (order by b.name,a.name,c.name) as id,c.index_id, 
b.name as schema_name,a.name as table_name,c.fill_factor,c.is_padded,
c.name as ix_name,c.type,e.name as column_name,d.index_column_id,c.is_primary_key,
d.is_included_column,f.name as filegroup_name,c.is_unique,c.ignore_dup_key,
d.is_descending_key as is_descending_key,c.allow_row_locks,c.allow_page_locks
from sys.tables as a
inner join sys.schemas as b on a.schema_id=b.schema_id and a.is_ms_shipped=0
inner join sys.indexes as c on a.object_id=c.object_id
inner join sys.index_columns as d on d.object_id=c.object_id and d.index_id=c.index_id
inner join sys.columns as e on e.object_id=d.object_id and e.column_id=d.column_id
inner join sys.data_spaces as f on f.data_space_id=c.data_space_id 
where a.object_id like '%'+isnull(ltrim(object_id(@tbname)),'')+'%'
and c.is_hypothetical=0 and is_disabled=0 and c.type>=@type
)
select distinct a.schema_name,a.table_name,a.ix_name,
case a.type when 1 then 'clustered' when 2 then 'nonclustered' else '' end as index_type,
case a.is_primary_key when 0 then 'no' else 'yes' end as is_primary_key,
m.ix_index_column_name,isnull(m.ix_index_include_column_name,'') as ix_index_include_column_name,
a.filegroup_name,replace('create '+ case when is_unique=1 then 'unique ' else '' end 
+ case when a.type=1 then 'clustered' else 'nonclustered' end  +' index '
+ a.ix_name+' on '+a.schema_name+'.'+a.table_name+'('+m.ix_index_column_name+')'
+ case when m.ix_index_include_column_name is null then '' else 'include('+m.ix_index_include_column_name

+')'end 
+ case when fill_factor>0 or ignore_dup_key=1 or is_padded=1 or allow_row_locks=0 or allow_page_locks

=0 then 'with(' else '' end
+ case when fill_factor>0 then ',fillfactor='+rtrim(fill_factor) else '' end 
+ case when is_padded=1 then ',pad_index=on' else '' end 
+ case when ignore_dup_key=1 then ',ignore_dup_key=on' else '' end 
+ case when allow_row_locks=0 then ',allow_row_locks=off' else '' end
+ case when allow_page_locks=0 then ',allow_page_locks=off' else '' end
+ case when fill_factor>0 or ignore_dup_key=1 or is_padded=1 or allow_row_locks=0 or allow_page_locks=0

 then ')' else '' end,'with(,','with(')
as sqlscript
from t as a
outer apply  
(  
    select ix_index_column_name= stuff(replace(replace(  
            (  
                select case when b.is_descending_key =1 then column_name + ' desc' else column_name end as 

column_name  
                from t as b where a.id=b.id and is_included_column=0 order by index_column_id for xml auto  
            ), '<b column_name="', ','), '"/>', ''), 1, 1, '')  
            ,ix_index_include_column_name= stuff(replace(replace(  
            (  
                select column_name from t as b where a.id=b.id and is_included_column=1  
                order by index_column_id for xml auto  
            ), '<e column_name="', ','), '"/>', ''), 1, 1, '')  
)m  
order by a.schema_name,a.table_name,a.ix_name

create database db_test
go
use db_test
go

create table tb(id int primary key,col_1 varchar(20),col_2 varchar(30),col_3 varchar(30))
go
insert into tb select 1,'a','b','c'
go
create index ix_01 on tb(col_1)
create index ix_03 on tb(col_1,col_2 desc)
create index ix_02 on tb(col_1)include(col_2)with(fillfactor=80,pad_index=on)
create unique index ix_04 on tb(col_1,col_3)include(col_2)with(ignore_dup_key=on)
go

--执行这个脚本的结果
p_helpindex tb
/*--生成的创建脚本
create nonclustered index ix_01 on dbo.tb(col_1)
create nonclustered index ix_02 on dbo.tb(col_1)include(col_2)with(fillfactor=80,pad_index=on)
create nonclustered index ix_03 on dbo.tb(col_1,col_2 desc)
create unique nonclustered index ix_04 on dbo.tb(col_1,col_3)include(col_2)with(ignore_dup_key=on)
create unique clustered index PK__tb__7C8480AE on dbo.tb(id)
*/

随着“金盾工程”建设的逐步深入和公安信息化的高速发展，公安计算机应用系统被广泛应用在各警种、各部门。与此同时，应用系统体系

的核心、系统数据的存放地――数据库也随着实际应用而急剧膨胀，一些大规模的系统，如人口系统的数据甚至超过了1000万条，可谓海量。

那么，如何实现快速地从这些超大容量的数据库中提取数据（查询）、分析、统计以及提取数据后进行数据分页已成为各地系统管理员和数

据库管理员亟待解决的难题。

在以下的文章中，我将以“办公自动化”系统为例，探讨如何在有着1000万条数据的MS SQL SERVER数据库中实现快速的数据提取和数

据分页。以下代码说明了我们实例中数据库的“红头文件”一表的部分数据结构：

CREATE TABLE [dbo].[TGongwen] ( --TGongwen是红头文件表名

[Gid] [int] IDENTITY (1, 1) NOT NULL ,
--本表的id号，也是主键

[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,  
--红头文件的标题

[fariqi] [datetime] NULL ,
--发布日期

[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
--发布用户

[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,

--需要浏览的用户。每个用户中间用分隔符“,”分开

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



下面，我们来往数据库中添加1000万条数据：

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,办公室,王局长,刘局长,张局长,

admin,

刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,外事科','这是最先的25万条记录')

set @i=@i+1

end

GO



declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','办公室','办公室,通信科,王局长,刘局长,张局长,

admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事科','这是中间的25万条记录')

set @i=@i+1

end

GO



declare @h int

set @h=1

while @h<=100

begin

declare @i int

set @i=2002

while @i<=2003

begin

declare @j int

set @j=0

while @j<50

begin

declare @k int

set @k=0

while @k<50

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))

+':'+cast(@j as varchar(2)),'通信科','办公室,通信科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,

外事科','这是最后的50万条记录')

set @k=@k+1

end

set @j=@j+1

end

set @i=@i+1

end

set @h=@h+1

end

GO



declare @i int

set @i=1

while @i<=9000000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,办公室,王局长,刘局长,张局长,

admin,

刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,外事科','这是最后添加的900万条记录')

set @i=@i+1000000

end

GO

通过以上语句，我们创建了25万条由通信科于2004年2月5日发布的记录，25万条由办公室于2004年9月6日发布的记录，2002年和2003

年各100个2500条相同日期、不同分秒的由通信科发布的记录（共50万条），还有由通信科于2004年5月5日发布的900万条记录，

合计1000万条。



一、因情制宜，建立“适当”的索引

建立“适当”的索引是实现查询优化的首要前提。

索引（index）是除表之外另一重要的、用户定义的存储在物理介质上的数据结构。当根据索引码的值搜索数据时，索引提供了对数据的快速

访问。事实上，没有索引,数据库也能根据SELECT语句成功地检索到结果，但随着表变得越来越大，使用“适当”的索引的效果就越来越明显

。注意，在这句话中，我们用了“适当”这个词，这是因为，如果使用索引时不认真考虑其实现过程，索引既可以提高也会破坏数据库的工

作性能。

（一）深入浅出理解索引结构

实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、

簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的

区别：

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是

“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以

“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，

因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才

的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合

“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中

“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些

字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文

中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所

需要的页码。

我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。

进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

（二）何时使用聚集索引或非聚集索引

下面的表总结了何时使用聚集索引或非聚集索引（很重要）。
动作描述  
使用聚集索引  
使用非聚集索引  


大数目的不同值  
不应  
应  

频繁更新的列  
不应  
应  

外键列  
应  
应  

主键列  
应  
应  

频繁修改索引列  
不应  
应  


事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，

恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这

本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中

查到每一项数据对应的页码，然后再根据页码查到具体内容。

（三）结合实际，谈索引使用的误区

理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行

综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。

1、主键就是聚集索引

这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。

通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例

中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在

数据库中按照ID进行物理排序，但笔者认为这样做意义不大。

显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。

从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。

在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主

键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”

规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。

在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的

是“日期”还有用户本身的“用户名”。

通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如

果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大

多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库

仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2

年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。

在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您

在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的

数据为25万条）：

（1）仅在主键上建立聚集索引，并且不划分时间段：

Select gid,fariqi,neibuyonghu,title from tgongwen

用时：128470毫秒（即：128秒）

（2）在主键上建立聚集索引，在fariq上建立非聚集索引：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用时：53763毫秒（54秒）

（3）将聚合索引建立在日期列（fariqi）上：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用时：2423毫秒（2秒）

虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数

据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒

弃ID列作为聚集索引的一个最重要的因素。

得出以上速度的方法是：在各个select语句前加：declare @d datetime

set @d=getdate()

并在select语句后加：

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能显著提高查询速度

事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚

合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。

从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。

在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能

只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他

们合并起来，建立一个复合索引（compound index）。

很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会

减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户

名neibuyonghu排在后列）

（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'  

查询速度：2513毫秒

（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'

查询速度：2516毫秒

（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'

查询速度：60280毫秒

从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用

上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不

起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会

形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。

（四）其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

下面是实例语句：（都是提取25万条数据）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

使用时间：3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用时间：4470毫秒

这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。

2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用时：12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用时：18843

这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚

集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。

3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

用时：6343毫秒（提取100万条）  

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

用时：3170毫秒（提取50万条）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

用时：3326毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/HEROWANG/archive/2009/08/11/4436605.aspx