第8章-索引-完整性-讲解剖课件

第8章索引与数据完整性数据库中的索引与书籍中的索引类似。在一本书中，利用索引（目录）可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。当创建数据库并优化其性能时，应该为数据查询所使用的表列创建索引。8.1索引8.1.1索引的概念

简单地说，索引是表中某列（或某些列）的值以某种顺序（升/降）排列后与其行记录的存储位置的指向指针之间的对照表。

记录号32451姓名升序值李刘王张赵记录号24135平均分升序值6080858990“平均分”索引“姓名”索引学生表记录号学号姓名性别专业平均分105001赵男自动化85205002刘女自动化60305003李男管理89405004王女自动化80505005张女管理90在有序的较小的范围内（索引上）查找数据，可快速定位要找的数据记录上。

数据按照输入的时间顺序堆放到数据页上索引页SQLServer一个表数据页索引是一个树状数据结构，索引页上的指针指向数据页。2．创建索引的优点大大加快数据的检索速度，是创建索引的最主要的原因。创建唯一性索引，保证数据库表中每一行数据的唯一性。加速表和表之间的连接，特别是在实现数据的参考完整性方面。在使用GROUPBY分组子句和ORDERBY排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。增加索引也有缺点：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就增加了系统的负担，降低了数据的维护速度。权衡:要从UPDATE与SELECT两方面考虑,查询需求大于更新需求时,要索引,反之不要索引.

查询频度高,数据量大的表考虑建索引

考虑建索引的列：作为主键的列；经常需要搜索的列；经常用于连接的列；WHERE子句中经常使用的列；经常需要排序的列；经常需要根据范围进行搜索的列。3．选择创建索引的参考原则不考虑建索引的列一个表不宜建立太多的索引,因为增删改牵涉到调整索引，不适合建立索引的列：在查询中很少使用或者参考的列；有大量重复值的列。如只有1和0，此时直接进行表扫描更有效；定义为TEXT、NTEXT、IMAGE或BIT等数据类型的列；当列的增删改操作远远多于检索操作时。索引的使用由系统自动选择,自动维护,不用用户操心,但有加快查询速度的索引才会被使用,否则,系统不予采纳,因此,不利的索引只会增加系统的维护成本.8.1.2索引的类型单列索引:只有一列进行索引复合索引:由多列组成的索引唯一(UNIQUE)索引:确保索引的列值没有重复值聚集索引(CLUSTERED):也叫簇索引.会改变表中记录的物理存储顺序,表的记录不再按输入记录的先后排列,而是按索引的顺序存放.非聚集索引(NONCLUSTERED):表中记录的物理顺序并不改变,系统将使用索引页来创建一个索引结构，用以表示行的逻辑顺序。关于SQLServer的数据存储堆127号扩展盘区128号扩展盘区129号扩展盘区010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......0102030405ConFunkWhiteDurkinLang………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......0102030405DunnRandallOtaSlichterLaBrie………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……GraffBaconKoch......………......130号扩展盘区01020304…SeattleParisTokyoAtlanta...………......堆数据页是无序的堆集存放扩展盘区

（8个连续页，每页8KB，每页可放置1~n行数据。）索引结构1.不使用索引时

在堆中查找行堆127号扩展盘区idindid=0FirstIAMsysindexesIAM128号扩展盘区129号扩展盘区010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......0102030405ConFunkWhiteDurkinLang………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......0102030405DunnRandallOtaSlichterLaBrie………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……GraffBaconKoch......………......130号扩展盘区01020304…SeattleParisTokyoAtlanta...………......…1271128112901301…扩展盘区位图SQLServer查询sysindexes表，找到IAM页，然后顺序查找表的所有页未索引值为0索引分配映射表(IAM)页映射数据库文件中由堆集或索引使用的扩展盘区。未索引，IAM指向有数据的扩展盘区

非叶级Page12-RootPage37Page28叶级

（键值）Page41Page51Page61Page71Akhtar...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:03堆Page707Page808Page70901020304..................AkhtarFunkSmithMatey...Page704Page705Page706010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01非聚集索引FileID#42.使用非聚集索引，在堆中查找行idindid=2rootsysindexesSELECTlastname,firstnameFROMmemberWHERElastnameBETWEEN'Masters'AND'Rudd'

非叶级第12页–根第37页第28页叶级

