索引与数据完整性约束

第5章索引与数据完整性约束5.1索引5.2数据完整性约束5.1索引5.1.1索引的分类目前大部分MySQL索引都是以B-树（BTREE）方式存储的。BTREE方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。这样，表中的每一行都会在索引中有一个对应值。查询的时候就可以根据索引值直接找到所在的行。5.1索引5.1.1索引的分类索引中的节点是存储在文件中的，所以索引也是要占用物理空间的，MySQL将一个表的索引都保存在同一个索引文件中。如果更新表中的一个值或者向表中添加或删除一行，MySQL会自动地更新索引，因此索引树总是和表的内容保持一致。按BTREE形式存储的主要索引类型介绍如下。1.普通索引（INDEX）这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。2.唯一性索引（UNIQUE）这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。5.1.1索引的分类3.主键（PRIMARYKEY）主键是一种唯一性索引，它必须指定为“PRIMARYKEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。4.全文索引（FULLTEXT）除了BTREE索引，MySQL还支持哈希索引（HASH）。使用哈希索引，不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。当根据一个值获取一个特定的行时，哈希索引非常快。5.1.2创建索引1.使用CREATEINDEX语句使用CREATEINDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。语法格式：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name[USINGindex_type]ONtbl_name(index_col_name,...)其中，index_col_name格式为：col_name[(length)][ASC|DESC]5.1.2创建索引说明：●index_name：索引的名称，索引在一个表中名称必须是唯一的。●USINGindex_type：MySQL支持的索引类型有BTREE和HASH。如果不指定USING子句，MySQL自动创建一个BTREE索引。●index_col_name：col_name表示创建索引的列名。length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。在某些情况下，只能对列的前缀进行索引。例如，索引列的长度有一个最大上限，因此，如果索引列的长度超过了这个上限，那么就可能需要利用前缀进行索引。BLOB或TEXT列必须用前缀索引。前缀最长为255字节，但对于MyISAM和InnoDB表，前缀最长为1000字节。5.1.2创建索引可以看出，CREATEINDEX语句并不能创建主键。【例5.1】根据XS表的学号列上的前5个字符建立一个升序索引XH_XS。CREATEINDEXXH_XSONXS(学号(5)ASC);可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。【例5.2】在XS_KC表的学号列和课程号列上建立一个复合索引XSKC_IN。CREATEINDEXXSKC_INONXS_KC(学号,课程号);5.1.2创建索引2.使用ALTERTABLE语句语法格式如下：ALTER[IGNORE]TABLEtbl_nameADDINDEX[index_name][index_type](index_col_name,...)/*添加索引*/|ADD[CONSTRAINT[symbol]]PRIMARYKEY[index_type](index_col_name,...)/*添加主键*/|ADD[CONSTRAINT[symbol]]UNIQUE[index_name][index_type](index_col_name,...)/*添加唯一性索引*/|ADD[CONSTRAINT[symbol]]FOREIGNKEY[index_name](index_col_name,...)[reference_definition]/*添加外键*/5.1.2创建索引说明：●index_type：语法格式为USING{BTREE|HASH}。当定义索引时默认索引名，则一个主键的索引叫做“PRIMARY”，其他索引使用索引的第一个列名作为索引名。如果存在多个索引的名字以某一个列的名字开头，就在列名后面放置一个顺序号码。●CONSTRAINT[symbol]：为主键、UNIQUE键、外键定义一个名字。这个将在命名完整性约束一节中介绍。5.1.2创建索引【例5.3】在XS表的姓名列上创建一个非唯一的索引。ALTERTABLEXSADDINDEXXS_XMUSINGBTREE(姓名);【例5.4】以XS表为例（假设XS表中主键未定），创建这样的索引，以加速表的检索速度：ALTERTABLEXSADDPRIMARYKEY(学号),ADDINDEXmark(出生日期,性别);记住，使用PRIMARYKEY的列，必须是一个具有NOTNULL属性的列。5.1.2创建索引3.在创建表时创建索引语法格式：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name[([column_definition],...|[index_definition])][table_option][select_statement];其中，index_definition为索引项：[CONSTRAINT[symbol]]PRIMARYKEY[index_type](index_col_name,...)/*主键*/|{INDEX|KEY}[index_name][index_type](index_col_name,...)/*索引*/|[CONSTRAINT[symbol]]UNIQUE[INDEX][index_name][index_type](index_col_name,...)/*唯一性索引*/|[CONSTRAINT[symbol]]FOREIGNKEY[index_name](index_col_name,...)[reference_definition]/*外键*/5.1.2创建索引【例5.5】创建XS_KC表的语句如下，XS_KC表带有学号和课程号的联合主键，并在成绩列上创建索引。CREATETABLEXS_KC(学号CHAR(6)NOTNULL,课程号CHAR(3)NOTNULL,成绩TINYINT(1),学分TINYINT(1),PRIMARYKEY(学号,课程号),INDEXCJ(成绩));5.1.3删除索引1.使用DROPINDEX语句删除索引语法格式：DROPINDEXindex_nameONtbl_name【例5.6】删除XS表上的XS_XH索引。DROPINDEXXS_XHONXS;2.使用ALTERTABLE语句删除索引语法格式：ALTER[IGNORE]TABLEtbl_name|DROPPRIMARYKEY/*删除主键*/|DROPINDEXindex_name/*删除索引*/|DROPFOREIGNKEYfk_symbol/*删除外键*/5.1.3删除索引其中，DROPINDEX子句可以删除各种类型的索引。使用DROPPRIMARYKEY子句时不需要提供索引名称，因为一个表中只有一个主键。【例5.7】删除XS表上的主键和mark索引。ALTERTABLEXSDROPPRIMARYKEY,DROPINDEXmark;如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。5.1.4界面方式创建和删除索引(略)5.1.5索引对查询的影响现在假设有一个表，表里只有一列，由数值1～1000的1000行组成，现在要想查找到数字1000所在的行。如果没有索引，要从第一行开始匹配，若数值不是1000，则转到下一行进行匹配，这样直到第1000行的时候才能找到数字1000所在行，也就是说服务器进行了1000次的运算。而当在该列上创建一个索引后，则可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表扫描至少快了100倍。5.1.5索引对查询的影响当执行涉及多个表的连接查询时，索引将更有价值。假如有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别由含有数值1～1000的1000行组成。查找对应值相等的表行组合的查询如下。此查询的结果应该为1000行，每个组合包含3个相等的值。如果在无索引的情况下处理此查询，则不可能知道哪些行包含这些值。因此，必须寻找出所有组合以便得出与WHERE子句相配的那些组合。可能的组合数目为1000×1000×1000（10亿），比匹配数目多100万倍。很多工作都浪费了，并且这个查询将会非常慢。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下。5.1.5索引对查询的影响（1）从表t1中选择第一行，查看此行所包含的值。（2）使用表t2上的索引，直接跳到t2中与来自t1的值匹配的行。类似地，利用表t3上的索引，直接跳到t3中与来自t1的值匹配的行。（3）进到表t1的下一行并重复前面的过程，直到t1中所有的行已经查询过。在此情形下，同样对表t1执行一个完全扫描，但能够在表t2和t3上进行索引查找，直接取出这些表中的行。从理论上说，这时的查询比未用索引时要快100万倍。如上所述，MySQL利用索引加速了WHERE子句中与条件相匹配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。5.1.6索引的弊端首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。但是这些弊端并不妨碍索引的应用，因为索引带来的好处已经基本掩盖了它的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的。5.2数据完整性约束5.2.1主键约束主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARYKEY约束来创建主键，而且PRIMARYKEY约束中的列不能取空值。由于PRIMARYKEY约束能确保数据的唯一，所以经常用来定义标志列。当为表定义PRIMARYKEY约束时，MySQL为主键列创建唯一性索引，实现数据的唯一性，在查询中使用主键时，该索引可用来对数据进行快速访问。如果PRIMARYKEY约束是由多列组合定义的，则某一列的值可以重复，但PRIMARYKEY约束定义中所有列的组合值必须唯一。5.2数据完整性约束可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARYKEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARYKEY（col_name，…）语句。【例5.8】创建表XS1，将姓名定义为主键。CREATETABLEXS1(学号varchar(6)NULL,姓名varchar(8)NOTNULLPRIMARYKEY,出生日期datetime);5.2.1主键约束【例5.9】创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中学号、