版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
第5章
MySQL索引与完整性约束MySQL索引5.15.2MySQL数据完整性约束5.1MySQL索引5.1.1索引及作用1.索引概念在数据库中,为了从大量的数据中迅速找到所需的内容,也采用类似于书目录这样的检索技术,使得数据查询时不必一行一行记录地扫描整个数据库中全部的表,就能迅速查到所需要的内容!这就是MySQL的索引技术。5.1.1索引及作用2.索引作用在数据库系统中建立索引主要有以下作用:快速读取数据;保证数据记录的唯一性;实现表与表之间的参照完整性;在使用GROUPBY、ORDERBY子句进行数据检索时,利用索引可减少排序和分组的时间。5.1.1索引及作用在MySQL5.6中,所有的MySQL列类型都能被索引,但要注意以下几点:只有当表类型为MyISAM、InnoDB或BDB时,才可以向有NULL、BLOB或TEXT列中添加索引。一个表最多可有16个索引。最大索引长度是256个字节。对于CHAR和VARCHAR列,可以索引列的前缀。这样索引的速度更快并且比索引整个列需要较少的磁盘空间。MySQL能在多个列上创建索引。5.1.2索引的分类1.普通索引(INDEX)这是最基本的索引类型,它没有唯一性之类的限制。创建普通索引的关键字是INDEX。创建索引:CREATEINDEX<索引的名字>ONtablename(列的列表);修改表:ALTERTABLEtablenameADDINDEX[索引的名字](列的列表);创建表的时候指定索引:CREATETABLEtablename([...],INDEX[索引的名字](列的列表));2.唯一性索引(UNIQUE)这种索引和前面的普通索引基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须是唯一的。创建唯一性索引的关键字是UNIQUE。创建索引:CREATEUNIQUEINDEX<索引的名字>ONtablename(列的列表);修改表:ALTERTABLEtablenameADDUNIQUE[索引的名字](列的列表);创建表的时候指定索引:CREATETABLEtablename([...],UNIQUE[索引的名字](列的列表));3.主键(PRIMARYKEY)主键是一种唯一性索引,它必须指定为“PRIMARYKEY”。主键一般在创建表的时候指定,也可以通过修改表的方式加入主键。但是每个表只能有一个主键。5.1.2索引的分类4.全文索引(FULLTEXT)MySQL支持全文检索和全文索引。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建,并且只能在MyISAM表中创建。它可以通过CREATETABLE命令创建,也可以通过ALTERTABLE或CREATEINDEX命令创建。对于大规模的数据集,通过ALTERTABLE(或者CREATEINDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快
【例5.4】以xs表为例(假设表中主键未定),创建这样的索引,以加速表的检索速度:altertablexs addindexmark(出生日期,性别);如果想要查看表中创建的索引的情况,可以使用SHOWINDEXFROMtbl_name语句,例如:showindexfromxs;系统显示已创建的索引信息如下:5.1.3创建和删除索引【例5.5】创建xs_kc表(结构同附录A表A.3),带有学号和课程号的联合主键,并在成绩列上创建索引。createtablexs_kc(
学号
char(6)notnull,
课程号
char(3)notnull,
成绩
tinyint(1),
学分
tinyint(1), primarykey(学号,课程号), indexcj(成绩));5.1.3创建和删除索引4.删除索引当一个索引不再需要的时候,可以用DROPINDEX语句或ALTERTABLE语句删除它。(1)使用DROPINDEX删除语法格式:DROPINDEXindex_nameONtbl_name这个语句语法非常简单,index_name为要删除的索引名,tb1_name为索引所在的表。【例5.6】删除xs表上的xh_xs索引。dropindexxh_xsonxs;5.1.3创建和删除索引(2)使用ALTERTABLE删除语法格式:ALTER[IGNORE]TABLEtbl_name......|DROPPRIMARYKEY /*删除主键*/|DROP{INDEX|KEY}index_name /*删除索引*/|DROPFOREIGNKEYfk_symbol /*删除外键*/【例5.7】删除xs表上的mark索引。altertablexs dropindexmark;5.1.4分析索引效率MySQL提供了EXPLAIN命令来完成分析索引效率这个任务。EXPLAIN命令的一般语法是:EXPLAIN<SQL命令>。例:EXPLAINSELECTpeopleidFROMpeopleWHEREfirstname='Mike'ANDlastname='Sullivan'ANDage='17';table:这是表的名字。type:连接操作的类型。下面是MySQL文档关于ref连接类型的说明:
“对于每一种与另一个表中记录的组合,MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀,或者如果键不是UNIQUE或PRIMARYKEY类型(换句话说,如果连接操作不能根据键值选择出唯一行),则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录,则ref是一种好的连接类型。”
在本例中,由于索引不是UNIQUE类型,ref是我们能够得到的最好连接类型。
如果EXPLAIN显示连接类型是“ALL”,而且你并不想从表里面选择出大多数记录,那么MySQL的操作效率将非常低,因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息,请参见MySQL的手册说明。
possible_keys:可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字(在本例中,它是“firstname”)。默认索引名字的含义往往不是很明显。
Key:显示了MySQL实际使用的索引的名字。如果它为空(或NULL),则MySQL不使用索引。
key_len:索引中被使用部分的长度,以字节计。在本例中,key_len是102,其中firstname占50字节,lastname占50字节,age占2字节。如果MySQL只使用索引中的firstname部分,则key_len将是50。
ref:它显示的是列的名字(或单词“const”),MySQL将根据这些列来选择行。在本例中,MySQL根据三个常量选择行。
rows:MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然,这里最理想的数字就是1。
Extra:
这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。在本例中,MySQL只是提醒我们它将用WHERE子句限制搜索结果集。
table
显示这一行的数据是关于哪张表的
type
这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE
INDEX(indexname)来强制使用一个索引或者用IGNOREINDEX(indexname)来强制MYSQL忽略索引
key_len
使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数
rows
MYSQL认为必须检查的用来返回请求数据的行数
Extra
关于MYSQL如何解析查询的额外信息。但这里可以看到的坏的例子是Usingtemporary和Usingfilesort,意思MYSQL根本不能使用索引,结果是检索会很慢
extra列返回的描述的意义
Distinct
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
Notexists
MYSQL优化了LEFTJOIN,一旦它找到了匹配LEFTJOIN标准的行,
就不再搜索了
Rangecheckedforeach
Record(indexmap:#)
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
Usingfilesort
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Usingindex
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
Usingtemporary
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDERBY上,而不是GROUPBY上
Whereused
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题
不同连接类型的解释(按照效率高低的顺序排序)
system
表只有一行:system表。这是const连接类型的特殊情况
const
表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
eq_ref
在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
ref
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
5.1.5索引的利弊1.使用索引的好处当执行涉及多个表的连接查询时,索引将更有价值。假如有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别由含有数值1~1000的1000行组成。查找对应值相等的表行组合的查询如下:selectc1,c2,c3 fromt1,t2,t3
wheret1.c1=t2.c2andt1.c1=t3.c3;5.1.5索引的利弊如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下。(1)从表t1中选择第一行,查看此行所包含的值。(2)使用表t2上的索引,直接跳到t2中与来自t1的值匹配的行。类似地,利用表t3上的索引,直接跳到t3中与来自t1的值匹配的行。(3)进到表t1的下一行并重复前面的过程,直到t1中所有的行已经查询过。5.1.4索引的利弊2.索引的弊端但是,索引也有它的弊端:首先,索引是以文件的形式存储的,索引文件要占用磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,在更新表中索引列上的数据时,对索引也需要更新,这可能需要重新组织一个索引,如果表中的索引很多,这是很浪费时间的。也就是说,这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多,则更新表的时间就越长。但是这些弊端并不妨碍索引的应用,因为索引带来的好处已经基本掩盖了它的缺陷,在表中有很多行数据的时候,索引通常是不可缺少的。5.2MySQL数据完整性约束5.2.1主键约束可以用两种方式定义主键,作为列或表的完整性约束。作为列的完整性约束时,只需在列定义的时候加上关键字PRIMARYKEY,这个在3.2.1节中已作过介绍。作为表的完整性约束时,需要在语句最后加上一条PRIMARYKEY(col_name,…)语句。【例5.8】创建表xs1,将姓名定义为主键。createtablexs1(
学号
varchar(6)null,
姓名
varchar(8)notnullprimarykey,
出生日期
datetime);索引使用注意1,索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。2,使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。3,排序的索引问题
mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么orderby中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4,like语句操作
一般情况下不鼓励使用like操作,尤其如果like是以%开始。like“%aaa%”不会使用索引而like“aaa%”可以使用索引。5,不要在列上进行运算
select*fromuserswhereYEAR(adddate)<2007;将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成select*fromuserswhereadddate<‘2007-01-01’;6,不使用NOTIN和<>操作NOTIN和<>操作都不会使用索引将进行全表扫描。NOTIN可以NOTEXISTS代替,id<>3则可使用id>3orid<3来代替。5.2.1主键约束当表中的主键为复合主键时,只能定义为表的完整性约束。【例5.9】创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中学号、课程号和毕业日期构成复合主键。createtablecourse(
学号
varchar(6)notnull,
姓名
varchar(8)notnull,
毕业日期
date notnull,
课程号
varchar(3),
学分
tinyint, primarykey(学号,课程号,毕业日期));5.2.1主键约束原则上,任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。这些规则源自于关系模型理论和MySQL所制定的以下规则:(1)每个表只能定义一个主键。来自关系模型的这一规则也适用于MySQL。(2)关系模型理论要求必须为每个表定义一个主键。(3)表中的两个不同的行在主键上不能具有相同的值,即所谓的“唯一性规则”。(4)如果从一个复合主键中删除一列后,剩下的列构成的主键仍然满足唯一性原则,那么,这个复合主键是不正确的,这条规则称为“最小化规则”(MinimalityRule)。也就是说,复合主键不应包含任何不必要的列。(5)一个列名在一个主键的列的列表中只能出现一次。5.2.1主键约束【例5.10】创建【例5.9】中的course表,把主键创建的索引命名为index_course。createtablecourse(
学号
varchar(6)notnull,
姓名
varchar(8)notnull,
毕业日期
date notnull,
课程号
varchar(3),
学分
tinyint, primarykeyindex_course(学号,课程号,毕业日期));5.2.2替代键约束在关系模型中,替代键像主键一样,是表的一列或一组列,它们的值在任何时候都是唯一的。替代键是没有被选作主键的候选键。定义替代键的关键字是UNIQUE。【例5.11】在表xs1中将姓名列定义为一个替代键。createtablexs1(
学号
varchar(6)null,
姓名
varchar(8)notnullunique,
出生日期
datetimenull,primarykey(学号));5.2.2替代键约束替代键还可以定义为表的完整性约束,故前面语句也可这样定义:createtablexs1(
学号
varchar(6)null,
姓名
varchar(8)notnull,
出生日期
datetimenull,primarykey(学号),unique(姓名));5.2.2替代键约束在MySQL中,替代键和主键的区别主要有以下几点:(1)一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键,并且它们甚至可以重合,例如,在C1和C2列上定义了一个替代键,并且在C2和C3上定义了另一个替代键,这两个替代键在C2列上重合了,而MySQL允许这样。(2)主键字段的值不允许为NULL,而UNIQUE字段的值可取NULL,但是必须使用NULL或NOTNULL声明。(3)一般创建PRIMARYKEY约束时,系统会自动产生PRIMARYKEY索引。创建UNIQUE约束时,系统自动产生UNIQUE索引。5.2.3参照完整性约束定义外键的语法格式已经在介绍索引时给出了,这里列出reference_definition的定义。语法格式:REFERENCEStbl_name[(index_col_name,...)][ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]说明:外键被定义为表的完整性约束,reference_definition中包含了外键所参照的表和列,还可以声明参照动作。tb1_name:外键所参照的表名,这个表叫做被参照表。index_col_name:格式为col_name[(length)][ASC|DESC]ONDELETE|ONUPDATE:可以为每个外键定义参照动作。5.2.3参照完整性约束【例5.12】创建xs1表,所有的xs表中学生学号都必须出现在xs1表中,假设已经使用学号列作为主键创建了xs表。createtablexs1(
学号
varchar(6)null,
姓名
varchar(8)notnull,
出生日期
datetimenull,primarykey(姓名),foreignkey(学号)referencesxs(学号)ondeleterestrictonupdaterestrict);5.2.3参照完整性约束这也适用于使用UPDATE语句更新xs1表中的学号列。即MySQL确保了xs1表中的学号列的内容总是xs表中学号列的内容的一个子集。也就是说,下面的SELECT语句不会返回任何行:select*fromxs1where学号
notin(select学号fromxs);5.2.3参照完整性约束当指定一个外键的时候,以下的规则适用:(1)被参照表必须已经用一条CREATETABLE语句创建了,或者必须是当前正在创建的表。(2)必须为被参照表定义主键。(3)必须在被参照表的表名后面指定列名(或列名的组合)。这个列(或列组合)必须是这个表的主键或替代键。(4)尽管主键是不能够包含空值的,但允许在外键中出现一个空值。这意味着,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。(5)外键中的列的数目必须和被参照表的主键中的列的数目相同。(6)外键中的列的数据类型必须和被参照表的主键中的列的数据类型对应相等。5.2.3参照完整性约束与外键相关的被参照表和参照表可以是同一个表。这样的表称为自参照表(Self-referencingTable),这种结构称为自参照完整性(Self-referentialIntegrity)。例如,可以创建这样的xs2表:createtablexs2(
学号
varchar(6)notnull,
姓名
varchar(8)notnull,
出生日期
datetimenull,primarykey(学号),foreignkey(学号)referencesxs1(学号));5.2.3参照完整性约束【例5.13】创建带有参照动作ASCADE的xs1表。createtablexs1(
学号
varchar(6)notnull,
姓名
varchar(8)notnull,
出生日期
datetimenull,primarykey(学号),foreignkey(学号)referencesxs(学号)onupdatecascade);5.2.4CHECK完整性约束CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束,也可定义为表完整性约束。语法格式:CHECK(expr)【例5.14】创建表student,只包括学号和性别两列,性别只能是男或女。createtablestudent(
学号
char(6)notnull,
性别
char(1)notnullcheck(性别
in('男','女')));MySQL中的check约束MySQL所有的存储引擎均不支持check约束,MySQL会对check子句进行分析,但是在插入数据时会忽略,因此check并不起作用,因此实现对数据约束有两种方法:a:在mysql种约束,如使用enum类型或者触发器等。b:在应用程序里面对数据进行检查再插入。
enum类型enum类型将数据的取值限定在在一个离散的集合中。在创建表的时候可以制定数据为enum
如:createtablet(idintauto_incrementprimarykey,sexenum('F','M'),namevarchar(20)notnull);在MySQL中,enum的变量是按照整数与其列表对应,从1开始,比如上面的表中F的索引为1,M的索引为2.表中的输入如下(1,F,z),(2,m,t),(3,F,K)如果要检索根据sex列检索数据,则可以根据枚举中的字符串检索mysql>select*fromtwheresex='F';还可以根据枚举列的索引检索,结果如下,其中1代表F的索引为1。mysql>select*fromtwhe
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 六年级语文上学期整本书阅读《童年》期末复习教学设计
- 初中语文七年级下册紫藤萝瀑布教学设计与实施
- 高二英语选择性必修二 Unit 5 Reading and Thinking 语篇深度解读与默写训练教学设计
- 初中语文九年级中考作文选材教学设计
- 小学五年级美术《生活中的标志》教学设计
- 高一英语必修第一册Unit 3 Sports and fitness 单元整体教学设计
- 高一体育五步拳教学设计
- 小学四年级劳动教育煎牛排教学设计
- 小学四年级劳动《花瓶罩饰》项目化教学设计
- 八年级语文下册第三单元养性怡情知识清单教学设计
- 抖音直播运营中心薪酬绩效考核实施管理方案
- 2026年学生入团考试题库及参考答案
- 2027届河南省部分城市联考高三上学期11月阶段检测历史试题(含答案)
- 2026年宁夏水务投资集团有限公司招聘笔试参考题库附带答案详解析版
- 2026秋北师大版选择性必修第一册Unit 1-3 句子语法填空练习(备考期中)
- 智能体电脑行业王伟东白皮书
- 秦皇岛市建设现代有轨电车项目调研报告
- 2026秋新教材苏教版五年级上册数学第七单元 用字母表示数量关系(一) 教案(共7课时)
- 《中药炮制技术》教案-13模块十三 其他炮制技术
- 2026年辅警笔试的题库及答案
- 多模式镇痛在加速康复外科中的应用现状
评论
0/150
提交评论