CHAPTER
第5章
MySQL索引与完整性约束
当查阅书中某些内容时,为了提高查阅速度,并不是从书的第一页开始顺序查找,而是
首先查看书的目录索引,找到需要的内容在目录中所列的页码,然后根据这一页码直接找到
需要的内容。
在MySQL 中,为了更快速地访问表中的数据,引入了索引;而为防止不符合规范的数
据进入数据库,MySQL 系统自动按一定的完整性约束条件对用户输入的数据进行监测,以
确保数据库中存储的数据正确、有效、相容。
5.SQL索引
1 My
11 索引及作用
5..
1.
索引
索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。
在列上创建了索引之后,查找数据时可以直接根据该列上的索引找到对应行的位置,从而快
速地找到数据。
例如,如果用户创建了xs表中学号列的索引,MySQL 将在索引中排序学号列,对于索
引中的每一项,MySQL 在内部为它保存一个数据文件中实际记录所在位置的“指针”。因
此,如果要查找学号为“081241”的学生信息,MySQL 能在学号列的索引中找到“081241”的
值,然后直接转到数据文件中相应的行,准确地返回该行的数据。在这个过程中,MySQL
只需处理一行就可以返回结果。如果没有“学号”列的索引,MySQL 则要扫描数据文件中
的所有记录。
2.MySQL索引
在MySQL5.6中,所有的MySQL 列类型都能被索引,但要注意以下几点:
(1)MySQL 能在多个列上创建索引。索引可以由最多15 个列组成。最大索引长度
是256B 。
(2)对于CHAR 和VARCHAR 列,可以索引列的前缀。这样索引的速度更快,并且比
索引列的全部内容需要较少的磁盘空间。
(3)一个表最多可有16 个索引。
(4)只有当表类型为MyISAM 、InnoDB 或BDB 时,才可以包含NULL 、BLOB 或
TEXT 类型的列添加索引。
第5章 MySQL索引与完整性约束 87
5.1.2 索引的分类
索引是存储在文件中的,所以索引也是要占用物理空间的,MySQL将一个表的索引都
保存在同一个索引文件中。如果更新表中的一个值或者向表中添加或删除一行,MySQL
会自动地更新索引,因此索引树总是和表的内容保持一致。
1.BTREE索引
目前大部分MySQL索引都是以B-树(BTREE)方式存储的,索引类型分成下列几个。
1)普通索引
普通索引(INDEX)是最基本的索引类型,它没有唯一性之类的限制。创建普通索引的
关键字是INDEX。
2)唯一性索引
唯一性索引(UNIQUE)索引和前面的普通索引基本相同,但有一个区别:索引列的所
有值都只能出现一次,即必须是唯一的。创建唯一性索引的关键字是UNIQUE。
3)主键
主键(PRIMARYKEY)是一种唯一性索引,它必须指定为PRIMARY KEY。主键一
般在创建表的时候指定,也可以通过修改表的方式加入主键。但是,每个表只能有一个
主键。
4)全文索引
MySQL支持全文检索和全文索引(FULLTEXT)。全文索引的索引类型为
FULLTEXT。全文索引只能在VARCHAR 或TEXT 类型的列上创建,并且只能在
MyISAM 表中创建。它可以通过CREATE TABLE 命令创建,也可以通过ALTER
TABLE或CREATEINDEX命令创建。对于大规模的数据集,通过ALTERTABLE(或
CREATEINDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。
2.哈希索引
当表类型为MEMORY 或HEAP时,除了BTREE索引外,MySQL还支持哈希索引
(HASH)。使用哈希索引,不需要建立树结构,但是所有的值都保存在一个列表中,这个列
表指向相关页和行。当根据一个值获取一个特定的行时,哈希索引非常快。
5.2 MySQL索引创建
1.用CREATEINDEX 语句创建
使用CREATEINDEX语句可以在一个已有表上创建索引,一个表可以创建多个索
引。其语法格式为:
CREATE [UNIQUE|FULLTEXT|SPATIAL]INDEX 索引名
[索引类型]
ON 表名(索引列名...)
[索引选项]...
索引列名=:
列名[(长度)][ASC|DESC]
88 MySQL教程(第2版)
说明:
● UNIQUE|FULLTEXT|SPATIAL———UNIQUE 表示创建的是唯一性索引;
FULLTEXT表示创建全文索引;SPATIAL表示为空间索引,可以用来索引几何数
据类型的列(本书不讨论这种索引)。
● 索引名———索引在一个表中名称必须是唯一的。
● 索引类型———MySQL支持的索引类型有BTREE和HASH。
● 索引列名———创建索引的列名后的长度表示该列前面创建索引字符个数。这可使
索引文件大大减小,从而节省磁盘空间。
BLOB或TEXT 列必须用前缀索引。前缀最长为255B,但对于MyISAM 和InnoDB
表,最长达1000B。
另外,还可以规定索引按升序(ASC)或降序(DESC)排列,默认为ASC。如果一条
SELECT语句中的某列按降序排列,那么在该列上定义一个降序索引可加快处理速度。
但是,CREATEINDEX语句并不能创建主键。
【例5.1】 根据xs表的学号列上的前5个字符建立一个升序索引xh_xs。
use xscj
create index xh_xs
on xs(学号(5) asc);
也可以在一个索引的定义中包含多个列,中间用逗号隔开,但它们属于同一个表,这样
的索引称为复合索引。
【例5.2】 在xs_kc表的学号列和课程号列上建立一个复合索引xskc_in。
create index xskc_in
on xs_kc(学号,课程号);
2.用ALTERTABLE语句创建
前面介绍过如何使用ALTERTABLE语句修改表,其中也包括向表中添加索引。其
语法格式为:
ALTER [IGNORE]TABLE 表名
...
|ADD {INDEX|KEY} [索引名] /*添加索引*/
[索引类型](索引列名...) [索引选项]...
|ADD [CONSTRAINT [symbol]]PRIMARY KEY /*添加主键*/
[索引类型](索引列名...) [索引选项]...
|ADD [CONSTRAINT [symbol]]UNIQUE [INDEX|KEY][索引名]
[索引类型](索引列名...) [索引选项]... /*添加唯一性索引*/
|ADD FULLTEXT [INDEX|KEY][索引名](索引列名...)[索引选项]... /*添加全文索引*/
|ADD SPATIAL [INDEX|KEY][索引名](索引列名...)[索引选项]... /*添加空间索引*/
|ADD [CONSTRAINT [symbol]]FOREIGN KEY [索引名](索引列名...) [参照性定义]
/*添加外键*/
第5章 MySQL索引与完整性约束 89
|DISABLE KEYS
|ENABLE KEYS
说明:
● 索引类型———语法格式为USING {BTREE|HASH}。
当定义索引时默认索引名,则一个主键的索引称作PRIMARY,其他索引使用索引的
第一个列名作索引名。如果存在多个索引的名字以某一个列的名字开头,就在列名后面放
置一个顺序号码。
● CONSTRAINT [symbol]———为主键、UNIQUE键、外键定义一个名字。这个将在
5.3节中介绍。
● DISABLE KEYS|ENABLE KEYS:只在MyISAM 表中有用,使用ALTER
TABLE...DISABLEKEYS可以让MySQL在更新表时停止更新MyISAM 表中的
非唯一索引,然后使用ALTERTABLE ...ENABLEKEYS重新创建丢失的索引,
这样可以极大地加快查询速度。
【例5.3】 在xs表的姓名列上创建一个非唯一的索引。
alter table xs
add index xs_xm using btree (姓名);
【例5.4】 以xs表为例(假设表中主键未定)创建一个复合索引,以加速表的检索速度。
alter table xs
add index mark(出生日期,性别);
如果想要查看表中创建的索引的情况,可以使用SHOWINDEXFROM 表名语句,
例如:
show index from xs;
系统显示已创建的索引信息,如图5.1所示。
图5.1 例5.4执行结果
90 MySQL教程(第2版)
3.在建立表时创建索引
在前两种情况下,索引都是在表建立之后创建的。索引也可以在创建表时一起创建。
在创建表的CREATETABLE语句中可以包含索引的定义,其语法格式为:
CREATE [TEMPORARY]TABLE [IF NOT EXISTS]表名
[([列定义], ...|[索引定义])]
[表选项][select 语句];
索引定义为:
[CONSTRAINT [symbol]]PRIMARY KEY [索引类型](索引列名...) /*主键*/
|{INDEX|KEY} [索引名][索引类型](索引列名...) /*索引*/
|[CONSTRAINT [symbol]]UNIQUE [INDEX|KEY][索引名][索引类型](索引列名...)
/*唯一性索引*/
|[FULLTEXT|SPATIAL][INDEX|KEY][索引名](索引列名...) /*全文索引*/
|[CONSTRAINT [symbol]]FOREIGN KEY [索引名](索引列名...)[参照性定义] /*外键*/
说明:KEY 通常是INDEX 的同义词。在定义列选项的时候,也可以将某列定义为
PRIMARYKEY,但是当主键是由多个列组成的多列索引时,定义列时无法定义此主键,必
须在语句最后加上一个PRIMARYKEY(列名,...)子句。
【例5.5】 在mytest数据库中创建成绩(cj)表,学号和课程号的联合主键,并在成绩列
上创建索引。
use mytest
create table xs_kc
(
学号 char(6) not null,
课程号char(3) not null,
成绩tinyint(1),
学分tinyint(1),
primary key(学号,课程号),
index cj(成绩)
);
说明:使用“SHOWINDEXFROM 表名”命令查看执行结果。
4.删除索引
当一个索引不再需要的时候,可以用DROPINDEX语句或ALTERTABLE语句删除
这个索引。
1)使用DROPINDEX删除
使用DROPINDEX删除索引的语法格式为:
DROP INDEX 索引名ON 表名
第5章 MySQL索引与完整性约束 91
2)使用ALTERTABLE删除
使用ALTERTABLE删除索引的语法格式为:
ALTER [IGNORE]TABLE 表名
...
|DROP PRIMARY KEY /*删除主键*/
|DROP {INDEX|KEY} 索引名/*删除索引*/
|DROP FOREIGN KEY fk_symbol /*删除外键*/
说明:DROP{INDEX|KEY}子句可以删除各种类型的索引。使用DROPPRIMARY
KEY子句时不需要提供索引名称,因为一个表中只有一个主键。
【例5.6】 删除xs表上的mark索引。
alter table xs
drop index mark;
读者可使用“SHOWINDEXFROM 表名”语句查看执行结果。
如果从表中删除了列,索引可能会受影响。如果所删除的列为索引的组成部分,则该列
也会从索引中删除。如果组成索引的所有列都被删除,则整个索引都将被删除。
5.3 MySQL数据完整性约束
保持数据库的数据完整性是DBMS最为重要的功能之一。数据完整性包括数据的一
致性和正确性。完整性约束(简称约束)是数据库的内容必须随时遵守的规则。
完整性约束的声明对于一个表的可能值做出了限制,可以通过CREATETABLE 或
ALTERTABLE语句定义几个完整性约束。例如,对于每一列,可以声明NOTNULL,这
意味着不允许为空值,即列必须填充,在前面已经讨论了这种约束。除此之外,MySQL还
有其他各种不同类型的完整性约束,下面将系统介绍。
5.3.1 主键约束
主键就是表中的一列或多个列的一组,其值能唯一地标志表中的每一行。通过定义
PRIMARYKEY 约束来创建主键,而且PRIMARY KEY 约束中的列不能取空值。由于
PRIMARYKEY约束能确保数据的唯一性,所以经常用来定义标志列。当为表定义
PRIMARYKEY约束时,MySQL为主键列创建唯一性索引,实现数据的唯一性,在查询中
使用主键时,该索引可用来对数据进行快速访问。如果PRIMARY KEY 约束是由多列组
合定义的,则某一列的值可以重复,但PRIMARY KEY 约束定义中所有列的组合值必须
唯一。可
以用两种方式定义主键,作为列或表的完整性约束。作为列的完整性约束时,只需在
列定义的时候加上关键字PRIMARYKEY,这一点在3.2.1节中已介绍过。作为表的完整
性约束时,需要在语句最后加上一条PRIMARYKEY(col_name,...)语句。
【例5.7】 创建表xs1,将姓名定义为主键。
92 MySQL教程(第2版)
create table xs1
(
学号 varchar(6) null,
姓名 varchar(8) not null primary key ,
出生日期datetime
);
说明:例中主键定义于空指定之后,空指定也可以在主键之后指定。
当表中的主键为复合主键时,只能定义为表的完整性约束。
【例5.8】 创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。
其中,学号、课程号和毕业日期构成复合主键。
create table course
(
学号 varchar(6) not null,
姓名varchar(8) not null,
毕业日期date not null,
课程号varchar(3) ,
学分tinyint ,
primary key (学号, 课程号, 毕业日期)
);
原则上任何列或者列的组合都可以充当一个主键,但是主键列必须遵守下列规则:
(1)每个表只能定义一个主键。
(2)MySQL可以创建一个没有主键的表。但是,从安全角度应该为每个基础表指定一
个主键。主要原因在于:若没有主键,则可能在一个表中存储两个相同的行,这两个行由于
不能彼此区分,在查询中,它们满足同样的条件,在更新时也总是一起更新,这样可能会导致
数据库崩溃。
(3)表中的两个不同的行在主键上不能具有相同的值,即所谓的“唯一性规则”。
(4)如果从一个复合主键中删除一列后,剩下的列构成的主键仍然满足唯一性原则,那
么这个复合主键是不正确的,这条规则称为“最小化规则”。也就是说,复合主键不应包含任
何不必要的列。
MySQL自动地为主键创建一个索引。通常,这个索引名为PRIMARY。然而,可以重
新给这个索引取名。
【例5.9】 创建的course表,同时创建主键索引,索引命名为index_course。
create table course
(
学号 varchar(6) not null,
第5章 MySQL索引与完整性约束 93
姓名 varchar(8) not null,
毕业日期date not null,
课程号varchar(3),
学分tinyint ,
primary key index_course(学号, 课程号, 毕业日期)
);
5.3.2 替代键约束
在关系模型中,替代键像主键一样,是表的一列或一组列,它们的值在任何时候都是唯
一的。替代键是没有被选作主键的候选键。定义替代键的关键字是UNIQUE。
【例5.10】 在表xs1中将姓名列定义为一个替代键。
create table xs1
(
学号 varchar(6) null,
姓名varchar(8) not null unique,
出生日期datetime null,
primary key(学号)
);
说明:关键字unique表示“姓名”是一个替代键,其列值必须是唯一的。
替代键还可以定义为表的完整性约束,故前面的语句也可定义如下:
create table xs1
(
学号 varchar(6) null,
姓名varchar(8) not null,
出生日期datetime null,
primary key(学号),
unique(姓名)
);
在MySQL中,替代键和主键的区别主要有以下3点:
(1)一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键,并且它们甚
至可以重合。例如,在C1 和C2 列上定义了一个替代键,并且在C2 和C3 上定义了另一个替
代键,这两个替代键在C2 列上重合了,而MySQL允许这样。
(2)主键字段的值不允许为NULL,而UNIQUE字段的值可取NULL,但是必须使用
NULL或NOTNULL声明。
(3)一般创建PRIMARYKEY约束时,系统会自动产生PRIMARYKEY索引。创建
UNIQUE约束时,系统自动产生UNIQUE索引。
通过PRIMERYKEY约束和UNIQUE约束可实现表的所谓实体完整性约束。定义
为PRIMERYKEY和UNIQUEKEY的列上都不允许出现重复的值。
94 MySQL教程(第2版)
5.3.3 参照完整性约束
在本书所例举的xscj数据库中,有很多规则是和表之间的关系有关的。例如,存储在
xs_kc表中的所有学号必须同时存在于xs表的学号列中。xs_kc表中的所有课程号也必须
出现在kc表的课程号列中。这种类型的关系就是“参照完整性约束”。参照完整性约束是
一种特殊的完整性约束,表现为一个外键,所以xs_kc表中的学号列和课程号列都可以定义
为一个外键,可以在创建表或修改表时定义一个外键声明。
定义外键的语法格式已经在介绍索引时给出了,这里列出“参照性定义”。
参照性定义为:
REFERENCES 表名[(索引列名...)]
[ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}]
[ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}]
索引列名为:
列名[(长度)][ASC|DESC]
说明:
(1)外键被定义为表的完整性约束,参照性定义中包含了外键所参照的表和列,还可以
声明参照动作。这里表名称为被参照表。而外键所在的表称为参照表。
列名是外键可以引用一个或多个列,外键中的所有列值在引用的列中必须全部存在。
外键可以只引用主键和替代键。外键不能引用被参照表中随机的一组列,它必须是被参照
表的列的一个组合,且其中的值都保证是唯一的。
(2)ONDELETE|ON UPDATE:可以为每个外键定义参照动作。
参照动作包含两部分:
在第一部分中,指定这个参照动作应用哪一条语句。这里有两条相关的语句,即
UPDATE和DELETE语句;
在第二部分中,指定采取哪个动作。可能采取的动作是RESTRICT、CASCADE、SET
NULL、NOACTION 和SETDEFAULT。
接下来说明这些不同动作的含义。
● RESTRICT:当要删除或更新父表中被参照列上在外键中出现的值时,拒绝对父表
的删除或更新操作。
● CASCADE:从父表删除或更新行时自动删除或更新子表中匹配的行。
● SETNULL:当从父表删除或更新行时,设置子表中与之对应的外键列为NULL。
如果外键列没有指定NOTNULL限定词,这就是合法的。
● NOACTION:意味着不采取动作,就是如果有一个相关的外键值在被参考的表里,
删除或更新父表中主要键值的企图不被允许,和RESTRICT一样。
● SETDEFAULT:作用和SETNULL一样,只不过SETDEFAULT是指定子表中
的外键列为默认值。
如果没有指定动作,两个参照动作就会默认使用RESTRICT。
第5章 MySQL索引与完整性约束 95
外键目前只可以用在那些使用InnoDB 存储引擎创建的表中,对于其他类型的表,
MySQL服务器能够解析CREATETABLE语句中的FOREIGNKEY语法,但不能使用或
保存它。
【例5.11】 创建xs1表,所有的xs表中学生的学号都必须出现在xs1表中,假设已经
使用学号列作为主键创建了xs表。
create table xs1
(
学号 varchar(6) null,
姓名varchar(8) not null,
出生日期datetime null,
primary key (姓名),
foreign key (学号)
references xs (学号)
on delete restrict
on update restrict
);
说明:在这条语句中,定义一个外键的实际作用是,在这条语句执行后,确保MySQL
插入外键中的每一个非空值都已经在被参照表中作为主键出现过。
这意味着,对于xs1表中的每一个学号,都执行一次检查,看这个号码是否已经出现在
xs表的学号列(主键)中。如果情况不是这样,用户或应用程序会收到一条出错消息,并且
更新被拒绝。这也适用于使用UPDATE语句更新xs1表中的学号列。即MySQL确保了
xs1表中的学号列的内容总是xs表中学号列内容的一个子集。下面的SELECT 语句不会
返回任何行:
select*
from xs1
where 学号not in
(select 学号
from xs
);
当指定一个外键的时候,以下的规则适用:
(1)被参照表必须已经用一条CREATETABLE语句创建了,或者必须是当前正在创
建的表。在后一种情况下,参照表是同一个表。
(2)必须为被参照表定义主键。
(3)必须在被参照表的表名后面指定列名(或列名的组合)。这个列(或列组合)必须是
这个表的主键或替代键。
(4)尽管主键是不能够包含空值的,但允许在外键中出现一个空值。这意味着,只要外
键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
(5)外键中的列的数目必须和被参照表的主键中的列的数目相同。
(6)外键中的列的数据类型必须和被参照表的主键中的列的数据类型对应相等。