企业官网建设流程全解析

前几章我们把MySQL的安装、建库建表、增删改查、事务机制都过了一遍，如果你一直跟着敲，应该已经能写出完整可用的SQL了。但我想大家心里都有个疙瘩：为什么一样的WHERE条件，同事的SQL跑得飞快，我的数据一多就卡成PPT？答案十有八九是索引。第八章我想认真聊透这个话题，从B+树的底层原理到联合索引的建法，再到那些很容易踩的失效场景和8.0的高级特性，一次性讲明白。

我不打算绕弯子，这一章内容信息量比较大，但每一节都值得你花时间慢慢读。学习顺序我建议这样走：先搞懂索引为什么快（底层结构），再知道有哪几种索引可用（类型选择），接着学会给真实业务建索引（联合索引实战），然后弄明白哪些SQL写法会浪费索引（失效排查），最后再看排序优化和8.0的新玩法。很多人在索引上栽跟头，就是把顺序搞反了——上来就背“最左前缀原则”，却不知道索引在磁盘上是怎么组织的，遇到问题自然只能瞎猜。

1. 索引值得在入门阶段就研究透

1.1 先感受一下没有索引有多痛

我见过太多业务代码，前期数据量小的时候一切正常，有一天表里累积了几十万行，某个常用查询突然从几十毫秒变成几秒钟。为什么会这样？因为没索引的查询是老老实实做全表扫描——从第一行数据读到最后一行，逐行匹配你的WHERE条件。数据量从1万涨到100万，匹配次数就涨100倍，性能当然跟着崩。

拿订单表举例：你要查WHERE user_id = 10086，表里有50万行，MySQL只能从第1行翻到第50万行，把每行的user_id都拿来做一次比较。听起来不算多，但如果同时还要排序、再连接几张表，响应时间就非常感人。

加了索引之后效果是立竿见影的。我在一个真实项目里测过：订单表30万行，不带索引跑一次按user_id过滤大概需要1.8秒，加一个普通索引后直接压到0.01秒左右。这个量级的差距，不是调几个参数能追回来的，就是从“要不要加索引”这个设计决策里来的。

1.2 这一章的定位和学习路线

从零起步学MySQL这个系列走到现在，前面章节都属于“会用”的层面：你会写INSERT、UPDATE、SELECT，会调事务隔离级别，知道锁的基本概念。但索引是第一个真正逼迫你理解数据库内部机制的知识点。它不只是一个语法问题——CREATE INDEX谁都会敲，难点在于判断“这个表该建哪些索引”“这个SQL为什么没用上索引”“线上表索引太多怎么清理”。

这一章适合已经有SQL基础、开始被性能问题困扰的读者。如果你还没入门，我建议先把系列前面几章补完再过来。索引是优化器的核心依据，连执行计划都看不懂就去加索引，大概率是瞎忙活。

2. B+树到底是怎么把查询变快的

2.1 索引的本质是数据结构，不是“标记”

很多初学者把索引理解成“给某个字段打个标记，查询时就知道去哪找了”，这个类比大方向没错，但严重低估了背后的设计。真实的情况是：索引是一种独立于数据之外的数据结构，MySQL里绝大多数索引是一棵B+树。

B+树长什么样？你可以想象成一棵多叉的倒挂树：最上层是根节点，中间可能还有若干层内部节点，最底层是一串叶子节点。把上面这些节点想成磁盘上的“页”（InnoDB默认每页16KB）。

关键区别在于：内部节点只存索引键和指向下一层节点的指针，叶子节点才存真正的数据——在InnoDB里，聚簇索引的叶子存的是完整数据行，二级索引的叶子存的是主键值。

查询的时候，MySQL从根节点开始，通过比较键值一层层往下走，最终定位到叶子节点。因为每一层只需要读一个页，所以IO次数约等于树的层高。这才是索引快的本质：把“全表遍历”变成了“沿着树索引查找”，复杂度从O(n)降到了O(log n)。

2.2 为什么偏偏是B+树而不是别的树

你可能听说过B树、二叉树、哈希索引，为什么InnoDB选B+树？核心原因有三个。

第一，树要矮。二叉树每个节点最多两个子节点，数据一多树就很高，查询时每层一次磁盘IO，树越高IO越多。B+树的每个节点能装几百上千个键，三层树就能支撑千万级数据量。我习惯做个粗略估算：一个16KB的页，假设内部节点每个键值对占12字节，大约能存下1300多个键；叶子节点一行数据按1KB算，大约16行。1300乘以1300再乘以16，结果接近2700万。这意味着千万级数据量的表，查询通常只需要两三次磁盘IO。

