mysql索引与数据完整性约束.pptVIP

索引与数据完整性约束 项目知识要点与目标 任务一 索引及其应用 索引 —— 是一种提高查找速度的机制 索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。 如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。 索引文件 学生表student中建立“学号”索引（升序）示意图 学生表 student 索引文件 索引文件如何影响原表 没有索引文件时： 指针在原表中顺序移动 如果要找位于第10000条的学号的记录, 计算机要在表中查找10000次 使用索引查找 有索引文件时：(二分法查找实例) 计算机先在索引文件中学号为的记录,找到相应的记录号,再到学生表中直接读取相关记录. 学生表 student 索引文件 索引文件如何加快查找速度 原因： （1）索引后，指针在索引文件中顺序移动。 （2）索引文件中记录是有序的。 （3）有序后，可以用各种方法加快查询速度， 如折半（二分）查找法，而排序前，只 能顺序查找记录。 B树索引示意图 50 =50 … … rowID rowID rowID rowID … … 40..50 30..40 20..30 10..20 … … … … 80..90 70..80 60..70 50..60 rowID rowID rowID rowID rowID … … 41 rowID 42 rowID 43 rowID 44 rowID … … 索引的分类 1. 普通索引（INDEX） 这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。 2. 唯一性索引（UNIQUE） 这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。 3. 主键（PRIMARY KEY） 主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。 4. 全文索引（FULLTEXT） MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。 创建索引 1. 使用CREATE INDEX语句 使用CREATE INDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。 语法格式： CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名(列名[(length)] [ASC | DESC],...) 说明： ●索引名：索引的名称，索引名在一个表中名称必须是唯一的。 ● 列名：表示创建索引的列名。 length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。BLOB或TEXT列必须用前缀索引。 ● UNIQUE:UNIQUE表示创建的是唯一性索引 ● FULLTEXT:FULLTEXT表示创建全文索引； ● CREATE INDEX 语句并不能创建主键。 创建索引举例 【例7.1】 根据Book表的书名列上的前6个字符建立一个升序索引name-_book。 CREATE INDEX name-_book ON Book(书名(6) ASC); 可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。 【例7.2】 在Sell表的身份证号列和图书编号列上建立一个复合索引sfz_bh-_sell。 CREATE INDEX sfz_bh-_sell ON Sell(身份证号,图书编号); 课堂练习 1、对Employees表中的DepartmentID列创建普通索引depart_ind 2、对Employees表中的Name和Address列创建复合索引Ad_ind 3、对Departments表中的DepartmentName列创建唯一索引 ALTER TAB