数据基础及应用 4

第5章索引和数据完整性5.3实施数据完整性索引概述5.2索引的操作CONTENTS目录

5.15.1索引概述5.1.1索引的概念索引是与表或视图关联的物理结构，可以加快从表或视图中检索行的速度索引包含由表或视图中的一列或多列生成的键，这些键存储在一个B树结构中，使MySQL可以快速有效地查找与键值关联的行索引的优点：（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性（2）可以大大加快数据的查询速度，这也是创建索引的主要原因5.1.1索引的概念（续）（3）在实现数据的参照完整性方面，可以加速表和表之间的连接（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间增加索引的不利方面：（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加（2）索引需要占用磁盘空间，每一个索引还要占用一定的物理空间（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护5.1.2索引的分类（1）普通索引（index/key）：索引的列可以包括重复的值（2）唯一索引（unique）：保证了列不包含重复的值，但可以为空（3）主键索引（primarykey）：又称为聚簇索引，使用B-Tree构建（4）全文索引（fulltext）：在定义索引的列上支持值的全文查找（5）空间索引（spatial）：对空间数据类型的字段建立的索引只包含单个列的索引叫做单列索引，也可以组合多个列以创建复合索引5.1.3索引的特点所有的MySQL列类型都能被索引索引是在存储引擎中实现的，每种存储引擎的索引不一定完全相同对于CHAR和VARCHAR列，可以索引列的前缀MySQL能对多个列创建组合索引，一个组合索引可以由最多15个列组成5.1.3索引的特点（续）设计索引的准则：（1）根据需要为表设置索引，避免对经常更新的表进行过多的索引（2）避免对经常更新的表进行过多的索引，应该在经常查询的字段上创建索引（3）不要为包含很少唯一值的列创建索引（4）如果索引包含多个列，应考虑列的顺序（5）当唯一性是某种数据本身的特征时，指定唯一索引（6）在频繁进行排序或分组的列上建立索引5.2索引的操作5.2.1使用Workbench创建索引1．建表时定义PRIMARYKEY或UNIQUE约束，系统自动创建索引2．使用Workbench创建索引步骤：（1）选择要创建索引的表，单击右键选择AlterTable菜单（2）单击Indexes（索引）选项卡（3）输入索引名称，选择索引类型（PRIMARY/UNIQUE/普通）（4）选择包含在索引中的列（5）单击Apply按钮创建索引5.2.2使用SQL语句创建索引1．在创建表的同时创建索引：CREATETABLEtable_name(col_namedata_type,[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]);5.2.2使用SQL语句创建索引（续）例5-1创建表student_3，在name列上设置唯一索引：CREATETABLEstudent_3(nameVARCHAR(20)NOTNULL,briefTEXTNOTNULL,UNIQUEINDEXname_UNIQUE(nameDESC));5.2.2使用SQL语句创建索引（续）2．使用ALTERTABLE语句为已有表创建新的索引：ALTERTABLEtable_nameADDINDEXindex_name(column_list);ALTERTABLEtable_nameADDUNIQUEindex_name(column_list);ALTERTABLEtable_nameADDPRIMARYKEY(column_list);5.2.2使用SQL语句创建索引（续）例5-2在学生表的姓名列上建立索引：ALTERTABLEstudentADDINDEX(name);例5-3在课程表的代码列和名称列上建立唯一索引：ALTERTABLEcourseADDUNIQUE(id,name);5.2.3查看索引效果使用EXPLAIN语句来分析SQL查询的执行计划可以查看该SQL查询有没有使用索引，有没有做全表扫描EXPLAIN输出字段说明：（1）select_type：所使用的SELECT查询类型（2）table：读取的数据表的名字（3）type：本数据表与其他数据表之间的关联关系5.2.3查看索引效果（续）（4）possible_keys：给出了MySQL在搜索数据记录时可选用的各个索引（5）key：是MySQL实际选用的索引（6）key_len：索引按字节计算的长度（7）ref：关联关系中另一个数据表里的数据列名（8）rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数（9）Extra：提供了与关联操作有关的信息5.2.4复合索引复合索引是指对表的多个列创建索引以下语句在student表的姓名、性别、民族列上创建了复合索引：ALTERTABLEstudentADDINDEX(name,gender,national);只有在查询条件中使用了这些索引字段的左边字段时，复合索引才会被使用，使用复合索引时遵循最左前缀集合在本例中，当查询条件中包含(name)、(name,gender)或(name,gender,national)时，复合索引才会被使用5.2.5查看与修改索引使用SQL语句查看索引：SHOWINDEXFROMtable_name;例5-4查看课程表的索引：SHOWINDEXFROMcourse;输出字段说明：Table：表名Non_unique：是否非唯一（0=唯一，1=可重复）Key_name：索引名称Column_name：列名5.2.6删除索引删除索引的SQL语句：ALTERTABLEtable_nameDROPINDEXindex_name;ALTERTABLEtable_nameDROPPRIMARYKEY;DROPINDEXindex_nameONtable_name;注意：删除PRIMARYKEY索引时，不需要索引名，因为一个表只有一个PRIMARYKEY5.2.6删除索引（续）例5-5删除学生表内名为id的索引：ALTERTABLEstudentDROPINDEXid;例5-6删除课程表的主键约束索引：ALTERTABLEcourseDROPPRIMARYKEY;5.3实施数据完整性5.3.1数据完整性的概念数据完整性是指数据的正确性、有效性和一致性具体来讲，完整性包括：（1）实体完整性：保证每行数据是唯一的可识别的（2）域完整性：保证列的数据符合要求的取值范围（3）参照完整性：保证表之间的引用关系正确（4）用户自定义完整性：根据具体业务逻辑的数据规则设置5.3.2主键约束（PRIMARYKEY）主键约束用于唯一标识表中的每一行一个表只能包含一个PRIMARYKEY约束在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL定义列级主键约束：[CONSTRAINTconstraint_name]PRIMARYKEY定义表级主键约束：[CONSTRAINTconstraint_name]PRIMARYKEY(column_name[,...])5.3.2主键约束（续）例5-8为选课表增加主键约束，设置学号和课程编号的组合为主键：ALTERTABLEstudent_courseADDCONSTRAINTPK_st_id_course_idPRIMARYKEY(student_id,course_id);注意：（1）一个表只能包含一个PRIMARYKEY约束（2）在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL5.3.3外键约束（FOREIGNKEY）外键约束定义了表与表之间的关系，用于建立和加强两个表的数据之间的连接定义表级外键约束的语法：[CONSTRAINTconstraint_name]FOREIGNKEY(column_name[,...])REFERENCESref_table[(ref_column[,...])][ONDELETE{CASCADE|NOACTION}]5.3.3外键约束（FOREIGNKEY）（续）[ONUPDATE{CASCADE|NOACTION}]CASCADE：级联操作，当主键表中某行被删除或更新时，外键表中相应数据也做相同的删除或更新操作5.3.3外键约束（续）例5-9为选课表增加两个外键约束：ALTERTABLEstudent_courseADDCONSTRAINTFK_st_idFOREIGNKEY(student_id)REFERENCESstudent(id);ALTERTABLEstudent_courseADDCONSTRAINTFK_course_idFOREIGNKEY(course_id)REFERENCEScourse(id);5.3.3外键约束（续）

