你以为索引加得越多越快？SQL查询优化的七个反直觉真相

上周帮一个同事看慢查询，他的表有两千多万数据，查询跑了40秒。我看了一眼EXPLAIN，发现他竟然在主键id上建了组合索引(id, created_at, status)——问他为什么这么干，他说："主键不是最快吗？我把常用的筛选字段都加上，查询肯定快。"

结果我删了两个冗余字段，查询从40秒降到200毫秒。他的表情从"你在逗我"到"我的世界观崩塌"只用了三秒钟。

数据库优化这个领域，充斥着大量"老司机经验"和"教科书教你"的规则，但真正在生产环境里跑过海量数据的人都知道——很多看似正确的优化原则，在特定场景下就是毒药。今天我们就来扒一扒SQL优化里那些反直觉的真相。

反直觉一：索引越多，查询越慢——不是越快

这是最普遍的错误认知："查询慢？加索引啊。"

索引是什么？是额外的数据结构，需要占用磁盘空间，需要在INSERT/UPDATE/DELETE时同步维护。每一个索引都是写操作的负担。

我见过一个订单表，建了17个索引。老板说："查询要快！"结果呢？每下一单，要更新17个索引结构，事务时间从3毫秒暴增到45毫秒，高峰期数据库CPU打满，一秒只能处理二十几单。这就是"为查询加速，让写入送命"的典型案例。

索引不是万能药。正确的思路是：先找到真正需要优化的慢查询（用EXPLAIN分析），然后针对这些查询设计索引，而不是在所有可能用到的字段上狂建索引。一般超过5个索引的表，就应该review一下了。

-- 查看索引使用情况，真正意义上的"用到了吗"
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'orders';

-- 如果某个索引 idx_scan = 0，说明从建好到现在一次都没被用过
-- 这种索引存在的唯一价值就是拖慢你的写性能

反直觉二：EXPLAIN显示"用了索引"，不代表真的快

很多人看到EXPLAIN输出里有Using index就放心了，心想"索引生效了，没问题"。但我告诉你，EXPLAIN里的索引状态，有至少三种欺骗你的方式。

第一种：索引覆盖了查询，但回表代价巨大。

-- 表结构：users (id PK, name, email, phone, address, created_at, updated_at)
-- 查询：SELECT id, name, email FROM users WHERE created_at > '2024-01-01'

-- 建立了索引：idx_created_at (created_at)
-- EXPLAIN显示：Using index condition (idx_created_at)
-- 但实际上：这个查询要先通过created_at索引找到所有符合条件的id，
-- 再逐个回表查id, name, email——如果符合条件的记录有几百万，
-- 回表成本可能比全表扫描还高！

第二种：索引选择率太低。

如果你的查询返回了表中30%以上的数据，数据库优化器会直接放弃索引做全表扫描——因为顺序读磁盘比随机读索引再回表更快。这是优化器的正确决策，但很多程序员不知道，看到没有"Using index"就慌了，开始瞎加索引。

第三种：索引列参与了计算或函数调用。

-- 看起来有索引但用不上
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
SELECT * FROM products WHERE price * 1.1 > 100;

-- 正确写法：提前计算或用范围查询
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE email = 'test@example.com';
SELECT * FROM products WHERE price > 100 / 1.1;

所以每次看EXPLAIN，不仅要看"用没用索引"，更要看索引扫描了多少行，回表多少次，排序有没有在内存里完成。只看索引有没有被提到，是初级的优化思路。

反直觉三：JOIN不一定比两次查询慢——看数据量

江湖上流传一种说法："JOIN效率低，能用两次查询就两次查询。"很多老司机奉为圭臬，见到JOIN就皱眉。

但这其实是有条件的。如果你的两次查询返回的数据量很大，而且需要应用层做内存关联，那个"两次查询"的方案反而是灾难。

-- 场景：查用户及其最近10笔订单
-- 方案A：两次查询
SELECT * FROM users WHERE id = 123;
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
-- 应用层组装：2次网络往返，2次数据库执行

-- 方案B：JOIN
SELECT u.*, o.id as order_id, o.amount, o.created_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = 123
ORDER BY o.created_at DESC
LIMIT 10;
-- 1次网络往返，1次数据库执行，对于小结果集JOIN往往更快

真正应该关心的是：JOIN的表有没有建立合适的关联字段索引，ON条件是否明确（避免笛卡尔积），以及结果集会不会产生行膨胀。合理的JOIN是现代数据库最强大的功能之一，别因为偏见就拒绝了它。

反直觉四：COUNT(*)和COUNT(1)一样快——不是谁比谁快

这个话题在知乎上吵了十年。有人信誓旦旦地说"COUNT(*)会解析字段名，COUNT(1)直接数行，所以COUNT(1)快"。

