企业官网建设流程全解析

先说个难受的事。

上个月我们线上一个订单查询接口，单表三百万行数据，明明相关字段都加了索引，可平均响应时间却从平时的 80ms 一路涨到了 2.8 秒。DBA 拉出慢查询日志一看，里面有十几条 SQL 的索引压根没生效。翻建表语句的时候我发现，三个月前我亲手建的两个索引，一个字段类型和查询条件匹配不上，一个被函数包住了，等于白建。

这篇文章把我这些年踩过的 5 个 MySQL 索引坑全部整理出来，每个坑都附上了生产环境验证过的解决方案。不管你是刚入门的后端开发，还是已经带项目的架构师，只要你的业务离不开 MySQL，这几条经验应该都能帮你省下几顿加班宵夜的冤枉钱。

1. 先说结论：索引不是越多越好，也不是随便建就行

1.1 索引的底层逻辑（B+树到底好在哪）

很多人建索引就是凭感觉：查询慢了，找个字段加上索引，发现没效果，再加一个。我早期就是这么干的，结果索引没少建，慢查询也没见少。直到认真把 InnoDB 的索引结构理清楚，才明白问题出在哪。

InnoDB 的索引底层是 B+ 树。你可以把它想象成一本新华字典的目录：非叶子节点只存“页码范围”，不存具体内容，这样一层层往下找，最终在叶子节点拿到完整数据。B+ 树的优势在于矮胖，三层就能容纳上千万条记录，每次定位只需要 3 次磁盘 IO 左右，这是它比哈希索引更适合范围查询和排序的根本原因。

InnoDB 里有两类索引：聚簇索引（主键索引）和二级索引（普通索引/联合索引）。聚簇索引的叶子节点直接存整行数据；二级索引的叶子节点只存索引字段和主键值。当你的查询条件命中了二级索引，但需要返回的列不在索引里，MySQL 就得拿着主键再去聚簇索引里查一次，这个动作叫回表。回表是性能杀手，后面第一个坑就跟它有关。

1.2 我踩坑之前是怎么建索引的

在建索引这件事上，我总结过三类典型的错误认知：

• 认为“只要查询慢，加索引就能解决”。其实加索引只是让“搜索”变快，如果你的 SQL 本身存在函数包裹、类型转换、非最左匹配等问题，索引一样不生效。
• 认为“索引越多越好”。每个索引都是一棵独立的 B+ 树，写入数据时全部要同步维护。索引多了，写入变慢、磁盘占用变高，还可能出现优化器选错索引的情况。
• 认为“反正都是等值查询，唯一索引和普通索引差不多”。区别主要在于：普通索引查到记录后还要继续向后扫描，直到碰到不相等的记录；唯一索引命中即停，因为唯一性保证了不会再有第二条。在重复率高的列上，唯一索引的等值查询通常会更快，代价是写的时候多一次唯一性检查。

这些坑，我踩过之后才慢慢总结出科学的建索引方法，后面第 7 部分我会统一整理成一套可以直接照着做的流程。现在先把最伤性能的 5 个坑逐个讲透。

2. 坑一：SELECT * 让覆盖索引白建了

2.1 回表到底有多伤

我接手过一个统计接口，逻辑很简单：查一张用户行为表，按 user_id 过滤，按 create_time 排序，再取最近 20 条。表里已经建了一个联合索引idx_user_time(user_id, create_time)。按道理说这个索引完全够用，可接口就是慢，要 1.6 秒。

看代码的时候我差点没骂出来——SQL 是这么写的：

SELECT * FROM user_action_log WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 20;

问题就出在SELECT *上。idx_user_time(user_id, create_time)的叶子节点里只有 user_id、create_time 和主键 id。你要返回表里所有列，MySQL 只能针对命中的每一行都回表一次，去聚簇索引拿完整数据。命中 500 行就回表 500 次，命中 3 万行就回表 3 万次。即使索引能快速定位到第一个符合条件的位置，后续的每次回表都是随机 IO，成本极高。

更麻烦的是，SELECT *把不需要的大字段（比如一个 10KB 的 content 字段）也全部捞了出来，网络传输和内存排序的开销全被放大了。

