技术文档

Appearance

索引性能分析实践指南：从理论到落地的深度解析 ​

阅读对象：全栈开发、数据库运维、后端工程师 技术基准：MySQL 8.0 + InnoDB 存储引擎 贯穿案例：电商平台的用户表（users）、订单表（orders）

本文不讲纯理论，而是把索引优化串成一条拿来就能用的完整流水线：定位慢 SQL → 拆解耗时 → 分析执行计划 → 设计索引 → 量化验证 → 持续监控。每个环节都配有可直接复制执行的 SQL 和真实业务场景。

一、为什么要做索引性能分析？ ​

1. 索引的本质：书的目录 ​

把一张没有索引的数据库表想象成一本没有目录的 1000 页厚书。

现在你要找"第 458 页那个叫张三的订单"，没有目录怎么办？只能从第 1 页开始，一页一页往后翻，翻到第 458 页才能找到。运气最差的情况，要翻完整个 1000 页——这就是全表扫描，时间复杂度 O(n)，表里有 120 万行数据，就要检查 120 万行。

而索引就是这本书的目录：先花 1 秒钟查目录，找到"张三"在第 458 页，然后直接翻过去——这就是索引查找，复杂度 O(logn)。120 万行数据的 B+ 树索引，只需要 3~4 次磁盘 IO 就能定位到目标。

对比项无索引（全表扫描）有索引（B+ 树）
类比逐页翻书查目录定位
复杂度O(n)O(logn)
120 万行需要读取的行数120 万3~4 次磁盘 IO
典型耗时秒级毫秒级

2. 矛盾：索引不是免费的午餐 ​

既然索引这么快，那给所有字段都加上索引不就行了？不行。

索引的本质是一棵额外维护的 B+ 树。每加一个索引，就意味着：

  • 写入变慢：每次 INSERT、UPDATE、DELETE，除了改数据本身，还要同步维护每一棵索引树的结构。索引越多，写入时维护的树就越多。
  • 磁盘变胖：索引文件本身要占磁盘空间，大表的索引甚至可能比数据文件还大。
  • 内存变挤：InnoDB 会把热点索引页缓存到 Buffer Pool，索引太多会挤占缓存数据页的空间。

这就像一本书：目录只有 2 页时很方便；如果每个段落都给你编一个目录，书的一半都是目录，翻起来反而累。

3. 结论：索引优化必须是"科学流程"，不是"拍脑袋" ​

盲目加索引 = 查询没快多少，写入先垮了。

正确的姿势是四步走：

  1. 先找——找到真正慢、且被高频调用的 SQL（而不是凭感觉猜）；
  2. 再析——分析它为什么慢（慢在哪个阶段？是不是没走索引？）；
  3. 精准加——针对问题设计最合适的索引；
  4. 量化验——用数据证明索引真的有效，而不是"感觉快了"。

下面进入完整流程。

二、排查第一步：定位低效 SQL ​

优化的第一步不是猜，而是找。要找到那些"用得最多、扫得最广、返回最少"的 SQL——它们才是性价比最高的优化目标。

1. 查看语句执行频次：给 SQL 排优先级 ​

MySQL 内部维护了全局统计计数器，可以告诉我们数据库从启动到现在，各类语句各执行了多少次。

sql
-- 查看全局各类 SQL 语句的累计执行次数
SHOW GLOBAL STATUS LIKE 'Com_%';

输出示例（截取关键行）：

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Com_select              | 86420000  |   ← 查询执行了 8600 多万次
| Com_insert              | 3200000   |
| Com_update              | 2100000   |
| Com_delete              | 150000    |
+-------------------------+-----------+

关键指标解读：

指标含义能说明什么
Com_selectSELECT 累计执行次数读压力
Com_insert / Com_update / Com_delete增删改累计执行次数写压力
Slow_queries慢查询累计次数慢查询的规模
sql
-- 查看 InnoDB 引擎累计扫描/读取了多少行数据（判断读放大程度的核心指标）
SHOW STATUS LIKE 'InnoDB_rows_read';
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| Innodb_rows_read | 125000000000 |   ← 累计扫了 1250 亿行
+------------------+--------------+

