SQL语句优化实战：34个核心技巧与性能提升指南

表处理与执行顺序优化

理解数据库解析器的工作原理是优化的前提。以Oracle为代表的传统关系型数据库通常遵循特定的解析顺序，掌握这些规律可避免盲目优化。

第1步：选择有效的表名顺序

目的：利用基于规则优化器（RBO）的特性，优先处理基数最小的数据源以减少中间结果集。

• 操作：在FROM子句中，将记录数最少或交叉引用最多的表放在最后（驱动表）。
• 注意：此规则主要适用于基于规则的优化器；在现代CBO（基于成本优化器）中，请优先依赖执行计划。

第2步：精准排列WHERE条件

目的：自下而上解析原理要求过滤性最强的条件后置，以便早期剔除无效数据行。

• 操作：将能过滤最大数量记录的条件写在WHERE子句末尾。
• 逻辑：先进行多表关联，再在最后阶段应用高选择性过滤，可减少关联运算的数据量。

查询结构与语法规范

第3步：明确SELECT字段列表

操作：避免使用 SELECT *，显式指定所需列名。

原因：*会导致数据库引擎查询数据字典以映射所有列，增加解析开销并传输无用带宽。

第4步：用WHERE替换HAVING

目的：在执行聚合计算前直接过滤记录，避免无效数据的排序与统计开销。

操作示例：

-- 低效：先分组再过滤
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT';

-- 高效：先过滤再分组
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' GROUP BY JOB;

第5步：区分ON、WHERE与HAVING的执行时机

• ON：最先执行，用于过滤JOIN前的原始表数据，能有效减少参与连接的行数。
• WHERE：次之，对连接后的临时结果集进行过滤。
• HAVING：最后执行，仅用于过滤聚合后的结果，无法利用索引加速。

集合操作与子查询优化

第6步：EXISTS替代IN/NOT IN

场景：主表大、子表小且存在一对一或一对多关联时。

操作：NOT IN会引发内部排序合并，效率最低；建议使用 EXISTS 或外连接。

-- 高效写法
SELECT * FROM EMP E WHERE EXISTS (
    SELECT 1 FROM DEPT D WHERE D.DEPTNO = E.DEPTNO AND LOC = 'MELB'
);

第7步：UNION ALL 替换 UNION

操作：若业务允许结果集包含重复记录，且无需去重，请使用 UNION ALL。

优势：跳过排序（SORT）步骤，显著降低内存消耗（SORT_AREA_SIZE）和处理时间。

第8步：UNION 替换 OR（针对索引列）

场景：当OR连接的各列均建有独立索引时。

操作：拆分为多个查询并使用 UNION，促使数据库走索引扫描而非全表扫描。

-- 高效：利用索引
SELECT LOC_ID, REGION FROM LOCATION WHERE LOC_ID = 10 
UNION 
SELECT LOC_ID, REGION FROM LOCATION WHERE REGION = 'MELBOURNE';

第9步：EXISTS 替代 DISTINCT

场景：查询一对多关联表中“唯一”的记录（如部门及其最后一个员工）。

操作：一旦子查询找到匹配项，EXISTS立即返回真值，无需继续扫描剩余行。

索引使用禁忌与维护

第10步：避免索引列上的类型转换

风险：隐式类型转换（如字符型索引列比较数值）会导致Oracle调用 TO_NUMBER() 等函数，致使索引失效。

修正：确保查询参数与列定义类型一致，或使用显式转换并确认其发生在非索引路径上。

第11步：拒绝在索引列上使用函数或表达式

错误示范：WHERE SAL * 12 > 25000 或 WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2023'。

正确写法：将函数移至等号另一侧：WHERE SAL > 25000 / 12。

第12步：慎用 NULL 值判断

现象：IS NULL 和 IS NOT NULL 通常无法使用索引，导致全表扫描。

替代方案：若非空校验不可少，可使用默认值替代（如用 >= 0 替代 IS NOT NULL），或在建表时强制非空约束。

第13步：复合索引遵循“最左前缀”原则

规则：优化器仅在查询条件引用了复合索引的第一个列时才使用该索引。仅查询第二列将触发全表扫描。

DML操作与资源管理

第14步：TRUNCATE 替代 DELETE

场景：需要清空全表数据且不需要回滚时。

