项目6 优化查询学生信息管理数据库

《达梦数据库原理及应用》课程教案适用专业：计算机应用技术、软件技术、大数据技术等计算机类专业授课对象：高职一年级学生课程类型：专业基础课先修课程：计算机基础、程序设计基础一、教案基本信息项目内容章节项目6优化查询学生信息管理数据库授课课时4课时（180分钟）授课形式理论讲授+案例分析+分组实操教学目标1.知识目标：-掌握索引、视图、物化视图的概念、类型及设计/创建原则；-掌握索引、视图、物化视图的创建、修改、删除及使用方法；-理解物化视图的刷新机制及物化视图日志的作用。2.能力目标：-能根据实际需求选择合适的索引类型并完成创建与优化；-能利用视图简化复杂查询并实现数据安全访问；-能使用物化视图解决数据汇总与历史数据保留问题；-能独立排查索引、视图、物化视图操作中的常见错误。3.素养目标：-培养数据库性能优化的工程思维和问题解决能力；-提升团队协作与规范操作的职业素养；-树立数据安全与高效管理的专业意识。教学重难点重点：-索引的创建与查询优化应用；-视图的创建、修改及数据访问操作；-物化视图的创建与刷新方法。难点：-索引设计原则的实际应用（如列选择、索引数量控制）；-视图数据更新的限制条件；-物化视图刷新策略的选择与日志配置。教学资源多媒体课件、DM数据库、案例素材（STUDENT、COURSE、SC表等）、实训任务单等。思政融入通过数据库性能优化案例，培养学生精益求精的工匠精神；2.结合视图的安全访问特性，强化数据安全与隐私保护意识；3.在分组实训中培养学生的团队协作与责任担当意识。二、教学过程设计（一）课前导入（5分钟）案例导入提问：“王宁在学生信息表中插入100万条记录后，查询某条学生信息耗时26.83秒，这样的响应速度会给用户带来什么影响？如何解决这类查询效率问题？”引出本节课核心主题--利用索引、视图、物化视图优化数据库查询性能。复习衔接：回顾上节课内容：SQL基本查询语句（SELECT、WHERE、JOIN等），提问学生：“多表连接查询时，若数据量较大，可能会遇到什么问题？”强化知识衔接，引出优化需求。（二）理论讲授（65分钟）【知识点1】：索引-核心作用：向数据表中添加新记录。-概念：索引是加快数据检索的数据库结构，类似书籍目录，无需扫描全表即可定位数据，DM8中默认采用B+树存储（除特殊索引类型）。-类型：按物理存储分为聚集索引（每张表只能有1个聚集索引）和非聚集索引（索引键与数据行物理存储顺序无关，每张表可创建多个非聚集索引）；按功能分为唯一索引（允许NULL值，仅能有1个，可避免数据重复）、普通索引（无键值唯一性限制）等；按键值个数分为单列索引（基于1个字段创建的索引）和组合索引（基于多个字段创建的索引）。-设计原则：①优先为查询频繁的字段（如WHERE条件、JOIN关联字段、ORDERBY排序字段）创建索引；②避免为更新频繁的字段创建过多索引（减少写操作开销）；③组合索引按“等值条件字段在前、范围条件字段在后”排序（符合最左匹配原则）；④避免为基数低的字段（如性别，仅男/女两个值）创建索引（索引选择性差，优化效果不明显）；⑤建议在数据批量插入后创建索引（避免插入过程中频繁维护索引）。-创建索引的语句：CREATE[UNIQUE][CLUSTERED]INDEX索引名ON表名(字段名1[ASC/DESC],字段名2[ASC/DESC],...);参数说明：UNIQUE指定唯一索引，CLUSTERED指定聚集索引（默认非聚集），ASC/DESC指定索引排序方向（默认ASC）。-修改与删除：修改：DM8中不支持直接修改索引，需先删除再重建；删除：DROPINDEX索引名ON表名;【知识点2】：视图-概念：视图是基于SQL查询语句的虚拟表，仅存储查询定义（元数据），不存储实际数据。当查询视图时，数据库会动态执行视图对应的SQL语句，从基表中获取数据并返回。-视图与基表的关系：视图依赖基表存在，若基表结构变更（如删除视图依赖的字段），则视图会失效，需重新创建或修改。-视图的优点：简化复杂查询：将多表关联、聚合计算等复杂SQL封装为视图，用户只需查询视图即可，无需记忆复杂SQL逻辑（如“查询学生选课及成绩信息”的多表关联查询可封装为视图）；实现数据安全访问：通过视图控制用户可见的字段和数据行，避免用户直接访问基表，保护敏感数据（如隐藏STUDENT表的身份证号、联系方式字段，仅向普通用户开放学号、姓名、班级字段）；保持数据独立性：若基表结构变更（如字段名修改），可通过修改视图定义适配，不影响用户的查询语句（用户仍按原视图字段查询）。-视图的创建CREATEVIEW视图名ASSELECT字段名1,字段名2,...FROM表名[JOIN其他表ON关联条件][WHERE过滤条件][GROUPBY分组字段][HAVING聚合条件][WITHCHECKOPTION][WITHREADONLY];①WITHCHECKOPTION：确保通过视图插入/更新的数据符合视图的过滤条件（如视图仅显示202001班学生，插入其他班级学生时会报错）；②WITHREADONLY：指定视图为只读视图，禁止通过视图执行INSERT/UPDATE/DELETE操作（适用于数据展示场景）。-数据更新限制：视图包含多表连接列、聚合函数、GROUPBY/DISTINCT、视图字段为计算字段时，无法直接更新数据。-修改与删除：修改语法：ALTERVIEW视图名AS新的查询语句;删除语法：DROPVIEW视图名;【知识点3】：物化视图-核心原理：物化视图是存储查询结果的实体表，不仅存储查询定义，还会将查询结果物理存储在数据库中，相当于“预先计算并保存复杂查询的结果”。-与视图的区别：存储：普通视图不存储数据，物化视图存储数据；查询效率：普通视图每次查询均需重新执行SQL，物化视图直接查询存储的结果，效率更高；数据同步：普通视图数据实时同步基表（因依赖基表动态查询），物化视图数据需通过“刷新”同步基表变更。-物化视图的优点与使用场景：优势：大幅提升复杂查询（如多表关联+聚合统计）的响应速度；降低基表的查询负载（避免频繁执行复杂SQL）；支持数据的分布式复制（如主库物化视图同步至从库）。适用场景：数据更新频率较低、查询频率高的复杂统计场景（如“各班级学生成绩平均分统计”“各课程不及格人数统计”）；需要保留历史数据快照的场景（如每月末的成绩汇总数据）。-创建语法：CREATEMATERIALIZEDVIEW物化视图名REFRESH[FAST|COMPLETE|FORCE][ONDEMAND|ONCOMMIT]AS复杂查询语句;-关键参数说明：①刷新方式（数据同步的具体方式）：FAST（增量刷新）：仅同步基表中自上次刷新后变更的数据（插入/更新/删除），效率高。依赖条件：必须为基表创建“物化视图日志”（记录基表的变更信息），否则无法使用FAST刷新。COMPLETE（全量刷新）：删除物化视图的原有数据，重新执行查询语句生成全量结果，效率低，但无需依赖物化视图日志。适用于基表数据大量变更或未创建物化视图日志的场景。FORCE（默认方式）：数据库自动判断是否可执行FAST刷新（检查物化视图日志及基表变更情况），若可以则执行FAST，否则执行COMPLETE。②刷新时机（触发同步的条件）：ONDEMAND（手动刷新）：需用户手动执行刷新命令（如DBMS_MVIEW.REFRESH('物化视图名')），适用于数据更新频率低、可接受非实时同步的场景（如每日凌晨刷新）。ONCOMMIT（提交时自动刷新）：当基表执行INSERT/UPDATE/DELETE并提交事务时，自动触发物化视图刷新，确保数据实时同步。适用于数据更新频率中等、需要实时同步的场景，但会增加基表写操作的开销。-物化视图日志的创建：作用：记录基表的变更操作（插入/更新/删除的行信息），供物化视图FAST刷新时获取增量数据。语法：CREATEMATERIALIZEDVIEWLOGON基表名[WITHPRIMARYKEY|ROWID];参数说明：WITHPRIMARYKEY表示按主键记录变更（适用于基表有主键的场景）；WITHROWID表示按数据行的物理地址记录变更（适用于基表无主键的场景）。-修改与删除修改语法：ALTERMATERIALIZEDVIEW物化视图名REFRESH[新的刷新策略];删除语法：DROPMATERIALIZEDVIEW物化视图名;（删除物化视图不影响基表及物化视图日志）；物化视图日志删除：DROPMATERIALIZEDVIEWLOGON基表名;（三）实操练习（100分钟）1.任务布置分组（每组3-4人），完成以下任务：任务1：根据实训任务单，完成优化查询任务，理解索引、视图、物化视图的作用及使用。2.过程指导教师巡视各组，重点检查组合索引的字段顺序设计、视图更新失败的原因分析、物化视图日志创建与FAST刷新的关联关系；鼓励小组内互相讨论解决实操问题，记录问题及解决方案。3.成果展示选取2-3组展示实训成果，重点点评索引设计的合理性、视图语句的规范性及更新限制的理解、物化视图刷新策略的配置正确性及结果验证。针对共性问题进行集中讲解。（四）课堂小结与作业布置（10分钟）1.课堂小结（5分钟）回顾核心内容：索引、视图、物化视图的概念、创建方法及使用；总结重难点：索引的B+树原理与最左匹配原则、视图更新的限制条件、物化视图的刷新策略与日志配置；核心原则强调：“索引不是越多越好，需结合查询场景精准设计；视图核心价值是简化与安全；物化视图适合‘查询频繁、更新稀少’的复杂统计场景”。2.作业布置（5分钟）1