（键值）第41页第51页第61页第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:03堆第707页第708页第709页01020304..................AkhtarFunkSmithMatey...第704页第705页第706页010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01非聚集索引FileID#4MartinMartin04...MateyMatey4:706:0402...PhuaPhua4:708:0201...RuddRudd4:705:0102...OtaOta4:707:02非聚集索引值为2使用非聚集索引，在堆中查找行非聚集索引索引里的指针指出特定项在表中的存储位置SQLServer的索引组织成B树的结构索引中的每页称为一个索引节点。叶节点包含行定位器，指针指向持有键值的数据行。每个指针（即行标识符）由文件标识符、页码和页上的行数构成3.使用聚集索引，

在聚集索引页中查找行idindid=1rootsysindexes聚集索引值为1在聚集索引中查找行

聚集索引就好像电话簿一样，同姓的人聚集在电话簿上的同一块地方聚集索引也是采用B树结构，和非聚集索引类似聚集索引B树结构的叶节点就是索引的数据页聚集索引的数据行是排序的，按照它们的聚集索引键顺序存储聚集索引的键值应尽量小，使得一个索引页能装入更多的索引行，从而提高性能LastName上的聚集索引FirstName上的非聚集索引非叶级叶级

（聚集键值）AaronDeanna…Aaron...JoseJoseNina…DeannaDonDougDaumHallHampton……AaronAdamAmieConBarrBaldwin……JoseJudyMikeLugoKaethlerNash……BarrAdamCoxDaumArletteDeanna………………KimKobaraLaBrieShaneLindaRyan………………NagataNashNixonSusanneMikeToby………………BarrKimNagataO’Melia4.使用基于聚集索引的非聚集索引，在聚集索引页中查找行idindid=2rootsysindexesSELECTlastname,firstname,phoneFROMmemberWHEREfirstname='Mike'姓氏上的

聚集索引名字上的

非聚集索引非叶级叶级

（聚集键值）AaronDeanna…Aaron...JoseJoseNina…DeannaDonDougDaumHallHampton……AaronAdamAmieConBarrBaldwin……JoseJudyMikeLugoKaethlerNash……BarrAdamCoxDaumArletteDeanna………………KimKobaraLaBrieShaneLindaRyan………………NagataNashNixonSusanneMikeToby………………BarrKimNagataO’MeliaMikeNashNagataNashMike…使用基于聚集索引的非聚集索引查找行当在已经有聚集索引的表上建立非聚集索引的时候，每个非聚集索引的行指示器包含了行的聚集索引键值当在同一张表上使用聚集索引和非聚集索引的时候，两类索引的B树结构都要被遍历，这产生了额外的I/O操作由于聚集索引的键值通常大于堆使用的八字节的行标识符，建立在聚集索引上的非聚集索引通常比建立在堆上的要大聚集索引与非聚集索引的对比聚集索引存取速度快，适合于主码，一个表只能建立一个聚集索引，即按一种物理顺序存放非聚集占用空间占用多，可以建立多个非聚集索引.

注意:最好在创建表时创建聚集索引,以免引起数据的移动重组,最好在作好了聚集索引后再做非聚集索引,以免重建非聚集索引.8.1.3索引的创建与管理1.利用管理平台创建索引:

展开表对象，展开索引新建索引2.使用Transact-SQL语句中的CREATEINDEX命令创建索引。注意：创建主键约束或唯一约束时,系统会自动创建唯一索引.如果表中已有数据,再创建索引时,系统将检查合法性,如不合法,则创建索引失败.SQLServer创建索引的方法创建索引的CREATEINDEX命令CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX

索引名ON

