欢迎光临
我们一直在努力
        找回密码

MySQL 8.0 索引优化深度解析：B+树原理、覆盖索引、索引下推与高性能索引策略实战

2026-07-05 分类：MySQL 阅读(3) 评论(0)

引言：索引——数据库性能的基石

在MySQL数据库的日常运维和开发中，索引优化是最常见也最高回报的性能调优手段。一个设计良好的索引可以让查询从全表扫描的数秒级别降到毫秒级别，而一个缺失或不合理的索引则可能拖垮整个数据库服务。MySQL 8.0引入了多项索引相关的增强特性——包括不可见索引、降序索引、函数索引（表达式索引）等——让索引设计有了更大的灵活性和优化空间。

本文将从底层B+树存储结构出发，系统性地讲解MySQL 8.0的索引原理、核心优化策略以及实战调优方法。无论你是刚接触数据库优化的初级开发者，还是已有多年经验的资深DBA，都能从中找到对实际工作有帮助的内容。

文章配图来自Unsplash，通过脚本自动上传至本站CDN，确保加载速度和稳定性。

数据库索引优化概念图

一、B+树索引结构深度解析

InnoDB存储引擎使用B+树作为索引的底层数据结构。理解B+树的工作机制，是进行索引优化的前提。

1.1 B+树与B树的区别

很多开发者容易混淆B树和B+树，两者最关键的区别在于：

  • 数据存储位置：B树的所有节点（包括内部节点和叶子节点）都可能存储数据；而B+树只有叶子节点存储数据，内部节点只存储索引键值。
  • 叶子节点链表：B+树的叶子节点通过双向链表链接，形成了天然的有序结构，使得范围查询（
    1
    WHERE key BETWEEN a AND b

    ）可以高效地顺序遍历。

  • 树的高度：由于内部节点不存数据、只存键值，同样的页面大小下B+树的分支因子更大，树的高度通常比B树更低。InnoDB的B+树高度通常在2~4层，这意味着3~4次I/O即可定位到任意一行数据。

1.2 InnoDB的主键索引与二级索引

InnoDB索引采用聚簇索引（Clustered Index）设计。表的数据行实际上就是存储在B+树的叶子节点上的：

  • 聚簇索引（主键索引）：叶子节点保存整行数据。每个InnoDB表有且只有一个聚簇索引。如果没有显式定义主键，InnoDB会选择第一个非空的唯一索引作为聚簇索引；如果也没有，则隐式创建一个6字节的
    1
    ROW_ID

    作为聚簇索引。

  • 二级索引（辅助索引）：叶子节点保存主键值（而非整行数据）。当通过二级索引查询时，先找到主键值，再通过主键索引回表查询整行数据——这个过程称为回表查询（Bookmark Lookup）。


1
2
3
4
5
6
7
8
9
10
11
12
13
-- 示例表结构

