MySQL 索引基础：一条 5 秒慢查询，怎么从全表扫描查到索引设计

列表页转圈五六秒，第一反应常常是页面渲染卡了；可只要打开一次 Network 面板，就能很快把锅从前端渲染层移开。页面本身没慢，真正拖时间的是接口，而接口再往下看，往往就是数据库查询在顶着。

这次订单列表的问题就是这样：偶发 5 秒多的响应时间，EXPLAIN 一跑，直接暴露出全表扫描。到这一步，索引就不再是课本里那个抽象的 B+ 树知识点，而是决定这条 SQL 到底扫几百行还是几十万行的真实分水岭。

后面会沿着一次慢查询排查走，把 EXPLAIN、B+ 树、回表、覆盖索引、最左前缀和深分页这些问题放回同一个现场里讲。

第一步：EXPLAIN，让 SQL 自己交代

后端同事先从慢查询日志里捞出那条 SQL，发给 DBA 在测试库单独跑，果然 5 秒开外。然后 DBA 在语句前面加了一个词：

复制
1explain select * from orders where user_phone = '13800138000' and status = 1;

EXPLAIN 不执行查询，只输出优化器打算怎么执行。DBA 教我重点看几列：type（ref/range 通常不错，ALL 是全表扫描要警惕），key（实际用到的索引，为 NULL 说明没走索引），rows（预估扫描行数，越小越好），Extra（额外动作，后面会反复出现）。

我们这条的结果：type: ALL，key: NULL，rows: 六十多万。人话翻译：这张六十多万行的订单表，每查一次都从头到尾扫一遍。排查慢查询的第一动作永远是 EXPLAIN，比凭感觉猜靠谱一百倍——这是我当天学到的第一件事。

DBA 给 user_phone 补了个索引，同一条查询从 5 秒降到 20 毫秒。就这么一下。我盯着那个 20ms 愣了一会儿：很多看起来像"前端慢"的问题，根子在数据库怎么取数据。

索引到底是什么

DBA 看我一脸新奇，干脆从头讲起。索引可以理解为数据库为了快速查找数据建立的目录。

没有索引时，查询只能一行一行扫：

复制
1select * from users where email = '[email protected]';

如果 email 上有索引，数据库就能直接定位到对应记录。

这个类比可以再具体一点：没索引就像在一本没有目录的书里找某个词，只能从第一页翻到最后一页；有索引就像查字典，先按拼音定位到大致位置，再翻几页就到了。区别在于全表扫描的耗时随数据量线性增长，几百行无所谓，几十万行就是灾难；而走索引（B+ 树）的查找次数大致随数据量对数增长，百万级和千万级的差距并不大。这就解释了我的一个疑惑：为什么这个接口以前不慢？因为上线头几个月订单表只有几千行，加不加索引没感觉；一旦上量，差距就是几个数量级。三月初运营说的"有点慢"，就是数据量爬坡时的早期信号，可惜我没接住。

后端同事补了一句区分度的提醒：索引的有效性跟字段的区分度强相关。给一个只有"男/女"两个值的性别字段建索引基本没意义，因为一查就命中半张表，优化器干脆不走索引直接全表扫。区分度高的字段（比如用户 ID、邮箱、手机号）才适合建索引。想量化的话可以 show index from 表名 看 Cardinality（基数）那一列，它是不重复值数量的估计，越接近总行数，区分度越高。

为什么偏偏是 B+ 树

我问了个书上看过但一直没吃透的问题：为什么索引非得是 B+ 树，哈希表查找不是 O(1) 更快吗？

DBA 的解释比书上的立体：哈希索引等值查询确实快，但它不保留顺序，没法做 between、>、order by 这类操作——而订单列表恰恰全是"按时间倒序、按状态过滤"这种查询，遇到范围查询哈希就退化成全表扫了。普通二叉树呢，最坏情况下（比如自增主键顺序插入）会退化成一条链表，树高失控。

B+ 树是多叉的，一个节点能放很多键，所以即使几千万行数据，树高通常也就三四层——意味着一次查询最多三四次磁盘 IO 就能定位。而且 B+ 树只在叶子节点存数据，叶子之间还用链表串起来，范围查询时顺着叶子链表扫一段就行，特别顺。这几个特性凑一起，才让它成了关系型数据库索引的默认选择。

