企业官网建设流程全解析

1. 一条三秒的查询，把索引这件事重新摆到我面前

凌晨一点半被告警叫醒，订单列表接口 P99 从 80ms 涨到 3.2s。翻了下监控，数据库 CPU 打满，慢日志里躺着一条再普通不过的 SQL：select * from t_order where user_id = 10086 order by create_time desc limit 20;。这张表 2400 万行，user_id上有索引，理论上不该慢。但explain出来type=ALL、rows=2400万，全表扫。事后复盘，原因是一次数据订正脚本把user_id从bigint改成了varchar，而代码里传参还是数字，隐式类型转换直接把 MySQL索引 干废了。

这件事之后我把索引相关的知识重新梳理了一遍，从最底层的 B+树 结构，到索引的几种类型，再到实际使用中那些神出鬼没的失效场景。这篇内容就是那次梳理的产物，偏实战、偏排查，不讲教科书式的定义堆砌。适合已经能写 SQL、但一遇到慢查询就只会加索引或者加机器的同学，也适合想搞明白"为什么索引会失效"这类问题背后逻辑的人。读完之后你至少能做到三件事：看一眼explain就知道问题出在哪、知道什么情况下不该建索引、遇到"明明有索引却没用上"能自己排查到底。

需要说清楚的是，索引不是银弹，它是拿空间和写入性能换查询性能的一笔交易。2400 万行的表上加一个索引，磁盘多占几百 MB 到几 GB，每次insert/update/delete都要多维护一棵树。所以"给每个 where 字段都加上索引"这种做法，短期看着爽，长期一定是灾难。搞清楚它怎么工作，比记住"要给字段加索引"这句话重要得多。

2. B+树凭什么成了MySQL索引的默认数据结构

2.1 先从磁盘IO的最小单位说起

理解 B+树，得先接受一个前提：数据库的数据存在磁盘上，而磁盘读写的最小单位不是"一行记录"，而是一个数据页，InnoDB 里默认 16KB。这意味着你哪怕只想读一行 100 字节的数据，操作系统也可能要搬 16KB 进内存。所以衡量索引好坏的核心指标不是"比较次数"，而是磁盘IO次数，具体说就是"树有多高"。

二叉树、红黑树这类结构在内存里跑得飞快，但一放到磁盘上就废了：1000 万条数据，红黑树的树高大概在 2×log₂(1000万) ≈ 46 层，最坏情况要 46 次磁盘随机读。而 B+树 的特性是矮胖——非叶子节点只存键值和一个页指针，不存真实数据行，所以单个页能塞下的键非常多，树高能压到 3 到 4 层。

给你算一笔账，这也是面试里最爱问的一道题。假设主键是bigint（8 字节），InnoDB 的页指针固定 6 字节，那么非叶子节点里一个"键值 + 指针"的组合是 14 字节。16KB 的页去掉页头页尾大约剩 15KB 可用，15000 ÷ 14 ≈ 1070，也就是说一个非叶子节点能指向 1070 个下层页。叶子节点存的是真实数据行，假设一行 1KB，一页能放 16 行。那么三层 B+树 的容量是：1070 × 1070 × 16 ≈1832 万行。四层就是 1070 倍，接近 200 亿行。

结论很直接：**一棵三层 B+树 就能撑住两千万行级别的表，任何一次等值查询最多三次磁盘IO，而且根节点和第二层大概率常驻内存（Buffer Pool 缓存），实际往往只有一到两次物理IO。**这就是 B+树 统治 MySQL索引 的根本原因，不是什么高深算法，纯粹是把磁盘特性利用到了极致。

2.2 和B树、哈希索引的正面比较

很多人会问，B树 不也是多路平衡树吗，为什么不用它？差别在两点，都非常关键。

第一，B树 的非叶子节点也存数据行。这会导致单个页能容纳的键数量骤减，树被迫长高。同样的数据量，B树 可能比 B+树 多一层甚至两层，磁盘IO 直接翻倍。而且非叶子节点里的数据行在查询时是"路过"的，属于浪费了页空间。

