面试:谈谈Oracle数据库SQL改写技巧？

Oracle数据库SQL改写技巧与性能优化

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 1.1k 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，在Oracle数据库开发中，有些SQL写法几乎一眼就能看出存在潜在的性能问题。这些写法通常会导致全表扫描、不必要的排序、大量的逻辑读/物理读、高CPU消耗或网络往返。以下是一些常见的“危险信号”SQL写法及其优化方法，尽可能详细说明：

一、导致全表扫描或低效索引访问的写法

在索引列上使用函数或表达式

问题写法：

SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYYMMDD') = '20230803';
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
SELECT * FROM products WHERE price * 1.1 > 100; -- 假设price有索引

问题： Oracle通常无法使用基于order_date、last_name或price的索引，因为索引存储的是列的原始值，而不是函数/表达式计算后的值。这会导致全表扫描。

优化方法：

改写谓词，避免函数/表达式包裹索引列：

SELECT * FROM orders WHERE order_date >= TO_DATE('20230803', 'YYYYMMDD') AND order_date < TO_DATE('20230804', 'YYYYMMDD');
SELECT * FROM employees WHERE last_name = 'Smith'; -- 确保输入数据大小写一致或在应用层转换
SELECT * FROM products WHERE price > 100 / 1.1; -- 将计算移到常量一侧

如果必须使用函数且无法改写，考虑基于函数的索引(FBI)：

