企业官网建设流程全解析

之前线上有个订单列表接口，用户一直反馈页面要转好几秒才出数据。我拉了一下慢查询日志，定位到一条按 user_id 和时间范围查 orders 表的 SQL，在 340 万行的表里跑了 2.6 秒。第一反应不是去改 SQL 写法，而是先看这张表到底有没有走索引——结果发现 WHERE 里的两个关键条件完全没有可用的二级索引。补了一个复合索引之后，这条 SQL 直接从 2.6 秒降到了 12 毫秒。

类似的场景我遇到过很多次。给 mysql 表添加索引这件事，是所有 SQL 优化里投入产出比最高的动作之一，但也是最容易“想当然”的操作。很多开发者拿到慢查询就加索引，加完发现 EXPLAIN 里还是 type=ALL，或者索引确实走了但性能也没什么变化。这篇内容我按自己的实际排查经验来写，从最基础的索引设计思路、类型选型、添加索引的完整操作，讲到索引失效、复合索引进阶和线上常见问题，希望能帮你少走几次弯路。

1. 加索引前，先搞清楚你的表到底需要什么索引

直接动手执行 ALTER TABLE ADD INDEX 之前，我建议先花十分钟想清楚一件事：这条 SQL 是怎么查数据的，瓶颈到底在哪。索引不是加得越多越好，加错了不仅是磁盘空间浪费，还会拖慢写入。

1.1 一条 SQL 是怎么在表里找到数据的

先说个生活化类比。一本几百页的书，你要找“MySQL 索引优化”这几个字，如果这本书没有目录，你只能从第 1 页翻到最后一页，一行一行找，这就是全表扫描。有了目录之后，你可以先定位到“索引”相关章节，再精确翻到那一页，这就是索引查找。

InnoDB 存储引擎里，每张表的数据都按主键顺序组织成一棵 B+ 树，这棵树叫聚簇索引。你根据主键查数据，直接走这棵树就能找到完整行记录。但如果你用其他字段查，比如WHERE user_id = 123，MySQL 需要先在一棵专门为 user_id 建的二级索引树里找到对应的主键值，再拿着主键回聚簇索引查整行数据，这个过程叫回表。

这也是为什么我给 orders 表加索引能带来质的提升：原来的查询没有可用索引，MySQL 只能把 340 万行记录全部读出来，再用 user_id 和时间条件逐行过滤。加了复合索引之后，MySQL 直接通过索引树定位到满足 user_id 条件的少量主键，再回表查数据，IO 量差了三个数量级。

理解这个底层机制，你才能解释很多现象：为什么覆盖索引能避免回表从而更快，为什么索引字段上有函数运算会导致索引失效，为什么 SELECT * 有时候不如只查索引字段快。这些在后面都会展开讲。

1.2 判断要不要加索引，可以看这三个信号

我不会一看到慢 SQL 就加索引，通常会先看三个信号。

第一个是慢查询日志。MySQL 里通过slow_query_log和long_query_time两个参数控制，long_query_time设置为 1 或 2 秒比较合理。线上如果某条 SQL 频繁出现在慢日志里，说明它有性能问题，值得进一步分析。我自己习惯把慢日志开到表里，方便按执行次数排序，快速找到“高频慢 SQL”。

第二个是 EXPLAIN 的执行计划。很多刚接触优化的同学不太看这个，但其实EXPLAIN SELECT ...是不真正执行查询的，只是让优化器输出执行计划，成本极低。重点关注type列：如果是ALL，说明全表扫描，大概率有加索引的空间；如果是ref或range，说明已经用上了索引，但可能还有优化余地；如果是index，要小心，这可能是扫描了整个索引树，并不代表效率高。

第三个是表的数据量和写入频率。只有几千行的表，全表扫描可能比走索引还快，因为 InnoDB 读数据以页为单位，小表一两次 IO 就搞定了，走索引反而要多读索引页。但表到了几十万、几百万行之后，全表扫描的代价就上来了。另外，如果你的表是典型的高并发写入表，比如订单流水、日志表，每加一个索引都会让 INSERT、UPDATE 多维护一棵 B+ 树，这时候就需要权衡查询收益和写入成本。

提示：判断加不加索引，最忌讳的是“看一条慢 SQL 就无脑加”。先把相同的查询条件、数据分布、表大小搞清楚，再动手。

2. 索引类型选型：单列、复合、唯一、全文和前缀

