企业官网建设流程全解析

上周线上有个订单查询接口突然慢得离谱，一个简单的按用户查订单列表，SQL 跑了快两秒。当时第一反应就是看EXPLAIN，结果type是ALL，rows直接飚到几十万，典型的全表扫描。那台 MySQL 实例上的订单表已经过千万级，加索引之前和之后完全是两种体验——这也是我想写这篇博文的直接原因。关于 MySQL 索引，从原理到实操、从命中规则到失效场景、从设计规范到维护巡检，我在这几年的数据库优化里踩了不少坑，这次一次性整理出来。

这篇内容不是帮你背概念，而是告诉你真正到线上环境给 MySQL 数据库建立索引时，每一步该怎么做、为什么要这么做、哪些做法看起来很合理其实是给自己埋雷。文章适合正在做后端开发、需要自己优化数据库性能的工程师，也适合准备 MySQL 面试、想系统梳理索引知识的同学。我尽量把 B+ Tree 原理、索引分类、实际建索引 SQL、组合索引设计、失效场景排查这整条链路讲清楚，让看完的人能直接上手。

1. 索引到底在解决什么问题？先了解InnoDB的存储模型

1.1 为什么全表扫描会慢，B+ Tree如何加速查找

先不急着写CREATE INDEX，你得知道 MySQL 无缘无故为什么要存一棵树。InnoDB存储引擎的数据是按页（page）组织的，默认每页 16KB，数据行存放在页里，页与页之间形成双向链表，同一个页内的行通过单向链表串联。当你执行不带索引的查询时，存储引擎只能从第一个页开始，把每一行依次读出来做匹配，这就是全表扫描。千万级的数据，哪怕只查一条，也要把几万甚至几十万个页全部读一遍，瓶颈完全卡在磁盘 IO 上。

B+ Tree 的核心价值在于把“遍历”变成“查找”。它是一棵矮胖的多路平衡树，根节点到叶子节点的高度通常只有 2 到 4 层。因为每一层节点保存多个键值和子节点指针，一次查找最多只需要 3、4 次磁盘 IO，就能定位到目标叶子页。从“读几十万个页”变成“读几个页”，性能差距就是这样拉开的。叶子节点之间通过双向链表连接，也让范围查询非常舒服——找到了起始位置之后，顺着链表往后扫就行，不需要回溯父节点。

1.2 聚簇索引、二级索引与主键索引的关系

InnoDB 里有个非常重要的设计：表本身就是一棵以主键为排序键的 B+ Tree，这叫聚簇索引。叶子节点直接保存整行数据，所以通过主键查数据，找到叶子节点就等于拿到了完整数据行。这也是为什么 InnoDB 表强烈建议显式定义主键，如果没有主键，它会选第一个非空的唯一索引作为聚簇索引，再没有就隐式生成一个 6 字节的 rowid。没有主键的 InnoDB 表，每次插入都可能引发页分裂，性能隐患很大。

二级索引（也叫辅助索引或普通索引）则不同，它的叶子节点存的是索引列的值，再加上对应主键值。查的时候先走二级索引树，找到主键值，再回聚簇索引树里查一次完整数据，这个动作叫回表。举个例子，你在email字段上建了普通索引，执行SELECT * FROM user WHERE email='xxx'，MySQL 会先查二级索引拿到主键 id，再用 id 回表取出完整行。这就是为什么有些查询看似走了索引，还是有性能损耗——回表多了，IO 次数自然上去了，这也是后面讲覆盖索引能够大幅提速的根本原因。

2. 为MySQL数据库建立索引：从语法到实战决策

2.1 创建索引的基本语法和使用场景

MySQL 里建索引最常用的有三种途径：建表时指定、CREATE INDEX、ALTER TABLE追加。拿一个实际订单表来举例：

CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT '订单号', user_id BIGINT NOT NULL COMMENT '用户ID', status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', ... );

如果要给订单号加唯一索引，可以这样：

CREATE UNIQUE INDEX uk_order_no ON t_order(order_no);

或者：

ALTER TABLE t_order ADD UNIQUE INDEX uk_order_no (order_no);

