MySQL数据库 4.2 索引的开发教案

教案首页课程名称MySQL数据库开发本节课题任务4.2索引的开发授课方式理实一体化教学参考及教具姜云桥主编.MySQL数据库开发.临科院：教务处,2023计算机MySQL数据库软件多媒体教学系统资料包教学目标及基本要求了解索引了解索引的分类理解索引的设计原则掌握创建索引的方法掌握管理索引的方法能创建索引、管理索引培养自主学习能力教学重点教学难点索引的分类、创建索引、管理索引管理索引教学小结作业及要求按照任务书做好预习任务创建和管理索引教后反思注意：用微课视频每个典型操作做示范

教案用纸教学内容、方法和过程附记任务4.2索引的开发【任务要求】本任务将从认识索引、索引的分类以及索引的设计原则等方面着手，介绍创建和管理索引的方法。特别要注意的是，索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。【任务必备知识】4.2.1索引的概念索引是？索引：是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。在MySQL中，所有的数据类型都可以被索引。MySQL支持的索引主要有？Hash索引和B-Tree索引。目前，大部分MySQL索引都是以B-树（BTREE）方式存储的，是MySQL数据库中使用最为频繁的索引类型，除了Archive存储引擎之外的其他所有的存储引擎都支持B-Tree索引。不仅在MySQL中如此，在其他的很多数据库管理系统中B-Tree索引也同样是作为最主要的索引类型的，这主要是因为B-Tree索引的存储结构在数据库的数据检索中有着非常优异的表现。一般来说，MySQL中的B-Tree索引的物理文件大多是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的叶子结点，而且到任何一个叶子结点的最短路径的长度都是完全相同的，所以把它称为B-Tree索引。MySQLHash索引相对于B-Tree索引，检索效率要高上不少。虽然Hash索引效率高，但是，由于Hash索引本身的特殊性也带来了很多限制和弊端，主要有以下内容。区别（1）MySQLHash索引仅仅能满足“=”，“IN”和“<=>”查询，不能使用范围查询。（2）MySQLHash索引无法被用来避免数据的排序操作。（3）MySQLHash索引不能利用部分索引键查询。（4）MySQLHash索引在任何时候都不能避免表扫描。（5）MySQLHash索引遇到大量Hash值相等的情况后，性能并不一定就会比B-Tree索引高。索引分类MySQL的索引包括普通索引（INDEX）、唯一性索引（UNIQUE）、主键（PRIMARYKEY）、全文索引（FULLTEXT）和空间索引（SPATIAL）。（1）普通索引（INDEX） 索引的关键字是INDEX，这是最基本的索引，它没有任何限制。（2）唯一性索引（UNIQUE） 关键字是UNIQUE。与普通索引类似，但是UNIQUE索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。在一个表上可以创建多个UNIQUE索引。（3）主键索引（PRIMARYKEY） 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。也可通过修改表的方法增加主键，但一个表只能有一个主键索引。（4）全文索引（FULLTEXT） FULLTEXT索引只能对CHAR、VARCHAR和TEXT类型的列编制索引，并且只能在MyISAM表中编制。在MySQL默认情况下，对于中文作用不大。（5）空间索引（SPATIAL） SPATIAL索引只能对空间列编制索引，并且只能在MyISAM表中编制。本书不讨论。另外，按索引建立在一列还是多列上，又可以分为单列索引、多列索引（复合索引）。4.2.2索引的建立创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTERTABLE语句来创建索引。下面将详细讲解这3种创建索引的方法。一、创建表时创建创建表的时候可以直接创建索引，这种方式最简单、方便。主键索引和唯一性索引在前面讲解约束的时候实际上已经完成了创建，也就说我们在创建主键约束和唯一性约束的时候会自动添加索引，我们主要讲解普通索引的创建方式，语法同创建表时一样，不过关键字是index（col_name,…）。【任务4.2.1】创建表student2，s_no为主键索引，s_name为唯一性索引，并在s_address列上前6位字符创建索引。mysql>createtablestudent2->(->s_nochar(6)primarykeycomment'学号',->s_namevarchar(20)uniquecomment'姓名',->sexchar(2)default'男'comment'性别',->birthdatecomment'出生日期',->d_nochar(6)comment'所在系部',->addressvarchar(20)comment'家庭地址',->phonevarchar(20)comment'联系电话',->indexaddress_index(address(6))->);查看创建的索引：mysql>showindexfromstudent2\G二、用createindex语句创建如果表已建好，可以使用createindex语句建立索引。createindexindex_nameontbl_name(col_name(length)[asc|desc],...)1.创建普通索引【任务4.2.2】student的d_no字段经常作为查询条件，建立普通索引。mysql>createindexd_no_indexonstudent(d_no);2.创建唯一性索引像课程表的课程名，部门表的部门名，商品表的商品名之类的字段，一般情况下是，可建立一个唯一性索引。【任务4.2.3】在course表的c_name列上建立一个唯一性索引c_name_index。mysql>createuniqueindexc_name_indexoncourse(c_name);3.创建多列索引可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。【任务4.2.4】在score表的s_no和c_no列上建立一个复合索引score_index。mysql>createindexscore_indexonscore(s_no,c_no);分析与讨论：1）对于char和varchar列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法。blob和text列也可以编制索引，但是必须给出前缀长度。2）createindex语句并不能创建主键。3）索引名可以不写，若不写索引名，则默认与列名相同。三、通过altertable语句创建索引在已经存在的表上可以用altertable语句创建索引。altertabletbl_nameadd{unique|index|primarykey}[index_name](col_name(length)[asc|desc],...)【任务4.2.5】在teacher表上建立t_no主键索引（还未建立主键），建立t_name和d_no的复合索引，以加速表的检索速度。mysql>altertableteacher->addprimarykey(t_no),->addindexmark_index(t_name,d_no);【任务4.2.6】在department表中的d_name创建唯一性索引。mysql>altertabledepartment->adduniqueindexd_name_index(d_name);4.2.3索引的查看如果想要查看表中创建的索引的情况，可以使用showindexfromtbl_name语句。4.2.4索引的删除删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。对于已经存在的索引，可以通过dropindexindex_nameontbl_name来删除。分析与讨论:1)删除唯一性索引，如同删除普通索引一样，用dropindex语句即可，不能写成dropuniqueindex，但是创建唯一性索引要写成adduniqueindex。2)删除主键索引，则需要altertable直接使用dropprimarykey子句进行删除，不需要提供索引名称，因为一个表中只有一个主键。尽管索引能够显著提高查找效率，但也并非越多越好，在设计索引的时候应该遵循如下原则：1)在主键上创建索引，在InnoDB中如果通过主键来访问数据效率是非常高的。2)为经常需要排序、分组和联合操作的字段建立索引，即那些将用于join、where判断和orderby排序的字段上。3)尽量不要对数据库中某个含有大量重复的值的字段建立索引，如“性别”字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。4)尽量使用数据量少的索引。5)尽量使用前缀来索引。【项目实践】在rsgl数据库中完成下列操作。1.在employee表上通过createindex语句在birth字段创建名为index_birth的索引，创建完成后可查询到如下输出（省略了部分输出）：mysql>showindexfromemployee\G***************************2.row***************************Table:employeeNon_unique:1Key_name:index_birthSeq_in_index:1Column_name:birth……2.在employee表上通过altertable语句在e_name字段创建名为index_name的唯一性索引，创建完成后可查询到如下输出（省略部分输出）：mysql>showindexfromemployee\G***************************3.row***************************Table:employeeNon_unique:1Key_name:index_nameSeq_in_index:1Column_name:e_name……3.在employee表上通过createindex语句在d_id和education字段上创建多列索引。***************************4.row***************************Table:employeeNon_unique:1Key_name:index_mulSeq_in_index:1Column_name:d_id……***************************5.row***************************Table:employeeNon_unique:1Key_name:index_mulSeq_in_index:2Column_n