索引与查询优化：别让查询全表扫描
理解索引、EXPLAIN、N+1 问题与批量取代替循环查
- 理解索引为什么能加速查询
- 知道什么时候不该加索引
- 认识 N+1 查询问题
- 会用一次批量查询代替循环里逐条查
查字典你不会一页页翻到底，而是先翻目录页定位到那一沓，再直接翻过去。数据库索引就是这个目录：它额外维护一份排好序的「目录」，让查找不用扫全表。但目录本身也要占地方、每次改内容都得更新。
索引 = 书的目录
没有索引，WHERE id=123 就得从第一行扫到最后一行（全表扫描）。给高频查询的列建索引后，数据库先在索引树里快速定位，再直接取那几行。
数据库索引大多用 B+ 树实现，它和书目录是同一个道理，但快在哪要说透。没有索引，找一行要逐行比对，数据量翻倍、工作量也翻倍（线性）；有 B+ 树索引，它是一棵平衡的多路树，数据按顺序排好存树上，查找时从根节点一层层往下分叉定位，数据量翻一千倍，树高通常只从 3 层变 4 层。换句话说，全表扫描是「翻完整本书」，B+ 树索引是「翻三四次目录页」。
一张 100 万行的表，没有索引按 user_id 查一行，最坏要比对约 100 万次；建了 B+ 树索引，通常只需顺着树走约 3–4 层、十几次就能定位到那一页。这是百万倍到十倍的量级差。注意这是量级感受：真实耗时还取决于是否命中缓存、行宽、磁盘类型，别当精确基准。
-- 给 posts 表的 user_id 列建索引（按作者查文章时会用到）
CREATE INDEX idx_posts_user ON posts(user_id);
-- EXPLAIN 看执行计划：确认查询有没有真的用上索引
EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42;复合索引与最左前缀
实际查询常常同时带多个条件，比如 WHERE user_id = 42 AND status = 'published'。这时建一个(user_id, status)的复合索引，比分别建两个单列索引更管用。但有个铁律叫「最左前缀」：索引是按列的顺序排好队的，查询必须从最左边那列开始用起，才能用上它。建了(user_id, status)，WHERE user_id=42 能用，WHERE user_id=42 AND status='published' 能用，但只写 WHERE status='published' 就用不上——因为你跳过了队伍最前面的 user_id，没法按顺序定位。
以为索引建得越多越好。索引加速查询，但每次 INSERT/UPDATE/DELETE 都要把改动同步写进所有相关索引树，这叫写入放大。一张表塞七八个索引，写入会明显变慢、索引本身还占磁盘。只为高频出现在 WHERE / JOIN / ORDER BY 的列建索引，小表和很少查的列别加。
EXPLAIN 怎么看：type=ALL 就是全表扫
加了索引不代表查询真的用上了。把 SQL 前面加 EXPLAIN（PostgreSQL 用 EXPLAIN ANALYZE）看执行计划，重点盯 type 这一列：出现 type=ALL 就意味着数据库老老实实在做全表扫描，一行一行扫，索引没生效；type 变成 range 或 ref 这类，才说明走了索引。看到 ALL 就要回去检查：是不是 WHERE 条件没命中索引列、还是写了函数把索引列包起来（如 WHERE DATE(created_at) = ...）导致索引用不上。
一是对索引列套函数：WHERE DATE(created_at) = '2026-09-01'，数据库没法用 B+ 树里按原始值排好的顺序，只能逐行算日期，索引作废。改成范围查询 WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02' 就能用上。二是前导通配符：WHERE name LIKE '%张'，通配符开头没法走索引顺序；只有 '张%' 这种前缀匹配才能走索引。
还有个进阶概念叫覆盖索引：如果查询要的列（SELECT 的列 + WHERE 的列）全都在索引里，数据库直接从索引树就把数据拿全了，不用再回表去数据行里读，这叫覆盖索引，更快。入门知道有这回事即可，建索引时可以把高频查询要的列尽量纳进来。
另外索引有「区分度」一说：一列里不同值越多，索引越好用。给性别这种只有男/女两个值的列建索引，数据库扫一半还是得回表，索引起不了多大作用；给 user_id、order_id 这种几乎每行都不同的列建索引，收益最大。建索引前先想这一列的值够不够分散。
N+1 问题
N+1 是最常见的性能坑：先查出 N 条主记录（1 次查询），然后在循环里为每条再查一次关联数据（N 次查询），总共 1+N 次。文章列表页里「查出 20 篇文章，再 for 循环逐篇查作者」就是典型。
真实场景：用户列表页要显示 50 个用户的头像。代码先 SELECT 出 50 个用户（1 次），然后渲染时发现每个用户对象里没有头像 URL，于是在循环里对每个用户都执行一次 SELECT avatar FROM users WHERE id=?（又 50 次）。一次页面加载就是 51 次数据库往返。数据库能扛住的并发连接数是有限的，51 次往返不仅慢，还可能把连接池占满、拖垮整个接口。改成一次性 SELECT id, avatar FROM users WHERE id IN (那 50 个 id)，再在内存里按 id 配好对，就从 51 次压成 2 次。
一次数据库往返哪怕只要 1–2 毫秒，50 次串行就是 50–100 毫秒纯等待，还没算查数据本身；批量 2 次往返只要几毫秒。N 越大差距越悬殊：1000 条主记录就是 1001 次往返，可能直接几秒。这就是为什么 ORM 都在帮你自动避免 N+1。
动手：对比 N+1 与批量查询
下面用计数模拟两种写法：循环逐条查 vs 一次性批量查，打印查询次数的差别。
把「循环里查 N 次」改成「用 IN (...) 或 JOIN 一次查出全部」，在内存里按 id 分好组。ORM（如 Prisma 的 include）会帮你自动做这件事。
N+1 查询问题应该怎么修？
资深工程师加餐
底层原理 · 大厂视角 · 工程经验，点卡片展开
穿透是反复查「根本不存在」的数据，用布隆过滤器或缓存空值挡；击穿是某个热点 key 过期瞬间大量请求压向数据库，用互斥重建或逻辑过期；雪崩是大批 key 同时过期，给 TTL 加随机抖动。缓存与库的一致性常用「先更新数据库、再删除缓存」，并靠重试/订阅补偿。
挑战任务
数出 N+1 与批量的查询次数
N+1 是 1+N 次查询，批量只需 2 次。实现 loop_count(n) 返回 1+n，batch_count(n) 返回 2，打印对比。
课后作业
修复 N+1：把循环查改成批量取
下面的 fake_rows 模拟数据库。n1(users) 是反面教材（循环里逐人查文章）。请实现 batch(users)：用一次 query_posts_batch 批量拿回所有作者的文章字典，直接返回。目标是把查询次数从 N 次压到 1 次。