网络数据库_10 SQL server建立索引

1、索引索引索引n回顾新华字典查字法!n 如 “张” 字的搜索方法!n 第一种 n 第二种Sequential File201040306050807010090Sparse Index1030507090110130150170190210230索引的创建和使用索引的创建和使用本章学习目标n了解索引的概念了解索引的概念n了解索引的优点了解索引的优点n掌握索引的分类掌握索引的分类n掌握创建、修改和删除索引的方法掌握创建、修改和删除索引的方法索引的概念n在数据库中，如果想在某个表中快速查找满足在数据库中，如果想在某个表中快速查找满足条件的记录，可以创建索引。索引可以使数据条件的记录，可以创建索引。索

2、引可以使数据库程序无须对整个表进行扫描，就可以在其中库程序无须对整个表进行扫描，就可以在其中找到所需数据。创建索引可以加快从表或视图找到所需数据。创建索引可以加快从表或视图中检索数据的速度。索引包含由表或视图中的中检索数据的速度。索引包含由表或视图中的一列或多列生成的键。一列或多列生成的键。索引的概念n当当SQL Server进行数据查询时，查询优化器进行数据查询时，查询优化器会自动计算现有的几种执行查询方案中，哪种会自动计算现有的几种执行查询方案中，哪种方案的开销最小，速度最快，然后方案的开销最小，速度最快，然后SQL Server就会按照该方案来查询。所以，可以通就会按照该方案来查询。所以

3、，可以通过高效的有序查找算法（如折半查找等）找到过高效的有序查找算法（如折半查找等）找到索引项，再根据索引项中记录的物理地址，找索引项，再根据索引项中记录的物理地址，找到查询结果的存储位置。到查询结果的存储位置。索引的优点n使用索引可以大大提高系统的性能，其具体表现在：使用索引可以大大提高系统的性能，其具体表现在：n可以大大加快数据检索速度。可以大大加快数据检索速度。n通过创建唯一索引，可以保证数据记录的唯一性。通过创建唯一索引，可以保证数据记录的唯一性。n在使用在使用ORDER BY和和GROUP BY子句进行检索数据子句进行检索数据时，可以显著减少查询中分组和排序的时间。时，可以显著减少查

4、询中分组和排序的时间。n使用索引可以在检索数据的过程中使用优化隐藏器，使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。提高系统性能。索引的优点n可以加速表与表之间的连接。可以加速表与表之间的连接。n建立索引的一般原则是：建立索引的一般原则是：n对经常用来检索的字段建立索引。对经常用来检索的字段建立索引。n对数据表中的主键建立索引。对数据表中的主键建立索引。n对数据表中的外键建立索引。对数据表中的外键建立索引。n对经常用于连接的字段建立索引。对经常用于连接的字段建立索引。索引的分类n在在SQL Server 2005中提供的索引类型主要有以下几中提供的索引类型主要有以下几类：聚集索引

5、、非聚集索引、唯一索引、包含性列索类：聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引以及引、索引视图、全文索引以及XML索引。索引。n1聚集索引和非聚集索引聚集索引和非聚集索引n按照存储结构的不同，可以将索引分为两类：聚集索按照存储结构的不同，可以将索引分为两类：聚集索引和非聚集索引。每个表最多可以有引和非聚集索引。每个表最多可以有1个聚集索引和个聚集索引和249个非聚集索引。其中包括为支持表中所定义的个非聚集索引。其中包括为支持表中所定义的PRIMARY KEY和和UNIQUE约束而生成的索引。约束而生成的索引。索引的分类n聚集索引对表在物理数据页中的数据按列进行排序，聚集

6、索引对表在物理数据页中的数据按列进行排序，然后再重新存储到磁盘上。一个表只能有一个聚集索然后再重新存储到磁盘上。一个表只能有一个聚集索引。用聚集索引查找数据很快。聚集索引一般创建在引。用聚集索引查找数据很快。聚集索引一般创建在表中经常搜索的列或者按顺序访问的列上。表中经常搜索的列或者按顺序访问的列上。n创建聚集索引时应该考虑以下几个因素：创建聚集索引时应该考虑以下几个因素：n每个表只能有一个聚集索引。每个表只能有一个聚集索引。n创建任何非聚集索引之前要首先创建聚集索引。创建任何非聚集索引之前要首先创建聚集索引。n关键值的唯一性使用关键值的唯一性使用UNIQUE关键字或者由内部的唯关键字或者由内

