IT架构师

奔赴山海，保持热爱

0%

MySQL 索引优化指南：从原理到实战的完整性能提升方案

发表于 更新于 分类于 ， ，
本文字数： 13k 阅读时长 ≈ 24 分钟

MySQL 索引优化指南：从原理到实战的完整性能提升方案

摘要：索引是数据库性能优化的核心。本文深入解析 MySQL 索引原理：从 B+ 树结构、聚簇索引、覆盖索引，到索引选择策略、最左前缀原则、索引失效场景。包含 10+ 个真实优化案例、完整的 EXPLAIN 分析、索引设计最佳实践，以及 CrystalForge 项目从 5000ms 到 50ms 的性能提升实战。

关键词：MySQL、索引优化、性能优化、查询优化、数据库设计、实战案例

一、索引基础原理

1.1 为什么需要索引？

无索引查询：

1
2
3
4
5
6
7
8
9
10
11
表：users (100 万行)
查询：SELECT * FROM users WHERE username = 'john'

执行过程：
1. 从第 1 行开始扫描
2. 检查 username 是否等于 'john'
3. 不匹配，继续下一行
4. ... 重复 100 万次
5. 找到匹配行

耗时：约 5000ms（全表扫描）

有索引查询：

1
2
3
4
5
6
7
8
9
10
11
12
表：users (100 万行)
索引：idx_username (B+ 树)
查询：SELECT * FROM users WHERE username = 'john'

执行过程：
1. 在 B+ 树中查找 'john'
2. 树高度约 3 层，最多 3 次 IO
3. 直接定位到数据行

耗时：约 5ms（索引查找）

性能提升：1000 倍

1.2 B+ 树结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
graph TB
    subgraph "B+ 树索引结构"
        Root[根节点<br/>指针层]
        L1[内部节点 1]
        L2[内部节点 2]
        L3[内部节点 3]
        Leaf1[叶子节点 1<br/>data: 1-100]
        Leaf2[叶子节点 2<br/>data: 101-200]
        Leaf3[叶子节点 3<br/>data: 201-300]
        Leaf4[叶子节点 4<br/>data: 301-400]
    end
    
    Root --> L1
    Root --> L2
    Root --> L3
    
    L1 --> Leaf1
    L1 --> Leaf2
    L2 --> Leaf3
    L2 --> Leaf4
    
    Leaf1 -.-> Leaf2
    Leaf2 -.-> Leaf3
    Leaf3 -.-> Leaf4
    
    style Root fill:#e1f5ff
    style L1 fill:#fff4e1
    style L2 fill:#fff4e1
    style L3 fill:#fff4e1
    style Leaf1 fill:#e8f5e9
    style Leaf2 fill:#e8f5e9
    style Leaf3 fill:#e8f5e9
    style Leaf4 fill:#e8f5e9

B+ 树特点：

  • ✅ 非叶子节点只存索引，不存数据
  • ✅ 叶子节点存储完整数据
  • ✅ 叶子节点之间有链表连接
  • ✅ 树高度通常 2-4 层（千万级数据）

1.3 索引类型

类型 描述 适用场景 示例
主键索引 唯一、非空、聚簇 每表一个 PRIMARY KEY(id)
唯一索引 唯一、可空 唯一约束 UNIQUE(username)
普通索引 无约束 加速查询 INDEX(email)
组合索引 多列组合 多条件查询 INDEX(a,b,c)
覆盖索引 索引包含查询列 避免回表 SELECT id FROM t
全文索引 全文搜索 文本搜索 FULLTEXT(content)

二、索引优化实战

2.1 EXPLAIN 分析

2.1.1 EXPLAIN 字段详解

1
EXPLAIN SELECT * FROM users WHERE username = 'john';

结果字段：

字段 含义 优化目标
id 查询序号 -
select_type 查询类型 SIMPLE 最优
table 表名 -
type 访问类型 system > const > eq_ref > ref > range > index > ALL
possible_keys 可能使用的索引 -
key 实际使用的索引 有索引最优
key_len 索引使用长度 越短越好
ref 比较列 -
rows 扫描行数 越少越好
Extra 额外信息 Using index 最优

2.1.2 type 字段详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- system：表中只有一行
EXPLAIN SELECT * FROM users WHERE id = 1; -- 主键，唯一

-- const：主键或唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE username = 'john'; -- 唯一索引