CREATE INDEX idx_upper_lastname ON employees(UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH'; -- 现在能使用FBI

隐式类型转换
- 问题写法：
```
SELECT * FROM customers WHERE customer_id = '12345'; -- customer_id是NUMBER类型
SELECT * FROM transactions WHERE trans_code = 1001; -- trans_code是VARCHAR2类型
```
- 问题： Oracle需要将一侧的数据类型转换为另一侧才能比较。如果转换发生在索引列上（如将customer_id(NUMBER)转换为字符串去匹配'12345'，或将trans_code(VARCHAR2)转换为数字去匹配1001），索引将失效，导致全表扫描。
- 优化方法： 确保比较的两侧数据类型完全一致：
```
SELECT * FROM customers WHERE customer_id = 12345;
SELECT * FROM transactions WHERE trans_code = '1001';
```
使用<>或NOT IN/NOT EXISTS不当
- 问题写法：
```
SELECT * FROM large_table WHERE status <> 'ACTIVE'; -- status有索引，但'ACTIVE'是少数状态
SELECT * FROM orders WHERE order_id NOT IN (SELECT order_id FROM processed_orders); -- 子查询结果集很大
```
- 问题：
  - <> (或 !=)：当列的选择性不高（即非目标值占比很高）时，优化器可能认为全表扫描比索引扫描更有效（因为索引扫描需要回表多次）。即使选择性高，Oracle有时也倾向于全表扫描。
  - NOT IN / NOT EXISTS：如果子查询返回的结果集非常大，或者NOT IN子查询包含NULL值（会导致整个条件返回UNKNOWN/FALSE），性能会非常差。NOT IN对NULL敏感。
- 优化方法：
  - 对于<>：
    - 尝试改用NOT EXISTS或LEFT JOIN ... WHERE ... IS NULL模式（如果逻辑等价）。
    - 如果status值有限，考虑显式列出所有不等于的值：WHERE status IN ('INACTIVE', 'PENDING', 'DELETED')（如果选择性高且能利用索引）。
  - 对于NOT IN/NOT EXISTS：
    - 优先使用NOT EXISTS： 它通常比NOT IN更高效，尤其在子查询结果集大时，且对NULL安全。
```
SELECT o.* FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM processed_orders p WHERE p.order_id = o.order_id
);
```
    - 使用LEFT JOIN + IS NULL：
```
SELECT o.* FROM orders o
LEFT JOIN processed_orders p ON o.order_id = p.order_id
WHERE p.order_id IS NULL;
```
    - 确保NOT IN子查询不返回NULL： 在子查询的列上加IS NOT NULL条件（但通常不如前两种方式好）。
    - 确保连接列（如order_id）上有索引。
对LIKE模式以通配符%开头
- 问题写法：
```
SELECT * FROM products WHERE product_name LIKE '%Widget%';
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
```
- 问题： LIKE '%something' 或 LIKE '%something%' 无法利用B树索引进行范围扫描（因为不知道开头是什么）。只有LIKE 'something%'可以利用索引进行前缀匹配扫描。
- 优化方法：
  - 避免前导通配符： 重新设计应用逻辑，尽量使用后缀匹配 ('ABC%')。
  - 使用Oracle Text索引： 如果必须进行复杂的模式匹配或全文搜索（包含词干、同义词等），考虑创建Oracle Text索引并使用CONTAINS操作符。
  - 考虑反向键索引(Reverse Key Index) + REVERSE函数： 对于后缀匹配 ('%xyz')，可以尝试在列上创建反向键索引，然后查询WHERE REVERSE(column) LIKE 'zyx%'。但这有局限性（破坏范围扫描，需要额外函数调用）。
  - 考虑外部搜索引擎： 对于极端复杂的模糊搜索需求，Elasticsearch/Solr可能更合适。
在WHERE子句中对索引列进行IS NULL判断
- 问题写法：
```
SELECT * FROM employees WHERE manager_id IS NULL; -- manager_id有索引
```
- 问题： 在标准的B树索引中，NULL值通常不会被索引（除非创建索引时显式指定NULL）。因此查询IS NULL无法利用该索引，会导致全表扫描。
- 优化方法：
  - 创建索引时包含NULL值 (Oracle 8i+)： 使用CREATE INDEX ... ON table_name(column_name) COMPRESS 1; 或更推荐使用位图索引（如果适用）。
  - 使用位图索引(Bitmap Index)： 如果该列基数（不同值数量）较低，且表主要用于读操作（或DML不频繁），位图索引可以高效处理NULL值查询。注意位图索引的锁机制对高并发DML不友好。
  - 添加一个NOT NULL的默认值： 如果业务允许，将列定义为NOT NULL并设置默认值（如0, ‘N/A’），避免NULL出现。
  - 使用函数索引： CREATE INDEX idx_mgr_null ON employees(NVL(manager_id, -1)); 然后查询WHERE NVL(manager_id, -1) = -1;。但需注意NVL函数的使用。

二、导致大量排序或高CPU消耗的写法

滥用DISTINCT或UNIQUE
- 问题写法：
```
SELECT DISTINCT d.department_name, e.first_name, e.last_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;
```
- 问题： DISTINCT会对结果集的所有列进行排序去重。如果结果集很大（连接后行数多）或者选择的列很多很长（如包含CLOB），排序操作会消耗大量内存（PGA）和CPU，甚至引发磁盘排序(Temp Tablespace I/O)，性能极差。很多情况下，DISTINCT被误用来消除因连接错误导致的多余行，而不是真正需要去重。
- 优化方法：
  - 检查DISTINCT是否真的必要： 仔细分析业务逻辑，是否因为连接条件错误（缺少关联条件、多对多关系）导致重复行？如果是，修正连接条件比加DISTINCT更根本。
  - 缩小结果集： 在应用DISTINCT前，通过WHERE子句或更精确的JOIN条件减少需要处理的行数。
  - 使用GROUP BY代替： 如果DISTINCT是必要的，且查询包含聚合函数，使用GROUP BY可能更清晰，有时优化器能选择更好的计划（如使用哈希聚合）。
  - 只DISTINCT真正需要去重的列： 如果可能，只选择必要的列去重。
  - 确保连接列/排序排序列有索引： 索引可以帮助排序操作更快完成（索引已有序）。
不必要的ORDER BY
- 问题写法：
```
SELECT product_id, product_name FROM products ORDER BY product_name; -- 显示给用户前100条
```
- 问题： 如果只是为了在应用层展示前几行（如分页），却对整个大表进行排序，浪费资源。
- 优化方法：
  - 使用ROWNUM或FETCH FIRST/OFFSET进行分页： 结合ORDER BY和ROWNUM/FETCH FIRST，确保只排序和返回需要的行。
```
-- 老方法 (ROWNUM)
SELECT * FROM (
  SELECT p.*, ROWNUM rnum
  FROM (SELECT product_id, product_name FROM products ORDER BY product_name) p
  WHERE ROWNUM <= 20
) WHERE rnum > 10;
-- 12c+ 新方法 (OFFSET/FETCH)
SELECT product_id, product_name FROM products
ORDER BY product_name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
```
  - 确保ORDER BY列上有索引： 这样优化器可能选择索引扫描避免排序操作（Index Range Scan Descending/Ascending）。
  - 在应用层缓存排序结果： 如果排序结果被频繁使用且变化不大。

在WHERE子句中对非索引列使用复杂表达式或函数

问题写法：

SELECT * FROM sales
WHERE (unit_price * quantity) > 1000
AND UPPER(customer_region) || '-' || UPPER(customer_country) = 'APAC-JAPAN';

问题： 这些复杂的表达式无法利用索引（除非有FBI），必须对每一行进行计算，消耗大量CPU。

优化方法：

简化表达式/拆分条件：

SELECT * FROM sales
WHERE unit_price * quantity > 1000
AND customer_region = 'APAC'
AND customer_country = 'Japan'; -- 假设应用层已转换大小写

创建基于函数的索引(FBI)： 如果表达式过滤性很好且查询频繁。

CREATE INDEX idx_sales_total ON sales(unit_price * quantity);
CREATE INDEX idx_region_country ON sales(UPPER(customer_region) || '-' || UPPER(customer_country));

添加冗余列： 如果允许，添加一个存储计算结果的列（如total_amount），并对其建索引，在写入时维护该列值。

三、导致高逻辑读/物理读或低效连接的写法

SELECT *
- 问题写法：
```
SELECT * FROM very_wide_table WHERE condition;
```
- 问题： 查询不需要的列。如果表很宽（列多、有大对象列LOB），即使WHERE条件利用了索引，回表（TABLE ACCESS BY INDEX ROWID）读取整行数据时，也会带来大量不必要的I/O（物理读）和内存占用（逻辑读），特别是当查询返回行数较多时。网络传输也可能成为瓶颈。
- 优化方法： 始终只查询需要的列！
```
SELECT col1, col2, col3 FROM very_wide_table WHERE condition;
```
笛卡尔积（缺少连接条件）
- 问题写法：
```
SELECT e.first_name, d.department_name
FROM employees e, departments d; -- 忘记了WHERE e.department_id = d.department_id
```
- 问题： 这是灾难性的！如果employees有10000行，departments有100行，结果集将是1,000,000行。消耗巨大内存、CPU、I/O资源，通常会导致应用或数据库挂起。
- 优化方法：
  - 绝对避免！ 在FROM子句列出多个表时，必须确保每个表之间都有正确的连接条件（ON或WHERE中的等值/非等值连接）。
  - 使用显式JOIN语法： 这比隐式连接（逗号分隔）更清晰，能有效减少笛卡尔积的书写错误。
```
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
```
低效或不当的表连接
- 嵌套循环连接(NL)不适合大表：
  - 问题： 当驱动表返回大量行，且被驱动表连接列没有高效索引时，嵌套循环连接效率极低（驱动表每行都要全扫描或索引扫描被驱动表）。
  - 优化： 确保被驱动表连接列有索引。如果两个表都很大，考虑让优化器选择哈希连接(Hash Join)或排序合并连接(Sort Merge Join)，可能需要收集统计信息或使用Hint（谨慎使用）/*+ USE_HASH(t1 t2) */ / /*+ USE_MERGE(t1 t2) */。
- 哈希连接(HJ)需要足够PGA：
  - 问题： 如果驱动表（构建哈希表的一方）太大，无法在PGA中容纳哈希表，会导致哈希表溢出到磁盘(Temp Tablespace)，性能急剧下降。
  - 优化： 确保PGA_AGGREGATE_TARGET/MEMORY_TARGET设置合理。尝试让较小的表做驱动表（Oracle通常会自动选择）。考虑增加过滤条件减少驱动表大小。
- 排序合并连接(SMJ)需要排序：
  - 问题： 如果输入源已经有序（如有索引），SMJ效率高。但如果需要排序的表很大，排序操作代价高昂。
  - 优化： 确保连接列上有索引（避免额外排序）。对于大结果集，HJ通常比SMJ更优。
IN子查询未优化 / EXISTS使用不当
- IN子查询问题写法：
```
SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT customer_id FROM customers WHERE credit_limit > 10000
);
```
- 问题： 老版本Oracle（或特定情况下）可能对子查询结果进行去重排序或将其物化，效率不如EXISTS或JOIN。特别是当子查询结果集很大时。
- 优化方法：
  - 优先使用EXISTS或JOIN： EXISTS通常在子查询结果集大时更高效，因为它找到一行匹配即可停止。
```
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c
  WHERE c.customer_id = o.customer_id AND c.credit_limit > 10000
);
-- 或使用JOIN
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.credit_limit > 10000;
```
  - 确保子查询关联列有索引： 对于EXISTS，子查询的关联列（如c.customer_id）和过滤列（如c.credit_limit）上的索引至关重要。
- EXISTS使用不当：
  - 问题： 如果EXISTS子查询本身非常复杂低效，或者没有正确关联（导致笛卡尔积），同样性能差。
  - 优化： 优化子查询本身，确保关联条件正确且高效，子查询中涉及的列有合适索引。

四、其他低效模式

在循环中执行单条SQL（N+1查询问题）
- 问题场景： 在应用程序代码中（如Java/Python），先查询得到一个ID列表，然后遍历这个列表，对每个ID单独执行一条SELECT ... WHERE id = ?查询。
- 问题： 网络往返次数 = N+1 (获取ID列表 + N个单行查询)。当N很大时，网络延迟和数据库解析开销成为巨大瓶颈。即使单个查询很快，总时间也会线性增长。
- 优化方法： 改为批量查询！
  - 使用IN列表：
```
SELECT * FROM items WHERE item_id IN (?, ?, ?, ..., ?); -- 绑定变量列表
```
  - 使用临时表： 将ID批量插入到全局临时表(GTT)，然后使用JOIN：
```
INSERT INTO my_temp_gtt (id) VALUES (?), (?), ...;
COMMIT; -- 如果GTT是事务级可能需要
SELECT i.* FROM items i JOIN my_temp_gtt t ON i.item_id = t.id;
```
  - 使用TABLE集合表达式： (Oracle 9i+)
```
SELECT * FROM items WHERE item_id IN (
  SELECT column_value FROM TABLE(?)
); -- 绑定一个包含ID的集合类型变量 (SQLT_NUM, SYS.ODCINUMBERLIST etc.)
```
  - 使用JOIN代替循环： 如果逻辑允许，尽量在一个SQL中完成所有工作。
滥用WITH子句（CTE） - 多次引用导致重复执行
- 问题写法：
```
WITH big_cte AS (
  SELECT ... FROM very_large_table ... -- 非常复杂昂贵的查询
)
SELECT * FROM big_cte WHERE col1 = 'A'
UNION ALL
SELECT * FROM big_cte WHERE col2 = 'B';
```
- 问题： 在Oracle 12c R1之前，Common Table Expression (CTE) 的每次引用都可能被物化执行一次。如果CTE查询很昂贵且被多次引用（如上面用了两次big_cte），会导致昂贵的查询执行两次，性能灾难。
- 优化方法：
  - 升级到Oracle 12c R2+： 引入了MATERIALIZE和INLINE提示，以及优化器增强，可以自动物化或内联CTE。
  - 在12c R2+中使用MATERIALIZE提示（谨慎）：
```
WITH big_cte AS (
  SELECT /*+ MATERIALIZE */ ... FROM very_large_table ...
) ...
```
  - 改用全局临时表(GTT)： 显式地将CTE结果插入到一个GTT中，然后在后续查询中多次引用这个GTT。这样确保只执行一次复杂查询。
  - 合并查询条件： 如果逻辑允许，尝试将多个引用合并到一个查询中，使用CASE WHEN或OR条件：
```
WITH big_cte AS (...)
SELECT * FROM big_cte
WHERE col1 = 'A' OR col2 = 'B'; -- 注意逻辑是否等价于UNION ALL
```
忽略绑定变量（大量硬解析）
- 问题写法：
```
-- 应用代码拼接SQL字符串 (Java/Python etc.)
String sql = "SELECT * FROM users WHERE username = '" + username + "'";
```
- 问题： 每次执行时username值不同，Oracle将其视为全新的SQL语句，需要进行硬解析（语法分析、语义分析、生成执行计划）。硬解析消耗大量CPU和Shared Pool Latch，在高并发下导致严重的竞争和性能下降。
- 优化方法： 强制使用绑定变量！
  - 应用层使用PreparedStatement： (Java), cx_Oracle.Cursor.prepare() (Python)等。
  - 在PL/SQL中使用绑定变量： 自然支持。
  - 设置CURSOR_SHARING = FORCE（不推荐）： 数据库级参数，Oracle尝试将字面值替换为绑定变量。可能改变语义，仅作为最后手段。

如何发现和诊断这些写法？

执行计划(EXPLAIN PLAN / DBMS_XPLAN): 最核心的工具！查看是否出现FULL TABLE SCAN, TABLE ACCESS FULL, SORT (UNIQUE, ORDER BY, GROUP BY), NESTED LOOPS 驱动大表且被驱动表无索引、HASH JOIN 需要溢出到磁盘等操作。
SQL Trace (10046 event) 和 TKPROF： 分析SQL实际执行的统计信息：逻辑读(consistent gets/ db block gets)、物理读(physical reads)、CPU时间、等待事件、解析次数等。
AWR/ASH报告： 识别系统级和SQL级的性能瓶颈，找到消耗资源最多的SQL。
SQL Monitoring (Oracle 11g+): 实时监控长时间运行SQL的执行计划和资源消耗。
SQL Health Check (SQLHC) / SQL Tuning Advisor： 官方工具，提供自动诊断和调优建议。
数据库告警日志和跟踪文件： 查看是否有ORA-04030 (PGA内存不足)、ORA-01555 (快照太旧，可能与长时间排序/查询有关)等错误。

总结： 编写高性能SQL的核心原则是：

让索引有效： 避免在索引列上使用函数、表达式、隐式转换、前导%的LIKE，确保比较类型一致。
减少访问的数据量： 只查需要的列，使用高效的WHERE条件过滤掉尽可能多的行，利用分页避免全量排序返回。
减少排序： 避免不必要的DISTINCT、UNION（用UNION ALL代替，除非确实需要去重）、ORDER BY，确保必要的排序能利用索引。
优化连接： 使用正确的连接方式（NL/HJ/SMJ），确保连接列有索引，避免笛卡尔积。优先使用EXISTS/JOIN代替IN（子查询结果集大时）。
减少解析： 使用绑定变量避免硬解析。
批量处理： 避免在循环中执行单条SQL。
理解高级特性： 谨慎使用WITH子句多次引用（尤其在老版本），理解位图索引、FBI的适用场景和代价。