创建与管理索引

创建与管理索引1目录1:数据库中数据的存储形式2:创建索引的必要性与索引类型3:簇索引与非簇索引4:全文索引2NO:1上节回顾3上节回顾视图概念创建视图视图的应用修改与删除视图4NO:1数据库中数据的存储形式5数据库中数据的存储形式表的行构成数据页，页集合构成堆每个表的数据存储在8KB的数据页的集合中，这些数据页的集合称为区域（extent）数据行不按特定的顺序存放，数据页也没特定的顺序数据页不在一个链表中连接当数据行插入一页，而该页满时，数据页被分割6数据库中数据的存储形式表扫描：扫描表的所有数据页从表的起点开始，逐页扫描表的全部行，取出满足查询条件的行使用索引遍历索引树结构，查找查询请求的行，只取出满足查询条件的行7NO:2创建索引的必要性与索引类型8创建索引的必要性与索引类型-为什么创建索引使用索引指向数据页上特定信息的位置，不必扫描表的全部数据。要求考虑：索引通常加速连接表和执行排序或组合操作的查询如果在创建索引时定义唯一性，索引强制数据行的唯一性索引按上升排序顺序创建和维护索引最好在高度选择性的列上创建9创建索引的必要性与索引类型-为什么不创建索引索引要耗用磁盘空间，并引起系统开销和增加维护的费用。要求考虑：在修改一个索引列的数据时，SQL更新相关的索引。维护索引要求空间和资源，所以不要创建不经常使用的索引小表上的索引没什么好处10创建索引的必要性与索引类型-使用场合可以索引的列：主键和外键频繁在范围中搜索的列频繁按排序顺序访问的列不可以索引的列：在查询中很少引用的列含有很少唯一值的列以bit,image,text数据类型定义的列11创建索引的必要性与索引类型-索引类型唯一索引：要求所有数据行中任意两行中的索引列不能存在重复值非唯一索引：不存在上述限制的索引簇索引：行的物理顺序与索引顺序完全相同非簇索引：与上述相反单列索引：对表中单个列进行索引复合索引：对表中两列或多列进行复合索引全文索引12NO:3簇索引与非簇索引13簇索引与非簇索引-簇索引行的物理位置与索引顺序一致一个表只能创建一个簇索引由于创建时要改变物理顺序，所以应该在创建非簇索引之前创建优点：比非簇索引检索快，适用于检索连续键值14簇索引与非簇索引-簇索引工作过程15簇索引与非簇索引-非簇索引非簇索引代表行的逻辑顺序非簇索引的查找比簇索引慢，尤其是在查找一个范围时优点：维护一个非簇索引的成本较低16簇索引与非簇索引-非簇索引的工作过程17簇索引与非簇索引-创建索引语法：CREATE[UNIQUE][clustered|nonclustered]indexindex_nameONtable(column[ASC|DESC][,...n])[WITH[pad_index][,fillfactor=fillfactor][,ignore_dup_key][,dropexisting][,statistics_norecompute][,sorted_data_reopg]][onfilegroup]18簇索引与非簇索引-说明[UNIQUE][clustered|nonclustered]：索引的类型，默认为非簇索引table(column[ASC|DESC][,...n])：说明索引的列，最多为16列（用于复合索引）fillfactor=fillfactor：1~100说明索引页面页级填充率（填充因~子），默认为0pad_index：ON|OFF指出索引填充，默认为OFFignore_dup_key:ON|OFF指定重复值是否能够被插入或不能dropexisting：ON|OFF说明先删除表的簇索引再重新构造statistics_norecompute：禁止SQL根据需要自动重新构造过期的索引信息sorted_data_reopg：消除创建簇索引时执行的排序19簇索引与非簇索引示例1:为STUDENT_TABLE表的PERSON_ID字段创建一个索引StudentIndex_1createindexStudentIndex_1onSTUDENT_TABLE(PERSON_ID)[注:PERSON_ID为新增列]20簇索引与非簇索引示例2:为STUDENT_TABLE表创建一个唯一簇索引StudentIndex_2createUNIQUEclusteredindexStudentIndex_2onSTUDENT_TABLE(STUDENT_NUM)21簇索引与非簇索引-管理索引检查表的索引信息Sp_helpindextable_nameSp_helptable_name注意：在创建之前，最好也检查一下表的索引信息，在一个列上创建多个索引是没意义的22簇索引与非簇索引-删除索引语法：dropindextable_name.index_name例：dropindexauthors.au_id_ind说明：可以删除createindex创建的索引，但不能删除为primarykey和unique约束创建的索引，这些索引只能通过删除约束或相应的表来实现23NO:4全文索引24全文索引能够对数据库中的字符类型列（如varchar,text等数据类型）进行索引，并通过索引实现全文搜索查询25全文索引-常规索引和全文索引比较常规索引全文索引使用createindex或约束定义创建，通过删除约束或dropindex删除使用全文索引存储过程创建和删除当插入、修改或删除数据时,SQL自动更新常规索引内容只能通过任务调度或执行存储过程来填充全文索引每个表可以创建多个常规索引每个表只有一个全文索引索引不能分组同个数据库的多个全文索引可以组织为一个全文目录常规索引存储在数据库文件组中全文索引存储在文件系统中26全文索引-创建全文索引的6个步骤启动数据库的全文处理功能（sp_fulltext_database）建立全文目录（createfulltextcatalognameasdefault）创建一个唯一索引（createuniqueindex…）为表创建全文索引（sp_fulltext_table）填充全文目录（sp_fulltext_catalog）27全文索引-激活全文搜索UseAdventureWorksgosp_fulltext_databaseenablego28全文索引-创建默认的全文目录在AdventureWorks数据库中创建全文目录createfulltextcatalogcat1asdefault29全文索引-创建全文索引在Address表上创建全文索引createfulltextindexonPerson.Address(AddressLine1)keyindexAK_Address_rowguid30全文索引-使用全文搜索搜索数据全文索引创建完成之后，可用使用全文搜索查询表Freetext:搜索列中给出的任何内容的变体语法：selectcolumnfromtablewherefreetext(column,’values’)Contains:搜索特定的内容语法：selectcolumnfromtablewherecontains(column,’valuenearvalue2’)31全文索引-使用contains搜索数据查找包含’santa’和‘drive’的地址select*fromPerson.Addresswherecontains(AddressLine1,’santaneardrive’)32总结数据库中数据的存储形式创建索引的必要性与索引类型簇索引与非簇索引全文索引33作业及练习34