{表名称|视图名}(列名[ASC|DESC][,...n])[WITH<索引选项>[,...n][ON文件组]其中：

ASC|DESC：用于指定具体某个索引列的升序或降序排序方向<索引选项>可为：{PAD_INDEX|FILLFACTOR|DROP_EXISTING|IGNORE_DUP_KEY}FILLFACTOR(填充因子):设置合适的填充因子将获得更好的性能，因子在0～100。如果初始数据只占最终数据的20%,则可设置填充因子为20%,意味着预留空间,以免以后装不下导致要拆分页.影响性能.PAD_INDEX:指定中间页节点保持开放的空间。与FILLFACTOR配合。IGNORE_DUP_KEY,在创建了唯一索引后，将忽略（接受但回滚操作/不接受即拒绝操作）插入的重复值。DROP_EXISTING,重建已存在的索引聚集索引与非聚集索引的效果显示执行select*intoxs_kc1fromxs_kc在xs_kc1表上,创建学号的聚集索引，打开表显示顺序删除学号的聚集索引。在xs_kc1表上创建课程号的聚集索引，打开表显示顺序8.1.2索引的创建【例1】为KC表的课程名列创建索引。/*使用简单索引*/USECJGLIFEXISTS(SELECTnameFROMsysindexesWHEREname='kc_name_ind')DROPINDEXKC.kc_name_indGOCREATEINDEXkc_name_indONKC(课程名)GO8.1.2索引的创建【例3】根据XS_KC表的学号列和课程号列创建复合索引。/*使用简单组合索引*/USECJGLCREATEINDEXxs_kc_indONXS_KC(学号,课程号)【例2】根据KC表的课程号列创建唯一聚集索引/*使用唯一聚集索引*/USECJGLCREATEUNIQUECLUSTEREDINDEXkc_id_indONKC(课程号)--指定CLUSTERED，将对磁盘上的数据进行物理排序。【例5】根据学号列创建唯一聚集索引。如果输入了重复的键，将忽略该INSERT或UPDATE语句。/*将XS表复制一部分数据到XS1表*/SELECT*INTOXS1FROMXS/*未使用IGNORE_DUP_KEY*/CREATEUNIQUECLUSTEREDINDEXxs_idONXS1(学号)insertxs1(学号,姓名)values('082202','章')反应：不能在具有唯一索引'xs_ind'的对象'dbo.xs1'中插入重复键的行。语句已终止/*使用IGNORE_DUP_KEY*/CREATEUNIQUECLUSTEREDINDEXxs_idONXS1(学号) WITHIGNORE_DUP_KEYinsertxs1(学号,姓名)values('082202','章')反应：已忽略重复的键。

(0行受影响)【例6】为成绩表XS_KC创建“学号+课号”的唯一聚集索引myindex1，具有50％的填充因子,指定中间页节点保持开放的空间,并重建已存在的索引SELECT*INTOXS1_KCFROMXS_KCCREATEUNIQUECLUSTEREDINDEXmyindex1ONXS1_KC(学号,课程号)WITHPAD_INDEX,FILLFACTOR=50CREATEUNIQUECLUSTEREDINDEX

myindex1

ONXS1_KC(学号,课程号)WITHPAD_INDEX,FILLFACTOR=50,

DROP_EXISTING删除索引选择索引右键删除命令:DROPINDEX表名.<索引名>[,…n]【例8.13】删除CJGL数据库中表XS的一个索引名为st_id_ind的索引。USECJGLIFEXISTS(SELECTnameFROMsysindexesWHEREname='st_id_ind')DROPINDEXXS.st_id_indGO注意：除去聚集索引将导致重建所有非聚集索引。删除视图或表时，自动删除在视图或表上永久性和临时性创建的所有索引。习题

P190二、1，28.2SQLServer的数据完整性

完整性类型:实施途径实体完整性:

主键,唯一(索引/约束),标识列域完整性:

默认,检查约束,外键,数据类型,规则参照完整性:

外键,检查约束,触发器,存储过程用户自定义完整性:约束,规则,触发器,存储过程下面主要介绍约束、默认、规则。第9章介绍触发器,存储过程。使用约束优先于规则,触发器SQLSERVER完整性的实施途径IDENTITYSQLServer的数据完整性约束8.2.1使用约束实施完整性

在SQLServer2000中有6种约束constraint,它们是在建表和修改表时创建的,它与表定义一起存储,一同删除,自动强制实施.空值约束（Null/notnull）主键约束（primarykeyconstraint）唯一性约束（uniqueconstraint）检查约束（checkconstraint）默认约束（defaultconstraint）外部键约束（foreignkeyconstraint）创建约束CREATETABLE是在创建表时创建约束ALTERTABLE是在一个已有的表上创建约束createtable和altertable创建约束createtable成绩表(学号char(6),课程编号char(6),成绩int,constraintpk_成绩primarykey(学号,课程编号)/*设置组合主键*/)altertable成绩表addconstraintpk_成绩primarykey(学号,课程编号)ALTERTABLE命令有关约束部分的语法ALTERTABLE表名{[WITHCHECK|WITHNOCHECK]

ADD<表级约束>[,…n]/*增加约束*/|DROP[CONSTRAINT]约束名}[,…n]

/*删除约束*/|{CHECK|NOCHECK}CONSTRAINT{ALL|约束名[,...n]}/*启用或暂停约束*/}表级约束部分的语法[CONSTRAINT约束名]{[{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED]{(列名,...n])}]|FOREIGNKEY

[(列名[,...n])]

REFERENCES参照表名[(参照列名[,...n])]

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]|DEFAULT

常量表达式

