企业官网建设流程全解析

这问题我太熟了。一说MySQL索引，很多人第一反应就是“建个索引能加速查询”，但真到线上出问题的时候，往往一头雾水——明明是加了索引的字段，为什么查询还是慢？为什么有时候走了索引反而更慢？为什么一组字段建了联合索引，换个查询条件索引就失效了？

我见过的真实案例：某天凌晨业务告警，一条统计报表SQL跑了将近30秒，把数据库CPU直接打满。开发同学看了一眼说“这表索引都建了呀”，结果EXPLAIN一执行，type是ALL，rows直接扫了上千万行，命中的索引数量是0。问题到底出在哪？索引这玩意儿，不是“建了”就完事的，你得知道它底层长什么样、什么时候会失效、怎么根据执行计划反向修正。

这篇文章我准备把MySQL索引从头到尾拆一遍，不绕概念，直接讲原理、讲选型、讲实操、讲排坑。无论你是刚入门的新手，还是已经被慢查询折磨过的老手，这中间应该都有你能直接用上的东西。

1. 索引到底是什么：从一次线上慢查询说起

1.1 一条查询是怎么“找数据”的

先复盘那个凌晨的慢查询。表结构大概是这样的：

CREATE TABLE user_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这条表建了user_id和create_time两个单列索引，看起来没毛病。慢SQL的查询条件是：

SELECT order_no, amount FROM user_order WHERE status = 1 AND create_time >= '2024-01-01 00:00:00' ORDER BY create_time DESC LIMIT 100;

为什么慢？因为条件里带了一个status字段，而这个字段没有索引。MySQL扫描的时候只能顺着create_time索引把2024年之后的数据逐行捞出来，再用status逐一过滤，结果捞出来十几万行，过滤完只剩几百行。这还只是单表，如果Join几张表，那放大效应更吓人。

这里的核心问题在于：索引不是自动帮你把所有字段都管了的，它是你给数据预先排好的顺序。你没给它安排的字段，查询时它就只能挨个查。

用生活类比来说，索引就像一本书的目录。没有目录的时候，你想找“MySQL索引”这一章，只能从第1页翻到第500页，运气好翻得快，运气差那就是灾难。有了目录，你直接定位到页码。但如果你用书里没有收录的词去查目录，那就又回到逐页翻的老路上了。

1.2 为什么是B+Tree而不是二叉树或者哈希表

很多刚接触数据库的人会问：为什么MySQL的InnoDB索引用B+Tree，不用二叉树？不用哈希表？这得从磁盘IO的角度理解。

数据库的数据不是存在内存里，是存在磁盘上的。而磁盘随机读是极慢的，一次随机IO大概要10毫秒量级。如果索引是个二叉树，几百万行数据的树高差不多到20层，意味着你查一次数据最多要做20次磁盘随机IO，那就是200毫秒起步，这种延迟对任何在线业务都是不可接受的。

B+Tree和二叉树的关键差异在于一个节点能存多个键值。InnoDB默认页大小是16KB，一个节点能容纳上百个键。数据量在千万级的时候，B+Tree的高度也只有3到4层。这意味着什么？意味着你在几十亿行数据里找一个值，最多做三四次磁盘IO就够了，跟查一本只有几十页的小册子似的。

那哈希表为什么不行？哈希索引的致命短板就是不支持范围查询。你查create_time >= '2024-01-01'这种条件，哈希表只能一个一个算哈希值去定位，没法告诉你“从2024年1月1号往后挨个看”。而B+Tree的叶子节点本身是有序链表，范围查询天然是强项，顺着链表一路往后扫就行。

所以B+Tree不是设计者拍脑袋选的，是被磁盘IO和查询模式这两座大山逼出来的最优解。理解了这一层，你后续看索引设计、看执行计划，才会有“原来如此”的感觉，而不是死记硬背结论。

1.3 聚簇索引与二级索引的关系

InnoDB的表数据本身是存在主键索引的叶子节点里的，这就是聚簇索引。换句话说，你建了一张有主键的表，它的数据就已经按主键排好序存放了。你根据主键查，直接就能定位到数据行，效率最高。

二级索引（也就是我们平时手动建的普通索引）叶子节点存的不是整行数据，而是主键值。查询过程是：先在二级索引里找到主键，再拿着主键去聚簇索引里回表查整行数据。这个“拿着主键再查一次”的动作，就叫做回表。

回表不是免费的。索引命中的行数越多，回表次数越多，性能就越是退化。很多人遇到的“明明命中了索引却还是很慢”，往往就是一次查询命中了几万行，回表了几万次。理解了聚簇索引和二级索引的这层关系，后面讲覆盖索引的时候你会豁然开朗。

