印刻数据库

外观

Oracle SQL优化技巧 ​

SQL优化是Oracle数据库性能优化的核心，良好的SQL设计可以显著提高数据库的执行效率。本文将介绍Oracle SQL优化的原则、具体技巧、最佳实践和常见问题，帮助开发人员编写高效的SQL语句。

SQL优化概述 ​

什么是SQL优化？ ​

SQL优化是指通过调整SQL语句的结构、使用合适的索引、优化表设计等方式，提高SQL语句的执行效率，减少资源消耗，提高数据库的整体性能。

SQL优化的重要性 ​

  • 提高查询响应速度
  • 减少数据库资源消耗（CPU、内存、I/O）
  • 提高数据库的并发处理能力
  • 降低系统维护成本
  • 提升用户体验

SQL优化的目标 ​

  • 减少逻辑读（Logical Reads）
  • 减少物理读（Physical Reads）
  • 减少CPU消耗
  • 减少执行时间
  • 优化执行计划

SQL优化原则 ​

1. 只查询需要的列 ​

避免使用SELECT *，只查询需要的列，减少网络传输开销和I/O消耗。

sql
-- 不好的写法
SELECT * FROM employees WHERE department_id = 60;

-- 好的写法
SELECT employee_id, first_name, last_name, email, salary FROM employees WHERE department_id = 60;

2. 只查询需要的行 ​

使用合适的WHERE条件过滤数据，减少返回的行数。

sql
-- 不好的写法
SELECT * FROM employees WHERE salary > 5000 ORDER BY hire_date;

-- 好的写法
SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary > 5000 AND department_id = 60 ORDER BY hire_date;

3. 使用绑定变量 ​

使用绑定变量，避免硬解析，提高SQL语句的重用性。

sql
-- 不好的写法：每次执行都会产生硬解析
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;

-- 好的写法：使用绑定变量，只产生一次硬解析
SELECT * FROM employees WHERE employee_id = :emp_id;

4. 避免在查询条件中使用函数 ​

避免在查询条件中使用函数或表达式，否则会导致索引失效。

sql
-- 不好的写法：在查询条件中使用函数
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2023';

-- 好的写法：避免使用函数
SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

5. 合理使用索引 ​

为查询条件中的列添加合适的索引，提高查询效率。

sql
-- 添加索引
CREATE INDEX emp_salary_dept_idx ON employees(salary, department_id);

-- 使用索引的查询
SELECT * FROM employees WHERE department_id = 60 AND salary > 5000;

6. 优化JOIN操作 ​

合理安排表的连接顺序，使用合适的连接方法，添加合适的索引。

sql
-- 好的写法：小表作为驱动表，连接表有合适的索引
SELECT e.first_name, e.last_name, d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id = 60;

7. 避免使用复杂的查询 ​

将复杂查询拆分为多个简单查询，提高查询效率和可维护性。

sql
-- 复杂查询
SELECT * FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > 10000
) WHERE department_id IN (60, 70, 80);

-- 拆分为多个简单查询
CREATE TABLE temp_avg_salary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 10000;

SELECT * FROM temp_avg_salary WHERE department_id IN (60, 70, 80);

具体优化技巧 ​

1. WHERE子句优化 ​

1.1 避免使用IS NULL或IS NOT NULL ​

避免在WHERE子句中使用IS NULL或IS NOT NULL，否则会导致索引失效。

sql
-- 不好的写法
SELECT * FROM employees WHERE manager_id IS NULL;

-- 好的写法：为manager_id添加默认值
SELECT * FROM employees WHERE manager_id = 0;

1.2 避免使用不等于（!=, <>, NOT） ​

避免在WHERE子句中使用不等于，否则会导致索引失效。

sql
-- 不好的写法
SELECT * FROM employees WHERE department_id != 60;

-- 好的写法：使用等于或范围查询
SELECT * FROM employees WHERE department_id IN (70, 80, 90);

1.3 避免使用LIKE '%xxx' ​

避免在LIKE子句中使用前导通配符，否则会导致索引失效。

sql
-- 不好的写法
SELECT * FROM employees WHERE last_name LIKE '%son';

-- 好的写法：使用后缀通配符
SELECT * FROM employees WHERE last_name LIKE 'son%';

-- 或者使用全文索引
CREATE INDEX emp_last_name_fti ON employees(last_name) INDEXTYPE IS CTXSYS.CONTEXT;
SELECT * FROM employees WHERE CONTAINS(last_name, 'son') > 0;

1.4 使用IN替代OR ​

使用IN替代OR，提高查询效率。

sql
-- 不好的写法
SELECT * FROM employees WHERE department_id = 60 OR department_id = 70 OR department_id = 80;

-- 好的写法
SELECT * FROM employees WHERE department_id IN (60, 70, 80);

1.5 使用EXISTS替代IN ​

当子查询结果集较大时，使用EXISTS替代IN，提高查询效率。

