MySQL教程（第4版） 课件 第5章 索引与数据完整性

第5章

索引与数据完整性——索

引01索引的分类普通索引（INDEX）唯一性索引（UNIQUE）主键（PRIMARYKEY）全文索引（FULLTEXT）空间索引（SPATIAL）索引的分类1．普通索引（INDEX）这种索引列的内容可以相同。例如，在学生表按照姓名只能创建普通索引，因为学生中可能存在姓名相同的记录。在这类索引中姓名相同的记录就会排在一起。定义子句如下：{INDEX|KEY}[索引名](键,...)按照指定的(键,...)创建索引，定义索引名，方便后面对其引用。例如后面可以删除已经创建的指定索引名。键如下：列名[(长度)][ASC|DESC]索引的分类2．唯一性索引（UNIQUE）这种索引列的值必须是唯一的。例如学号就是为了区分学生的，在学生表中不可能重复出现。定义子句如下，按照标识引用：[CONSTRAINT[标识]]UNIQUE(键,...)3．主键（PRIMARYKEY）主键是一种唯一性索引，它必须指定为PRIMARYKEY。一个表中唯一性的列可以有多个，但只能选择一个作为主键。例如学生信息表，除了学号外，身份证号、电话号码、微信号也是唯一的，但学生管理中，应该采用学号作为主键，因为它是管理学生的最重要的信息，而且使用最频繁。定义子句如下：[CONSTRAINT[标识]]PRIMARYKEY(键,...)索引的分类4．全文索引（FULLTEXT）主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引跟其它索引大不相同，它更像是一个搜索引擎，需要配合MATCHAGAINST操作使用，而不是一般的WHERE语句加LIKE。目前只有char、varchar、text列上可以创建全文索引。定义子句如下：FULLTEXT{INDEX|KEY}[索引名](键,...)在MySQL中，全文索引的类型为FULLTEXT，并且只能在MyISAM存储引擎的表中创建。5．空间索引（SPATIAL）空间索引是对空间数据类型的字段建立的索引。MySQL使用SPATIAL关键字进行扩展，使其能够用于创建空间索引。创建空间索引的列，必须将其声明为NOTNULL。定义子句如下：SPATIAL{INDEX|KEY}[索引名](键,...)02创建和删除索引在建立表时创建索引在已有表中创建索引修改表结构增加索引删除索引创建和删除索引1．在建立表时创建索引CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名 [([列定义],... |[索引定义])]说明：（1）在列定义的时候，可以指定PRIMARYKEY（主键）或者UNIQUE（唯一性键）属性。【例】在创建kc1表结构时指定主键。USExscj;CREATETABLEkc1(

课程号 char(3) NOTNULLPRIMARYKEY,

课程名 varchar(8)NOTNULL,

开课学期 tinyint NOTNULL,

学时 tinyint NOTNULL,

学分 tinyint NOTNULL);创建和删除索引（2）当主键或者唯一性键由多个列组成时，必须在最后一列后面加上子句。【例】在创建cj1表结构列后指定多列主键。USExscj;CREATETABLEcj1(

学号 CHAR(6)NOTNULL,

课程号 CHAR(3)NOTNULL,

成绩 TINYINT(1), PRIMARYKEY(学号,课程号), INDEXcj(成绩));SHOWINDEXFROMcj1;其中，在所有列定义后定义主键PRIMARYKEY(学号,课程号)和索引INDEXcj(成绩)。显示cj1表索引情况如图。创建和删除索引2．在已有表中创建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名 [索引类型]ON表名(键,...) ...键：列名[(长度)][ASC|DESC]UNIQUE|FULLTEXT|SPATIAL：UNIQUE表示创建的是唯一性索引；FULLTEXT表示创建全文索引；SPATIAL表示创建空间索引。索引名：索引名在一个表中必须是唯一的，在创建索引时定义、修改表结构时修改，通过该名删除索引。创建和删除索引【例】在已有xs1表上创建普通索引和唯一性索引。USExscj;CREATETABLExs1SELECT*FROMxs; CREATEUNIQUEINDEXxm_xh4 ONxs1(姓名,学号(4)ASC);SHOWINDEXFROMxs1;显示xs1表索引情况如图。创建和删除索引3．修改表结构增加索引ALTERTABLE表名 ...... |ADD索引定义 |DISABLEKEYS|ENABLEKEYS其中：DISABLEKEYS|ENABLEKEYS，只在MyISAM表中有用，DISABLEKEYS可以让MySQL在更新表时停止更新MyISAM表中的非唯一索引，ENABLEKEYS重新创建丢失的索引，这样可以加快查询速度。【例】在已有xs1表的出生日期列上创建一个非唯一索引。ALTERTABLExs1 ADDINDEXcsrq(出生日期);创建和删除索引4．删除索引当一个索引不再需要时，可以删除。（1）专门删除索引。DROPINDEX索引名ON表名本语句删除各种类型索引，包括普通索引、主键（PRIMARYKEY）、唯一索引（UNIQUE）、全文索引（FULLTEXT）和空间索引（SPATIAL）。【例】删除xs1表上的csrq索引。DROPINDEXcsrqONxs1;（2）修改表结构删除索引。ALTER[IGNORE]TABLE表名 ...... DROPINDEX索引名

