企业官网建设流程全解析

在MySQL的日常使用里，索引大概是最容易被低估的东西。我刚接触数据库时也以为建索引就是把几个字段加上，直到线上一条本来毫秒级的查询突然变成几百毫秒甚至秒级，才真正意识到索引设计不是简单的“加个B+树索引”就能了事。这篇文章我会把索引从原理到优化的关键环节都串起来，结合我实际排查慢SQL的经验，说清楚什么时候该建索引、什么时候索引会失效、怎么用EXPLAIN判断问题，以及深分页、排序、覆盖索引这些高频场景到底该怎么处理。无论你是刚入门的学生还是写了好几年业务代码的开发，这篇文章都能给你一套可落地的排查思路。

1. 索引不是万能的：先搞清楚它到底解决了什么问题

1.1 一条慢SQL引发的排查：B+树为什么能快

有一次线上告警，某个订单查询接口平均耗时从30ms涨到800ms，查了下慢日志，发现是这么一条语句：

SELECT id, order_no, user_id, amount FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 20;

orders表当时数据量已经到2000万行，而user_id这一列根本没有索引。在没有索引的情况下，MySQL只能对整张表做全表扫描，逐行比对user_id，再把命中的结果放入临时文件排序，最后才取20条返回。2000万行数据全部扫描一遍，再排序，时间自然就上去了。

加了一个普通索引之后问题立刻缓解：

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

添加索引后，查询先通过B+树定位到user_id为12345的所有记录。B+树的查找复杂度是O(log N)级别，2000万行数据大概也就二十几次磁盘I/O就能定位到目标。如果索引叶子节点上还包含了create_time，那排序也能省掉。这就是索引最核心的作用：把全表遍历变成树上的快速定位，顺便还能辅助排序和分组。

1.2 索引选型背后的取舍

索引不是建得越多越好。每个索引在写入数据时都要额外维护一棵B+树，插入、更新、删除的成本都会增加。而且索引还占用磁盘空间，InnoDB里每一个二级索引都是一套完整的B+树结构，数据量大了以后空间开销非常明显。

真正到生产环境我一般遵循几个原则：

• 区分度高、经常出现在WHERE条件里的列，优先考虑索引
• 出现在ORDER BY、GROUP BY、DISTINCT子句里的列，也值得加索引，因为B+树本身有序，可以直接利用这个有序性避免额外排序
• 频繁更新的字段要谨慎加索引，写多读少的表，索引带来的查询提速可能远小于写入代价
• 区分度极低的列，比如性别、状态这类只有两三个枚举值的字段，单独建索引几乎没有意义，优化器可能直接放弃走索引

所以索引设计本质上是个权衡问题。核心不是“建了多少个索引”，而是“每个查询有没有合适的索引可用”。这个下一节具体展开。

2. 索引设计的几个关键决策：主键、联合索引与覆盖索引

2.1 主键索引的正确打开方式

InnoDB是聚簇索引组织表，数据行本身存储在聚簇索引的叶子节点上，聚簇索引的键就是主键。表没有显式主键时，InnoDB会找第一个非空的唯一索引来当聚簇索引，找不到就自动生成一个隐藏的rowid，也就是内部6字节的自增列。

所以建议每个InnoDB表都显式定义一个主键。主键的选择优先顺序依次是：自增整型主键、业务天然唯一的整型字段、UUID（不太推荐）。

如果用自增主键，新插入的数据总是在B+树的末尾追加，维护成本很低。反过来，如果用UUID这类随机字符串做聚簇索引，每次插入都需要在B+树中间某位置执行节点分裂，页分裂带来额外的碎片和写放大，时间长了表的性能和空间利用率都会明显下降。

二级索引的叶子节点存储的并不是数据行本身，而是主键值。这意味着通过二级索引查询时，要先在二级索引的B+树里找到主键，再拿着主键回聚簇索引去查完整数据行。这个过程叫回表。回表本身有一次额外的磁盘I/O，查询结果集很大时，回表次数也会很多，这时就要考虑覆盖索引。

2.2 联合索引和失效场景

联合索引是优化查询频率很高的复合条件。联合索引的排列原则是“最左前缀”：MySQL会从联合索引的最左列开始匹配，中间不能断。比如建了(a, b, c)联合索引，查询条件里如果只有b而没有a，这个索引就完全用不上。

另一个容易被忽视的问题是范围查询会中断后续字段的索引匹配。WHERE a = 1 AND b > 100 AND c = 5，在联合索引(a, b, c)中，a可以用来精确定位，b用来范围扫描，但c就不能继续利用索引了，因为b的范围结果集里c是无序的。所以建联合索引时，通常把等值条件的列放在前面，范围条件的列放在后面。

还有一个很典型的设计方式叫覆盖索引。覆盖索引是指查询需要的所有列都在索引里，不需要回表。比如有一条高频查询：