sql
-- 不好的写法：子查询结果集较大时效率低
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- 好的写法：使用EXISTS
SELECT * FROM employees e WHERE EXISTS (
    SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 1700
);

1.6 使用NOT EXISTS替代NOT IN ​

使用NOT EXISTS替代NOT IN，提高查询效率，同时避免NULL值问题。

sql
-- 不好的写法：NOT IN会排除NULL值
SELECT * FROM employees WHERE department_id NOT IN (60, 70, NULL);

-- 好的写法：使用NOT EXISTS
SELECT * FROM employees e WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_id IN (60, 70)
);

2. JOIN优化 ​

2.1 合理安排表的连接顺序 ​

将小表作为驱动表，大表作为连接表，提高连接效率。

sql
-- 好的写法：小表作为驱动表
SELECT e.first_name, e.last_name, d.department_name
FROM departments d -- 小表
JOIN employees e ON d.department_id = e.department_id -- 大表
WHERE d.department_id = 60;

2.2 使用合适的连接方法 ​

  • 嵌套循环连接：适合驱动表小，连接表有合适索引的情况
  • 哈希连接：适合两个表都较大的情况
  • 排序合并连接：适合两个表都需要排序的情况
sql
-- 强制使用哈希连接
SELECT /*+ USE_HASH(e d) */ e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

2.3 为连接列添加索引 ​

为连接列添加合适的索引，提高连接效率。

sql
-- 为连接列添加索引
CREATE INDEX emp_dept_id_idx ON employees(department_id);

-- 使用连接索引的查询
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

3. 排序优化 ​

3.1 避免不必要的排序 ​

避免在查询中使用不必要的ORDER BY或GROUP BY，减少排序操作。

sql
-- 不好的写法：不必要的排序
SELECT * FROM employees WHERE department_id = 60 ORDER BY employee_id;

-- 好的写法：如果employee_id是主键，索引已经有序，不需要额外排序
SELECT * FROM employees WHERE department_id = 60;

3.2 使用索引避免排序 ​

为ORDER BY或GROUP BY的列添加合适的索引，避免排序操作。

sql
-- 添加索引
CREATE INDEX emp_hire_date_idx ON employees(hire_date);

-- 使用索引避免排序
SELECT * FROM employees WHERE department_id = 60 ORDER BY hire_date;

3.3 优化GROUP BY操作 ​

为GROUP BY的列添加合适的索引，避免排序操作。

sql
-- 添加索引
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary);

-- 使用索引优化GROUP BY
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

4. 子查询优化 ​

4.1 使用物化视图缓存子查询结果 ​

对于频繁执行的复杂子查询，可以使用物化视图缓存结果，提高查询效率。

sql
-- 创建物化视图
CREATE MATERIALIZED VIEW emp_avg_salary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 使用物化视图的查询
SELECT * FROM emp_avg_salary_mv WHERE department_id = 60;

4.2 使用WITH子句简化子查询 ​

使用WITH子句简化复杂子查询，提高查询的可读性和可维护性。

sql
-- 使用WITH子句
WITH avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.first_name, e.last_name, e.salary, a.avg_salary
FROM employees e
JOIN avg_salary a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

5. 聚合函数优化 ​

5.1 为聚合函数的列添加索引 ​

为聚合函数的列添加合适的索引，提高聚合查询的效率。

sql
-- 添加索引
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary);

-- 使用索引优化聚合查询
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

5.2 使用ROWNUM限制结果集 ​

对于只需要前N行结果的查询，使用ROWNUM限制结果集，减少资源消耗。

sql
-- 使用ROWNUM限制结果集
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

6. 批量操作优化 ​

6.1 使用批量INSERT替代单行INSERT ​

使用批量INSERT替代单行INSERT，减少网络传输开销和事务开销。

sql
-- 批量INSERT
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary)
VALUES (207, 'John', 'Doe', 'johndoe@example.com', SYSDATE, 60, 6000),
       (208, 'Jane', 'Smith', 'janesmith@example.com', SYSDATE, 60, 5500),
       (209, 'Mike', 'Johnson', 'mikejohnson@example.com', SYSDATE, 70, 6500);

6.2 使用FORALL批量执行DML ​

在PL/SQL中使用FORALL批量执行DML操作，提高执行效率。

sql
-- 使用FORALL批量更新
DECLARE
  TYPE emp_id_type IS TABLE OF employees.employee_id%TYPE;
  emp_ids emp_id_type;
BEGIN
  -- 获取员工ID列表
  SELECT employee_id BULK COLLECT INTO emp_ids
  FROM employees
  WHERE department_id = 60;
  
  -- 批量更新
  FORALL i IN 1..emp_ids.COUNT
    UPDATE employees
    SET salary = salary * 1.1
    WHERE employee_id = emp_ids(i);
  
  COMMIT;
END;
/

SQL优化工具 ​

1. EXPLAIN PLAN ​

使用EXPLAIN PLAN查看SQL语句的执行计划，分析查询效率。