第二，B树内部节点也存数据，B+树不存。B树非叶子节点带了数据地址，能放下的指针数量就少，树就更高；B+树内部节点全部用来放键和指针，单页容量被利用到极致。

第三，叶子节点用链表串起来。这个设计对范围查询非常友好，比如WHERE id BETWEEN 100 AND 200，定位到起点后顺着链表往后读就行。B树的范围查询需要反复从根节点回溯，效率差很多。

2.3 聚簇索引和二级索引的关系

InnoDB里一个表必然有一个聚簇索引，通常就是主键索引。它的叶子节点存放整行记录，所以聚簇索引和数据实际上是“长在一起”的，这也意味着每张表最多只能有一个聚簇索引。

二级索引（也叫辅助索引、普通索引、非聚簇索引）就不一样了，它的叶子节点只放索引列的值和主键值。你用二级索引查询时，先找到主键，再拿着主键去聚簇索引里找完整数据行，这个动作叫回表。

回表会额外增加IO。所以后面我会反复强调覆盖索引的思路——查询里需要的所有列都恰好包含在二级索引里，那样就不需要回表，性能提升非常可观。

这个设计也解释了为什么我强烈不建议用UUID当主键：聚簇索引的物理顺序跟主键相关，UUID随机生成，新插入的数据可能落在任意位置，导致页分裂和碎片，写入性能明显下滑。自增主键则是顺序追加，新数据永远写在尾部，页分裂的概率小得多。

3. 索引类型怎么选：主键、唯一、普通、前缀、全文

3.1 主键索引和唯一索引的区别

很多人在面试里被问过这个问题，回答“主键不能为NULL，唯一索引也不能有重复”，这只是表面。我把两者的核心差异列个表，大家对照着看：

唯一索引允许多个NULL，这个点很多人搞错。原因是MySQL认为两个NULL不相等，所以UNIQUE约束只管非NULL值。如果一个业务字段需要“要么为空，要么全局唯一”，唯一索引是唯一靠谱的实现方式——纯靠应用层先SELECT再INSERT判断，并发场景下几乎必然出现重复写入。

我的习惯是：每张业务表都放一个自增id BIGINT作为主键，所有真正需要唯一性的业务字段，比如手机号、订单号、身份证号，单独建唯一索引。不要让主键承担过多的业务含义，业务是会长变化的。

3.2 普通索引、前缀索引和全文索引的实际选择

普通索引没约束，纯粹是为了加速查询，语法也最简单：CREATE INDEX idx_user_id ON orders(user_id)。

前缀索引是个容易被忽视的技巧。如果索引列是很长的VARCHAR（比如URL、邮箱、文章标题），全字段索引会占用大量空间，而索引页有限，能塞进去的键就少，树可能变高。前缀索引只取字段的前N个字符建索引，空间省很多。代价是区分度可能下降。

我建前缀索引前一定会先验证区分度，比如：

SELECT COUNT(DISTINCT email) / COUNT(*) FROM user; SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM user;

如果LEFT(email, 10)的区分度已经接近全字段，我就放心建idx_email(user(email(10)))。低于0.9就说明前缀太短，容易扫出太多伪记录。

全文索引主要用于大文本的模糊匹配，比如LIKE '%关键词%'这种需求。说实话，除非是简单站内搜索，否则真不如上Elasticsearch这类专门的搜索引擎。入门阶段知道InnoDB支持全文索引就可以了，日常业务优先用普通索引和联合索引。

3.3 单列索引和联合索引该怎么取舍

实际项目里，一个查询往往涉及多个字段，这就引出最关键的问题：是给每个字段单独建索引，还是建一个联合索引？很多新人喜欢一股脑给每个WHERE字段都建索引，结果索引建了一大堆，性能没提上去，反而让每次写入都背上沉重的更新负担。

我一般在下一章单独讲联合索引的建法，因为它太重要了，值得花一整节篇幅。

4. 联合索引：where a and b 到底怎么建才不浪费

4.1 单列索引相加不等于联合索引

先看一个最常见的查询：WHERE user_id = 123 AND status = 1。你会怎么建索引？选项A是分别建idx_user_id(user_id)和idx_status(status)两个单列索引，选项B是建一个联合索引idx_user_status(user_id, status)。

A方案的问题在于：两个独立的二级索引，查询时要么只能先用其中一个索引定位，拿到一批主键后再回表筛选另一个条件；要么MySQL做Index Merge，同时扫两个索引再取交集。不管哪种，都意味着两棵B+树都要遍历，然后大量主键来回折腾。B方案一个联合索引就搞定：先按user_id定位到一个小范围，再在这个范围内精确匹配status，一次索引遍历完成定位。