怎么用这两个数据做决策？

  • 用 Innodb_rows_read ÷ Com_select 粗算一下：平均每次查询要扫多少行。如果平均每次 SELECT 要扫几千上万行，说明大量查询没走索引。
  • 用 Com_select 与 Com_insert + Com_update + Com_delete 的比例，判断系统是读多写少还是写多读少：读多写少的系统可以放心加索引；写多读少的系统加索引要克制。

业务场景还原：

电商平台日活 10 万。运营同学反馈"后台偶尔有点卡"。查频次统计发现：

  • 商品列表查询：每天执行 80 万次；
  • 订单详情查询：每天执行 5 万次；
  • 售后工单查询：每天执行 800 次。

这时哪怕售后查询慢到 3 秒，也不如先把"只慢了 200ms 但每天跑 80 万次"的商品列表查询优化掉——优化优先级 = 频次 × 单次耗时。

2. 慢查询日志：精准捕捉超时 SQL ​

频次统计只能告诉我们"大盘"，具体到是哪条 SQL 慢，就要靠慢查询日志了。

它是 MySQL 自带的"低效 SQL 记录本"：任何一条 SQL 执行时间超过你设定的阈值，就自动被记录下来，包括执行时间、扫描行数、锁等待时间、SQL 原文。这是找慢查询的第一入口。

第一步：开启慢查询日志

修改配置文件 my.cnf（Linux）或 my.ini（Windows）：

ini
[mysqld]
slow_query_log = 1                                  # 1 = 开启慢查询日志
long_query_time = 1                                 # 阈值：执行超过 1 秒就记录
slow_query_log_file = /var/log/mysql/slow.log       # 日志文件路径
log_queries_not_using_indexes = 1                   # 额外福利：没走索引的语句也记下来

生产环境建议阈值设为 1 秒；压测/调优期可以临时调到 0.1 秒抓得更细。

也可以不改配置文件，用 SQL 临时开启（注意：重启后失效）：

sql
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

-- 确认是否开启成功
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

第二步：用 mysqldumpslow 分析日志

慢日志文件直接看很痛苦（同样的 SQL 参数不同会重复出现），MySQL 自带了聚合分析工具 mysqldumpslow：

bash
# 按平均执行时间排序，取最慢的前 10 条（找"单次最慢"）
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 按扫描行数排序，取前 10 条（找"扫得最广"）
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 按出现次数排序，取前 10 条（找"高频慢"，性价比最高）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

输出示例：

Count: 200000  Time=2.30s (460000s)  Lock=0.00s  Rows=1500
  SELECT * FROM orders WHERE user_id = N AND create_time > 'S'

这一行告诉我们：这条 SQL 出现了 20 万次，平均每次 2.3 秒，累计吃掉 46 万秒的数据库时间。

案例落地：

慢查询日志捕获到这样一条用户订单查询：

sql
SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2024-01-01';
-- 执行时间 2.3s，每天调用 20 万次

频次高、单次慢，优化价值极高——带着它进入下一步深度分析。

三、慢 SQL 深度分析：从耗时拆解到执行计划 ​

找到了嫌疑人（慢 SQL），接下来要做两件事：先搞清楚它慢在哪个阶段（SHOW PROFILES），再搞清楚 MySQL 打算怎么执行它（EXPLAIN）。

1. SHOW PROFILES：给 SQL 做"全身 CT" ​

SHOW PROFILES 能把一条 SQL 从解析、优化到执行的每个阶段耗时全部列出来，就像体检报告：不是笼统地说"你身体不好"，而是精确到"哪个指标异常"。

第一步：开启 profiling（仅当前会话有效）

sql
SET profiling = 1;

-- 确认开启
SHOW VARIABLES LIKE 'profiling';

第二步：执行目标 SQL

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

第三步：查看最近所有 SQL 的耗时清单

sql
SHOW PROFILES;
+----------+------------+------------------------------------------------------+
| Query_ID | Duration   | Query                                                |
+----------+------------+------------------------------------------------------+
|        1 | 2.31452000 | SELECT * FROM orders WHERE user_id = 1001 AND ...    |
+----------+------------+------------------------------------------------------+

第四步：查看指定 SQL 的阶段耗时明细

sql
SHOW PROFILE FOR QUERY 1;

-- 进阶：还可以看 CPU、块 IO、上下文切换等系统资源占用
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

