2022年北大青鸟MYSQL基础教程2.ppt

Version 3.0第六章第六章索引和全文索引索引和全文索引回顾回顾 子查询子查询 联合查询联合查询 DISTINCT 子句子句 COMPUTE BY SELECT INTO 2 2目标目标索引的含义索引的含义创建和删除索引创建和删除索引全文索引全文索引全文检索全文检索3 3索引概念索引概念索引是对数据库表中一个或多个列的值进行索引是对数据库表中一个或多个列的值进行排序的结构排序的结构索引提供指针以指向存储在表中指定列的数索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指据值，然后根据指定的排序次序排列这些指针针 4 4索引优点缺点索引优点缺点优点优点加快访问速度加快访问速度加强行的唯一性加强行的唯一性缺点缺点带索引的表在数据库中需要更多的存储空间。带索引的表在数据库中需要更多的存储空间。操纵数据的命令需要更长的处理时间，因为操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新。它们需要对索引进行更新。5 5适合适合创建索引创建索引情况：情况：该列该列频繁频繁用于进行用于进行搜索。搜索。该列用于对该列用于对数据进行排序数据进行排序。不适合创建索引情况：不适合创建索引情况：列列中中仅包含仅包含几个不同的值几个不同的值。表表中中仅包含几行。仅包含几行。创建索引的指导原则创建索引的指导原则6 6创建索引创建索引1-2 CREATE INDEX 语句用于为给定的表创建语句用于为给定的表创建索引。索引。该语句通过修改表的物理顺序，或者向该语句通过修改表的物理顺序，或者向查询优化器提供表的一个逻辑顺序以提高查询优化器提供表的一个逻辑顺序以提高查询效率。查询效率。只有表的所有者能为表创建索引。只有表的所有者能为表创建索引。7 7CREATE INDEX 语句的语法为语句的语法为：创建索引创建索引2-2CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_name(column_name,column_name)WITH PAD_INDEX,FILLFACTOR=x,DROP_EXISTING 8 8索引分类索引分类表中各行的物理顺序与键值的逻辑（索引）表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引顺序相同。表只能包含一个聚集索引 非聚集索引指定表的逻辑顺序。非聚集索引指定表的逻辑顺序。9 9Clustered IndexPage 140-RootPage 100 Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.76788078243459782634.SELECT lastname,firstnameFROM memberWHERE lastname=Ota使用聚集索引进行查询使用聚集索引进行查询聚集索引聚集索引Page 140 根级页 100 页 120 页 130 页 141页 145AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmith页110 GanioHallJonesJonesJones.76788078243459782634.MartinMartinidindid=1rootsysindexes1010查询示例使用非聚集索引进查询示例使用非聚集索引进行查询行查询 Non-LeafLevelPage 12-RootPage 37Page 28Leaf Level(Key Value)Page 41Page 51 Page 61 Page 71 Akhtar.MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith.SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio.GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Page 706 010203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.GanioJonesHall.MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01NonClusteredIndexFile ID#40404.MateyMateyidindid=2rootsysindexesMartinMartinMartinMartinMateyMatey 4:706:044:706:040202.PhuaPhuaPhuaPhua 4:708:024:708:020101.RuddRuddRuddRudd 4:705:014:705:010202.OtaOtaOtaOta4:707:024:707:02SELECT lastname,firstnameFROM memberWHERE lastnameBETWEEN Masters AND Rudd1111填充因子填充因子1-3创建聚集索引后，表中数据会按照索引列的值的创建聚集索引后，表中数据会按照索引列的值的顺序存储在数据页中。顺序存储在数据页中。表中添加了新记录或索引列的值发生修改时，表中添加了新记录或索引列的值发生修改时，SQL Server 需要重新组织数据存储模式，以便为需要重新组织数据存储模式，以便为新记录腾出空间并维持数据的有序存储。新记录腾出空间并维持数据的有序存储。向已满的索引页中添加新行时，向已满的索引页中添加新行时，SQL Server 会将会将大约一半的行移到新的一页上，以便为新行腾出大约一半的行移到新的一页上，以便为新行腾出空间，从而导致页拆分。空间，从而导致页拆分。1212填充因子填充因子3-3如果无须再修改数据，可以指定填充因子如果无须再修改数据，可以指定填充因子100，这样将完全占满索引页，因而所占用，这样将完全占满索引页，因而所占用的存储空间最少。的存储空间最少。如果需要频繁地修改表中的数据，则应指如果需要频繁地修改表中的数据，则应指定一个较低的填充因子，以便在数据页中保定一个较低的填充因子，以便在数据页中保留更多的空白空间留更多的空白空间 。1414聚集索引决定数据的物理存储顺序。聚集索引决定数据的物理存储顺序。一个表只能有一个聚集索引。一个表只能有一个聚集索引。索引可以包含多个数据列。索引可以包含多个数据列。示例示例：创建聚集索引创建聚集索引CREATE CLUSTERED INDEX CLINDX_titleid ON roysched (title_id)1515创建聚集索引的指导原则创建聚集索引的指导原则选择唯一值比例高的数据列，一般应选择选择唯一值比例高的数据列，一般应选择主键列主键列先创建聚集索引，再创建非聚集索引先创建聚集索引，再创建非聚集索引在创建聚集索引时使用在创建聚集索引时使用FILLFACTOR选项选项以确保在插入新行时有足够的可用空间。以确保在插入新行时有足够的可用空间。1616非聚集索引指定表的逻辑顺序。数据存储在非聚集索引指定表的逻辑顺序。数据存储在一个一个位置位置，索引存储在另一个，索引存储在另一个位置位置，索引中，索引中包含包含指向指向 数据存储位置的指针。数据存储位置的指针。一个表可以有一个表可以有多多个个（最多（最多249个）个）非聚集索非聚集索引。引。示例示例：创建非聚集索引创建非聚集索引CREATE NONCLUSTERED INDEX NCLINDX_ordnum ON sales(ord_num)1717除非绝对必要除非绝对必要，否则请不要添加非聚集否则请不要添加非聚集索引，这是因为非聚集索引需要额外的索引，这是因为非聚集索引需要额外的维护开销维护开销在频繁访问的列上建立索引在频繁访问的列上建立索引默认情况下，默认情况下，CREATEINDEX语句创建的语句创建的是非聚集索引是非聚集索引在相异值多的数据列上创建非聚集索引，在相异值多的数据列上创建非聚集索引，如名和姓的组合项如名和姓的组合项创建非聚集索引的指导原则创建非聚集索引的指导原则1818聚集索引和非聚集索引可以创建聚集索引和非聚集索引可以创建为为唯一索唯一索引或组合索引。引或组合索引。唯一索引不允许索引列中存在重复唯一索引不允许索引列中存在重复的的值。值。组合索引允许在创建索引时使用两组合索引允许在创建索引时使用两列或更列或更多多的列。的列。索引的特性索引的特性1919由于每一行都包含一个唯一的值，唯一索引由于每一行都包含一个唯一的值，唯一索引可以确保实体完整性。可以确保实体完整性。键列中的键列中的NULL 值也算作唯一值。值也算作唯一值。不能在包含重复数据的列上创建唯一索引。不能在包含重复数据的列上创建唯一索引。在创建唯一索引前必须更新或删除重复在创建唯一索引前必须更新或删除重复的的数数据。据。创建唯一索引的指导原则创建唯一索引的指导原则2020创建组合索引的指导原则创建组合索引的指导原则当需要频繁地将两列或多列作为一个整当需要频繁地将两列或多列作为一个整体进行体进行 搜索时，可以创建组合索引。搜索时，可以创建组合索引。创建组合索引时先列出唯一性最好的列。创建组合索引时先列出唯一性最好的列。组合索引中列的顺序和数量会影响查询组合索引中列的顺序和数量会影响查询的性能。的性能。2121创创建建索索引引后后，可可能能需需要要查查找找有有关关索索引引的的信信息息，例例如如查查看看在在某某个个表表上上创创建建的的索索引引的的列列表表或或者者表表中中的的索引列。索引列。系系统统存存储储过过程程sp_helpindex报报告告有有关关表表的的索索引引的的信信息。息。语法语法：查看索引查看索引sp_helpindex 2222SQL Server使用查询优化器选择执行查询使用查询优化器选择执行查询的最的最佳佳方式方式 ，包括选择要使用的索引，查询优化器包括选择要使用的索引，查询优化器以以页页I/O为标准，尝试选出成本最低的方法为标准，尝试选出成本最低的方法 。SQL Server中可以强制查询优化器选择某个特定中可以强制查询优化器选择某个特定的索引。的索引。制查询优化器选择某个特定的索引。制查询优化器选择某个特定的索引。语法语法(INDEX=index_name)SELECT*FROM sales(INDEX=nclindx_ordnum)WHERE ord_num=P3087a 在查询中强制使用索引在查询中强制使用索引示例示例2323全文搜索全文搜索SQL Server使用全文索引执行全文搜索。使用全文索引执行全文搜索。可以在无结构文本上建立索引，以便能够在可以在无结构文本上建立索引，以便能够在文本文本 中搜索特定的条目。中搜索特定的条目。该功能是通过该功能是通过Microsoft搜索服务提供的。使搜索服务提供的。使用用Microsoft搜索服务，可以用语言搜索条件实搜索服务，可以用语言搜索条件实现复杂的搜索。现复杂的搜索。语言搜索可用于搜索单词或短语、相邻的几语言搜索可用于搜索单词或短语、相邻的几个目标词和不同形式的词语。个目标词和不同形式的词语。2525全文索引跟踪表中的重要词语。全文索引跟踪表中的重要词语。该索引用于包含索引词的搜索和高级搜索选项，该索引用于包含索引词的搜索和高级搜索选项，如短语搜索。如短语搜索。全文索引需要一个包含唯一键值的数据列。全文索引需要一个包含唯一键值的数据列。添加、添加、修改修改或删除表中数据时，必须手动更新或删除表中数据时，必须手动更新全文索引。全文索引。全文索引全文索引2626所有的全文索引都存储在全文目录中。所有的全文索引都存储在全文目录中。默默认认情情况况下下，一一个个数数据据库库的的所所有有全全文文索索引引都都存存放放在一个全文目录中。在一个全文目录中。如如果果索索引引非非常常大大，系系统统管管理理员员也也可可以以将将目目录录分分割割成多个目录。成多个目录。全文目录全文目录2727搜索包含搜索包含基于基于字符字符的的数据类型的列数据类型的列，查找：，查找：单个词语或短语的匹配项单个词语或短语的匹配项特定距离特定距离内内的词语或的词语或加权匹配项加权匹配项 搜索不区分大小写。搜索不区分大小写。示例示例SELECT title FROM titles WHERE CONTAINS(title,computer or cooking or silicon)CONTAINS 谓词谓词2929FREETEXT关键关键字在字在列列中搜索中搜索与搜索条件中的词语与搜索条件中的词语意意义相匹配但形式却并不完全匹配义相匹配但形式却并不完全匹配的值的值搜索字符串被分割成多个搜索搜索字符串被分割成多个搜索短词短词，然后再查找，然后再查找 匹配项匹配项搜索字符串可以是一组单词或短语，甚至可以是一搜索字符串可以是一组单词或短语，甚至可以是一个完整的句子个完整的句子示例示例 SELECT title FROM titles WHERE FREETEXT(title,computer cooking and silicon)FREETEXT 谓词谓词3030索引的概念索引的概念索引的优缺点索引的优缺点聚集索引聚集索引非聚集索引非聚集索引唯一索引唯一索引组合索引组合索引全文索引全文索引全文搜索全文搜索总结总结3131