企业官网建设流程全解析

MySQL索引原理图文详解，这个标题我盯着看了一会儿。有人拿它当面试八股，有人把它当成慢查询优化的救命稻草，但真正能把索引讲明白的人不多。索引这个词在MySQL里实在太常被提起，建表时要加索引，SQL优化时要看索引，甚至Navicat里点几下也能加索引，可一旦问到“它为什么快”“它什么时候会失效”，很多同学就开始含糊了。

这篇文章我不想从概念定义开始，而是想站在一个普通后端开发、运维甚至面试者的角度，把MySQL索引原理从底层数据结构到执行计划，再到底层设计细节完整串一遍。无论你是刚入行的小白，还是写了几年SQL的老手，看完之后都应该能回答“一个没有索引的查询走到哪一步”“B+树到底存了什么”“为什么明明建了索引还是不生效”这几个问题。我自己做性能调优这些年，多少慢查询都是栽在索引失效的细节上，下面的内容基本是一个一个坑踩出来的，希望对你有用。

1. 索引为什么快：从一次查询说起

1.1 没有索引时MySQL在做什么

先来模拟一个最普通的场景：有一张用户表，里面有一百万行数据，我只想查name = '张三'的那条记录。

在没有索引的情况下，InnoDB存储引擎会怎么做？别想得太复杂，它就是老老实实把这张表的数据从头到尾读一遍，一行一行比对name字段。这就是我们常说的全表扫描。你可以把它理解成翻一本没有目录的新华字典，我想找“猫”字，没有任何捷径，只能从第一页开始一页一页翻到最后一页。运气好，“猫”在第一页，运气不好，“猫”在最后一页，那就得整个人工遍历完一整本字典。

全表扫描的时间复杂度是 O(n)，数据量越大，查询越慢。这就是为什么当你SELECT * FROM user WHERE name='张三'的时候，如果表里只有几百行，你可能完全感觉不到慢；但如果表里有一千万行，这个查询可能就会跑到几百毫秒甚至几秒。很多线上故障就是这么来的。更麻烦的是，你翻完一遍之后，如果下次再查“李四”，还得再从头翻一遍，没有任何缓存可以利用。

1.2 索引让查询路径发生了哪些变化

加索引之后，情况完全不同。索引会在存储引擎之上单独建立一套数据结构，这套结构把字段值和对应行的位置做了映射，MySQL就可以先在这套结构里做精确定位或范围搜索，找到目标记录之后再回头去拿整行数据。

同样是查name = '张三'，加了索引之后MySQL不再需要扫描所有行，它会直接走进索引树里进行快速查找。这个查找过程的时间复杂度可以近似看作 O(log n)，对于一百万行数据来说，大概只需要几十次磁盘IO甚至更少。你想想，一万次IO和几十次IO的差别，那就是天壤之别。

这里你可能会问：既然索引这么好，为什么不给每一列都建索引？因为索引本质上是拿空间换时间。每个索引都是一棵独立的B+树，它需要占用额外的磁盘空间，而且每次插入、修改、删除数据，索引结构也要同步更新。如果你的表写入频繁而查询很少，那你建一堆索引只会拖慢写入速度。这就是索引设计的第一个原则：只给必要的查询建索引。

2. 索引的底层数据结构

2.1 为什么选B+树而不是哈希表

MySQL的索引默认使用B+树，但很多人没想过一个更基础的问题：为什么不用哈希表？

哈希表确实在等值查询场景下更快，理论时间复杂度是 O(1)，也就是一次哈希计算就能定位到数据。但现实中的查询往往没有那么简单，我们经常要查范围，比如WHERE age BETWEEN 20 AND 30，或者做排序ORDER BY age。哈希表的底层是散列表，数据是无序的，遇到范围查询就只能一个个遍历，效率极低。B+树则不一样，叶子节点上的数据天然有序，而且叶子节点之间通过链表串联起来，查一个范围就相当于在一个有序链表上滑动，代价小得多。