阶段耗时明细示例：

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000042 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000031 |
| init                 | 0.000018 |
| optimizing           | 0.000085 |   ← 优化器制定执行计划，仅 0.08ms
| statistics           | 0.000120 |
| executing            | 0.000012 |
| Sending data         | 1.968542 |   ← 占比 85%，瓶颈在这！
| end                  | 0.000009 |
| closing tables       | 0.000011 |
+----------------------+----------+

案例分析：

Sending data 阶段耗时 1.97 秒，占总耗时 85%。这个阶段是 MySQL 扫描数据行、过滤条件、把结果发给客户端的过程。它耗时高，几乎可以断定：扫描了太多不该扫的行。

顺便科普几个常见阶段的含义：

  • Opening tables 慢 → 表太多或表缓存不够；
  • optimizing 慢 → SQL 结构太复杂（几十个 JOIN）；
  • Sending data 慢 → 扫描行数过多，索引问题高发区；
  • Creating tmp table 慢 → 用了临时表（常见于 GROUP BY 没走索引）。

既然怀疑是扫描阶段的问题，下一步就该看 MySQL 的"执行路线图"——EXPLAIN。

2. EXPLAIN：解读 MySQL 的执行计划 ​

EXPLAIN 的作用是：在不真正执行 SQL 的前提下，告诉你 MySQL 打算怎么执行这条语句——走哪个索引、扫多少行、以什么顺序 JOIN。它是索引优化的核心工具，地位相当于医生的 CT 报告。

使用方式：在任何 SELECT 前面加 EXPLAIN 即可。

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

核心字段逐个拆解：

字段含义重点关注什么
id查询块的执行序号id 大的先执行；id 相同从上往下执行。嵌套子查询时 id 会递增
select_type查询的类型SIMPLE 简单查询（无子查询/UNION）；PRIMARY 最外层查询；SUBQUERY 子查询；DERIVED 派生表；UNION 联合查询的第二及后续部分
table这一行访问的是哪张表多表 JOIN 时会列出多张表
type访问类型，最重要的一列详见下方性能排序，至少要达到 range
possible_keysMySQL 认为"可能"用到的索引为 NULL 说明连候选索引都没有
key实际真正用到的索引为 NULL 说明没走索引，红色警报
key_len实际使用了索引的前多少个字节用来判断联合索引用到了前几列
rows预估要扫描的行数越小越好，这是优化器的估算值
filtered过滤后剩余行数的百分比100% 表示扫描的行全部有效
Extra额外补充信息Using index 覆盖索引（好）；Using filesort 额外排序（警惕）；Using temporary 用了临时表（警惕）；Using where 存储引擎返回后还在服务端过滤

type 字段性能排序（从快到慢，背下来）：

system > const > eq_ref > ref > range > index > ALL
type 值含义典型场景评价
system表里只有一行系统表理论最快
const主键/唯一索引等值查询，最多返回一行WHERE id = 1001完美
eq_ref多表 JOIN 时，被驱动表用主键/唯一索引关联JOIN ON 主键完美
ref普通（非唯一）索引等值查询WHERE user_id = 1001很好
range索引范围查询WHERE create_time > '2024-01-01'、BETWEEN、IN可接受
index扫描整棵索引树（但没回表）只用索引列做全索引扫描一般
ALL全表扫描没有任何索引可用红色警报

经验法则：生产环境的查询，type 至少要达到 range 级别，最好 ref 以上。看到 ALL 就要立刻处理。

案例落地：

对慢查询日志抓到的那条 SQL 执行 EXPLAIN：

sql
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2024-01-01';
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | filtered| Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | 1.2M |    10.0 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

逐字段诊断：

  • type = ALL：全表扫描；
  • possible_keys = NULL：连可用的候选索引都没有；
  • key = NULL：确认没走任何索引；
  • rows = 120 万：每次查询要扫 120 万行；
  • filtered = 10%：扫的 120 万行里只有 10% 满足条件，90% 的行是白扫的。

实锤结论：2.3s 的元凶就是缺少合适的索引导致全表扫描。病根找到了，下面开药方。

四、索引规则与失效场景：避坑指南 ​

1. 验证索引效率：加完索引怎么知道好不好用？ ​

很多同学的误区是：索引一加，任务完成。错。加索引不是终点，量化验证才是。

针对上面的问题，创建联合索引：

sql
CREATE INDEX idx_user_time ON orders(user_id, create_time);

然后用三个核心指标验证效果：

指标①：执行时间（最直观）