CREATE INDEX和ALTER TABLE ADD INDEX在 MySQL 里效果是一样的，唯一索引用UNIQUE关键字，普通索引直接INDEX或KEY。建表时指定的话就在表定义的最后加上KEY idx_user_id (user_id)这样的语法。我平时更习惯用ALTER TABLE，因为线上表结构变更一般通过工具执行，ALTER TABLE语义更明确。要注意的是，大表在线建索引不能直接ALTER TABLE，MySQL 8.0 之前这会锁表，生产环境用gh-ost或pt-online-schema-change这类在线改表方案，这个后面细说。

另一个实用场景是联合唯一索引。比如业务上要求同一个用户对同一个商品只能有一条评价记录，假设表里已有user_id和product_id字段，可以直接建联合唯一索引来兜底防重：

ALTER TABLE t_review ADD UNIQUE INDEX uk_user_product (user_id, product_id);

这个索引既保证了唯一性，又能加速“查某人对某商品的评价”这类高频查询，一举两得。但要注意，联合唯一索引的字段顺序会影响唯一性约束的判断范围，顺序不同，语义完全相同，但查询利用的效率不同，选择时优先让最常用于等值查询的字段放在最左边。

2.2 索引类型怎么选：普通、唯一、全文、前缀

索引类型这件事，选错了不是不能用，而是会造成没必要的成本。普通索引（INDEX）只加速查询，不约束数据唯一性；UNIQUE索引额外多一层唯一性约束，写入时会多做一次冲突检测，所以如果没有唯一性要求，不要随便加UNIQUE。全文索引（FULLTEXT）在 MySQL 里专门处理大文本的模糊匹配，比如文章内容的词法搜索，如果你只是对VARCHAR字段做LIKE 'abc%'，普通索引就能搞定，不需要全文索引。LIKE '%abc%'即使有普通索引也走不了，这属于索引失效范畴，后面单独讲。

VARCHAR字段长度很长的时候，比如某个业务表里存了邮箱或 URL，整个字段建索引会导致索引树变得很大，占用空间多，且单个索引条目太大，一个页能存放的键值变少，树的高度可能增加。这时候用前缀索引更划算——只取字段前 N 个字符做索引：

ALTER TABLE t_user ADD INDEX idx_email_prefix (email(20));

到底取多长？核心看选择性。选择性 = 去重后的前缀值数量 / 去重后的完整值数量，越接近 1 越好。通常的做法是分别试5、10、15、20不同前缀长度的区分度，选一个能让选择性超过 0.9 且长度尽量短的值。代价是前缀索引无法用于ORDER BY email或者覆盖索引扫描，但如果只是做等值查询，实际效果非常好。

2.3 索引不是越多越好：建索引前必须权衡的问题

很多新手容易犯的一个错误，是为了优化某个慢查询，立刻加一个索引，结果索引越加越多，最后一张表二三十个索引。索引不是免费的午餐，它在加速读取的同时，牺牲的是写入性能和存储空间。每次INSERT、UPDATE、DELETE，InnoDB 不仅要修改聚簇索引里的数据页，还要同步维护每一条二级索引树，索引越多，写入放大越明显。在写入频繁的表上，多加几个索引，TPS 可能出现肉眼可见的下降。

另一个容易被忽略的问题：冗余索引和重复索引。比如你建了(user_id, status)联合索引，又单独建了(user_id)索引，后者就是冗余的——因为联合索引的最左前缀原则已经能覆盖user_id单独查询的场景。重复索引则更直接，建了KEY idx_user_id (user_id)又建KEY idx_user_id_2 (user_id)，完全一样的两棵树，纯浪费。我建议每半年做一次索引梳理，用sys.schema_unused_indexes视图查一下哪些索引从未被使用，结合慢日志确认后删除。这个视图在 MySQL 5.7 以上就自带了，非常方便：

SELECT * FROM sys.schema_unused_indexes;

这里我要多说一句：删索引前一定确认它没被使用，别只看视图结果——视图统计的是服务启动以来的使用情况，如果业务有周期性任务刚好在统计周期之外，可能误判。稳妥做法是把慢日志里所有 SQL 拉出来，手工检查一遍可能走这些索引的查询，确认没有命中再动手。

