MySQL 之索引性能分析

例如在用户表中查询user_id=100的记录，有索引时可以直接定位到该记录所在的数据页，而无索引则需要读取整张表的所有数据页。EXPLAIN 是 MySQL 中最基础且最常用的查询分析工具，它可以模拟 MySQL 优化器执行 SQL 查询的过程，帮助开发者理解查询是如何被执行的，以及索引是否被有效利用。Performance Schema 是 MySQL 5.5+ 引入的内置性能监控工具，可以实

祈祷苍天赐我java之术

895人浏览 · 2025-09-06 11:56:52

祈祷苍天赐我java之术 · 2025-09-06 11:56:52 发布

一、索引基础

1.1 索引的本质与价值

索引本质上是一种特殊的数据结构（如 B+树、哈希表、位图等）。其核心价值主要体现在以下两个方面：

减少磁盘 I/O 次数：在数据库系统中，数据通常存储在磁盘上，而磁盘读取是相对较慢的操作。通过建立索引，可以快速定位数据位置，避免全表扫描。例如在用户表中查询user_id=100的记录，有索引时可以直接定位到该记录所在的数据页，而无索引则需要读取整张表的所有数据页。
降低 CPU 消耗：索引可以看作是数据的"目录"，它已经按照特定方式组织好了数据。当执行查询时，数据库引擎可以直接利用索引的有序性进行快速筛选，无需对全表数据进行排序或过滤操作，从而大大减少CPU的计算负担。

以MySQL InnoDB引擎的默认索引结构B+树为例：

B+树是一种多路平衡查找树，其特点是所有数据都存储在叶子节点，非叶子节点只存储键值和指针
对于百万级数据量的表，B+树的高度通常维持在2-4层
查询时只需2-4次磁盘I/O即可定位到目标数据
相比之下，全表扫描可能需要数十甚至数百次I/O（视表大小而定）

1.2 索引性能的常见误区

写入性能下降：
- 每次数据变更（INSERT/UPDATE/DELETE）都需要同步维护相关索引
- 对于B+树索引，数据写入可能导致节点的分裂与合并操作
- 实测表明，每增加一个索引，写入性能可能下降10%-20%
- 案例：某电商平台在促销期间，由于索引过多导致订单写入性能下降，出现超时问题
空间占用过高：
- 索引本身需要占用存储空间
- 对于组合索引，存储空间可能达到原数据的50%-100%
- 案例：某系统发现200GB的表中，索引就占用了120GB空间
索引失效风险：
- 不当的查询语句（如使用函数操作索引列）会导致索引失效
- 不合理的索引设计（如区分度低的列建立索引）无法有效过滤数据
- 案例：某查询在索引列上使用LIKE '%关键字%'，导致全表扫描

因此，索引性能分析的核心目标是：

通过查询分析识别高频且性能关键的业务SQL
评估现有索引的使用效率（使用EXPLAIN分析执行计划）
在"查询性能"和"写入性能"之间找到最佳平衡点
定期审查并剔除冗余索引（如长期未使用的索引）
优化低效索引（如调整组合索引的字段顺序）

最佳实践表明，一个设计良好的数据库系统通常每个表保持3-5个核心索引即可满足大部分查询需求。

二、索引性能分析核心工具：从 Explain 到 Performance Schema

2.1 Explain：解读查询与索引的交互

EXPLAIN 是 MySQL 中最基础且最常用的查询分析工具，它可以模拟 MySQL 优化器执行 SQL 查询的过程，帮助开发者理解查询是如何被执行的，以及索引是否被有效利用。

2.1.1 Explain 输出字段详解

执行 EXPLAIN SELECT * FROM user WHERE age > 30 AND name = '张三'; 后，输出结果包含 12 个核心字段，以下是关键字段的详细解读：