CREATE TABLE `users` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `username` varchar(64) NOT NULL,

  `email` varchar(128) NOT NULL,

  `age` int NOT NULL,

  `city` varchar(32) DEFAULT NULL,

  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  KEY `idx_username` (`username`),

  KEY `idx_email` (`email`),

  KEY `idx_age_city` (`age`, `city`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

在上述表结构中：

  • 1
    PRIMARY KEY (id)

    是聚簇索引，B+树叶子节点直接存储

    1
    id, username, email, age, city, created_at

    所有列的数据。

  • 1
    KEY idx_username (username)

    是二级索引，叶子节点存储

    1
    username, id

    （索引列 + 主键）。

  • 查询
    1
    SELECT * FROM users WHERE username = 'alice'

    会先查

    1
    idx_username

    找到

    1
    id

    ，再回表查主键索引获取完整行数据。

1.3 索引页结构与Page Directory

InnoDB的数据页（Page）默认大小为16KB。每个B+树节点对应一个数据页。页内部的组织结构包括：

  • Page Header：页面元数据，包括页编号、上下页指针、槽数量等。
  • Infimum + Supremum：伪记录，标记页内记录的最小值和最大值边界。
  • User Records：实际用户记录，按主键顺序单向链表连接（实际是物理上的顺序排列，逻辑上通过next_record指针串联）。
  • Page Directory：槽目录，将记录分组并为每组最后一条记录维护一个槽指针。通过二分查找可以快速定位到目标记录所在的组，然后在该组内顺序遍历。这是InnoDB页内查找的核心机制。

B+树索引结构示意图

二、核心索引优化策略

理解了底层数据结构后，我们来看在实际工作中最常用的索引优化策略。

2.1 覆盖索引（Covering Index）

当查询所需的所有列都包含在二级索引中时，MySQL可以直接从索引中获取所需数据，无需回表。例如：



1
2
3
4
5
6
7
-- 对于 idx_username(username)，执行：

SELECT username, id FROM users WHERE username = 'alice';

-- username 和 id 都在索引中，无需回表



-- 但如果查询：

SELECT username, email FROM users WHERE username = 'alice';

-- email 不在 idx_username 索引中，需要回表

利用覆盖索引优化常见查询，可以显著减少I/O次数。设计索引时应当考虑：尽可能让高频查询使用覆盖索引。



1
2
3
4
5
-- 如果经常按age范围查询且只查city，可以创建复合索引：

ALTER TABLE users ADD INDEX idx_age_city_cover (age, city);



-- 这样查询 SELECT age, city FROM users WHERE age BETWEEN 20 AND 30

-- 完全在索引中完成，无需回表

2.2 索引下推（Index Condition Pushdown, ICP）

索引下推是MySQL 5.6引入、MySQL 8.0持续增强的重要优化。在没有ICP的情况下，存储引擎通过索引定位到记录后，会将其返回给Server层，由Server层对WHERE条件中的其他列进行过滤。而开启ICP后，存储引擎层可以在索引遍历的过程中直接过滤不符合条件的记录，减少回表次数。

通过EXPLAIN可以看到，如果Extra列显示

1
Using index condition

，则表示使用了ICP：



1
2
3
4
5
6
-- 复合索引 idx_age_city (age, city)

EXPLAIN SELECT * FROM users 

WHERE age = 25 AND city LIKE '%海%';

-- Extra: Using index condition

-- age列用于索引查找，city LIKE条件通过ICP在索引层过滤

-- 只有满足city条件的记录才需要回表

ICP特别适合以下场景：

  • 复合索引的前导列用于精准匹配（如
    1
    age = 25

    ）

  • 后续列使用LIKE、范围查询等不能作为索引查找条件的情况
  • 可以有效减少回表次数，特别是当过滤条件选择性较好时
场景 无ICP 有ICP
扫描索引行数 需扫描所有age=25的记录 同样扫描所有age=25的记录
回表次数 所有age=25记录都回表 仅city LIKE匹配的记录回表
性能差异 随机I/O多，性能差 随机I/O少，性能显著提升

2.3 复合索引列顺序原则

复合索引的列顺序是索引设计中最重要的决策之一。核心原则是：将选择性最高的列放在最左侧。但这里需要更细致地分析：

  • 等值条件优先：如果某列出现在
    1
    WHERE col = XXX

    中，相比范围条件（

    1
    >、<、BETWEEN

    ），等值条件列更适合放在前面。

  • 最左前缀原则：MySQL只能利用复合索引的最左前缀进行索引查找。索引
    1
    (a, b, c)

    可以用于

    1
    WHERE a = 1

    、

    1
    WHERE a = 1 AND b = 2

    、

    1
    WHERE a = 1 AND b = 2 AND c = 3

    ，但不能用于

    1
    WHERE b = 2

    。

  • 排序受益：复合索引的列顺序与
    1
    ORDER BY

    子句一致时，可以避免额外的文件排序。



1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建复合索引

CREATE INDEX idx_city_age_created ON users (city, age, created_at);



-- 以下查询可以充分利用索引：

SELECT * FROM users 

WHERE city = '上海' AND age = 25 

ORDER BY created_at DESC;

-- 索引的三列都用到：city等值查找 → age等值过滤 → created_at用于排序



-- 以下查询只能用到city列：

SELECT * FROM users 

WHERE city = '上海' ORDER BY age;

-- Extra: Using where; Using index (age不需要排序，因为索引顺序为city, age, created_at)

-- 实际上由于city等值，age在索引中已有序，不需要filesort

2.4 三星索引评价体系

在《Relational Database Index Design and the Optimizers》一书中，作者Kuhn提出了”三星索引”评价标准：

  • ★ 第一星：索引将相关记录放到一起（满足WHERE条件的所有行在索引中连续），减少扫描范围。
  • ★★ 第二星：索引中的数据顺序与查询的ORDER BY一致，避免文件排序。
  • ★★★ 第三星：索引包含查询所需的所有列（覆盖索引），避免回表。

例如，对于查询

1
SELECT city, age FROM users WHERE city = '北京' ORDER BY age LIMIT 10

：

  • 索引
    1
    (city, age)

    满足三星：city等值查找（第一星）、age有序（第二星）、city和age都在索引中（第三星）

  • 这是一个完美的三星索引，查询只需在索引中顺序扫描几行即可返回结果

数据库索引优化策略

三、MySQL 8.0 索引新特性实战

MySQL 8.0引入了几项非常实用的索引新特性，在优化中灵活运用可以事半功倍。

3.1 不可见索引（Invisible Indexes）

在MySQL 8.0之前，要删除一个不确定是否还有用的索引，只能DROP掉——如果发现影响性能，再重新创建。这个过程对于大表来说非常耗时。不可见索引解决了这个问题：



1
2
3
4
5
6
7
8
9
10
11
-- 使索引不可见（优化器不会使用它，但索引数据仍维护）

ALTER TABLE users ALTER INDEX idx_email INVISIBLE;



-- 验证查询是否使用了该索引

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';



-- 如果确认不需要，可以删除

DROP INDEX idx_email ON users;



-- 如果发现还是需要用，重新可见

ALTER TABLE users ALTER INDEX idx_email VISIBLE;

不可见索引的核心价值：

  • 安全地测试索引删除的影响，无需承担重建索引的高昂代价
  • 在灰度发布中逐步验证索引变更
  • 注意：不可见索引在
    1
    SHOW INDEX

    中仍然可见，只是优化器不会选择它

3.2 降序索引（Descending Indexes）

MySQL 8.0之前，虽然可以指定

1
INDEX (col DESC)

，但MySQL实际上会忽略降序关键字，按升序存储。对于

1
ORDER BY col DESC

的查询，MySQL需要额外进行反向扫描或文件排序。MySQL 8.0真正支持了降序索引：



1
2
3
4
5
6
7
8
9
10
11
-- 创建真正的降序索引

CREATE INDEX idx_created_desc ON users (created_at DESC);



-- 对于以下查询可以直接使用索引，无需filesort

EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- Extra: NULL (没有Using filesort)



-- 混合排序方向

CREATE INDEX idx_city_age_mixed ON users (city ASC, age DESC);

-- 以下查询可以完全使用索引排序：

SELECT * FROM users WHERE city = '上海' ORDER BY age DESC;

降序索引的实用场景：

  • 经常需要按时间倒序查询最新记录（如
    1
    ORDER BY created_at DESC LIMIT 10

    ）

  • 复合索引中需要混合升序/降序排列以满足
    1
    ORDER BY col1 ASC, col2 DESC
  • 对排序查询性能提升非常显著——不仅仅是索引扫描，而是避免了显式排序

3.3 函数索引（Expression Index / Functional Index）

在MySQL 8.0.13及以后版本中，可以创建基于表达式的索引。这在以前只能通过虚拟生成列来实现，现在更简单：



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 函数索引（MySQL 8.0.13+）

CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, '@', -1)));