sql
SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2024-01-01';
优化前：2.3s   →   优化后：0.02s   （提升 100 倍以上）

指标②：EXPLAIN 执行计划（看本质）

字段优化前优化后
typeALL（全表扫描）ref / range（走索引）
keyNULLidx_user_time
rows120 万1200

扫描行数从 120 万降到 1200 行，减少 99.9%。

指标③：Handler 读取次数（看磁盘 IO）

Handler_read% 系列计数器记录了 MySQL 通过存储引擎读数据的次数，是衡量"磁盘读了几次"的权威指标。使用方法是：执行查询前后各查一次，取差值。

sql
-- 第一次查询，记录基线
SHOW STATUS LIKE 'Handler_read%';

-- 执行目标查询
SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2024-01-01';

-- 第二次查询，对比差值
SHOW STATUS LIKE 'Handler_read%';

关键指标含义：

指标含义优化后的期望表现
Handler_read_key通过索引键读取的次数大幅上升（好事，说明在用索引）
Handler_read_rnd_next全表扫描逐行读取的次数大幅下降（好事，全表扫描消失了）
Handler_read_next按索引顺序读取下一行的次数上升（范围扫描会用到）

三个指标相互印证，才能说"这次优化是真的有效"。

2. 最左前缀法则：联合索引的核心逻辑 ​

这是联合索引最容易踩坑、面试必考的规则。

通俗类比：联合索引是"按顺序排的多级目录"。创建 (user_id, create_time, order_status) 联合索引，数据的排序方式是：

  1. 先按 user_id 排序；
  2. user_id 相同的，再按 create_time 排序；
  3. create_time 也相同的，再按 order_status 排序。

就像字典的"拼音 + 笔画"目录：先按拼音排序，同音字再按笔画排序。你只有先按拼音查，笔画目录才有意义——直接按笔画查字，目录完全帮不上忙。

sql
CREATE INDEX idx_multi ON orders(user_id, create_time, order_status);

案例①：从最左开始连续使用——命中完整索引

sql
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND create_time > '2024-01-01' AND order_status = 1;
type: range   key: idx_multi   key_len: 最大长度（三列全用上）

✅ 三个字段全部走索引。

案例②：跳过最左列——索引整体失效

sql
EXPLAIN SELECT * FROM orders
WHERE create_time > '2024-01-01' AND order_status = 1;
type: ALL   key: NULL

❌ 查询条件跳过了最左边的 user_id，相当于"不查拼音直接查笔画"，索引完全用不上，退化为全表扫描。

案例③：用到最左列但跳过中间列——只命中前缀

sql
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND order_status = 1;
type: ref   key: idx_multi   key_len: 4（只有 user_id 部分）

⚠️ user_id 用上了索引；但跳过了中间的 create_time，order_status 在索引里不再是"有序"的，只能回表后逐行过滤。

怎么用 key_len 验证索引用到了第几列？

key_len 表示实际使用的索引字节数。以 idx_multi(user_id INT, create_time DATETIME, order_status TINYINT) 为例：

  • user_id（INT）：4 字节
  • create_time（DATETIME，8.0 中 5 字节 + NULL 标记 1 字节）：6 字节
  • order_status（TINYINT + NULL 标记）：2 字节
key_len说明
4只用到了 user_id 一列
10用到了 user_id + create_time 两列
12三列全部用上

通过 key_len 反推索引使用长度，是排查"索引只生效一半"问题的利器。

补充规则：联合索引中遇到范围查询（>、<、BETWEEN、LIKE 'xx%'）时，该列之后的列索引失效。例如 WHERE user_id = 1001 AND create_time > '2024-01-01' AND order_status = 1，create_time 是范围条件，order_status 的索引用不上。所以设计联合索引时，等值条件列在前，范围条件列在后。

3. 索引失效的 7 种高频场景 ​

以下每个场景都给出：错误写法 → 失效原因 → 正确改写。这是日常开发中最常踩的坑。

场景①：索引字段参与运算或函数

sql
-- ❌ 错误：对索引列使用了函数
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

失效原因：索引 B+ 树里存的是 create_time 的原始值（如 2024-03-15 10:30:00），而 YEAR(create_time) 计算后的结果（2024）在索引里根本不存在。MySQL 只能对每一行都算一遍函数再比较——全表扫描。