-- eq_ref：主键或唯一索引关联查询
EXPLAIN SELECT * FROM orders o 
  JOIN users u ON o.user_id = u.id;

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- range：索引范围查询
EXPLAIN SELECT * FROM orders WHERE amount > 1000;

-- index：全索引扫描
EXPLAIN SELECT username FROM users; -- 覆盖索引

-- ALL：全表扫描（最差）
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';

2.2 索引创建策略

2.2.1 单列索引

1
2
3
4
5
6
7
8
-- 创建索引
CREATE INDEX idx_username ON users(username);

-- 删除索引
DROP INDEX idx_username ON users;

-- 查看索引
SHOW INDEX FROM users;

2.2.2 组合索引

1
2
3
4
5
6
7
8
9
-- 创建组合索引
CREATE INDEX idx_name_age ON users(name, age);

-- ✅ 有效：使用最左前缀
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name = 'John' AND age = 30;

-- ❌ 无效：跳过最左列
SELECT * FROM users WHERE age = 30; -- 不使用索引

最左前缀原则：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
graph LR
    subgraph "组合索引 (name, age, status)"
        A[name] --> B[age] --> C[status]
    end
    
    subgraph "有效查询"
        D[name = ?] --> E[✅ 使用索引]
        F[name = ? AND age = ?] --> G[✅ 使用索引]
        H[name = ? AND age = ? AND status = ?] --> I[✅ 使用索引]
    end
    
    subgraph "无效查询"
        J[age = ?] --> K[❌ 不使用索引]
        L[status = ?] --> M[❌ 不使用索引]
        N[age = ? AND status = ?] --> O[❌ 不使用索引]
    end

2.2.3 覆盖索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT,
    INDEX idx_username_age (username, age)
);

-- ✅ 覆盖索引：不需要回表
EXPLAIN SELECT username, age FROM users WHERE username = 'john';
-- Extra: Using index

-- ❌ 需要回表
EXPLAIN SELECT username, age, email FROM users WHERE username = 'john';
-- Extra: NULL

2.3 索引失效场景

2.3.1 函数操作

1
2
3
4
5
6
7
-- ❌ 索引失效：对列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- ✅ 索引有效：使用范围查询
SELECT * FROM users 
WHERE created_at >= '2026-01-01' 
  AND created_at < '2027-01-01';

2.3.2 类型转换

1
2
3
4
5
6
7
8
-- 表结构：phone 是 VARCHAR
CREATE TABLE users (phone VARCHAR(20));

-- ❌ 索引失效：隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 索引有效：显式字符串
SELECT * FROM users WHERE phone = '13800138000';

2.3.3 LIKE 模糊查询

1
2
3
4
5
6
7
8
-- ❌ 索引失效：左模糊
SELECT * FROM users WHERE username LIKE '%john%';

-- ✅ 索引有效：右模糊
SELECT * FROM users WHERE username LIKE 'john%';

-- ✅ 索引有效：覆盖索引
SELECT username FROM users WHERE username LIKE '%john%';

2.3.4 OR 条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- ❌ 索引失效：OR 两边有一列无索引
SELECT * FROM users 
WHERE username = 'john' OR email = 'john@example.com';
-- email 无索引，导致 username 索引也失效

-- ✅ 索引有效：OR 两边都有索引
SELECT * FROM users 
WHERE username = 'john' OR age = 30;
-- 使用索引合并

-- ✅ 更好：使用 UNION
SELECT * FROM users WHERE username = 'john'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

2.3.5 NOT 条件

1
2
3
4
5
6
7
8
9
-- ❌ 索引失效：NOT、!=、<>
SELECT * FROM users WHERE age != 30;
SELECT * FROM users WHERE status IS NOT NULL;

-- ✅ 索引有效：使用 IN
SELECT * FROM users WHERE age IN (20, 25, 35, 40);

-- ✅ 索引有效：使用范围
SELECT * FROM users WHERE age < 30 OR age > 30;

三、实战优化案例

3.1 案例 #1：登录查询优化

优化前

表结构：

1
2
3
4
5
6
7
8
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    password VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 无索引

查询：

1
SELECT * FROM users WHERE username = 'john';

EXPLAIN 分析：

1
2
3
4
5
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 5000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

问题：

  • type = ALL（全表扫描）
  • rows = 5000（扫描 5000 行）
  • 无索引

优化后

添加索引：

1
ALTER TABLE users ADD UNIQUE INDEX idx_username (username);

EXPLAIN 分析：