第二，B+树 的叶子节点用双向链表串起来了。这个设计对范围查询是决定性的：where create_time between '2024-01-01' and '2024-01-31'，B+树 只需要先定位到起始位置，然后沿着链表顺序往后扫就行，全程顺序IO。而 B树 要做中序遍历，节点在磁盘上东一块西一块，随机IO 一大堆。

至于哈希索引，它确实能做到 O(1) 的等值查找，但完全不支持范围查询、排序，也不支持最左前缀。所以它只适合纯等值场景。InnoDB 默认建的都是 B+树 索引，但它有一个自适应哈希索引（Adaptive Hash Index）的小机制：当某个索引页被频繁以相同模式访问时，InnoDB 会自动在内存里为它建一套哈希索引来加速，这个动作是引擎自己做的，你既不用配置也没法直接干预。我在实际使用中发现，这个特性在高并发点查场景下收益可观，但也会占用额外的 Buffer Pool 内存，有时候反而挤压了正常页的缓存空间，属于权衡项。

2.3 聚簇索引与二级索引，一次查询走了几条路

InnoDB 是索引组织表，意思是数据行本身就存在主键索引的叶子节点上，这棵主键索引树就叫聚簇索引（Clustered Index）。这一点直接决定了所有二级索引的查询方式。

你给user_id建的索引是一棵独立的 B+树，它的叶子节点存的不是整行数据，而是user_id的值 + 主键值。所以当你执行select * from t_order where user_id = 10086时，实际走了两步：先在user_id索引树上找到10086，拿到对应的主键 id 列表；再拿这些 id 去主键索引树上一个个查出完整行。这个第二步就是老生常谈的回表。

提示：回表是二级索引查询的固有成本，不是"故障"，但可以通过覆盖索引消除。判断依据是explain的 Extra 里出现Using index。

回表的代价很大，因为主键值通常是乱序的，回表意味着大量随机IO。这也是为什么我一直建议主键用自增或者趋势递增的值（比如雪花ID的某种变体）——如果主键是随机的（比如 UUID），二级索引里存的主键没规律，回表就是完全随机的磁盘访问，性能会差一大截，同时插入时还会引发频繁的页分裂。

3. 索引家族的全景：从数据结构到功能分类

3.1 按数据结构分的四种索引

MySQL 支持多种索引，但它们归属于不同的存储引擎，不能混用。

日常 95% 的场景都是 B+树 索引。全文索引要注意一个坑：InnoDB 的全文索引对中文的支持依赖ngram分词器，默认配置下中文分词粒度不理想，做商品搜索这类需求往往还是老老实实上专门的检索组件。哈希索引在 Memory 引擎里有个特点：只能整列匹配，where a = 1能用，where a > 1就是全表扫。

3.2 按功能分的六种索引，以及它们真正的差别

这部分是实操中最容易混淆的，我把它们摊开讲。

主键索引：一张表只有一个，非空且唯一，InnoDB 里它就是聚簇索引。如果你建表时没指定主键，InnoDB 会挑一个非空唯一索引顶上，再没有就自动生成一个 6 字节的隐藏行 ID。我强烈建议永远显式指定主键，不要让引擎帮你猜。

唯一索引：值必须唯一，但允许 NULL（多个 NULL 不冲突，这点和标准 SQL 的直觉不一样）。它的查询性能和普通索引几乎一致，区别在于插入时多一次唯一性校验。别为了"查询快"把普通索引改成唯一索引，如果业务上真的能保证唯一，那加上是合理的；如果保证不了，写入时会频繁报错。

普通索引（二级索引）：最常用的类型，没有任何约束，纯粹为了加速查询。

联合索引（复合索引）：多个列组合成一棵索引树。它的核心规则是最左前缀——idx(a, b, c)能被a、a,b、a,b,c使用，但单独用b或c用不上。这里有个常见的误解需要澄清：联合索引的叶子节点里，是先按a排序，a相同再按b排，b相同再按c排。所以where b = 2这种查询，b的值在整棵树里是"局部有序、全局无序"的，没法做二分定位。