2. 索引类型全景盘点与选型

2.1 按功能分类：主键、唯一、普通、全文

MySQL索引按功能分，大致是四类：

这里面最容易踩坑的是唯一索引。很多人不加思考就把所有“看起来不会重复”的字段都建了唯一索引，但没意识到唯一索引的插入是有额外校验开销的。数据量大的时候，每次插入都要走一遍唯一性检查，这个成本在高并发写入场景下会被放得非常明显。

我自己见过一个场景：一张流水表，业务上明明有主键，又给流水号字段加了个唯一索引，防止重复写入。听起来没毛病，但这条表每秒写入上千次，每次插入都要额外做一次唯一性检查，高峰期明显感觉到写入延迟上升。后来业务侧在应用层做了去重，把唯一索引砍掉，写入性能才恢复正常。

所以建唯一索引前，先问自己三个问题：这个字段真的必须由数据库保证唯一吗？应用层能不能做这个约束？写入压力大不大？不要为了“保险”让数据库承担不必要的开销。

2.2 按数据组织方式理解B+Tree索引

从实现角度看，InnoDB的所有索引本质上都是B+Tree结构，但根据是否存储数据行，又分成聚簇索引（主键索引）和二级索引（非聚簇索引）。

这里有个关键且容易被忽略的细节：主键的选择会影响所有二级索引的大小。因为二级索引叶子节点存的是主键值，主键越长，二级索引占用的空间越大，每次回表时对比的代价也越高。

这也是为什么业内普遍建议用自增整数或者UUID但转为二进制存储做为主键，而不是直接用长字符串。你拿一个64位的字符串当主键，每个二级索引都要背上这个64位字符串的空间和比较成本；换成8字节的BIGINT，成本和空间都大幅下降。数据量越大，这个差距越明显。

还有一个实操中的常见问题：没有显式主键怎么办。InnoDB的规矩是，你定义了主键就用主键，没定义就看有没有非空唯一索引，再没有就自动生成一个隐式的6字节RowId。这个隐式RowId对我们是不可见的，意味着你无法在业务上直接用主键查询。我建议每张表都手动指定主键，不仅是因为聚簇索引的数据组织需要，更是因为在做主从复制、数据归档、批量删除的时候，没有明确主键会让你寸步难行。

2.3 联合索引与最左前缀原则

联合索引是索引设计里最核心也最容易出问题的部分。

所谓联合索引，就是多个字段共同组成一个索引，比如idx_user_status(user_id, status)。它的底层结构仍然是B+Tree，但排序的规则是先按第一个字段排，再按第二个字段排，依次类推。

这就引出了最左前缀原则：查询条件里必须包含联合索引的最左字段，这个索引才可能被用到。更准确地说，查询条件里用到的字段，必须是从联合索引最左字段开始的一个连续子集。

举个例子，索引是(user_id, status, create_time)：

• WHERE user_id = 1—— 命中，用第一列。
• WHERE user_id = 1 AND status = 2—— 命中，用前两列。
• WHERE status = 2—— 无法命中，因为跳过了最左字段。
• WHERE user_id = 1 AND create_time > '2024-01-01'—— 只能用到user_id这一列，create_time的排序在当前索引下没有意义，因为中间断了status。

很多人不理解最后一条。我打个比方：你把一批数据先按“班级”排好，再按“身高”排好，再按“体重”排好。现在有人问“一班里体重超过120斤的人是谁”，你可以先筛一班，但“体重”这个条件的排序是在“身高”之后生效的，跳过身高直接按体重查，相当于你还是得在一班范围内挨个翻。这就是为什么中间断列的查询无法利用后续字段。

联合索引的字段顺序设计，基本遵循两个原则：

1. 高频等值查询字段放前面。等值查询（=）能最大程度利用索引的定位能力。
2. 范围查询字段放后面。范围条件（>、<、BETWEEN）会中断索引后续字段的利用，所以把范围字段放最后，前面的字段还能继续走索引。

这里有个大误区需要纠正：很多人以为把选择性最高（区分度最大）的字段放最前面就好。这个说法在纯等值查询时对，但如果既有等值又有范围，优先保证等值字段命中索引更重要。举个例子，(user_id, status)里user_id等值查询，status是范围条件，这个顺序就是合理的；但如果反过来(status, user_id)，user_id等值查询时因为status的B+Tree排序在前，user_id这个条件反而用不上索引的二分定位了。

3. 索引设计的关键细节与实操要点

3.1 区分度：建索引前先算一笔账

聊完类型和结构，真正动手设计索引时，第一件事不是拍脑袋决定建哪个字段，而是先算这个字段的区分度。

