企业官网建设流程全解析

Oracle优化SQL语句 — 语法知识点与使用方法详解

一、环境准备（Oracle数据库安装简要说明）

注意：Oracle数据库安装较为复杂，以下为简化版安装流程（以 Oracle Database 21c Express Edition 为例）。

1. 下载 Oracle Database 21c XE

• 官网地址：https://www.oracle.com/database/technologies/xe-downloads.html
• 支持平台：Windows / Linux

2. 安装步骤（以 Windows 为例）

1. 以管理员身份运行安装程序OracleXE21c_Win64.exe
2. 设置SYS / SYSTEM 用户密码（记住该密码）
3. 选择安装路径（默认即可）
4. 安装完成后，服务自动启动：
   • OracleServiceXE
   • OracleXETNSListener

3. 验证安装

# 打开命令行sqlplus sys/your_password@//localhost:1521/XE as sysdba

若成功进入 SQL> 提示符，说明安装成功。

4. 创建测试用户（可选）

-- 以 sys 用户登录后执行CREATEUSERtest_user IDENTIFIEDBYtest123;GRANTCONNECT,RESOURCE,DBATOtest_user;

二、常规SQL语句优化

1. 不用“*”代替所有列名

✅ 原理：

• SELECT *会读取所有列，增加 I/O 和网络传输开销。
• 明确指定列可利用覆盖索引，提升性能。

📌 案例代码：

-- ❌ 不推荐SELECT*FROMemployees;-- ✅ 推荐：只查询需要的字段SELECTemployee_id,first_name,last_name,salaryFROMemployees;

2. 用 TRUNCATE 代替 DELETE（清空整表时）

✅ 原理：

• DELETE是 DML，逐行删除并写日志，可回滚，但慢。
• TRUNCATE是 DDL，直接释放数据段，不可回滚，速度快，不触发触发器。

⚠️ 注意：

• TRUNCATE不能带WHERE条件。
• 需要DROP ANY TABLE权限（通常 DBA 或表所有者）。

📌 案例代码：

-- 删除所有员工记录（可回滚，慢）DELETEFROMemployees_temp;-- 清空整个表（不可回滚，快）TRUNCATETABLEemployees_temp;

3. 在确保完整性的情况下多用 COMMIT

✅ 原理：

• 频繁COMMIT可释放 UNDO 段，减少锁争用，避免 ORA-1555 错误。
• 但不要过度提交（影响事务一致性）。

📌 案例代码（批量插入 + 分批提交）：

DECLAREi NUMBER :=0;BEGINFORrecIN(SELECTlevelASid,'User_'||levelASnameFROMdualCONNECTBYlevel<=10000)LOOPINSERTINTOusers(user_id,user_name)VALUES(rec.id,rec.name);i :=i+1;-- 每 1000 行提交一次IFMOD(i,1000)=0THENCOMMIT;DBMS_OUTPUT.PUT_LINE('Committed '||i||' records');ENDIF;ENDLOOP;COMMIT;-- 提交剩余记录END;/

4. 尽量减少表的查询次数

✅ 原理：

• 多次访问同一表会增加逻辑读和物理读。
• 使用子查询、连接或分析函数合并操作。

📌 案例代码：

-- ❌ 两次查询 employees 表SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=10;SELECTAVG(salary)FROMemployeesWHEREdepartment_id=10;-- ✅ 一次查询完成SELECTCOUNT(*),AVG(salary)FROMemployeesWHEREdepartment_id=10;

5. 用 [NOT] EXISTS 代替 [NOT] IN

✅ 原理：

• NOT IN在子查询含NULL时返回空结果（逻辑陷阱）。
• EXISTS使用半连接（semi-join），效率更高。

📌 案例代码：

-- ❌ NOT IN：若子查询有 NULL，结果为空！SELECTemployee_idFROMemployeesWHEREdepartment_idNOTIN(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1700);-- ✅ 使用 NOT EXISTS（安全且高效）SELECTe.employee_idFROMemployees eWHERENOTEXISTS(SELECT1FROMdepartments dWHEREd.department_id=e.department_idANDd.location_id=1700);

三、表连接优化

1. 驱动表的选择

✅ 原理：

• 驱动表（Driving Table）是嵌套循环连接中先被扫描的表。
• 应选择结果集小的表作为驱动表。

📌 案例（使用 /*+ LEADING */ 提示）：

-- 假设 departments 表小，employees 表大SELECT/*+ LEADING(d) */e.first_name,d.department_nameFROMemployees eJOINdepartments dONe.department_id=d.department_idWHEREd.location_id=1700;

2. WHERE 子句的连接顺序

✅ 原理：

• 虽然 CBO（Cost-Based Optimizer）会重排，但清晰的写法有助于阅读和调试。
• 先写连接条件，再写过滤条件。

📌 案例：

-- 推荐写法SELECTe.first_name,j.job_titleFROMemployees eJOINjobs jONe.job_id=j.job_idWHEREe.salary>5000ANDj.job_titleLIKE'%Manager%';

四、合理使用索引

1. 何时使用索引？

• 列出现在WHERE、JOIN、ORDER BY、GROUP BY中。
• 高选择性列（如主键、唯一ID）。
• 避免在低选择性列（如性别）上建单列索引。

2. 索引列和表达式的选择

❌ 错误：对列使用函数 → 索引失效

-- 索引无法使用SELECT*FROMemployeesWHEREUPPER(last_name)='KING';