实际上在主流数据库（MySQL、PostgreSQL）里，COUNT(*)和COUNT(1)在性能上没有区别。优化器会把COUNT(1)当成COUNT(*)，因为1是个常量，不需要读取任何列。

真正有区别的，是COUNT(主键)和COUNT(普通字段)：

-- MySQL InnoDB：
-- COUNT(*) / COUNT(1)：不做任何列读取，直接数行
-- COUNT(主键)：需要读取主键字段，但InnoDB做了优化
-- COUNT(普通字段)：需要读取该字段值，遇到NULL要跳过

-- PostgreSQL：
-- 差异不大，都会走全表扫描或索引扫描，索引扫描更快

所以别再迷信"COUNT(1)比COUNT(*)快"了。如果有人拿这个反驳你，直接问他："你测过吗？"十有八九没测过，都是道听途说。

反直觉五：分页offset越大越慢——不是因为数据多

做分页的都知道，深分页会越来越慢。常见的解释是："offset大了，要跳过前面那么多行，当然慢。"这个解释对，但不够准确。真实原因是：数据库需要先扫描到offset+limit的位置，把这些行都扫一遍，然后扔掉前offset行，只返回后面的limit行。

比如说LIMIT 10 OFFSET 100000，数据库要扫10万零10行，只返回最后10行。这就是经典的"大offset问题"。

解决方案不是优化查询本身，而是改变分页逻辑：

-- 错误：offset分页，越往后越慢
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- 正确：基于游标的分页，只查需要的
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 10;
-- 如果id上有索引，这个查询只扫描10行，无论offset多大

Google和Facebook的搜索结果为什么没有"第100页"？不只是产品设计问题，底层就是因为offset分页在高偏移量下性能灾难。它们用的就是游标分页。

反直觉六：COUNT(*)性能不是线性增长的

很多人以为COUNT(*)的性能跟数据量成正比，1000万行比100万行慢10倍。

实际上，MySQL InnoDB的COUNT(*)性能取决于：数据能否放在Buffer Pool里，以及存储引擎是否有优化。如果数据在内存里，COUNT一千万行可能只需要零点几秒；如果数据在磁盘上，可能要几十秒。

但更有意思的是：同样硬件条件下，从100万行COUNT到1000万行，性能不一定慢10倍——可能只慢2到3倍，因为数据库会利用多核并行扫描、压缩页扫描等优化。

真正影响COUNT(*)性能的：Buffer Pool命中率、是否有覆盖索引可以扫描、MVCC在某些隔离级别下的可见性判断开销。所以先看看你的Buffer Pool配置够不够大，再考虑加缓存。

反直觉七：批量INSERT快不是因为数据库变快了

批量插入能提升性能，这点没人质疑。但为什么快？很多人会说"减少了网络往返"。这是原因之一，但不是最重要的。

真正的性能差距来源于：事务提交次数。如果你逐条INSERT并逐条COMMIT，每条记录都触发一次完整的事务过程——写日志、锁检查、刷盘、同步。批量INSERT一条COMMIT，数据库只需要做一次。

-- 逐条插入：10000条，每条单独提交
for (Order order : orders) {
    db.execute("INSERT INTO orders ...");
    db.commit();
}
// 耗时：30秒+

-- 批量插入：一条大事务
db.beginTransaction();
for (Order order : orders) {
    db.execute("INSERT INTO orders ...");
}
db.commit();
// 耗时：200毫秒

-- 更进一步：LOAD DATA INFILE（MySQL）或COPY（PostgreSQL）
-- 绕过SQL解析层，以数据库内部格式批量导入
-- 耗时：50毫秒以内

但事务太大也有问题：如果有100万条，一个大事务会导致回滚段膨胀、锁持有时间过长、主从复制延迟爆炸。所以批量大小要控制好，通常建议每批5000到1万条，分批提交。

总结：优化数据库，本质上是理解数据库的思维方式

做了这么多年数据库优化，我最大的感受是：大多数性能问题，不是数据库太慢，而是程序员的认知模型跟数据库的实际工作原理有偏差。

索引不是越多越好、JOIN不是越少越好、EXPLAIN不是看到索引就完事、分页offset是万恶之源、COUNT没有你想的那么慢也没有你想的那么快——每一条反直觉的真相背后，都是数据库实现和直觉假设的差异。

下次遇到慢查询，别急着加索引。先问自己三个问题：

• 这条查询真正返回了多少数据？（不是表有多少数据）
• 索引选中了多少行？（选择率决定了是否该用索引）
• 执行计划是数据库做的选择还是我强制的？（有时候优化器比你想的更聪明）

想清楚这三个问题，你的SQL优化就已经赢过80%的开发者了。