执行ALTER TABLE ADD INDEX之前，你还得选对索引类型。MySQL 里索引不是只有一种形态，不同的业务需求对应的索引结构差别很大。我见过不少表，把所有查询字段都单独建了单列索引，结果一条多条件查询还是慢，这就是典型的“索引类型没选对”。

2.1 六种常用索引类型快查

下面这张表我按实际使用频率整理，每一条都写了对应的 SQL 示例。

很多人会忽略唯一索引的隐藏价值：它不只是约束，还能给优化器提供更精确的估算。如果某个字段的业务逻辑上就必须唯一，直接建唯一索引而不是普通索引，既省一个索引，又保证数据质量。

2.2 主键索引不是唯一索引

我经常被问到一个问题：“我用了一个唯一约束字段，是不是就不用建主键索引了？”这是两个完全不同的概念。主键索引是 InnoDB 的数据组织方式，每张表都必须有，它决定了数据行在磁盘上的物理排列。唯一索引只是一个二级索引，它约束字段值不能重复，但数据行的存储仍然依赖主键。

如果你建表时没指定主键，InnoDB 会选一个非空的唯一索引作为聚簇索引；如果连唯一索引都没有，它会生成一个隐藏的 6 字节 rowid 作为聚簇索引。这种情况下，你的二级索引回表时查的是隐藏 rowid，既不可控，也可能因为索引页利用率低产生额外开销。所以我的习惯是：所有表必须有主键，而且尽量选自增整数或雪花 ID 这类单调递增的值，避免页分裂。

从 EXPLAIN 里辨认主键索引也很简单：key列显示PRIMARY，type通常是const或eq_ref，比如WHERE id = 123走主键查询时，优化器知道最多返回一行，所以成本估算最精准。

2.3 复合索引是大部分慢查询的答案

单列索引能力有限。举个例子，WHERE user_id = 123 AND create_time > '2024-01-01'，如果你只在 user_id 上建了单列索引，MySQL 能用它定位到 user_id=123 的所有记录，然后再在内存里过滤 create_time 条件。如果这个用户有几万条订单，回表和过滤的代价依然不小。

更合理的做法是建复合索引(user_id, create_time)。这样索引树里先按 user_id 排序，再在 user_id 相同的情况下按 create_time 排序，查询时通过索引就能同时完成过滤，甚至排序。这就是为什么我会把第 5 节单独拿出来讲复合索引，因为它才是生产环境里解决慢查询的主力。

3. 实操：给 MySQL 表添加索引的完整流程

理论说得再多，不如直接上手操作一遍。这一节我带你完整走一遍“查当前索引状态 → 添加索引 → 验证执行计划”的流程，并解释每一条命令背后的含义。

3.1 加索引的两种写法：ALTER TABLE 还是 CREATE INDEX

MySQL 里添加索引的 SQL 有两条常用路径：

ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);

CREATE INDEX idx_user_time ON orders (user_id, create_time);

两条语句本质上做的事情是一样的，但 ALTER TABLE 的扩展性更强：它可以在一条语句里调整多种表结构，比如同时加索引、修改字段、改表注释。CREATE INDEX 则只负责建索引，语义更聚焦。我个人的习惯是，单加索引用 CREATE INDEX，涉及表结构调整时用 ALTER TABLE 统一处理。

还有一点值得重点说明：在 MySQL 5.6 及以后版本，ALTER TABLE 默认支持在线 DDL。之前大家普遍担心“加索引会锁表导致业务停摆”，在 5.6 之前确实会，但在 5.7、8.0 里，添加二级索引默认使用ALGORITHM=INPLACE, LOCK=NONE，意思是只锁定很短的时间做元数据变更，索引数据是后台逐步构建的。不过这不代表你可以无视线上负载，极大数据量下构建索引仍然会占用大量 IO，后文我会单独写这个问题。

3.2 EXPLAIN 验证索引是否真正生效

先看一个具体例子。假设 orders 表结构如下：

CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, status TINYINT NOT NULL DEFAULT 0, KEY idx_user_time (user_id, create_time) ) ENGINE=InnoDB;

然后我们执行：

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2024-06-01';

输出结果里你会看到几个关键列：

• type：这里是range，说明通过索引做了范围扫描，比ALL好很多。
• key：idx_user_time，说明优化器实际选用了这个索引。
• key_len：这个值很有用，它表示索引里用了多少个字节。user_id 是 INT，占 4 字节，create_time 是 DATETIME，在 MySQL 5.6+ 占 5 字节（含 1 字节小数秒标识），所以这里 key_len 可能显示 9 左右，说明两个索引列都被使用上了。
• rows：优化器预估扫描的行数，这个值越小越好。
• Extra：如果出现Using index condition，说明触发了索引下推；如果NULL就表示回表查了完整行。

