企业官网建设流程全解析

作为一个后端开发者，相信你没少被慢查询折磨过。尤其是在数据量上了百万、千万级之后，一条不带索引的 SQL 足以让数据库 CPU 飙升，接口响应从几十毫秒变成几十秒。网上的 MySQL 索引调优资料很多，但大多零散不成体系，要么只讲 B+Tree 原理不讲实战，要么只丢几条“军规”却不解释为什么。这篇文章围绕 MySQL 索引调优和面试高频考点做一次系统梳理，把底层数据结构、执行计划分析、索引设计原则、慢 SQL 优化场景以及面试中容易被追问的问题点一次性讲透。不管你是刚接触索引优化的新人，还是准备冲击大厂数据库岗位的老兵，都能从中找到可以直接落地的思路和话术。

1. 为什么索引调优是 MySQL 性能优化的核心

1.1 索引到底是什么

索引在 MySQL 中是一种数据结构，它把表中的一列或多列数据按照特定顺序组织起来，让数据库在查找数据时不需要扫描全表。你可以把它理解为书的目录：没有目录，想找一个知识点只能一页页翻；有了目录，直接定位到页码，效率提升几个数量级。

从官方定义来说，MySQL 的索引由存储引擎层实现，不同的存储引擎索引工作方式不同。InnoDB 作为最常用的存储引擎，其索引类型是 B+Tree 索引，这也是本文讨论的重点。

举一个最简单的场景：一张用户表有 1000 万行数据，如果查询条件是WHERE user_id = 123456，没有索引时 InnoDB 只能从第一行开始逐行扫描，直到找到目标数据，平均需要访问 500 万行。如果有主键索引，InnoDB 通过 B+Tree 查找，一般只需要 3 到 4 次磁盘 I/O 就能定位到数据所在的页。

1.2 索引解决的核心问题

索引本质上是拿空间换时间，它解决的核心问题包括：

• 快速定位数据行，减少磁盘 I/O 次数。
• 避免全表扫描，降低 CPU 和磁盘负载。
• 帮助 MySQL 优化器选择更优的执行路径。
• 对排序和分组操作起到加速作用，避免filesort和临时表。

在面试中，关于索引最常问的一句话是：“索引为什么能这么快？”答案的关键就在于 B+Tree 的树高控制。InnoDB 的页大小默认为 16KB，B+Tree 每层节点可以存储大量键值。以 BIGINT 主键为例，每个非叶子节点大约能存储 1170 个键值（16KB / (8B + 6B)），三层高的 B+Tree 能存储约 2000 万行数据。这意味着查找任意一行数据，最多只需要 3 次磁盘 I/O（根页常驻内存，实际 2 到 3 次）。

1.3 哪些场景必须关注索引调优

实际业务中，以下几类问题最值得通过索引调优来解决：

• 接口响应突然变慢，排查后发现 SQL 走了全表扫描。
• 多表 JOIN 时驱动表和被驱动表的连接字段没有索引。
• ORDER BY、GROUP BY 频繁出现Using temporary和Using filesort。
• 分页深度过大，例如第 1000000 页之后的查询越来越慢。
• 数据量增长后，即使有索引，由于索引设计不合理导致选择度下降。

2. 从 B+Tree 到索引底层原理拆解

2.1 B+Tree 相比其他数据结构的优势

MySQL 选择 B+Tree 作为索引结构，不是偶然的。面对象磁盘存储的数据库，最昂贵的操作是磁盘 I/O，所以索引树必须矮胖，减少层高。和几种常见数据结构对比来看：

• 哈希索引能实现 O(1) 的等值查询，但无法支持范围查询和排序，哈希冲突多时性能不稳定。
• 二叉搜索树在数据有序插入时会退化成链表，树高不可控。
• 红黑树虽然能保持平衡，但树高明显高于 B+Tree，数据量大时层级过深，磁盘 I/O 次数太多。
• B-Tree 的每个节点同时存储键和数据，非叶子节点能存储的键数量有限，树高相对更高。
• B+Tree 的非叶子节点只存键不存数据，单个节点能存储更多键，树更矮。同时叶子节点通过双向链表连接，非常适合范围扫描和排序。

2.2 InnoDB 的聚簇索引与二级索引

InnoDB 的索引可以分为两类：