前缀索引：针对长字符串列，比如varchar(255)的 URL，只取前 N 个字符建索引。alter table t add index idx_url(url(20));。它能显著减小索引体积，代价是无法用于覆盖索引（因为不存完整值，必须回表），而且区分度可能不够。选 N 的方法是用select count(distinct left(url, 20)) / count(*) from t;逐步试探，让区分度接近全列的区分度。

全文索引：见上一节的说明。

3.3 联合索引的列顺序，到底该怎么排

这是被问得最多的问题。我的排序原则是三条，按优先级来：

1. 等值查询的列放最前面。等值条件下，列在索引里的先后顺序对定位效率影响不大，但把等值列前置能保证后续列可以继续用于排序或过滤。
2. 区分度高的列放前面。区分度 =count(distinct col) / count(*)。区分度越高，一次定位筛掉的数据越多。
3. 需要排序的列，遵循"等值列在前、排序列在后"。因为order by只有在索引里天然有序时才能免排序。

举个实例：select * from t_order where shop_id = 1 and status = 2 order by create_time desc limit 20;，正确的索引是idx(shop_id, status, create_time)。这样shop_id和status等值定位后，create_time在这个子集里天然有序，order by直接顺着链表取 20 条就结束，Extra 里会显示没有Using filesort。

如果建成idx(create_time, shop_id, status)，虽然也能过滤，但create_time在树里是全局有序的，优化器大概率会用它来做排序然后回表过滤，扫的行数反而更多。我踩过这个坑，一个列表页从 20ms 掉到 400ms，换成正确顺序后立刻恢复。

4. 建完索引之后，怎么确认它真的被用上了

4.1 explain 里必须看懂的几列

建完索引不代表生效，explain才是唯一的裁判。我一般只看四列，其它列作为参考。

type：访问类型，从好到坏的顺序是system > const > eq_ref > ref > range > index > ALL。生产环境的慢查询里如果出现index或ALL，基本可以判定有问题。index是扫整棵二级索引树（比全表扫略好，因为索引文件小），ALL是扫聚簇索引全表。

key：实际用上的索引。注意possible_keys有值但key是 NULL，说明优化器评估后放弃了索引，这种情况通常出现在区分度低或者统计信息不准的时候。

rows：预估扫描行数，是优化器的估算值，不是精确值。但它的量级很有参考意义，比如 rows 是 200 万而最终只返回 10 条，说明索引过滤能力不足。

Extra：信息量最大的一列。几个关键值：

• Using index：覆盖索引，没有回表，最理想。
• Using index condition：索引下推（ICP）生效。
• Using where：在存储引擎返回数据后，Server 层还要再过滤一遍。
• Using filesort：额外排序，尽量消除。
• Using temporary：用了临时表，通常出现在group by场景，要警惕。

4.2 一个真实的执行计划改写过程

原始 SQL 和索引：

-- 表结构（简化） create table t_order ( id bigint primary key auto_increment, user_id bigint not null, shop_id int not null, status tinyint not null, amount decimal(10,2) not null, create_time datetime not null, remark varchar(255) default null, key idx_user (user_id) ); -- 慢查询：统计某用户近30天的订单总额 select sum(amount) from t_order where user_id = 10086 and create_time >= '2024-06-01' and status in (1,2,3);

explain显示：type=ref，key=idx_user，rows≈12000，Extra=Using where。也就是说，索引只帮它定位了user_id的 12000 行，create_time和status都是回表之后再过滤的。12000 次回表，就是这个查询慢的原因。

改成联合索引并把过滤条件都放进去：

alter table t_order add index idx_user_time_status(user_id, create_time, status);

