数据基础及应用 7

教案一：索引和数据完整性——索引概述与索引操作（2学时）一、授课内容本节课主要讲授MySQL数据库中索引的基本概念、分类以及索引的创建、查看和删除操作，具体包括以下内容：（一）索引概述1.索引的概念索引是与表或视图关联的物理结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键，这些键存储在B树结构中，使MySQL可以快速有效地查找与键值关联的行。表的存储由数据页面和索引页面两部分组成，查询时先搜索索引页面找到指针，再通过指针读取数据。2.索引的优点（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性（2）可以大大加快数据的查询速度，这也是创建索引的主要原因（3）在实现数据的参考完整性方面，可以加速表和表之间的连接（4）在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间3.索引的缺点（1）创建索引和维护索引要耗费时间，随数据量增加所耗费时间也会增加（2）索引需要占用磁盘空间，除数据表空间外每个索引还要占用额外物理空间（3）对数据进行增加、删除和修改时索引也需动态维护，降低了数据维护速度4.索引的分类（1）普通索引（INDEX）最基本的索引类型，索引的列可以包括重复的值。（2）唯一索引（UNIQUE）保证列不包含重复的值，但可以为空。对于多列唯一索引，保证多列值的组合不重复。（3）主键索引（PRIMARYKEY）又称为聚簇索引，使用B-Tree构建，与UNIQUE索引非常类似。一个表只能包含一个PRIMARYKEY，不能为空值。（4）全文索引（FULLTEXT）在定义索引的列上支持值的全文查找，可以在CHAR、VARCHAR或TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。（5）空间索引（SPATIAL）对空间数据类型（GEOMETRY、POINT、LINESTRING、POLYGON）字段建立的索引，只能在MyISAM存储引擎中创建。5.索引的特点所有MySQL列类型都能被索引；索引在存储引擎中实现，不同存储引擎支持的索引类型不同；MyISAM和InnoDB支持BTREE索引，MEMORY/HEAP支持HASH和BTREE索引；可索引列的前缀；支持最多15个列的组合索引。（二）索引的操作1.索引设计准则（1）为表设置索引应适度，避免对经常更新的表进行过多的索引（2）在经常查询的字段上创建索引，但要避免添加不必要的字段（3）不要为包含很少唯一值的列创建索引（如性别字段）（4）组合索引应考虑列的顺序，等值、范围查询和连接条件的列放在最前面（5）当唯一性是数据本身的特征时，指定唯一索引（6）在频繁排序或分组的列上建立索引2.使用Workbench创建索引（1）建表时定义PRIMARYKEY约束或UNIQUE约束对表列定义PRIMARYKEY约束或UNIQUE约束，系统会自动创建索引。（2）使用AlterTable菜单创建索引选择表，右键选择"AlterTable"菜单，单击"Indexes"选项卡，输入索引名称，选择索引类型和包含的列，单击"Apply"按钮。3.使用SQL语句创建索引（1）在创建表的同时创建索引语法格式：CREATETABLEtable_name(col_namedata_type,[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC])。UNIQUE、FULLTEXT和SPATIAL为可选参数，INDEX与KEY为同义词。（2）使用ALTERTABLE语句为已有表创建索引ALTERTABLEtable_nameADDINDEXindex_name(column_list)创建普通索引；ALTERTABLEtable_nameADDUNIQUEindex_name(column_list)创建唯一索引；ALTERTABLEtable_nameADDPRIMARYKEY(column_list)创建主键索引。（3）使用CREATEINDEX语句创建索引CREATEINDEXindex_nameONtable_name(column_list)创建普通索引；CREATEUNIQUEINDEXindex_nameONtable_name(column_list)创建唯一索引。注意不能用CREATEINDEX创建PRIMARYKEY索引。4.查看索引效果（EXPLAIN语句）使用EXPLAIN语句分析SQL查询的执行计划，查看查询是否使用了索引。EXPLAIN可查询：表的读取顺序、操作类型、哪些索引可用和实际使用、表间引用方式、预估扫描行数等。重点关注type、possible_keys、key、key_len、rows等字段。5.复合索引对表的多个列创建索引。复合索引遵循最左前缀原则：只有查询条件中使用了索引字段的最左边的字段时，复合索引才会被使用。例如在(name,gender,national)上建立复合索引，查询条件包含(name)、(name,gender)、(name,gender,national)时会使用，包含(gender,national)时不会使用。6.查看索引（1）使用Workbench查看索引（2）使用SQL语句查看索引SHOWINDEXFROMtable_name;可以查看表中所有索引的详细信息。7.删除索引（1）使用Workbench删除索引在索引查看界面中右键选择"DeleteSelected"，点击"Apply"按钮。（2）使用SQL语句删除索引ALTERTABLEtable_nameDROPINDEXindex_name删除普通索引和唯一索引；ALTERTABLEtable_nameDROPPRIMARYKEY删除主键索引；DROPINDEXindex_nameONtable_name删除索引。二、教学目标（一）知识目标1.理解索引的概念、作用和基本原理2.掌握索引的分类：普通索引、唯一索引、主键索引、全文索引、空间索引3.掌握创建索引的多种方式：CREATETABLE、ALTERTABLE、CREATEINDEX4.掌握使用EXPLAIN语句查看索引效果5.理解复合索引的最左前缀原则6.掌握查看和删除索引的方法（二）能力目标1.能够根据实际查询需求设计合理的索引方案2.能够使用SQL语句创建、查看和删除索引3.能够使用EXPLAIN分析查询执行计划，判断是否使用了索引4.能够正确使用复合索引并遵循最左前缀原则（三）素养目标1.培养学生权衡索引利弊的数据库优化意识2.增强学生根据实际业务场景选择合适索引策略的综合分析能力三、教学重点与难点（一）教学重点1.索引的分类和各类索引的特点2.使用ALTERTABLE和CREATEINDEX创建索引3.使用EXPLAIN语句查看索引效果4.复合索引的最左前缀原则（二）教学难点1.索引的底层原理（B树结构）和查询优化器的工作机制2.复合索引的最左前缀原则的理解和应用3.索引设计准则——在什么场景下应该或不应该创建索引4.EXPLAIN执行计划中各字段的含义和分析四、教学实施（一）导入环节（5分钟）通过类比引导学生思考：大家在使用字典或书本时，如何快速找到需要的内容？——通过目录。数据库中的索引就像书本的目录，可以帮助快速定位数据，而无需扫描整张表。在大数据量的情况下，没有索引的查询可能需要几秒甚至几分钟，有了索引可能只需要几毫秒。本节课将系统学习索引的概念和操作。（二）讲授环节（55分钟）1.索引概述（20分钟）（1）讲解索引的概念和基本原理类比书本目录，讲解索引页面和数据页面的关系，以及B树结构的基本思想。（2）讨论索引的优缺点从查询加速、唯一性保证、连接加速等方面讲解优点；从时间消耗、空间占用、维护开销等方面讲解缺点。引导学生思考"索引是不是越多越好"。（3）讲解索引的五种分类普通索引、唯一索引、主键索引、全文索引、空间索引，对比各自的特点和使用场景。（4）讲解索引的特点存储引擎对索引的支持差异（BTREEvsHASH）、前缀索引、组合索引。2.索引的操作（35分钟）（1）讲解索引设计准则从适度原则、查询频率、唯一值、列顺序、排序分组等维度讲解索引设计应考虑的因素。通过反面案例说明不当索引设计的问题。（2）演示使用Workbench创建索引演示建表时定义主键约束自动创建索引，以及通过AlterTable菜单手动创建索引。（3）讲解并演示使用SQL语句创建索引演示例5-1：CREATETABLE同时创建唯一索引。演示例5-2：ALTERTABLE创建普通索引。演示例5-3：ALTERTABLE创建唯一索引。对比三种创建方式的适用场景。（4）讲解并演示EXPLAIN语句演示EXPLAINSELECT*FROMstudentWHEREname='周'，逐字段解读执行计划：select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。（5）讲解复合索引和最左前缀原则演示ALTERTABLEstudentADDINDEX(name,gender,national)，分析不同查询条件下索引是否被使用。（6）演示查看和删除索引演示例5-4：SHOWINDEXFROMcourse。演示例5-5~5-7：三种删除索引的方式。（三）实践环节（25分钟）【课堂练习】学生根据student数据库，完成以下索引操作：练习1：在student表的major列上使用ALTERTABLE创建普通索引练习2：在course表的name和category列上创建复合索引练习3：使用EXPLAIN分析SELECT*FROMstudentWHEREmajor='通信工程'的执行计划，确认是否使用了索引练习4：使用EXPLAIN对比分析以下两个查询的执行计划差异：WHEREmajor='通信工程'和WHEREcategory='必修'练习5：使用SHOWINDEX查看student表的索引信息练习6：删除练习1中创建的索引，并使用DROPINDEX语句再次创建后删除（四）课堂总结（5分钟）1.提问：索引有哪五种分类？各自有什么特点？2.提问：创建索引有哪些方式？3.提问：什么是复合索引的最左前缀原则？4.总结本节课重点，布置课后习题（教材习题5的相关部分）五、总结与反思（一）本节课总结本节课系统学习了索引的概念和操作。索引是与表关联的物理结构，通过B树存储键值来加速数据检索。索引分为普通索引、唯一索引、主键索引、全文索引和空间索引五种类型。创建索引有多种方式：建表时同时创建、ALTERTABLE、CREATEI