那为什么不用普通的二叉树或者二叉搜索树呢？因为树高的问题。二叉搜索树的查找次数取决于树的深度，对一百万行数据来说，理想情况下深度大约是20层，这看起来还能接受。但如果MySQL要去读每一层的节点，每一层都可能是一次磁盘IO，20次磁盘IO已经非常慢了。而且二叉树每个节点只存一个键值，节点的利用率太低，数据量一大树就会变得很高。B+树则不同，它的每个节点可以存储很多个键值和指针，一次IO可以把大量索引项加载进来，树的高度通常只有三层左右，几百万、几千万的数据量都能压在三层树里。

这里有一张简化的示意图，你可以感受一下B+树的结构：

[ 58 | 100 ] / | \ [ 1, 20, 34 ] [ 60, 77, 90 ] [ 110, 125, 150 ] ... ... ... ...

根节点只存少量键值用于路由，下面的内部节点继续做指向，数据全部集中在叶子节点，并且叶子节点之间用链表连起来。你要的效果就是：三层以内的访问，一次定位。

2.2 B+树是如何组织数据的

InnoDB中的数据是按页存储的，每一页默认大小是16KB。B+树的每一个节点，在物理上就对应一个页。根节点、内部节点和叶子节点的大小虽然都是16KB，但里面存的内容完全不一样。

内部节点和根节点只存键值和指向子节点的指针，它们不存实际的数据行。这样设计是有讲究的：一个16KB的页如果全部用来存键值和指针，大约可以存一千个以上的索引项，那么一个三层的B+树就能够管理十亿级别的数据量。如果内部节点也存整行数据，树高会急剧增加，IO次数直接翻倍，索引的性能优势就没了。

叶子节点是真正存数据的地方。对于主键索引来说，叶子节点存放的就是完整的用户数据行。我在建表的时候通常指定一个主键id，InnoDB就会自动为主键建立聚簇索引，这个聚簇索引的叶子节点保存了整行的所有字段。如果表没有主键，InnoDB会自己找一列非空的唯一字段作为主键，再找不到就生成一个隐藏的主键。

B+树的叶子节点还有一个特别重要的特性：它们是按索引键值排序的，而且互相之间通过双向链表连接。所以你执行一条WHERE age BETWEEN 20 AND 30的语句，MySQL一旦定位到最小的20岁用户，就可以顺着叶子节点的链表一直往后扫，直到超过30岁为止。这种设计让范围查询和排序查询都变得非常流畅。

2.3 主键索引与二级索引的差异

很多新手会把“索引”当成一个抽象概念，但实际使用中，你至少要分清聚簇索引和二级索引。

聚簇索引就是主键索引，它的叶子节点直接包含整行数据。每条记录只会属于一个聚簇索引，所以你在建表时设置主键，InnoDB就会根据主键来组织整张表的物理存储顺序。

二级索引，也叫辅助索引或普通索引，是你在其他列上建立的索引。二级索引的叶子节点并不包含整行数据，它只包含两样东西：当前索引的键值，以及主键值。比如你给name建了一个普通索引，叶子节点里存的是name和id。当你用name去查数据时，MySQL会先在二级索引里找到对应的name和id，然后再拿着这个id到主键索引里找完整的行。这个过程就是常说的“回表”。

为什么二级索引不直接保存数据行的物理地址？因为B+树在插入、删除、分裂时，数据行的物理位置会频繁变化。如果二级索引里保存的是物理地址，每移动一次数据就要更新一次二级索引，维护成本太高。而保存主键值就稳定得多，无论数据行挪到哪里，主键值都不会变。理解了这一点，你也就理解了为什么二级索引查询往往需要“两次搜索”：先在二级索引中找到主键，再回聚簇索引拿数据。

3. 从执行计划看索引的真实行为

3.1 用EXPLAIN读懂一次索引命中的过程

说再多理论，都不如直接在SQL语句前面加一个EXPLAIN看得清楚。这是我在排查SQL性能时第一个使用的工具。

我先建一个简单的用户表：

CREATE TABLE `user` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `age` int NOT NULL, `city` varchar(50) NOT NULL, PRIMARY KEY (`id`), KEY `idx_name_age` (`name`, `age`) ) ENGINE=InnoDB;

