MySQL索引优化：B+树原理与实战调优

MySQL索引优化：B+树原理与实战调优

索引是MySQL中提升查询性能最重要的手段。一个设计良好的索引可以让查询速度提升几个数量级，而一个糟糕的索引则可能占用大量磁盘空间却毫无作用。理解索引的底层原理是做好索引优化的前提。本文将从B+树的数据结构讲起，深入分析MySQL索引的工作机制和优化策略。

一、为什么MySQL使用B+树

MySQL选择B+树作为索引结构，是基于磁盘I/O特性做出的最优选择：

B+树的特点：

• 所有数据都存储在叶子节点，非叶子节点只存储索引键值
• 叶子节点之间通过双向链表连接，支持高效的范围查询
• 树的高度很低，通常3-4层就能存储千万级数据
• 每个节点的大小等于一个磁盘页（InnoDB默认16KB），一次I/O读取一个完整节点

-- B+树的查找过程
-- 假设有1000万条数据，B+树高度为3
-- 从根节点到叶子节点只需3次磁盘I/O

-- 第1次I/O：读取根节点（常驻内存）
-- 第2次I/O：读取第二层节点
-- 第3次I/O：读取叶子节点，获取数据

二、InnoDB索引类型

1. 聚簇索引（主键索引）

聚簇索引将数据和主键索引存储在一起，一张表只能有一个聚簇索引：

-- 主键即聚簇索引
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- 通过主键查询：只需查B+树一次
SELECT * FROM users WHERE id = 1001;

2. 二级索引（非主键索引）

二级索引的叶子节点存储的是主键值，查询时需要"回表"：

-- 通过email查询：先查二级索引，再回表查聚簇索引
SELECT * FROM users WHERE email = "test@example.com";
-- 1. 在idx_email中找到 email="test@example.com" -> id=1001
-- 2. 在主键索引中找到 id=1001 的完整行数据

-- 覆盖索引：避免回表
SELECT id, email FROM users WHERE email = "test@example.com";
-- 只需要查二级索引，因为id和email都在idx_email中

三、组合索引与最左前缀原则

-- 创建组合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 能使用索引的查询
WHERE user_id = 1
WHERE user_id = 1 AND status = 1
WHERE user_id = 1 AND status = 1 AND create_time > "2024-01-01"
WHERE user_id = 1 ORDER BY status            -- 索引排序
WHERE user_id = 1 ORDER BY status, create_time  -- 索引排序

-- 不能使用索引的查询（违反最左前缀）
WHERE status = 1
WHERE create_time > "2024-01-01"
WHERE status = 1 AND create_time > "2024-01-01"

-- 部分使用索引
WHERE user_id = 1 AND create_time > "2024-01-01"
-- 只能用到user_id部分，因为跳过了status

四、EXPLAIN深度解读

EXPLAIN SELECT * FROM orders 
WHERE user_id = 1 AND status = 1 
ORDER BY create_time DESC LIMIT 10;

EXPLAIN输出的关键列：

type字段从好到差的排序：system > const > eq_ref > ref > range > index > ALL

五、索引优化实战案例

案例1：慢查询优化

-- 原始查询（耗时2.5s）
SELECT * FROM orders 
WHERE status = 2 AND create_time BETWEEN "2024-01-01" AND "2024-12-31"
ORDER BY amount DESC LIMIT 20;

-- 分析：type=ALL，全表扫描
-- 优化：创建组合索引
CREATE INDEX idx_status_time_amount ON orders(status, create_time, amount);

-- 优化后（耗时0.01s）
-- type=range, key=idx_status_time_amount

案例2：避免filesort

-- 出现Using filesort
SELECT * FROM articles WHERE category_id = 5 ORDER BY create_time DESC;

-- 优化：创建覆盖排序的索引
CREATE INDEX idx_category_time ON articles(category_id, create_time DESC);

-- 优化后：Extra中filesort消失

六、索引使用注意事项

-- 1. 避免在索引列上使用函数
-- 不好
WHERE YEAR(create_time) = 2024
-- 好
WHERE create_time >= "2024-01-01" AND create_time < "2025-01-01"

-- 2. 避免隐式类型转换
-- 不好（phone是varchar，但传了数字）
WHERE phone = 13800138000
-- 好
WHERE phone = "13800138000"

-- 3. LIKE通配符位置
-- 不好（前导通配符无法用索引）
WHERE name LIKE "%张%"
-- 好
WHERE name LIKE "张%"

-- 4. OR条件的处理
-- 不好
WHERE status = 1 OR type = 2
-- 好（使用UNION）
SELECT * FROM t WHERE status = 1
UNION
SELECT * FROM t WHERE type = 2

索引优化是MySQL调优最核心的技能。建议养成在开发时先用EXPLAIN分析查询计划的习惯，确保每个查询都正确使用了索引。同时定期使用慢查询日志和pt-query-digest工具发现潜在的性能问题，及时优化。