说实话数据库这门课里“视图与索引”这一节属于典型的“看着简单、考起来花样百出”。我自己当年复习到这里时就有这种感觉概念背得滚瓜烂熟结果题目换个问法就懵了。比如“视图能不能更新”这五个字能衍生出多种答案关键取决于题目里的限定条件。后来真正接触生产库天天跟线上查询和慢SQL打交道才把这一块的细节一点点补齐。这篇内容我按“题库笔记”的方式来写把创建视图、管理视图、创建索引、管理索引这些高频考点重新过了一遍题目参考了教材常考题型和真实业务场景每题都给了分析和避坑提示。期末复习、考研、准备面试前翻一翻应该能帮你省不少时间。1. 为什么视图和索引总被安排在同一节复习1.1 视图与索引在数据库体系里的定位很多教材会把创建视图和创建索引放在同一节不是随手安排的它们都属于SQL的数据定义功能和我们更熟悉的建表语句是一套逻辑。但这两个东西解决的问题完全不同。视图解决的是“看数据的方式”问题。它本质是一个虚拟表不实际存储数据只保存一段SELECT语句。你可以把它理解为一条“保存好的查询捷径”把频繁使用的复杂查询固化下来以后每次只要调用视图名字就行。索引解决的是“找数据的速度”问题。它是在磁盘上额外维护一套查找结构相当于一本书最后面的“目录”不改变正文内容但能让你更快定位到目标行。用一个我常打的比方视图是“常用查询的快捷方式”索引是“查询引擎的加速器”。复习时如果把这两件事混在一起学很容易出现“知道概念但不会做题”的情况。所以先建立这个认知看到视图题想的是结果集、权限、逻辑独立性这些词看到索引题想的是B树、回表、覆盖索引、查询优化器这些词。一旦方向分清楚题目难度直接降一半。1.2 复习题库背后的考点地图这一节常考的内容归纳起来就三块视图的定义与使用、视图更新条件、索引的分类与设计原则。再加一个进阶点索引失效的场景分析。从往年的题目分布来看视图部分比较集中在概念辨析和SQL书写上索引部分则偏爱选择题和综合题。你会发现一个有意思的现象视图题的答案往往“模棱两可”因为题目里藏着条件索引题则刚好相反答案非常依赖具体场景。同一个查询改一个WHERE条件索引从“能用”变成“失效”这种变化是索引题的核心考察方式。所以我整理题库的时候特意把“条件变化”作为出题重点后文每道题都能看到这个特征。2. 视图创建与管理虚表题目里那几个容易丢分的地方2.1 从一道判断题说起视图到底存不存数据先看最基础的一道也是很多复习资料里出现率极高的题。判断题视图是一个虚拟表视图本身不存储数据只保存定义视图的查询语句。这个说法是正确的。视图在数据库里保存的确实是一段经过解析的查询定义而不是实际的数据副本。每次你查询视图时数据库都会根据这个定义去基本表里取数据再动态生成结果集返回给你。这里有一个隐藏考点既然视图不存数据那基表数据变了视图查出来的结果也会变。有些同学会把视图和“临时表”混淆临时表是实打实把数据复制了一份会话结束就释放视图则永远是个“窗口”你通过它看到的是基表的实时内容。这个概念搞清楚了后面很多题目都不会错。2.2 创建视图的SQL书写最容易扣分的三个细节这类题一般直接要求写SQL比如题目学生表Student(Sno, Sname, Ssex, Sage, Sdept)创建计算机系CS学生的视图包含学号、姓名、性别、年龄四个字段。标准写法是CREATE VIEW V_CS AS SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept CS;大部分人都能写到这一步但丢分往往在细节上。第一视图名不能和已有表名、视图名重名第二如果SELECT子句里出现了表达式、聚合函数或者多表连接后有重名字段必须在视图名后面用括号列出字段名比如写成CREATE VIEW V_CS_Avg(Sno, AvgGrade) AS ...第三WHERE条件里的字符串要正确使用单引号这个错误在机考里特别常见平时写惯了Java、Python字符串容易顺手用双引号。再进阶一点视图可以建立在已经存在的视图之上这叫“嵌套视图”。比如CREATE VIEW V_CS_MALE AS SELECT Sno, Sname FROM V_CS WHERE Ssex 男;教材里明确支持这种写法考试时不用怕按普通视图的创建规则套用即可。但要注意嵌套视图的层次不能过深实际工作中也不建议多层嵌套否则查询效率会明显下降排查问题时还很难追踪数据来源。2.3 视图更新限制WITH CHECK OPTION是高频考点视图能不能用INSERT、UPDATE、DELETE语句更新数据答案要看视图是否满足“可更新视图”的条件。教材里给出的判断标准一是视图必须基于单个基本表二是视图必须包含基本表的主键或候选键使得每一行能被唯一标识三是视图中不能包含聚合函数、DISTINCT、GROUP BY、HAVING等成分。所以像下面这种分组统计视图只能查询不能更新CREATE VIEW V_Cnt(Cno, Num) AS SELECT Cno, COUNT(*) FROM SC GROUP BY Cno;你不可能通过这个视图去INSERT一行到SC表因为数据库根本不知道新增的“选课人数”该落到哪个具体元组上。这个逻辑想通了比死记硬背条件要牢靠得多。还有一个很容易被忽略的点是WITH CHECK OPTION。看这道题题目定义计算机系学生视图时加上WITH CHECK OPTION然后通过该视图把某个学生的院系改成“MA”会发生什么CREATE VIEW V_CS AS SELECT Sno, Sname, Sdept FROM Student WHERE Sdept CS WITH CHECK OPTION;执行UPDATE V_CS SET Sdept MA WHERE Sno 2001;时数据库会拒绝这条语句。因为WITH CHECK OPTION表示“对视图进行插入、修改、删除操作时系统会检查数据是否满足视图定义里的WHERE条件”修改后的Sdept变成MA不再满足Sdept CS这个条件所以操作不被允许。这个机制有什么用打个比方视图就像一个装了门禁的房间WITH CHECK OPTION就是门禁规则你从房间里搬东西出去可以但搬进来的东西必须符合这个房间的“准入标准”。实际开发中这个选项能有效防止误操作污染数据我参与过的一个权限管理模块就是这么做的对外提供分层视图业务侧更新数据时只能碰自己权限范围内的行越过边界的操作直接报错。2.4 删除与管理视图一句话里也有考法管理类题目相对简单但有个高频陷阱。题目删除视图V_CS应使用哪条语句答案是DROP VIEW V_CS;。注意这里不是DELETE FROM V_CS。DELETE是删除视图结果集中的“数据行”但视图本身不存在物理数据真正删的是基表里的数据而DROP VIEW才是删除视图这个“定义对象”。这个区别一定要分清我见过不止一个同学在机考时把这两条语句写混直接导致后续题目全部跑偏。MySQL里还可以用DROP VIEW IF EXISTS V_CS;避免报错标准SQL考试里一般不要求写IF EXISTS但工作中这是个习惯性操作。视图删除后基于该视图建立的其他视图也会失效属于“牵一发而动全身”这个知识点在简答题里偶尔会考到回答时提一句“级联影响”就能拿全分。3. 索引的应用与设计答选择题比写SQL更难的是“选对列”3.1 索引分类的辨析这几组概念别再混淆索引题喜欢在概念分类上做文章。最常见的就是区分“唯一索引”和“非唯一索引”、“聚集索引”和“非聚集索引”、“单列索引”和“复合索引”、“B树索引”和“哈希索引”。看这道题题目下面关于索引的说法错误的是A. 一个表上只能创建一个聚集索引 B. 一张表可以有多个非聚集索引 C. 唯一索引既能保证数据唯一性也能加速查询 D. 哈希索引适合范围查询B树索引适合等值查询答案是D。恰恰说反了哈希索引极其适合等值查询因为哈希函数的特性决定了它能一次定位到目标桶但遇到范围查询比如WHERE age BETWEEN 20 AND 30哈希索引就没法按顺序遍历性能反而不如B树。B树索引因为叶子节点形成了有序链表既能等值定位也能范围扫描是绝大多数关系型数据库的默认选择。A选项里“一个表只能有一个聚集索引”是对的。聚集索引的意思是表中数据的物理存储顺序和索引的逻辑顺序一致就像一本字典的正文按拼音排列一样一本书只能有一种物理排列方式所以聚集索引只能有一个。B选项也是对的辅助索引非聚集索引可以建很多个只是每个都会占用额外空间并增加写操作负担。3.2 创建索引的SQL语法简单真正考点在设计选择写索引的SQL本身不难CREATE UNIQUE INDEX Idx_SC ON SC(Sno, Cno);这一句在选课表SC上基于学号和课程号创建了一个唯一索引含义是同一名学生选修同一门课只能有一条记录。很多教材的练习都会拿这个当例子因为它既演示了唯一索引的创建又体现了业务约束选课记录不允许重复。索引题目真正的难点不在“怎么写”而在“该不该写”。比如这种选择题题目以下哪个列最适合创建索引A. 性别列取值只有“男”“女” B. 频繁更新的价格列 C. 订单表中的订单号列 D. 包含大量NULL值的备注列答案C。理由很直接索引的价值在于快速过滤出少数行如果一列取值只有两种查询时无论索引怎么走都要扫过一半数据优化器大概率直接放弃索引走全表扫描。这就是“索引选择性”的概念——列的重复值越少选择性越高索引越有价值。订单号几乎每条记录都不重复区分度极高建索引效果最好。B选项是有名的“索引反模式”列频繁更新意味着索引结构要跟着不断调整写操作的代价大增如果这个列查询需求又没那么强烈性价比很差。D选项也不适合大量NULL值会让索引结构稀疏很多数据库连NULL都不写进普通索引里用不上力。3.3 聚集索引与辅助索引简洁题的标准答法这类题如果在简答题里出现其实并不难答但很多人容易答不到点子上。题目简述聚集索引与非聚集索引的区别。我一般建议分四点来答。第一物理顺序聚集索引的表数据按索引键值的顺序物理存储而非聚集索引的表数据物理顺序和索引顺序无关。第二数量限制一个表只能有一个聚集索引非聚集索引可以有多个。第三查找过程通过聚集索引定位到键值时数据行就在旁边一次就能拿到整行通过非聚集索引查找时通常先在索引里找到主键值再根据主键去聚集索引里回表查询一次完整数据这个过程叫回表。第四典型应用InnoDB引擎的主键索引就是聚集索引而针对普通字段建的索引一般是辅助索引。回表这个点我多说一句它是面试里特别爱追问的细节。假设一张学生表以Sno为主键建了聚集索引又在Sname上建了非聚集索引执行SELECT * FROM Student WHERE Sname 张三数据库会先在Sname索引里定位“张三”拿到对应的主键Sno再用Sno去聚集索引里找完整行整个过程经历了两次索引查找。如果查询的字段恰好都包含在Sname索引里比如只查Sname和Sno数据库就可以不用回表直接返回这种“索引覆盖”的情况就是覆盖索引效率最高。复习时理解清楚这个链路比死背“非聚集索引需要回表”要灵活得多。4. 从一条综合题看索引失效的完整排查链路4.1 索引失效的典型场景选择题爱考的四种写法索引建了查询却不走索引这就是“索引失效”。教材里提到的不多但实际工作和各种考试里出现频率极高。我总结出四种最常见的失效写法。第一种对索引列使用函数或运算。比如WHERE YEAR(birth_date) 1995即使birth_date建了索引因为每一行都要先计算YEAR函数索引的有序性被破坏优化器只能放弃索引。正确的写法是WHERE birth_date 1995-01-01 AND birth_date 1996-01-01让索引直接发挥范围扫描能力。第二种隐式类型转换。比如手机号列phone是VARCHAR类型查询写成WHERE phone 13800000000数字常量会被隐式转换为字符串但转换过程可能让优化器无法准确匹配索引。这一点在不同数据库上表现不完全一样但考场里基本默认是坑。第三种LIKE查询以通配符开头。WHERE name LIKE %张三%无法使用索引因为索引按从左到右的顺序匹配前缀开头就是模糊的没法定位。但WHERE name LIKE 张三%就可以走索引。第四种OR条件中包含了非索引列。比如WHERE age 18 OR name 张三如果只有age列有索引而name列没有数据库要同时处理两边条件为了拿到name张三的那部分只能回头走全表扫描整个查询的索引就废了。遇到这种情况可以考虑把OR拆成UNION或者给name也建索引。4.2 一条EXPLAIN语句的完整排查案例选择题考场景识别综合题就会考排查思路和方法了。我设计一道典型题目题目订单表orders(order_id, user_id, amount, status, create_time)其中status列已建索引。查询语句SELECT * FROM orders WHERE status PAID执行缓慢用EXPLAIN查看发现type为ALL全表扫描。请排查原因并给出改进建议。这类题我建议按三步来答。第一步先确认索引是否存在这是最容易忽略的点很多人一上来就分析SQL结果索引压根没建。第二步看数据分布如果status字段上绝大多数记录都是‘PAID’只有极少数是其他状态那查询优化器会认为走索引和全表扫描差别不大甚至全表更快于是主动放弃索引这是非常正常的“优化器决策”。第三步复现问题时要留意统计信息是否过期如果表数据量变化很大而统计信息没更新优化器也会做出错误判断。改进方案一般从几个方向入手如果业务场景里极少查询具体状态码而对复合条件查询更多可以考虑创建复合索引比如(status, create_time)如果数据量确实大可以按时间做分区表把扫描范围限制在一个分区内还有一种做法是把大查询拆成多个等值条件的UNION ALL提高优化器选择索引的概率。在实际排障时我习惯先看EXPLAIN里的possible_keys和key两列possible_keys表示优化器“可能用到”的索引key表示“实际用到的”索引。如果possible_keys有值但key为空说明优化器评估后选择了放弃如果possible_keys本来就是空的说明SQL写法有问题才会导致索引根本没进入考虑范围。这个区分能快速定位是“索引设计问题”还是“SQL写法问题”排查效率高很多。4.3 一道关于最左前缀原则的坑题复合索引的考点里“最左前缀原则”是必考内容而且经常配合坑题出现。题目表student上创建复合索引(sname, sage, ssex)以下查询中哪个无法使用该索引A. WHERE sname 张三 B. WHERE sname 张三 AND sage 20 C. WHERE sage 20 AND ssex 男 D. WHERE sname 张三 AND ssex 男答案是C。复合索引从左到右按字段顺序构建B树查询条件里必须包含最左边的字段才有可能用上索引。C选项直接跳过了sname从第二个字段开始索引的树形结构就派不上用场这就是“最左前缀”的含义。这个坑很隐蔽的地方在于很多同学以为“只要WHERE里出现了索引里的字段就行”忽略了顺序要求。实际上只要sname出现在条件里后面字段的顺序是否合理只是影响“能用索引的深度”但至少索引能起步完全没有sname就完全用不上这个复合索引。写到这里我想起自己的一个教训以前图省事给表里所有可能查询的字段建了一个大复合索引结果因为字段顺序和业务查询不匹配很多SQL还是全表扫。后来学乖了建复合索引之前先统计业务里最常出现的WHERE条件组合把区分度最高、最常作为过滤起点的字段放最左边。5. 复习节奏与易错点复盘考前重点看这份清单5.1 我刷题库的顺序从概念题到场景题如果你正准备考试我建议不要上来就刷综合题。先把概念判断题过一遍比如视图是不是虚表、索引能不能加速排序、聚集索引一个表能有几个这一轮过关后再写SQL题把CREATE VIEW、CREATE INDEX的语法练到不假思索。最后再碰场景题就是“给定一条慢SQL分析问题并优化”这种因为场景题考的是综合判断能力前面两类题的积累会在这里同时发挥作用。我自己带过几个新人备考发现一个普遍问题大家更愿意刷写SQL的题觉得有代码才是实战反而忽视概念题。但考试里丢分最多的恰恰是概念辨析。尤其像“视图更新条件”“WITH CHECK OPTION”这种细节SQL写法可能只有一个标准答案但概念题能变出十几种说法稍不留神就踩坑。所以建议把概念题和处理成简答题的考点用自己话先复述一遍能讲清楚才算真正掌握。5.2 这份易错清单请直接背下来我整理了这张表算是我自己复习和带人过程中沉淀下来的高频易错点考前过一遍很管用。易错点错误理解正确理解视图存储视图保存了查询结果数据视图只保存查询定义数据实时来自基表视图删除DELETE可以删除视图删除视图用DROP VIEWDELETE操作的是基表数据视图更新所有视图都可以更新必须是基于单表且含主键、无聚合无分组的视图才可更新WITH CHECK OPTION只是查询时做权限检查插入、更新、删除时都会校验是否满足视图条件索引数量索引越多查询越快索引有维护成本过多会拖慢增删改索引失效建了索引就一定能用上函数、隐式转换、前导通配符、OR条件等都会导致失效复合索引条件里出现任一索引字段就能用索引必须满足最左前缀原则最左字段不能缺席表格里最后一行是我特别想强调的。很多人在复合索引上栽跟头不是不会建而是不会用。宁可把复合索引的字段数量控制少一点也要保证每个字段都有真实查询价值。一堆“看起来可能有用”的字段堆在一起最后往往只走了一个最左字段剩下全成了空间浪费。5.3 最后分享两个项目里的实际体会工作中用视图和索引和备考有个不太一样的地方教材强调的是“正确”生产环境强调的是“权衡”。比如视图考试可能只问你能不能更新但在真实业务里我们更关心视图嵌套层级会不会拖慢查询以及视图给数据权限带来的安全收益。我自己在好几个系统里都用“按部门隔离”的视图做数据出口上层应用完全看不到全表这样就算应用被拖库攻击者能拿到的也只是一个裁剪过的视图结果集。索引这边生产环境里的心得更多。以前上线一个报表功能报表SQL要关联三张表、过滤条件还特别多最初全表扫描要跑四五秒。后来我按查询条件建了复合索引把执行时间从五秒压到几十毫秒这件事给我留下的印象特别深索引不是万能药但用对了地方收益极其明显。反过来有一次我在一张频繁写入的表上加了三个索引结果业务高峰期锁等待暴增最后删掉两个低频查询的索引才恢复平稳。索引是典型的“空间换时间”而且换来的时间还要看业务查询是不是真的用到了用不到就是纯开销。复习这一节我的建议很简单不要死背题要把每个考点还原成“数据库为什么要这么做”。理解了动机题面怎么换你都不怕。这套题库笔记里的每道题你都可以试着用“我会怎么跟别人解释这个知识点”来代替“我记住了这个答案”效果会比单纯刷题好不少。
说实话数据库这门课里“视图与索引”这一节属于典型的“看着简单、考起来花样百出”。我自己当年复习到这里时就有这种感觉概念背得滚瓜烂熟结果题目换个问法就懵了。比如“视图能不能更新”这五个字能衍生出多种答案关键取决于题目里的限定条件。后来真正接触生产库天天跟线上查询和慢SQL打交道才把这一块的细节一点点补齐。这篇内容我按“题库笔记”的方式来写把创建视图、管理视图、创建索引、管理索引这些高频考点重新过了一遍题目参考了教材常考题型和真实业务场景每题都给了分析和避坑提示。期末复习、考研、准备面试前翻一翻应该能帮你省不少时间。1. 为什么视图和索引总被安排在同一节复习1.1 视图与索引在数据库体系里的定位很多教材会把创建视图和创建索引放在同一节不是随手安排的它们都属于SQL的数据定义功能和我们更熟悉的建表语句是一套逻辑。但这两个东西解决的问题完全不同。视图解决的是“看数据的方式”问题。它本质是一个虚拟表不实际存储数据只保存一段SELECT语句。你可以把它理解为一条“保存好的查询捷径”把频繁使用的复杂查询固化下来以后每次只要调用视图名字就行。索引解决的是“找数据的速度”问题。它是在磁盘上额外维护一套查找结构相当于一本书最后面的“目录”不改变正文内容但能让你更快定位到目标行。用一个我常打的比方视图是“常用查询的快捷方式”索引是“查询引擎的加速器”。复习时如果把这两件事混在一起学很容易出现“知道概念但不会做题”的情况。所以先建立这个认知看到视图题想的是结果集、权限、逻辑独立性这些词看到索引题想的是B树、回表、覆盖索引、查询优化器这些词。一旦方向分清楚题目难度直接降一半。1.2 复习题库背后的考点地图这一节常考的内容归纳起来就三块视图的定义与使用、视图更新条件、索引的分类与设计原则。再加一个进阶点索引失效的场景分析。从往年的题目分布来看视图部分比较集中在概念辨析和SQL书写上索引部分则偏爱选择题和综合题。你会发现一个有意思的现象视图题的答案往往“模棱两可”因为题目里藏着条件索引题则刚好相反答案非常依赖具体场景。同一个查询改一个WHERE条件索引从“能用”变成“失效”这种变化是索引题的核心考察方式。所以我整理题库的时候特意把“条件变化”作为出题重点后文每道题都能看到这个特征。2. 视图创建与管理虚表题目里那几个容易丢分的地方2.1 从一道判断题说起视图到底存不存数据先看最基础的一道也是很多复习资料里出现率极高的题。判断题视图是一个虚拟表视图本身不存储数据只保存定义视图的查询语句。这个说法是正确的。视图在数据库里保存的确实是一段经过解析的查询定义而不是实际的数据副本。每次你查询视图时数据库都会根据这个定义去基本表里取数据再动态生成结果集返回给你。这里有一个隐藏考点既然视图不存数据那基表数据变了视图查出来的结果也会变。有些同学会把视图和“临时表”混淆临时表是实打实把数据复制了一份会话结束就释放视图则永远是个“窗口”你通过它看到的是基表的实时内容。这个概念搞清楚了后面很多题目都不会错。2.2 创建视图的SQL书写最容易扣分的三个细节这类题一般直接要求写SQL比如题目学生表Student(Sno, Sname, Ssex, Sage, Sdept)创建计算机系CS学生的视图包含学号、姓名、性别、年龄四个字段。标准写法是CREATE VIEW V_CS AS SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept CS;大部分人都能写到这一步但丢分往往在细节上。第一视图名不能和已有表名、视图名重名第二如果SELECT子句里出现了表达式、聚合函数或者多表连接后有重名字段必须在视图名后面用括号列出字段名比如写成CREATE VIEW V_CS_Avg(Sno, AvgGrade) AS ...第三WHERE条件里的字符串要正确使用单引号这个错误在机考里特别常见平时写惯了Java、Python字符串容易顺手用双引号。再进阶一点视图可以建立在已经存在的视图之上这叫“嵌套视图”。比如CREATE VIEW V_CS_MALE AS SELECT Sno, Sname FROM V_CS WHERE Ssex 男;教材里明确支持这种写法考试时不用怕按普通视图的创建规则套用即可。但要注意嵌套视图的层次不能过深实际工作中也不建议多层嵌套否则查询效率会明显下降排查问题时还很难追踪数据来源。2.3 视图更新限制WITH CHECK OPTION是高频考点视图能不能用INSERT、UPDATE、DELETE语句更新数据答案要看视图是否满足“可更新视图”的条件。教材里给出的判断标准一是视图必须基于单个基本表二是视图必须包含基本表的主键或候选键使得每一行能被唯一标识三是视图中不能包含聚合函数、DISTINCT、GROUP BY、HAVING等成分。所以像下面这种分组统计视图只能查询不能更新CREATE VIEW V_Cnt(Cno, Num) AS SELECT Cno, COUNT(*) FROM SC GROUP BY Cno;你不可能通过这个视图去INSERT一行到SC表因为数据库根本不知道新增的“选课人数”该落到哪个具体元组上。这个逻辑想通了比死记硬背条件要牢靠得多。还有一个很容易被忽略的点是WITH CHECK OPTION。看这道题题目定义计算机系学生视图时加上WITH CHECK OPTION然后通过该视图把某个学生的院系改成“MA”会发生什么CREATE VIEW V_CS AS SELECT Sno, Sname, Sdept FROM Student WHERE Sdept CS WITH CHECK OPTION;执行UPDATE V_CS SET Sdept MA WHERE Sno 2001;时数据库会拒绝这条语句。因为WITH CHECK OPTION表示“对视图进行插入、修改、删除操作时系统会检查数据是否满足视图定义里的WHERE条件”修改后的Sdept变成MA不再满足Sdept CS这个条件所以操作不被允许。这个机制有什么用打个比方视图就像一个装了门禁的房间WITH CHECK OPTION就是门禁规则你从房间里搬东西出去可以但搬进来的东西必须符合这个房间的“准入标准”。实际开发中这个选项能有效防止误操作污染数据我参与过的一个权限管理模块就是这么做的对外提供分层视图业务侧更新数据时只能碰自己权限范围内的行越过边界的操作直接报错。2.4 删除与管理视图一句话里也有考法管理类题目相对简单但有个高频陷阱。题目删除视图V_CS应使用哪条语句答案是DROP VIEW V_CS;。注意这里不是DELETE FROM V_CS。DELETE是删除视图结果集中的“数据行”但视图本身不存在物理数据真正删的是基表里的数据而DROP VIEW才是删除视图这个“定义对象”。这个区别一定要分清我见过不止一个同学在机考时把这两条语句写混直接导致后续题目全部跑偏。MySQL里还可以用DROP VIEW IF EXISTS V_CS;避免报错标准SQL考试里一般不要求写IF EXISTS但工作中这是个习惯性操作。视图删除后基于该视图建立的其他视图也会失效属于“牵一发而动全身”这个知识点在简答题里偶尔会考到回答时提一句“级联影响”就能拿全分。3. 索引的应用与设计答选择题比写SQL更难的是“选对列”3.1 索引分类的辨析这几组概念别再混淆索引题喜欢在概念分类上做文章。最常见的就是区分“唯一索引”和“非唯一索引”、“聚集索引”和“非聚集索引”、“单列索引”和“复合索引”、“B树索引”和“哈希索引”。看这道题题目下面关于索引的说法错误的是A. 一个表上只能创建一个聚集索引 B. 一张表可以有多个非聚集索引 C. 唯一索引既能保证数据唯一性也能加速查询 D. 哈希索引适合范围查询B树索引适合等值查询答案是D。恰恰说反了哈希索引极其适合等值查询因为哈希函数的特性决定了它能一次定位到目标桶但遇到范围查询比如WHERE age BETWEEN 20 AND 30哈希索引就没法按顺序遍历性能反而不如B树。B树索引因为叶子节点形成了有序链表既能等值定位也能范围扫描是绝大多数关系型数据库的默认选择。A选项里“一个表只能有一个聚集索引”是对的。聚集索引的意思是表中数据的物理存储顺序和索引的逻辑顺序一致就像一本字典的正文按拼音排列一样一本书只能有一种物理排列方式所以聚集索引只能有一个。B选项也是对的辅助索引非聚集索引可以建很多个只是每个都会占用额外空间并增加写操作负担。3.2 创建索引的SQL语法简单真正考点在设计选择写索引的SQL本身不难CREATE UNIQUE INDEX Idx_SC ON SC(Sno, Cno);这一句在选课表SC上基于学号和课程号创建了一个唯一索引含义是同一名学生选修同一门课只能有一条记录。很多教材的练习都会拿这个当例子因为它既演示了唯一索引的创建又体现了业务约束选课记录不允许重复。索引题目真正的难点不在“怎么写”而在“该不该写”。比如这种选择题题目以下哪个列最适合创建索引A. 性别列取值只有“男”“女” B. 频繁更新的价格列 C. 订单表中的订单号列 D. 包含大量NULL值的备注列答案C。理由很直接索引的价值在于快速过滤出少数行如果一列取值只有两种查询时无论索引怎么走都要扫过一半数据优化器大概率直接放弃索引走全表扫描。这就是“索引选择性”的概念——列的重复值越少选择性越高索引越有价值。订单号几乎每条记录都不重复区分度极高建索引效果最好。B选项是有名的“索引反模式”列频繁更新意味着索引结构要跟着不断调整写操作的代价大增如果这个列查询需求又没那么强烈性价比很差。D选项也不适合大量NULL值会让索引结构稀疏很多数据库连NULL都不写进普通索引里用不上力。3.3 聚集索引与辅助索引简洁题的标准答法这类题如果在简答题里出现其实并不难答但很多人容易答不到点子上。题目简述聚集索引与非聚集索引的区别。我一般建议分四点来答。第一物理顺序聚集索引的表数据按索引键值的顺序物理存储而非聚集索引的表数据物理顺序和索引顺序无关。第二数量限制一个表只能有一个聚集索引非聚集索引可以有多个。第三查找过程通过聚集索引定位到键值时数据行就在旁边一次就能拿到整行通过非聚集索引查找时通常先在索引里找到主键值再根据主键去聚集索引里回表查询一次完整数据这个过程叫回表。第四典型应用InnoDB引擎的主键索引就是聚集索引而针对普通字段建的索引一般是辅助索引。回表这个点我多说一句它是面试里特别爱追问的细节。假设一张学生表以Sno为主键建了聚集索引又在Sname上建了非聚集索引执行SELECT * FROM Student WHERE Sname 张三数据库会先在Sname索引里定位“张三”拿到对应的主键Sno再用Sno去聚集索引里找完整行整个过程经历了两次索引查找。如果查询的字段恰好都包含在Sname索引里比如只查Sname和Sno数据库就可以不用回表直接返回这种“索引覆盖”的情况就是覆盖索引效率最高。复习时理解清楚这个链路比死背“非聚集索引需要回表”要灵活得多。4. 从一条综合题看索引失效的完整排查链路4.1 索引失效的典型场景选择题爱考的四种写法索引建了查询却不走索引这就是“索引失效”。教材里提到的不多但实际工作和各种考试里出现频率极高。我总结出四种最常见的失效写法。第一种对索引列使用函数或运算。比如WHERE YEAR(birth_date) 1995即使birth_date建了索引因为每一行都要先计算YEAR函数索引的有序性被破坏优化器只能放弃索引。正确的写法是WHERE birth_date 1995-01-01 AND birth_date 1996-01-01让索引直接发挥范围扫描能力。第二种隐式类型转换。比如手机号列phone是VARCHAR类型查询写成WHERE phone 13800000000数字常量会被隐式转换为字符串但转换过程可能让优化器无法准确匹配索引。这一点在不同数据库上表现不完全一样但考场里基本默认是坑。第三种LIKE查询以通配符开头。WHERE name LIKE %张三%无法使用索引因为索引按从左到右的顺序匹配前缀开头就是模糊的没法定位。但WHERE name LIKE 张三%就可以走索引。第四种OR条件中包含了非索引列。比如WHERE age 18 OR name 张三如果只有age列有索引而name列没有数据库要同时处理两边条件为了拿到name张三的那部分只能回头走全表扫描整个查询的索引就废了。遇到这种情况可以考虑把OR拆成UNION或者给name也建索引。4.2 一条EXPLAIN语句的完整排查案例选择题考场景识别综合题就会考排查思路和方法了。我设计一道典型题目题目订单表orders(order_id, user_id, amount, status, create_time)其中status列已建索引。查询语句SELECT * FROM orders WHERE status PAID执行缓慢用EXPLAIN查看发现type为ALL全表扫描。请排查原因并给出改进建议。这类题我建议按三步来答。第一步先确认索引是否存在这是最容易忽略的点很多人一上来就分析SQL结果索引压根没建。第二步看数据分布如果status字段上绝大多数记录都是‘PAID’只有极少数是其他状态那查询优化器会认为走索引和全表扫描差别不大甚至全表更快于是主动放弃索引这是非常正常的“优化器决策”。第三步复现问题时要留意统计信息是否过期如果表数据量变化很大而统计信息没更新优化器也会做出错误判断。改进方案一般从几个方向入手如果业务场景里极少查询具体状态码而对复合条件查询更多可以考虑创建复合索引比如(status, create_time)如果数据量确实大可以按时间做分区表把扫描范围限制在一个分区内还有一种做法是把大查询拆成多个等值条件的UNION ALL提高优化器选择索引的概率。在实际排障时我习惯先看EXPLAIN里的possible_keys和key两列possible_keys表示优化器“可能用到”的索引key表示“实际用到的”索引。如果possible_keys有值但key为空说明优化器评估后选择了放弃如果possible_keys本来就是空的说明SQL写法有问题才会导致索引根本没进入考虑范围。这个区分能快速定位是“索引设计问题”还是“SQL写法问题”排查效率高很多。4.3 一道关于最左前缀原则的坑题复合索引的考点里“最左前缀原则”是必考内容而且经常配合坑题出现。题目表student上创建复合索引(sname, sage, ssex)以下查询中哪个无法使用该索引A. WHERE sname 张三 B. WHERE sname 张三 AND sage 20 C. WHERE sage 20 AND ssex 男 D. WHERE sname 张三 AND ssex 男答案是C。复合索引从左到右按字段顺序构建B树查询条件里必须包含最左边的字段才有可能用上索引。C选项直接跳过了sname从第二个字段开始索引的树形结构就派不上用场这就是“最左前缀”的含义。这个坑很隐蔽的地方在于很多同学以为“只要WHERE里出现了索引里的字段就行”忽略了顺序要求。实际上只要sname出现在条件里后面字段的顺序是否合理只是影响“能用索引的深度”但至少索引能起步完全没有sname就完全用不上这个复合索引。写到这里我想起自己的一个教训以前图省事给表里所有可能查询的字段建了一个大复合索引结果因为字段顺序和业务查询不匹配很多SQL还是全表扫。后来学乖了建复合索引之前先统计业务里最常出现的WHERE条件组合把区分度最高、最常作为过滤起点的字段放最左边。5. 复习节奏与易错点复盘考前重点看这份清单5.1 我刷题库的顺序从概念题到场景题如果你正准备考试我建议不要上来就刷综合题。先把概念判断题过一遍比如视图是不是虚表、索引能不能加速排序、聚集索引一个表能有几个这一轮过关后再写SQL题把CREATE VIEW、CREATE INDEX的语法练到不假思索。最后再碰场景题就是“给定一条慢SQL分析问题并优化”这种因为场景题考的是综合判断能力前面两类题的积累会在这里同时发挥作用。我自己带过几个新人备考发现一个普遍问题大家更愿意刷写SQL的题觉得有代码才是实战反而忽视概念题。但考试里丢分最多的恰恰是概念辨析。尤其像“视图更新条件”“WITH CHECK OPTION”这种细节SQL写法可能只有一个标准答案但概念题能变出十几种说法稍不留神就踩坑。所以建议把概念题和处理成简答题的考点用自己话先复述一遍能讲清楚才算真正掌握。5.2 这份易错清单请直接背下来我整理了这张表算是我自己复习和带人过程中沉淀下来的高频易错点考前过一遍很管用。易错点错误理解正确理解视图存储视图保存了查询结果数据视图只保存查询定义数据实时来自基表视图删除DELETE可以删除视图删除视图用DROP VIEWDELETE操作的是基表数据视图更新所有视图都可以更新必须是基于单表且含主键、无聚合无分组的视图才可更新WITH CHECK OPTION只是查询时做权限检查插入、更新、删除时都会校验是否满足视图条件索引数量索引越多查询越快索引有维护成本过多会拖慢增删改索引失效建了索引就一定能用上函数、隐式转换、前导通配符、OR条件等都会导致失效复合索引条件里出现任一索引字段就能用索引必须满足最左前缀原则最左字段不能缺席表格里最后一行是我特别想强调的。很多人在复合索引上栽跟头不是不会建而是不会用。宁可把复合索引的字段数量控制少一点也要保证每个字段都有真实查询价值。一堆“看起来可能有用”的字段堆在一起最后往往只走了一个最左字段剩下全成了空间浪费。5.3 最后分享两个项目里的实际体会工作中用视图和索引和备考有个不太一样的地方教材强调的是“正确”生产环境强调的是“权衡”。比如视图考试可能只问你能不能更新但在真实业务里我们更关心视图嵌套层级会不会拖慢查询以及视图给数据权限带来的安全收益。我自己在好几个系统里都用“按部门隔离”的视图做数据出口上层应用完全看不到全表这样就算应用被拖库攻击者能拿到的也只是一个裁剪过的视图结果集。索引这边生产环境里的心得更多。以前上线一个报表功能报表SQL要关联三张表、过滤条件还特别多最初全表扫描要跑四五秒。后来我按查询条件建了复合索引把执行时间从五秒压到几十毫秒这件事给我留下的印象特别深索引不是万能药但用对了地方收益极其明显。反过来有一次我在一张频繁写入的表上加了三个索引结果业务高峰期锁等待暴增最后删掉两个低频查询的索引才恢复平稳。索引是典型的“空间换时间”而且换来的时间还要看业务查询是不是真的用到了用不到就是纯开销。复习这一节我的建议很简单不要死背题要把每个考点还原成“数据库为什么要这么做”。理解了动机题面怎么换你都不怕。这套题库笔记里的每道题你都可以试着用“我会怎么跟别人解释这个知识点”来代替“我记住了这个答案”效果会比单纯刷题好不少。