SELECT user_id, status FROM orders WHERE user_id = 123;

如果只建idx_user_id(user_id)，查询到user_id之后还要回表拿status。但建了idx_user_id_status(user_id, status)联合索引后，索引叶子节点上已经有了status值，优化器看到需要的列都在索引里，就会直接走索引返回结果，连回表都省了。对于大表和高频查询，覆盖索引往往比单纯加一个单列索引收益大很多。

3. 用EXPLAIN看懂MySQL的执行计划

3.1 explain关键字段速查

遇到慢SQL，我第一步永远是执行EXPLAIN，看执行计划里MySQL到底怎么跑这条语句的。这里分享一个我常用的字段速查表：

type字段是最直观的判断依据。至少要保证range级别，最好能到ref或const。如果看到ALL扫描，而且表本身很大，那这条SQL基本就是需要优化的目标了。

3.2 从type级别识别慢查询

我实际排查过一条查询，EXPLAIN结果里type是ALL，rows预估60多万行。表其实只有几万行，但由于WHERE条件里对索引列做了函数操作，索引就失效了。

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

create_time字段本身有索引，但DATE(create_time)套了函数之后，索引列被改变，优化器无法按原有顺序去B+树查找，只能放弃索引做全表扫描。改成范围查询之后：

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

这一步直接让type从ALL变成了range，rows从60万降到了几百。执行计划里的type变化是最直观的优化效果验证方式，完全不需要去猜。

再补充一个坑：隐式类型转换也会导致索引失效。常见的情况是有一个varchar类型的字段phone，查询时条件写成WHERE phone = 13800138000，MySQL会尝试把字符串转成数字去比较，相当于在索引列上做了隐式操作，索引照常失效。解决办法很粗暴，查询参数跟字段类型保持一致，或者用WHERE phone = '13800138000'。

4. 常见慢SQL优化实战案例拆解

4.1 深分页优化：LIMIT 100000, 20为什么越翻越慢

业务后台常见的分页接口，数据量一大就会出现“翻到后面几页明显变卡”的现象：

SELECT id, user_id, amount, status FROM orders WHERE create_time > '2024-01-01' ORDER BY id LIMIT 100000, 20;

这条SQL的痛点是LIMIT 100000, 20需要先把前100000行取出来，然后丢弃掉，只返回最后20行。即使走了主键索引，这100000次回表一次都跑不掉，越到后面代价越大。

一个比较常见的优化方案是延迟关联：先利用覆盖索引快速查到目标主键ID，再用主键ID去关联回原表拿整行数据。

SELECT o.id, o.user_id, o.amount, o.status FROM ( SELECT id FROM orders WHERE create_time > '2024-01-01' ORDER BY id LIMIT 100000, 20 ) t JOIN orders o ON t.id = o.id;

子查询里只查id列，可以在覆盖索引上完成排序和分页，避免大量回表。外层再用JOIN把只需要的20行完整数据取回来。这个方案在分页深度比较大的场景里实测提速非常明显。

如果业务场景允许，还可以用游标分页代替偏移分页。也就是前端记录最后一条的id，下一页查询带上WHERE id > 上次最大id，配合ORDER BY id LIMIT 20。这种方式没有offset的无效扫描，数据量再大也能稳定在毫秒级。代价是用户不能直接跳到任意页码，更适合滚动加载类的场景。

4.2 排序导致的filesort：为什么明明走了索引还是慢

还有一类慢SQL，走索引了但EXPLAIN的Extra里出现Using filesort，排序没有完全依赖索引的有序性，MySQL需要在内存或磁盘上自己做排序。数据量大时，这个排序过程可能比查询本身还慢。

之前优化过一个订单列表接口，条件语句是：

SELECT order_no, amount FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 50;

初始索引只有idx_status(status)，MySQL先通过status筛出数据，再对create_time排序。因为id并非连续，不能直接利用主键顺序，于是触发了filesort。优化方案是建联合索引idx_status_create_time(status, create_time)，让status等值过滤后，create_time天然就是有序的，排序直接省掉，Extra里的filesort也消失了。

这里有一个细节：联合索引设计的“等值条件放前面，排序字段放后面”原则，在这个场景就体现出来了。如果你反过来把create_time放前面，status放后面，那WHERE status = 1的时候就匹配不到这个联合索引，索引直接废掉。

4.3 前缀索引与索引下推

对于超长文本字段，比如URL、备注信息这类，直接对整个字段建索引会导致索引体积膨胀，而且索引树里的每一条都特别长。MySQL支持前缀索引，比如：

ALTER TABLE articles ADD INDEX idx_url_prefix (url(64));

只索引字段前64个字符。好处是索引体积小、建树快，缺点是有一定概率出现前缀相同导致额外回表判断。具体取多少长度，可以通过统计区分度来决定：