优势：TRUNCATE 是DDL操作，不产生大量undo日志，执行速度极快且不占用回滚段空间。

第15步：批量删除重复记录

方法：利用 ROWID 标记物理存储位置，保留最小RowID，删除其余重复项。

DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.ID = E.ID);

第16步：合理提交COMMIT

原理：频繁的事务提交可释放回滚段、锁资源和Redo Log Buffer空间，防止长事务导致的空间堆积和阻塞。

第17步：调整ARRAYSIZE获取批量数据

操作：在SQL*Plus等工具中设置 SET ARRAYSIZE 200（或更高）。

效果：减少网络往返次数，每次Fetch获取多条记录，提升I/O吞吐量。

高级查询与特定场景处理

第18步：识别低效SQL的监控手段

工具：查询 V$SQLAREA 视图，关注 HIT_RADIO（缓冲区命中率）和 Reads_per_run。

指标：命中率低（如 < 0.8）且单次读取高的SQL应作为重点优化对象。

第19步：ORDER BY 与索引的兼容限制

条件：ORDER BY使用的列必须位于同一索引中，且保持相同排序方向，同时不能为非空列缺失的情况。

建议：尽量让WHERE过滤列与ORDER BY列共用同一个复合索引。

第20步：避免消耗资源的复杂集合操作

注意：DISTINCT、UNION、MINUS、INTERSECT 均需启动排序进程。若非必须，尝试通过业务逻辑重构消除这些操作。

第21步：整合简单查询

操作：将多个独立的简单查询合并为单次连接查询，减少数据库解析和执行上下文切换的开销。

第22步：使用DECODE简化逻辑

场景：需要基于单列不同值返回不同结果或进行条件计数时。

优势：相比多个子查询或CASE WHEN，DECODE 可在一次遍历中完成计算，减少重复扫描。

常见误区与补充说明

第23步：关于大小写的规范

建议：虽然现代优化器不区分大小写，但统一使用大写关键字有助于提高SQL的可读性及团队一致性。

第24步：Java中的字符串拼接

提醒：在应用层（如Java/JDBC）中，频繁使用 “+” 拼接SQL会导致动态SQL生成缓慢及潜在的SQL注入风险。应始终使用预编译语句（PreparedStatement）及 StringBuilder。

第25步：大于等于(>=)优于大于(>)

原理：>=4 可直接定位到索引树的第一个匹配节点，而 >3 需先找到3的位置再向后扫描，虽差异细微，但在海量数据下累积效应明显。

第26步：IN列表的高效使用

对比：LOC_ID IN (10, 20, 30) 通常比连续多个 OR 条件更清晰且在部分引擎中执行路径更优。

第27步：警惕特殊符号引发的全表扫描

禁用：在索引列上使用 !=、|| (连接符) 或 + (算术符) 以及同列互相比较（如 A > B，其中A、B同属一索引列），均会迫使优化器放弃索引。

第28步：索引适用的阈值判断

经验值：若检索数据量超过表的总记录数30%左右，使用索引带来的随机I/O开销可能高于顺序全表扫描。此时应考虑全表扫描（Hint: FULL）。

第29步：定期重构索引

维护：随着INSERT/UPDATE/DELETE的持续进行，B-Tree索引会产生碎片。定期执行 ALTER INDEX REBUILD 可恢复紧凑结构，提升后续查询效率。

第30步：别名与列前缀

规范：在多表连接中务必使用别名（Alias），并用别名前缀每一列（如 E.ID 而非 ID）。这不仅能加快解析速度，还能避免歧义报错。

第31步：减少冗余的数据库交互

策略：通过批量操作（Batching）或多语句打包执行，减少应用与数据库之间的握手次数。

第32步：子查询的极致精简

原则：在含有子查询的语句中，尽量减少子查询涉及的表数量，并确保子查询尽可能使用索引。

第33步：内部函数的双刃剑

警示：复杂的内置函数虽能简化SQL逻辑，但往往阻碍优化器使用索引。在追求极致性能的场景下，优先选择纯列操作或前置计算。

第34步：事务隔离与性能平衡

提醒：虽然提倡多用COMMIT，但过于频繁的提交在高并发场景下可能引发闩锁（Latch）争用。需根据业务TPS需求寻找最佳提交频率。

以上就是SQL语句优化34个核心技巧的详细内容，更多关于数据库性能调优的资料请关注本站其它相关文章！