第5章MySQL索引与完整性约束

1、第第5章章 MySQL索引与完整性约束索引与完整性约束 MySQL索引索引5.1 5.2MySQL数据完整性约束数据完整性约束 5.1 MySQL索引 5.1.1 索引及作用索引及作用 1索引概念索引概念 在数据库中，为了从大量的数据中迅速找到所需的内容，也采用类似于书目 录这样的检索技术，使得数据查询时不必一行一行记录地扫描整个数据库中全部 的表，就能迅速查到所需要的内容！这就是MySQL的索引技术。 5.1.1 索引及作用索引及作用 2索引作用索引作用 在数据库系统中建立索引主要有以下作用： l快速读取数据； l保证数据记录的唯一性； l实现表与表之间的参照完整性； l在使用GROUP B

2、Y、ORDER BY子句进行数据检索时，利用索引可减少排序 和分组的时间。 5.1.1 索引及作用索引及作用 在MySQL 5.6中，所有的MySQL列类型都能被索引，但要注意以下几点： l只有当表类型为MyISAM、InnoDB或BDB时，才可以向有NULL、BLOB或 TEXT列中添加索引。 l一个表最多可有16个索引。最大索引长度是256个字节。 l对于CHAR和VARCHAR列，可以索引列的前缀。这样索引的速度更快并且比 索引整个列需要较少的磁盘空间。 lMySQL能在多个列上创建索引。 5.1.2 索引的分类索引的分类 1普通索引（普通索引（INDEX） 这是最基本的索引类型，它没有

3、唯一性之类的限制。创建普通索引的关键字 是INDEX。 2唯一性索引（唯一性索引（UNIQUE） 这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只 能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。 3主键（主键（PRIMARY KEY） 主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。主键一般在创 建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主 键。 5.1.2 索引的分类索引的分类 4全文索引（全文索引（FULLTEXT） MySQL支持全文检索和全文索引。在MySQL中，全文索引的索引类型为 FULLTEXT。全文索

4、引只能在VARCHAR或TEXT类型的列上创建，并且只能在 MyISAM表中创建。 5哈希索引（哈希索引（HASH） 当表类型为MEMORY或HEAP时，除了BTREE索引，MySQL还支持哈希索 引（HASH）。使用哈希索引，不需要建立树结构，但是所有的值都保存在一个 列表中，这个列表指向相关页和行。 5.1.3 创建和删除索引创建和删除索引 1CREATE INDEX语句创建语句创建 使用CREATE INDEX语句可以在一个已有表上创建索引，一个表可以创建 多个索引。 语法格式：语法格式： CREATE UNIQUE | FULLTEXT | SPATIAL INDEX index_na

5、me index_type ON tbl_name (index_col_name,.) index_option . 其中，index_col_name格式为： col_name (length) ASC | DESC 5.1.3 创建和删除索引创建和删除索引 说明：说明： lUNIQUE | FULLTEXT | SPATIAL：UNIQUE表示创建的是唯一性索引； FULLTEXT表示创建全文索引；SPATIAL表示为空间索引，可以用来索引几何数 据类型的列（本书不讨论这种索引）。 lindex_name：索引的名称，索引在一个表中名称必须是唯一的。 lindex_type：部分存储引擎

6、允许在创建索引时指定索引的类型。 lindex_col_name：col_name表示创建索引的列名。 5.1.3 创建和删除索引创建和删除索引 【例5.1】根据xs表的学号列上的前5个字符建立一个升序索引xh_xs。 create index xh_xs on xs(学号(5) asc); 也可以在一个索引的定义中包含多个列，中间用逗号隔开，但它们属于同一 个表，这样的索引叫作复合索引。 【例5.2】在xs_kc表的学号列和课程号列上建立一个复合索引xskc_in。 create index xskc_in on xs_kc(学号,课程号); 5.1.3 创建和删除索引创建和删除索引 2AL

