MySQL索引优化实战：从原理到高效调优的完整指南

首页

业界动态

热心网友

转载

2026-05-21

之前遇到一个典型的性能问题：一个订单查询接口，平均响应时间达到了3秒，P99响应时间甚至超过10秒。用户投诉不断，老板也天天催着解决。排查后发现，一张500万数据的订单表，查询条件是WHERE user_id = ? AND status = ? AND create_time > ?，但表上只有一个孤零零的单列索引idx_user_id。

解决方案其实很简单：创建一个联合索引idx_user_status_time(user_id, status, create_time)。就这么一个改动，查询时间直接从3秒降到了50毫秒。这个案例很能说明问题：索引不是越多越好，关键在于用对地方。今天，我们就从B+树的底层原理聊起，结合实战优化，把MySQL索引这件事彻底搞清楚。

B+树索引：理解MySQL索引的底层逻辑

为什么是B+树而不是B树？

先来看一张对比图：

图片

这张图清晰地展示了B+树和B树的核心差异。MySQL选择B+树作为索引结构，背后有几个关键考量：

首先，范围查询更高效。B+树的叶子节点通过指针串联成链表，做范围查询时，只需要沿着链表遍历即可。而B树需要复杂的中序遍历。

其次，磁盘IO更少。B+树的非叶子节点不存储实际数据，只存放索引键值和指针。这意味着一个数据页（通常是16KB）能容纳更多的索引项，从而让整棵树变得更“矮”。树越矮，查询时需要加载的磁盘页就越少，IO效率自然就上去了。

最后，查询性能更稳定。无论查什么，B+树的查询路径最终都要走到叶子节点，时间复杂度稳定在O(log n)。而B树的查询可能在非叶子节点就结束，性能不那么稳定。

B+树的结构解析

再来看看B+树的具体结构：

图片

典型的B+树分为三层：根节点、中间节点和叶子节点。

根节点和中间节点负责导航，存储索引键值和指向下一层节点的指针。叶子节点才是数据的“终点站”。对于聚簇索引，叶子节点存储的是完整的行数据；对于二级索引，叶子节点存储的则是主键值。

我们可以简单算一笔账：InnoDB的页大小默认是16KB。假设主键是BIGINT类型（8字节），加上一个指针（约6字节），那么一页大约可以存储 16KB / (8+6) ≈ 1170 个索引项。一个三层的B+树，理论上可以存储约 1170 × 1170 × 16 ≈ 2000万条数据。这就是为什么我们说，三层B+树足以支撑千万级数据量的高效查询。

聚簇索引 vs 二级索引：两种索引的本质区别

聚簇索引（主键索引）

先看结构：

图片

聚簇索引有几个核心特点：它的叶子节点直接存储完整的行数据，数据行在物理上就是按照主键顺序排列的。正因为如此，一张表有且只能有一个聚簇索引，通常就是主键。

它的优点是显而易见的：根据主键查询速度极快，因为一次索引查找就能拿到所有数据。但缺点也同样明显：插入新记录时，为了维持有序性，可能会发生频繁的页分裂，影响写入性能。

二级索引（非聚簇索引）

二级索引的结构有所不同：

图片

二级索引的叶子节点存储的不是完整数据，而是对应记录的主键值。这就引出了一个关键操作——“回表”。

举个例子：

-- 表结构
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20),
    INDEX idx_phone(phone)
);

-- 查询（需要回表）
SELECT * FROM user WHERE phone = '13800138000';

这个查询的执行过程是：先在idx_phone索引中找到phone='13800138000'对应的主键id，然后再拿着这个id回到聚簇索引中去查找完整的行数据。多出来的这一步，就是“回表”。

覆盖索引：避免回表的利器

有没有办法避免回表呢？有，这就是覆盖索引。

图片

覆盖索引的原理很简单：如果查询所需要的所有字段，都包含在某个索引中，那么MySQL就不需要回表去聚簇索引里取数据了，直接从当前索引就能拿到结果，性能自然大幅提升。

