加索引写入TPS掉到1/3？订单表慢查询的五个反直觉真相

大家好，我是数据库小学妹👋 我踩过的坑，你别再踩。

周一刚坐下，开发小哥就甩来一条慢查询：“这表索引都建了，怎么还这么慢？”我看了眼他发来的SQL，第一反应也是老毛病：加索引。话到嘴边又咽了回去。就是加索引这个习惯，让我翻过太多次车。今天这篇，就聊聊我踩过之后才想明白的五件事，每一件都挺反直觉。

五个真相，先看总表

这五条不是背的，全在生产环境里磨出来的。下面逐个拆。

一、EXPLAIN的type会骗人：type=index也是全扫

先讲个自己丢人的事。有段时间我判断SQL慢不慢，只看key有没有值。key不为空，我就觉得走了索引，问题不在我。后来被一条SQL打脸。

-- 查最新10条带售后标记的订单
SELECT id, order_no, pay_time
FROM t_order
WHERE refund_flag = 1
ORDER BY pay_time DESC
LIMIT 10;

t_order三百多万行，带售后标记的不到1%。表上只有idx_pay_time，没建refund_flag的索引。EXPLAIN结果：

key有值吧？type还不是ALL。我第一次看，觉得没问题，实际慢到怀疑人生。

要解释它为什么慢，得先看清InnoDB的索引结构。主键索引是聚簇索引，叶子节点直接存整行数据；二级索引是另一棵B+树，叶子节点只存主键值。走二级索引取数，必须拿主键值再去聚簇索引里找一遍，这一步叫回表，本质是又一次B+树查找，通常要走两三层。

这条SQL的症结就在type=index上，它代表全索引扫描。优化器为了蹭pay_time的有序性，选了idx_pay_time从最新一条开始往下捋，每捋一条就回表验证一次refund_flag。带售后标记的订单埋在一堆正常订单下面，它得一路回表翻，翻到攒够10条为止。三万条里才出一条，多数回表落空。这不是定位，是扫街。

EXPLAIN的type是有高低的，从差到好依次是：

ALL < index < range < ref < eq_ref < const

ALL是全表扫，index是全索引扫，两者本质都靠遍历，区别只是扫聚簇索引还是扫二级索引。真正算得上“用索引定位”的，是range、ref往上的级别。所以看执行计划，别只看key有没有值。key有值、type=index、rows接近全表，一样是灾难。type和rows才是真信号。

打那以后我养成了习惯：EXPLAIN先看type和rows，再看Extra里有没有Using filesort、Using temporary。

二、索引不是免费的：加索引把写入拖垮过

第二个坑，学费更贵。

之前有个订单服务，读写比大概7:3，不算极端。开发为了一条报表，一口气给t_order加了三个二级索引，想让查询走覆盖索引。查询确实快了，从800ms降到120ms，我当时还挺高兴。三天后主库开始不对劲，写入往下掉，从库延迟从几百毫秒涨到几十秒，磁盘IO使用率天天顶着。我一度怀疑是机器问题，查了一圈，最后把目光停在刚加的三个索引上。

要算清这笔账，得看一次写入到底动了多少东西。InnoDB里每个二级索引都是一棵独立的B+树。插入一行，主键索引写一次，三个二级索引各写一次，等于四棵树都要动。如果更新的列恰好是某个二级索引的一部分，InnoDB不会原地改，而是先给旧条目打删除标记、再插入新条目，这一个索引就要写两遍。更要命的是，二级索引的叶子页不按主键顺序排列，插入基本都是随机写，页写满了还得分裂。change buffer能暂缓一部分变更，可buffer pool空间有限，扛不住的时候照样要落盘刷脏。写放大就是这么来的。

对照压测结果很直观：

读快了680ms，写吞吐掉到三分之一。对一个7:3读写的服务，这笔账算不过来。

后来怎么处理的？查冗余，做减法：