7、部的唯一标识符明确维护。一标识符明确维护。n要保证有足够的空间创建聚集索引。要保证有足够的空间创建聚集索引。索引的分类n使用非聚集索引不会影响数据表中记录的实际使用非聚集索引不会影响数据表中记录的实际存储顺序。非聚集索引中存储了组成非聚集索存储顺序。非聚集索引中存储了组成非聚集索引的关键字值和行定位器。由于非聚集索引使引的关键字值和行定位器。由于非聚集索引使用索引页存储，因此它比聚集索引需要较少的用索引页存储，因此它比聚集索引需要较少的存储空间，但检索效率比聚集索引低。由于一存储空间，但检索效率比聚集索引低。由于一个表只能建一个聚集索引，当用户需要建立多个表只能建一个聚集索引，当用户需要建立多

8、个索引时，就需要使用非聚集索引了。每个表个索引时，就需要使用非聚集索引了。每个表中最多只能创建中最多只能创建249个非聚集索引。个非聚集索引。索引的分类n在下列情况下，可以考虑使用非聚集索引：在下列情况下，可以考虑使用非聚集索引：n含有大量唯一值的字段。含有大量唯一值的字段。n返回很小的或者单行结果集的检索。返回很小的或者单行结果集的检索。n使用使用ORDER BY子句的查询。子句的查询。索引的分类n2唯一索引唯一索引n如果要求索引中的字段值不能重复，可以建立唯一索如果要求索引中的字段值不能重复，可以建立唯一索引。引。n创建唯一索引时，对于具有单个字段的索引，要求这创建唯一索引时，对于具有单个

9、字段的索引，要求这个字段中的各个值不能重复。也可以对多个字段的组个字段中的各个值不能重复。也可以对多个字段的组合创建索引，索引中包含多个字段的复合索引也可以合创建索引，索引中包含多个字段的复合索引也可以是唯一索引，多个字段的组合取值不能重复。是唯一索引，多个字段的组合取值不能重复。n注意：一个索引中最多可以包含注意：一个索引中最多可以包含16个字段的组合，并个字段的组合，并且索引中的所有字段必须属于同一个数据表。且索引中的所有字段必须属于同一个数据表。创建索引n在在SQL Server 2005中，索引可以由系统自动中，索引可以由系统自动创建，也可以由用户手工创建。创建，也可以由用户手工创建。

10、n系统在创建表中的其他对象时可以附带地创建系统在创建表中的其他对象时可以附带地创建新索引，例如新建表时，如果创建主键或者唯新索引，例如新建表时，如果创建主键或者唯一性约束，系统会自动创建相应的索引。一性约束，系统会自动创建相应的索引。n例例8-2-1：在：在STUDENT数据库中创建一个新表数据库中创建一个新表T_DEPARTMENT，并将其中的并将其中的D_NAME字字段设置为聚集的唯一索引。段设置为聚集的唯一索引。创建索引n如果在如果在SQL Server管理控制台中用鼠标设置管理控制台中用鼠标设置主键，系统会自动创建一个唯一的聚集索引，主键，系统会自动创建一个唯一的聚集索引，索 引 名

11、为索 引 名 为 “ P K _ 表 名表 名 ” 。 如 果 是 使 用。 如 果 是 使 用Transact-SQL语句添加主键约束，也会创建语句添加主键约束，也会创建一个唯一索引，但索引名称为一个唯一索引，但索引名称为“PK_表名表名_xxxxxxxx”，其中其中x由系统自动生成的。由系统自动生成的。创建索引n如果使用如果使用NONCLUSTERED关键字，会生成关键字，会生成非聚集的唯一索引；使用非聚集的唯一索引；使用CLUSTERED关键字，关键字，会生成聚集的唯一索引。不使用关键字时，如会生成聚集的唯一索引。不使用关键字时，如果此表存在聚集索引，则生成非聚集的唯一索果此表存在聚集索