2.2 生产级解决方案：覆盖索引 + 明确列

这个问题的修复可以说分两步走。

第一步，把SELECT *改成只查业务需要的列。比如列表页只需要 id、user_id、action_type、create_time，那就写清楚这四列。

第二步，把联合索引扩展成覆盖索引。所谓覆盖索引，就是查询要返回的列全部包含在索引的叶子节点里，这样 MySQL 在二级索引上就能拿到全部数据，连回表都省了。当时我把索引调整成了idx_user_action_time(user_id, action_type, create_time)，SQL 改成只查这三列加 id，执行计划里 Extra 从Using index condition变成了Using index，接口直接从 1.6 秒掉到了 0.2 秒。

这里有一条经验供你参考：高频查询里出现了“查 A 表，按 B 字段过滤，返回 C 字段”这种固定模式，就可以把 (A, B, C) 建成联合索引，让一次查询完全在索引里完成。这就是典型的覆盖索引优化。

注意：覆盖索引也不是越多越好。如果把大字段（比如 text、blob）也塞进索引，索引体积会急剧膨胀，写入性能和存储成本都会恶化。一般只把高频返回的小字段纳入覆盖索引。

3. 坑二：索引失效的五个日常操作

3.1 失效场景逐个数

索引建好了，不等于它一定会被用到。哪怕你索引设计得再合理，SQL 写法不对照样失效。下面这五个场景，全是线上真实出现过的。

场景一：隐式类型转换

表中 mobile 字段是 varchar 类型，但代码传参时传了一个整数：

SELECT * FROM user WHERE mobile = 13812345678;

MySQL 的隐式类型转换规则是：当字段类型是字符型、传入值是数值型时，MySQL 会对字段列做 CAST，也就是把 mobile 转成数字再比较。一旦对索引列做了函数或运算，索引就失效了。这种问题用 EXPLAIN 一眼就能看出来——type 是 ALL，全表扫描。

解决方式很简单：传参保持字符串类型，或者干脆在 SQL 里写死引号：

SELECT * FROM user WHERE mobile = '13812345678';

场景二：函数/表达式包裹索引列

统计某天注册用户数，最常见的错误写法：

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

DATE()函数对 create_time 做了处理，索引自然失效。改成范围查询就能用到索引：

SELECT COUNT(*) FROM user WHERE create_time >= '2024-11-11 00:00:00' AND create_time < '2024-11-12 00:00:00';

这条规则适用于所有函数：LOWER(column)、YEAR(column)、LEFT(column, 5)、CONCAT、SUBSTRING等等。索引列上出现函数，基本等于让索引报废。

场景三：左模糊查询

LIKE '%keyword'这种写法，因为不知道匹配的起点在哪，B+ 树无法按序定位，索引失效。但LIKE 'keyword%'这种前缀匹配是能走索引的，因为 B+ 树支持范围扫描。

业务上如果必须要模糊搜索，可以考虑把需求从“匹配任意位置”转成“匹配前缀”，或者引入独立的全文检索组件，不要在千万级大表上硬扛。

场景四：OR 连接非索引列

SELECT * FROM order_2024 WHERE status = 3 OR remark = 'VIP';

如果 remark 字段上没有索引，MySQL 就只能把所有status=3的行都查出来，再全表扫描判断 remark 条件，这个过程没法高效利用索引。把 OR 改写成 UNION ALL，或者给 remark 也加上索引，都能解决。

场景五：联合索引违反最左前缀原则

联合索引(a, b, c)的匹配顺序是从左到右的。你直接查b = 1 AND c = 2，跳过了 a，索引就无法使用。很多人踩这个坑是因为不清楚联合索引的字段顺序直接影响可用性，后面第 4 部分排序优化里我会再展开。

3.2 现场排查技巧：EXPLAIN 看 type 和 key

我以前排查索引失效，全靠肉眼看 SQL，效率低还容易漏。后来养成了一个固定习惯：任何慢 SQL，先 EXPLAIN 再看执行计划。

EXPLAIN 输出里有几个核心字段：