字段	含义与性能分析要点
id	查询的优先级（越大越先执行），在多表查询或子查询中，id 值相同的为同一组查询，id 值越大优先级越高。例如：在包含 UNION 的查询中，第一个 SELECT 的 id 为 1，第二个 SELECT 的 id 为 2。
select_type	查询类型，常见值包括：<br>- SIMPLE：简单查询（不含子查询或 UNION）<br>- PRIMARY：最外层查询<br>- SUBQUERY：子查询中的第一个 SELECT<br>- DERIVED：派生表（FROM 子句中的子查询）<br>- UNION：UNION 中的第二个或后面的 SELECT<br>复杂查询类型可能影响索引使用效率。
table	当前查询涉及的表名。如果显示为 `<derivedN>` 表示使用了派生表，`<unionM,N>` 表示使用了 UNION 结果。
type	访问类型（最核心指标），从优到差依次为：<br>- system：系统表，只有一行记录<br>- const：通过主键或唯一索引查询单条记录<br>- eq_ref：多表关联时，关联条件为主键或唯一键<br>- ref：普通索引查询<br>- range：索引范围查询（如 age > 30）<br>- index：全索引扫描<br>- ALL：全表扫描<br>高频查询应至少达到 range 级别。
possible_keys	可能被使用的索引列表。如果为 NULL，表示没有可用索引，应考虑添加合适的索引。
key	实际使用的索引。如果为 NULL 但 possible_keys 有值，说明索引未被使用，需要分析原因。
key_len	索引使用的字节数。对于复合索引，可以判断使用了索引的哪些部分。例如：<br>- int 类型字段：4 字节<br>- varchar(20) utf8 字段：20*3+2=62 字节（utf8 每个字符最多 3 字节，+2 是长度前缀）<br>- 可为 NULL 的字段会额外占用 1 字节
rows	MySQL 预估需要检查的行数。这个值越小越好，如果远大于实际数据量，可能是统计信息过时，可以执行 ANALYZE TABLE 更新统计信息。
Extra	额外信息，常见值及其含义：<br>- Using index：使用了覆盖索引（直接从索引中获取数据，无需回表）<br>- Using where：在存储引擎检索记录后，服务器层进行了额外过滤<br>- Using temporary：使用了临时表（常见于 GROUP BY、ORDER BY 等操作）<br>- Using filesort：使用了文件排序（性能较差，应尽量通过索引优化）

2.1.2 基于 Explain 的常见问题诊断

问题 1：key 为 NULL 但 possible_keys 有值

可能原因：

查询条件中使用了函数或表达式，如 SUBSTR(name,1,2) = '张' 或 age + 1 > 30
存在隐式类型转换，如 age = '30'（age 是 int 类型）
使用了否定条件，如 NOT IN、!= 或 NOT LIKE
使用了 OR 条件但未对所有条件建立索引
索引选择性太低（重复值太多），优化器认为全表扫描更快

解决方案：

避免在索引列上使用函数或表达式
确保比较操作的两边数据类型一致
用 IN 替代 NOT IN，用 > AND < 替代 !=
为 OR 条件的每个字段建立适当索引
对低选择性索引考虑删除或重建

问题 2：Extra 出现 Using temporary 或 Using filesort

可能原因：

查询包含 ORDER BY 但排序字段未包含在索引中
查询包含 GROUP BY 但分组字段未包含在索引中
使用了 DISTINCT 操作
多表连接时连接条件不当

解决方案：

创建包含排序字段的复合索引，如 ALTER TABLE user ADD INDEX idx_name_age (name, age)
确保 GROUP BY 和 ORDER BY 使用相同的索引
减少不必要的 DISTINCT 操作
优化连接条件，确保连接字段有适当索引

2.2 Performance Schema：追踪索引的实时性能

Performance Schema 是 MySQL 5.5+ 引入的内置性能监控工具，可以实时追踪索引的读写性能、等待事件等，比 EXPLAIN 提供更全面的性能数据。

2.2.1 核心表与查询场景

追踪索引的使用频率

通过 performance_schema.table_io_waits_summary_by_index_usage 表可以查看每个索引的读写次数，从而识别冗余索引：