-- 找出长期没被用到的索引
SELECT * FROM sys.schema_unused_indexes;

-- 确认没有业务引用后，再删
ALTER TABLE t_order DROP INDEX idx_report_xxx;

sys.schema_unused_indexes是MySQL 5.7起自带的统计视图，读performance_schema里的索引使用记录，筛出长期没被访问的索引。版本更老没有它，就自己拿information_schema.statistics跟慢日志手工比对。

删完再压测，写入缓回来了。那条报表查询改走另一条更合适的联合索引，也没慢到哪去。用不用这索引，得算总账。查快了，写入的利息交不交得起。

三、LIMIT救不了深分页

第三个坑，在分页接口上。

运营后台有个订单流水列表，翻到后面就卡。第一页30ms，翻到一万页直接两秒起步。我一开始也以为是表太大，直到认真看了那条SQL：

-- 第10001页，每页20条
SELECT * FROM t_order_log
ORDER BY id DESC
LIMIT 200000, 20;

LIMIT 200000,20看着是取20条，坑却埋在偏移量上。MySQL执行时得把前二十万行一条条数过去，再丢掉。这不是MySQL偷懒，是B+树压根做不到“跳过N行直接定位”，它只支持从某个位置开始顺序遍历叶子链表。要第20万行之后的数据，只能从第一行数起。真正贵的从来不是那个20，是前面那个200000。

如果ORDER BY的列还没有索引支撑，会更糟。MySQL先对所有候选行做一次filesort，结果放内存排序缓冲，数据量大就溢到磁盘用归并排序，排完再数偏移量。

解法是换一种分页姿势。排序字段单调的话，用keyset分页：

-- 记下上一页最后一条id，下一页从这里开始
SELECT * FROM t_order_log
WHERE id < :last_id
ORDER BY id DESC
LIMIT 20;

为什么快？last_id有主键索引，MySQL能沿B+树二分定位到这一条，然后只顺序读后面的20条叶子。执行代价从“数N行”变成“一次定位加读20行”。

业务复杂改不了，还有个通用套路叫延迟关联：先在子查询里只查主键、分好页，再JOIN回原表取整行。这样深翻页时那些大字段不会跟着前二十万行一起被反复搬运，回表次数也被压到最少。

运营后台那种深翻页，后来我们直接限制了最大翻页深度，再深就提示用时间筛选。用户不会真的翻到十万页，但爬虫会。别让分页接口成了慢查询制造机。

四、大JOIN拆成小查询，反而更快

第四个真相，当年颠覆我的认知。

一张报表SQL，JOIN了五张表，跑30秒。我第一反应是优化JOIN：加索引、调buffer，都试了没起色。最后是个老DBA给我一句话：“拆了它。”我当时不信，查询次数变多了怎么可能更快？试完沉默了。拆成三条单表查询，加应用层组装，整页300ms。

要讲清楚为什么，得从MySQL怎么执行JOIN说起。它用的是Nested-Loop Join：先挑一张表当驱动表，拿它每一行去另一张表里找匹配。被驱动表要是能在JOIN列上用索引，一次匹配就是一次索引查找；要是没索引，就退化成都拿内存里的join buffer跟整张表比对，buffer装不下还得把被驱动表反复读好几遍。驱动表越大，循环次数越多，中间还会攒下大量中间行，超过阈值就落临时表，列再宽一点就溢到磁盘。

第一件事，是看驱动表选对没有。EXPLAIN结果的第一行，就是驱动表：

-- EXPLAIN结果
id | table      | type | rows
1  | t_product  | ALL  | 1200000   -- 驱动表选了百万行的商品表
1  | t_order    | ref  | 50

驱动表应该选过滤后行数最少的那张。那张报表真正的时间窗里只有5000笔订单，先拿订单主表当入口，把5000个order_id查出来，再去查明细、商品、商家，每步都走索引，反而快。优化器会选错驱动表，多半是统计信息不准，或者过滤条件写在后面关联的表上，它先把大表放进了循环。