12、引，则生成非聚集的唯一索引，否则生成聚集的唯一索引。引，否则生成聚集的唯一索引。n例例8-2-2：在：在STUDENT数据库中创建一个新表数据库中创建一个新表T_SPECIALTY，并将其中的并将其中的P_NUMBER字字段设置为主键。段设置为主键。使用SQL Server管理控制台创建索引n使用使用SQL Server管理控制台直接创建索引可管理控制台直接创建索引可以使用两种方法：以使用两种方法：n方法一：在方法一：在SQL Server管理控制台中，展开管理控制台中，展开指定的服务器和数据库，单击要创建索引的表，指定的服务器和数据库，单击要创建索引的表，并右击其中的并右击其中的“索引索引”

13、文件夹，从弹出的快捷文件夹，从弹出的快捷菜单中选择菜单中选择“新建索引新建索引”选项。选项。n例例8-2-1：创建一个新的非聚集索引，要求按：创建一个新的非聚集索引，要求按照照T_STUDENT中的中的S_NAME字段的降序创建，字段的降序创建，索引名为索引名为“IX_S_NAME”。使用SQL Server管理控制台创建索引n方法二：方法二：n进入表结构的设计窗口，在窗口中右击鼠标，进入表结构的设计窗口，在窗口中右击鼠标，从快捷菜单中选择从快捷菜单中选择“索引索引/键键”，会打开，会打开“索索引引/键键”对话框。可以添加新索引或删除已有对话框。可以添加新索引或删除已有的索引。的索引。使用Tr

14、ansact-SQL语句创建索引n利用利用Transact-SQL语句中的语句中的CREATE INDEX命令可命令可以创建索引：以创建索引：nC R E AT E U N I Q U E C L U S T E R E D | NONCLUSTERED n INDEX index_name ON table | view （ column ASC | DESC ,.n ） n其中，各参数的说明如下：其中，各参数的说明如下：nUNIQUE：用于指定为表或视图创建唯一索引。用于指定为表或视图创建唯一索引。nCLUSTERED：用于指定创建的索引为聚集索引。用于指定创建的索引为聚集索引。使用Tra

15、nsact-SQL语句创建索引nNONCLUSTERED：用于指定创建的索引为用于指定创建的索引为非聚集索引。非聚集索引。nindex_name：用于指定所创建的索引名称。用于指定所创建的索引名称。ntable：用于指定创建索引的表名称。用于指定创建索引的表名称。nview：用于指定创建索引的视图名称。用于指定创建索引的视图名称。nASC|DESC：用于指定某个具体索引列的升用于指定某个具体索引列的升序或降序排序方向。默认值为升序（序或降序排序方向。默认值为升序（ASC）。）。nColumn：用于指定被索引的列。用于指定被索引的列。使用Transact-SQL语句创建索引n例例8-2-3：使用

16、：使用CREATE INDEX语句为表语句为表T_STUDENT创建一个非聚集索引，索引字段创建一个非聚集索引，索引字段为为S_NAME，索引名为索引名为IX_STUDENTName。n例例8-2-4：为表：为表T_STUDENT创建一个复合索创建一个复合索引，使用的字段为引，使用的字段为SEX字段和字段和BIRTHDAY字段，字段，排序顺序排序顺序SEX为降序，为降序，BIRTHDAY为升序。为升序。查看、修改和删除索引使用SQL Server管理控制台查看、修改和删除索引n在在SQL Server管理控制台中，展开指定的服管理控制台中，展开指定的服务器和数据库，右击某个表，从弹出的快捷菜务

17、器和数据库，右击某个表，从弹出的快捷菜单中选择单中选择“修改修改”选项，进入表结构的设计窗选项，进入表结构的设计窗口，在窗口中右击鼠标，选择口，在窗口中右击鼠标，选择“索引索引/键键”，即可打开管理索引对话框，在窗口的左部选中即可打开管理索引对话框，在窗口的左部选中某个索引，在窗口右部可以查看此索引的信息，某个索引，在窗口右部可以查看此索引的信息，并可修改相关信息，也可以单击并可修改相关信息，也可以单击“删除删除”按钮，按钮，删除索引。删除索引。使用系统存储过程查看索引信息和更改索引名称n1使用系统存储过程查看索引信息使用系统存储过程查看索引信息n系统存储过程系统存储过程sp_helpinde