不需要把树结构画得很复杂，但要知道：索引不是哈希表那么简单，它既支持等值查询，也支持范围查询和排序优化。这段我年初在书上啃过一遍，当时觉得懂了，被 DBA 当着真表真数据讲一遍才发现之前的"懂"有多虚——纸上得来终觉浅，这回是真在生产库上见着了。

相关的一个设计决策：主键为什么都用自增

聊到"二叉树被顺序插入退化"时，后端同事顺势讲了个相关的设计决策：为什么他们所有表的主键都是自增 ID，而不是订单号、UUID 这种业务字段。

因为 InnoDB 的数据就存在主键的 B+ 树上，按主键顺序排列。自增主键每次都往树的最右边追加，页写满了就开新页，整整齐齐；换成 UUID 这种随机值，每次插入的位置都不可预测，经常要往已经写满的页中间硬塞——页就得分裂成两页、挪一半数据过去，写入性能被拖慢不说，页的空间利用率也七零八落。主键要小、要有序、跟业务解耦，订单号那种业务含义重的字段，做成普通唯一索引就好。这个知识点跟前端八竿子打不着，但听懂那一刻还挺爽的——原来"建表规范"里每条死规矩背后都有一棵树在受苦。

主键索引、二级索引和回表

接着是当天让我开眼界的概念：一次查询可能走两棵树。

InnoDB 表数据按主键索引组织，主键索引叶子节点存整行数据。普通索引（二级索引）叶子节点存的是主键值，查到主键后再拿主键去主键索引树上查整行，这个过程叫回表。

例如：

复制
1select name from users where email = '[email protected]';

如果 email 是普通索引，查到主键后还要回表拿 name。

回表的代价容易被低估。单条查询多一次查找无所谓，但如果是范围查询命中了上万行，每行都回一次表，IO 就被放大成了上万次随机读，慢得很明显。我们当天就撞见了活例子：订单表另一条按时间段查询的 SQL，EXPLAIN 看 type 是 range，看着挺正常，但就是慢——命中行太多、回表太频繁。DBA 说优化器其实也知道回表贵，命中行数太多时它甚至会放弃索引直接全表扫，因为顺序读整表反而比海量随机回表更快。"加了索引却没走"，很多时候不是索引坏了，是优化器算过账了。

覆盖索引：不回表的快感

那怎么治回表？如果查询字段都在索引里，就不需要回表。

复制
1select id, email from users where email = '[email protected]';

如果索引包含 email 和 id，就可能直接从索引拿到结果。这叫覆盖索引。InnoDB 里二级索引的叶子节点本来就存了主键值，所以查 id 天然不用回表。如果业务上经常要根据 email 同时查出 name，也可以专门建一个把 name 带上的联合索引 (email, name)，让这个查询完全走索引、不回表。EXPLAIN 时 Extra 列出现 Using index，就说明命中了覆盖索引。

DBA 现场演示了一把：订单页头部有个统计接口，select count(*) 配几个条件字段，原来回表回得很惨。他把涉及的几个字段做成一个联合索引让查询被覆盖之后，Extra 里出现 Using index，快了一个数量级。代价是这个索引占了额外空间，而且字段越多索引越大，所以覆盖索引也不能无脑往里塞字段，只覆盖高频查询真正需要的列。

后端同事趁机敲打了一下写接口的习惯：select * 是覆盖索引的天敌。字段列表里但凡有一个不在索引里，整个查询就得回表，前面的设计全白搭。而我们这条订单 SQL 开头赫然就是 select *——列表页面其实只展示七八个字段。后端把 * 改成明确的字段列表，前端这边我也把接口文档里"返回全部字段"的约定改掉了。这是我当天第一次意识到，前端随口一句"都返回吧，省得以后加字段再改"，在数据库那头是有账单的。

联合索引和最左前缀：字段顺序是门学问

订单表的主力查询是"按状态 + 时间段"，DBA 建的是联合索引：

复制
1create index idx_order_status_time on orders(status, created_at);

能支持：

复制
1where status = 1

也能支持：

复制
1where status = 1 and created_at > '2019-01-01'

但如果只查：

复制
1where created_at > '2019-01-01'

就可能用不上这个联合索引。这就是最左前缀原则。可以把联合索引 (status, created_at) 想象成先按 status 排序、status 相同再按 created_at 排序的一本电话簿。你知道姓（status）能直接定位，但只知道名（created_at）不知道姓，就只能整本翻——索引帮不上忙。

