单元7 索引与数据完整性约束

1、单元七单元七 索引与数据完整性约束索引与数据完整性约束 项目知识要点与目标项目知识要点与目标 项目知识要点项目知识要点知识能力目标知识能力目标学时学时 7.1 7.1 任务任务1 1 索引及其应用索引及其应用 7.1.1 7.1.1 索引的分类索引的分类 7.1.2 7.1.2 创建索引创建索引 7.1.3 7.1.3 删除索引删除索引 7.1.4 7.1.4 索引对查询的影响索引对查询的影响 7.2 7.2 任务任务2 2 数据完整性约束数据完整性约束 7.2.1 7.2.1 主键约束主键约束 7.2.2 7.2.2 替代键约束替代键约束 7.2.3 7.2.3 参照完整性约束参照完整性约束

2、 7.2.4 CHECK7.2.4 CHECK完整性约束完整性约束 7.3 7.3 综合实例综合实例 PetStorePetStore数数 据索引与完整性约束据索引与完整性约束 【情境情境】：PetstorePetstore索引与完整性约束索引与完整性约束 【职业岗位职业岗位】：操作员、程序员：操作员、程序员 【典型工作任务典型工作任务】：创建索引、建立数：创建索引、建立数 据完整性约束据完整性约束 【技能要求技能要求】： 1 1、创建索引；、创建索引； 2 2、建立数据完整性约束；、建立数据完整性约束； 【知识要求知识要求】： 1 1、索引的作用与使用、索引的作用与使用 2 2、索引与数据完

3、整性约束的、索引与数据完整性约束的SQLSQL语句语句 8 8 项目实训七项目实训七“员工管理系统员工管理系统”索引与完整性约束索引与完整性约束 8 8 任务一任务一 索引及其应用索引及其应用 索引索引 是一种是一种提高查找速度提高查找速度的机制的机制 索引用来快速地寻找那些具有特定值的记录，如果没有索引，执索引用来快速地寻找那些具有特定值的记录，如果没有索引，执 行查询时行查询时MySQLMySQL必须从必须从第一个记录开始第一个记录开始扫描整个表的所有记录，直至扫描整个表的所有记录，直至 找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越找到符合要求的记录。表里面的记录数量越多，

4、这个操作的代价就越 高。高。 索引提供指针以指向存储在表中指定列的数据值，然后根据指定索引提供指针以指向存储在表中指定列的数据值，然后根据指定 的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很 相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。 如果作为搜索条件的列上已经创建了索引，如果作为搜索条件的列上已经创建了索引，MySQLMySQL无需扫描任何无需扫描任何 记录即可迅速得到目标记录所在的位置。如果表有记录即可迅速得到目标记录所在的位置。如果表

5、有10001000个记录，通过个记录，通过 索引查找记录至少要比顺序扫描记录快索引查找记录至少要比顺序扫描记录快100100倍。倍。 索引文件索引文件 学生表学生表studentstudent中建立中建立“学号学号”索引（升序）示意图索引（升序）示意图 学号学号记录号记录号 20060101200601011 1 20060102200601023 3 20060203200602032 2 20070201200702011000010000 2080999208099950005000 学号学号姓名姓名性别性别年龄年龄 20060101 20060101 张建张建男男1818 200602

6、0220060202王欢王欢女女1717 2006010220060102田静田静男男1818 . 20809992080999李华李华女女1919 . 2007020120070201张丽张丽女女1717 索引文件如何影响原表索引文件如何影响原表 如果要找位于第如果要找位于第1000010000条的学号条的学号”2007020120070201”的记录的记录, , 计算机要在表中查找计算机要在表中查找1000010000次次 学号姓名性别年龄 20060101 张建男18 20060202王欢女17 20060102田静男18 . 2080999李华女19 . 20070201张丽女17 使

7、用使用 2007020120070201”的记录的记录, ,找到找到 相应的记录号相应的记录号, ,再到学生表中直接读取相关记录再到学生表中直接读取相关记录. . 学号学号记录号记录号 20060101200601011 1 20060102200601023 3 20060203200602032 2 20070201200702011000010000 2080999208099950005000 学号学号姓名姓名性别性别年龄年龄 20060101 20060101 张建张建男男1818 2006020220060202王欢王欢女女1717 2006010220060102田静田静男男18

8、18 . 20809992080999李华李华女女1919 . 2007020120070201张丽张丽女女1717 索引文件如何加快查找速度索引文件如何加快查找速度 原因：原因： （1 1）索引后，指针在索引文件中顺序移动。）索引后，指针在索引文件中顺序移动。 （2 2）索引文件中记录是有序的。）索引文件中记录是有序的。 （3 3）有序后，可以用各种方法加快查询速度，）有序后，可以用各种方法加快查询速度， 如折半（二分）查找法，而排序前，只如折半（二分）查找法，而排序前，只 能顺序查找记录。能顺序查找记录。 =50 rowID rowID rowID rowID 40.50 30.40 20.30 10.20 80.90 70.80 60.70 1980-01-01)1980-01-01) ); ); 如果使用一条如果使用一条DROP TABLEDROP TABLE语句删除一个表，所有的完整性约束都自语句删除一个表，所有的完整性约束都自 动被删除了。被参照表的所有外键也都被删除了，使用动被删除了。被参照表的所有外键也都被删除了，使用ALTER TABLEALTER TABLE语语 句，完整性可以独立地被删除，而不