SELECT
    object_schema AS db_name,
    object_name AS table_name,
    index_name,
    count_star AS total_ops,  -- 总操作次数
    count_read AS read_ops,   -- 读操作次数
    count_write AS write_ops  -- 写操作次数
FROM
    performance_schema.table_io_waits_summary_by_index_usage
WHERE
    object_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
    AND index_name IS NOT NULL
    AND index_name != 'PRIMARY'
ORDER BY
    count_star ASC  -- 使用次数最少的排在前面
LIMIT 20;

分析建议：

对于长期无读操作 (count_read=0) 的索引，考虑删除
对于写操作远多于读操作的索引，评估其必要性
注意监控周期应覆盖业务高峰期和低频期

分析索引等待事件

通过 performance_schema.events_waits_current 表可以查看索引操作的等待事件：

SELECT
    event_name,
    object_schema AS db_name,
    object_name AS table_name,
    index_name,
    timer_wait/1000000 AS wait_ms,  -- 等待时间(毫秒)
    operation,
    number_of_bytes
FROM
    performance_schema.events_waits_current
WHERE
    object_type = 'TABLE'
    AND index_name IS NOT NULL
    AND timer_wait > 0
ORDER BY
    timer_wait DESC
LIMIT 10;

常见等待事件分析：

wait/io/table/sql/handler：表 I/O 等待
wait/lock/table/sql/handler：表锁等待
wait/io/file/sql/FRM：表结构文件读取等待

2.3 sys 库：简化性能分析的上层工具

sys 库是 MySQL 5.7+ 提供的官方工具库，基于 Performance Schema 和 information_schema 封装了大量易用的视图，简化了性能分析工作。

2.3.1 常用视图与实战查询

识别未使用的索引

SELECT
    object_schema AS db_name,
    object_name AS table_name,
    index_name,
    index_columns  -- 索引包含的列
FROM
    sys.schema_unused_indexes
WHERE
    object_schema = 'your_database'
ORDER BY
    object_name, index_name;

注意事项：

确保监控已开启足够长时间（至少覆盖一个完整业务周期）
某些索引可能只在特定时期使用（如月末报表）
删除索引前应评估其对写入性能的影响

分析索引选择性

SELECT
    table_schema AS db_name,
    table_name,
    index_name,
    stat_value AS cardinality,  -- 不同值的数量
    table_rows,                -- 表总行数
    ROUND(stat_value/table_rows, 3) AS selectivity,  -- 选择性
    CONCAT(ROUND(stat_value/table_rows*100, 2), '%') AS selectivity_pct
FROM
    mysql.innodb_index_stats
JOIN
    information_schema.tables
    ON innodb_index_stats.table_name = tables.table_name
    AND innodb_index_stats.database_name = tables.table_schema
WHERE
    innodb_index_stats.database_name = 'your_database'
    AND stat_name = 'n_diff_pfx01'  -- 索引第一列的不同值数量
    AND tables.table_rows > 1000
ORDER BY
    selectivity
LIMIT 20;

选择性评估标准：

30%：选择性良好
10%-30%：选择性一般
<10%：选择性较差，考虑优化或删除

三、影响索引性能的关键因素

3.1 索引选择性：越高越好

索引选择性是衡量索引筛选数据能力的重要指标，计算公式为：

选择性 = 去重后的索引值数量 / 表记录总数

选择性越高，意味着索引能更精准地定位数据行。例如：

性别字段（gender）通常只有"男"、"女"两个值，假设表有100万记录，选择性为2/1000000≈0.000002，几乎无法筛选数据；
手机号字段（mobile）通常具有唯一性，选择性接近1，能直接定位到单条记录；
状态字段（如order_status）可能有5个枚举值，选择性为5/1000000=0.000005，同样很低。

优化建议详细说明：

对于低选择性字段（如状态、类型、布尔值），应避免单独建立索引，否则：
- 索引树扫描范围仍然很大
- 增删改时需要维护索引却收益很小
若必须使用低选择性字段，建议：
- 作为联合索引的非前缀字段（如INDEX(create_time, status)）
- 结合高选择性字段建立复合索引（如INDEX(region, gender)）

