第4章 索引

第4章索引——加快查询速度主要内容•索引类型•创建索引的方法•维护索引的方法2020/1/2224.1.1索引的简介及分类索引是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。它提供了数据库中编排表中数据的内部方法。通常情况下一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说小得多。2020/1/223为什么要创建索引•创建索引的优点•帮助用户提高查询速度•利用索引的唯一性来控制记录的唯一性•可以加速表与表之间的连接•降低查询中分组和排序的时间SQLServer访问数据的方式表扫描SQLServer扫描表的所有页索引SQLServer使用索引页找到行4.1.1.2索引的分类唯一索引和非唯一索引聚集索引和非聚集索引单列索引、复合索引和包含性列索引视图索引全文索引2020/1/226索引的分类1）聚集索引将表中的记录在物理数据页中的位置按索引字段值重新排序，再将重排后的结果写回到磁盘上。每个表只能有一个聚集索引创建时注意：＊每个表只能有一个聚集索引＊表中的物理和索引中的物理顺序是相同的＊保证有足够的空间创建聚集索引索引的分类2）非聚集索引与表中数据行的实际存储结构无关不会改变数据表中记录的实际存储顺序，每个表可以有多个非聚集索引3）复合索引对表创建的索引是基于多个字段对表中的记录排序的4）唯一索引要求创建索引的关键字段值在表中不能有重复值，建立唯一索引的字段最好不允许为空（NOTNULL，空值被认为是重复字段）4.1.2创建索引的注意事项1.要合理的建立索引，而不要认为索引越多越好，否则不仅达不到提高性能的目的，反而会适得其反2.每个表只能有一个聚簇(聚集)索引3.聚集索引适合于检索连续键值2020/1/229表4.1合理使用聚集索引和非聚集索引2020/1/22104.1.3在SQLServerManagementStudio创建索引创建索引的方法有直接和间接的方法1.直接方法2.间接方法2020/1/22114.1.3.1间接创建索引•当为表的某个字段建立了主键或者唯一性约束，系统会自动为这个字段创建索引•主键（PrimaryKey）－聚集索引•唯一键（Unique）－非聚集索引2020/1/22124.1.3.2在SQLServerManagementStudio中创建索引1．打开“SQLServerManagementStudio”窗口，连接到本地实例，在【对象资源管理器】窗口中，依次展开到索引节点。2．右击“索引”，在弹开的快捷菜单中选择【新建索引】命令。弹出新建索引对话框2020/1/2213点这里2020/1/22143.输入索引名称选择索引类型点这里4.出现选择列对话框选择姓名点这里2020/1/22155.选择排序规则最后确定多媒体：新建索引用户反映对于Sales.SalesOrderDetail表的访问相当缓慢，你发现用户经产需要查询OrderQty列。请为该表创建索引，同时索引应当尽可能小。CREATEINDEX语句WITH选项目的ALLOW_ROW_LOCKS在索引上允许/不允许行级锁ALLOW_PAGE_LOCKS在索引上允许/不允许页级锁ONLINE在创建的时候允许/不允许访问索引FILLFACTOR控制在叶级页上释放控件PAD_INDEX控制在非叶级页上释放空间CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...,n])INCLUDE(column[,...,n])[WITHoption[,...,n]][ON{partition_scheme(column)|filegroup|default}]唯一索引•确保在索引键上不存在相同的值CREATEUNIQUENONCLUSTEREDINDEX[AK_Employee_LoginID]ON[HumanResources].[Employee]([LoginID]ASC)EmployeeIDLoginIDGenderMaritalStatus…216mike0MS…231fukiko0MM…242pat0MS…291pat0FS……不允许存在相同值2020/1/22192020/1/22204.1.4查看修改索引•SQLServerManagementStudio•“对象资源管理器”•索引“属性”窗口•报告•系统存储过程•sp_help•sp_helpindex2020/1/22214.1.5重新生成索引2020/1/22224.1.6禁用索引1.利用SQLServerManagementStudio禁用索引2.使用T-SQL语句中的DISABLE参数禁用索引2020/1/22234.1.7删除索引利用T-SQL的DROPINDEX语句删除索引例4-13:将成绩表中的课程号字段的UQ__成绩__023D5A04删除。USExueshengGODROPINDEX成绩.UQ__成绩__023D5A042020/1/2224课堂练习设有一个职员表为Customers,其上有客户姓名（Name），客户ID（ID）等列，表执行如下语句：CREATECLUSTEREDINDEXidxONCustomers(Name)得到以下错误Cannotcreatemorethanoneclusteredindex原因是什么，为什么会出错?4.2全文索引表4.3常规索引与全文索引比较2020/1/22264.2.1全文索引介绍全文索引包含在全文索引目录中（Full-TextCatalogs），通常是由同一数据库中的一个或多个表的全文索引构成一个全文索引目录。一个表只能有一个全文索引，因此每个有全文索引的表只隶属于一个全文索引目录。2020/1/22274.2.2全文目录管理全文目录是存储全文索引的地方，全文目录必须保存在与SQLServer实例相关联的本地硬盘上，每个全文目录可以用于满足数据库内的一个或多个表的索引需求。要想创建全文索引，首先必须创建全文目录。2020/1/22284.2.2.1启动全文搜索1.选择windows操作系统的【管理工具】|【服务】中，对【SQLServerFulltextSearch】进行启动。如图4.19所示。图4.19利用windows启动全文搜索2020/1/22292.可以直接在SQLServer2005中依次选择【管理】|【全文搜索】，单击右键选择【启动】命令即可。如教材中图4.20所示。3.利用T-SQL命令实现对应的T-SQL语句命令格式如下：sp_fulltext_database[@action=]'action'其中[@action=]‘action’表示将要执行的操作。action的数据类型为varchar(20)，可以是enable和disable两个值。其中enable表示在当前数据库中启用全文索引；disable表示对于当前数据库中，删除文件系统中所有的全文目录，并且将该数据库标记为已经禁用全文索引。此操作并不在全文目录或表级上更改任何全文索引元数据。2020/1/22304.2.2.1启动全文搜索4.2.2.2创建全文目录在SQLServerManagementStudio创建全文索引目录使用T-SQL语句创建全文目录创建全文目录的T-SQL语句格式为：CREATEFULLTEXTCATALOGcatalog_name/*全文目录名称/[ONFILLEGROUPfilegroup]/*包含全文目录的文件组名默认是主文件组*/[INPATH'rootpath']/*全文目录的路径*/[WITHcatalog_option]/*指定将应用于该目录的选项*/[ASDEFAULT]/*指定该全文目录为默认目录*/[AUTHORIZATIONowner_name]/*将全文目录的所有者设为数据库用户名或角色的名称*/2020/1/22314.2.3创建全文索引例4-18为“学生”表的“姓名”、“家庭住址”2个字段创建全文索引，其代码如下：CREATEFULLTEXTINDEXON学生（姓名，家庭住址TYPECOLUMN扩展名）KEYINDEXPK_学生ONSQL全文目录2020/1/22324.2.4使用全文搜索查询在一个表中创建了全文索引后，才可以对表中的数据进行全文检索。进行全文检索需要在SELECT命令的WHERE字句中使用Transact-SQL谓词：CONTAINS和FRETEXT。2020/1/22334.2.4.1使用CONTAINS搜索简单词的搜索方式简单词就是搜索一个或多个特定的词或短语。例4-19:搜索学生表中的家庭住址中含有“黑龙江省”的记录，其代码如下：SELECT*FROM学生WHERECONTAINS(家庭住址,'黑龙江省')前缀词的搜索方式例4-20:搜索学生表中的家庭住址中含有“上海”开头的单词的记录，其代码如下：SELECT*FROM学生WHERECONTAINS(家庭住址,'上海*')其中*只能放在英文字母之后，代表一个或多个字符。2020/1/2234邻近词的搜索方式例4-21:搜索学生表中家庭住址中与黑龙江省考生相关的记录。usexueshengSELECT*FROM学生WHERECONTAINS(家庭住址,'黑龙江省NEAR家庭住址')2020/1/2235FREETEXT的语法代码为：FREETEXT({column_name|(column_list)|*},'freetext_string'[,LANGUAGElanguage_term})其中：column_name：字段名column_list：字段列表*表示所有字段freetext_string：要搜索的字符串LANGUAGElanguage_term：用于单词断字、词干分析、同义词查询以及干扰词删除特定的语言4.2.4.2使用FREETEXT搜索2020/1/2236例4-22查询“黑龙江省”的学生信息。usexueshengSelect*from学生Wherefreetext(家庭住址,'黑龙江省')2020/1/2237Anyquestion？