拆查询也不是无脑拆。小表JOIN、两边过滤后都很小，一条JOIN没问题。怕的是大表、大范围、多层JOIN叠在一起。碰到这种，先看EXPLAIN第一行，驱动表不对就先纠正，纠正不了再考虑拆，或者用hint显式指定驱动表兜底。

五、慢查询的锅，常常不在SQL

最后一个，最气人也最容易被忽略。

开发拿着一条SQL来找我：“测试库5ms，生产慢日志天天有它，你们库是不是有问题？”我单独一跑，确实5ms，执行计划干干净净。可慢日志不会说谎。后来我学乖了：慢查询要连“那一刻”一起看。生产不是测试库，是几十个业务挤在一起的，那条SQL慢的时候，同一秒里可能正发生这些事：

• 别的业务在跑凌晨的数据批处理，把磁盘IO打满
• 一个大事务攥着行锁不放，后面一堆等锁的查询全排队
• buffer pool被别的扫描挤占，热点数据被踢出去，缓存命中率往下掉
• 更隐蔽的一种：一条没提交的事务握着表的元数据锁（MDL），整张表的查询和DDL全被堵在门口

这条慢查询很可能只是受害者，凶手在旁边。我当时用系统表抓了一次现场：

-- 看那段时间到底谁在跑、跑了多久、卡在什么状态
SELECT id, user, time, state, left(info, 80)
FROM information_schema.processlist
WHERE time > 5
ORDER BY time DESC;

结果排在最前面的，是一条凌晨批处理的UPDATE，一跑十几分钟，把整个实例的IO拖垮。后面等锁的查询，State一水是Waiting for table metadata lock，光看单条SQL永远查不出名堂。
后来再碰这类问题，我会补一张锁等待视图，把谁在等谁、持锁跑了多久、执行的是什么SQL一次拉全：

-- 看谁在等谁、持锁事务跑了多久、执行的是什么SQL
-- MySQL 8.0+ 用 performance_schema.data_lock_waits
-- MySQL 5.7 用 sys.innodb_lock_waits

持锁方、等待方，一摆出来就能对上。要是卡的是Waiting for table metadata lock，那是另一条链路。MDL不归前面两个InnoDB锁视图管，得去performance_schema.metadata_locks或者sys.schema_table_lock_waits里找，别在行锁视图里空转。

所以我现在排查慢查询的顺序是：先看执行计划，确认SQL本身没毛病。再看慢日志的时间点，那个时刻实例整体什么状态。有没有锁等待、IO打满、大事务。SQL、数据、环境三样一起看，才能定位真凶。

避坑清单

上面几个坑，每个都是拿真金白银换的。收尾前，把最容易再犯的三件事唠叨一遍。

第一件，看执行计划别只盯着key有没有值。type=index名字里带index，其实是把整棵索引树从头扫到尾，跟全表扫是难兄难弟。我第一次看明白这点，是在白折腾了一整天之后，不冤。

第二件，加索引前先做减法。我在那张订单表上吃过亏，当时光顾着加，忘了翻翻老索引还在不在。sys.schema_unused_indexes一查，好几个建了就没被碰过。删掉之后，写入才缓过劲来。

第三件，慢查询别急着给SQL定罪。单跑5ms、线上天天上榜的SQL，我最后查出来，问题大多不在SQL本身，是大事务攥着锁，是批处理把IO打满，SQL常常只是背锅的。

写在最后

说了这么多，其实就一个意思：收到慢查询，第一反应别是加索引。先让执行计划说话，再看那段时间实例的状态，最后才轮到动索引。动的时候，先想能不能删，再想能不能加。

索引不是银弹。你手里的执行计划，才是真相。

你最近处理过最“反直觉”的一次慢查询是什么？是加索引加出来的，还是根子根本不在SQL上？评论区聊聊。

我是数据库小学妹，帮你少走弯路少踩坑，咱们下篇见👋