《新编数据库技术-MySQL》电子教案 第6章 数据库视图与索引

《新编数据库技术--MySQL》教案（学期名称）第13-14讲授课日期授课班级教案主题数据库视图与索引应用学时分配（线下课程，本单元学时数）4（混合式课程，线上/线下学时数）4主讲教师（本单元授课教师，可以多人）教学目标理解视图的虚拟表特性及与物理表的本质区别，能准确阐述视图在数据安全和查询简化中的作用。

掌握视图创建（单表/多表）、修改、删除的SQL语法，能独立完成图书馆借阅系统中的视图设计案例。

了解视图算法（TEMPTABLE/MERGE）的执行机制，能分析不同算法对视图更新和查询性能的影响。

掌握索引的分类（普通/唯一/主键/复合）及创建方法，能通过实验验证索引对查询效率的优化效果。

理解索引的存储代价与维护成本，能根据业务场景制定合理的索引策略（如频繁查询字段优先建索引）。

综合运用视图和索引解决实际问题，如设计多表关联视图简化借阅统计，并通过复合索引提升查询性能。

教学重点1.视图的创建与更新

解决方案：采用"图书馆学生信息管理"案例，分步骤演示单表视图（CREATEVIEWv_studentASSELECT...）和多表视图（关联tb_student与tb_borrow）的创建过程，通过对比UPDATE语句在TEMPTABLE与MERGE算法视图上的执行结果（如案例6.9），明确可更新视图的限制条件（无聚合/分组/外连接）。

2.索引的分类与适用场景

解决方案：设计"索引效率对比实验"，使用10万行数据的tb_test1（无索引）和tb_test2（name列普通索引），通过SELECTname='name543'的执行时间差（0.047秒vs0.000秒）直观展示索引作用；结合学生表（school+major复合索引）和图书表（ISBN唯一索引）案例，归纳各类索引的适用场景。

3.视图与索引的综合应用

解决方案：以"图书馆借阅统计系统"为项目载体，要求学生先创建包含学号、书名、借阅时间的多表视图v_record，再基于该视图创建借阅量统计视图v_borrowing，最后对v_record中的stu_num字段添加索引优化查询，通过SHOWINDEX验证索引有效性，并分析执行计划（EXPLAIN）中的type字段变化。

教学难点1.视图算法的执行原理

解决方案：通过流程图对比TEMPTABLE（先执行视图查询生成临时表）与MERGE（将视图逻辑合并到外部查询）的执行步骤，结合案例6.8中视图v_student2（TEMPTABLE）无法更新而v_student3（MERGE）可更新的现象，辅以SQL执行时间日志分析，帮助学生理解算法选择对性能的影响。

2.复合索引的最左匹配原则

解决方案：设计"学生表复合索引验证"实验，创建index_sm(school,major)后，通过EXPLAIN分析以下查询的索引使用情况：WHEREschool='信息学院'（使用索引）、WHEREmajor='计算机'（不使用索引）、WHEREschool='信息学院'ANDmajor='计算机'（使用索引），总结最左前缀匹配规律。

3.索引设计的权衡策略

解决方案：引入"索引成本模型"，通过案例6.12中10万行数据插入耗时对比（有索引表比无索引表慢20%），讨论索引对INSERT/UPDATE的性能损耗，结合图书馆系统中"借阅记录表频繁插入但查询少"的场景，引导学生制定"查询频繁字段建索引，更新频繁字段少建索引"的策略。

教学设计思路采用"问题驱动-案例实践-原理探究-综合应用"的四阶教学模式，以大学图书馆借阅管理系统为贯穿案例，将抽象的数据库概念转化为可操作的业务场景。

1.情境导入：通过图书馆管理员"查询信息学院学生借阅记录"的真实需求，引出视图简化多表查询和索引加速检索的必要性，展示无视图时需编写6表连接的复杂SQL，激发学生学习兴趣。

2.分层教学：视图模块按"概念→操作→算法→应用"递进，先通过可视化工具（如Navicat）展示视图结构，再动手实践单表/多表视图创建；索引模块采用"对比实验→原理剖析→规则归纳"路径，从10万行数据查询耗时差异切入，延伸到B+树索引结构原理。

3.虚实结合：使用MySQLWorkbench作为实操环境，同步结合动画演示视图数据动态生成过程和索引B+树结构，解决物理表与虚拟表、索引存储与查询路径的抽象理解难题。

4.过程性评价：设置"视图安全审计"（找出含敏感字段的视图漏洞）和"索引失效诊断"（分析使用函数/模糊查询导致索引失效的SQL）两个任务点，通过小组互评和教师点评强化知识应用能力。

教学过程1.导入环节（15分钟）

-问题情境：展示图书馆管理员需查询"信息学院17级学生借阅的TP类图书"的业务需求，对比直接编写多表连接SQL（涉及tb_student、tb_borrow、tb_book、tb_bibliography）与使用预定义视图的代码量差异，引出视图的"简化操作"优势；通过10万条图书记录无索引查询耗时（0.5秒）与有索引查询耗时（0.01秒）的对比视频，引出索引的性能优化作用。

-知识回顾：提问"SELECT*FROMtb_studentWHEREschool='信息学院'"的执行过程，复习表扫描原理，为索引学习铺垫。

2.新课讲授（60分钟）

-视图基础（20分钟）