我问：那反过来建 (created_at, status) 行不行？DBA 说这正是新手最容易建反的地方——要把等值查询的字段放前面、范围查询的字段放后面。因为一旦在某个字段上用了范围条件（>、<、between、like 'x%'），它后面的字段就用不上索引了。查询大多是 where status = 1 and created_at > ? 的话，建 (status, created_at) 就对；建成 (created_at, status)，created_at 是范围条件，status 那一段就白搭。他说这个顺序问题他自己也吃过亏，索引建了查询还是慢，调换一下字段顺序立刻就好。

联合索引还有个跟排序有关的加成：这条 (status, created_at) 建好之后，where status = 1 order by created_at desc 也不用额外排序了，因为索引本身就是按这个顺序存的。反过来如果 EXPLAIN 的 Extra 里出现 Using filesort，说明 MySQL 在内存（甚至磁盘）里额外做了一次排序——订单列表那条 SQL 之前就挂着一个 Using filesort，联合索引建对之后它就消失了。列表接口的 order by 字段值不值得进联合索引，看一眼 filesort 就知道。

后端同事又补了个 Extra 里的新朋友：Using index condition，索引条件下推（ICP），MySQL 5.6 之后就有。简单说，联合索引里那些没法用来定位、但字段本身在索引里的条件（比如 (status, created_at) 上查 where status = 1 and created_at like '2019-03%' 之外再挂个能用索引字段判断的条件），存储引擎会先在索引层面把不满足的行过滤掉，再回表——回表次数少了，自然快。这个不需要你做什么，优化器自动来，但在 Extra 里看到它时知道是好事就行。

索引失效的常见姿势

DBA 顺手把项目里见过的"有索引但没走"的写法给我过了一遍。

对字段使用函数：

复制
1where date(created_at) = '2019-01-01'

索引存的是 created_at 的原始值，套一层函数后优化器就没法用树定位了。等价改写成范围条件就能救回来：where created_at >= '2019-01-01' and created_at < '2019-01-02'。

隐式类型转换：

复制
1where phone = 13800138000

如果 phone 是字符串，可能影响索引。这条特别阴——SQL 不报错、结果也对，就是悄悄全表扫。相当于 MySQL 替你对每行的 phone 做了一次转数字的函数调用，和上一条本质相同。

模糊查询前缀 %：

复制
1where name like '%tom'

这类查询通常很难利用普通索引。因为 B+ 树是按字段值从左往右排序的，like 'tom%' 这种前缀确定的还能用索引（相当于范围查找），但 %tom 连开头都不确定，索引没法定位，只能全表扫。所以做后缀模糊搜索、全文搜索这类需求，到一定量级就别硬用 like 了，该上 Elasticsearch 之类的搜索引擎——DBA 说他们已经在给商品搜索调研这条路。

or 也容易让索引失效。where a = 1 or b = 2，如果只有 a 有索引、b 没有，优化器可能放弃索引走全表扫。!=、not in、is not null 这些也经常用不上索引。这些规则不用死记，养成习惯：写完慢的查询就 EXPLAIN 一下，看 key 是不是 NULL、type 是不是 ALL，比背规则实在。

另外长字符串字段（比如商品标题、地址）直接建全字段索引又大又慢，可以建前缀索引：

复制
1create index idx_title on products(title(20));

只索引前 20 个字符，空间小很多。代价是前缀索引没法当覆盖索引用（索引里不是完整值），前缀长度也要按区分度挑——取一个"前 N 个字符的不重复率接近全字段"的 N。

深分页：前端也有份的坑

最后揪出来的一个问题跟前端直接相关。运营说"翻着翻着变慢"，不是错觉——列表接口的分页是 limit 100000, 20 这种写法，MySQL 实际上要先扫描并丢弃前面十万行，再取 20 行，越往后翻越慢。

优化办法是记住上一页最后一条的主键，下一页用 where id > 上次的id limit 20 代替大 offset，让索引直接定位起点，这就是常说的"游标分页"。这个改造需要前端配合——翻页参数从页码换成游标，我当场认领了前端这半边。前端做无限滚动列表时，配合这种翻页方式体验会好很多；而"跳到第 N 页"这种交互在大数据量下天然和游标分页冲突，产品设计时就该权衡。回来的路上我已经在想怎么跟产品聊掉那个"页码跳转"输入框了。

索引不是越多越好

我下意识问了句："那把常查的字段全加上索引不就完了？"两个人同时摇头。索引会提升查询，但会增加写入成本。