• type：访问类型。从好到差大致是const → eq_ref → ref → range → index → ALL。如果看到ALL，意味着全表扫描，索引失效或没有索引。
• key：实际用到的索引。如果为 NULL，说明没走索引。
• rows：预估扫描行数。这个数字越大越危险。
• Extra：Using filesort代表排序没用上索引；Using index代表覆盖索引；Using index condition代表走了索引下推，但还需要回表。

我遇到过一次很经典的排查：开发同学反馈某查询很慢，EXPLAIN 一看，type=ALL，rows=两百多万，key=NULL。再查表结构发现 user_id 字段是 varchar，但 SQL 里用了WHERE user_id = 123456。改成WHERE user_id = '123456'之后，type 变成了 ref，查询直接降到毫秒级。

提醒：EXPLAIN 是只读操作，不影响数据，线上可以直接执行。但要注意它展示的是优化器“预估”的执行计划，极端情况下实际执行会有偏差，可以再用EXPLAIN ANALYZE（MySQL 8.0+）看真实执行数据。

4. 坑三：ORDER BY 排序慢，filesort 背锅还是索引背锅

4.1 filesort 是怎么发生的

分页接口排序慢，是索引优化里特别容易被忽略的一环。MySQL 的排序有两种方式：一种是利用索引天然有序的特性直接顺序读取，另一种是先把数据查出来再排序，后者就叫 filesort。

filesort 不一定发生在磁盘文件里，数据量小的时候在内存 sort buffer 就能完成，但不管怎样，它都要额外的 CPU 和内存开销。数据量大到超过sort_buffer_size时，MySQL 会用临时文件做归并排序，产生大量的磁盘 IO，这时候性能就会断崖式下跌。

什么情况下会触发 filesort？

• ORDER BY的字段不在索引里；
• ORDER BY字段顺序和联合索引顺序不一致；
• 升降序方向和索引定义不一致（MySQL 8.0 之前索引只支持升序存储）；
• 查询里有范围条件，范围之后的排序字段无法继续利用索引。

4.2 联合索引排序优化实战

这是一个之前优化过的真实案例。订单表 orders 有一个高频查询：

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

原来的索引是idx_user(user_id)。查询流程是：先用 user_id 捞出该用户所有订单，再按 create_time 在内存里排序，最后取 50 条。这个用户订单有 8 万多条，每次查询都要先把 8 万行读出来排序，耗时 800ms 左右。

我把索引换成了联合索引：

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

关键点在于字段顺序：等值条件字段放前面，排序字段放最后。user_id = 8888 AND status = 1是两个等值过滤，create_time是排序字段。联合索引排好序后，MySQL 可以在索引里直接按序扫描，取够 50 条就停，完全不用再排序。优化后查询耗时降到了 45ms。

还有几个细节值得注意：

• 范围查询会中断联合索引的排序连续性。比如WHERE user_id = 8888 AND create_time > '2024-01-01' ORDER BY status，create_time 是范围条件，它后面的 status 字段就用不上索引排序了。所以设计联合索引时，要把范围条件能过滤掉大部分数据的字段放前面，排序字段往后放，但别放在范围字段后面。
• 升降序问题。MySQL 8.0 之前，索引默认按升序存储，如果 ORDER BY 是降序，MySQL 可能仍然无法直接用索引，需要反过来读或 filesort。8.0 开始支持降序索引，如果你的业务高频场景是ORDER BY xxx DESC，可以用ALTER TABLE ... ADD INDEX idx_name(col DESC)来优化。
• 分页越深越慢的问题。LIMIT 1000000, 20这种写法，MySQL 得先扫 100 万行再扔掉，即使有索引也一样慢。常见解法是记录上次查询的最大 id（或者排序字段的值），下次查询带上WHERE id > 上次最大id ORDER BY id LIMIT 20，这叫“基于游标的分页”，线上效果明显。

5. 坑四：二级索引更新时的死锁时间窗（重点！）

5.1 加锁顺序复盘：先锁二级索引项，再回表锁主键

这个是并发场景下很容易踩的深坑，我专门把它单拎出来讲，因为网上能查到的资料不多，但一旦踩中，线上就会直接报Deadlock found when trying to get lock。

