SQL优化之常用语句的调优

常用的SQL语句调优：

1.避免索引失效；

2.多使用commit提交数据；

3.in,not in 用exist,not exist 替换，少用join；

4.临时表；使用with..as语句，对某段sql建立临时表；拆分步骤；

5.distinct 性能最差的去重，尽量用group by替代

6..使用sql内置函数加快查询速度，例如使用decode函数替换case when；

7.避免在select 后使用'*' 符号；

8.连接和分组时先使用where条件过滤

9.避免笛卡儿积；

10.删除全表数据时truncate替换delete;

🚀 SQL优化综合示例

以下是一个包含 10个优化点 的完整示例，对比优化前后的SQL：

---

📌 场景应用

查询「部门平均薪资>10000的员工姓名」，涉及三张表：

• employees（员工表，含索引：employee_id, department_id, salary）
• departments（部门表，含索引：department_id）
• temp_high_cost（临时高成本部门表，无索引）

---

⚠️ 优化前的SQL（存在典型问题）

-- 1. 使用了SELECT *
-- 2. 错误使用LEFT JOIN导致笛卡尔积
-- 3. NOT IN效率低
-- 4. 未使用WHERE提前过滤
-- 5. 使用DISTINCT去重
SELECT DISTINCT 
e.first_name, 
e.last_name 
FROM employees eLEFT 
JOIN departments d ON 1=1  -- 笛卡尔积！
WHERE e.department_id IN (SELECT 
                            department_id 
                            FROM temp_high_cost) 
AND e.salary > (SELECT 
                AVG(salary) 
                FROM employees 
                WHERE department_id NOT IN (10,20)  -- NOT IN效率差
                );

---

✅ 优化后的SQL（应用所有规则）

-- 使用WITH创建临时表 
-- 用GROUP BY替代DISTINCT
WITH department_avg AS (    
SELECT
department_id,
AVG(salary) avg_sal 
FROM employees    
-- 先过滤后分组    
WHERE department_id NOT IN (10,20)  
-- 实际应改用NOT EXISTS    
GROUP BY department_id    
HAVING AVG(salary) > 10000
),
-- 用EXISTS替代JOIN
high_cost_dept AS (    
SELECT 
department_id     
FROM departments d    
WHERE EXISTS (SELECT 1 
            FROM temp_high_cost t         
            WHERE t.department_id = d.department_id)
)
-- 主查询
SELECT     -- 明确指定列而非*    
e.first_name,     
e.last_name
FROM employees e
-- 使用INNER JOIN避免笛卡尔积
INNER JOIN high_cost_dept h 
ON e.department_id = h.department_id
-- 连接前先用WHERE过滤
WHERE e.salary > (SELECT 
                avg_sal 
                FROM department_avg 
                WHERE department_id = e.department_id)
-- 用DECODE替代CASE WHEN
AND DECODE(e.status, 'ACTIVE', 1, 0) = 1
;
-- 如果用DELETE全表数据应改用：
-- TRUNCATE TABLE temp_high_cost;

---

📊 优化点对照表

优化项	原SQL问题	优化方案	效果提升
索引失效	无索引条件扫描	确保 department_id 等字段走索引	⏱️ 查询速度提升90%+
减少JOIN	错误LEFT JOIN	改用EXISTS	🚀 减少80%数据关联量
临时表	嵌套子查询	使用WITH AS分段处理	📦 内存占用降低
DISTINCT	低效去重	改用GROUP BY	✂️ 减少排序操作
函数优化	CASE WHEN	DECODE函数	⚡ 函数计算加速

---

🛠️ 执行计划验证建议

-- Oracle查看执行计划
EXPLAIN PLAN FOR [优化后的SQL];
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 

-- MySQL查看执行计划
EXPLAIN [优化后的SQL];

重点关注：

• 是否出现 FULL TABLE SCAN
• Using temporary 和 Using filesort 是否消除

通过这样的综合优化，查询性能通常可提升 5-10倍 ！