插入、更新、删除数据时，索引也要维护。每多一个索引，就多一棵 B+ 树：插入一行数据，所有相关索引树都得跟着插入并可能触发节点分裂；更新被索引的字段，对应索引也要改。后端同事给我看过一张反面教材表，为了应付各种零散查询建了十几个索引，写入性能被拖得很难看，而且其中好几个索引线上根本没查询用到——纯属负担。

所以索引要围绕真实查询场景设计。他们的做法是开着慢查询日志（slow_query_log，阈值 long_query_time 设成 1 秒），定期看哪些查询真正慢、真正高频，针对性补索引，而不是一上来就给每个字段都加。MySQL 5.7 之后还能查 sys.schema_unused_indexes 看哪些索引从来没被用过，定期清理掉这些僵尸索引，写入会轻快不少。

回家作业：把 EXPLAIN 自己玩了一遍

周末我在自己电脑上装了个 MySQL 5.7，造了张一百万行的测试表，把白天见过的每种情况都亲手复现了一遍。最大的收获是把 type 那一列的档位排出了顺序，从好到差大致是：

复制
1system > const > eq_ref > ref > range > index > ALL

不用背定义，配着例子看很快就有体感：

复制
1explain select * from t where id = 100;              -- const，主键等值，一步到位
2explain select * from t where email = '[email protected]';     -- ref，普通索引等值
3explain select * from t where id > 100 and id < 200; -- range，索引范围扫描
4explain select email from t;                          -- index，扫整棵二级索引树
5explain select * from t where nickname = 'tom';       -- ALL，无索引，全表扫描

日常够用的判断标准：**线上查询看到 range 以上基本安心，看到 index 要皱眉（虽然走了索引，其实是把整棵索引树扫了一遍），看到 ALL 就该干活了。**配合 rows 一起看更准——type 再好看，rows 预估几十万也快不起来。

另一个自己踩出来的发现：给测试表刚灌完数据时，EXPLAIN 的 rows 估得离谱，查了才知道统计信息不是实时的，analyze table t 一下就准了。优化器所有的"算账"都基于这份统计信息，它不准的时候，优化器选错索引也就不奇怪了——后端同事说生产上偶发的"同一条 SQL 忽快忽慢"，有一类根因就是统计信息漂了。

唯一索引还是普通索引：一个我没想到有讲究的选择

造表时我顺手把商品编码建成了唯一索引，心想反正业务上不重复，还能白得一个约束。周一跟后端同事闲聊提起，他说这个选择其实有讲究，不是"能唯一就唯一"。

查询性能上两者差别微乎其微：唯一索引找到第一条就能停，普通索引要多看一眼下一条确认没有重复值——就多这一眼，忽略不计。差别在写入：普通索引的插入可以先记在内存的一块缓冲里（change buffer），攒着批量落盘；唯一索引不行，因为它插入前必须先把数据页读出来检查有没有冲突，缓冲机制用不上。写多读少的大表，普通索引配合业务层保证唯一，写入吞吐会好看不少；数据正确性要求高、靠数据库兜底防重的，才用唯一索引。

对我这个前端来说，这段的价值不在结论本身，而在它展示的思维方式：数据库的每个选择背后都是读写两头的权衡，跟我们在前端权衡"预渲染还是运行时渲染"是同一种思考。基础知识学到后面，各领域的道理会开始互相串门。

一下午的收获

订单列表接口当天就恢复到几十毫秒，投诉平息了。游标分页的前端改造我排进了下个迭代。但对我来说更值钱的是另一件事：我第一次真正看清了"接口慢"这三个字后面的完整链条——SQL 怎么被优化器执行、索引树长什么样、回表和排序的代价在哪、前端随手要的"全字段"和"跳页"在数据库那头值多少钱。

前端不一定要会调 SQL，但懂一点索引，和后端讨论接口性能时就不再是"你们接口好慢"和"你们页面好卡"的互相甩锅，而是能一起看着 EXPLAIN 说话。B+ 树、联合索引、最左前缀、回表、索引失效——这些概念每一个都对应一条真实的慢查询。**书上的 B+ 树是知识，生产库里那个从 5 秒到 20 毫秒的瞬间，才让它变成了我的知识。**年初的补基础清单上，"数据库"这一项，我觉得可以认真地打个勾了。下次运营再说"有点慢"，我不会再让它在待办里躺一个月。