7、TER TABLE语句创建语句创建 语法格式如下： ALTER IGNORE TABLE tbl_name . | ADD INDEX|KEY index_name/*添加索 引*/ index_type (index_col_name,.) index_option . | ADD CONSTRAINT symbol PRIMARY KEY/*添加主键*/ index_type (index_col_name,.) index_option . | ADD CONSTRAINT symbolUNIQUE INDEX|KEY index_name /*添加唯一性索引*/ index_type

8、(index_col_name,.) index_option . | ADD FULLTEXT INDEX|KEY index_name (index_col_name,.) index_option . /*添加全文索引*/ | ADD SPATIAL INDEX|KEY index_name (index_col_name,.) index_option . /*添加空间索引*/ | ADD CONSTRAINT symbol FOREIGN KEY index_name (index_col_name,.) reference_definition /*添加外键*/ | DISABLE

9、 KEYS | ENABLE KEYS 5.1.3 创建和删除索引创建和删除索引 说明：说明： lindex_type：语法格式为USING BTREE | HASH。 lCONSTRAINT symbol：为主键、UNIQUE键、外键定义一个名字。这个 将在命名完整性约束一节中介绍。 lDISABLE KEYS | ENABLE KEYS：只在MyISAM表中有用，使用ALTER TABLE.DISABLE KEYS可以让MySQL在更新表时停止更新MyISAM表中的非 唯一索引，然后使用ALTER TABLE . ENABLE KEYS重新创建丢失的索引，这 样可以极大地加快查询速度。 【

10、例5.3】在xs表的姓名列上创建一个非唯一的索引。 alter table xs add index xs_xm using btree (姓名); 5.1.3 创建和删除索引创建和删除索引 【例5.4】以xs表为例（假设表中主键未定），创建这样的索引，以加速表 的检索速度： alter table xs add index mark(出生日期,性别); 如果想要查看表中创建的索引的情况，可以使用SHOW INDEX FROM tbl_name语句，例如： show index from xs; 系统显示已创建的索引信息如下： 5.1.3 创建和删除索引创建和删除索引 3在建立表时创建索引在建

11、立表时创建索引 语法格式：语法格式： CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name ( column_definition , . | index_definition ) table_option select_statement; 其中，index_definition为索引项： CONSTRAINT symbolPRIMARY KEY index_type (index_col_name,.)/*主键*/ | INDEX | KEY index_name index_type (index_col_name,.)/*索引*/ | CONSTR

12、AINT symbol UNIQUE INDEX|KEY index_name index_type (index_col_name,.) /*唯一性索引*/ | FULLTEXT|SPATIAL INDEX|KEY index_name (index_col_name,.)/*全文索引*/ | CONSTRAINT symbol FOREIGN KEY index_name (index_col_name,.) reference_definition /*外键*/ 5.1.3 创建和删除索引创建和删除索引 【例5.5】创建xs_kc表（结构同附录A表A.3），带有学号和课程号的联合 主键，

13、并在成绩列上创建索引。 create table xs_kc ( 学号 char(6) not null, 课程号 char(3) not null, 成绩 tinyint(1), 学分 tinyint(1), primary key(学号,课程号), index cj(成绩) ); 5.1.3 创建和删除索引创建和删除索引 4删除索引删除索引 当一个索引不再需要的时候，可以用DROP INDEX语句或ALTER TABLE语 句删除它。 （1）使用DROP INDEX删除 语法格式：语法格式： DROP INDEX index_name ON tbl_name 这个语句语法非常简单，inde

14、x_name为要删除的索引名，tb1_name为 索引所在的表。 【例5.6】删除xs表上的xh_xs索引。 drop index xh_xs on xs; 5.1.3 创建和删除索引创建和删除索引 （2）使用ALTER TABLE删除 语法格式：语法格式： ALTER IGNORE TABLE tbl_name . | DROP PRIMARY KEY /*删除 主键*/ | DROP INDEX|KEY index_name /*删除索引*/ | DROP FOREIGN KEY fk_symbol /*删除外键*/ 【例5.7】删除xs表上的mark索引。 alter table xs

