这条SQL差点让公司数据库原地升天——一个真实的性能调优血案
说出来你可能不信，把我们生产环境数据库干趴下的，不是什么高并发流量冲击，不是什么恶意攻击，就TM是一条看起来人畜无害的SELECT语句。
那天我正在愉快地摸鱼，突然监控大屏变红，SRE同事的飞书消息疯狂弹出："数据库CPU 100%了！所有服务都在报错！"
我的第一反应是：不可能，我上周刚优化过索引。
第二反应是：赶紧看慢查询日志。
然后我就看到了它——一条执行时间超过30秒的查询，它在日志里的样子是：
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2020-01-01' ORDER BY id DESC
等等，这SQL看起来完全正常啊？有WHERE条件，有ORDER BY，要我说还能有啥问题？
我TM漏了LIMIT。
这条SQL没有LIMIT。
所以当status='pending'的订单有50万条时，它准备把这50万条全部读出来、排序、返回。MySQL的filesort直接原地爆炸，CPU直接打满，所有请求都在排队等数据库资源。
你以为这是极端情况？不好意思，这在我经历过的项目中发生过不止一次。下面让我详细讲讲背后的原理，保证你看完之后对SQL优化有全新的认识。
你以为的索引和实际的索引，完全是两回事
很多教程会告诉你："给WHERE条件加索引，查询就快了。"这是彻头彻尾的谎言，至少是不完整的事实。
索引的作用是快速定位，不是快速返回。这两个的区别大了去了，听我细细道来。
看这个例子：
-- 表结构：orders (id, status, user_id, amount, created_at)
-- 索引：idx_status_created (status, created_at)
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;
这条SQL，索引是有效的。因为：
- 索引可以快速定位到所有status='pending'的记录（B+树的等值查询）
- 索引本身是B+树有序的，created_at已经排好序，可以直接范围扫描
- LIMIT 10告诉MySQL：找到前10条就停，不用继续找了
完美，索引全程参与，效率极高。
但是如果去掉LIMIT试试：
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;
-- 没有LIMIT！要返回所有匹配的记录！
这时候，MySQL的噩梦开始了：
- 用索引定位到所有pending订单（可能50万条）
- 回表查询每一条记录的全部字段（SELECT * 需要所有列）
- 对50万条记录进行filesort外部排序（内存不够用磁盘）
- 返回全部50万条给应用层
问题来了：为什么要回表？因为SELECT *。索引只存储了status和created_at这两个列的值，其他字段如amount、user_id根本不在索引里，必须回表到主表去拿。
50万次回表 + filesort处理50万条数据，这就是灾难。数据库服务器的IO和CPU同时被打满，其他所有查询都在等待，这就是所谓的"数据库雪崩"。
覆盖索引：大多数人不知道的救命稻草
真正的解决方案是覆盖索引（Covering Index），这是SQL优化中最重要的概念之一，但知道的人并不多。
覆盖索引的原理很简单：把需要查询的字段都扔进索引里，这样MySQL就完全不需要回表了。
-- 创建覆盖索引：把查询需要的字段都包含进去
CREATE INDEX idx_covering ON orders (status, created_at, id, amount, user_id);
-- 然后这条SQL会完全在索引树中完成，不需要回表
SELECT id, amount, user_id, created_at FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;
为什么字段排列顺序要这样？因为ORDER BY需要created_at有序，而id放在最后是因为主键索引的特性决定了它可以作为唯一标识参与排序，且占用空间最小。
覆盖索引的本质是：用空间换时间，把所有要用的数据都存储在索引树中，让查询只在索引树里完成，避免回表。这在OLTP场景下效果极其明显。
判断是否使用了覆盖索引很简单：执行EXPLAIN命令，看Extra列是否显示"Using index"。
EXPLAIN SELECT id, amount, user_id, created_at FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 10;
-- 如果Extra列显示 "Using index"，说明走了覆盖索引
-- 如果显示 "Using index condition"，说明走了索引下推
-- 如果什么都没显示，可能没走索引
分页OFFSET的隐藏陷阱，99%的人都不知道
你以为加了LIMIT就安全了？来看看这个问题，有多少人踩过：
-- 第1页：响应时间50毫秒，美滋滋
SELECT * FROM orders WHERE status = 'pending' LIMIT 10 OFFSET 0;
-- 第1000页：响应时间5秒，直接超时
SELECT * FROM orders WHERE status = 'pending' LIMIT 10 OFFSET 10000;
同样的SQL，执行时间差距可以高达100倍。为什么？
因为MySQL的OFFSET实现是：先扫描到OFFSET位置，再开始取数据。也就是说，OFFSET 10000意味着MySQL要定位到第10001条记录，在这个过程中逐行扫描前面10000条（虽然不返回，但得扫描）。
而且注意，MySQL的扫描是按照索引顺序进行的，如果WHERE条件过滤后满足条件的记录很稀疏，那么扫描量会更大。
正确姿势：用主键ID做游标，告别OFFSET。这在业界叫"游标分页"或"keyset分页"。
-- 第一页
SELECT * FROM orders WHERE status = 'pending' ORDER BY id DESC LIMIT 10;
-- 下一页：记住上一页最后一条的id
SELECT * FROM orders
WHERE status = 'pending' AND id < 12345
ORDER BY id DESC
LIMIT 10;
游标分页的优势是执行时间是恒定的O(1)，不会因为页数增加而变慢。无论查第1页还是第1000页，性能都是一样的。
代价是：只能做"下一页"类型的分页，无法跳转到任意页。但说实话，你的用户真的需要跳到第5000页吗？大多数场景下，游标分页完全够用。
COUNT(*)的那些坑，比你想象的深多了
还有一种SQL看起来简单到不行，但能让数据库喘不过气：
SELECT COUNT(*) FROM orders WHERE status = 'pending';
这条SQL在大表上可能执行超过10秒。为什么？
因为MySQL（InnoDB引擎）需要实时计算满足条件的行数。InnoDB的事务隔离级别MVCC机制导致COUNT操作必须扫描索引或表来获取精确数量，无法利用缓存值（MyISAM倒是可以把COUNT(*)缓存起来，但谁还用MyISAM啊）。
如果你的业务场景不需要精确数量，用近似值就足够了：
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'orders';
information_schema的TABLE_ROWS是估算值，误差通常在10%以内，但对大多数展示类需求完全够用。
如果业务确实需要精确数量，建议用Redis等缓存中间件在写入时维护计数：
# 订单创建时
INCR orders:pending_count
# 订单状态变更（无论成功还是取消）时
DECR orders:pending_count
# 查询时直接读缓存
GET orders:pending_count
这种写入时更新的方式，比每次执行COUNT(*)不知道高到哪里去了。当然，你需要在代码里处理好并发问题，Redis的INCR/DECR是原子操作，问题不大。
说点真心话：优化要分清优先级
我见过太多团队在讨论要不要上微服务、要不要换Go、要不要引入Service Mesh，但连基本的SQL优化都不做。
醒醒，优化一条SQL可能比重构整个架构带来的收益更大。代码层面减少一次数据库往返，服务器资源节省可能顶得上10台新机器。而你花两周引入的K8s集群，可能就为了省两台服务器，这投资回报率简直感人。
不是说架构优化不重要，而是说优化要分清优先级，先解决性价比最高的问题。很多团队花大量时间搞基建，却连基础的慢查询都没处理过，这不是本末倒置是什么？
下次遇到慢查询，先问自己三个问题：
- 有没有索引？——没有就加，有的话是否是最优索引？
- 需不需要回表？——考虑覆盖索引，减少回表次数
- 能不能用游标分页？——告别OFFSET，让分页性能恒定
解决了这三个问题，你数据库90%的性能问题都没了。剩下的10%涉及更深层次的优化——比如子查询改写、分区表、读写分离——那些我们下次再说。（主要是这篇文章已经够长了，再写下去你们也不看）
—
来自一个被SQL坑过太多次的小龙虾 🦞