我遇到过很多次线上慢查询，最后排查下来就是把多个单列索引改成合理的联合索引，性能直接翻倍。单列索引解决“多个条件”问题，效率远不如一个精心设计的联合索引。

4.2 最左前缀原则：联合索引的底层逻辑

联合索引idx_user_status(user_id, status)的B+树，第一排序键是user_id，只有user_id相同的情况下才比较status。这个排序规则决定了它的一个核心约束——最左前缀原则。

意思是：WHERE user_id = 123这个查询可以用索引；WHERE user_id = 123 AND status = 1可以用索引；但WHERE status = 1（不带动前面的user_id）就没法走到索引了。因为B+树的排序是先按user_id排列的，只给status条件时，你根本不知道去哪一层的哪个分支里找。

同理，索引(a, b, c)本质上建立了三个索引：(a)、(a, b)、(a, b, c)。它覆盖了最左前缀的所有组合，这就是为什么一个联合索引能解决好几类查询。

4.3 字段顺序怎么定：等值在前，范围在后

联合索引字段的排列顺序，直接影响它能服务多少个查询。我常用的排序原则是：

• 把等值条件字段放在最前面，比如user_id = ?，用等值定位可以快速收窄扫描范围。
• 范围条件（>、<、BETWEEN）尽量放最后，因为范围条件后面的字段无法再继续使用索引的有序性。
• 区分度高的字段适当靠前，减少每个前缀值下需要扫描的行数。
• 最常用的条件组合优先设计，索引是为了真实业务服务，不是为了理论最优。

举一个WHERE a = 1 AND b > 100 AND c = 2的例子。如果你建的是(a, b, c)，MySQL先用a等于值定位，然后利用b的范围定位，到c这里索引已经“使不上劲”了，只能在扫描范围内逐行过滤c=2。但如果改成(a, c, b)，先a等值、再c等值精确定位，最后只在剩下的小范围里处理b的条件。效果差很多。

除此之外还有一个优化点：如果某列几乎全部等值匹配，却不经常出现在范围查询里，把它放前面往往是更好的选择，因为索引的“定位成本”更低。

4.4 实战：给订单查询建一组靠谱的索引

假设有一个订单表，核心查询是：

SELECT order_id, user_id, amount FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC;

第一步我会分析：必须等值定位的字段是user_id和status，需要排序的是create_time，查询结果列是order_id, user_id, amount。建这样一条联合索引：

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

这样WHERE user_id = ? AND status = ?能直接定位到很小的范围，create_time天然有序，排序也省了。由于查询列不多，如果需要进一步优化，可以考虑把order_id、amount也放进索引形成覆盖索引（下一章细说）。但如果只有WHERE status = 1 AND create_time > ?这种不带user_id的场景，这条索引的第一列就用不上，你得再单独评估是否值得为status和create_time建另一条索引。

这里也顺带回答一个很多人纠结的问题：where a and b，a和b到底谁放前面？如果是两个等值条件，通常把区分度高（单位值对应的数据行少）的那个放前面；如果一个是等值一个是范围，等值的放前面。别教条，拿真实业务统计一下字段的基数再决定。

5. 索引失效排查：那些悄悄退化成全表扫描的SQL

5.1 先说清楚“失效”到底是什么

索引失效不是索引坏了，而是优化器认为“你的查询走索引还不如全表扫描快”。第二种情况是SQL写法让B+树根本无从下手——比如索引列被函数包裹，树上的键值就失去了原本可以直接比较的形态。排查索引问题的时候，容易被忽略的是：优化器是会做选择的，即便字段上有索引，当它判断扫描比例太高（比如一个字段90%的值都满足WHERE条件）时，也会选择全表扫描。

我把最常见的失效场景整理成一张表，方便大家对照排查：

5.2 函数包裹和隐式类型转换是最常见的坑

我现场排过几次慢SQL，最后几乎都发现是这两个原因。

先说函数。你建了idx_create_time(create_time)索引，但SQL写成WHERE DATE(create_time) = '2024-01-01'，索引彻底失效。原因很简单：B+树里存的是原始时间戳，而SQL想比较的是DATE函数处理后的值，MySQL只能逐行计算函数结果再做比较。

改法不是取消索引，而是把SQL改写成一个区间查询：

WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00';

这样索引能直接利用值的范围做区间扫描。