然后执行：

EXPLAIN SELECT id, name, age FROM user WHERE name = '张三' AND age = 18;

执行计划中几个重要字段你应该重点关注。

第一是type，它表示MySQL在表里找到目标行的访问方式。常见值的排序大概是这样：system>const>eq_ref>ref>range>index>ALL。如果你看到ALL，那就是全表扫描，通常意味着索引没有生效；看到ref或range，说明走了索引但还有优化空间；看到const或eq_ref，说明查询条件非常精准。

第二是key，它显示MySQL实际选择使用的索引名称。如果这个字段是NULL，那说明优化器没有选到任何索引，这里就要警惕了。第三是rows，它是个估算值，表示MySQL认为需要扫描多少行才能找到结果。如果rows非常大，就算key不为空，也要想一下是不是查询条件太宽了。

还有一条比较实用的经验：不要只在开发环境用EXPLAIN，生产环境的慢查询日志里找出来的SQL，全部都要拿EXPLAIN过一遍。很多时候开发环境数据量太小，索引失效也看不出来，生产环境一千万行数据立刻现出原形。

3.2 回表、覆盖索引与索引下推

理解了EXPLAIN，就要开始关注回表和覆盖索引了。直接改一下上面的查询：

EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 18;

这个查询和上一个查询的唯一区别是：把查询字段从id, name, age改成了*。执行计划可能会有变化，重点是看Extra列。如果Extra列出现Using index，说明这个查询完全在二级索引中就完成了，不需要回表，这是最理想的情况。如果Extra列没有这个提示，说明MySQL在二级索引中找到目标记录后，还要拿着主键回到聚簇索引里去读取其他字段，这就叫回表。

回表不一定是坏事，但如果每条命中记录都要回表，而命中结果集又很大，那性能就会直线下降。比如你查name匹配了十万行，那就要回表十万次，每次回表都是一次随机IO。

覆盖索引的意思是说，查询需要的所有字段都在同一个二级索引叶子节点里，MySQL根本不需要回表。举个例子，你的二级索引是idx_name_age(name, age)，你只查name,age,id，那么二级索引本身就包含这三个字段，直接返回即可。所以在写项目代码时，我会尽量不写毫无节制的SELECT *，而是明确写出业务需要的字段，这样才有机会做到覆盖索引。

还有一个容易被忽略的特性叫索引下推，英文缩写ICP。MySQL 5.6版本之后开始支持。当你有联合索引(name, age)，查询条件里name用到了索引，age过滤条件就会被“下推”到存储引擎层，在读取二级索引时就过滤掉不符合age的叶子节点，减少回表次数。你会在Extra列看到Using index condition，这其实是好事。它说明MySQL已经尽量在索引层就完成工作了，剩下的回表次数已经压缩到最小。

4. 索引失效：我踩过的那些坑

4.1 最左前缀法则到底是怎么失效的

联合索引是MySQL索引优化里的重头戏，也是最容易踩坑的地方。我有一个联合索引(name, age, city)，如果你完全按照这个顺序写条件：

WHERE name = '张三' AND age = 18 AND city = '北京'

那这个联合索引可以完整命中的所有三层，效果最好。但如果你写成：

WHERE name = '张三' AND city = '北京'

中间跳过了age，那索引就只用到name这一层，city虽然也在联合索引里，但MySQL已经没法继续用它过滤了。这就是所谓的最左前缀法则。

很多人的误区是以为“只要查询条件里有索引的第一列，整个索引就一定能被用上”。这句话要打一个折扣：第一列name确实能触发索引，但不代表后面的age和city都能被用上。只有在查询条件中从联合索引最左侧开始连续命中，索引才能发挥最大效果。

还有几种情况会让最左前缀直接失效。比如LIKE查询时，只有LIKE '张%'这种前缀匹配能走索引，LIKE '%张'因为通配符在前面，索引无从途用，只能全表扫描。又比如你对索引列做了运算或函数操作，最左前缀也可能失效。这类问题在改造老项目的时候特别常见，线上SQL已经写死了，不能轻易改，我通常建议优先调整索引顺序，其次才考虑改写SQL。

