
SQL 语句性能调整参考点 第 1 页 共 9 页
SQL 语句性能调整参考点
选择最有效率的表名顺序 ------------------------------------------------------------------------------------------------------------------------------------- 2
WHERE 子句中的解析顺序 -------------------------------------------------------------------------------------------------------------------------------------- 2
SELECT 子句中避免使用“*” -------------------------------------------------------------------------------------------------------------------------------- 2
使用 DECODE 函数来减少处理时间 --------------------------------------------------------------------------------------------------------------------------- 3
用 Where 子句替换 HAVING 子句 ------------------------------------------------------------------------------------------------------------------------------ 3
减少对表的查询 ------------------------------------------------------------------------------------------------------------------------------------------------- 3
通过自定义函数提高 SQL 效率-------------------------------------------------------------------------------------------------------------------------------- 4
使用表的别名(Alias) ------------------------------------------------------------------------------------------------------------------------------------------ 5
用 EXISTS 替代 IN ----------------------------------------------------------------------------------------------------------------------------------------------- 5
用 NOT EXISTS 替代 NOT IN ----------------------------------------------------------------------------------------------------------------------------------- 5
用表连接替换 EXISTS (视具体情况而定) ------------------------------------------------------------------------------------------------------------------ 5
用 EXISTS 替换 DISTINCT -------------------------------------------------------------------------------------------------------------------------------------- 6
用索引提高效率 ------------------------------------------------------------------------------------------------------------------------------------------------- 6
索引的操作 ------------------------------------------------------------------------------------------------------------------------------------------------------- 6
等式比较和范围比较 ------------------------------------------------------------------------------------------------------------------------------------------- 7
用 UNION ALL 替换 UNION (如果有可以的话) ------------------------------------------------------------------------------------------------------------- 7
需要当心的 WHERE 子句 ---------------------------------------------------------------------------------------------------------------------------------------- 8
避免在索引列上使用 NOT -------------------------------------------------------------------------------------------------------------------------------------- 8
避免在索引列上使用计算 ------------------------------------------------------------------------------------------------------------------------------------- 8
避免在索引列上使用 IS NULL 和 IS NOT NULL ------------------------------------------------------------------------------------------------------------ 8
避免改变索引列的类型 ---------------------------------------------------------------------------------------------------------------------------------------- 8
参考位置 --------------------------------------------------------------------------------------------------------------------------------------------------------- 9