慢查询优化实战四条套路
不用背一百个优化技巧。所有 SQL 优化就这四条路——从投入小到大排列，挨个试。
优化金字塔（从便宜到昂贵）：
第 1 层：改 SQL 写法 ← 零成本，先试这个
第 2 层：改/加索引 ← 小成本，主力手段
第 3 层：改表结构 ← 中成本，需要数据迁移
第 4 层：改架构（缓存/读写分离） ← 高成本，最后手段
1.1 第 1 层：改 SQL 写法（零成本）
套路 1：SELECT * → 只查需要的列
❌ SELECT * FROM users WHERE name = '张三';
→ 把所有列的数据全拿回来，包括你根本不需要的 avatar(大 BLOB)、intro(长 TEXT)
✅ SELECT id, name, age FROM users WHERE name = '张三';
→ 只拿三列，传输数据量小 80%，更可能触发覆盖索引
套路 2：大偏移分页 → 改游标分页
❌ SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
→ MySQL 先查前 1000020 行，扔掉前 1000000 行，返回后 20 行。
→ 扫了 100 万行才吐 20 行——白做 999980 行的无用功！
✅ SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
→ 从 id=1000000 开始直接往后取 20 行，只扫了 20 行。
→ 前提：前端配合记住上一页最后一条的 id。
套路 3：JOIN 替代子查询
❌ SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
→ 子查询先生成临时结果 → 再和 users 表匹配。数据量大时临时表是瓶颈。
✅ SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;
→ JOIN 比 IN 子查询更容易被优化器优化，索引利用率更高。
套路 4：COUNT(*) vs COUNT(列)
✅ SELECT COUNT(*) FROM orders; → 统计总行数（包括 NULL 行）
⚠️ SELECT COUNT(column) FROM orders; → 统计 column 不为 NULL 的行数
→ 两者语义不同！用哪个取决于你要不要排除 NULL。
套路 5：避免在 WHERE 里对列做运算
❌ SELECT * FROM orders WHERE YEAR(created_at) = 2026;
✅ SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
1.2 第 2 层：改/加索引（小成本，主力手段）
套路 1：WHERE 条件列 + ORDER BY 列 + JOIN 关联列 → 建索引
建索引口诀：哪里出现在 WHERE、ORDER BY、JOIN ON 里，哪里就该考虑建索引。
套路 2：复合索引列的顺序 = 等值在前，范围在后
WHERE name = '张三' AND age > 20 AND status = 'active' ORDER BY created_at
等值：name, status
范围：age
排序：created_at
→ 建索引 idx(name, status, age, created_at)
→ 等值条件放前面，范围条件放后面，ORDER BY 放最后
→ 这是"最佳左前缀"的精髓
套路 3：覆盖索引——让查询永远不用回表
SELECT name, age FROM users WHERE name = '张三';
→ 建 idx(name, age) 而不是只建 idx(name)
→ 查询只需要 name 和 age → 两列全在索引里 → 直接读完索引就走 → 不回表
→ Extra: Using index ✅
套路 4：长得像的索引删掉
idx_a(a) ← 冗余，idx_a_b 包含 a
idx_a_b(a, b)
删 idx_a，留 idx_a_b。覆盖 a 的查询同样能用 idx_a_b（最左前缀）。
冗余索引 = 浪费磁盘 + INSERT/UPDATE 时要多维护一份索引 = 写操作变慢。
套路 5：建完索引用 EXPLAIN 验证
→ type 从 ALL 变成 ref/range 了吗？
→ rows 减少了吗？
→ Extra 里 Using filesort / Using temporary 消失了吗？
没变 → 索引没生效 → 回头看第四章的失效场景。
1.3 第 3 层：改表结构（中成本）
场景：字段太大或设计不合理。
① 大字段拆表
users 表有一个 avatar BLOB 列（头像二进制数据），但 90% 的查询不需要它。
→ 拆成 users 表（基本信息）+ user_profiles 表（大字段）
→ users 表的行变小 → 一页能装更多行 → 全表扫描也更快
② 垂直拆分——按列的访问频率分表
热点列（经常查的）放一张表，冷门列放一张表。主键一致。
③ 冗余字段——反范式
orders 表每次查都要 JOIN users 拿 user_name。
→ 在 orders 表里直接加 user_name 冗余列。
→ 牺牲一点空间（多存一个名字）和一致性（用户改名要同步改），换取不用 JOIN。
1.4 第 4 层：改架构（高成本）
① 加 Redis 缓存 → 热点查询走缓存，不碰数据库
② 读写分离 → 主库写，从库读，分担压力
③ 分库分表 → 一张表太大（几十亿行），拆成多张表
这三个是第八九天会专门讲的，今天知道有这条退路就行。
速记卡（面试闪卡）
Q1：一句话讲清「慢查询优化实战四条套路」到底是什么？
A：慢 SQL 优化有四条从便宜到贵的万能套路：改写法、加索引、改表结构、改架构，挨个试。
Q2：第 1 层改 SQL 写法有啥招？ —— 怎么理解？
A：像少跑腿少搬东西——SELECT * 改成只查需要的列（传输少 80%、易触发覆盖索引）；大偏移分页 LIMIT 1000000,20 改成游标 WHERE id>上一页;子查询换 JOIN;别在 WHERE 里对列套函数（YEAR(created_at) 会让索引失效）。
Q3：第 2 层加索引怎么加才对？ —— 怎么理解？
A：像在常用路口设红绿灯——WHERE、ORDER BY、JOIN ON 出现的列就该建索引；复合索引按等值在前、范围在后、排序最后（最左前缀）；建覆盖索引让查询不回表；建完用 EXPLAIN 看 type/rows/Extra 验证生效。
Q4：第 3、4 层改表和改架构是啥？ —— 怎么理解？
A：像店面改造和开分店——第 3 层改表结构：大字段拆表、冷热列垂直拆分、加冗余字段反范式省 JOIN；第 4 层改架构（高成本退路）：加 Redis 缓存、读写分离、分库分表。
Q5：优化金字塔为什么从便宜到贵排？ —— 怎么理解？
A：像看病先吃药再手术——改写法零成本先试，加索引小成本主力，改表结构中成本要迁移，改架构高成本才动。多数慢查询前两层就解决了，别一上来就分库分表。
Q6：核心速记主线有哪些？
-
第1层改SQL：只查所需列、游标分页、JOIN替子查询、别在列上套函数
-
第2层加索引：WHERE/ORDER BY/JOIN列建索引、复合索引最左前缀、覆盖索引不回表、EXPLAIN验证
-
第3层改表：大字段拆表、垂直拆分、冗余字段反范式
-
第4层改架构：Redis缓存、读写分离、分库分表（最后手段）
口诀
A：慢查询，四条路，从便宜到贵数。
先改写法零成本，只查所需分页游。
再加索引小成本，最左前缀覆盖住。
表结构，架构层，前两层不行再动土。