注意这里的顺序：user_id等值在前，create_time是范围查询，放在第二，status是in等值但只能放在范围后面。范围列后面的列在MySQL 5.6 之前是完全用不上的，5.6 引入索引下推（Index Condition Pushdown）之后，status虽然不能用于定位，但可以在存储引擎层直接过滤掉不符合条件的行，减少回表次数。改完之后rows≈300，Extra=Using index condition，耗时从 800ms 降到 15ms。

注意：范围列右边的列只能做过滤、不能做定位，这是个硬性规则。所以联合索引里，范围查询的列尽量往后放。

5. 那些让索引"看起来失效"的真实场景还原

5.1 第一类：列被"动了手脚"

这类问题的共同特征是索引列参与了运算或函数调用，导致 B+树 无法按值定位。

-- 反例：索引列被函数包裹 select * from t_order where date(create_time) = '2024-06-01'; -- 正例：改成范围 select * from t_order where create_time >= '2024-06-01 00:00:00' and create_time < '2024-06-02 00:00:00'; -- 反例：索引列参与运算 select * from t_order where id + 1 = 100; -- 正例 select * from t_order where id = 99;

原理很直白：B+树 是按create_time的原始值排好序的，你把每一行都套上date()之后，原有的顺序就失去意义了，优化器只能全表扫一遍挨个算。

隐式类型转换是这类里最阴的一个，因为它藏在参数里看不见。如果user_id是varchar类型，你写where user_id = 10086（数字），MySQL 会把字符串列转成数字再比较，等价于cast(user_id as signed) = 10086，索引直接失效。反过来，如果列是数字类型、参数是字符串'10086'，MySQL 是把字符串转成数字，索引仍然有效。所以规则是：字符串列绝不能用数字去比。我文章开头那次线上事故就是栽在这上面。

我的建议是在代码层做参数类型校验，ORM 层尽量用强类型绑定，别让原始字符串直接拼进 SQL。

5.2 第二类：最左前缀被破坏

-- 索引：idx(a, b, c) select * from t where a = 1 and b = 2 and c = 3; -- 全部命中 select * from t where a = 1 and c = 3; -- 只用到 a select * from t where b = 2; -- 完全用不上 select * from t where a = 1 and b > 2 and c = 3; -- a、b 定位，c 只能过滤

有个特殊情况很多人不知道：where a = 1 and b = 2里b用的是等值，所以索引树里b的部分是局部有序的，order by a, b可以免排序。但如果是where a = 1 and b > 2 order by b, c，b是范围，c就失去有序性了，会触发 filesort。

另外，like也遵守最左原则：like 'abc%'能用索引（它是范围查询），like '%abc'和like '%abc%'用不上。如果业务上非得做后缀匹配，可以考虑把字符串反转后存一列，用反转列建索引，这是个土办法但确实有效。

5.3 第三类：优化器主动"放弃"索引

这一类最容易被误解成"失效"，其实索引好好的，是优化器算了一笔账，觉得全表扫更便宜。

典型场景是区分度低的列。比如status只有 0 和 1 两个值，表里 99% 都是 1。你查where status = 1，优化器会算：走索引要扫 99% 的索引页然后回表 99% 的行，还不如直接顺序扫全表。这种时候加索引不仅是无效的，还会拖累写入。判断标准是：当预估要返回的行数超过全表 20%~30% 时，优化器大概率会放弃索引。

还有几个会让优化器放弃的写法：

select * from t where col != 1; -- 不等值 select * from t where col not in (1,2); -- 大概率走全表 select * from t where col is not null; select * from t where a = 1 or b = 2; -- b 没索引则整体失效

最后那个or的问题，解法是给b也建上索引，让优化器做index_merge，或者改写成union all两条查询。

还有一种是统计信息不准导致的选错索引。InnoDB 的统计信息是采样估算的，有时候会偏差很大。可以通过analyze table t_order;重新采集。我在线上遇到过一次，同一张表同样的 SQL，在从库上走了索引，在主库上不走，最后就是统计信息差异造成的。

5.4 一个完整的三小时排查链路

把上面这些串起来，讲一次真实排查。现象：报表接口偶发超时，SQL 是select count(*) from t_log where biz_type = 5 and create_time > '2024-06-01';，biz_type和create_time上都有单列索引。