-- 覆盖索引示例
-- 表结构
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20),
    INDEX idx_phone_name(phone, name) -- 创建联合索引
);

-- 覆盖索引查询（不需要回表）
SELECT id, phone, name FROM user WHERE phone = '13800138000';

-- Extra显示"Using index"，表示使用了覆盖索引
EXPLAIN SELECT id, phone, name FROM user WHERE phone = '13800138000';

索引设计原则：5条黄金法则

法则1：最左前缀原则

这是联合索引设计的基石。

图片

假设我们创建了一个联合索引idx_a_b_c (a, b, c)。索引会先按a排序，a相同再按b排序，b相同再按c排序。因此，查询条件必须从最左边的列开始匹配，索引才能生效。

-- 联合索引
CREATE INDEX idx_a_b_c ON t(a, b, c);

-- ✅ 能用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ❌ 不能用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

这里有个常见的坑：曾经有个查询条件是WHERE create_time > '2025-01-01'，表上有个(status, create_time)的索引，结果导致全表扫描。后来把索引改成(create_time, status)才解决了问题。顺序很重要。

法则2：选择区分度高的列

索引的价值在于快速定位数据。如果一个字段的值大部分都相同（比如“性别”字段），建索引的意义就不大。可以通过计算区分度来判断：

-- 计算区分度
SELECT
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;
-- 区分度 > 0.8，适合建索引
-- 区分度 < 0.1，不适合建索引

举个例子：

-- ❌ 性别字段（区分度约0.5）
CREATE INDEX idx_gender ON user(gender);  -- 不推荐

-- ✅ 手机号字段（区分度接近1）
CREATE INDEX idx_phone ON user(phone);    -- 推荐

法则3：为排序和分组创建索引

索引不仅能加速查询，还能优化ORDER BY和GROUP BY操作。设计时，记得把WHERE条件字段放在前面，排序或分组字段放在后面。

-- 排序查询
SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC;

-- 创建索引（WHERE条件在前，排序字段在后）
CREATE INDEX idx_user_time ON orders(user_id, create_time);

法则4：控制索引数量

索引不是免费的午餐。索引过多会带来几个问题：占用额外的磁盘空间；每次数据增删改时，都需要维护相关的索引，降低写入性能；查询优化器在选择索引时也可能陷入“选择困难症”。

通常建议：单表索引数量不超过5个，单个联合索引的字段数也不宜超过5个。定期使用系统表查看索引的使用情况，清理那些“僵尸索引”。

-- 查看索引使用情况
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_READ DESC;

法则5：避免索引失效的场景

即使创建了索引，一些不当的写法也会导致索引失效。常见的陷阱包括：

-- ❌ 索引失效（对索引列使用函数）
SELECT * FROM user WHERE YEAR(create_time) = 2025;
-- ✅ 索引有效（使用范围查询）
SELECT * FROM user WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';

-- ❌ 索引失效（隐式类型转换）
SELECT * FROM user WHERE phone = 13800138000;  -- phone是VARCHAR类型
-- ✅ 索引有效
SELECT * FROM user WHERE phone = '13800138000';

慢查询优化实战

慢查询分析工具

工欲善其事，必先利其器。优化慢查询，首先要找到它们。

-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒的记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 2. 使用EXPLAIN分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1;

-- 3. 使用EXPLAIN ANALYZE（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 1;

EXPLAIN关键字段解读

图片

EXPLAIN的结果里，有几个字段需要特别关注：

type：访问类型，从好到坏大致是：system > const > eq_ref > ref > range > index > ALL。看到ALL（全表扫描）就要警惕了。
key：实际使用的索引。
rows：预估需要扫描的行数。
Extra：额外信息，比如Using index表示使用了覆盖索引，Using filesort表示需要额外的排序。

-- 好的执行计划（使用了索引）
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref, key: idx_user_id, rows: 10

