MySQL索引深度解析：从B+树原理到慢查询优化实战

数据库性能优化是后端开发中永恒的话题，而索引优化则是其中最关键的一环。深入理解MySQL索引的底层原理，不仅能帮助我们写出更高效的查询，还能在出现问题时快速定位并解决。本文将从B+树的数据结构开始，逐步深入到索引的使用策略、慢查询分析方法，以及我在实际项目中遇到的优化案例。

一、MySQL索引的底层结构：B+树

1.1 为什么MySQL选择B+树

在理解索引优化之前，我们需要先了解MySQL为什么选择B+树作为索引的数据结构。常见的数据结构如哈希表、二叉搜索树、B树等，各有其适用场景：

哈希表：查询时间复杂度O(1)，但不支持范围查询和排序，且容易发生哈希冲突
二叉搜索树：查询时间复杂度O(log n)，但在数据量大的情况下树高会很高，导致磁盘I/O次数增加
B树：多路平衡搜索树，降低了树高，但非叶子节点也存储数据，导致每个节点能存储的键值对数量减少
B+树：在B树基础上改进，非叶子节点只存储键值，叶子节点存储完整数据，并通过链表连接，非常适合磁盘存储和范围查询

B+树的优势在于：非叶子节点不存储数据，使得一个节点可以存储更多的键值，从而降低树高，减少磁盘I/O次数。同时，叶子节点通过双向链表连接，天然支持高效的范围查询和排序。

1.2 InnoDB的索引结构

InnoDB存储引擎使用B+树实现索引，主要有两种索引类型：

聚簇索引（Clustered Index）

聚簇索引的叶子节点存储的是完整的行数据。InnoDB表的数据本身就是按照聚簇索引的顺序存储的，因此每张表只能有一个聚簇索引。默认情况下，主键就是聚簇索引。如果没有定义主键，InnoDB会选择第一个非空唯一索引作为聚簇索引；如果也没有这样的索引，则会隐式创建一个6字节的rowid作为聚簇索引。

-- 创建表时指定主键
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT,
  INDEX idx_age (age)
) ENGINE=InnoDB;

-- id列自动成为聚簇索引
-- email列上的UNIQUE约束会自动创建唯一索引
-- idx_age是普通二级索引

二级索引（Secondary Index）

二级索引的叶子节点存储的是索引列的值和对应的主键值。当通过二级索引查询时，MySQL首先找到对应的主键值，然后再通过主键值到聚簇索引中查找完整的行数据，这个过程称为「回表」。

为了减少回表操作，MySQL引入了「覆盖索引」的概念：如果查询的所有列都包含在索引中，MySQL可以直接从索引中获取所有需要的数据，无需回表。

-- 假设有索引 idx_name_email (name, email)
-- 以下查询可以使用覆盖索引，无需回表
SELECT name, email FROM users WHERE name = '张三';

-- 以下查询需要回表，因为需要获取age列
SELECT name, email, age FROM users WHERE name = '张三';

二、索引的设计原则

2.1 最左前缀原则

联合索引遵循最左前缀原则，即查询条件必须从索引的最左边开始连续匹配。如果跳跃了某个列，索引将不能被完全利用。

-- 创建联合索引
CREATE INDEX idx_name_age_email ON users(name, age, email);

-- 可以使用索引（匹配最左前缀）
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
SELECT * FROM users WHERE name = '张三' AND age = 25 AND email = 'zhangsan@example.com';

-- 不能使用索引（跳过了name）
SELECT * FROM users WHERE age = 25;

-- 部分使用索引（只能使用name列）
SELECT * FROM users WHERE name = '张三' AND email = 'zhangsan@example.com';

2.2 索引选择性

索引的选择性是指不重复索引值与总记录数的比值。选择性越高，索引的区分度越好，查询效率越高。一般来说，选择性低于10%的列不适合建立索引。

-- 计算索引选择性
SELECT 
  COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
  COUNT(DISTINCT age) / COUNT(*) AS age_selectivity,
  COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;

-- 结果示例：name_selectivity = 0.95, age_selectivity = 0.30, gender_selectivity = 0.02
-- gender列的选择性太低，不适合单独建立索引

2.3 避免索引失效的常见场景

在实际开发中，有很多常见的写法会导致索引失效：

-- 1. 对索引列进行函数操作
SELECT * FROM users WHERE YEAR(created_at) = 2026;  -- 索引失效
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';  -- 索引有效

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone是varchar，索引失效
SELECT * FROM users WHERE phone = '13800138000';  -- 索引有效