注意：（1）FOREIGNKEY约束只能引用所引用的表的PRIMARYKEY或UNIQUE约束中的列（2）FOREIGNKEY约束仅能引用位于同一服务器上的同一数据库中的表5.3.4非空值约束（NOTNULL）非空值约束限制的数据列不能为空当表数据发生变化时，对于有非空值约束的字段必须给出确定的值使用SQL语句设置NOTNULL约束：ALTERTABLE表名MODIFYCOLUMN列名数据类型NOTNULL;5.3.4非空值约束（NOTNULL）（续）例5-10为课程表的课程名称字段设置非空值约束：ALTERTABLEcourseMODIFYCOLUMNnameVARCHAR(20)NOTNULL;注意：NULL不是零或空白，NULL表示该列的值未知或没有值5.3.5唯一性约束（UNIQUE）唯一性约束限制约束的列在表的范围内不允许有两行包含相同的非空值唯一性约束的特点：（1）唯一性约束用于限定非主键的一列或多列组合的取值不重复（2）一个表可以定义多个唯一性约束，但只能定义一个主键约束（3）主键约束不能用于定义允许空值的列（4）每个UNIQUE约束都生成一个索引5.3.5唯一性约束（UNIQUE）（续）例5-11创建表student_2，要求学生身份证号st_identity具有唯一性：CREATETABLEstudent_2(st_idCHAR(8),st_identityCHAR(18),CONSTRAINTuk_st_identityUNIQUE(st_identity));5.3.6检查约束（CHECK）检查约束对输入列或整个表中的值设置检查条件，通常是一个取值范围以限制输入值定义检查约束的语法：[CONSTRAINTconstraint_name]CHECK(logical_expression)5.3.6检查约束（CHECK）（续）

例5-12为选课表的成绩列添加检查约束：ALTERTABLEstudent_courseADDCONSTRAINTmark_checkCHECK(mark>=0ANDmark<=100);注意：（1）对每列可以指定多个CHECK约束（2）列级CHECK约束只能引用被约束的列5.3.7默认值约束（DEFAULT）默认约束通过定义列的默认值，确保在没有为某列指定数据时由MySQL来指定列的值定义默认约束的语法：[CONSTRAINTconstraint_name]DEFAULTconstant_expression5.3.7默认值约束（DEFAULT）（续）注意：（1）每列中只能有一个默认约束（2）默认约束只能用于INSERT语句（3）约束表达式不能应用于数据类型为timestamp的列和具有AUTO_INCREMENT属性的列例5-13修改学生表，设置is_bonus字段默认约束为0：ALTERTABLEstudentMODIFYCOLUMNis_bonusintDEFAULT0;本章小结索引是与表或视图关联的物理结构，包含由一列或多列生成的键，存储在B树结构中，可大大加快数据库的检索速度。索引分类：普通索引、唯一索引、主键索引