[FOR列名][WITHVALUES]|CHECK(逻辑表达式)

}FOREIGNKEY约束如果子表某列与父表已有PRIMARYKEY约束或UNIQUE约束的列相关联，则可在子表添加FOREIGNKEY约束。FOREIGNKEY约束包含一个[CASCADE|NOACTION]选项，NOACTION是默认值。它们的作用如下：CASCADE表示级联参照完整性，允许将UNIQUE列或者PRIMARYKEY列的值的修改自动传递到引用它的外键上。例如，当定义了XS_KC表的外键学号参照XS表的主键学号时，我们希望在XS表中改变学号值时XS_KC表的学号值也相应改变，就可以使用CASCADE选项。NOACTION表示任何企图删除（ONDELETE）或者更新（ONUPDATE）被外键所引用的主键的操作都将引发一个错误，对数据的改变会被回滚。修改表时施加约束假定有学生表、课程表、成绩表，表中都有下面语句中涉及的列名。(1)altertable成绩表

addconstraintpk_序号

primarykey（序号）

(2)altertable课程表addconstraintuq_课程名

unique（课程名）

/*设置唯一约束*/(3)altertable课程表altercolumn类别nchar(4)notnull/*空值约束，只能通过修改列来设置*/修改表时施加约束(4)altertable学生表withnocheck/*现有数据不强制这个约束*/addConstraintck_联系电话check（联系电话like'027[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'）/*设置检查约束，规定联系电话只能输入027+8位0~9之间的数字*/(5)altertable学生表

addconstraintdf_籍贯

default‘湖北省’

for籍贯注意写法修改表时施加约束(6)Altertable成绩表Addconstraintfk_成绩表_学号

Foreignkey(学号)references学生表(学号)onupdatecascade,constraintfk_成绩表_课号

Foreignkey(课号)references课程表(课号)onupdatecascade主键值修改了，外键值也级联修改默认约束DEFAULT在添加一个默认约束时，可以使用参数WITHVALUES，它表示用默认值填充表中已有行的新列。【例】修改表XS1，为“入学日期”设置默认值约束为系统当前日期。USECJGLALTERTABLExs1ADDCONSTRAINTdate_dfltDEFAULTgetdate()FOR入学日期WITHVALUES/*WITHVALUES用默认值当前日期填充表中已有行的新列*/默认值约束名解释以下语句作用altertable课程表addConstraintck_类别

check(类别in(‘公共基础课’,’选修课’,‘专业基础课’,’专业课’))

默认值和规则8.2.3使用默认实施数据的完整性所有约束是依附于表而存在,是在创建表和修改表时定义的.默认对象DEFAULT则不依附于表而存在,此处的默认不是默认约束,而是单独存储的数据库对象,当用户向绑定有默认值的列进行增删改时,将由默认检查其完整性.可以说,默认对象是将默认约束从表定义中抽出来,将应用面扩大可以将同一个默认用于多个表,更便于管理和使用.使用默认的方法:创建默认对象后必须将其绑定到某个列,不用时解除绑定,并删除.创建:CREATEDEFAULT默认名AS常量表达式绑定:SP_BINDEFAULT‘默认名’

,‘表名.列名’解除绑定:SP_UNBINDEFAULT‘表名.列名’删除:DROPDEFAULT默认名[,…n]默认值对象的定义、使用【例8.4】默认对象的创建与绑定使用举例。CREATEDEFAULTdfo_zymAS'计算机'GOCREATEDEFAULTdfo_cjAS0GO/*将默认对象dfo_zym绑定到xs表的专业名列上*/EXECsp_bindefaultdfo_zym,'xs.专业名'/*将默认对象dfo_cj绑定到xs_kc表的成绩列上*/EXECsp_bindefaultdfo_cj,'xs_kc.成绩'GO解除绑定关系，删除默认值对象【例8.5】默认对象的解除与删除使用举例。USECJGLGOEXECsp_unbindefault'xs.专业名'/*解除xs表的专业名列上默认对象的绑定*/EXECsp_unbindefault'xs_kc.成绩'GODROPDEFAULTdfo_zym，dfo_cj/*删除名为dfo_zym和dfo_cj的默认对象*/GO默认对象练习创建一个默认对象，默认值为’男‘，并绑定到职员表的性别列上创建一个默认对象，默认值为300，并绑定到工资表的津贴列上。默认对象示例Createdefaultdfo_xbas‘男’Createdefaultdfo_jtas300GOSP_bindefaultdfo_xb,‘职员表.性别’SP_bindefaultdfo_jt,’工资表.津贴’

8.2.4规则规则也是类似于默认的单独存储的数据库对象,可以说,规则对象是将检查约束从表定义中抽出来,将应用面扩大,即规则可以应用于多个具有同样要求的表中。更便于管理和使用.当用户向绑定有规则的列进行增删改时,规则将检查其完整性.使用规则的方法:

创建规则对象后，将其绑定到某个列,不用时解除绑定,也可单独的删除.创建:CREATERULE规则名AS条件绑定