区分度的计算很简单：

-- 一个字段的去重值与总行数的比值 SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

这个比值越接近1，说明字段的每个值几乎只对应一行，索引定位的能力越强。比值越低，说明这个字段重复值太多，建了索引也要扫很多行。

我见过最典型的反面案例：给一个性别字段（只有0和1）建索引，区分度0.000002。查询时即使走了这个索引，每次也还是能扫出一大片数据，索引的价值趋近于零。当然这也不是说区分度低就一定不能建，得结合具体的查询模式来判断。比如你要查“所有状态为2的用户”，如果状态为2的数据占比只有千分之一，那索引还是有价值的，它帮你把扫描范围缩小了1000倍。但如果占比是一半，全表扫描反而可能更快。这个判断就需要用EXPLAIN的实际结果来验证，别光靠感觉。

极端情况下还有一种策略叫反向查询——比如“查询状态不为2的数据”，MySQL走索引时会在索引里找所有不等于2的键，这往往比全表扫描还慢。这时候不如直接WHERE status = 0 OR status = 1，或者干脆不建这个索引，让优化器自由发挥。

3.2 前缀索引：长字段怎么建才不浪费

对VARCHAR(255)以上的长文本字段，尤其是像URL、文章标题、订单号前缀这种，直接建整个字段的索引会有两个问题：一是索引体积膨胀，二是比较成本变高。解决办法是前缀索引，只对字段的前N个字符建索引。

ALTER TABLE article ADD INDEX idx_title_prefix (title(20));

那N怎么选？也是看区分度。实践里最常用的方法：分别统计不同前缀长度的区分度，找一个跟全字段区分度接近，同时长度又不太大的值。

SELECT COUNT(DISTINCT LEFT(title, 5)) / COUNT(*) AS sel5, COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(title, 15)) / COUNT(*) AS sel15 FROM article;

一般做到前缀区分度和全字段区分度的差值在1%以内，长度就算是找着了。前缀索引的代价是：它不能用于ORDER BY和GROUP BY，也不能用于覆盖索引。因为索引里只存了前N个字符，无法还原完整字段做排序和分组，select出来的数据必须回表取完整值。所以业务上如果频繁需要按这个字段排序，前缀索引就不合适。

3.3 覆盖索引与索引下推：两个容易被低估的优化

前面说了，二级索引查到主键后要回表才能拿完整数据行。覆盖索引就是想办法让查询需要的所有字段都在索引里，从而省掉回表这一步。

举个例子：

SELECT user_id, status FROM user_order WHERE user_id = 1;

如果存在idx_user_id_status(user_id, status)这个联合索引，查询只需要user_id和status两列，而这两列都直接包含在索引的B+Tree里，MySQL直接遍历索引就能拿到结果，不需要回表。这在高频查询场景下，省掉的可是海量的随机IO。

怎么判断有没有用上覆盖索引？看EXPLAIN里的Extra字段，如果显示Using index，就是索引覆盖生效了。如果显示Using index condition，则是另一个东西——**索引下推（ICP，Index Condition Pushdown）**在起作用。

索引下推是什么？还是用联合索引(user_id, status)举例。如果你查WHERE user_id = 1 AND status = 2，在没有ICP的旧版本里，MySQL只能先在索引里定位到所有user_id=1的记录，然后逐条回表，再在回表后的完整行上判断status是否等于2。ICP的作用就是：把status=2的判断下沉到索引遍历的过程中，在索引层就把不满足条件的记录过滤掉，减少回表次数。

在EXPLAIN里看到Using index condition，基本可以判断ICP生效了。MySQL 5.6之后的默认行为就是开启ICP的，所以大多数场景下你不需要手动配置，但你要能认得它、理解它的意义，否则你会在分析执行计划时看懵。

3.4 什么时候不要建索引

不是所有字段都需要索引，也不是索引越多越好。我总结了几种明确不该建的情况：

• 数据量小且查询不频繁的表。几百行数据全表扫描的成本比维护索引的成本还低，建了纯属浪费空间。
• 写入频繁、查询极少的热点表。每次INSERT、UPDATE都要维护所有索引的B+Tree结构，索引越多，写入越慢。
• 区分度极低的字段。上面说过的性别、状态这类字段，如果没有配合高频过滤需求，建了基本没用。
• 频繁更新的字段。索引的更新跟数据更新是同步的，字段改一次，索引就要动一次，性能开销叠加。

核心思想是：索引是拿“写入/更新的开销+存储空间”换“查询速度”。你能接受的查询性能底线越高，越不需要额外建一堆索引。很多刚入行的人容易走极端，把能见的字段全建一遍索引，最后表空间膨胀不说，压测一跑写入直接崩。

