ORACLE中的SQL如何进行优化，都有哪些方式？｜阿里巴巴数据分析面经解析

60 秒回答模板

我会从五个层次回答 Oracle SQL 优化。第一，先定位问题 SQL，确认慢在解析、执行、I/O、锁等待、排序还是网络返回。第二，查看真实执行计划，重点看访问路径、连接顺序、连接算法、基数估算和实际行数差异。第三，保证优化器有正确统计信息，包括表、索引、列直方图和分区统计信息。第四，再做具体优化：合理建索引、避免索引列上使用函数或隐式转换、让谓词尽量下推、减少返回列和中间结果、优化 join、排序、分页和聚合。第五，大表场景考虑分区、并行、物化视图或 SQL 改写；hint 只作为稳定计划或临时干预手段，不能替代根因分析。最后必须做前后对比，用执行时间、buffer gets、disk reads、rows processed、等待事件和执行计划变化说明优化是否真正有效。

考点 定位瓶颈

主线 真实执行计划

易错点 只回答加索引而不分析执行计划、统计信息和数据分布，会显…

深入解析

定位瓶颈

优化前要先确认问题边界：是单条 SQL 慢、并发时慢、某个绑定变量慢，还是某个时间窗口整体变慢。Oracle 中常用 V$SQL、AWR、ASH、SQL Monitor、应用日志和会话等待事件来定位。需要区分 CPU 消耗高、逻辑读过多、物理 I/O 高、锁等待、临时表空间排序、解析过多、网络传输过大等不同原因。

真实执行计划

Oracle SQL 优化的核心是执行计划。不能只说 EXPLAIN PLAN，因为它可能不是运行时真实计划，更可靠的是结合 DBMS_XPLAN.DISPLAY_CURSOR 查看实际执行过的 SQL、绑定变量、谓词信息、行数估算和实际执行统计。重点关注访问方式、join 顺序、join 方法、大规模排序、回表、临时空间、笛卡尔连接，以及估算行数和实际行数是否严重偏离。

统计信息

Oracle 优化器依赖统计信息做成本估算。表数据量变化大、数据倾斜明显、分区新增频繁、索引重建或批量导入后，如果统计信息过旧，优化器可能选择错误访问路径和 join 顺序。常见手段包括使用 DBMS_STATS 收集表、索引、分区统计信息，对倾斜列收集直方图，保证采样比例和收集时间合理。

索引设计

索引优化要围绕过滤条件、连接字段、排序字段和数据选择性来设计。高选择性字段、常用 join 字段、where 条件中的组合过滤字段适合考虑索引；组合索引要注意前导列顺序。过多索引会增加 DML 成本，也可能让优化器选择不稳定计划。对低选择性字段、频繁更新字段、大范围扫描场景，要谨慎建索引。

避免索引失效

常见问题是对索引列使用函数、表达式或发生隐式类型转换，例如对日期列做格式化、对字符列和数字常量混用、在列上做计算，都会让普通 B-tree 索引难以直接使用。更好的方式是改写谓词，把函数或计算移到常量侧，使用范围条件表达日期区间，保证字段类型一致。

SQL 改写

SQL 改写的目标是尽早过滤、减少中间结果、减少重复计算。能在 where 中提前过滤的条件不要拖到外层；能通过谓词下推减少扫描范围的查询，要避免被不必要的视图、函数或复杂表达式阻断。exists、in、join 没有绝对优劣，关键看数据量、选择性、是否去重、是否相关子查询以及优化器能否转换。

Join 优化

多表关联时，应关注驱动表选择和 join 方法。小结果集驱动大表并通过索引访问，常适合 Nested Loops；大表之间批量关联，Hash Join 往往更合适；Sort Merge Join 多见于有序数据或特定条件。join 条件缺失会造成笛卡尔积，join 字段数据类型不一致会引发隐式转换。

分页和大结果集

分页优化要避免深分页导致大量扫描和排序。Oracle 中可以使用 rownum 或 fetch first 语法实现分页，但关键仍是 order by 字段是否有合适索引、是否能先定位起始范围、是否必须返回很深页码。对于列表查询，应减少 select *，只返回必要列；对大结果集导出类 SQL，要区分在线查询和离线任务。

分区与并行

当表很大且查询天然按时间、地区、租户、业务线等维度过滤时，可以考虑分区表。分区的价值在于分区裁剪、分区级维护、降低单次扫描范围和提升运维效率。hint 和并行查询可以用于特定场景，但不应替代统计信息、SQL 改写和索引设计。

指标闭环

优化完成后要做可量化对比，而不是只说执行计划变了。常见指标包括响应时间、执行次数、逻辑读 buffer gets、物理读 disk reads、CPU time、elapsed time、返回行数、临时空间、等待事件和 plan hash value。还要在接近生产的数据量和绑定变量下验证，避免只在小数据测试环境中有效。

易错点

只回答加索引而不分析执行计划、统计信息和数据分布，会显得优化思路很浅。
把走索引等同于一定更快，忽略大范围扫描、回表成本和全表扫描的顺序读优势。
只看 EXPLAIN PLAN，不看真实执行过的计划和实际行数，容易被估算计划误导。
忽略统计信息，直接用 hint 强行改计划，短期可能有效，长期容易导致计划僵化。
优化后没有量化前后对比，只说快了很多，无法证明优化结果可复现。

面试官追问

Oracle 中如何判断 SQL 是否走了索引？

查看真实执行计划中的访问路径。如果出现 INDEX RANGE SCAN、INDEX UNIQUE SCAN、INDEX FULL SCAN 等，说明使用了索引；但还要看是否大量回表、是否扫描行数过多、是否比全表扫描更划算。

为什么加了索引 SQL 还是慢？

可能是索引选择性低、组合索引前导列未命中、对索引列使用函数或隐式转换、返回比例过高导致回表成本大、统计信息不准、绑定变量导致计划不适合当前参数，或真正瓶颈在排序、join、锁等待和网络传输。

exists 和 in 哪个性能更好？

不能简单说哪个一定更好。Oracle 优化器可能把它们转换成半连接或其他等价执行方式。判断时要看子查询是否相关、子查询结果集大小、是否有索引、是否需要去重、是否存在 null 语义差异，以及实际执行计划。

为什么不要在索引列上使用函数？

因为普通 B-tree 索引保存的是列的原始值，如果查询条件变成函数结果，优化器通常无法直接用原索引定位数据，只能扫描更多数据。可把函数移到常量侧、改成范围条件，或建立函数索引。

AWR 和 ASH 怎么用？

AWR 适合看一段时间内数据库整体负载、Top SQL、等待事件、I/O 和实例级趋势；ASH 更适合分析活跃会话在某个时间点附近具体等待什么、执行哪条 SQL、卡在哪个执行阶段。