✅ 正确：使用函数索引 或 改写条件

-- 方式1：创建函数索引CREATEINDEXidx_emp_upper_lastnameONemployees(UPPER(last_name));-- 方式2：避免函数（如果业务允许）SELECT*FROMemployeesWHERElast_name='King';-- 假设数据规范

3. 选择复合索引主列

✅ 原理：

• 复合索引(col1, col2, col3)只在WHERE包含col1时有效（最左前缀原则）。

📌 案例：

-- 创建复合索引CREATEINDEXidx_emp_dept_salONemployees(department_id,salary);-- ✅ 能用索引SELECT*FROMemployeesWHEREdepartment_id=10ANDsalary>5000;-- ✅ 能用索引（只用到第一列）SELECT*FROMemployeesWHEREdepartment_id=10;-- ❌ 无法使用索引SELECT*FROMemployeesWHEREsalary>5000;

4. 避免全表扫描大表

• 对大表（>百万行）的查询必须有索引支持。
• 使用EXPLAIN PLAN验证是否走索引。

5. 监视索引是否被使用

-- 开启索引监控（11g 后已弃用，改用 AWR 或 V$SEGMENT_STATISTICS）-- 替代方案：查询执行计划或使用 SQL Monitor-- 查看索引统计信息SELECTindex_name,table_name,num_rows,last_analyzedFROMuser_indexesWHEREtable_name='EMPLOYEES';

五、优化器的使用

1. 运行 EXPLAIN PLAN

📌 案例：

-- 生成执行计划EXPLAINPLANFORSELECTe.first_name,d.department_nameFROMemployees eJOINdepartments dONe.department_id=d.department_idWHEREe.salary>10000;-- 查看执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);

输出解读关键字段：

• OPERATION: 访问方式（TABLE ACCESS FULL / INDEX RANGE SCAN）
• COST: 估算代价
• ROWS: 估算返回行数

2. Oracle 中 SQL 执行计划管理（SPM）

适用于防止执行计划突变（如统计信息更新后变差）

-- 加载 SQL 到 SPM BaselineDECLAREl_plans_loaded PLS_INTEGER;BEGINl_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'abc123xyz'-- 从 V$SQL 获取);END;/

六、数据库和 SQL 重演

1. 数据库重演（Database Replay）

捕获生产负载，在测试环境重放（需 Enterprise Edition）

2. SQL 重演（SQL Performance Analyzer, SPA）

比较变更前后 SQL 性能（如升级、参数调整）

-- 创建 SPA 任务（示例）DECLAREl_task_name VARCHAR2(30);BEGINl_task_name :=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'my_sqlset',task_name=>'spa_task_01');DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>l_task_name,execution_type=>'TEST EXECUTE');END;/

七、Oracle 性能顾问

1. SQL 调优顾问（SQL Tuning Advisor）

-- 创建调优任务DECLAREl_task_name VARCHAR2(30);BEGINl_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text=>'SELECT * FROM employees WHERE salary > 10000',task_name=>'tune_emp_salary');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'tune_emp_salary');END;/-- 查看建议SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('tune_emp_salary')FROMdual;

2. SQL 访问顾问（SQL Access Advisor）

建议创建索引、物化视图等

DECLAREl_task_name VARCHAR2(30);l_wkld_name VARCHAR2(30);BEGIN-- 创建工作负载（可来自 SQL Cache）l_wkld_name :=DBMS_ADVISOR.CREATE_FILE(directory=>'DATA_PUMP_DIR',filename=>'workload.txt');l_task_name :=DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor','access_task_01');DBMS_ADVISOR.ADD_STS_REF(l_task_name,'MY_WORKLOAD_STS');DBMS_ADVISOR.EXECUTE_TASK(l_task_name);END;/

八、综合性案例

场景：优化一个慢查询报表

原始 SQL（性能差）：

SELECT*FROMemployees e,departments d,locations lWHEREe.department_id=d.department_id(+)ANDd.location_id=l.location_id(+)ANDe.salary>(SELECTAVG(salary)FROMemployees)ANDUPPER(e.last_name)LIKE'%A%';

优化步骤：

步骤1：避免SELECT *和外连接写法

-- 改为 ANSI JOIN，明确字段SELECTe.employee_id,e.first_name,e.last_name,e.salary,d.department_name,l.cityFROMemployees eLEFTJOINdepartments dONe.department_id=d.department_idLEFTJOINlocations lONd.location_id=l.location_idWHEREe.salary>(SELECTAVG(salary)FROMemployees)ANDUPPER(e.last_name)LIKE'%A%';

步骤2：创建函数索引

CREATEINDEXidx_emp_upper_lastnameONemployees(UPPER(last_name));

步骤3：物化子查询（避免重复计算）

WITHavg_salAS(SELECTAVG(salary)ASavg_salaryFROMemployees)SELECTe.employee_id,e.first_name,e.last_name,e.salary,d.department_name,l.cityFROMemployees eLEFTJOINdepartments dONe.department_id=d.department_idLEFTJOINlocations lONd.location_id=l.location_idCROSSJOINavg_salWHEREe.salary>avg_sal.avg_salaryANDUPPER(e.last_name)LIKE'%A%';

步骤4：验证执行计划

EXPLAINPLANFOR-- 上述 WITH 查询SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);

预期结果：使用INDEX RANGE SCANonidx_emp_upper_lastname，子查询只执行一次。

总结

通过以上方法，可显著提升 Oracle SQL 性能，降低系统资源消耗。