聚簇索引（Clustered Index）：表的主键构成聚簇索引，叶子节点直接存储整行数据。InnoDB 表本身就是按聚簇索引组织的，所以每个 InnoDB 表必须有主键。如果没有显式主键，InnoDB 会选择一个非空唯一索引作为聚簇索引；如果也没有，InnoDB 会隐式生成一个 ROW_ID 作为聚簇索引。

二级索引（Secondary Index）：除主键以外的索引都叫二级索引，叶子节点存储的是索引列的值 + 主键值。通过二级索引查找数据时，先找到主键，再通过主键到聚簇索引中回表获取完整行数据。

这里有一个非常关键的概念——回表（Bookmark Lookup）。SQL 走了二级索引，但需要的字段不在二级索引中，就必须回表查询。回表次数越多，性能越差。如果查询的字段恰好全部包含在二级索引中，就不需要回表，这种索引叫做覆盖索引（Covering Index）。

2.3 索引下推（ICP）优化

索引下推（Index Condition Pushdown）是 MySQL 5.6 引入的优化。没有 ICP 时，存储引擎通过索引找到记录后，要把整行数据回传给 Server 层，由 Server 层判断 WHERE 条件；有了 ICP，可以在存储引擎层过滤部分 WHERE 条件，减少回表和 Server 层的数据交互。

举个常见例子，联合索引(name, age)，查询条件为WHERE name LIKE '张%' AND age = 20。没有 ICP 时，存储引擎通过 name 范围定位到多条记录，逐条回表后再过滤 age；有了 ICP，age 条件在存储引擎层就被过滤掉了，只需回表少量记录。

ICP 对覆盖索引场景下的 InnoDB 表也有帮助，但需要提醒的是，ICP 只支持二级索引，主键索引没有生效的余地。

3. 环境准备与实验表设计

3.1 环境版本说明

本文示例的 SQL 和优化思路基于 MySQL 8.0 的常见版本。MySQL 8.0 与 5.7 在索引方面的主要差异包括：8.0 移除了查询缓存、支持隐藏索引和降序索引、提供了更好的窗口函数支持，但 B+Tree 索引的核心模型是一致的。如果你还在使用 MySQL 5.7，本文的大部分内容同样适用，只需注意个别语法差异。

建议实验环境：

• 操作系统：Linux（CentOS/Ubuntu）或 macOS 均可。
• 数据库：MySQL 8.0.x。
• 客户端工具：MySQL 命令行或 Navicat、DataGrip、Workbench。
• 如果需要快速搭建环境，可以使用 Docker 启动 MySQL 实例，但注意本地目录挂载和端口映射。

3.2 创建测试表与模拟数据

为了后续演示 EXPLAIN 和执行计划，我们创建一张订单表，模拟电商场景。