①概念辨析：展示视图与表的存储结构对比图（视图仅存储SQL逻辑，表存储物理数据），结合案例6.1中v_student视图的数据来源（动态引用tb_student），强调"虚拟表"特性。

②操作实践：以"创建信息学院学生视图"为例，分步演示CREATEVIEW语法，要求学生同步在MySQL中操作，并使用DESCv_student验证结构；通过ALTERVIEW修改视图条件（添加年级筛选）和DROPVIEW删除操作，强化语法记忆。

-视图进阶（15分钟）

①多表视图：解析案例6.6中v_record视图的创建过程（LEFTJOIN连接4张表），重点讲解字段别名（s_name/b_name）的必要性，避免列名冲突；指导学生使用SHOWCREATEVIEWv_record查看视图定义SQL。

②算法选择：演示案例6.8中v_student2（TEMPTABLE）和v_student3（MERGE）的创建，执行UPDATEname='周睿睿1'命令，对比报错结果（1288错误vs成功更新），总结MERGE算法的优势及使用场景。

-索引核心（25分钟）

①类型讲解：结合案例6.13-6.15，分别演示CREATEINDEX（普通索引）、UNIQUE（ISBN字段）、PRIMARYKEY（id字段）、复合索引（school+major）的创建语法，使用SHOWINDEXFROMtb_student查看索引详情。

②效率实验：指导学生运行存储过程auto_insert生成10万行测试数据，执行SELECT*FROMtb_test1WHEREname='name543'（全表扫描）和tb_test2相同查询（索引扫描），记录执行时间并分析EXPLAIN结果（type:ALLvsref）。

3.课堂练习（30分钟）

-基础任务：为图书馆数据库创建"计算机专业学生借阅视图v_cs_borrow"，要求包含学号、姓名、书名、借阅日期，需连接3张表并筛选major='计算机'，完成后使用SELECT验证数据正确性。

-拓展任务：针对v_cs_borrow视图的查询需求，分析应在哪些基表字段上创建索引，并写出CREATEINDEX语句，通过EXPLAINSELECT*FROMv_cs_borrowWHEREstu_num='17130202'验证索引是否被使用。

4.总结与答疑（15分钟）

-知识梳理：用思维导图回顾视图（创建/更新/算法）和索引（分类/创建/优化）的核心知识点，强调可更新视图的"三不原则"（不聚合、不分组、不外联）和索引设计的"三适原则"（适场景、适字段、适数量）。

-答疑解惑：针对学生提出的"视图嵌套视图是否影响性能"（如v_borrowing基于v_record），通过执行计划分析（EXPLAINSELECT*FROMv_borrowing）说明优化器会合并视图逻辑，无需过度担忧性能问题。

课后作业与评价1.基础作业（40%）

-必做题：完成教材"思考与练习"中的视图创建题（创建包含图书ISBN、书名、作者、出版社的视图v_book_info，并添加WITHCHECKOPTION约束）和索引设计题（为借阅记录表tb_record的borrow_time字段创建普通索引，分析该索引对SELECT*FROMtb_recordWHEREborrow_time>'2023-01-01'的优化效果）。

-提交要求：SQL脚本文件（命名格式：学号_视图索引作业.sql），包含视图创建/修改/删除语句、索引创建语句及执行结果截图（如查询时间对比）。

2.综合项目（50%）

-项目名称：图书馆数据安全与性能优化

-任务描述：

①安全模块：为不同角色设计视图（管理员视图含所有字段，学生视图隐藏联系方式），验证通过视图插入数据时WITHCHECKOPTION的作用（如禁止插入非本学院学生）。

②性能模块：对图书表（tb_bibliography）的category字段（高频筛选）和pub_date字段（高频排序）创建合适索引，使用10万行测试数据对比优化前后的查询性能（提供EXPLAIN执行计划截图）。

-提交形式：项目报告（含需求分析、SQL代码、测试结果、优化总结）+演示视频（3分钟，展示视图使用和索引验证过程）。

3.评价标准

-基础作业：语法正确性（30%）、结果准确性（40%）、代码规范（30%，如命名规范、注释清晰）。

-综合项目：需求理解（20%）、方案合理性（30%，如视图设计符合最小权限原则、索引选择恰当）、性能优化效果（30%，查询时间降低比例）、文档完整性（20%）。

-过程性评价：课堂练习完成度（10%），通过小组互评和教师抽查记录。

总结与反思教学效果预期

通过"案例驱动+实验验证+项目实战"的教学模式，使85%以上的学生能独立完成视图的创建与更新，90%的学生能正确区分索引类型并根据场景选择合适的创建方式，75%的学生能综合运用视图和索引解决简单的数据库性能优化问题（如将多表查询时间从秒级降至毫秒级）。

2.教学改进方向

-难点突破：针对视图算法理解困难的问题，可增加"算法执行模拟"互动环节，让学生扮演TEMPTABLE和MERGE算法的执行流程，通过卡片排序直观展示临时表生成与查询合并的区别。

-技术融合：引入数据库可视化工具（如MySQLWorkbench的ER图和执行计划可视化），帮助学生更直观地理解视图的依赖关系和索引的使用情况。

-分层教学：对于基础薄弱学生，提供"视图操作步骤清单"（含语法模板和常见错误排查）；对于学有余力学生，增设"物化视