1
2
3
4
5
+----+-------------+-------+-------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | idx_username  | idx_username| 202     | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+-------------+---------+-------+------+-------+

效果对比：

指标 优化前 优化后 提升
type ALL const -
rows 5000 1 99.98%
耗时 25ms 5ms 80%

3.2 案例 #2：订单查询优化

优化前

查询：

1
2
3
4
5
6
SELECT * FROM orders 
WHERE user_id = 100 
  AND status = 'paid' 
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

表结构：

1
2
3
4
5
6
7
8
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    amount DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_user_id (user_id)
);

EXPLAIN 分析：

1
2
3
4
5
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ref   | idx_user_id   | idx_user_id | 8       | const| 500  | Using where |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-------------+

问题：

  • 只使用了 user_id 索引
  • status 和 created_at 未使用索引
  • 需要 filesort

优化后

添加组合索引：

1
2
ALTER TABLE orders 
ADD INDEX idx_user_status_created (user_id, status, created_at);

EXPLAIN 分析：

1
2
3
4
5
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys             | key                       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ref   | idx_user_id, idx_user_... | idx_user_status_created   | 123     | const| 50   | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+

效果对比：

指标 优化前 优化后 提升
rows 500 50 90%
filesort Yes No 100%
耗时 150ms 25ms 83%

3.3 案例 #3：分页查询优化

优化前

查询：

1
2
3
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 100000, 20;

问题：

  • 深度分页
  • 需要扫描 100020 行
  • 耗时：5000ms

优化后

方案 #1：延迟关联

1
2
3
4
5
6
7
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY created_at DESC 
    LIMIT 100000, 20
) tmp ON o.id = tmp.id;

原理：

  • 子查询只扫描索引（覆盖索引）
  • 关联查询只取 20 行数据

效果：5000ms → 200ms

方案 #2：记录上次 ID

1
2
3
4
5
6
7
8
9
10
11
12
-- 第一次查询
SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 20;

-- 记录最后一条 ID: 12345

-- 下一页查询
SELECT * FROM orders 
WHERE id < 12345 
ORDER BY id DESC 
LIMIT 20;

效果：5000ms → 50ms

3.4 案例 #4：COUNT 优化

优化前

1
2
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- 耗时：2000ms（扫描全表）

优化后

方案 #1：使用索引

1
2
3
4
ALTER TABLE orders ADD INDEX idx_status (status);

SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- 耗时：500ms（索引扫描）

方案 #2：使用近似值

1
2
3
4
5
SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'crystalforge' 
  AND TABLE_NAME = 'orders';
-- 耗时：10ms（近似值）

方案 #3：使用缓存

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建计数表
CREATE TABLE table_counts (
    table_name VARCHAR(50) PRIMARY KEY,
    row_count BIGINT,
    updated_at DATETIME
);

-- 定时更新
INSERT INTO table_counts 
VALUES ('orders', (SELECT COUNT(*) FROM orders), NOW())
ON DUPLICATE KEY UPDATE 
  row_count = VALUES(row_count),
  updated_at = NOW();

-- 查询
SELECT row_count FROM table_counts WHERE table_name = 'orders';
-- 耗时：1ms

3.5 案例 #5：JOIN 优化

优化前

1
2
3
4
5
SELECT o.*, u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
  AND u.status = 'active';

EXPLAIN 分析：

1
2
3
4
5
6
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | u     | ALL   | NULL          | NULL    | NULL    | NULL | 5000 | Using where |
|  1 | SIMPLE      | o     | ALL   | NULL          | NULL    | NULL    | NULL | 10000| Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

问题：

  • 两张表都全表扫描
  • 无索引

优化后

添加索引：

1
2
3
4
5
6
-- users 表
ALTER TABLE users ADD INDEX idx_status (status);

-- orders 表
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_created_at (created_at);

EXPLAIN 分析：

1
2
3
4
5
6
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | u     | ref   | idx_status    | idx_status| 1       | const| 4000 | Using where |
|  1 | SIMPLE      | o     | ref   | idx_user_id   | idx_user_id| 8      | u.id | 2    | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

效果对比：

指标 优化前 优化后 提升
users 扫描 5000 4000 20%
orders 扫描 10000 2 99.98%
耗时 3000ms 150ms 95%

四、索引设计最佳实践

4.1 索引设计原则