15、drop index mark; 5.1.4 索引的利弊索引的利弊 1使用索引的好处使用索引的好处 当执行涉及多个表的连接查询时，索引将更有价值。 假如有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分 别由含有数值11000的1000行组成。 查找对应值相等的表行组合的查询如下： select c1,c2,c3 from t1,t2,t3 where t1.c1=t2.c2 and t1.c1=t3.c3; 5.1.4 索引的利弊索引的利弊 如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理 如下。 （1）从表t1中选择第一行，查看此行所包含的值。 （

16、2）使用表t2上的索引，直接跳到t2中与来自t1的值匹配的行。类似地，利 用表t3上的索引，直接跳到t3中与来自t1的值匹配的行。 （3）进到表t1的下一行并重复前面的过程，直到t1中所有的行已经查询过。 5.1.4 索引的利弊索引的利弊 2索引的弊端索引的弊端 但是，索引也有它的弊端： 首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量 的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。 其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新 组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降 低了添加、删除、修改和其他写入操作的效率。

17、表中的索引越多，则更新表的时 间就越长。 但是这些弊端并不妨碍索引的应用，因为索引带来的好处已经基本掩盖了它 的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的。 5.2 MySQL数据完整性约束 5.2.1 主键约束主键约束 可以用两种方式定义主键，作为列或表的完整性约束。作为列的完整性约 束时，只需在列定义的时候加上关键字PRIMARY KEY，这个在3.2.1节中已作 过介绍。作为表的完整性约束时，需要在语句最后加上一条PRIMARY KEY(col_name,)语句。 【例5.8】创建表xs1，将姓名定义为主键。 create table xs1 ( 学号 varchar(6) n

18、ull, 姓名 varchar(8) not null primary key , 出生日期 datetime ); 5.2.1 主键约束主键约束 当表中的主键为复合主键时，只能定义为表的完整性约束。 【例5.9】创建course表来记录每门课程的学生学号、姓名、课程号、学分 和毕业日期。其中学号、课程号和毕业日期构成复合主键。 create table course ( 学号 varchar(6) not null, 姓名 varchar(8) not null, 毕业日期date not null, 课程号varchar(3) , 学分tinyint , primary key (学号,

19、课程号, 毕业日期) ); 5.2.1 主键约束主键约束 原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一 些规则。这些规则源自于关系模型理论和MySQL所制定的以下规则： （1）每个表只能定义一个主键。来自关系模型的这一规则也适用于MySQL。 （2）关系模型理论要求必须为每个表定义一个主键。 （3）表中的两个不同的行在主键上不能具有相同的值，即所谓的“唯一性 规则”。 （4）如果从一个复合主键中删除一列后，剩下的列构成的主键仍然满足唯 一性原则，那么，这个复合主键是不正确的，这条规则称为“最小化规则” （Minimality Rule）。也就是说，复合主键不应包含任何不必

20、要的列。 （5）一个列名在一个主键的列的列表中只能出现一次。 5.2.1 主键约束主键约束 【例5.10】创建【例5.9】中的course表，把主键创建的索引命名为 index_course。 create table course ( 学号varchar(6) not null, 姓名varchar(8) not null, 毕业日期date not null, 课程号varchar(3), 学分tinyint , primary key index_course(学号, 课程号, 毕业日期) ); 5.2.2 替代键约束替代键约束 在关系模型中，替代键像主键一样，是表的一列或一组列，它们的