sql
-- ✅ 正确：索引列保持"干净"，把计算挪到条件右侧
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

场景②：使用 != / <> / IS NOT NULL

sql
-- ❌ 错误：不等判断
SELECT * FROM orders WHERE user_id != 1001;

失效原因：B+ 树索引擅长"定位到某个值/某个范围"，但"不等于"意味着要跳过某个值扫描其余所有，索引的有序性帮不上忙，优化器通常放弃索引。

sql
-- ✅ 正确：转化为两个范围查询的并集
SELECT * FROM orders WHERE user_id < 1001
UNION ALL
SELECT * FROM orders WHERE user_id > 1001;

-- 或者从业务上拆分：排除法换正向列举（比如状态不等于"已删除" → 状态 IN (正常, 已发货, 已完成)）

场景③：模糊查询以 % 开头

sql
-- ❌ 错误：通配符 % 在最前面
SELECT * FROM orders WHERE order_sn LIKE '%10001%';

失效原因：B+ 树是按字符串从左到右排序的。LIKE '%10001%' 不知道目标以什么开头，无法在树中定位起点，只能全表扫。

sql
-- ✅ 方案一：改成前缀匹配（知道开头才能用索引）
SELECT * FROM orders WHERE order_sn LIKE '2024%';

-- ✅ 方案二：真正的"任意位置关键词搜索"是搜索引擎的活，
-- 用 Elasticsearch / 全文索引，别让 B+ 树硬扛

场景④：字段类型不匹配（隐式类型转换）

sql
-- ❌ 错误：order_sn 是 VARCHAR 类型，查询时传了数字
SELECT * FROM orders WHERE order_sn = 20240001;

失效原因：MySQL 会默默做隐式类型转换，实际执行的是 WHERE CAST(order_sn AS SIGNED) = 20240001——等于对索引列套了函数，与场景①同理，索引失效。

sql
-- ✅ 正确：参数类型与字段定义严格一致，字符串加引号
SELECT * FROM orders WHERE order_sn = '20240001';

这个坑在 Java 开发里尤其常见：字段是 VARCHAR，代码里传了 Long。肉眼完全看不出来，只能靠 EXPLAIN 发现。

场景⑤：OR 连接的条件中存在非索引字段

sql
-- ❌ 错误：user_id 有索引，但 phone 没有索引
SELECT * FROM orders WHERE user_id = 1001 OR phone = '13800138000';

失效原因：OR 的语义是"满足任意一个条件就行"。phone 没有索引，意味着查 phone 那半边必须全表扫——既然都要全表扫了，优化器干脆整个查询都不用索引。

sql
-- ✅ 正确：拆成两个各自走索引的查询，用 UNION 合并
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE phone = '13800138000';
-- 前提：给 phone 也建上索引

场景⑥：MySQL 评估全表扫描更快（优化器的"自作主张"）

sql
-- 这条 SQL 写法没有任何问题，索引也存在
SELECT * FROM orders WHERE user_id > 100;

失效原因：这条 SQL 可能命中表里 80% 的数据。优化器算了一笔账：走索引要扫描索引树 + 逐行回表 80 万次，比直接顺序全表扫描还慢。一般经验值是命中数据超过表总量约 20% 时，优化器倾向于全表扫描。

这不是 bug，是优化器的理性决策。应对方式：

  • 如果业务确实要取大量数据，接受全表扫描（或走离线数仓）；
  • 如果确信索引更快，用 FORCE INDEX 强制（见下文）；
  • 检查统计信息是否过期，执行 ANALYZE TABLE orders; 让优化器拿到准确数据再决策。

场景⑦：多索引时优化器选错索引

sql
-- orders 表上同时存在 idx_user_time(user_id, create_time) 和 idx_status(order_status)
SELECT * FROM orders WHERE user_id = 1001 AND order_status = 1;

失效原因：优化器基于统计信息估算每个索引的成本。如果统计信息过期（比如刚批量导入了 500 万行数据），它可能误判 idx_status 更优，实际执行却很慢。

解决方案：

sql
-- 方案一：更新统计信息，让优化器基于准确数据重新决策
ANALYZE TABLE orders;

-- 方案二：用 SQL 提示强制指定索引（见下一节）

五、进阶索引优化：索引类型的选型与实践 ​

1. SQL 提示：手动干预优化器的选择 ​

