MySQL与Oracle面试题详解：分页、锁、存储过程核心原理

一排人坐在会议室里面试官扔出一个看起来很普通的问题“你们项目里分页怎么写的MySQL 怎么写Oracle 怎么写差异在哪”结果好几个人都愣住甚至有人直接说“Oracle 不就用 ROWNUM 吗”就交卷了。这是这系列的第二篇我不打算讲那些网上满天飞的背诵条目只挑几个面试里出现频率高、又能真实反映水平的点把原理拆开揉碎顺便说说面试官问这道题时脑子里在想什么。内容围绕 MySQL 和 Oracle 的面试题展开适合正在找工作的人看也适合带新人的老手拿来当参考。1. 一道分页题定生死MySQL 的 LIMIT 与 Oracle 的 ROWNUM 根本不是一回事面试官问分页从来不是单纯问你语法他想用一道题同时试探三件事基础语法熟不熟、有没有真正写过慢查询、懂不懂数据库引擎层面的执行逻辑。这道题答得好后面状态会完全不同答不好后面往往就被贴上了只会背题的标签。1.1 MySQL 的 LIMIT 好写但深分页会要命MySQL 的分页太简单了简单到很多人在简历里写精通 MySQL 分页优化。但面试官只要轻轻追问一句你的表有几百万条数据翻到第 10 万页怎么办很多人就沉默了。核心原因在于一个执行细节。-- 常规写法数据量小的时候没什么问题 SELECT * FROM orders ORDER BY create_time DESC LIMIT 999990, 10;这条 SQL 看着没毛病实际上 MySQL 会先把前 100 万行捞出来排序然后把前 999990 行全部扔掉只留最后 10 行返回。这个捞出来再扔的过程就是典型的深分页性能灾难。表越大offset 越大消耗的内存和磁盘 IO 就越吓人。我在实际项目中就遇到过接口超时查了半天发现就是这条看似人畜无害的 LIMIT 语句在作怪。面试如果聊到这里千万别停留在知道有这个问题的层面要能说出解决方案场景感立刻就出来了-- 方案一记住上一页的最大值用条件代替偏移 SELECT * FROM orders WHERE create_time 2025-11-20 10:30:00 ORDER BY create_time DESC LIMIT 10; -- 方案二延迟关联先取主键再回表 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 999990, 10 ) t ON o.id t.id;方案一适合有明确排序字段且能记住位置的场景比如按时间倒序的列表页方案二在无法记住位置、必须跳页时比较实用。这两种做法本质上都是减少了回表次数或扫描范围直接回答了怎么优化这个隐含问题。还有一点值得提LIMIT 后面跟的 offset 过大时哪怕走索引也可能效率低下有些业务干脆做了限制最多只让查前 100 页这也是常见取舍。1.2 Oracle 的 ROWNUM 是个假行号这是最大的面试陷阱Oracle 的分页比 MySQL 多一层心理门槛因为 ROWNUM 这个伪列的行为实在太容易出错了。很多候选人写出来的第一版是错的原因是对 ROWNUM 的赋值时机没理解透。-- 错误写法想取第 11 到 20 行 SELECT * FROM emp WHERE ROWNUM BETWEEN 11 AND 20;这条语句返回空结果集。原因在于 ROWNUM 是在结果集生成过程中逐行赋值的第一行如果满足条件就是 1不满足就丢弃然后下一行重新赋值为 1。换句话说ROWNUM 永远从 1 开始想直接取第 11 行出发的子集根本不成立。这是 ROWNUM 和 MySQL LIMIT 最本质的区别LIMIT 是结果出来后再截断ROWNUM 是跟着行一起走的。正确的经典写法是三层嵌套SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY sal DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;很多人不理解为什么要套三层。第一层先做真正的排序第二层给排好序的结果编行号并限制最大范围第三层再去掉前面的部分。如果直接把排序放在第二层内层ROWNUM 在排序前就赋完值了取出来的永远是排序前的某些行。这个点的坑我在实际写 Oracle 报表时也踩过明明查出来的数据对不上排查半天才发现是 ROWNUM 的取值时机出了问题。1.3 窗口函数和 12c 的 FETCH FIRST答出来是亮点如果候选人能把 ROW_NUMBER() 这个方案说出来面试官通常会眼前一亮。原因很简单这说明你不只会在旧体系里打转还知道用 SQL 标准思路解决问题。SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM emp e ) WHERE rn 10 AND rn 20;Oracle 12c 及以上版本也支持了 ANSI 标准的 OFFSET FETCH 子句SELECT * FROM emp ORDER BY sal DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;这个方案虽然语法上干净但在大量数据场景下依然存在性能问题不能因为语法优雅就忽视原理。2. 锁的分类这道题背得下来不算会得讲出为什么MySQL 锁的分类大概是面试里被问得最多的基础题各种教程里都写全局锁、表锁、行锁、间隙锁、意向锁背下来很容易。但一追问间隙锁和临键锁到底锁的是什么为什么 Oracle 好像不太提间隙锁很多人就露馅了。这道题真正考察的是并发控制的基本功。2.1 MySQL 的锁从粗到细各自解决什么问题MySQL 的锁体系可以按照粒度从大到小整理成一条线全局锁、表级锁、行级锁。全局锁就是FLUSH TABLES WITH READ LOCK让整个库进入只读状态主要用在备份场景。FTWRL 会让所有写操作阻塞所以生产环境执行时必须非常小心。表级锁里除了传统意义的 LOCK TABLES还有容易忽略的元数据锁MDL这个锁是 MySQL 5.5 之后加的用来保护表结构定义。MDL 锁有个经典问题一个事务在操作大表另一个会话执行 ALTER TABLE 就会堵住后面所有对这个表的访问都会排队连查询都进不来。我遇到过一次线上事故就是 ALTER TABLE 卡住引发查询堆积。行级锁是 InnoDB 的核心武器也是面试重头戏。要分清楚三个概念记录锁、间隙锁、临键锁。Record Lock 只锁一行Gap Lock 锁的是一个开区间Next-Key Lock 是前两者合并锁的是当前行和它前面的间隙。为什么需要间隙锁因为 InnoDB 默认隔离级别是 RR可重复读光锁行解决不了幻读——你在事务期间查一个范围另一事务插入了满足条件的新行你查出来数量变了。间隙锁把范围锁住别人在这个空隙里插不进去幻读就被预防住了。这是为什么要有间隙锁的标准解释面试时要主动讲出来。2.2 Oracle 的锁机制和 MVCC是理解为什么没有间隙锁的前提Oracle 面试题里锁的问题不像 MySQL 那么多但面试官一旦把两个库放在一起问就是下钩子为什么 Oracle 不太需要间隙锁因为 Oracle 靠 MVCC多版本并发控制和 undo 数据实现了一致性读。读操作不会阻塞写写操作也不会阻塞读。一个 SELECT 看到的是某个 SCN 下的快照版本而不是当前物理行。就算另一个事务插入了新行正在执行的查询也看不到因为快照里没有它。所以在 Oracle 中幻读的问题主要通过 undo 机制解决不需要像 MySQL 那样用间隙锁去物理上挡住插入。Oracle 的锁主要分 TM表级 DDL 锁和 TX事务锁。TM 锁保护表结构防止事务进行中表被 drop 或修改TX 锁是在行上获取的锁配合 undo 里的前镜像实现并发。还有一个细节容易被忽略Oracle 的行锁其实是在索引条目上实现的如果表没有索引锁会升级为整个表锁。这个问题放在面试里通常是问为什么 Oracle 建议表要有主键答案之一就是避免行锁升级成表锁。2.3 死锁问题两个数据库的处理风格完全不同死锁这道题答到互相持有对方需要的资源只能算及格。真正的分水岭是两个库各自怎么处理。InnoDB 默认会开启死锁检测发现死锁后自动回滚其中一个事务让另一个继续跑同时记录一条包含事务 id 的日志。而 Oracle 的死锁处理是服务器后台进程发现死锁后直接报出 ORA-00060: deadlock detected并把其中一个事务的语句回滚掉事务本身还在用户可以选择提交或回滚。一个是有内建检测主动回滚一个是通过错误通知强迫应用处理背后的设计哲学不同面试提一嘴会显得很懂。实际生产里我更关心的是如何避免死锁。固定的加锁顺序、事务尽量短、大查询分批处理这些是经验之谈。面试时能举一个自己线上处理死锁的案例比空谈原理有力得多。3. Oracle 存储过程为什么是面试重灾区考点到底在哪存储过程在 MySQL 面试里问得不算太深但在 Oracle 面试里几乎是必考。原因很现实Oracle 在传统企业级系统里的生态太强了大量核心业务逻辑跑在 PL/SQL 里面试官必须确认候选人能看懂、能改、能写。这一节我只整理几个真正高频又容易翻车的点。3.1 PL/SQL 块结构和简单的过程骨架必须张口就来很多候选人对存储过程的理解是一串 SQL 放在库里。这话不能说错但面试官想听到的是块结构意识。PL/SQL 的基本结构是声明区、执行区、异常区CREATE OR REPLACE PROCEDURE get_emp_salary ( p_emp_id IN NUMBER, p_salary OUT NUMBER ) IS -- 声明区局部变量 v_bonus NUMBER : 1000; BEGIN -- 执行区 SELECT salary INTO p_salary FROM emp WHERE emp_id p_emp_id; p_salary : p_salary v_bonus; EXCEPTION -- 异常区 WHEN NO_DATA_FOUND THEN p_salary : 0; END;注意这里有个非常容易踩的坑SELECT INTO语句必须确保返回一行多了会报 TOO_MANY_ROWS少了会报 NO_DATA_FOUND。很多初学者没有这个意识上线后数据一变存储过程直接报错。面试里能把 NO_DATA_FOUND 和 TOO_MANY_ROWS 的区别讲清楚已经比一半候选人强了。3.2 游标、异常、事务控制是拉分的关键点Oracle 存储过程面试游标几乎绕不开。显式游标和隐式游标的区别处理数据量时如何优化批量操作这些都是高频考点。我给出一个实际改过的写法批量更新时用 BULK COLLECT 配合 FORALL效率比循环里逐条 UPDATE 高一个量级DECLARE TYPE emp_ids IS TABLE OF emp.emp_id%TYPE; v_ids emp_ids; CURSOR c_emp IS SELECT emp_id FROM emp WHERE deptno 10; BEGIN OPEN c_emp; FETCH c_emp BULK COLLECT INTO v_ids; CLOSE c_emp; FORALL i IN 1..v_ids.COUNT UPDATE emp SET bonus bonus * 1.1 WHERE emp_id v_ids(i); END;慢在哪逐条 UPDATE 要反复执行 SQL 引擎和 PL/SQL 引擎之间的上下文切换。批量操作正是解决这个问题的核心思路。这类优化面试官非常喜欢听因为它直接和线上系统性能挂钩。事务控制方面还有一个坑存储过程里默认需要有 COMMIT 或 RETURN 来控制提交实际开发中到底在哪里 COMMIT 要看业务流程。自治事务PRAGMA AUTONOMOUS_TRANSACTION也是一个经典考点常用于写审计日志避免日志插入失败影响主事务。这个如果面试官不问你不用主动展开一旦问了能答出原理就非常加分。3.3 和 MySQL 存储过程一对比差异点就是考题另一个常见问题MySQL 存储过程和 Oracle 的有啥区别这个问题本身就是一个整合考点。对比项OracleMySQL块结构DECLARE / BEGIN / EXCEPTION / ENDBEGIN / END配合 DELIMITER 切换分隔符参数模式IN / OUT / IN OUTIN / OUT / INOUT错误处理EXCEPTION 区异常种类丰富DECLARE ... HANDLER写起来更繁琐支持包支持 PACKAGE不支持事务控制支持自治事务、SAVEPOINT 等支持但自治事务要改配置写起来绕MySQL 的存储过程有个老牌经典坑默认情况下一条语句执行完就悄悄提交了想在过程里控制事务必须手动管理而且不能用 RETURN 返回结果集只能通过 OUT 参数带出。相比之下Oracle 的 PL/SQL 更像一门成熟的开发语言语法精细、工具链完善。面试时能把这个比较说得有层次说明你对两个数据库都有真实的项目体验。4. 排序类题目最容易答偏索引排序、filesort 与临时表空间排序的问题表面对准语法实际上是在问数据库为什么要费劲排序排序一定能用索引吗。MySQL 和 Oracle 在排序上都有各自的细节也是面试官试探候选人会不会真的查过慢 SQL的好题。4.1 MySQL 的 ORDER BY什么时候走索引什么时候走 filesortMySQL 处理 ORDER BY 有两种策略一种是用索引天然有序直接输出另一种是拿到数据后再排序filesort。用了索引就不用额外排序这是效率最高的路径。什么样的 ORDER BY 能走索引核心规则是排序字段正好符合索引列顺序且和 WHERE 条件能拼成一个完整的联合索引前缀。-- 联合索引 (deptno, salary) SELECT * FROM emp WHERE deptno 10 ORDER BY salary DESC;这条语句里deptno 提供等值过滤salary 提供有序输出联合索引能一步到位。但如果改成 ORDER BY salary单独对第二个字段排序没有最左前缀支持索引就用不上MySQL 就要把数据捞出来做 filesort。很多人把ORDER BY 能用索引背成了万能结论实际上对联合索引的理解不到位一追问就露馅。我见过不少慢查询都是因为开发以为走索引就完了实际 EXPLAIN 里明明白白显示 Using filesort。filesort 还分内存版和磁盘版。数据量小的时候在 sort_buffer 里排非常快数据量一大放不下就写临时文件多路归并性能断崖式下跌。优化思路一般是三条让排序走索引、把不需要的字段剔除缩小排序行大小、适当调大 sort_buffer_size。但第三条不能盲目调我看过有人一上来就调 256MB结果服务器内存全部吃紧。4.2 Oracle 的排序PGA 与临时表空间的分工逻辑Oracle 里排序优先在内存中的 PGAProgram Global Area里做如果内存不够就往临时表空间写。面试官问临时表空间是干嘛的标准答案之一就是排序和哈希操作的空间。深层考点是为什么用户能感觉到临时表空间暴涨因为一条大排序 SQL 可能一次性消耗几十 GB 的临时空间。Oracle 里的排序走不走索引和 MySQL 底层思路类似如果 ORDER BY 能完全使用索引列的有序性就不需要额外排序。但 Oracle 的执行计划里更常见的是SORT ORDER BY和INDEX FULL SCAN两种路径。面试时可以提一点经验如果一条 SQL 频繁执行且排序字段固定可以考虑建立对应组合索引来消除排序但如果是报表类大批量查询排序本身往往无法避免更实际的做法是优化 PGA 内存、确保临时表空间有合理增长空间。还有个小技巧分页场景下比如ORDER BY create_time DESC如果数据量大且有大量并发翻页可以考虑用降序索引这属于优化向的加分点。4.3 一个很容易被追问的细节排序稳定性面试官尤其喜欢在排序题最后补一刀那如果 ORDER BY 的字段有重复值分页结果会不会乱MySQL 和 Oracle 的默认行为并不完全相同经验不足的人会懵。说得严谨一点Oracle 的逻辑是如果你不指定 SECONDARY 排序稳定性和数据块的物理读取路径有关MySQL 在 use index 排序时也不保证完全稳定。所以在业务上如果你要求每一页数据绝对不能重复或遗漏必须在 ORDER BY 后面补一个唯一字段比如ORDER BY create_time DESC, id DESC。这是我做支付账单分页时踩过的坑不加 id 时翻到第二页可能出现和第一页重了一条数据。这个细节说出来面试官马上能判断你是真的处理过分页数据一致性问题的人。5. 面试官连环追问的底层逻辑以及怎么准备才算真的懂到了这一步单点知识已经讲完了但面试终究不只是背零散知识点面试官的追问都是串起来的。很多人感觉这些问题我都会就是答不透其实是没理解追问的链条。梳理一下这个系列面试题里最常见的追问路径比多刷十道题还有用。5.1 一个典型的追问链从分页一路问到 MVCC我模拟一下真实的面试节奏开始问分页的语法接着问深分页性能你答了延迟关联面试官满意马上接一句为什么 LIMIT 深了会慢你答要排序、要回表面试官再问那你说说这条 SQL 的锁是怎么加的这里很多人就开始卡壳。因为分页查询也是一个范围查询它同样会涉及间隙锁、临键锁如果隔离级别是 RR那锁的范围不等于最终返回的那 10 行而是扫描过程中触碰过的所有间隙。到了这一步面试官已经把你从基础语法带到了InnoDB 锁机制最后再问一句那如果换成 Oracle锁的行为会变吗直接点到 MVCC。能顺着这条链跌跌撞撞走完的人哪怕有几个地方答得不够精确面试官一般也会给过。因为他清楚这已经是真实排查 SQL 问题时需要的完整思维链了。所以准备面试时不要一个一个知识点孤立地背试着用一个 SQL 在数据库里到底怎么执行贯穿起来解析器怎么处理、优化器怎么选路径、执行器怎么拿锁、缓冲池怎么起作用、排序在哪发生、隔离级别怎么影响快照、返回结果时怎么提交或回滚。这套逻辑能串起来才叫懂数据库不是会背题目。5.2 给正在准备面试的人几个压箱底的建议第一准备一个小本子或文档专门记录真实的线上故障不用多三个以内就够。比如有一次 ALTER TABLE 引发了 MDL 锁排队深分页查询导致接口超时后来怎么用延迟关联解决的Oracle 存储过程批量更新太慢改 FORALL 之后快了 30 倍。这种真实案例在面试里一抛出来比任何八股文都有说服力。第二动手建一个性能测试环境导入几十万行数据自己验证一下 LIMIT 深分页到底有多慢。数据是骗不了人的你有这个实验经历被追问细节时底气完全不同。第三把两个数据库的对比维度常挂在嘴边事务隔离级别、锁粒度、MVCC 实现、分页方式、存储过程语法、索引组织方式。面试官喜欢用对比题来考察经验边界你能主动对比等于在引导他问你会的内容。我在实际带人的过程中发现能在这类面试里拿高分的往往不是背题最多的那个而是真的动手踩过坑、写过慢 SQL、调过存储过程的人。面试题只是过滤器过滤器后面的真本事要靠一行一行代码、一次一次故障复盘攒出来。这也是这个系列一直想表达的题目是结果能力才是原因。