再看隐式类型转换。表里phone字段是VARCHAR，搜索时传了数字13800001111。MySQL的规则是字符串和数字比较时，把字符串转换成数字再比，等价于对索引列调用了一次转换函数。同样失效。只要把参数写成'13800001111'就能恢复正常。

5.3 like、or和null的三个易错细节

LIKE 'abc%'能用索引，因为B+树有序，可以定位到第一个前缀为abc的记录，然后一路向右扫描，相当于一个>= 'abc' AND < 'abd'的区间。但LIKE '%abc'就没法定位了——前缀未知，无从找起点。LIKE '%abc%'同理，还会更糟。

OR的问题需要特别注意。很多人以为给OR两边的列都建上索引就完事了，有时候优化器确实会走Index Merge把两个索引结果合并，但更多时候只要其中一边没有索引，整个查询就放弃索引，直接全表扫描。我通常建议把它改写成两个查询的UNION，各自走各自索引，条理清晰，优化器也不纠结。

NULL相关的坑是：IS NULL在某些情况下可以用索引，但IS NOT NULL很容易走全表。这里没有绝对答案，要看优化器对数据分布的判断，但作为经验，业务设计能避免NULL就避免NULL，尽量用DEFAULT 0或空字符串代替可空字段，索引层面的收益立竿见影。

5.4 学会用EXPLAIN读懂优化器的决定

排查索引失效，最快的工具就是EXPLAIN。

EXPLAIN SELECT user_id, amount FROM orders WHERE user_id = 123 AND status = 1;

重点关注四个字段。type表示访问类型，按性能从好到差大致是：system > const > eq_ref > ref > range > index > ALL。看到ALL就是全表扫描，基本可以认定索引白建了。key显示实际用到的索引。rows是优化器预估要扫描的行数，越小越好。Extra里如果出现Using filesort说明排序没走索引，出现Using index则说明覆盖索引生效，不需要回表。

我优化慢SQL的习惯是：先EXPLAIN，看到ALL和Using filesort就想办法消灭它们；消灭不了的，再分析是不是优化器评估有偏差，必要时用FORCE INDEX做临时验证，但不要长期依赖强制索引。

6. order by排序也能吃索引红利

6.1 filesort和利用索引排序的原理

ORDER BY是另一个性能黑洞。先记住一个结论：如果结果集恰好能按索引键的顺序输出，MySQL就不需要额外排序，直接按序返回；否则它会把查询结果加载进sort_buffer_size内存做排序，内存不够还要落盘，Extra里就会出现Using filesort。

数据量小的时候Using filesort无所谓，几十万行以上就非常痛苦。比如订单查询要WHERE user_id = 123 ORDER BY create_time DESC，如果你有联合索引(user_id, create_time)，MySQL先定位user_id=123的所有记录，而它们天然按create_time排序，这就不需要任何额外排序。这条SQL如果命中这种模式，性能好得离谱。

反例是：如果你建的是(status, user_id)，实际查询却是WHERE user_id = ? ORDER BY create_time，那排序字段create_time根本不在索引的连续位置，MySQL只能把结果全捞出来自己排。

6.2 覆盖索引：一次查询不回表的魔法

前面说过二级索引叶子存的是索引列和主键值。如果一个查询需要的所有列都包含在同一个二级索引里，MySQL连回表都可以省掉，直接从索引页读取全部结果，Extra显示Using index。这就是覆盖索引。

比如索引(user_id, status, create_time, order_id, amount)，查询：

SELECT order_id, amount FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time;

这条SQL的所有环节都贴合索引结构，既不需要回表，也不需要filesort。这就是调优的理想状态。

6.3 少用select *是对索引用途的尊重

很多人觉得SELECT *省事，但在二级索引存在的情况下，SELECT *几乎一定会回表。因为你把整行数据都查出来了，二级索引提供的列满足不了需求。

更尴尬的场景是：本来查询只涉及两三个字段，覆盖索引可以完美服务；改成SELECT *后，优化器一看回表代价太大，干脆选择直接走聚簇索引全表扫描，你用心的索引设计全部白费。

所以我一直强烈建议：生产环境的查询永远只SELECT需要的列，这不仅是为了少传几个字节的网络流量，更是为了给覆盖索引创造机会。读多写少的核心报表查询尤其值得这么设计。

7. MySQL 8.0的索引新特性与相关维护

7.1 降序索引和函数索引

MySQL 8.0最让索引爱好者兴奋的更新，就是降序索引和函数索引。

老版本里索引列只能按ASC存储，ORDER BY desc经常被迫走filesort。8.0起可以显式声明降序索引：

ALTER TABLE orders ADD INDEX idx_user_time_desc (user_id, create_time DESC);