4.2 隐式转换、函数操作与排序场景

下面这几个问题，是参加“MySQL面试题”时经常翻车的点，也是我实际排查线上故障时见过最多的类型。

第一个是隐式类型转换。表里有一个字段mobile varchar(20)，它本身建了索引，但查询时如果写成：

SELECT * FROM user WHERE mobile = 13800138000

注意，mobile是varchar，条件却是整数型，MySQL会尝试把字段列转换为数值类型，这一转，索引就废了。同类的问题还有日期字段传年月日字符串、字符编码不一致导致连接时转换等。判断起来也简单：执行EXPLAIN看key是否是NULL，如果是，再去检查字段类型和条件类型是否一致。

第二个是函数操作。索引列一旦套上了函数，优化器就没法直接使用B+树上的原始值了。比如：

SELECT * FROM user WHERE DATE(create_time) = '2024-01-01'

虽然create_time上有索引，但DATE()函数调用之后，MySQL无法按原始的create_time值去索引树中搜索，只能先取出所有create_time再做计算，最后过滤。正确处理一般是改成范围查询：WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'。这样索引就能被正常使用。

第三个是排序场景。当你在ORDER BY里用了索引字段，MySQL可以直接从B+树叶子节点的链表顺序读取，这叫做内部有序扫描。但如果你排序的字段不符合索引最左顺序，或者排序方向不一致，MySQL就只好把所有结果集找出来，再进行一次额外的排序，这时EXPLAIN的Extra列会出现Using filesort。Using filesort不是说出现了磁盘临时文件，而是指MySQL没有利用索引天然的有序性，必须自己重新排序，它在数据量大的时候代价非常高。

我印象比较深的一次排查，是线上有个订单列表接口，SQL里同时有WHERE条件和ORDER BY条件，单独看每一个字段都有索引，但优化器就是选了全表扫描。最后发现是因为排序字段和WHERE条件没法共用同一个索引，MySQL需要另起炉灶做filesort，代价比全表扫描还高。解决方式很直接：把WHERE和ORDER BY涉及的字段重新设计成一个联合索引，让这个索引同时兼顾过滤和排序，查询瞬间就快了十倍。

5. 索引设计实战：这样建索引才合理

5.1 区分度、基数与选择性

很多人只关心“怎么加索引”，却忽略了“这一列到底适不适合加索引”。判断标准里面最重要的一个指标是区分度。

区分度，也叫选择性，计算公式是：字段的去重值数量除以总行数。举个例子，一个性别字段只有“男”和“女”两个值，那一百万条数据里区分度只有两万分之一，非常低。如果你在这样一个字段上建普通索引，MySQL扫描索引后可能命中五十万行，然后再回表五十万次，这比直接全表扫描还傻。所以像性别、状态、是否删除这类枚举值极少的字段，我一般不建议单独建索引。

反过来，像name、mobile、email这类字段，值基本不重复，区分度极高，索引筛选效率就非常好。在业务里，我会把高频查询条件里区分度高的字段放最前面，这样B+树每走一层就能快速排除大量数据。

如果字段本身很长，比如存文章摘要的text字段，直接建普通索引会浪费大量空间，而且索引页能容纳的索引项变少，B+树会变高。这种情况下可以考虑前缀索引，比如KEY idx_content(content(20))，只对字段前20个字符建立索引。前缀索引同样能加速等值查询和模糊前缀匹配，但它有一个短板：无法用于排序，也无法做到覆盖索引。同时，前缀选择过短还可能让区分度不足，需要实测确认长度。

5.2 联合索引设计要点与字段顺序

聊完了单列，再聊多列。联合索引的字段顺序是整个索引设计的核心。我在实际项目里通常遵循两条经验。

第一条，等值条件的字段排在前面，范围条件的字段排在后面。比如业务查询经常是WHERE age = 18 AND create_time BETWEEN '2024-01-01' AND '2024-01-31'，那联合索引建议建(age, create_time)。因为等值条件可以精确定位到B+树的某一小段，范围条件在等值条件确定后顺着叶子链表接着扫就行。如果反过来建(create_time, age)，MySQL虽然也能用索引，但在第一步的范围扫描时可能会扫一大片，再对age做过滤，效率会差很多。