【例】删除x1s表上的xm_xh4索引。ALTERTABLExs1 DROPINDEXxm_xh4;SHOWINDEXFROMxs1;03索引建立、分析和使用索引项的选择复合索引的选择索引失效的情况建立索引的原则索引建立、分析和使用1．索引项的选择一般来说，索引项的选择需要考虑一些基本原则：（1）对于学生表（xs），由于学号不可能重复，它也是标识学生的信息，应该作为主键。因为会经常按学号查询学生信息，定义为主键，可以很快找到对应记录。（2）以姓名查询的机会也很多，但姓名可以重名，所以该列只能创建普通索引。（3）以出生日期查询的可能性较小，所以不要建立索引。因为创建索引除了需要额外占用存储资源外，系统需要根据表记录内容的变化更新索引文件内容，降低系统运行效率。（4）虽然（xsk表）家庭地址区分度很好，但其数据类型（json）不适合建立索引。索引建立、分析和使用2．复合索引的选择如果表频繁查询的多列（大多为二列）一起才能保证唯一性，那么就需要多列联合创建索引。创建联合索引需要注意以下几点：（1）查询关注度高和区分度好的列需要排列在前面。例如，xs表如果需要对（姓名，专业，性别）一起创建索引，因为按照姓名查询比较频繁，而且姓名相同的可能性小，所以姓名应放在第一项，虽然性别比专业可能查询更多，但区分度不如专业，所以专业应放第二列。（2）对构成复合索引的列组合进行查询，均可使用索引。例如，按（姓名，专业，性别）创建了索引，如下WHERE的查询条件都可使用该索引：WHERE姓名=?WHERE姓名=?AND专业=?WHERE姓名=?AND性别=?WHERE姓名=?AND专业=?AND性别=?（3）合理创建联合索引，避免冗余。对于a、b、c列集合(a)，(a,b)，(a,b,c)，只要创建了(a,b,c)索引，其他按照(a)和(a,b)创建索引就是冗余的，因为后者已经包含在(a,b,c)索引中。索引建立、分析和使用3．索引失效的情况下列情况下，MySQL不会使用已有的索引：（1）索引列进行数据运算或者函数运算。例如：WHERESUBSTR(学号,3,2)='12'，即使按“学号”创建了索引，但对学号进行函数运算的条件却不能使用该索引。（2）如果LIKE是以％开始就不能使用索引。也就是说，查询索引列只能是全部或者是前面一部分。例如：WHERE专业LIKE'%工程%'