如果你发现type还是ALL，或者key是 NULL，就得回头检查 SQL 里是不是写了让索引失效的表达式，比如在索引列上用了函数，或者隐式类型转换。这类问题下一节详细说。

3.3 用 SHOW INDEX 查看索引状态并清理冗余

加完索引后，我习惯再执行一次SHOW INDEX FROM orders，看看索引列表里有没有之前的历史残留。

这个命令会输出表的全部索引信息，其中有几个字段需要关注：Cardinality表示索引去重后的估计值，它与表行数的比值越接近越好；如果某个索引的 Cardinality 很小，说明这个索引的区分度很低，比如性别字段，那它大概率没有价值。但这个值是统计信息采样得来的，不是精确值，建议定期ANALYZE TABLE orders;更新统计信息，否则优化器可能因为陈旧统计选错索引。

另外，很多表上线久了之后会有大量冗余索引，比如 user_id 上有单列索引，又建了(user_id, create_time)复合索引，那么前者的价值就几乎为零。你可以用pt-duplicate-key-checker这类工具扫描重复索引，人工确认后删除。删除索引要谨慎，先在测试环境验证，避免因为删掉一个“看似没用”的索引导致另一个查询变慢。

4. 为什么索引失效：比忘记加索引更隐蔽的坑

如果只是“没加索引”，问题反而好解决，加上就行。真正让人头疼的是：明明建了索引，EXPLAIN 里却看不到索引生效，或者时灵时不灵。这类问题通常和 SQL 写法、字符集、优化器行为有关。

4.1 七种典型失效场景

我把自己踩过和帮同事排查过的场景整理如下，每一条都配了问题示例和修复方向。

1. 在索引列上使用函数：比如WHERE DATE(create_time) = '2024-06-01'，MySQL 要先把每行的 create_time 都算成 DATE 才能比较，索引就失效了。正确做法是写create_time >= '2024-06-01' AND create_time < '2024-06-02'，让查询条件和索引列直接可比。

2. 前导模糊查询：WHERE title LIKE '%MySQL%'，开头就是通配符，B+ 树的有序性无法利用。只有WHERE title LIKE 'MySQL%'才能走索引，这是范围扫描。

3. 隐式类型转换：字段是 VARCHAR，查询条件却写WHERE order_no = 10086，MySQL 会把字符串转成数字比较，导致索引失效。经验是保持查询条件和字段类型一致。

4. OR 连接非索引条件：WHERE user_id = 1001 OR status = 1，如果 status 没有索引，MySQL 可能直接放弃整个索引扫描。改成两个查询用 UNION ALL 合并，或给 status 也建合适索引。

5. 联合索引没有满足最左前缀：索引是(a, b, c)，查询条件是WHERE b = 1 AND c = 2，跳过了最左列 a，索引无法被有效利用。在第 5 节会详细讲。

6. 大量负向查询：WHERE status != 1、WHERE user_id NOT IN (...)，这些“排除式”条件很难利用索引有序性。如果业务必须这么做，建议改成等值匹配或设计更合理的状态字段。

7. 优化器判断走索引反而更慢：当表只有几万行，且查询要返回表中大部分数据时，优化器会认为全表扫描更划算，这时候key列就是 NULL，这是正常的，不代表索引是坏的。

4.2 学会看 EXPLAIN 的 Extra 列

排查索引失效时，最容易忽略的是 Extra 列。它记录了执行计划里的附加信息，很多问题在这里比看 key 列更直观。

• Using where：表示存储引擎返回记录后，Server 层又做了条件过滤。如果这个过滤可以下推到索引条件里，通常说明索引设计还没最优。
• Using index：这是最理想的情况，说明查询所需的字段都在索引树里，不需要回表，也就是覆盖索引。
• Using index condition：表示用了索引下推（ICP），意思是部分 WHERE 条件在索引遍历过程中就被过滤掉了，减少了回表次数。在 MySQL 5.6 之后的版本中比较常见，是好事。
• Using filesort：说明 ORDER BY 没走索引顺序，需要额外排序。排序数据量大时会生成临时文件，这是性能隐患。
• Using temporary：说明查询用了临时表，常见于 GROUP BY 和去重操作，也说明索引设计可能不合理。