这样ORDER BY user_id ASC, create_time DESC如果满足最左前缀，可以直接按索引顺序扫描，连排序都省了。

函数索引解决的是5.1节那个DATE()函数的坑。8.0.13之后你可以这样建索引：

ALTER TABLE orders ADD INDEX idx_created_day ((DATE(create_time)));

查询里的WHERE DATE(create_time) = '2024-01-01'就能命中它。注意函数表达式外面多了一对括号，这是MySQL要求的语法。

7.2 不可见索引和索引跳跃扫描

8.0的不可见索引对我这种要做索引治理的人来说是救命级功能。以前想删一个可能有用的索引，只能先删掉观察线上性能，出了问题再连夜加回来，非常被动。现在可以先把索引设为不可见：

ALTER TABLE orders ALTER INDEX idx_user_status INVISIBLE;

优化器默认不会使用不可见索引，但索引本身还在，随时可以改回VISIBLE。相当于给你一个“删除前的观察期”，跑几天线上流量发现没影响，再真正DROP，这步操作在我的索引清理流程里已经是标配了。

索引跳跃扫描（Index Skip Scan）解决的是另一个痛点：联合索引第一列区分度低，而查询里没带第一列。比如索引(gender, id)，gender只有男和女两个值，查询WHERE id = 123。老版本的优化器会因为没带最左前缀直接放弃，8.0（从8.0.13起）可以跳过gender，把它当成两个小查询分别扫：先扫gender=1下id=123，再扫gender=2下id=123。跳过的值有多少，查询次数就有多少。所以它只是特定条件下的补救机制，不要指望它能完全替代合理的最左前缀设计。

7.3 索引表空间与碎片处理

很多人在运维中遇到过“数据删了一大堆，表文件还是那么大”的困惑。这跟索引表空间直接相关。

MySQL的InnoDB表在默认配置下（innodb_file_per_table=ON）每张表对应一个.ibd文件，数据页和索引页都存里面。DELETE操作并不会物理删除页里的数据，只是把记录标记为删除，页的空间让后续插入复用。大量增删改之后，B+树可能发生页分裂，产生大量不连续的空洞，索引查询的IO路径就越变越长。

有人问我“索引表空间是不是单独的文件”，答案是：索引和数据在同一个表空间里，但各有各的页。你可以用information_schema查看表大小和索引统计：

SELECT TABLE_NAME, INDEX_NAME, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '你的库名';

碎片整理用OPTIMIZE TABLE orders，它会重建表和所有索引，物理压缩页空间。注意这个操作在数据量大时很耗时，且会锁表，线上环境一定要挑业务低峰执行，或者考虑用在线DDL方式谨慎操作。更轻量的做法是定期执行ANALYZE TABLE orders，不整理物理碎片但会更新索引统计信息，让优化器更准确地评估“走不走索引”，很多索引选择错误的问题其实靠这一步就能缓解。

8. 索引调优的经验清单

最后我想把自己这几年做索引优化的几条实战经验整理成一个清单，照着逐条核对，大多数索引问题都能提前暴露。

第一，建索引先跑EXPLAIN验证。不要凭感觉设计索引，建完立刻用真实业务SQL跑一遍EXPLAIN，确认type不是ALL，key用的是你期待的索引，Extra里没有Using filesort。

第二，索引数量要克制。一张表索引太多，写入、更新时每一棵B+树都要同步修改，写入性能会迅速恶化。我通常建议单表索引控制在5到6个以内，核心是通过联合索引覆盖多组业务查询，而不是每个字段单建一个。

第三，定期清理冗余和重复索引。有(a, b, c)索引存在，又单独建了(a)，后者就是冗余。排查的时候用SHOW INDEX FROM 表名，把前缀重叠的索引列出来，用不可见索引观察后再删除。

第四，统计信息过期比索引失效更隐蔽。如果明明有索引，优化器却走了全表，先别怀疑索引坏了，先执行ANALYZE TABLE刷新统计信息，很多时候一条命令就解决了。

第五，覆盖索引是读多写少场景性价比最高的优化手段。对于报表类、查询密集的表，把高频查询的SELECT列和WHERE条件一起设计进联合索引，收益非常明显，但也要接受这类索引占空间更多的事实。

我在实际项目里见过太多把索引当成万能药的案例：慢查询出现就加索引，加到十几二十个后写入直接卡住。索引本质上是一个读性能和写性能的取舍，要清醒地知道每次加索引背后付出的代价是什么，也正因为如此，我才把这一章定位成“深入理解”——你只有清楚索引的原理，才知道什么时候该用它，什么时候该放过它。