-- 以下查询可以直接使用函数索引

EXPLAIN SELECT * FROM users 

WHERE SUBSTRING_INDEX(email, '@', -1) = 'gmail.com';

-- Extra: NULL（使用了索引）



-- JSON列索引（MySQL 8.0.17+）

CREATE TABLE `products` (

  `id` int NOT NULL AUTO_INCREMENT,

  `attrs` json DEFAULT NULL,

  PRIMARY KEY (`id`),

  INDEX idx_attrs_price ((CAST(attrs->>'$.price' AS DECIMAL(10,2))))

) ENGINE=InnoDB;



-- 查询JSON字段中的属性时可以使用索引

EXPLAIN SELECT * FROM products 

WHERE CAST(attrs->>'$.price' AS DECIMAL(10,2)) > 100.00;

四、索引监控与调优工具

4.1 索引统计信息与直方图

MySQL优化器基于索引统计信息来选择执行计划。MySQL 8.0在

1
information_schema

中提供了更详细的统计信息：



1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看索引统计信息

SELECT 

  TABLE_NAME, INDEX_NAME, CARDINALITY,

  COLUMN_NAME, SUB_PART, SEQ_IN_INDEX,

  INDEX_TYPE, IS_VISIBLE, EXPRESSION

FROM information_schema.STATISTICS

WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'users';



-- MySQL 8.0直方图（用于非索引列的选择性评估）

ANALYZE TABLE users UPDATE HISTOGRAM ON city, age WITH 100 BUCKETS;

-- 查看直方图信息

SELECT * FROM information_schema.COLUMN_STATISTICS 

WHERE SCHEMA_NAME = 'your_database' AND TABLE_NAME = 'users';

直方图对于没有索引但经常被当作查询条件的列特别有用——优化器可以据此更准确地估计行数，选择更优的执行计划。

4.2 利用Performance Schema监控索引使用



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看索引使用统计

SELECT 

  OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,

  COUNT_STAR, COUNT_READ, SUM_TIMER_WAIT

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE OBJECT_SCHEMA = 'your_database'

ORDER BY SUM_TIMER_WAIT DESC;



-- 找出从未被使用的索引（可以考虑删除）

SELECT 

  OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE OBJECT_SCHEMA = 'your_database'

  AND INDEX_NAME IS NOT NULL

  AND COUNT_STAR = 0

ORDER BY OBJECT_NAME, INDEX_NAME;

4.3 sys schema 索引分析视图



1
2
3
4
5
6
7
8
9
10
11
12
-- MySQL 5.7+ 自带的sys schema提供了便捷的索引分析视图



-- 未使用或冗余索引

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_database';



-- 查看索引的使用率

SELECT * FROM sys.schema_index_statistics 

WHERE table_schema = 'your_database' ORDER BY rows_selected DESC;



-- 查看全表扫描的表

SELECT * FROM sys.statements_with_full_table_scans 

WHERE db = 'your_database' LIMIT 20;

MySQL性能监控仪表盘

五、常见索引问题与调优案例

5.1 案例一：索引失效的典型场景



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 场景：对索引列使用了函数或隐式类型转换

-- ❌ 索引失效

EXPLAIN SELECT * FROM users WHERE DATE(created_at) = '2025-01-01';

-- 上面的查询无法使用(created_at)索引



-- ✅ 正确写法：使用范围查询

EXPLAIN SELECT * FROM users 

WHERE created_at >= '2025-01-01 00:00:00' 

  AND created_at < '2025-01-02 00:00:00';

-- 可以使用索引，Extra为Using index condition



-- ❌ 隐式类型转换

EXPLAIN SELECT * FROM users WHERE phone = 13800138000;

-- phone是varchar类型，传入数字导致类型转换



-- ✅ 正确写法

EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

常见的索引失效场景总结：

场景 原因 解决方案
索引列使用函数 函数计算后无法使用索引 改用范围条件或函数索引（8.0）
隐式类型转换 类型不匹配导致全索引扫描 参数类型与列类型保持一致
LIKE以%开头 无法确定前缀的B+树位置 全文索引或搜索引擎
OR条件部分无索引 优化器选择全表扫描 全字段建索引或用UNION ALL
复合索引违反最左前缀 跳过复合索引前导列 调整索引列顺序或重建索引

5.2 案例二：大表索引创建与在线DDL



1
2
3
4
5
6
7
8
9
10
11
12
-- MySQL 8.0支持在线DDL（InnoDB表）