对于高频查询的枚举字段，典型的优化方案是：

-- 原始低效查询
SELECT * FROM orders WHERE status = 'paid';

-- 优化方案
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);

3.2 联合索引的顺序：前缀原则详解

联合索引的存储结构类似于电话簿的"姓氏+名字"排序方式，必须按定义的字段顺序匹配才能生效。

索引生效场景分析

对于联合索引INDEX(name, age, address)：

✅ 生效的查询模式：

-- 场景1：完整匹配最左前缀
WHERE name = '张三' AND age = 30 AND address = '北京'

-- 场景2：匹配前两列
WHERE name = '李四' AND age > 25

-- 场景3：仅匹配第一列（仍然走索引）
WHERE name LIKE '王%'

❌ 失效的查询模式：

-- 场景1：跳过最左列
WHERE age = 30

-- 场景2：非连续匹配
WHERE name = '张三' AND address = '上海'

-- 场景3：对索引列进行计算
WHERE CONCAT(name, '_suffix') = '张三_suffix'

顺序优化策略

选择性优先原则：
- 将区分度高的字段靠左（如user_id比status更适合放在首位）
- 计算字段选择性的SQL：
```
SELECT 
  COUNT(DISTINCT column1)/COUNT(*) as selectivity1,
  COUNT(DISTINCT column2)/COUNT(*) as selectivity2
FROM table_name;
```
业务查询模式优先：
- 分析高频查询条件（如80%查询都包含create_time条件）
- 使用EXPLAIN分析慢查询的执行计划
避免冗余索引：
- 已有INDEX(a,b)时，单独INDEX(a)是多余的
- 可通过SHOW INDEX FROM table_name检查现有索引

3.3 索引覆盖：避免回表查询的深度优化

回表机制图解

[二级索引]         [聚簇索引]
name-age索引  →    id-name-age-address
   ↓                   ↑
 找到行指针 ------------┘

实战案例扩展

表结构：user(id PK, name, age, address) 索引：INDEX idx_name_age(name, age)

🔵 覆盖索引场景：

-- 案例1：查询字段全在索引中
EXPLAIN SELECT name, age FROM user WHERE name = '张三';
-- Extra: Using index

-- 案例2：包含主键（InnoDB二级索引会自动包含主键）
EXPLAIN SELECT id, name FROM user WHERE name LIKE '张%';
-- Extra: Using index

🔴 非覆盖索引场景：

-- 案例3：需要address字段（触发回表）
EXPLAIN SELECT name, address FROM user WHERE age > 20;
-- Extra: Using where; Using filesort

-- 案例4：使用SELECT *
EXPLAIN SELECT * FROM user WHERE name = '李四';
-- Extra: NULL

高级优化技巧

索引扩展：

-- 原始索引
ALTER TABLE orders ADD INDEX idx_status(status);

-- 优化为覆盖索引
ALTER TABLE orders ADD INDEX idx_status_cover(status, order_no, amount);

函数索引（MySQL 8.0+）：

-- 对JSON字段建立覆盖索引
ALTER TABLE products ADD INDEX idx_name_data ((CAST(data->>'$.price' AS DECIMAL(10,2))));

物化视图（通过冗余实现覆盖）：

CREATE TABLE user_view AS
SELECT id, name, age FROM user WHERE status = 1;

3.4 数据量与索引维护：全生命周期管理

索引膨胀的监控

-- 查看索引碎片率（MySQL 5.7+）
SELECT 
  table_name,
  index_name,
  ROUND(stat_value * 0.5) / 1024 AS 'Size(MB)',
  ROUND(stat_value * 0.5) / 
    (SELECT ROUND(data_length/1024) FROM information_schema.tables 
     WHERE table_schema = DATABASE() AND table_name = 'your_table') * 100 AS 'Fragmentation(%)'
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND database_name = DATABASE();

索引维护策略

在线重建（生产环境推荐）：

