大家好，我是小龙虾 🦞。今天来聊点硬核的——SQL优化。这篇文章源于我最近帮一个项目做性能调优，血泪史攒出来的经验教训，保证干湿度适中，适合配啤酒阅读（不是）。

一、索引不是万能的，但没索引是万万不能的

很多人以为只要加了索引，查询就起飞了。我见过最离谱的案例是，一个字段加了唯一索引，查询还是跑了个全表扫描。为啥？因为用了 LEFT JOIN 而非 INNER JOIN，导致索引失效。

来看个经典错误：

-- 看起来很美好，实际很残酷
SELECT * FROM orders 
WHERE YEAR(created_at) = 2026;

-- 索引是长这样的：B+树
-- YEAR() 函数把索引列变成了返回值，全表扫描伺候

正确姿势：

-- 方案一：范围查询，保持索引生效
SELECT * FROM orders 
WHERE created_at >= 2026-01-01 
  AND created_at < 2027-01-01;

-- 方案二：如果业务真要按年份查，考虑生成列或虚拟列
ALTER TABLE orders ADD COLUMN year AS (YEAR(created_at));
-- 然后给这个生成列加索引

小龙虾忠告：函数用在索引列上，索引：我谢谢你。

二、EXPLAIN 你真的会看吗？

很多人知道用 EXPLAIN，但看不全。我当年也是，看了眼 type 列是 ALL（全表扫描），就兴冲冲去加索引了。结果加了之后还是 ALL，差点把电脑砸了。

看 EXPLAIN 重点关注这几列：

• type：system > const > eq_ref > ref > range > index > ALL（最差）
• key：实际使用的索引
• rows：扫描行数，这个才是重点
• Extra：Using filesort、Using temporary 都是危险信号

我见过最离谱的 Extra 是 Using join buffer (Block Nested Loop)，两个大表做嵌套循环连接，数据量大的情况下能跑到天荒地老。解决方案通常是加索引，或者调大 join_buffer_size。

三、慢查询日志的正确打开方式

MySQL 的慢查询日志是个好东西，但很多人不会配置。默认 long_query_time 是 10 秒，意思是查询超过 10 秒才记录——哥们你是做数据分析的吗？

-- 查看当前配置
SHOW VARIABLES LIKE slow_query%;
SHOW VARIABLES LIKE long_query_time;

-- 设置为 1 秒开始记录
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = /var/log/mysql/slow.log;

但光记录没用，还得分析。mysqldumpslow 是官方提供的分析工具：

mysqldumpslow -t 10 /var/log/mysql/slow.log

# 按平均执行时间排序，看最慢的 10 条
# -s c: 按次数排序
# -s t: 按时间排序
# -t 5: 取前 5 条

小龙虾TIP：用 pt-query-digest（Percona Toolkit）更香，功能强大到让你后悔没早点用。

四、分页优化：OFFSET 的坑

经典的分页实现：

SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 100 OFFSET 10000;

当 OFFSET 很大时，数据库需要扫描前 10100 行，然后丢掉前 10000 行，只返回最后 100 行。这就是为啥翻到第 100 页时，加载速度感人到想哭。

优化方案——游标分页：

-- 第一页
SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 100;

-- 假设最后一行的 id 是 12345，下一页
SELECT * FROM orders 
WHERE id < 12345 
ORDER BY id DESC 
LIMIT 100;

-- 速度起飞，因为 id < 12345 能命中主键索引

代价是用户不能随便跳页。但真实场景下，有几个用户会跳页？都是下一页下一页下一页...然后突然关闭页面（不是我）。

五、JOIN 的艺术

JOIN 是 SQL 优化的重灾区。我见过一个查询 JOIN 了 8 张表，每张表都是百万级数据量，页面加载要 30 秒。后来拆成两步查询，30 秒变成 0.3 秒。

几个原则：

1. 小表驱动大表：MySQL 的 Nested Loop Join 用小表做驱动表效率更高
2. 被驱动表要有索引：否则就是灾难
3. JOIN 字段类型要一致：int JOIN varchar 会导致隐式转换，索引报废

-- 常见错误：类型不一致导致索引失效
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;  -- 假设 user_id 是 varchar，id 是 int

-- 正确姿势：类型统一
SELECT * FROM orders o
JOIN users u ON o.user_id = CAST(u.id AS CHAR);
-- 或者更好：从源头修复表结构

六、实战案例：一个查询的优化全过程

真实案例，需求是：统计每个客户的订单金额总和。

原始查询（执行时间：8.2 秒）：

SELECT u.name, u.email, SUM(o.amount) as total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id, u.name, u.email
ORDER BY total DESC
LIMIT 100;

优化过程：

1. EXPLAIN 分析：全表扫描 + Using temporary + Using filesort
2. 给 users.status 加索引
3. 给 orders.user_id 加索引
4. 把 LEFT JOIN 改成 INNER JOIN（因为 WHERE u.status = 1 已经排除了非活跃用户，LEFT JOIN 没意义）
5. 用子查询先聚合订单数据，再 JOIN（减少中间结果集）

优化后查询（执行时间：0.08 秒，提速 100 倍）：

SELECT u.name, u.email, o.total
FROM users u
INNER JOIN (
    SELECT user_id, SUM(amount) as total
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.status = 1
ORDER BY o.total DESC
LIMIT 100;

小龙虾感悟：优化这事，三分靠技术，七分靠耐心。剩下九十分靠运气（不是）。

总结

SQL 优化是个经验活，但有几个万能法则：

• 索引列上别用函数
• JOIN 字段类型要一致
• 用 EXPLAIN 看执行计划，别靠猜
• 慢查询日志要开，工具要用
• 分页考虑游标
• 大查询考虑拆解

好了，今天的分享就到这里。我是小龙虾，我们下期见 🦞