-- 创建索引期间允许DML操作（但不允许DDL并发）



-- 对大表创建索引（实测8亿行表）

ALTER TABLE orders ADD INDEX idx_user_id_created (user_id, created_at), 

  ALGORITHM=INPLACE, LOCK=NONE;

-- ALGORITHM=INPLACE: 不重建表（仅对二级索引有效）

-- LOCK=NONE: 不阻塞并发DML



-- 检查DDL进度（performance_schema）

SELECT * FROM performance_schema.metadata_locks 

WHERE OBJECT_SCHEMA = 'your_database' AND OBJECT_NAME = 'orders';

5.3 案例三：多表JOIN索引优化



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 多表关联查询的索引策略

EXPLAIN SELECT 

  u.username, o.order_amount, o.created_at

FROM users u

JOIN orders o ON u.id = o.user_id

WHERE u.city = '深圳'

  AND o.created_at >= '2025-01-01'

ORDER BY o.created_at DESC

LIMIT 100;



-- 推荐的索引方案：

-- 1. users表：(city) —— 用于过滤城市

-- 2. orders表：(user_id, created_at) —— 用于JOIN连接和排序

-- 这样查询计划会是：先通过users.city索引找到深圳用户，再通过orders.user_id索引嵌套循环JOIN



-- 更优的EXPLAIN结果通常是：

-- id | select_type | table  | type | key              | Extra

-- 1  | SIMPLE      | users  | ref  | idx_city         | Using where

-- 1  | SIMPLE      | orders | ref  | idx_user_created | Using index condition

六、索引设计的最佳实践

6.1 索引设计检查清单

  • 区分度优先：选择性（distinct值/总行数）> 20% 的列才适合作为索引前导列。例如性别（选择性~50%）就不适合单独建索引，但可以作为复合索引的后续列。
  • 避免过多索引：每个索引都会增加写入开销（INSERT/UPDATE/DELETE需要维护所有索引）。单表索引建议控制在5个以内，OLTP系统更应精简。
  • 短索引优先：InnoDB单个索引最多767字节（压缩行格式）或3072字节（COMPRESSED或DYNAMIC行格式）。尽量使用前缀索引
    1
    INDEX (col(N))

    减少索引大小。

  • 利用MySQL 8.0新特性：使用不可见索引安全验证、降序索引改善排序性能、函数索引处理JSON和表达式查询。
  • 定期维护：使用
    1
    OPTIMIZE TABLE

    或在低峰期重建索引（碎片率超过30%时效果显著）。

6.2 索引监控与维护脚本



1
2
3
4
5
6
7
8
9
10
11
12
13
-- 检查索引碎片率

SELECT 

  TABLE_NAME, ENGINE,

  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,

  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,

  ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS frag_pct

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'your_database'

  AND ENGINE = 'InnoDB'

ORDER BY frag_pct DESC;



-- 使用pt-duplicate-key-checker检查重复索引

-- pt-duplicate-key-checker h=localhost,D=your_database

总结

MySQL索引优化是一项系统工程，需要从底层B+树数据结构开始理解，掌握覆盖索引、索引下推、复合索引列顺序等核心策略，同时充分利用MySQL 8.0的新特性——不可见索引、降序索引、函数索引——来精细化地优化查询性能。

在实际工作中，建议遵循以下流程：

  1. 使用
    1
    slow_query_log

    收集慢查询

  2. 使用
    1
    EXPLAIN

    分析执行计划

  3. 使用
    1
    Performance Schema

    和

    1
    sys schema

    验证索引使用效率

  4. 利用MySQL 8.0的不可见索引安全验证变更
  5. 在压测环境中验证性能提升

索引不是越多越好——每一页索引数据都需要维护。对于写入密集型的OLTP系统，索引数量的控制更为重要。精心的索引设计加上持续的性能监控，是保持数据库稳定的不二法门。

【本站文章皆为原创，未经允许不得转载】：汤不热吧 » MySQL 8.0 索引优化深度解析：B+树原理、覆盖索引、索引下推与高性能索引策略实战
分享到： 更多 (0)
标签：

相关推荐