CREATE TABLE `t_order` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', `order_no` VARCHAR(64) NOT NULL COMMENT '订单号', `user_id` BIGINT NOT NULL COMMENT '用户ID', `product_id` BIGINT NOT NULL COMMENT '商品ID', `amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态 0-待支付 1-已支付 2-已取消', `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `pay_time` DATETIME DEFAULT NULL COMMENT '支付时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`), KEY `idx_user_create` (`user_id`, `create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单测试表';

这张表包含三个索引：

• 主键索引id，聚簇索引。
• 唯一索引uk_order_no，二级索引。
• 联合索引idx_user_create，覆盖了user_id和create_time两个字段。

为了让实验数据更有说服力，建议插入至少 100 万行数据。数据量太小，优化器可能认为全表扫描比走索引更快，导致 EXPLAIN 结果不典型。可以用存储过程批量插入模拟数据。

DELIMITER $$ CREATE PROCEDURE insert_order_data(IN total INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= total DO INSERT INTO t_order (order_no, user_id, product_id, amount, status, create_time, pay_time) VALUES ( CONCAT('SN', LPAD(i, 10, '0')), FLOOR(RAND() * 100000), FLOOR(RAND() * 5000), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 3), DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY), IF(FLOOR(RAND() * 10) > 3, DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 300) DAY), NULL) ); SET i = i + 1; END WHILE; END$$ DELIMITER ; CALL insert_order_data(1000000);

这段存储过程会插入 100 万行订单数据。由于使用了RAND()和日期计算，每条数据的用户、商品、状态和时间都有较大随机性，更适合分析索引选择度。

4. EXPLAIN 执行计划实战解读

4.1 EXPLAIN 基本用法

EXPLAIN 是 MySQL 索引调优最强有力的工具。它不会真正执行 SQL，而是模拟优化器生成执行计划。用法非常简单，在 SELECT 前加上 EXPLAIN 关键字即可。

EXPLAIN SELECT * FROM t_order WHERE user_id = 12345 AND create_time > '2025-01-01';

执行后返回的结果集中，最需要关注的字段包括：type、key、rows、Extra、possible_keys。

4.2 type 字段的访问类型

type是衡量 SQL 性能最重要的指标，从好到差依次是：

• system：表中只有一行数据的特殊场景。
• const：使用主键或唯一索引等值查询，最多返回一行。
• eq_ref：多表 JOIN 时，被驱动表使用唯一索引或主键等值匹配，常见于 JOIN 查询。
• ref：使用普通索引或联合索引的最左前缀进行等值匹配。
• range：使用索引进行范围查找，如>、<、BETWEEN、IN。
• index：全索引扫描，需要遍历整个索引树，比 ALL 好但也不理想。
• ALL：全表扫描，需要重点优化。

在实际项目中，const和ref是理想状态，range可以接受，index和ALL需要优化。

执行以下 SQL 观察 type 变化：

EXPLAIN SELECT * FROM t_order WHERE id = 1; EXPLAIN SELECT * FROM t_order WHERE order_no = 'SN0000000001'; EXPLAIN SELECT * FROM t_order WHERE user_id = 12345; EXPLAIN SELECT * FROM t_order WHERE user_id > 12345; EXPLAIN SELECT * FROM t_order WHERE product_id = 10;

第三条 SQL 使用idx_user_create的最左前缀 user_id，type 为 ref；第四条为 range；第五条由于 product_id 没有索引，大概率是 ALL。

4.3 key 和 possible_keys 的区别

possible_keys 表示 MySQL 优化器认为可能用到哪些索引，而 key 是实际使用的索引。如果 possible_keys 有多个，但 key 为 NULL，说明优化器最终放弃了索引，通常原因是索引选择度不够高，或者优化器认为全表扫描更快。

还有一种情况是 possible_keys 不为空，key 也使用了索引，但使用的并不是最优索引。例如idx_user_create中的 user_id 过滤性好，优化器有时也会错误选择另一个索引。我们可以通过FORCE INDEX或调整 SQL 来人工干预，但生产环境要谨慎使用。

4.4 Extra 字段的隐藏信息

Extra 是 EXPLAIN 中信息量最大的一列，常见值包括：

• Using index：使用覆盖索引，无需回表，性能好。
• Using where：存储引擎返回数据后，Server 层再进行条件过滤。
• Using temporary：使用了临时表，常见于 GROUP BY 和 DISTINCT，需要优化。
• Using filesort：无法利用索引排序，需要在内存或磁盘排序，数据量大时性能差。
• Using index condition：使用了索引下推（ICP）优化。
• Using join buffer：JOIN 时被驱动表没有可用索引，使用了连接缓冲区。

执行这条 SQL，观察 Extra：

EXPLAIN SELECT user_id, create_time FROM t_order WHERE user_id = 12345;

由于查询字段 user_id 和 create_time 都在联合索引idx_user_create中，Extra 会显示Using index，表示完全走覆盖索引。

5. 索引调优实战场景：从慢 SQL 到执行计划优化

5.1 最左前缀原则与实际应用

联合索引遵循最左前缀原则。假设我们有一个联合索引idx_user_create(user_id, create_time)，那么可以命中该索引的查询条件包括：

• WHERE user_id = ?
• WHERE user_id = ? AND create_time > ?
• WHERE user_id = ? ORDER BY create_time

但如果查询条件是WHERE create_time > ?，优化器不会使用该索引，因为不满足最左前缀。

这里有一个容易被忽略的点：联合索引中的字段顺序设计跟查询频率密切相关。一个高频查询是“查某个用户最近订单”，那么(user_id, create_time)就是合理的顺序；但如果系统也频繁按时间范围查询所有用户订单，单独给 create_time 建索引可能更好。不要把多个不频繁使用的字段都塞进同一个联合索引。

5.2 ORDER BY 排序优化

Using filesort 是常见性能瓶颈。如果 SQL 需要对多个字段排序，而这些字段恰好在一个联合索引中且顺序一致，MySQL 可以直接利用索引的有序性完成排序，避免 filesort。

以下两条 SQL，第一条可以利用索引排序，第二条不能：

EXPLAIN SELECT * FROM t_order WHERE user_id = 100 ORDER BY create_time DESC; EXPLAIN SELECT * FROM t_order WHERE user_id = 100 ORDER BY amount DESC;

第一条的 WHERE 条件命中了 user_id，ORDER BY create_time 也符合联合索引顺序，代价很小。第二条的 amount 不在联合索引中，Extra 大概率出现Using filesort。

优化方向有两种：一是为高频排序查询单独设计索引，例如(user_id, amount)；二是尽量减少排序字段数量和排序数据量，比如先通过覆盖索引查出主键，再回表取完整数据。后者适合深分页场景。

5.3 深分页优化思路

MySQL 深分页慢的根源是 LIMIT 的分页偏移量太大。比如LIMIT 800000, 20，MySQL 需要先扫描出前 800020 行，再丢弃前 800000 行，代价非常大。

常见优化方案有两种：

方案一：延迟关联（Deferred Join）。先利用覆盖索引快速查出主键，再做 JOIN 回表取完整数据。

SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 800000, 20 ) tmp ON t.id = tmp.id;

子查询在索引上只扫描主键，代价小于全行回表。通过t_order的主键与子查询关联，再回表获取完整记录。

方案二：书签定位（基于游标）。记住上一页的最大 create_time 或 id，再把大于该值的数据作为下一页起点。

SELECT * FROM t_order WHERE status = 1 AND create_time < '2025-06-01 00:00:00' ORDER BY create_time DESC LIMIT 20;

这种方案适合适合 App 下拉加载、翻页大小固定的场景，不适合可随意跳页的后台列表。每次查询可以直接走索引定位到起点附近，避免了扫描大量已读数据。

5.4 覆盖索引防止回表

覆盖索引是减少 I/O 最直接的手段。它要求查询字段和 WHERE、ORDER BY、GROUP BY 中出现的字段都能被某个索引覆盖。

例如，有一个高频统计 SQL：

SELECT COUNT(*) FROM t_order WHERE status = 1;

如果 status 上没有索引，InnoDB 需要扫描整个聚簇索引统计行数，代价随表数据量线性增长。给 status 建立普通索引后，COUNT(*) 可以直接统计二级索引的键数量，因为二级索引比聚簇索引小很多，扫描代价大大降低。

再看一个查询：

SELECT user_id, COUNT(*) FROM t_order WHERE create_time >= '2025-01-01' AND create_time < '2025-02-01' GROUP BY user_id;

如果只创建create_time索引，查询时需要根据 user_id 回表，性能不理想。如果创建联合索引(create_time, user_id)，查询可以直接从索引中取得 user_id 和 create_time，通过Using index完成过滤和分组，不需要回表。

5.5 索引选择度过低的问题

不是所有列都适合建索引。索引选择度（Cardinality）指的是索引列去重后的值数量与总行数的比值。选择度太低，例如性别列只有 0 和 1 两个值，优化器大概率会放弃索引。

来看一个典型的错误设计：给订单表的 status 字段单独建索引。status 只有 0、1、2 三个值，数据分布均匀时，任意一个状态都包含几十万行，通过索引查找再回表的代价可能比全表扫描还高。优化器一旦判断索引代价更高，就会直接走 ALL。

处理低选择度字段的正确思路是组合其他高选择度字段建立联合索引。比如“查某个用户已支付的订单”，索引应该是(user_id, status)而不是单独索引status。由于 user_id 在等值条件下过滤掉了大量数据，status 只需要在索引内部过滤即可。

5.6 隐式类型转换与函数操作

索引列上做函数操作、运算或隐式类型转换，会导致索引失效。这是新手最容易踩的坑。

EXPLAIN SELECT * FROM t_order WHERE DATE(create_time) = '2025-06-01'; EXPLAIN SELECT * FROM t_order WHERE order_no = 1000000001;

第一条 SQL 对 create_time 使用了 DATE 函数，索引列不再是纯列，MySQL 无法直接比较索引键，只能放弃索引。第二条 SQL 中 order_no 是 VARCHAR 类型，但条件值是整数，MySQL 会把字符串列隐式转换为数字再做比较，同样导致索引失效。

正确写法应该保持索引列的纯净：

EXPLAIN SELECT * FROM t_order WHERE create_time >= '2025-06-01' AND create_time < '2025-06-02'; EXPLAIN SELECT * FROM t_order WHERE order_no = '1000000001';

凡是看到索引列被函数或运算包裹，第一反应就是改写 SQL，或者考虑生成列（MySQL 5.7+ 支持）和函数索引（MySQL 8.0.13+ 支持）。

5.7 LIKE 模糊查询与 OR 条件

LIKE 的索引使用规则是：后缀模糊不影响索引，前导模糊可能导致索引失效。

-- 可以走索引 EXPLAIN SELECT * FROM t_order WHERE order_no LIKE 'SN0000%'; -- 无法走索引或使用效果差 EXPLAIN SELECT * FROM t_order WHERE order_no LIKE '%0000%';

在 B+Tree 中，索引键按字典序排列。前缀已知，可以快速定位到范围起点；前缀未知，无法确定搜索起点，优化器只能扫描整个索引甚至全表。

OR 条件也需要注意。如果 OR 连接的字段中有一个没有索引，MySQL 往往退化为全表扫描。

EXPLAIN SELECT * FROM t_order WHERE user_id = 100 OR amount = 100.00;

amount 列没有索引，这条 SQL 很难同时利用多个索引进行合并，通常直接全表扫描。优化方式是拆分为两条 SQL 使用 UNION，或者确保 OR 两侧字段都有可用索引。在 MySQL 8.0 中，优化器存在 Index Merge 的能力，但依赖具体条件和数据分布，不建议把性能赌在优化器上。

5.8 主键设计对索引的影响

InnoDB 的聚簇索引叶子节点存储整行数据，二级索引叶子节点存储主键值。主键的大小直接影响所有索引的体积，主键越长，每个二级索引占用的空间越大，缓冲池中能缓存的索引页越少。

InnoDB 推荐使用自增 BIGINT 主键或 UUID 的比较方案需要分场景讨论。自增主键写入时是顺序追加，页分裂概率低；UUID 作为主键，随机性导致插入位置随机，频繁页分裂，性能下降明显。但从数据分布和分布式拆分角度，UUID 或雪花 ID 又具有唯一性识别上的优势。

折中方案是使用雪花 ID 或基于时间戳的分布式 ID，保持趋势递增，同时避免后端多节点写入冲突。如果业务表本身有天然的业务单号，比如 order_no，也不建议用它直接作为主键，因为业务字段可能变更，且长度通常大于 BIGINT，会增加索引体积。

6. 索引失效场景全汇总

索引失效是面试中最爱考、开发中最高频踩坑的部分。下面这几个场景建议反复记忆。

需要特别说明的是，范围查询后面的字段失效并不是索引完全失效。例如(user_id, create_time)，WHERE user_id = 100 AND create_time > '2025-01-01'中 user_id 的部分仍然使用索引，只是 create_time 由于范围断裂无法继续用于严格等值匹配。在优化器能力足够时，排序和分页仍然能受益于索引的有序性。

7. 高频 MySQL 索引面试题整理

7.1 为什么使用 B+Tree 而不是 B-Tree

B+Tree 相比 B-Tree 有几点显著优势：

• 非叶子节点只存键不存数据，可以容纳更多键，树更矮，磁盘 I/O 更少。
• 叶子节点形成有序双向链表，范围查询极其高效。
• 所有数据都在叶子节点，查询路径长度稳定，性能波动小。

数据库存储引擎非常依赖范围扫描能力，比如between、>、<等条件。B-Tree 的中序遍历需要跨层访问，效率远低于 B+Tree 的叶子链表。

7.2 聚簇索引和非聚簇索引的区别

聚簇索引的叶子节点直接存整行数据，表数据物理顺序与索引顺序一致，InnoDB 中只有一个聚簇索引。二级索引的叶子节点存索引列和主键值，通过二级索引查数据需要回表。

回答时可以补充 InnoDB 和 MyISAM 的对比：MyISAM 的索引文件与数据文件分离，索引叶子节点存放的是数据行的物理地址，无论主键索引还是二级索引都在同一个索引文件中。

7.3 为什么主键推荐自增 BIGINT

自增主键写入顺序递增，新数据插入时直接追加到 B+Tree 尾部，页分裂概率低，索引维护代价小。UUID 主键是随机字符串，插入位置随机，容易导致页分裂、数据碎片增加、缓冲池命中率下降。

此外，主键长度直接决定二级索引大小。BIGINT 占 8 字节，UUID 如果存 VARCHAR(32) 则占 32 字节以上，二级索引被显著放大，内存和磁盘资源消耗更高。分布式场景可以使用雪花 ID，保持趋势递增并减少长度。

7.4 什么是最左前缀原则

联合索引可以看作一个多级排序结构，先按第一列排序，第一列相同再按第二列排序。查询时必须从第一列开始匹配，跳过第一列就无法高效定位。

举例来说，索引(a, b, c)可以支持以下查询：

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 ORDER BY b

但不能支持WHERE b = 2或WHERE c = 3。面试时可以补充：范围查询列之后的索引列无法用于等值定位，但仍可能在覆盖索引和排序中发挥作用。

7.5 覆盖索引和回表的关系

覆盖索引是指查询涉及的所有字段都被索引覆盖，查询过程不需要回表。它是解决回表问题最彻底的手段。

实际工作中，不要直接把整个表的字段都塞进一个超宽索引。索引字段越多，写入代价越高。优先考虑高频查询的 SELECT 字段。

7.6 深分页为什么慢，怎么优化

深分页是指LIMIT offset, size中 offset 很大的情况。MySQL 必须先扫描 offset + size 行才能拿到最终数据，offset 越大，无用工作越多。优化方案包括延迟关联和书签定位，前文已有说明。面试时如果能补充这两种方案的优缺点，会显得更加完整。

8. 常用调优工具与命令

8.1 慢查询日志开启

调优的第一步往往是定位慢 SQL。开启慢查询日志可以直接记录执行时间超过阈值的 SQL。

-- 查看当前慢查询日志状态 SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 设置日志文件路径 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

线上环境不建议长期全量开启慢查询日志，建议定期分析日志或只开启预设阈值，避免日志刷写影响数据库性能。MySQL 8.0 中也支持performance_schema或sys.schema_unused_indexes来发现冗余索引。

8.2 使用 SHOW INDEX 查看索引信息

SHOW INDEX FROM t_order;

输出中的Cardinality字段表示索引选择度估值。如果 Cardinality 与表行数相近，说明索引列区分度高，适合索引过滤；如果远低于行数，需要考虑是否真的需要这个索引。

8.3 Profile 与 Optimizer Trace

EXPLAIN 只能看到执行计划，无法看到真实的执行耗时。MySQL 的SHOW PROFILE可以查看 SQL 执行的各阶段耗时，适合分析 Sending data、Sorting result 等阶段。

MySQL 8.0 中SHOW PROFILE已经废弃，官方推荐使用performance_schema.events_statements_history_long。也可以使用OPTIMIZER_TRACE查看优化器决策过程，但信息量较大，适合深入分析极端场景。

SET optimizer_trace = "enabled=on"; SELECT * FROM t_order WHERE user_id = 12345 AND create_time > '2025-01-01'; SELECT * FROM information_schema.OPTIMIZER_TRACE;

通过 trace 可以清楚看到优化器权衡索引和全表扫描的成本计算过程。

9. 常见问题与排查思路

9.1 为什么加了索引但 EXPLAIN 显示不走索引

可能原因之一是数据量太少。优化器认为全表扫描 [通过顺序 I/O 读取] 比索引查找加回表更便宜。另一种原因是索引列的可选择性低，例如性别、状态这类低 Cardinality 字段。还有一种情况是 SQL 写法导致索引失效，例如对索引列使用了函数。

排查顺序建议：先看 SQL 写法是否符合索引使用规范，再看执行计划中的rows和type，最后结合实际数据分布判断是否应该强制索引。

9.2 联合索引字段顺序怎么确定

经验法则是：等值查询字段放前面，范围查询字段放后面。过滤性强的字段放前面。但最终还是要通过实际 SQL 组合频率来权衡。

比如系统低频查询是WHERE create_time > ? AND user_id = ?，高频查询是WHERE user_id = ?。此时把 user_id 放第一位，即使 create_time 是范围字段，也能保证高频查询命中索引前缀。

9.3 为什么查询变慢但 EXPLAIN 显示还是走了索引

索引访问很快，但回表次数太多。假设二级索引命中了 10 万行数据，每行都需要回表获取完整行，等于做了 10 万次随机 I/O。这种情况下即使 type 为 ref，性能也很差。

解决方案是把 SQL 改写为覆盖索引查询，利用延迟关联减少回表行数，或者检查是否应该调整索引设计，让过滤条件更精准。

9.4 为什么建立了索引但写入性能下降

每建一个二级索引，INSERT、UPDATE、DELETE 都需要额外维护索引结构。数据量越大，索引越多，写入放大越严重。

线上大表加索引时要注意 DDL 锁问题。MySQL 8.0 支持在线 DDL，但大表加索引仍然会产生主从延迟。可以在低峰期执行，或使用 gh-ost、pt-online-schema-change 等工具平滑变更。

10. 索引工程落地最佳实践

10.1 索引设计规范建议

• 每个表主键推荐 BIGINT 自增或趋势递增分布式 ID，避免 UUID 大字段主键。
• 控制单表索引数量，一般建议不超过 5 个；索引不是越多越好。
• 联合索引字段数控制在 3 到 4 个以内，避免过多冗余字段。
• 更新频繁的字段谨慎建索引，维护代价高。
• 禁止在索引列上做函数操作和隐式类型转换。
• 使用覆盖索引优化高频查询，优先让 SELECT 字段落在索引中。
• 使用SHOW INDEX定期排查冗余索引、重复索引。

10.2 慢 SQL 治理流程

推荐以下闭环流程：

1. 开启慢查询日志，设置合理阈值，例如 1 秒。
2. 定期采集慢 SQL，分析执行计划。
3. 根据 type、rows、Extra 定位问题。
4. 设计或调整索引，小流量验证。
5. 上线后持续观察执行计划和耗时变化。
6. 下线不再使用的冗余索引。

10.3 生产环境变更注意事项

涉及线上数据库变更时，必须遵守一些底线原则：

• 任何加索引、删索引、改表结构操作前，先在测试环境压测验证。
• 大表 DDL 操作尽量使用在线变更工具，避免锁表阻塞业务。
• 涉及 DELETE 或 UPDATE 的语句，必须确认 WHERE 条件能有效利用索引，避免一次更新或删除过多数据。
• 生产环境禁止执行没有 WHERE 条件的 UPDATE 和 DELETE。
• 变更前做好备份，必要时使用 MySQL 从库或者备份工具进行恢复演练。

10.4 SQL 审核制度

团队协作中，SQL 审核非常重要。可以在提交代码前引入 SQL 检查，由 DBA 或资深开发统一评审：

• 是否滥用 SELECT *？是否需要覆盖索引？
• 多表 JOIN 的连接字段是否有索引？
• 有没有索引失效的写法？
• 分页查询是否用了深分页？
• 事务范围是否过长？

如果团队有自动化平台，也可以接入 SQL 审核工具，把 EXPLAIN 的 type、rows 等指标作为质量门禁。

11. 深入进阶：从索引调优到查询优化器

索引调优做到一定程度，瓶颈往往不在索引本身，而在 SQL 写法与优化器的交互。MySQL 的查询优化器会根据表统计信息估算执行代价，但统计信息可能过期，也可能因数据分布不均匀产生错误判断。这时候可以用ANALYZE TABLE更新统计信息：

ANALYZE TABLE t_order;

对于复杂查询，还需要关注 JOIN 顺序、驱动表选择、子查询改写等问题。希望深入的话，建议后续按照以下路线继续学习：

• 学习 EXPLAIN 的 format=tree 选项，了解 MySQL 8.0 基于代价的优化细节。
• 练习使用 OPTIMIZER_TRACE 分析优化器的成本计算。
• 理解 InnoDB 的索引页结构、页分裂和合并机制。
• 学习 JOIN 和子查询的执行方式差异，例如 Semi-Join、Materialization 等。
• 了解 MySQL 8.0 的降序索引、隐藏索引、函数索引等新特性。
• 通过 sys schema 和 performance_schema 建立监控指标体系。

索引调优是一项长期工作，核心思路始终是围绕“减少磁盘 I/O、减少回表、避免额外排序和临时表”这三个方向展开。建议先在自己負責的业务表上做一次完整的慢 SQL 巡检，把 EXPLAIN 结果和自己预判的结果做对比，坚持两周左右，对索引的理解会有明显提升。如果本文对你有帮助，可以收藏备用，后续我会继续整理 MySQL 事务、锁机制、主从复制等数据库系列内容。