先交代背景：InnoDB 的行锁是基于索引的。当一条 UPDATE 语句通过二级索引定位行的时候，加锁过程不是一步到位的，而是：

1. 先在二级索引对应的记录上加 X 锁；
2. 再回表到聚簇索引，对主键记录加 X 锁。

这个分两步的加锁顺序，在并发事务里会形成交叉等待。

举个例子。表结构如下：

CREATE TABLE t ( id INT PRIMARY KEY, c INT, INDEX idx_c (c) ) ENGINE=InnoDB;

表里有两条数据：(1, 100)和(2, 200)。

事务 A 执行：

UPDATE t SET c = 200 WHERE c = 100;

事务 B 执行：

UPDATE t SET c = 100 WHERE c = 200;

按我前面说的加锁顺序拆解一下：

• 事务 A 先给二级索引idx_c中 c=100 的索引项加锁，然后回表给主键 id=1 的记录加锁；
• 事务 B 先给二级索引idx_c中 c=200 的索引项加锁，然后回表给主键 id=2 的记录加锁。

此时还没问题。但 A 执行完 UPDATE 后要更新 c 的值，它需要去改 c=200 这个索引项的数据，而 c=200 的索引项已经被 B 锁住了；B 同样需要改 c=100 的索引项，而这个被 A 锁住了。于是 A 等 B，B 等 A，死锁形成。

如果把两个事务执行的顺序稍微打乱，比如 A 锁了二级索引 c=100 后准备回表，B 已经在等主键 1 的锁，同时锁住了二级索引 c=200，A 又要锁 c=200 的索引项，交叉就出现了。这个窗口非常小，但高并发下出现的概率并不低。

5.2 复现与解决方案

我当时是在压测环境第一次碰到这问题的。日志里每隔几分钟就会出现一次死锁，应用层报错“transaction rollback”，用户重试几次又能成功，但核心链路的成功率被拉下来一大截。

要复现这个场景也很简单：开两个 MySQL 终端，按上面两个 SQL 的顺序分别执行，大概率能复现死锁。执行完SHOW ENGINE INNODB STATUS\G，在LATEST DETECTED DEADLOCK部分能看到两个事务互相等待的信息，关键是看LOCK WAIT锁的对象是哪个索引。

解决方案我整理了三条，按优先级排列：

1. 统一加锁顺序：如果业务里对同一组数据有多个更新路径，尽量保证所有事务都以相同的顺序访问索引和主键。比如都先通过主键 id 更新，就能直接锁主键，完全绕开二级索引回表这一步。
2. 用主键更新代替二级索引更新：业务允许的话，先SELECT id FROM t WHERE c = ?，拿到主键后再UPDATE ... WHERE id = ?，这样加锁路径只有聚簇索引，死锁窗口直接消失。
3. 死锁重试机制：即使做了前两条，死锁也不能 100% 避免。应用层要捕获Deadlock found异常，做有限次重试（通常 3 次，加 500ms 随机退避）。不要把数据库死锁当成程序 bug 一直抛给用户，它只是并发写的一种正常现象。

注意：InnoDB 的死锁检测默认是开启的（innodb_deadlock_detect=ON），发生后会自动回滚代价较小的事务。但在高并发场景下，死锁检测本身也有性能开销，如果业务对写入性能极其敏感，可以评估关闭死锁检测并靠锁等待超时兜底，但这种操作要谨慎，建议先压测验证。

6. 坑五：重复索引和冗余索引

6.1 冗余索引的危害

很多人（包括曾经的我）建索引时根本不做整体规划，今天发现a字段慢加个(a)，明天发现(a, b)查询也慢再加一个(a, b)。结果(a)这个索引就是完全多余的——因为(a, b)索引的最左前缀已经能覆盖所有查询a = ?。

这种索引叫冗余索引。危害是实打实的：