3. 组合索引与排序优化：让一条索引服务多个查询

3.1 最左前缀原则与实际字段编排方法

组合索引是 MySQL 索引设计里最能体现功力的部分。一张表最多建那么几个索引，怎么样让这几个索引覆盖尽可能多的高频查询场景，靠的就是对组合索引字段顺序的理解。核心规则是最左前缀原则：MySQL 只能从组合索引最左边的字段开始连续匹配，跳到中间字段再查后面的字段就没法用索引了。

比如创建了组合索引(a, b, c)，那么：

• 查询条件是a = 1，能用到索引；
• 查询条件是a = 1 AND b = 2，能用到索引；
• 查询条件是a = 1 AND b = 2 AND c = 3，完整用到索引；
• 查询条件是b = 2 AND c = 3，用不到这个索引；
• 查询条件是a = 1 AND c = 3，只能用到a列，c列无法从索引中过滤。

明白了这个规则，编排组合索引字段顺序时应该遵循几条原则：等值查询的字段放最前面；经常用于范围查询（>、<、BETWEEN）的字段放在后面；区分度高的字段尽量靠前。之所以区分度高的放前面，是因为索引树在每层节点上能更早过滤掉更多数据，减少向下检索的路径长度。但这不是绝对的，如果某个低区分度字段是高频率的等值查询条件（比如status=1），把 status 放前面反而能让查询直接定位到对应分支。所以最终要结合业务查询的实际频率，而不是机械套公式。

3.2 覆盖索引：如何避免回表带来的性能损耗

回表操作要额外读一次聚簇索引，在数据量大或者二级索引本身不在内存里的时候，等于多一次磁盘 IO。那有没有办法让查询需要的列全部存在于二级索引树中？有，这就是覆盖索引。MySQL 如果发现二级索引里已经有查询需要的全部列，就会直接返回结果，不再回表，执行计划里Extra会显示Using index。

举个实际例子，订单列表页需要展示订单号、状态和创建时间，查询条件是user_id：

SELECT order_no, status, created_at FROM t_order WHERE user_id = 10086;

如果只是建一个KEY idx_user_id (user_id)，查出来的是user_id和主键 id，要拿order_no、status、created_at还得回表。改成建一个覆盖索引，直接把这三列都放进去：

ALTER TABLE t_order ADD INDEX idx_user_cover (user_id, order_no, status, created_at);

这样查询所需的所有列都在二级索引树上，一次索引扫描直接返回结果，完全不需要回表。注意覆盖索引不是让你把所有字段都塞进索引，那样索引体积会失控，而是针对「高频查询、固定返回列」的场景做定向覆盖。像订单状态这种只是展示用的字段，加进来没多大成本；如果连order_detail这种大的TEXT字段也放索引里，就得不偿失了。

3.3 排序与索引的配合：ORDER BY 走索引优化

ORDER BY也是索引能明显起作用的地方。B+ Tree 本身就是按索引列排序的，如果查询的排序顺序和索引顺序一致，MySQL 可以直接利用索引的有序性返回结果，不需要额外的filesort（文件排序）。文件排序很贵，要先把结果集取出来放到内存或磁盘临时文件里排一遍，数据量大的时候性能极差。

比如订单列表经常要按创建时间倒序：

SELECT * FROM t_order WHERE user_id = 10086 ORDER BY created_at DESC;

这时如果索引是(user_id, created_at)，先按user_id等值定位，created_at天然有序，倒序扫一遍就行，Extra里会显示Using index condition且没有Using filesort。但如果索引是(created_at, user_id)，虽然created_at有序，可等值条件user_id在右边，没办法先定位用户再按时间取，排序就落回filesort了。

MySQL 8.0 还引入了降序索引的支持，可以显式指定索引列的排序方向：

ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at DESC);

在 8.0 之前，索引列默认都是升序存储，反向扫描也能工作但效率略低。如果你确定某列的排序方向几乎总是倒序，在 8.0 里建降序索引会更稳妥。不过说实话，实际工程里大部分场景升序索引加反向扫描已经够用了，降序索引更多用于混合排序（一部分升序一部分降序）这种特殊情况，日常开发不必过度设计。