，即使按专业建立了索引，该查询也不能使用索引；而WHERE学号LIKE'20%'则可以使用包含学号列的索引。（3）WHERE条件使用NOT、<>、!=、IN和NOTIN运算符，无法使用索引。例如：WHERE学号<>'221201'，无法使用学号列索引。（4）使用OR分割的条件，如果OR前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用。（5）对WHERE后边条件为字符串的一定要加引号，字符串如果为数字MySQL会自动转为字符串，但是不会使用索引。索引建立、分析和使用4．建立索引的原则（1）唯一性索引可以更快速的通过它来确定某条记录，应优先使用。不唯一的尽量选择区分度高的列作为普通索引。（2）为经常作为查询条件的列建立索引，也经常需要排序、分组和联合操作的列建立索引。（3）如果索引的值很长，那么查询速度会受到影响。如果查询内容为列前部，尽量使用前缀来建立索引。（4）列需要计算或者经函数处理后才能查询的，不能加入索引。（5）限制索引的数目，索引越多，更新表效率会越低。不建非必要的索引，删除不再使用或很少使用的索引。04查询索引使用评估MySQL查询优化器EXPLAIN查看执行计划评估查询使用索引查询索引使用评估1．MySQL查询优化器MySQL中有专门负责优化SELECT语句的优化器模块，它通过计算分析系统中收集到的统计信息，为客户端请求的查询提供最优的执行计划，根据查询进行相应的计算分析，然后再得出最后的执行计划。2．EXPLAIN查看执行计划使用EXPLAIN语句可以模拟优化器执行SQL查询语句，分析其中的查询语句或是表结构的性能瓶颈。EXPLAIN查看执行计划（Explain+SQL语句）：EXPLAINSELECT语句\G;可以获取下列信息：表的读取顺序和操作类型、分区情况、哪些索引可以使用、哪些索引被实际使用、执行时计划扫描的记录行数以及是否使用外部排序等。查询索引使用评估3．评估查询使用索引（1）单表索引使用【例5.7】xs表查询评估。USExscj;EXPLAIN SELECT*FROMxs WHERE学号='221201';评估查询运行结果如图。查询索引使用评估（2）多表连接查询【例】xs、kc和cj表连接查询评估。USExscj;EXPLAIN SELECTxs.学号,姓名,课程名,成绩 FROMcjJOINxsONcj.学号=xs.学号 JOINkcONcj.课程号=kc.课程号 WHERE课程名='计算机导论'AND成绩>=80;评估查询运行结果如图。查询索引使用评估（3）没有使用索引【例】xs表没有使用索引的查询评估。USExscj;EXPLAIN SELECT姓名,学号,总学分 FROMxs WHERE专业='计算机'AND性别=0;评估查询运行结果如图。第5章

索引与数据完整性——数据完整性01实体完整性约束主键约束唯一键约束实体完整性约束1．主键约束通过定义表一列为PRIMARYKEY约束来创建主键，其组成主键的列值唯一而且不能取NULL值，就可以用此标识表中的行。如果主键约束是由多列组合定义的复合主键，则某一列的值可以相同，但定义主键中的所有列的组合值必须唯一，不能包含NULL值。例如：学生表（xs）中的学号列，课程表（kc）中的课程号列。实体完整性约束2．唯一键约束在关系模型中，唯一键像主键一样，可以由表的一列或多列组成，它是唯一性索引，关键字是UNIQUE。它们的值在任何时候都是唯一的，但可以包含一行或者多行NULL值。（1）在列中定义唯一键【例】在表xs1中将姓名列定义为一个唯一键。USExscj;DROPTABLEIFEXISTSxs1;CREATETABLExs1(学号 varchar(6) NOTNULL,姓名 varchar(8) UNIQUE,出生日期 datetime NULL, PRIMARYKEY(学号));实体完整性约束（2）在表中定义唯一键【例】在表xs1中列定义后将姓名定义为唯一键。DROPTABLEIFEXISTSxs1;CREATETABLExs1(学号 varchar(6) NOTNULL,姓名 varchar(8) NULL,出生日期 datetime NULL, PRIMARYKEY(学号), UNIQUExm(姓名));02参照完整性约束参照完整性约束定义参照完整性外键规则删除参照完整性约束参照完整性约束1．参照完整性约束定义参照完整性约束可以在创建表或修改表时定义一个外键声明。[CONSTRAINT]参照名 FOREIGNKEY(外键列)REFERENCES表名(主键) [ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}] [ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]参照语句和动作：（1）参照语句

