企业官网建设流程全解析

做统计表的网站,永康网站优化,网站服务器维护技术,做公司集团网站Oracle优化SQL语句 — 语法知识点与使用方法详解一、环境准备#xff08;Oracle数据库安装简要说明#xff09;注意#xff1a;Oracle数据库安装较为复杂#xff0c;以下为简化版安装流程#xff08;以 Oracle Database 21c Express Edition 为例#xff09;。1. 下载 Or…Oracle优化SQL语句 — 语法知识点与使用方法详解一、环境准备Oracle数据库安装简要说明注意Oracle数据库安装较为复杂以下为简化版安装流程以 Oracle Database 21c Express Edition 为例。1. 下载 Oracle Database 21c XE官网地址https://www.oracle.com/database/technologies/xe-downloads.html支持平台Windows / Linux2. 安装步骤以 Windows 为例以管理员身份运行安装程序OracleXE21c_Win64.exe设置SYS / SYSTEM 用户密码记住该密码选择安装路径默认即可安装完成后服务自动启动OracleServiceXEOracleXETNSListener3. 验证安装# 打开命令行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_||levelASnameFROMdualCONNECTBYlevel10000)LOOPINSERTINTOusers(user_id,user_name)VALUES(rec.id,rec.name);i :i1;-- 每 1000 行提交一次IFMOD(i,1000)0THENCOMMIT;DBMS_OUTPUT.PUT_LINE(Committed ||i|| records);ENDIF;ENDLOOP;COMMIT;-- 提交剩余记录END;/4. 尽量减少表的查询次数✅ 原理多次访问同一表会增加逻辑读和物理读。使用子查询、连接或分析函数合并操作。 案例代码-- ❌ 两次查询 employees 表SELECTCOUNT(*)FROMemployeesWHEREdepartment_id10;SELECTAVG(salary)FROMemployeesWHEREdepartment_id10;-- ✅ 一次查询完成SELECTCOUNT(*),AVG(salary)FROMemployeesWHEREdepartment_id10;5. 用 [NOT] EXISTS 代替 [NOT] IN✅ 原理NOT IN在子查询含NULL时返回空结果逻辑陷阱。EXISTS使用半连接semi-join效率更高。 案例代码-- ❌ NOT IN若子查询有 NULL结果为空SELECTemployee_idFROMemployeesWHEREdepartment_idNOTIN(SELECTdepartment_idFROMdepartmentsWHERElocation_id1700);-- ✅ 使用 NOT EXISTS安全且高效SELECTe.employee_idFROMemployees eWHERENOTEXISTS(SELECT1FROMdepartments dWHEREd.department_ide.department_idANDd.location_id1700);三、表连接优化1. 驱动表的选择✅ 原理驱动表Driving Table是嵌套循环连接中先被扫描的表。应选择结果集小的表作为驱动表。 案例使用 /* LEADING */ 提示-- 假设 departments 表小employees 表大SELECT/* LEADING(d) */e.first_name,d.department_nameFROMemployees eJOINdepartments dONe.department_idd.department_idWHEREd.location_id1700;2. WHERE 子句的连接顺序✅ 原理虽然 CBOCost-Based Optimizer会重排但清晰的写法有助于阅读和调试。先写连接条件再写过滤条件。 案例-- 推荐写法SELECTe.first_name,j.job_titleFROMemployees eJOINjobs jONe.job_idj.job_idWHEREe.salary5000ANDj.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_nameKing;-- 假设数据规范3. 选择复合索引主列✅ 原理复合索引(col1, col2, col3)只在WHERE包含col1时有效最左前缀原则。 案例-- 创建复合索引CREATEINDEXidx_emp_dept_salONemployees(department_id,salary);-- ✅ 能用索引SELECT*FROMemployeesWHEREdepartment_id10ANDsalary5000;-- ✅ 能用索引只用到第一列SELECT*FROMemployeesWHEREdepartment_id10;-- ❌ 无法使用索引SELECT*FROMemployeesWHEREsalary5000;4. 避免全表扫描大表对大表百万行的查询必须有索引支持。使用EXPLAIN PLAN验证是否走索引。5. 监视索引是否被使用-- 开启索引监控11g 后已弃用改用 AWR 或 V$SEGMENT_STATISTICS-- 替代方案查询执行计划或使用 SQL Monitor-- 查看索引统计信息SELECTindex_name,table_name,num_rows,last_analyzedFROMuser_indexesWHEREtable_nameEMPLOYEES;五、优化器的使用1. 运行 EXPLAIN PLAN 案例-- 生成执行计划EXPLAINPLANFORSELECTe.first_name,d.department_nameFROMemployees eJOINdepartments dONe.department_idd.department_idWHEREe.salary10000;-- 查看执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);输出解读关键字段OPERATION: 访问方式TABLE ACCESS FULL / INDEX RANGE SCANCOST: 估算代价ROWS: 估算返回行数2. Oracle 中 SQL 执行计划管理SPM适用于防止执行计划突变如统计信息更新后变差-- 加载 SQL 到 SPM BaselineDECLAREl_plans_loaded PLS_INTEGER;BEGINl_plans_loaded :DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_idabc123xyz-- 从 V$SQL 获取);END;/六、数据库和 SQL 重演1. 数据库重演Database Replay捕获生产负载在测试环境重放需 Enterprise Edition2. SQL 重演SQL Performance Analyzer, SPA比较变更前后 SQL 性能如升级、参数调整-- 创建 SPA 任务示例DECLAREl_task_name VARCHAR2(30);BEGINl_task_name :DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_namemy_sqlset,task_namespa_task_01);DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_namel_task_name,execution_typeTEST EXECUTE);END;/七、Oracle 性能顾问1. SQL 调优顾问SQL Tuning Advisor-- 创建调优任务DECLAREl_task_name VARCHAR2(30);BEGINl_task_name :DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_textSELECT * FROM employees WHERE salary 10000,task_nametune_emp_salary);DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_nametune_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 Cachel_wkld_name :DBMS_ADVISOR.CREATE_FILE(directoryDATA_PUMP_DIR,filenameworkload.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_idd.department_id()ANDd.location_idl.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_idd.department_idLEFTJOINlocations lONd.location_idl.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_idd.department_idLEFTJOINlocations lONd.location_idl.location_idCROSSJOINavg_salWHEREe.salaryavg_sal.avg_salaryANDUPPER(e.last_name)LIKE%A%;步骤4验证执行计划EXPLAINPLANFOR-- 上述 WITH 查询SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);预期结果使用INDEX RANGE SCANonidx_emp_upper_lastname子查询只执行一次。总结优化点关键操作列选择避免*只选必要字段删除数据大量清空用TRUNCATE事务控制批量操作分批COMMIT子查询用EXISTS替代IN索引高选择性列、复合索引最左前缀、避免函数执行计划用EXPLAIN PLAN验证自动优化使用 SQL Tuning Advisor通过以上方法可显著提升 Oracle SQL 性能降低系统资源消耗。