SQL 进阶:子查询与索引的第一课
上篇我们学会了用查询语句从单表取数、用连接把多表拼起来,日常工作中八成查询到此为止。但还有两类问题只靠上篇的写法会很绕:一个是“先算出一个结果,再拿它去筛另一张表”,另一个是“表变大之后查询越来越慢”。前者靠子查询,后者靠索引。
一、子查询:把一条查询的结果当作输入
子查询就是嵌在另一条语句里的查询。最常见的用法是放在条件里:先找出符合条件的用户,再查他们的订单。
SELECT amount, created_at FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE city = '杭州'
);
括号里的查询先执行,返回一列编号,外层再用它过滤订单。注意子查询返回一列时才能配合条件判断使用,返回多列会报错。
子查询也可以放在查询来源的位置,相当于先做出一张临时表再查它:
SELECT city, AVG(order_total) AS avg_total
FROM (
SELECT u.city AS city, SUM(o.amount) AS order_total
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.city
) AS user_totals
GROUP BY city;
内层先按人汇总出每人的订单总额,外层再按城市取平均。写法比一步到位长,但每一步都容易单独验证。
判断“存在”时用存在性子查询,比先查出全部再比对更直接:
SELECT name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 1000
);
这里的内层查询只关心有没有满足条件的行,找到一行就够了。查“从未下过单的用户”时,把存在性判断取反即可,效果与上篇的左连接写法相同,按可读性任选一种。
子查询的代价是可读性:嵌套超过两层就很难一眼看懂。能用连接直接表达的优先用连接,子查询留给“先汇总、再过滤”这类连接不好表达的场景。
二、索引:给常查的列建一本目录
没有索引时,数据库要从头到尾扫整张表才能找到满足条件的行,表越大越慢。索引相当于按某一列排好序的目录:查这列时先翻目录定位,再取行。
CREATE INDEX idx_users_city ON users (city);
CREATE INDEX idx_orders_user_id ON orders (user_id);
第一条让按城市过滤变快,第二条让按用户编号关联订单变快——连接条件里最常用的列,正是最值得建索引的地方。
可以用执行计划验证索引是否生效:
EXPLAIN SELECT name FROM users WHERE city = '杭州';
如果计划里出现索引扫描而不是全表扫描,说明目录被用上了;如果还是全表扫描,可能是表太小、优化器认为扫全表更划算,不必强求。
索引不是越多越好。每建一个索引,写入、修改、删除时都要多维护一份目录;一张表上建太多索引,写操作会明显变慢。经验是:只给经常出现在过滤条件、连接条件和排序里的列建索引,写多读少的表尤其要克制。主键自带索引,不用重复建。
三、动手练习
- 用子查询查出订单总金额高于全部用户平均订单总额的用户。
- 用存在性子查询找出 2026 年下过单、且单笔金额超过 500 的用户。
- 给订单表的创建时间列建索引,再用执行计划对比按时间范围查询前后的变化。
小结
子查询解决的是“分步算”的问题:先算出中间结果,再拿它去过滤、汇总或判断存在。索引解决的是“查得慢”的问题:给常查的列建目录,让定位代替全表扫描。一个管表达,一个管速度,合起来就是从“会写查询”到“写出好查询”的第一步。下一步可以学事务与约束:多条写操作如何要么一起成功、要么一起回滚。
- 点赞
- 收藏
- 关注作者
评论(0)