绝大多数情况应该信任优化器，但统计信息失真时它也会犯错。SQL 提示（Index Hint）就是留给人的"一票否决权"：

sql
-- ① USE INDEX：给优化器"提建议"（它可以不听）
SELECT * FROM orders USE INDEX(idx_user_time)
WHERE user_id = 1001 AND create_time > '2024-01-01';

-- ② FORCE INDEX：下"死命令"，强制使用（必须听）
SELECT * FROM orders FORCE INDEX(idx_user_time)
WHERE user_id = 1001 AND create_time > '2024-01-01';

-- ③ IGNORE INDEX：拉黑某个索引，让优化器别用它
SELECT * FROM orders IGNORE INDEX(PRIMARY)
WHERE user_id = 1001;

案例：大促批量导入历史订单后，统计信息过期，MySQL 误选了主键索引执行这条查询，耗时从 0.02s 恶化到 1.8s。应急处理：

sql
SELECT * FROM orders FORCE INDEX(idx_user_time)
WHERE user_id = 1001 AND create_time > '2024-01-01';
-- 执行时间立刻恢复 0.02s

注意：FORCE INDEX 是应急手段而非日常手段。SQL 里写死索引名，会让 SQL 和索引结构耦合——以后索引改名、删索引，SQL 就报错。根本解法还是 ANALYZE TABLE 更新统计信息、或者重新审视索引设计。

2. 覆盖索引 & 回表查询：减少磁盘 IO 的核心技巧 ​

先理解 InnoDB 的索引结构：

  • 聚簇索引（主键索引）：叶子节点存的是整行数据；
  • 二级索引（普通索引）：叶子节点只存"索引列的值 + 主键值"，不存整行数据。

回表：用二级索引查到主键后，还要拿着主键去聚簇索引里再查一次，才能拿到整行数据。相当于：你翻目录找到了页码，还得再翻到那一页拿内容——多了一次磁盘 IO，命中行数多时代价巨大。

二级索引 [user_id=1001] → 拿到主键 id=50001 → 回表查聚簇索引 → 拿到整行数据
        （一次索引查找）                    （又一次索引查找）

覆盖索引：如果你要查的字段全部都在索引里，就不需要回表了——索引里直接拿结果，省掉第二次查找。相当于：目录上直接写了答案，连翻页都省了。

sql
-- 已有联合索引 idx_user_time(user_id, create_time)

-- ✅ 覆盖索引：SELECT 的字段（user_id、create_time）全在索引里
SELECT user_id, create_time FROM orders WHERE user_id = 1001;
EXPLAIN 结果中 Extra = Using index   ← 覆盖索引的标志，没有回表
sql
-- ❌ 需要回表：SELECT * 包含的 address、amount、remark 等字段不在索引里
SELECT * FROM orders WHERE user_id = 1001;
EXPLAIN 结果中 Extra 没有 Using index   ← 每命中一行都要回表取整行数据

实践建议：

  • 高频查询避免 SELECT *，只 SELECT 需要的字段，给覆盖索引创造条件；
  • 极端高频的查询，可以针对性地把查询字段"塞进"联合索引，人为制造覆盖索引。比如订单列表页只需要展示 (user_id, create_time, order_status, amount)，就把这四个字段建成一个联合索引，列表查询全程不回表。

3. 前缀索引：解决长字段的索引空间问题 ​

问题场景：用户表要支持按邮箱查用户。email 是 VARCHAR(100)，平均长度 30 字符。给整列建索引：

  • 每个索引项存 100 字节，一千万用户就是约 1GB 的索引；
  • 索引大 → 一个 B+ 树节点能装的索引项少 → 树变高 → IO 变多。

前缀索引：只取字段的前 N 个字符建索引，用一点点区分度的损失，换来索引体积的大幅缩小。

第一步：计算区分度，找到合适的前缀长度

区分度 = 前缀不重复的数量 ÷ 总行数，越接近 1（100%）越好。

sql
-- 分别测试不同前缀长度的区分度
SELECT
  COUNT(DISTINCT LEFT(email, 8))  / COUNT(*) AS prefix_8,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
  COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15,
  COUNT(DISTINCT email)           / COUNT(*) AS full_col
FROM users;
+----------+-----------+-----------+----------+
| prefix_8 | prefix_10 | prefix_15 | full_col |
+----------+-----------+-----------+----------+
|   0.9123 |    0.9658 |    0.9989 |   1.0000 |
+----------+-----------+-----------+----------+