4. 索引失效排查与EXPLAIN实战：索引建了却不生效怎么办

4.1 最常见的六种索引失效场景盘点

索引建了不等于 SQL 一定会走。线上最常见的索引失效场景，我总结了六种，几乎每个项目都能碰上：

第一，对索引列使用了函数或表达式计算。比如WHERE DATE(created_at) = '2025-01-01'，这在created_at上做了函数运算，MySQL 无法利用索引的有序性，只能全表扫。正确写法是WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'，才是范围查询。

第二，隐式类型转换。字段类型是VARCHAR，查询条件却写成数字，比如WHERE phone = 13800138000（phone 是VARCHAR），MySQL 会把phone转成数字再比较，索引列上发生了隐式函数转换，索引就废了。反过来如果字段是整型，条件写字符串通常不会失效，因为优化器会把字符串转成数字。但千万别依赖这个行为，开发规范里就要求字段类型和参数类型严格一致。

第三，LIKE以通配符开头的模糊查询。WHERE name LIKE '%张'没法走索引，因为 B+ Tree 定位的时候必须知道前缀。WHERE name LIKE '张%'就能走。如果业务确实需要后缀模糊匹配，考虑引入搜索引擎或者改用前缀索引思路重建字段，不要在查询上硬扛。

第四，联合索引没有遵循最左前缀。前面已经详细讲过了，这里不再展开。第五，OR连接条件时有一个字段没有索引。WHERE user_id = 10086 OR status = 1，如果status没有索引，MySQL 可能选择全表扫描而不是分别走两个索引合并结果，因为单独用索引再合并的成本往往比全表扫描更高。解决办法是给status也加索引，或者优化器选择INDEX_MERGE。第六，NOT IN、NOT LIKE和!=这类否定操作，大部分情况下也无法利用索引，因为 B+ Tree 索引本质上是基于等值和范围有序匹配的，否定操作很难直接定位区间。

4.2 EXPLAIN输出如何判断SQL是否用到索引

判断一条 SQL 有没有走索引，不是靠猜，也不是看执行时间，而是直接看EXPLAIN输出。以最典型的字段来说：

• type：访问类型。从好到差依次是system > const > eq_ref > ref > range > index > ALL。看到ALL基本就是全表扫描了，得警惕；index是扫描了整棵索引树，也不怎么样；range开始就比较健康，比如BETWEEN、>、<这类范围查询；ref是等值查询走了普通二级索引；const是主键或唯一索引等值查询，性能最好。
• key：实际使用的索引名称。如果为NULL，就是没走任何索引。
• rows：优化器预估需要扫描的行数。这个数字不精确，但量级能说明问题，几千和几十万完全是两个概念。
• Extra：补充信息。看到Using filesort说明排序没走索引，Using temporary说明用了临时表，Using index是最理想的情况，代表覆盖索引。

举个例子：

EXPLAIN SELECT order_no, status FROM t_order WHERE user_id = 10086 ORDER BY created_at DESC;

如果执行结果的Extra里有Using filesort，说明(user_id, created_at)这个组合索引没建，或者字段顺序不对，数据库只能用文件排序。这一条信息，比任何性能调优文档都更能直接指导你该建什么索引。

4.3 索引下推与优化器的那些细节

MySQL 5.6 引入了索引下推（Index Condition Pushdown，简称 ICP），这是个容易被忽视但很有用的优化。在没有 ICP 时，如果组合索引是(user_id, status)，查询条件是user_id > 1000 AND status = 1，InnoDB 要先把所有user_id > 1000的索引记录取出来回表，再过滤status = 1。有了 ICP，存储引擎在读取二级索引时就同时判断status = 1，把不满足条件的记录直接过滤掉，减少了回表次数。EXPLAIN的Extra里会显示Using index condition。

另外一个细节是优化器对索引的选择并不总是最优的。MySQL 的优化器基于统计信息估算成本，有时候明明有索引，但它估算走全表扫描成本更低（比如表很小、或者索引列区分度很低），就会放弃索引。这时候先用ANALYZE TABLE t_order;更新统计信息，再重新看执行计划。如果确实不该走索引，也别强行FORCE INDEX，那是最后的手段，而且索引数据分布一变，强制索引可能变成负优化。