我排查问题的顺序通常是：先看 type 是不是 ALL，再看 key 是否为空，最后看 Extra 有没有Using filesort或Using temporary。这三关都过了，SQL 一般就没什么大问题。

5. 复合索引进阶：最左前缀、ORDER BY 和索引下推

生产环境里，我已经很少建单列索引了，基本上都是复合索引。但复合索引有个天生的特性叫“最左前缀”，很多人在这里翻车。同时，它对 ORDER BY 的优化也是很多慢查询的解法，值得单独讲透。

5.1 复合索引的字段顺序与最左前缀法则

假设我们有一个复合索引idx_a_b_c (a, b, c)。这个索引的真实存储逻辑是：先按 a 排序，a 相同时按 b 排序，b 相同时再按 c 排序。所以 MySQL 能利用它的查询条件并不是任意的组合，而是要满足“从左往右匹配”的规则。

这里有个细节很多人会忽略：范围查询会让后续字段失去排序能力。比如WHERE a > 1 AND b = 2，a 是范围条件，b 的排序是在 a 范围内的顺序，不一定全局有序，所以优化器不会用 b 去精确定位。实际开发中，我建议把等值条件放在前面，范围条件放在后面，这样索引利用率最高。

另一个经验是先分清高频查询字段的过滤度。过滤度是指某个字段去重后的值数量与总行数的比值，比如性别字段只有 2 个值，过滤度极低。复合索引的字段顺序应该把过滤度高的字段放前面，而不是想当然地按表结构顺序排。

5.2 用复合索引直接解决 ORDER BY 的 filesort

慢查询日志里有一类很典型的 SQL：WHERE user_id = 1001 ORDER BY create_time DESC。如果你只用(user_id)单列索引，MySQL 能通过索引快速找到 user_id=1001 的所有记录，但 ORDER BY create_time 还需要再排序一次，数据量大时就会出现Using filesort。

如果把索引改成(user_id, create_time)，情况就完全不同了。因为索引树本身就是按 user_id、create_time 两级排序的，user_id=1001 对应的记录天然就是按 create_time 有序排列的，MySQL 直接按索引顺序读取即可，不需要额外排序。

这个优化思路还可以扩展到 GROUP BY。比如统计每个用户每天的订单量：

SELECT user_id, DATE(create_time) AS day, COUNT(*) FROM orders GROUP BY user_id, DATE(create_time);

如果索引是(user_id, create_time)，但 GROUP BY 里对 create_time 做了 DATE() 函数，索引会失效。更合理的方案是让时间字段的粒度直接满足业务，比如单独存一个day字段，然后建(user_id, day)索引，让 GROUP BY 的字段和索引从左到右对齐。

5.3 覆盖索引与索引下推：让查询少回一次表

覆盖索引这个概念很多教材里都有，但实际用得好的却不多。所谓覆盖索引，就是查询的所有字段都包含在某个二级索引里，这样 MySQL 不需要回表查聚簇索引，直接遍历二级索引就能返回结果。

比如：

SELECT user_id, create_time FROM orders WHERE user_id = 1001 AND create_time > '2024-06-01';

上述查询所需的 user_id、create_time 两个字段都在idx_user_time里，EXPLAIN 的 Extra 会显示Using index，说明完全不需要回表。相反，如果 SELECT 后面还带了 order_no、status 等不在索引里的字段，就需要回表查完整行。

索引下推则是 MySQL 5.6 引入的优化手段。以前，如果一个复合索引是(user_id, create_time)，像WHERE user_id = 1001 AND create_time > '2024-06-01'，存储引擎遍历索引时会拿着 user_id 条件定位，等回表后再过滤 create_time。有了 ICP 之后，create_time 的过滤被下推到索引遍历阶段，回表的次数大幅减少。EXPLAIN 里出现Using index condition就是在告诉你 ICP 生效了。

这也解释了为什么我对推荐索引时的建议通常很明确：查询字段尽量控制在索引字段范围内，查询条件尽量用等值开头，范围条件靠后。这样覆盖索引和索引下推能同时发挥最大作用。

6. 常见问题与排查技巧实录

这一节整理几个 Fréquent 被问到的问题，都是线上实战里真实碰到的。如果你按照前面的流程操作之后还是有问题，可以对照这里来查。

6.1 大表加索引怕卡业务怎么办

给百万级、千万级的大表加索引，最担心的就是执行期间业务卡死。前面提过，MySQL 5.6 之后二级索引的在线 DDL 已经做了很多优化，默认情况下不会长时间锁表，但实际执行时仍可能因为后台构建索引而拖慢 IO，尤其是机械硬盘环境。