第一步，先看explain。结果type=ALL。先排除函数包裹、隐式转换这两类，检查发现biz_type是tinyint，参数是数字，create_time是datetime，参数是标准字符串，排除类型问题。

第二步，看区分度。select count(distinct biz_type), count(*) from t_log;结果biz_type只有 6 个不同值，而biz_type = 5的行占了全表的 40%。到这里基本能定性了：优化器判定走biz_type索引要回表 40% 的行，不如全表扫。

第三步，验证。强制走索引select count(*) from t_log force index(idx_biz_type) where biz_type = 5 and create_time > '2024-06-01';，耗时反而从 1.2s 涨到 4s，印证了优化器的判断是对的。

第四步，求解。建立联合索引idx(biz_type, create_time)，并且让它成为覆盖索引——count(*)只需要索引里已有的列，不用回表。建完之后type=range，Extra=Using index，耗时 60ms。这里的关键是：count(*)走覆盖索引时，扫描的是一棵体积小得多的二级索引树，IO 量天然就少。

这条链路里每一步都在做一件事：把猜测变成证据。不猜"是不是索引失效了"，而是用explain、用区分度统计、用force index对比来验证。

6. 绕不开的几个名词，讲透它们比背定义有用

6.1 回表、覆盖索引、索引下推

回表：前面讲过，二级索引拿到主键后再去聚簇索引查完整行。回表次数等于二级索引命中的行数，所以索引过滤能力越弱，回表越多，性能越差。

覆盖索引：查询需要的所有列都在索引里，不需要回表。判断标志是Extra=Using index。它的价值不只是省几次IO，更重要的是避免了随机IO。举个典型：select id, user_id from t_order where user_id = 10086;，因为二级索引叶子节点存的就是user_id + id，这个查询天然覆盖，速度飞快。如果写成select *，就必然回表。所以我在写查询时有个习惯：只 select 需要的列，不做无脑select *。

索引下推（ICP）：MySQL 5.6 引入的优化。在没有 ICP 的年代，where a = 1 and b like '%x%'这种，存储引擎只能靠a定位，然后回表把行交给 Server 层，Server 层再判断b的条件。有了 ICP 之后，b的判断被"下推"到存储引擎层，在索引里就能判断，不符合的直接不回表。减少的是回表次数，不是扫描行数。explain里显示Using index condition。

6.2 区分度、基数与索引选择性

基数（Cardinality）：索引列里不同值的个数。show index from t_order;里的Cardinality列就是这个，它是估算值。基数越大，说明值越分散。

区分度（选择性）：Cardinality / 总行数，越接近 1 越好。大于 0.3 通常算不错，低于 0.01 基本没有建索引的必要。这个指标是决定"要不要建索引"的第一道筛子。

6.3 页分裂、页合并与自增主键的价值

这一组概念解释了"为什么主键不要用随机值"。

B+树 的叶子节点是一个个 16KB 的页，页里的记录按主键顺序排列。如果主键是自增的，新记录永远追加在最后一页，写满就开新页，顺序写，顺序IO，效率极高。

如果主键是随机的（比如 UUID），新记录会随机插到中间某个页。当目标页满了，InnoDB 必须把这个页拆成两个（页分裂），并把一半记录挪到新页，同时更新父节点的指针。这个过程伴随大量的数据搬移和随机IO，而且拆出来的页往往填充率只有 50% 左右，空间浪费严重，树也更容易变高。

反过来，当相邻页因为删除导致填充率过低时，InnoDB 会做页合并回收空间。所以频繁的随机删除+插入，也会引发页的反复分裂与合并。

提示：关于 UUID 主键还有一个隐藏成本——UUID 是 36 字节的字符串，而bigint是 8 字节。二级索引里每一行都要存主键值，用 UUID 会让所有二级索引的体积膨胀好几倍。

