sql索引

数据库应用软件计算机科学与工程学院网信系魏永山第6章索引创建索引的原因及应该考虑的问题索引类型创建索引索引的查看和删除全文索引6.1创建索引的原因及应该考虑的问题607080828590953520601085505525829570409080下面两个表格，如果查找82，可以采用哪些查找方法？哪个查找效率高？102025354050556.1创建索引的原因及应该考虑的问题索引是对数据库表中一个或多个列的值进行排序的结构，用于快速访问数据库表中的特定数据。索引是由索引页面组成，每个索引页面中的行都包含逻辑指针，通过该指针可以直接检索到数据，从而加快物理数据的索引。索引有下述优点1.提高查询速度2.提高连接、ORDERBY和GROUPBY执行的速度3.查询优化器依靠索引起作用4.强制实施行的唯一性6.1.1创建索引的原因需要创建索引的情况：经常查询某列中的数据时，才需要在表上创建包含该列的索引。索引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在多数情况下，索引所带来的检索速度的优势超过他的不足之处。然而，如果应用程序频繁地更新数据或磁盘空间有限，最好限制索引的数量。6.1.1创建索引的原因索引对下列查询有帮助精确匹配查询：搜索符合特定搜索关键字值的行范围查询：搜索其搜索关键字的值为范围值的行连接查询分组查询搜索重复值，以实施PRIMAYKEY和UNIQUE约束搜索已定义了FOREIGNKEY约束的两个表之间匹配的行6.1.2创建索引应该考虑的问题6.1.2创建索引应该考虑的问题创建索引应考虑的主要因素：一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有索引都需进行适当的调整。另一方面，对于不需要修改数据的查询，大量索引有助于提高性能，因为SQLServer有更多的索引可供选择，以便确定以最快速度访问数据的最佳方法。覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。因为检索数据时只需引用表的索引页，不必引用数据页。对小型表进行索引可能不会产生优化效果，因为SQLServer在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。可以在视图和计算列上指定索引。6.1.2创建索引应该考虑的问题下面的情况则不考虑建立索引：从来不或者很少在查询中引用的列值很少的列，例如性别（男或女）记录数很少的表6.1.2创建索引应该考虑的问题6.2索引类型聚集索引：数据库表中记录的物理顺序与索引顺序相同，一个表只有一个。非聚集索引：数据库表中记录的物理顺序与索引顺序可以不同。一个表可有多个。唯一索引：索引列不包含重复的值组合索引：索引中包含多列6.2.1B-Tree索引结构B-Tree的顶端结点称为根结点，底层结点称为叶结点，在根结点和叶结点之间的称为中间结点。B-Tree数据结构从根结点开始，以左右平衡的方式排列数据，中间可以根据需要分成许多层。图6.1B-Tree的数据结构根结点…………………………中间结点叶结点6.2.1B-Tree索引结构如：6.2.1B-Tree索引结构308020102550603540557090828595聚集索引和非聚集索引都使用B-Tree结构建立。聚集索引和非聚集索引都包括索引页和数据页，索引页用来放索引键值和指到下一层的指针，数据页用来存放记录。6.2.2聚集索引和非聚集索引6.2.2聚集索引和非聚集索引1.聚集索引：表中存储的数据按照索引的顺序存储，即表中记录的物理顺序与索引顺序相同。聚集索引的一个数据页包含一笔记录，再由多个数据页生成一个中间结点的索引页，接着由数个中间节点的索引页合成更上层的索引页，组合后生成最顶层的根节点的索引页。聚集索引示例AkhtaGanio…MartinSmith…Akhta2334…Barr5678…Con2534…Funk1334…Funk1334…………Ganio7678…Hall8078…Jones2324…Jones5978…Jones2634…………Martin5334…Martin6778…Ota5878…Phua7878…Rudd6078…………Smith1434…Smith5778…Smith7978…White2234…White1634…………Akhta…Martin索引页数据页Page140rootPage141Page145Page100Page110Page120Page130聚集索引的特点：聚集索引对经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理顺序上也是相邻的。聚集索引的检索效率比非聚集索引高，但对数据新增、修改和删除的影响比较大。一个表只有一个聚集索引。6.2.2聚集索引和非聚集索引如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上进行聚集索引，避免每次查询该列时都进行排序，从而节省检索时间。如果表中没有创建其他的聚集索引，则在表的主键列上自动创建聚集索引。如果表中未创建聚集索引，则按输入顺序存储。6.2.2聚集索引和非聚集索引可考虑将聚集索引用于下面几种情况：包含大量非重复值的列。使用下列运算符返回一个范围值的查询：BETWEEN、、=、和=。被连续访问的列。返回大型结果集的查询。经常被使用连接或GROUPBY子句的查询访问的列。OLTP（联机事务处理）类型的应用程序。这些应用程序要求进行非常快的单行查询。6.2.2聚集索引和非聚集索引2.非聚集索引：对表中的数据进行逻辑排序，不影响表中的数据存储顺序，即数据存储在一个地方，索引存储在另一个地方。非聚集索引与聚集索引一样有B-树结构，但有两个差别：•数据行不按非聚集索引键的顺序排序和存储。•非聚集索引的叶层不包含数据页，而包含索引行。非聚集索引的数据结构。图6.36.2.2聚集索引和非聚集索引非聚集索引示例AkhtaGanio…MartinSmith…Akhta4:706:01Barr4:705:03Con4:704:01Funk4:706:02Funk4:704:02……Ganio4:709:01Hall4:709:04Jones4:809:02Jones4:708:03Jones4:703:03……Martin4:708:01Martin4:706:04Ota4:707:04Phua4:708:02Rudd4:705:01……Smith4:706:03Smith4:708:04Smith4:707:01White4:704:04White4:705:02……Akhta…Martin索引页数据页Page140rootPage141Page145Page100Page110Page120Page13001Rudd……………………………………04Martin……………………04Ota…………02Phua………………Page705Page706Page707Page708非聚集索引的特点：索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。非聚集索引检索效率比聚集索引低，对数据新增、修改和删除的影响较少。一个表可有多个非聚集索引。6.2.2聚集索引和非聚集索引可考虑将非聚集索引用于下面的情况：包含大量非重复值的列。不返回大型结果集的查询。返回精确匹配的查询的搜索条件（WHERE子句）中经常使用的列。在特定的查询中覆盖一个表中的所有列。经常需要连接和分组的决策支持系统应用程序。6.2.2聚集索引和非聚集索引唯一索引：唯一索引表示表中任何两条记录的索引值都不相同。它可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。在创建主键和唯一约束的列上会自动创建唯一性索引。组合索引：索引中包含多个字段。6.2.3唯一索引和组合索引6.3创建索引SQLServer提供了两种方法来创建索引：直接创建索引使用CREATEINDEX语句或者SQLServerManagementStudio来直接创建索引。间接创建索引使用CREATETABLE语句创建表时，或者使用ALTERTABLE修改表时，如果指定PRIMARYKEY约束或者UNIQUE约束，则SQLServer自动为这些约束创建索引。在创建索引时，需要指定索引的特征：聚集还是非聚集唯一还是不唯一单列还是多列索引中的列顺序为升序还是降序覆盖还是非覆盖6.3创建索引例：使用SQLServer管理控制器，在school数据库中student表的sclass列上创建一个升序的非聚集索引Q_sclass。使用SQLServer管理控制器，在school数据库中score表的cno、degree列上创建一个先按cno升序排，cno一样再按degree降序排的非聚集索引Q_cd。6.3.1通过SQLServerManagement创建索引具有包含性列的索引在SQLServer2005中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。非键列具有下列优点：它们可以是不允许作为索引键列的数据类型。在计算索引键列数或索引键大小时，数据库引擎不考虑它们。当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘I/O操作。当索引包含查询引用的所有列时，称为“覆盖查询”。键列存储在索引的所有级别中，而非键列仅存储在叶级别中。6.3.1通过SQLServerManagement创建索引利用SQLServer管理平台创建索引：展开指定的【服务器】和【数据库】，选择要创建索引的表，展开该表，选择【索引】选项（如图１所示），右键单击【索引】，从弹出的快捷菜单中选择【新建索引】，就会出现【新建索引】对话框（如图2所示）。（图１）（图2）点击【添加】按钮，会弹出【从“创建索引表”中选择列】，可选择用于创建索引的字段，如图3所示『从“dbo.s”中选择列』。打开创建索引对话框的【选项】页框，在此还可以设定索引的属性，如图4所示。（图3）（图4）CREATEINDEX语句创建索引的语法格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,…n])[WITHindex_option[,…n]][ONfilegroup]其中：index_option定义为{PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB}6.3.2使用SQL语言创建索引FILLFACTOR：指定各索引叶级的填满程度。PAD_INDEX：指定索引中间级中每个页上保持开放的空间。IGNORE_DUP_KEY：控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。DROP_EXISTING：指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。STATISTICS_NORECOMPUTE：指定过期的索引统计不会自动重新计算。若要恢复自动更新统计，可执行没有NORECOMPUTE子句的UPDATESTATISTICSSORT_IN_TEMPDB：指定生成索引的中间排序结果将存储在tempdb数据库中。6.3