18、x可以返回表的所可以返回表的所有索引信息，其语法形式如下；有索引信息，其语法形式如下；nsp_helpindex objname=namen其中，其中，objname=name参数用于指定当参数用于指定当前数据库中的表的名称。前数据库中的表的名称。n例例 8 - 3 - 1 ： 使 用 系 统 存 储 过 程 查 看： 使 用 系 统 存 储 过 程 查 看T_STUDENT表的索引信息。表的索引信息。使用系统存储过程查看索引信息和更改索引名称n2使用系统存储过程更改索引名称使用系统存储过程更改索引名称n可以使用系统存储过程可以使用系统存储过程sp_rename更改索引更改索引的名称，其语法形

19、式如下：的名称，其语法形式如下：nsp_renameobjname=object_name,newname=new_namen,objtype=object_type n例例8-3-2：将：将T_STUDENT表中的索引表中的索引I X _ T _ S T U D E N T 2 的 名 称 更 改 为的 名 称 更 改 为IX_T_STUDENT_SexAndBirth。使用Transact-SQL语句删除索引n可以使用可以使用DROP INDEX命令删除一个或者多命令删除一个或者多个当前数据库中的索引：个当前数据库中的索引：nDROP INDEX table.index | view.in

20、dex ,.n n其中，其中，table | view用于指定索引列所在的表用于指定索引列所在的表或索引视图；或索引视图；index用于指定要删除的索引名用于指定要删除的索引名称。称。使用Transact-SQL语句删除索引n注 意 ：注 意 ： D R O P I N D E X 命 令 不 能 删 除 由命 令 不 能 删 除 由CREATE TABLE或者或者ALTER TABLE命令创命令创建的主键或者唯一性约束索引。建的主键或者唯一性约束索引。n例例8-3-3：删除表：删除表T_STUDENT中的索引中的索引IX_T_STUDENT_SEXAndBirth。n创建简单非聚集索引n以下

21、示例为Purchasing.ProductVendor 表的VendorID 列创建非聚集索引nUSE AdventureWorks;nGOnIF EXISTS (SELECT name FROM sys.indexesn WHERE name = NIX_ProductVendor_VendorID)n DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;nGOnCREATE INDEX IX_ProductVendor_VendorID n ON Purchasing.ProductVendor (VendorI

22、D); nGOnB. 创建简单非聚集组合索引n以下示例为Sales.SalesPerson 表的SalesQuota 和SalesYTD 列创建非聚集组合索引。nUSE AdventureWorksnGOnIF EXISTS (SELECT name FROM sys.indexesn WHERE name = NIX_SalesPerson_SalesQuota_SalesYTD)n DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;nGOnCREATE NONCLUSTERED INDEX IX_Sale

23、sPerson_SalesQuota_SalesYTDn ON Sales.SalesPerson (SalesQuota, SalesYTD);nGOnC. 创建唯一非聚集索引n以下示例为Production.UnitMeasure 表的Name 列创建唯一的非聚集索引。该索引将强制插入Name 列中的数据具有唯一性。nUSE AdventureWorks;nGOnIF EXISTS (SELECT name from sys.indexesn WHERE name = NAK_UnitMeasure_Name)n DROP INDEX AK_UnitMeasure_Name ON Prod

24、uction.UnitMeasure;nGOnCREATE UNIQUE INDEX AK_UnitMeasure_Name n ON Production.UnitMeasure(Name);nGOn以下查询通过尝试插入与现有行包含相同值的一行来测试唯一性约束。n-Verify the existing value.nSELECT Name FROM Production.UnitMeasure WHERE Name = NOunces;nGOnINSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)n