第二条，把区分度高的字段放在前面。如果联合索引是(name, status)，而status的区分度非常低，那索引失效的概率会更大。我们把name放前面，B+树第一层就能按高区分度迅速收敛，然后status在小区间里做过滤，整体效率最高。

另外，建索引别忘了考虑覆盖索引的业务收益。如果一个查询高频出现，而且需要的字段不多，我会有意识把这些字段组合成一个联合索引，让查询在二级索引内直接完成，避免回表。但这个度要把握好，不是每一条SQL都值得给它定制一个索引。索引数量增加，写入、更新成本都会上升，磁盘空间占用也会增加。我的底线是：单个表上的索引数尽量控制在五个以内，除非业务有非常强烈的查询需求。

创建索引的常用语法很简单：

ALTER TABLE user ADD INDEX idx_age_name (age, name); CREATE INDEX idx_age_name ON user (age, name); DROP INDEX idx_age_name ON user;

还有一种少加索引的写法，是用ALTER TABLE给主键加约束，这个我就不展开了。我想强调的是，加索引之前，先在测试环境导入接近生产的数据量，用EXPLAIN验证一遍，再决定是否上线。不要上了生产才发现索引没用，白折腾一次变更。

6. 常见问题速查与排查技巧

6.1 索引相关面试题速答

这里把一些常见的MySQL索引面试题整理成一张速查表，面试前翻一翻很有用。

这表虽然简单，但你把这些内容用口语讲给面试官听，比你背出一整段教科书定义要强得多。面试官更在意的是你能不能把逻辑串起来。

6.2 排查索引问题的几条经验

最后分享几条排障经验。很多人一遇到SQL慢就急着加索引，其实正确的排查顺序应该是：先开慢查询日志，把慢SQL捞出来，然后逐条用EXPLAIN分析，再决定改索引还是改SQL。

我常用的工具有mysqldumpslow和performance_schema中的表，可以把高频慢查询按执行次数排序，优先处理影响最大的那几条。拿到SQL之后，第一步看EXPLAIN的type字段，如果是ALL，先别急着加索引，看看是不是字段类型不匹配、函数运算导致失效。如果确认是查询条件本身没有可用的索引，这时候才去建索引。

有一次我排查一个线上问题，EXPLAIN显示type = ref，key也命中了，Rows估算只有几百行，但接口还是慢。后来发现问题是每一条命中记录都要回表，而查询字段里有十几个大字段，每次回表都要读取完整数据页。解决方案不是加索引，而是把SQL里的SELECT *改成只查必要的字段，并且把查询字段调整成覆盖索引的一部分，性能立刻恢复正常。

还有一个很容易踩的坑：统计信息不准确。MySQL优化器选择索引是基于采样统计的，如果表数据频繁变更，统计信息没及时更新，优化器可能选错索引。这时候可以执行ANALYZE TABLE user;让MySQL重新统计。也有时候，优化器确实选了索引，但代价估算反而比全表扫描高，那就要考虑改写SQL，比如强制索引FORCE INDEX(idx_name_age)，但这种方式我一般只在紧急修复时使用，长期方案还是要理清表结构和查询逻辑。

我个人在实际操作中的体会是，MySQL索引原理不是靠背出来的，而是靠一次次执行计划堆出来的。你不要把B+树想成一个很玄的东西，它就是一本带目录和页脚的书，MySQL先翻目录找到页码，再翻到正文那一页读内容。你真正要盯住的只有三个点：走没走索引、走了多少行、有没有额外的排序和回表。这三点只要每一次查SQL都带着问题去看，时间长了你会发现自己对慢查询的判断力提升得非常快。最后再分享一个小技巧，每次建完索引，都用老SQL、新SQL两条语句对比跑一遍，记录执行时间，上线后也持续观察一周慢查询日志，确认效果稳定才算真正完事。MySQL索引这件事，慢工出细活，急不来。