4. 慢查询排查与EXPLAIN实战

4.1 EXPLAIN核心字段到底怎么读

遇到慢查询，第一件事不是猜，是看执行计划。习惯用工具的同学可以用可视化平台或者IDE的图形化执行计划，但命令行方式必须会读，因为线上环境很多时候只能用命令行排查。

EXPLAIN SELECT order_no, amount FROM user_order WHERE user_id = 1 AND status = 1 ORDER BY create_time DESC LIMIT 100;

输出会有一堆字段，最关键的五个：

type=ALL是第一个红灯，说明没有走索引。但type=index也要注意，它表示全索引扫描——遍历了整个索引树，本质上和全表扫描差不多，只是扫描的是索引而不是数据行。有些场景下，因为索引比数据小，这种扫描会比全表快，但绝对算不上高效。

Extra里的Using filesort是另一个高频红灯。MySQL发现无法利用索引的有序性来完成ORDER BY时，就会在内存或磁盘里额外做一次排序。数据量一大，这个排序成本很可能超过查询本身。解决办法通常是让ORDER BY字段的排序方向跟索引排序方向一致，或者把排序字段加入到联合索引中。

4.2 索引失效的经典场景

这部分是实践中最容易踩雷的，我把高频场景都列出来，每个都是我实际遇到过的：

场景一：对索引列使用函数或表达式

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

这个写法看起来合理，但因为在create_time上套了DATE()函数，索引直接失效。正确的做法是把条件改写为范围查询：

SELECT * FROM user WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00';

场景二：隐式类型转换

SELECT * FROM user WHERE phone = 13800138000;

phone字段如果是VARCHAR类型，拿数字去比较，MySQL会先把字段转成数字再比较，索引就失效了。一定要保证两边类型一致。

场景三：LIKE前缀模糊查询

SELECT * FROM article WHERE title LIKE '%关键字%';

前面带%的模糊查询，因为无法确定关键字的起始位置，索引的B+Tree排序完全派不上用场。只有LIKE '关键字%'这种前缀匹配才能用上索引。

场景四：OR连接的非索引条件

SELECT * FROM user WHERE name = '张三' OR age = 30;

假设name有索引，age没有。因为中间用了OR，MySQL必须同时处理两种情况，无法只走name索引。把OR拆成两个查询再UNION，或者确保OR两边的列都有索引。

场景五：联合索引不满足最左前缀

前面已经讲过，这里再强调一遍。查询条件里如果没有联合索引的最左字段，整个索引直接废掉。这个是最常见的“建了索引但还是慢”的元凶。

场景六：优化器选择不索引

这也是最容易让人困惑的情况：字段有索引、条件也匹配、就是索引没用上。原因往往是优化器评估后发现走索引还要回表太多行，全表扫描更快。数据分布偏斜时尤其明显，比如查询条件命中了表中30%的数据，优化器大概率选择全表。这时候可以尝试用FORCE INDEX引导，但根本上还是要优化索引设计或者改写SQL。

4.3 从执行计划反推索引设计

看执行计划的最终目的，不是看懂就完了，而是反过来指导索引改造。

分享一次完整的排查过程。某公司的订单查询页面，用户输入时间范围拉取订单，SQL长这样：

SELECT * FROM orders WHERE merchant_id = 123 AND create_time >= '2024-03-01 00:00:00' AND create_time < '2024-03-02 00:00:00' ORDER BY id DESC LIMIT 50;

第一版执行计划：type=ALL，rows=800万，Extra里有Using filesort。看索引结构，表上只有一个idx_create_time。

问题很明显：merchant_id没有索引，create_time单列索引只能过滤时间范围，之后还要在范围内逐行比对merchant_id，800万行里捞。ORDER BY id又没法用create_time索引的有序性，只能filesort。

改造方案：

ALTER TABLE orders ADD INDEX idx_merchant_time(merchant_id, create_time);

再EXPLAIN一次：type=ref，rows从800万降到几千，Extra里的filesort也消失了。为什么？因为联合索引(merchant_id, create_time)在B+Tree里先按merchant_id排好，内部再按create_time排好，查询直接定位到merchant_id=123这一块，然后在时间里二分定位，跨天边界就是一个范围扫描。ORDER BY id的排序无法走索引，但因为扫描行数从800万降到了几千，filesort的成本变得可忽略。

这就是从执行计划反推索引设计的标准路径：先定位type和rows的问题，再判断是索引缺失还是索引顺序不对，最后用联合索引覆盖查询条件。每一步都有执行计划做依据，不是拍脑袋。