25、VALUES (OC, Ounces, GetDate();n nD. 使用IGNORE_DUP_KEY 选项n以下示例首先在该选项设置为ON 时在临时表中插入多行，然后在该选项设置为OFF 时执行相同操作，以演示IGNORE_DUP_KEY 选项的影响。单个行被插入#Test 表，在执行第二个多行INSERT 语句时将导致出现重复值。表中的行计数会返回插入的行数。nUSE AdventureWorks;nGOnCREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);nGOnCREATE UNIQUE INDEX A

26、K_Index ON #Test (C2)n WITH (IGNORE_DUP_KEY = ON);nGOnINSERT INTO #Test VALUES (NOC, NOunces, GETDATE();nINSERT INTO #Test SELECT * FROM Production.UnitMeasure;nGOnSELECT COUNT(*)AS Number of rows FROM #Test;nGOnDROP TABLE #Test;nGOn n但将IGNORE_DUP_KEY 设置为OFF。nUSE AdventureWorks;nGOnCREATE TABLE #Tes

27、t (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);nGOnCREATE UNIQUE INDEX AK_Index ON #Test (C2)n WITH (IGNORE_DUP_KEY = OFF);nGOnINSERT INTO #Test VALUES (NOC, NOunces, GETDATE();nINSERT INTO #Test SELECT * FROM Production.UnitMeasure;nGOnSELECT COUNT(*)AS Number of rows FROM #Test;nGOnDROP TABLE #

28、Test;nGOn nE. 使用DROP_EXISTING 删除和重新创建索引n以下示例使用DROP_EXISTING 选项在Production.WorkOrder 表的ProductID 列上删除并重新创建现有索引。还设置了FILLFACTOR 和PAD_INDEX 选项。nUSE AdventureWorks;nGOnCREATE NONCLUSTERED INDEX IX_WorkOrder_ProductIDn ON Production.WorkOrder(ProductID)n WITH (FILLFACTOR = 80,n PAD_INDEX = ON,n DROP_EXIST

29、ING = ON);nGOnG. 创建具有包含性（非键）列的索引创建具有包含性（非键）列的索引n以下示例创建具有一个键列以下示例创建具有一个键列(PostalCode) 和四个非键列和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的非聚集）的非聚集索引。然后执行该索引覆盖的查询。若要显示查询优化器选择的索引，索引。然后执行该索引覆盖的查询。若要显示查询优化器选择的索引，执行查询前请在执行查询前请在SQL Server Management Studio 中的中的“查询查询”菜单上选菜单上选择择“显示实际执行计划显示实际执行计划”。nU

30、SE AdventureWorks;nGOnIF EXISTS (SELECT name FROM sys.indexesn WHERE name = NIX_Address_PostalCode)n DROP INDEX IX_Address_PostalCode ON Person.Address;nGOnCREATE NONCLUSTERED INDEX IX_Address_PostalCoden ON Person.Address (PostalCode)n INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);nGO

31、nSELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCodenFROM Person.AddressnWHERE PostalCode BETWEEN N98000 and N99999;nGOntext索引应用n在公文表中 对索引进行举例.哪些地方适合建立索引n举例!哪些地方适合建立索引n举例!n列名 操作符 n列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：nName=张三 n价格5000 n50005000 nname like 张% ，可建索引 n而：name like %张 ,不应该建索引

32、nor 会引起全表扫描 Name=张三 and 价格5000 符号可以建立索引n而：Name=张三 or 价格5000 不应建立索引,使用or会引起全表扫描。 nIN 的作用相当与OR 语句： nSelect * from table1 where tid in (2,3) 和 Select * from table1 where tid=2 or tid=3 是一样的，都会引起全表扫描，如果tid上有索引，其索引也会失效。改善SQL语句n字段提取要按照“需多少、提多少”的原则，避免“select *” n我们来做一个试验：nselect top 10000 gid,fariqi,reader

33、,title from tgongwen order by gid descn用时：毫秒nselect top 10000 gid,fariqi,title from tgongwen order by gid descn用时：毫秒nselect top 10000 gid,fariqi from tgongwen order by gid descn用时：毫秒改善SQL语句norder by按聚集索引列排序效率最高n我们来看：（gid是主键，fariqi是聚合索引列）：nselect top 10000 gid,fariqi,reader,title from tgongwenn用时：毫秒。

34、扫描计数1，逻辑读289 次，物理读1 次，预读1527 次。nselect top 10000 gid,fariqi,reader,title from tgongwen order by gid ascn用时：毫秒。扫描计数1，逻辑读41956 次，物理读0 次，预读1287 次。nselect top 10000 gid,fariqi,reader,title from tgongwen order by gid descn用时：毫秒。扫描计数1，逻辑读55350 次，物理读10 次，预读775 次。nselect top 10000 gid,fariqi,reader,title from tgongwen order by fariqi ascn用时：毫秒。扫描计数1，逻辑读290 次，物理读0 次，预读0 次。nselect top 10000 gid,fariqi,reader,title from tgongwen order by fariqi descn用时：毫秒。扫描计数1，逻辑读289 次，物理读0 次，预读0 次。n从以上我们可以看出，不排序的速度以及逻辑读次数都