如果你运维的是 MySQL 5.5 或更早版本，或者想要更可控的加索引窗口，我会用pt-online-schema-change这类工具：它通过创建一个新表结构、在旧表上建触发器同步增量数据、再把新表重命名的方式完成加索引，业务影响很小。不过这个方案需要额外部署工具，过程也比较复杂，除非业务对可用性非常敏感，默认还是推荐直接用在线 DDL。

个人建议的操作策略是：先在从库上执行加索引并观察一段时间，确认没有主从延迟放大器问题之后，再在业务低峰期操作主库。加索引本身是一件低风险操作，但“低风险”不等于“零风险”，保留一个可以回滚的窗口很重要。

6.2 索引建了还是慢，问题还可能出在哪

很多同学遇到过这种怪事：索引加上了，EXPLAIN 也显示走了索引，但 SQL 还是很慢。这时通常要往三个方向排查。

第一个是回表太多。二级索引帮你快速定位到一批主键，但如果这批主键对应的数据行物理分布很分散，回表会产生大量随机 IO。表现在慢日志里就是每次查询的耗时波动很大。解决办法是扩大覆盖索引范围，把要查询的字段都塞进索引，或者优化 SQL 只查询必要字段。

第二个是统计信息和实际数据分布严重不一致。索引选择是靠优化器估算的，如果表刚经历大量增删改，或者很久没跑 ANALYZE TABLE，Cardinality 就会失真。有时你用FORCE INDEX指定某个索引，反而比默认选择的索引快得多。

第三个是字符集和排序规则不一致导致的隐式转换。比如两个表字段都是 VARCHAR，但一个表的排序规则是utf8mb4_general_ci，另一个是utf8mb4_unicode_ci，JOIN 时 MySQL 可能会做隐式转换，让索引失效。排查时看SHOW CREATE TABLE的CHARSET和COLLATE是否一致。

6.3 加索引后写入变慢，索引数量怎么权衡

索引能加速查询，但每次 INSERT、UPDATE、DELETE 都要同步维护索引树。如果一张表有五个索引，写入时就要更新五棵 B+ 树。对于日志表、流水表这类高频写入场景，这个代价很容易被放大。

取舍逻辑我一般这样定：单表索引数量尽量控制在 5 到 6 个以内，少建低区分度字段上的索引；大字段（如 TEXT、超长 VARCHAR）尽量用前缀索引或单独拆分表；如果写入是核心诉求，甚至可以考虑在业务上把查询分流到只读从库，主库只承担写入和事务查询。

另外一点，MySQL 对索引键长度有限制，默认 767 字节（8.0 可选扩大到 3072 字节）。索引多个长字段前要估算长度，比如 VARCHAR(255) utf8mb4 字段本身占 1020 字节，直接建索引就会超出限制，这时必须用前缀索引，或者把字段长度改短。

6.4 关于全文索引和前缘扩展的补充

很多从 Oracle 或 PostgreSQL 转过来的开发者会顺手问一句“这两个库都能建很多索引，MySQL 行不行”。MySQL 对单表的索引数量没有硬性上限，但列总长度、索引键大小是有限制的，复合索引最多可包含 16 个列。所以只要你遵循“按查询设计、控制冗余、控制长度”这几个原则，就不太会碰到数据库层面的限制，更多时候是加索引前没想清楚业务查询路径。

全文索引在 MySQL 里也能用，但说实话，中文全文检索并不是 MySQL 的强项，分词效果和召回率都不如 Elasticsearch 这类专业搜索引擎。如果只是简单的中英文模糊查询，全文索引可以救急；如果业务量再大一点，尽早把搜索功能拆出去，性能和管理都比堆在 MySQL 里更可控。

在实际操作中，我最深的体会是：加索引不难，真正难的是克制。上线新需求时，每一条主要查询路径都值得你多花十分钟看看它的 WHERE、ORDER BY、GROUP BY 到底长什么样，再决定索引字段和顺序。等系统流量上来之后你会发现，一个设计合理的复合索引，比事后拍脑袋补的五个单列索引有用得多。另外，索引不是一劳永逸的，表结构变更、数据分布变化都会影响之前的索引效果，每隔一段时间重新抓一遍慢查询，对照执行计划做一次索引体检，是我现在做数据库维护时的固定动作。最后分享一个小经验：如果你不确定某个索引该不该留，就把它单独摘出来，跑一遍相关业务的压测脚本，让数据帮你做决定，比凭感觉分配合理得多。