第二十二篇-SQL优化之等价改写从青铜到王者

2025-03-13 约 1234 字预计阅读 3 分钟

https://bing.ee123.net/img/rand?artid=146221177

第二十二篇 SQL优化之等价改写：从青铜到王者

一、新手村：减少数据扫描的6大绝招 🛠️

1.1 CASE WHEN合并术 —— 快递合并配送

场景：老板让你分别统计北京和上海的订单量

-- 青铜写法（跑两趟）
SELECT (SELECT COUNT(*) FROM orders WHERE city='北京') AS beijing,
       (SELECT COUNT(*) FROM orders WHERE city='上海') AS shanghai;

-- 王者写法（一趟搞定）
SELECT 
    SUM(CASE WHEN city='北京' THEN 1 ELSE 0 END) AS beijing,
    SUM(CASE WHEN city='上海' THEN 1 ELSE 0 END) AS shanghai
FROM orders;

原理：就像快递小哥合并配送路线，省时省力！

1.2 分页优化秘籍 —— 图书馆找书技巧

问题：查第100页数据时，别傻傻数前999条！

-- 错误示范（全楼找书）
SELECT * 
FROM (SELECT t.*, ROWNUM rn FROM books ORDER BY title) 
WHERE rn BETWEEN 9901 AND 10000;

-- 正确姿势（直达书架）
SELECT * 
FROM (
    SELECT t.*, ROWNUM rn 
    FROM books t 
    WHERE ROWNUM <= 10000  -- 先锁定范围
    ORDER BY title
) 
WHERE rn > 9900;          -- 再跳过前9900

1.3 直接路径插入 —— 老板的VIP通道

适用场景 ：批量导入百万数据（如双11订单归档）

INSERT /*+ APPEND */ INTO sales_archive   -- 走VIP通道
SELECT * FROM sales WHERE sale_date < '2023-01-01';

⚠️ 注意：VIP通道会暂时封路（锁表），高峰期慎用！

1.4 精准取件 —— 别搬整个快递柜

反面教材 ：

SELECT * FROM products;  -- 包含图片等大字段

正确做法 ：

SELECT product_id, name, price FROM products;

✅ 效果：传输数据量减少80%！

1.5 拒绝套娃查询 —— 让小哥一次问清楚

问题SQL ：

SELECT emp_name, 
       (SELECT dept_name FROM dept WHERE dept_id=emp.dept_id) 
FROM emp;

优化方案 ：

SELECT e.emp_name, d.dept_name 
FROM emp e 
JOIN dept d ON e.dept_id = d.dept_id;

1.6 ROWID直达 —— 记住书的精确位置

适用场景 ：已知数据物理地址时闪电查询

SELECT * FROM books WHERE ROWID = 'AAAAB0AABAAAAOhAAA';

⚠️ 注意：书架重组后位置会变（表结构修改导致ROWID失效）

二、高手进阶：排除外部干扰的3大心法 🧙♂️

2.1 强制走索引 —— 给导航仪纠错

场景：明明有高速路，导航却导到乡间小道

SELECT /*+ INDEX(emp emp_name_idx) */ emp_id 
FROM emp 
WHERE emp_name LIKE '张%';  -- 强制走姓名索引

2.2 子查询变形术 —— 拒绝低效循环

错误写法 ：

SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customer WHERE vip_level > 5);

优化方案 ：

SELECT o.* 
FROM orders o 
JOIN customer c ON o.customer_id = c.id 
WHERE c.vip_level > 5;

2.3 资源瓶颈诊断 —— 数据库体检指南

症状	可能病因	解决方案
硬盘灯狂闪	I/O过高	增加索引/优化全表扫描
内存占用95%+	缓存不足	调整SGA/PGA参数
CPU持续100%	复杂计算太多	优化排序/减少嵌套循环

三、闯关练习：测测你的优化段位 🎯

题目1：分页查询优化

请优化以下查询：

SELECT * 
FROM (SELECT t.*, ROWNUM rn FROM user_log t) 
WHERE rn BETWEEN 100001 AND 100100;

答案：

SELECT * 
FROM (
    SELECT t.*, ROWNUM rn 
    FROM user_log t 
    WHERE ROWNUM <= 100100  -- 先限制上限
) 
WHERE rn > 100000;         -- 再跳过前10万

四、学习加油站 ⛽

推荐资源 ：

📚 入门必看：《SQL必知必会》
🛠️ 工具神器：Oracle SQL Developer的执行计划功能
🔧 实战宝典：《高性能MySQL》第6章

学习建议 ：

多用 EXPLAIN 分析SQL执行路径
每月检查一次慢查询日志
在测试环境大胆尝试优化方案

🎯下期预告 ：《SQL优化之过程函数优化》
💬互动话题 ：你在学习SQL时遇到过哪些坑？欢迎评论区留言讨论！
🏷️温馨提示 ：我是[随缘而动，随遇而安], 一个喜欢用生活案例讲技术的开发者。如果觉得有帮助， 点赞关注不迷路 🌟