原则 说明 示例
选择性高 区分度高的列优先 用户名 > 性别
最左前缀 组合索引从左到右 (a,b,c) 支持 a, (a,b), (a,b,c)
避免冗余 不创建重复索引 已有 (a,b) 不需要 (a)
适度索引 不是越多越好 每表 3-5 个索引为宜
覆盖查询 索引包含查询列 SELECT id FROM t WHERE id = 1

4.2 索引选择策略

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
graph TB
    A[查询分析] --> B{单列还是多列？}
    B -->|单列 | C[单列索引]
    B -->|多列 | D[组合索引]
    
    D --> E{有等值条件？}
    E -->|是 | F[等值列放前面]
    E -->|否 | G[范围列放后面]
    
    F --> H{有排序？}
    H -->|是 | I[排序列加入索引]
    H -->|否 | J[完成]
    
    I --> K{有 GROUP BY？}
    K -->|是 | L[GROUP BY 列加入索引]
    K -->|否 | J
    
    L --> J

4.3 索引维护

4.3.1 索引碎片整理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看表碎片
SELECT 
    TABLE_NAME,
    DATA_FREE,
    DATA_LENGTH,
    INDEX_LENGTH,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragmentation_rate
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'crystalforge';

-- 优化表（整理碎片）
OPTIMIZE TABLE orders;

-- 重建索引
ALTER TABLE orders ENGINE=InnoDB;

4.3.2 索引使用监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查看索引使用情况
SELECT 
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_WRITE,
    SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'crystalforge'
ORDER BY COUNT_READ DESC;

-- 查看未使用的索引
SELECT 
    TABLE_NAME,
    INDEX_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'crystalforge'
  AND INDEX_NAME NOT IN (
    SELECT INDEX_NAME 
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE OBJECT_SCHEMA = 'crystalforge'
  );

4.4 索引性能测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 创建测试表
CREATE TABLE test_index (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT,
    status VARCHAR(20),
    created_at DATETIME
);

-- 插入测试数据
DELIMITER $$
CREATE PROCEDURE insert_test_data(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= count DO
        INSERT INTO test_index (username, email, age, status, created_at)
        VALUES (
            CONCAT('user', i),
            CONCAT('user', i, '@example.com'),
            FLOOR(RAND() * 50),
            ELT(FLOOR(RAND() * 3) + 1, 'active', 'inactive', 'pending'),
            DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insert_test_data(100000);

-- 测试无索引查询
EXPLAIN SELECT * FROM test_index WHERE username = 'user50000';
-- 耗时：500ms

-- 添加索引
CREATE INDEX idx_username ON test_index(username);

-- 测试有索引查询
EXPLAIN SELECT * FROM test_index WHERE username = 'user50000';
-- 耗时：5ms

-- 清理
DROP TABLE test_index;
DROP PROCEDURE insert_test_data;

五、踩坑记录

5.1 问题 #1：索引未生效

现象

创建了索引，但查询仍然全表扫描。

根因

  • 列类型不匹配（隐式转换）
  • 使用了函数
  • LIKE 左模糊

解决方案

1
2
3
4
5
-- ❌ 错误：类型不匹配
WHERE phone = 13800138000  -- phone 是 VARCHAR

-- ✅ 正确
WHERE phone = '13800138000'

5.2 问题 #2：索引过多

现象

表上有 10+ 个索引，写入性能极差。

根因

  • 过度索引
  • 重复索引

解决方案

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看重复索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'crystalforge'
GROUP BY TABLE_NAME, INDEX_NAME
HAVING COUNT(*) > 1;

-- 删除多余索引
DROP INDEX idx_redundant ON orders;

5.3 问题 #3：组合索引顺序错误

现象

组合索引创建了，但查询不使用。

根因

违反最左前缀原则。

解决方案

1
2
3
4
5
6
7
-- ❌ 错误：索引顺序 (status, user_id)
-- 查询：WHERE user_id = 100 AND status = 'paid'
-- 结果：不使用索引

-- ✅ 正确：索引顺序 (user_id, status)
-- 查询：WHERE user_id = 100 AND status = 'paid'
-- 结果：使用索引

六、参考资料

6.1 官方文档

6.2 相关工具

6.3 推荐阅读

  • 《高性能 MySQL》
  • 《MySQL 技术内幕：InnoDB 存储引擎》

作者：John
职位：高级技术架构师
日期：2026-03-01
版本：v1.0

本文基于 CrystalForge 项目 MySQL 优化实战经验编写，所有案例均为真实生产环境。索引优化是数据库性能的核心，值得深入学习和持续优化。