我在做新表设计时，主键一律用自增bigint或者趋势递增的分布式ID（保证大致有序即可），这个习惯省下来的性能非常可观。

6.4 MRR 与 Buffer Pool 的关系

MRR（Multi-Range Read）是 MySQL 5.6 引入的另一个优化。回表时主键通常是乱序的，直接挨个回表就是随机IO。MRR 的做法是先把一批主键收集起来排序，再去聚簇索引里顺序读取。它减少的是随机IO次数，在机械盘上收益明显，在 SSD 上收益相对小一些。

Buffer Pool是 InnoDB 最重要的内存区域，缓存数据页和索引页。前面算过，B+树 的前两层通常会被缓存，这就是为什么三层树的实际查询往往只有一次物理IO。如果你的 Buffer Pool 设置得过小，根节点频繁被换出，性能会断崖式下跌。一般建议设置为物理内存的 50%~70%。

7. 索引该加还是该删：线上维护的几条实操经验

7.1 用系统视图找出冗余和没用的索引

索引不是越多越好，维护成本是实打实的。MySQL 自带两个视图能帮上大忙：

-- 查看冗余索引（前缀重复） select * from sys.schema_redundant_indexes; -- 查看从未被使用过的索引（需先开启 performance_schema 相关采集） select * from sys.schema_unused_indexes;

schema_redundant_indexes能找出idx(a)和idx(a, b)这类重复——后者完全覆盖前者的功能，前者可以删。我在一次清理中删掉了 40 多个冗余索引，那张表的写入 TPS 提升了约 30%，因为每次写入要维护的 B+树 从 3 棵变成了 1 棵。

7.2 大表加索引，别在业务高峰干这事

alter table t add index ...在 MySQL 5.6 之后支持Online DDL，可以做到加索引期间不阻塞读写：

alter table t_order add index idx_shop_time(shop_id, create_time), algorithm=inplace, lock=none;

但要注意几个前提：ALGORITHM=INPLACE不是万能的，改列类型、改字符集这类操作仍然需要COPY（重建整表）；Online DDL 期间会产生大量InnoDB临时日志，需要保证innodb_online_alter_log_max_size足够大，否则会报错；重建索引会消耗大量 IO 和 CPU，千万别在业务高峰期做。我一般会先在一个只读从库上试跑，记录耗时和资源占用，再决定主库的执行窗口。

对于超大表（上亿行），更稳妥的做法是用第三方工具做在线表结构变更，它通过建影子表+增量同步的方式，全程可控、可暂停、可回滚。

7.3 上线前的索引自查清单

最后把我自己用的一张清单分享出来，每次提交涉及 SQL 变更的代码前过一遍：

• 联合索引的列顺序是否满足"等值在前、范围在后、排序最后"。
• 查询列是否都包含在索引里，能否做成覆盖索引。
• 索引列的区分度是否足够（Cardinality / 总行数）。
• 是否存在函数、运算、隐式类型转换包裹索引列。
• 是否存在以%开头的like。
• order by的列是否与索引顺序一致，能否免排序。
• 是否存在可被更长的联合索引覆盖的短索引（冗余索引）。
• 新增索引带来的写入成本，是否在可接受范围内。
• explain的type是否达到range或更好。

7.4 我的个人体会

做数据库这几年，我最深的感受是：索引优化的核心不是"建更多索引"，而是"用更少的索引覆盖更多的查询"。一张表上挂十几个单列索引，和挂三四个精心设计的联合索引，后者在读写两方面都更优。真正的难点在于你知道业务会跑哪些 SQL——所以每次加索引之前，我都会先把这一批查询收集起来，看看它们能不能被同一棵索引树照顾到。

还有一个经验：别迷信规则，要相信explain。网上流传的"!=一定不走索引""or一定失效"之类说法，都是特定数据分布下的结论，换一张表可能完全不成立。优化器是基于成本的，成本又取决于数据分布，所以同一句 SQL 在今天走索引、下个月数据量涨了可能就不走了。养成改完索引就看执行计划的习惯，比背一百条规则都有用。