21、值在任何 时候都是唯一的。替代键是没有被选作主键的候选键。定义替代键的关键字是 UNIQUE。 【例5.11】在表xs1中将姓名列定义为一个替代键。 create table xs1 ( 学号 varchar(6) null, 姓名 varchar(8) not null unique, 出生日期 datetime null, primary key(学号) ); 5.2.2 替代键约束替代键约束 替代键还可以定义为表的完整性约束，故前面语句也可这样定义： create table xs1 ( 学号 varchar(6) null, 姓名 varchar(8) not null, 出生日期 d

22、atetime null, primary key(学号), unique(姓名) ); 5.2.2 替代键约束替代键约束 在MySQL中，替代键和主键的区别主要有以下几点： （1）一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键， 并且它们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和 C3上定义了另一个替代键，这两个替代键在C2列上重合了，而MySQL允许这样。 （2）主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是 必须使用NULL或NOT NULL声明。 （3）一般创建PRIMARY KEY约束时，系统会自动产生PRIMARY K

23、EY索 引。创建UNIQUE约束时，系统自动产生UNIQUE索引。 5.2.3 参照完整性约束参照完整性约束 定义外键的语法格式已经在介绍索引时给出了，这里列出 reference_definition的定义。 语法格式：语法格式： REFERENCES tbl_name (index_col_name,.) ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION 说明：说明：外键被定义为表的完整性约束，reference_definition

24、中包含了外键 所参照的表和列，还可以声明参照动作。 ltb1_name：外键所参照的表名，这个表叫做被参照表。 index_col_name：格式为 lcol_name (length) ASC | DESC lON DELETE | ON UPDATE：可以为每个外键定义参照动作。 5.2.3 参照完整性约束参照完整性约束 【例5.12】创建xs1表，所有的xs表中学生学号都必须出现在xs1表中，假 设已经使用学号列作为主键创建了xs表。 create table xs1 ( 学号 varchar(6) null, 姓名 varchar(8) not null, 出生日期 datetime

25、null, primary key (姓名), foreign key (学号) references xs (学号) on delete restrict on update restrict ); 5.2.3 参照完整性约束参照完整性约束 这也适用于使用UPDATE语句更新xs1表中的学号列。即MySQL确保了xs1 表中的学号列的内容总是xs表中学号列的内容的一个子集。也就是说，下面的 SELECT语句不会返回任何行： select * from xs1 where 学号 not in ( select 学号 from xs ); 5.2.3 参照完整性约束参照完整性约束 当指定一个外键

26、的时候，以下的规则适用： （1）被参照表必须已经用一条CREATE TABLE语句创建了，或者必须是当 前正在创建的表。 （2）必须为被参照表定义主键。 （3）必须在被参照表的表名后面指定列名（或列名的组合）。这个列（或 列组合）必须是这个表的主键或替代键。 （4）尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意 味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。 （5）外键中的列的数目必须和被参照表的主键中的列的数目相同。 （6）外键中的列的数据类型必须和被参照表的主键中的列的数据类型对应 相等。 5.2.3 参照完整性约束参照完整性约束 与外键相关的被参照

27、表和参照表可以是同一个表。这样的表称为自参照表 （Selfreferencing Table），这种结构称为自参照完整性（Selfreferential Integrity）。例如，可以创建这样的xs2表： create table xs2 ( 学号 varchar(6) not null, 姓名 varchar(8) not null, 出生日期 datetime null, primary key (学号), foreign key (学号) references xs1 (学号) ); 5.2.3 参照完整性约束参照完整性约束 【例5.13】创建带有参照动作ASCADE的xs1表。 cr

28、eate table xs1 ( 学号 varchar(6) not null, 姓名 varchar(8) not null, 出生日期 datetime null, primary key (学号), foreign key (学号) references xs (学号) on update cascade ); 5.2.4 CHECK完整性约束完整性约束 CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可定 义为表完整性约束。 语法格式：语法格式： CHECK(expr) 【例5.14】创建表student，只包括学号和性别两列，性别只能是男或女。 create table student ( 学号 char(6) not null, 性别 char(1) not null check(性别 in (男,