• 写放大：表里每多一个索引，INSERT、UPDATE、DELETE 都要多维护一棵 B+ 树。在高写入的业务里，冗余索引会让吞吐量明显下降。这还不是最要命的——MySQL 提交事务时，索引页的变更要写 redo log，索引越多，日志量越大，刷盘压力越高。
• 占用存储空间：每个索引都是物理文件上的 B+ 树。一张表多几个冗余索引，几千万行的表能多占用几个 GB 的空间。
• 优化器选择困难：索引太多会让优化器“挑花眼”，估算成本时可能选错索引，导致本来应该很快的查询反而走了低效的执行计划。

6.2 生产级排查 SQL

排查冗余索引有一套现成的办法。MySQL 5.7+ 自带的 sys 库有视图schema_redundant_indexes，可以直接查：

SELECT * FROM sys.schema_redundant_indexes\G

如果 sys 库不可用，也可以自己从information_schema.STATISTICS里统计。核心逻辑就是：如果一个索引的字段序列是另一个联合索引字段序列的前缀，那前者大概率是冗余的。比如索引(a)就是(a, b)的前缀索引。

我处理过一个真实案例：一张 2000 万行的日志表里有 12 个索引，其中 5 个是冗余的。通过 sys 视图确认后，在测试环境删掉了 4 个（保留了一个因为单独有降序需求），线上 INSERT 的 p99 延迟从 180ms 降到了 130ms，差不多提升了 28%。并且所有 SELECT 查询性能没有任何退化。

这里一定要加一句警告：删索引之前至少观察两周的慢查询日志，确认没有业务 SQL 依赖这个索引。而且要在低峰期操作，用ALTER TABLE ... DROP INDEX会锁表，MySQL 5.7 之前可能阻塞读写，8.0 虽然优化了 DDL，但大表操作依然要小心，建议用工具或者在从库上先演练。

另外可以从工具层面辅助检查。Percona Toolkit 里的pt-duplicate-key-checker可以批量扫出重复索引、冗余索引，生成删除建议。我团队从 GitLab 转到自建 MySQL 后，每季度用这个工具做一次索引健康巡检，每次都能揪出几个不该建的索引。

7. 索引设计的通用方法论与速查表

7.1 索引设计六步法

踩了这么多坑之后，我总结了一套建索引的标准作业流程，现在团队里新同学我都要求按这个来：

1. 从慢查询日志开始。不要拍脑袋建索引，先看slow_query_log里哪些 SQL 耗时最多、执行频率最高，这些才是优化的重点。
2. 分析查询模式。确认每条 SQL 是等值查询、范围查询、排序还是分组，对应的索引设计完全不同。
3. 检查字段区分度。区分度太低（比如性别、状态这种枚举值很少的）就不值得单独建索引。估算区分度的 SQL 是：

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

这个值越接近 1，索引选择性越好。低于 10% 的字段单独建索引收益很低，除非是联合索引里配合高区分度字段一起用。

4. 按最左前缀设计联合索引。把等值查询的字段放在前面，范围查询字段放中间，排序字段放最后。一个联合索引能覆盖多条 SQL，比拆成多个单列索引经济得多。
5. 用 EXPLAIN 验证。重点看 type、key、rows、Extra 四个字段，确保没有ALL和Using filesort。
6. 上线后观察指标。别建完索引就不管了。注意监控慢查询数量、锁等待时间、写入延迟，数据量翻倍后再回头看看索引是否依然高效。

7.2 常见问题速查表

我把前面聊到的问题整理成一张速查表，方便你直接对号入座：

说实话，这些坑我基本都真金白银地交过学费。有些是上线后半夜爬起来做紧急优化，有些是压测时突然冒出来的死锁告警，还有一次是因为一个脑门一热加上的索引，导致本来好好的写入路径慢了两倍。现在回头看，绝大多数问题其实都源于没有把索引当成“一个需要持续设计的系统”，而只是当成“一个加完就完事的补丁”。

我个人的操作习惯是：每个业务表都单独维护一份索引设计文档，写明这个表主要有哪些查询模式、对应哪个联合索引、为什么这样设计、有没有冗余待清理。每次大促前，团队还会带着慢查询日志做一轮索引 review，把新增的 SQL 和现有索引逐一比对，该调整的调整，该删的删。这套流程看着笨，但确实让我在过去一年里，几乎没有再因为索引问题被半夜叫醒过。