5. 常见问题与排查技巧实录：把索引问题做进日常巡检

5.1 索引碎片与维护：为什么加了索引性能还是越来越差

一个长期运行的线上表，即使索引建得没问题，性能也可能越来越差。这通常是索引碎片（fragmentation）和统计信息不准确导致的。InnoDB 的索引页在频繁删除、更新后可能出现大量碎片空间，页利用率下降，导致同样的数据量占用更多页，扫描 IO 变高。可以通过information_schema查看索引的页数和碎片情况，也可以用OPTIMIZE TABLE t_order;重建表并整理索引。

不过OPTIMIZE TABLE在大表上会锁表，不能直接在生产环境跑。我的经验是结合在线改表工具做，或者选择低峰期操作。顺便说一个更省事的方案：定期执行ANALYZE TABLE t_order;更新统计信息，让优化器拿到更准确的基数估计。这个操作成本远低于OPTIMIZE，日常巡检可以优先用好它。如果确认碎片严重，再找窗口期做在线OPTIMIZE。

5.2 针对慢SQL的排查流程与线上问题速查表

在团队里我经常带新人做慢查询排查，慢慢固定下来一套流程，这里直接分享出来，照着做基本能解决大部分问题：

1. 在 RDS 或自建 MySQL 的慢查询日志里把慢 SQL 捞出来，按执行次数和耗时排序。
2. 对目标 SQL 执行EXPLAIN，先看type、key、rows、Extra，判断是全表扫描、回表还是 filesort。
3. 对照索引设计原则，检查是缺索引、索引字段顺序不对，还是 SQL 写法导致索引失效。
4. 如果是索引失效，优先改 SQL 写法——比如把函数运算改成范围条件、去掉隐式转换、调整联合索引的字段顺序。
5. 线上索引变更走在线 DDL 工具，不要直接ALTER TABLE锁表。
6. 变更后回看执行计划和性能指标，确认问题闭环。

给你一个速查表，对应常见问题直接查找方案：

5.3 数据库同步与在线索引变更的影响

如果业务用了主从复制，或者接了数据库同步工具，在线加索引的时候要格外小心。MySQL 的主从同步是逻辑复制，主库执行 DDL 后，从库也会执行同样的 DDL。MySQL 8.0 之前的 DDL 大部分需要获取元数据锁，会阻塞该表的 DML 操作，如果从库同步延迟本身偏高，一个慢的ALTER TABLE可能让从库延迟进一步拉大，进而影响线上读写分离的稳定性。

我自己做线上索引变更的推荐姿势：优先用gh-ost这类无锁在线变更工具，它在主库上通过 binlog 同步的方式创建临时表，逐步拷贝数据，最后原子切换，对线上影响小得多。如果没有这类工具，至少在业务低峰期执行ALTER TABLE，并把lock_wait_timeout设小一点，避免长时间拿不到锁而阻塞。变更前最好在测试环境用同样量级的数据先跑一遍，估算执行时间。记住，索引是给查询加速的，但加索引这个动作本身如果影响了可用性，那得不偿失。

MySQL 索引这件事，看起来是几条 SQL 语法，真正吃透之后会发现整个数据库优化的思路都清晰了。从 B+ Tree 的存储结构，到聚簇索引和二级索引的配合，再到组合索引如何覆盖高频查询，每一步都需要结合你实际的表结构和业务查询来做决策。我在实操中最深的体会是，没有任何一套索引设计规则能直接套用到所有项目，核心方法就是多跑EXPLAIN、多观察真实查询、多做索引梳理，慢慢你会形成一种直觉：看到一条慢 SQL，脑子里基本能判断出是缺索引、索引失效，还是索引根本没设计好。最后再分享一个小习惯，我每周五下午会花十分钟看一次慢日志和sys.schema_unused_indexes，这个动作坚持了快两年，线上因为索引导致的性能问题基本都在萌芽期就被干掉了。希望你也能养成这个习惯，少踩几个我在生产环境里踩过的坑。