SELECT COUNT(DISTINCT LEFT(url, 64)) / COUNT(*) AS selectivity FROM articles;

区分度接近1说明64个字符基本能代表整列。

再提一个很多人不知道的优化点：索引下推，英文是Index Condition Pushdown，简称ICP。以前是WHERE条件里有索引覆盖不到的列时，必须在回表之后再过滤。开启ICP后，MySQL会在索引遍历过程中先把索引包含的列做一遍条件判断，只有满足条件的才回表。这个特性在MySQL 5.6之后默认开启，配合联合索引效果尤其明显。如果你的MySQL版本比较旧，还是建议升级，ICP对特定SQL的性能提升是实打实的。

5. 索引维护与避坑：回表、统计信息与索引失效的其他场景

5.1 为什么建了索引还是很慢

经常有这样的情况：索引建了，EXPLAIN也显示走了索引，但查询还是慢。第一个怀疑方向是回表次数太多。比如一个查询命中了索引范围，rows预估100万，那就要回表拿100万次数据，这种情况下即使走索引，代价也很高。

解决办法是把查询需要的列尽可能都放到二级索引里，形成覆盖索引。如果无法覆盖，就要考虑改变查询条件缩小命中范围，比如在时间维度上加过滤条件。

第二个要排查的是索引统计信息过期。优化器决定是否走索引时，依赖表的统计信息来估算扫描行数。如果表数据变化剧烈而统计信息没更新，优化器可能对一个明明有索引的查询选择全表扫描。通常跑一次ANALYZE TABLE让优化器重新统计即可。

第三个容易被忽略的是表碎片。频繁的删除和更新会让InnoDB表产生大量碎片，导致索引页利用率下降，扫描相同行数的代价变大。OPTIMIZE TABLE可以重建表、整理碎片，但这个操作会锁表，生产环境要选在低峰期执行，或者用gh-ost、pt-online-schema-change这类在线工具来操作。

5.2 索引失效的其他隐藏场景

除了前面提到的函数操作和隐式类型转换，还有几个我实际踩过的坑：

• 使用不等于条件，比如WHERE status != 1，多数情况下MySQL无法用索引快速定位，因为B+树本来就是按等值和范围来设计的
• 使用LIKE '%-关键字%'这样以通配符开头的模糊查询，最左前缀匹配原则会失效；LIKE '关键字%'这种以固定字符串开头的则可以用索引
• OR条件中的一个分支没有索引，优化器可能放弃整个查询的索引
• 联合索引但没遵循最左前缀法则，比如索引是(a, b)，WHERE只查b

这些都是执行计划里type变成ALL或索引使用不充分的直接原因。我排查问题的习惯是拿到一条慢SQL，第一步看表结构和已有索引，第二步EXPLAIN看执行计划，第三步根据执行计划反推是索引缺失、失效还是索引设计不合理，然后针对性地改。

6. 常见问题速查与日常工作建议汇总

6.1 常见问题速查表

6.2 我日常维护索引的几点经验

索引优化不是一次性工作，而是伴随表结构和业务变化的持续过程。我一般在每个大版本上线前做一次索引评审，把慢查询日志里的SQL拉出来，逐一核对执行计划。用Percona Toolkit里的pt-query-digest分析慢日志也很方便，它可以按执行次数和时间消耗排序，帮我快速锁定最值得优化的SQL。

有一个细节值得说：新增索引尽量用在线DDL方式，避免长时间锁表。MySQL 5.6之后InnoDB支持在线DDL，执行ALTER TABLE ADD INDEX时通常会允许并发DML继续执行，但具体还取决于算法和锁级别，大批量数据操作前最好先在测试环境评估影响。

删除无用索引也要注意。我见过一个表上有7个索引，其中两个单列索引完全被新的联合索引覆盖，属于冗余索引。这种冗余不仅浪费空间，还白白增加每次写入的维护成本。通过sys.schema_unused_indexes视图可以查到哪些索引从未被使用过，再结合慢日志确认，就可以安全删除了。

另外，MySQL 8.0提供了不可见索引和函数索引两个实用特性。不可见索引可以让你在不删除索引的情况下测试“没有这个索引时优化器会怎么走”，风险非常低。函数索引则直接解决对字段做函数操作导致索引失效的问题，可以评估升级到8.0的价值。

最后想说的是，索引优化的核心从来不是背诵多少规则，而是理解数据结构和执行计划。B+树解决了有序存储与高效查询的矛盾，理解了这一点，很多索引设计原则都不用死记。真正遇到慢SQL的时候，拿着EXPLAIN一步步看type、rows和Extra，结合表的数据特征和业务场景，自然就知道该怎么改。希望这篇内容能帮你少走一些弯路。