【MYSQL】MYSQL学习的一大重点：视图
个人主页艾莉丝努力练剑❄专栏传送门《C语言》《数据结构与算法》《C/C干货分享学习过程记录》《Linux操作系统编程详解》《笔试/面试常见算法从基础到进阶》《Python干货分享》⭐️为天地立心为生民立命为往圣继绝学为万世开太平 艾莉丝的简介文章目录0 ~ 前置概念辨析0.1 两个 “视图” 的本质区分1 ~ 视图基础概念1.1 核心定义1.1.1 易错问题1.2 基表与视图的联动关系1.2.1 纠正2 ~ 视图基本操作2.1 创建视图2.1.1 标准语法2.1.2 示例2.2 查询视图2.3 视图数据修改2.3.1 通过视图修改基表2.3.2 修改基表同步到视图2.3.3 可更新视图的核心约束2.4 删除视图2.4.1 标准语法2.4.2 示例3 ~ 视图规则与使用限制4 ~ 工程实践与行业现状4.1 视图的核心价值4.2 大厂使用规范5 ~ 实战例题5.1 题目5.2 标准解答结尾0 ~ 前置概念辨析0.1 两个 “视图” 的本质区分明确强调二者无任何关联更加精准的定义如下本文的视图View数据库模式层面的对象是一段被命名存储的 SELECT 查询定义以虚拟表的形式对外提供访问能力属于 SQL 标准语法范畴。事务中的 Read ViewInnoDB 引擎 MVCC 机制的底层组件是事务执行快照读时生成的可见性判断快照用于判定当前事务能看到哪些版本的行数据属于事务隔离级别的内部实现逻辑。二者分属完全不同的技术维度没有任何关联。1 ~ 视图基础概念1.1 核心定义视图是一个虚拟表其内容由查询定义同真实表一样包含命名的列和行数据。1.1.1 易错问题如下所示“在内存级别上创建好了一张表”“把筛选出来的数据插入到表中”“查询结果变成了一个临时表结构”视图本身不存储任何数据也不会默认生成内存临时表。视图的本质是一段被持久化存储的 SELECT 查询语句。当查询视图时MySQL 会将视图定义与外层查询合并后执行所有数据均实时从基表计算得到。补充执行算法MERGE合并算法默认优先将视图定义的 SQL 与外层查询 SQL 合并为单条语句执行无额外临时表开销性能等价于直接执行原生查询。TEMPTABLE临时表算法当视图定义包含聚合、分组、DISTINCT 等无法合并的逻辑时MySQL 先将视图查询结果写入临时表内存或磁盘再基于临时表执行外层查询存在额外性能开销。“视图本质上就是一种表结构”视图是虚拟表仅存储定义元数据MySQL 5.x 存.frm结构文件8.0 后纳入数据字典没有对应的.ibd数据文件不持久化存储行数据与物理基表有本质区别。删除视图后无对应数据文件也印证了这一点。1.2 基表与视图的联动关系这个表述对吗视图的数据变化会影响基表基表的数据变化也会影响视图。1.2.1 纠正实际上该结论存在前提缺失完整严谨的表述为基表变更 → 视图同步变更恒成立。视图数据是查询时实时计算生成基表数据变更后再次查询视图必然反映最新数据。视图变更 → 基表同步变更仅可更新视图支持且存在严格的语法约束。绝大多数复杂视图聚合、多表深度连接等无法执行写入操作。2 ~ 视图基本操作2.1 创建视图2.1.1 标准语法create view视图名as select语句;存在空格缺失问题标准语法如下CREATE[ORREPLACE]VIEW视图名[(自定义列名列表)]ASSELECT查询语句[WITH[CASCADED|LOCAL]CHECKOPTION];关键字说明OR REPLACE视图已存在时直接覆盖原有定义避免重名报错。自定义列名列表可重命名视图的输出列数量必须与 SELECT 结果列数一致不指定则默认沿用 SELECT 的列名。WITH CHECK OPTION写入视图数据时校验数据是否满足视图的 WHERE 过滤条件防止写入后数据从视图中 “消失”。2.1.2 示例基于 EMP、DEPT 表创建员工姓名 - 部门名称视图。-- 创建内连接视图CREATEVIEWv_ename_dnameASSELECTemp.ename,dept.dnameFROMempINNERJOINdeptONemp.deptnodept.deptno;2.2 查询视图视图的查询语法与普通物理表完全一致支持 WHERE、ORDER BY、联表等所有查询语法。-- 查询视图并按部门排序SELECT*FROMv_ename_dnameORDERBYdname;执行逻辑等价于直接运行视图定义的完整 SELECT 语句。2.3 视图数据修改2.3.1 通过视图修改基表示例更新视图中的员工姓名基表同步变更。-- 通过视图更新员工姓名UPDATEv_ename_dnameSETenamesmithWHEREenameSMITH;执行结果视图与基表emp的ename字段同步更新。成立前提ename列唯一来自单表emp且视图满足可更新条件若尝试更新dname来自dept表多表连接视图通常不支持跨表更新。2.3.2 修改基表同步到视图示例更新基表部门名称视图数据同步变更。-- 修改基表 DEPT 的部门名称UPDATEdeptSETdnameaaaaaWHEREdeptno30;执行结果再次查询视图所有 30 号部门对应的dname同步更新。结论基表数据变更必然实时反映到视图中无任何前提条件。2.3.3 可更新视图的核心约束补充工业界标准约束。视图支持 UPDATE/INSERT/DELETE 必须同时满足无聚合函数SUM/COUNT/MAX 等、无 GROUP BY、无 DISTINCT、无 UNION、无 HAVING。视图列不能是表达式、常量或函数计算的结果。单表视图天然支持更新多表连接视图仅允许更新其中一张基表的列且连接不能导致行映射歧义。FROM 子句中不能包含子查询。2.4 删除视图2.4.1 标准语法DROPVIEW[IFEXISTS]视图名;2.4.2 示例DROPVIEWv_ename_dname;执行后仅删除视图的定义元数据不会对基表数据产生任何影响。物理层面删除视图不会删除任何.ibd数据文件仅移除对应的视图定义文件。3 ~ 视图规则与使用限制命名唯一性同一数据库内视图名不能与表名、其他视图名重复。创建数量无上限但复杂查询定义的视图会增加优化器解析成本可能导致执行计划退化不建议滥用。功能限制普通视图无法创建索引无法关联触发器不能为列设置默认值。注MySQL 原生不支持物化视图无法为视图持久化存储数据与索引若需物化视图需通过定时任务生成物理表手动模拟。权限规则访问视图需要拥有视图的对应权限同时视图创建者必须拥有基表的查询权限。视图可实现列级权限控制只向用户开放允许查看的字段。排序覆盖规则视图定义中可以写 ORDER BY但如果查询视图的外层语句也包含 ORDER BY视图内部的排序会被外层覆盖最终以外层排序为准。联表兼容性视图可与普通物理表混合使用支持内连接、外连接、子查询等所有表级查询语法。4 ~ 工程实践与行业现状4.1 视图的核心价值判断一下下面这句话的表述有没有问题“高频访问不用做多表查询、字段更清晰”。这句话是有问题的下面纠正一下SQL 复用与简化将复杂多表关联封装为视图业务层直接调用避免重复编写冗余 SQL降低维护成本。逻辑解耦基表结构变更时可通过修改视图定义屏蔽底层变化上层业务代码无需改造。数据安全实现行列级权限隔离例如只开放员工姓名、部门字段隐藏薪资、身份证等敏感数据。性能误区修正视图不会提升查询性能。MERGE 算法下性能与原生 SQL 完全等价TEMPTABLE 算法下反而会增加临时表开销性能下降。原始笔记中 “方便快速读取” 指的是编写便捷而非执行性能提升。4.2 大厂使用规范互联网行业普遍现状大厂内部限制使用视图核心原因如下运维成本高业务逻辑下沉到数据库层增加了 SQL 排查、优化的复杂度不利于 DBA 统一管控。架构扩展性差分库分表、读写分离的分布式架构下视图基本无法正常使用。研发规范约束主流研发规范要求业务逻辑全部收敛在应用代码层数据库仅负责数据存储避免数据库逻辑过重导致整体架构僵化。学习建议视图是 SQL 标准核心概念必须掌握其原理与语法生产环境需谨慎评估优先在业务代码层封装查询逻辑。5 ~ 实战例题5.1 题目针对actor表创建视图actor_name_view只包含first_name以及last_name两列。5.2 标准解答CREATEVIEWactor_name_viewASSELECTfirst_name,last_nameFROMactor;结尾uu们本文的内容到这里就全部结束了艾莉丝在这里再次感谢您的阅读艾莉丝努力练剑C/C Linux 底层探索者 | 一个正在努力练剑的技术博主【关注】跟随我一起深耕技术领域见证每一次成长。❤️【点赞】让优质内容被更多人看见让知识传递更有力量。⭐【收藏】把核心知识点存好在需要时随时查、随时用。【评论】分享你的经验或疑问评论区一起交流避坑不要忘记给博主“一键四连”哦“今日练剑达成”“技术之路难免有困惑但同行的人会让前进更有方向。”结语希望对学习Linux相关内容的uu有所帮助不要忘记给博主“一键四连”哦往期回顾【MYSQL】MYSQL学习的一大重点事务下- InnoDB 事务隔离性原理MVCC 视角博主在这里放了一只小狗大家看完了摸摸小狗放松一下吧૮₍ ˶ ˊ ᴥ ˋ˶₎ა
)