-- 差的执行计划（全表扫描）
EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) = 2025;
-- type: ALL, key: NULL, rows: 5000

真实优化案例

案例1：分页查询优化

深分页是性能杀手，尤其是LIMIT 1000000, 20这种写法，越往后翻越慢。

-- ❌ 问题SQL（越往后越慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 优化方案1：使用子查询，先查主键
SELECT * FROM orders a
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) b
ON a.id = b.id;

-- ✅ 优化方案2：使用WHERE条件，记录上次查询的最大ID
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

案例2：COUNT优化

大表的COUNT(*)或带条件的COUNT很容易成为瓶颈。

-- ❌ 问题SQL（全表扫描）
SELECT COUNT(*) FROM orders WHERE status = 1;

-- ✅ 优化方案1：使用覆盖索引
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 1; -- 如果status区分度高，会走索引

-- ✅ 优化方案2：维护计数表（适合频繁统计且数据实时性要求不极高的场景）
CREATE TABLE order_count (
    status INT PRIMARY KEY,
    count INT
);

案例3：JOIN优化

JOIN查询时，驱动表的选择至关重要。基本原则是：让小表驱动大表。

-- ❌ 问题SQL（大表驱动小表）
SELECT * FROM big_table b JOIN small_table s ON b.small_id = s.id;

-- ✅ 优化方案：小表驱动大表
SELECT * FROM small_table s JOIN big_table b ON s.id = b.small_id;

-- 或者使用STRAIGHT_JOIN强制指定驱动表（需谨慎）
SELECT /*+ STRAIGHT_JOIN */ * FROM small_table s JOIN big_table b ON s.id = b.small_id;

最佳实践总结

索引设计清单

创建索引前，先问自己几个问题：

[ ] 这个查询频率高吗？（低频查询不值得建索引）
[ ] 字段的区分度够高吗？（通常> 0.8才值得）
[ ] 查询条件、排序、分组字段都分析清楚了吗？
[ ] 对表的写入性能影响评估过了吗？

创建索引时，遵循这些原则：

[ ] 严格遵守最左前缀原则。
[ ] 把WHERE条件字段放前面，排序/分组字段放后面。
[ ] 高区分度的字段尽量靠前。
[ ] 考虑使用覆盖索引来避免回表。

创建索引后，别忘了后续工作：

[ ] 用EXPLAIN验证索引是否真的被用上了。
[ ] 持续监控慢查询日志。
[ ] 定期清理那些长期不用的索引。

面试加分项

Q：为什么MySQL使用B+树而不是B树？

MySQL选择B+树主要有三个原因：第一，范围查询更高效，B+树的叶子节点用指针串联成链表，范围查询只需遍历链表，而B树需要中序遍历；第二，磁盘IO更少，B+树的非叶子节点不存数据，一页能存更多索引项，树更矮，一般三层就能存2000万数据；第三，查询性能更稳定，B+树所有查询都要到叶子节点，时间复杂度稳定在O(log n)。

Q：什么是回表？如何避免？

回表是指通过二级索引查找到主键值后，再回到聚簇索引查找完整数据的过程。回表会影响性能，因为需要两次索引查找。避免回表的方法是使用覆盖索引，即把查询的所有字段都包含在索引中。比如查询SELECT id, name FROM user WHERE phone = '138'，如果创建(phone, name)的联合索引，就不需要回表，因为name字段已经在索引中了。

Q：联合索引的设计原则是什么？

联合索引设计遵循三个原则：第一，最左前缀原则，索引(a,b,c)可以支持a、(a,b)、(a,b,c)的查询，但不能支持b或c的单独查询；第二，高区分度字段在前，比如(user_id, status)比(status, user_id)更好，因为user_id区分度更高；第三，WHERE条件字段在前，排序/分组字段在后，比如WHERE user_id = 123 ORDER BY create_time，索引应该是(user_id, create_time)，这样可以同时优化查询和排序。