经验值：区分度超过 95% 就够用。上表中前 10 个字符的区分度已达 96.6%，性价比最优。

第二步：创建前缀索引

sql
CREATE INDEX idx_email_prefix ON users(email(10));

前缀索引的两个局限（选型时要知道）：

  1. 无法做覆盖索引：索引里存的不是完整值，MySQL 必须回表确认完整 email 是否匹配；
  2. 不能用于 ORDER BY / GROUP BY：前缀排序不等于完整值排序。

4. 单列索引 vs 联合索引：什么时候用哪个？ ​

先记住一个关键事实：MySQL 一次查询通常只使用一个索引（index merge 是少数例外场景）。这意味着：

给 user_id、create_time、order_status 各建一个单列索引，执行多条件查询时，MySQL 只能挑其中一个索引用，剩下的条件要在结果集里逐行过滤——三个索引废了两个。

选型决策：

场景 A：单一条件的高频查询 → 单列索引足够

sql
-- 业务：只按用户 ID 查订单（比如"我的订单"入口）
SELECT * FROM orders WHERE user_id = 1001;

-- 方案：单列索引即可
CREATE INDEX idx_user ON orders(user_id);

场景 B：多条件组合查询 → 联合索引

sql
-- 业务：按"用户 + 时间范围 + 订单状态"组合查询（订单筛选页）
SELECT * FROM orders
WHERE user_id = 1001 AND create_time > '2024-01-01' AND order_status = 1;

-- 方案：一个联合索引顶三个单列索引
CREATE INDEX idx_multi ON orders(user_id, create_time, order_status);

真实对比数据（120 万行 orders 表实测量级）：

方案EXPLAIN type扫描行数耗时
三个单列索引ref（只用上 user_id 一个）约 5 万（剩余两列回表后过滤）1.5s
联合索引range（三列全用上）约 8000.15s

效率相差 10 倍以上。

sql
-- 验证：EXPLAIN 中 key 命中联合索引，key_len 达到三列总长度，rows 大幅下降
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND create_time > '2024-01-01' AND order_status = 1;

联合索引设计三原则：

  1. 区分度高的字段放左边（user_id 区分度高，order_status 只有几种取值，放右边）；
  2. 等值查询列在前，范围查询列在后（范围列后面的列索引失效）；
  3. 一个联合索引尽量覆盖多个查询场景：有了 (user_id, create_time, order_status)，就不需要再建 (user_id)、(user_id, create_time)——最左前缀天然兼容。

六、索引性能分析完整 Workflow 总结 ​

把全文所有技术点串成一套可复用的闭环流程：

① 定位：慢查询日志（mysqldumpslow）+ Com_% / Innodb_rows_read 频次统计
        → 找出「高频 × 单次慢」的性价比最高优化目标
        ↓
② 拆解：SET profiling = 1 → SHOW PROFILES → SHOW PROFILE FOR QUERY N
        → 确认慢在哪个阶段（Sending data 占比高 = 扫描行数过多）
        ↓
③ 分析：EXPLAIN 执行计划
        → 看 type（是不是 ALL？）、key（是不是 NULL？）、rows、Extra
        → 确认是"没索引"还是"索引失效"
        ↓
④ 设计：结合最左前缀法则，避开 7 种失效场景
        → 选择索引类型：联合索引 / 前缀索引 / 覆盖索引
        → 遵循三原则：高区分度在左、等值在范围前、一个联合索引覆盖多场景
        ↓
⑤ 落地：CREATE INDEX 创建索引
        ↓
⑥ 验证：三指标量化确认
        → 执行时间（如 2.3s → 0.02s）
        → EXPLAIN（type: ALL→ref，rows: 120万→1200）
        → Handler_read%（read_rnd_next 大降，read_key 大升）
        ↓
⑦ 兜底：优化器选错索引时
        → ANALYZE TABLE 更新统计信息
        → 应急用 FORCE INDEX 等 SQL 提示
        ↓
        回到 ① 持续监控，形成闭环

核心心法一句话：

索引优化不是"加个索引"就结束了，而是"定位 → 分析 → 设计 → 验证 → 持续监控"的完整闭环。每一步都要拿数据说话，不要拿感觉说话。

网站备案号:津ICP备15001525号-2