-- InnoDB在线重建（不会锁表）
ALTER TABLE user ENGINE=InnoDB;

-- 针对特定索引
ANALYZE TABLE user;

离线维护（大数据量时）：

# 使用pt-online-schema-change工具
pt-online-schema-change --alter "DROP KEY idx_name, ADD KEY idx_new_name(name)" D=database,t=user

维护窗口建议：
- 每月对核心表执行一次ANALYZE TABLE
- 每季度对增长快的表重建索引
- 发现查询性能下降15%以上时立即检查碎片率

索引生命周期管理

创建阶段：

-- 使用INVISIBLE特性测试新索引
CREATE INDEX idx_test ON orders(create_date) INVISIBLE;

-- 确认有效后启用
ALTER INDEX idx_test ON orders VISIBLE;

淘汰阶段：

-- 使用索引使用率统计（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;

-- 安全删除索引
ALTER TABLE orders DROP INDEX idx_unused;

监控体系：

-- 开启性能监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%index%';

四、实战案例：从性能问题到索引优化

4.1 场景描述

某大型电商平台的订单表(order)存储了海量交易数据，当前数据量已达100万条记录。该表记录了平台所有的订单信息，具有以下数据结构：

CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID，自增主键',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID，关联用户表',
  `order_no` varchar(64) NOT NULL COMMENT '订单编号，业务唯一标识',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额，精确到分',
  `create_time` datetime NOT NULL COMMENT '订单创建时间',
  `status` tinyint(4) NOT NULL COMMENT '订单状态（0：待支付，1：已支付，2：已取消）',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台订单表';

在高频业务场景中，平台需要频繁执行以下查询：

SELECT id, order_no, amount, status
FROM `order`
WHERE user_id = 12345
AND create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
ORDER BY create_time DESC;

该查询用于获取特定用户在某个月份内的所有订单信息，并按创建时间降序排列，是用户中心、订单管理等模块的核心查询。

4.2 性能问题分析

执行计划分析

使用EXPLAIN命令分析查询性能：

EXPLAIN SELECT id, order_no, amount, status 
FROM `order` 
WHERE user_id = 12345 
AND create_time BETWEEN '2024-01-01' AND '2024-01-31' 
ORDER BY create_time DESC;

关键分析结果：

type: range（由于create_time使用了范围查询）
possible_keys: idx_user_id, idx_create_time（可能使用的索引）
key: idx_user_id（实际选择了user_id索引）
rows: 预估扫描行数（假设为500）
Extra: Using where; Using filesort（存在严重性能瓶颈）

性能瓶颈详解

索引选择问题：
- 优化器选择了idx_user_id索引，因为user_id是等值查询条件
- 但需要回表查询create_time字段来满足BETWEEN条件
排序性能问题：
- 由于结果需要按create_time降序排列
- 当前索引不包含排序字段，导致MySQL必须使用filesort操作
- filesort需要在内存或磁盘上创建临时表进行排序，效率极低
回表查询问题：
- 查询需要返回order_no、amount、status字段
- 这些字段都不在现有索引中，需要回表查询
- 对百万级数据表，回表操作会产生大量随机I/O

4.3 优化方案

优化方案设计

根据查询特点，设计覆盖索引方案：

CREATE INDEX idx_user_create ON `order`(
  user_id,          -- 最左列，满足等值查询
  create_time DESC, -- 范围查询+排序字段
  order_no,         -- 覆盖查询字段
  amount,           -- 覆盖查询字段
  status            -- 覆盖查询字段
);

优化原理：

最左前缀原则：user_id作为索引最左列，确保WHERE条件能使用索引
排序优化：create_time作为第二列，既满足范围查询，又避免filesort
覆盖索引：包含所有查询字段(id是主键自动包含)，避免回表

优化效果验证

执行EXPLAIN验证：

EXPLAIN SELECT id, order_no, amount, status 
FROM `order` 
WHERE user_id = 12345 
AND create_time BETWEEN '2024-01-01' AND '2024-01-31' 
ORDER BY create_time DESC;

优化后结果：