sql
-- 查看执行计划
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. SQL Monitor ​

使用SQL Monitor实时监控长运行的SQL语句，分析执行情况。

sql
-- 启用SQL Monitor
ALTER SESSION SET statistics_level = ALL;

-- 执行SQL语句
SELECT /*+ MONITOR */ * FROM employees WHERE department_id = 60;

-- 查看SQL Monitor报告
SELECT dbms_sqltune.report_sql_monitor FROM dual;

3. AWR报告 ​

使用AWR报告分析SQL语句的历史执行情况，识别性能瓶颈。

sql
-- 生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

4. ADDM报告 ​

使用ADDM报告自动分析数据库性能问题，提供优化建议。

sql
-- 生成ADDM报告
@$ORACLE_HOME/rdbms/admin/addmrpt.sql

版本差异 ​

Oracle 12c ​

  • 引入了自适应执行计划
  • 支持实时SQL监控
  • 增强了SQL计划管理

Oracle 18c ​

  • 增强了自适应执行计划的功能
  • 引入了SQL计划基线
  • 支持自动执行计划捕获

Oracle 19c ​

  • 长期支持版本
  • 增强了SQL计划管理的功能
  • 支持自动执行计划演进

Oracle 21c ​

  • 支持SQL计划的AI优化
  • 增强了自适应执行计划的功能
  • 支持SQL计划的云原生监控

常见问题（FAQ） ​

Q: 如何确定SQL语句的性能瓶颈？ ​

A: 可以通过以下方法确定性能瓶颈：

  • 查看执行计划，分析耗时操作
  • 使用SQL Monitor实时监控
  • 分析AWR报告，查看资源消耗情况
  • 使用ADDM报告自动分析

Q: 为什么索引没有被使用？ ​

A: 可能的原因包括：

  • 查询条件中使用了函数或表达式
  • 查询条件使用了不等于或IS NULL
  • 表较小，全表扫描比索引扫描更高效
  • 索引的选择性较低
  • 表的统计信息不准确

Q: 如何优化慢查询？ ​

A: 可以通过以下方法优化慢查询：

  • 查看执行计划，分析耗时操作
  • 添加合适的索引
  • 重写SQL语句，优化查询结构
  • 优化JOIN操作，添加合适的索引
  • 使用合适的连接方法
  • 更新表和索引的统计信息

Q: 如何优化批量操作？ ​

A: 可以通过以下方法优化批量操作：

  • 使用批量INSERT替代单行INSERT
  • 使用FORALL批量执行DML操作
  • 禁用索引，批量操作后重建索引
  • 调整COMMIT频率
  • 使用并行操作

Q: 如何优化聚合查询？ ​

A: 可以通过以下方法优化聚合查询：

  • 为聚合列添加合适的索引
  • 使用物化视图缓存聚合结果
  • 避免在聚合查询中使用复杂的过滤条件
  • 使用并行查询

Q: 如何优化JOIN查询？ ​

A: 可以通过以下方法优化JOIN查询：

  • 合理安排表的连接顺序，小表作为驱动表
  • 为连接列添加合适的索引
  • 使用合适的连接方法
  • 避免在JOIN条件中使用函数或表达式
  • 限制返回的列数量

SQL优化最佳实践 ​

1. 设计阶段优化 ​

  • 合理设计表结构，避免冗余字段
  • 为常用查询添加合适的索引
  • 选择合适的数据类型
  • 设计合理的主键和外键

2. 开发阶段优化 ​

  • 编写高效的SQL语句，遵循优化原则
  • 使用绑定变量，避免硬解析
  • 避免使用复杂的查询，拆分为简单查询
  • 测试SQL语句的性能，优化慢查询

3. 运维阶段优化 ​

  • 定期更新表和索引的统计信息
  • 定期重建索引，优化索引碎片
  • 监控SQL语句的执行情况，识别性能瓶颈
  • 定期分析AWR和ADDM报告，优化数据库性能
  • 根据业务需求调整数据库参数

总结 ​

SQL优化是Oracle数据库性能优化的核心，良好的SQL设计可以显著提高数据库的执行效率。本文介绍了Oracle SQL优化的原则、具体技巧、优化工具、版本差异和常见问题，涵盖了WHERE子句优化、JOIN优化、排序优化、子查询优化、聚合函数优化和批量操作优化等方面。

在实际应用中，应结合实际情况选择合适的优化方法，如添加索引、重写SQL语句、优化JOIN操作、使用批量操作等。同时，应定期监控SQL语句的执行情况，分析执行计划，识别性能瓶颈，持续优化SQL语句。

随着Oracle版本的更新，SQL优化的功能不断增强，如自适应执行计划、SQL计划管理、AI优化等，开发人员和DBA应关注新版本的特性，提高SQL优化的效率和效果。

SQL优化是一个持续的过程，需要不断学习、实践和总结，才能编写出高效的SQL语句，提高数据库的整体性能。