ONDELETE|ONUPDATE：删除记录语句和更新记录语句。（2）采取的动作RESTRICT：拒绝对父表被参照列的删除或更新操作。它是默认值。CASCADE：从父表删除或更新行时自动删除或更新子表中匹配的行。SETNULL：当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。NOACTION：作用和RESTRICT一样。SETDEFAULT：父表有变更时,子表将外键列设置成一个默认的值（但InnoDB不能识别）。参照完整性约束2．参照完整性外键规则（1）被参照表必须已经存在，或者与参照表是同一个表。（2）必须为被参照表定义主键。（3）尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。（4）外键中列的数目必须和被参照表的主键中列的数目和数据类型相同。【例】定义学生表（xs1）、课程表（kc1）和成绩表（cj1）参照完整性。参照完整性包括下列3个方面：（1）在成绩表（cj1）中插入一条记录，如果学生表（xs1）中没有该学号对应的记录则不能插入。在学生表（xs1）中删除指定学号记录，同时也会删除成绩表（cj1）中对应该学号的所有记录。（2）在成绩表（cj1）中插入一条记录，如果课程表（kc1）中没有该课程号对应的记录，则不能插入。在课程表（kc1）中删除指定课程号记录，同时也会删除成绩表（cj1）中对应该课程号的所有记录。（3）如果被引用表xs1表和kc1表学号键值更改了，对该键值的所有引用cj1表外键学号键值一致进行更改。参照完整性约束因为成绩表（cj1）已经创建，所以需要修改（cj1）表结构，添加表完整性约束，SQL语句如下：USExscj;ALTERTABLEcj1 ADDCONSTRAINTfr_xh_xsFOREIGNKEY(学号)REFERENCESxs1(学号) ONUPDATERESTRICT ONDELETECASCADE, ADDCONSTRAINTfr_kch_kcFOREIGNKEY(课程号)REFERENCESkc1(课程号) ONUPDATERESTRICT ONDELETECASCADE;3．删除参照完整性约束在修改表结构时删除表参照完整性约束。ALTERTABLE表名 DROPFOREIGNKEY约束名;03域完整性约束列定义完整性约束表完整性约束完整性约束命名应用程序保证数据完整性域完整性约束1．列定义完整性约束下列方式指定需要检查的条件，在更新表数据的时候，MySQL会检查更新后的数据行是否满足CHECK的条件。[CONSTRAINT[约束名]]CHECK(条件)例如：创建表xs2，性别只能是男（1）或女（0），出生日期只能在2001年1月1日以后。USExscj;DROPTABLEIFEXISTSxs2;CREATETABLExs2(

学号 char(6)NOTNULL,

姓名 char(4),

性别 tinyintCHECK(性别IN(1,0)),

出生日期 dateNOTNULL CHECK(出生日期>'2001-01-01'),

专业 varchar(10));域完整性约束2．表完整性约束（1）列的完整性约束可以在所有列定义后作为表完整性约束。例如：USExscj;DROPTABLEIFEXISTSxs2;CREATETABLExs2(

学号 char(6)NOTNULL,

姓名 char(4),

性别 tinyint,

出生日期 date,

专业 varchar(10), CHECK(性别IN(1,0)), CHECK(出生日期>'2001-01-01'));域完整性约束（2）如果完整性约束中需要表的两列或多列，那么该完整性约束只能定义为表完整性约束。例如：USExscj;DROPTABLEIFEXISTSxs2;CREATETABLExs2(

学号 char(6)NOTNULL,

姓名 char(4),

性别 tinyint,

出生