5. 索引维护与常见问题排查实录

5.1 索引碎片：为什么索引越来越慢

时间长了你会发现，一个原本很快的查询，某天开始变慢了。其中一个容易被忽视的元凶是索引碎片。

InnoDB的B+Tree在频繁的删除和随机插入下，叶子节点会出现页分裂和页合并，物理存储不再连续，逻辑上相邻的数据在磁盘上不一定相邻。这就导致本该是一次顺序IO的范围扫描，退化成多次随机IO，速度自然下降。

怎么判断索引碎片化程度？可以查表的统计信息：

SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

对比一下业务数据和索引数据的大小比例，如果索引占用的空间明显超出合理范围，碎片化可能已经比较严重了。更直接的方法是看表的ROW_COUNT和DATA_LENGTH的比值是否异常。

处理办法是重建表或者整理索引碎片：

ALTER TABLE your_table ENGINE = InnoDB;

这个操作会重建整张表和所有索引，还原连续存储。注意，这个操作在表数据量大时会有锁表风险，还是要选择在业务低峰期执行。线上批量删除数据时，删除完一大片旧数据后，及时做一次类似操作是值得考虑的。但我得强调一点：不是每次删除都要重建表，频繁重建本身也会带来性能抖动。一般是在删除了大量行（比如超过总数20%）之后再做。

索引统计信息的过期是另一个“越来越慢”的假象来源。MySQL的优化器是基于统计信息估算行数的，如果统计信息长期没更新，优化器可能做出错误的执行计划选择。可以用ANALYZE TABLE刷新统计信息（只统计索引值分布，不重建索引，成本低很多）：

ANALYZE TABLE your_table;

5.2 线上踩过的坑：一次真实排查全记录

有一次，业务方反馈一个报表查询突然变慢了。检查过程是这样的：

第一步，用慢查询日志抓出SQL，发现慢SQL运行时长从100毫秒涨到5秒。

SHOW VARIABLES LIKE 'slow_query_log%';

第二步，EXPLAIN看了执行计划，type从ref变成了ALL。奇怪的是表结构和索引都没变过。这就很蹊跷。

第三步，检查数据分布。发现这个表按天跑批导数据，某天导入的量突然暴增，查询条件命中了超过30%的行，优化器自动改选了全表扫描。这个案例的关键点在于：索引没变，数据分布变了，优化器的判断就会跟着变。

怎么解决？两个方向：一是SQL上用FORCE INDEX或者改写查询让优化器回到原来的选择，二是在数据量暴增后重建索引和统计信息，让优化器拿到最新的分布数据。我们最后选择了后者，ANALYZE TABLE之后执行计划恢复正常。顺带也说明了：优化器的“聪明”是建立在统计信息上的，统计信息失真，它也会“犯傻”。

另一个坑是在长事务里执行DDL。某次调整索引结构，直接在生产库上跑ALTER TABLE，结果因为表上有长时间未提交的事务，MDL锁被卡住，后续所有查询全部阻塞。这是个经典问题：ALTER TABLE在更改表结构时需要拿MDL锁，但如果有事务一直持有该表的元数据锁，ALTER会一直等待，后面的所有DML就全堵住了。

我现在的习惯是：任何生产环境的结构变更，先看有没有长事务，再用专门的在线DDL工具或者低峰期窗口来操作，绝不手痒直接在生产库上跑。这一点值得所有经历过线上事故的人重视。

5.3 索引设计自查清单

最后整理一份自查清单，我每次做完索引设计或者排查完慢查询都会过一遍：

6. 一点个人体会：索引是设计出来的，不是堆出来的

经常有同学问我：到底怎么才能把索引学好？我的体会是，索引不是一个“建了就完”的一次性操作，它是一个需要持续观察、反复修正的设计过程。你永远不能脱离具体的SQL和执行计划来谈索引优化，理论学得再好，最后都得到EXPLAIN面前去接受检验。

我个人对索引的学习路径建议是：先搞懂B+Tree的物理结构和聚簇索引、二级索引的差异，这是地基；再花时间把EXPLAIN的每一个字段吃透，这是工具；然后积累索引失效的经典场景，这是经验；最后形成一套自己的索引设计检查和变更规范，这是方法论。四步走完，基本很难有人在索引问题上把你问倒。

实战里还有一个小建议：建索引的时候顺手在表注释或者需求文档里写一句“为什么建这个索引、覆盖什么查询场景”。三个月后你回来看，会感谢当时那个写注释的自己。索引不是越多越好，数据库里每多一个索引，都是拿写入性能和磁盘空间换来的，别让未来的自己为今天的贪多买单。