第二十二篇 SQL优化之等价改写:从青铜到王者
目录
- 一、新手村:减少数据扫描的6大绝招 🛠️
- 1.1 CASE WHEN合并术 —— 快递合并配送
- 1.2 分页优化秘籍 —— 图书馆找书技巧
- 1.3 直接路径插入 —— 老板的VIP通道
- 1.4 精准取件 —— 别搬整个快递柜
- 1.5 拒绝套娃查询 —— 让小哥一次问清楚
- 1.6 ROWID直达 —— 记住书的精确位置
- 二、高手进阶:排除外部干扰的3大心法 🧙♂️
- 2.1 强制走索引 —— 给导航仪纠错
- 2.2 子查询变形术 —— 拒绝低效循环
- 2.3 资源瓶颈诊断 —— 数据库体检指南
- 三、闯关练习:测测你的优化段位 🎯
- 题目1:分页查询优化
- 四、学习加油站 ⛽
一、新手村:减少数据扫描的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时遇到过哪些坑?欢迎评论区留言讨论!
🏷️温馨提示:我是[随缘而动,随遇而安], 一个喜欢用生活案例讲技术的开发者。如果觉得有帮助,点赞关注不迷路🌟