-- 3. 使用LIKE前缀模糊匹配
SELECT * FROM users WHERE name LIKE '%张三%';  -- 索引失效
SELECT * FROM users WHERE name LIKE '张三%';   -- 索引有效（前缀匹配）

-- 4. OR条件使用不当
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 可能索引失效
-- 优化：使用UNION
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25 AND name != '张三';

-- 5. 不等于和NOT IN
SELECT * FROM users WHERE age != 25;  -- 索引可能失效，取决于数据分布

-- 6. 索引列参与计算
SELECT * FROM users WHERE age + 1 = 26;  -- 索引失效
SELECT * FROM users WHERE age = 25;      -- 索引有效

三、慢查询分析与优化

3.1 启用慢查询日志

慢查询日志是定位性能问题的重要工具。可以通过以下配置开启：

-- 临时开启
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录执行时间超过1秒的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

-- my.cnf永久配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

3.2 EXPLAIN分析执行计划

EXPLAIN是分析SQL执行计划的核心工具。通过分析EXPLAIN的输出，我们可以了解MySQL是如何执行查询的。

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 20;

关键字段解读：

type：访问类型，从优到差依次为：system > const > eq_ref > ref > range > index > ALL。应尽量避免ALL（全表扫描）
possible_keys：可能使用的索引
key：实际使用的索引
rows：估计需要扫描的行数，越小越好
Extra：额外信息。Using index表示使用了覆盖索引；Using where表示存储引擎返回数据后还需要过滤；Using filesort表示需要额外排序

3.3 一个真实的优化案例

在我们的订单系统中，有一个查询需要按创建时间倒序分页查询某个用户的订单：

-- 原始查询
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 100000, 10;

这个查询在数据量大时非常慢，EXPLAIN显示type为index，rows超过100万。问题出在两个方面：

LIMIT 100000, 10需要扫描前100010条记录，然后丢弃前100000条
ORDER BY created_at DESC需要对所有记录进行排序

优化方案：

-- 方案1：使用覆盖索引优化
-- 创建覆盖索引
CREATE INDEX idx_user_created_id ON orders(user_id, created_at, id);

-- 先查询id，再回表查询完整数据
SELECT * FROM orders 
WHERE id IN (
  SELECT id FROM orders 
  WHERE user_id = 123 
  ORDER BY created_at DESC 
  LIMIT 100000, 10
);

-- 方案2：使用延迟关联（更优）
SELECT o.* 
FROM orders o
INNER JOIN (
  SELECT id 
  FROM orders 
  WHERE user_id = 123 
  ORDER BY created_at DESC 
  LIMIT 100000, 10
) tmp ON o.id = tmp.id;

-- 方案3：使用书签记录上一页的最后一条记录（最佳）
-- 上一页最后一条记录 created_at = '2026-04-01 12:00:00', id = 99999
SELECT * FROM orders 
WHERE user_id = 123 
  AND (created_at < '2026-04-01 12:00:00' 
       OR (created_at = '2026-04-01 12:00:00' AND id < 99999))
ORDER BY created_at DESC, id DESC
LIMIT 10;

最终采用方案3，查询耗时从原来的3.2秒降低到10毫秒。

四、索引维护与监控

4.1 索引的维护

索引并非创建后就一劳永逸，需要定期维护：

-- 查看索引使用情况
SELECT 
  table_name,
  index_name,
  cardinality,
  rows_selected,
  rows_inserted
FROM information_schema.statistics
WHERE table_schema = 'your_database';

-- 分析表和索引统计信息
ANALYZE TABLE orders;

-- 优化表（会锁表，谨慎使用）
OPTIMIZE TABLE orders;

-- 查看索引碎片情况
SELECT 
  table_name,
  index_name,
  ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'your_database' 
  AND data_free > 0;

4.2 索引优化检查清单

是否为WHERE、JOIN、ORDER BY、GROUP BY中使用的列建立了索引？
联合索引是否遵循最左前缀原则？
是否存在冗余索引（如(a,b)和(a)同时存在）？
选择性低的列是否建立了索引？
是否存在从未使用过的索引？
大表的分页查询是否优化了？
是否有查询使用了filesort或temporary table？

MySQL索引优化是一门需要理论基础和实践经验相结合的学问。理解B+树的结构和工作原理，掌握EXPLAIN的使用方法，遵循索引设计的基本原则，再加上定期的监控和维护，才能真正发挥索引的最大价值。希望本文的分享能帮助你在实际项目中写出更高效的SQL。