MySQL 索引全面指南：避免索引失效的实践

索引是数据库性能优化的核心工具，但错误使用索引会导致查询性能下降。本文从原理到实战，全面讲解索引的机制、常见失效场景、解决方案及优化策略，并附可执行 SQL 示例。

索引原理深入讲解
索引优化技巧
覆盖索引与前缀索引实践
联合索引优化策略
EXPLAIN 高级使用技巧
索引维护与监控

1. 索引原理与工作机制

1.1 B-Tree 索引

结构：平衡树（B-Tree/B+Tree），叶子节点存储数据指针
适用场景：
- 精确查询：=
- 范围查询：>, <, BETWEEN
- 排序：ORDER BY （按索引顺序）
注意：对于前置 % 的 LIKE 查询无效，因为无法从最左前缀定位。

1.2 Hash 索引

结构：哈希表
适用场景：
- 精确匹配查询
限制：
- 不支持范围查询
- 不支持排序
- InnoDB 默认不支持 Hash 索引，只在 MEMORY 表可用

1.3 全文索引

用途：文本搜索（如文章内容）
适用场景：
- MATCH() AGAINST()
- 搜索关键词匹配
特点：
- 适合大文本查询
- 对短文本或精确匹配不如 B-Tree 高效

2. 索引类型与应用场景

索引类型	使用场景	优点	注意事项
主键索引	唯一标识	聚集索引，访问快	表必须有主键
唯一索引	唯一约束	防止重复数据	插入数据时会检查
普通索引	查询加速	查询快	写入慢
联合索引	多列查询	支持多条件组合	遵循最左前缀原则
前缀索引	长字符串节省空间	节省索引空间	匹配全列需注意可能重复
覆盖索引	查询列在索引中	查询无需回表	适合 SELECT 列较少的场景

3. 常见索引失效场景与解决方案

3.1 LIKE 前置通配符

错误：

SELECT * FROM users WHERE name LIKE '%John';

原因：无法利用 B-Tree 索引
解决方案：

SELECT * FROM users WHERE name LIKE 'John%';

或使用全文索引 / 外部搜索引擎。

3.2 函数操作索引列

错误：

SELECT * FROM orders WHERE YEAR(order_date) = 2025;

解决方案：改写为范围查询

SELECT * FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

3.3 数据类型不一致

错误：

SELECT * FROM products WHERE product_id = '123';

解决方案：

SELECT * FROM products WHERE product_id = 123;

3.4 联合索引顺序错误

CREATE INDEX idx_name_age ON users(name, age);

-- 错误
SELECT * FROM users WHERE age = 25 AND name = 'John';

-- 正确
SELECT * FROM users WHERE name = 'John' AND age = 25;

优化策略：

高频过滤列放在最左列
尽量让联合索引覆盖常用查询条件

3.5 OR 条件优化

错误：

SELECT * FROM users WHERE name = 'John' OR age = 30;

优化：使用 UNION 拆分

SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE age = 30;

3.6 ORDER BY / GROUP BY 优化

避免对索引列使用函数
保证索引列顺序与排序列一致
使用覆盖索引可加速排序

3.7 大偏移量分页

错误：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

优化：索引列条件分页

SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

4. 覆盖索引与前缀索引实践

4.1 覆盖索引

当 SELECT 查询的所有列都包含在索引中，可以避免回表，提高查询效率：

-- 联合索引覆盖查询列
CREATE INDEX idx_name_email ON users(name, email);

SELECT name, email FROM users WHERE name = 'John';
-- 不访问数据表，仅扫描索引

4.2 前缀索引

对长字符串节省空间
注意：匹配全列可能会重复，需要结合查询优化

CREATE INDEX idx_email_prefix ON users(email(10));

SELECT * FROM users WHERE email LIKE 'john%@example.com';

5. EXPLAIN 高级使用技巧

type：访问类型（ALL、index、ref、range）
key：使用的索引
rows：扫描行数
Extra：额外信息（Using index、Using where、Using filesort）

示例：

EXPLAIN SELECT * FROM users WHERE name = 'John';
EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';

通过 EXPLAIN 可以判断索引是否被使用，并评估查询性能。

6. 索引维护与监控

定期检查慢查询
- SHOW PROCESSLIST
- slow_query_log
定期优化表
- ANALYZE TABLE users; 更新统计信息
- OPTIMIZE TABLE users; 重新整理表
删除不必要的索引
- 避免过多索引增加写入开销
监控索引使用率
- performance_schema.table_io_waits_summary_by_index_usage
- 找出未使用索引进行优化

7. 可执行示例脚本（索引失效演示）

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50),
    age INT,
    created_at DATETIME,
    INDEX idx_name(name),
    INDEX idx_age(age),
    INDEX idx_name_age(name, age)
);

INSERT INTO users (name, email, age, created_at)
VALUES
('John Doe','john@example.com', 25, '2025-01-01 10:00:00'),
('Jane Smith','jane@example.com', 30, '2025-02-15 12:30:00'),
('Alice','alice@example.com', 22, '2025-03-10 09:15:00'),
('Bob','bob@example.com', 28, '2025-04-05 16:45:00');

-- LIKE 前置通配符
EXPLAIN SELECT * FROM users WHERE name LIKE '%John';
EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';

-- 函数操作
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2025;
EXPLAIN SELECT * FROM users
WHERE created_at >= '2025-01-01 00:00:00'
  AND created_at < '2026-01-01 00:00:00';

-- 数据类型不一致
EXPLAIN SELECT * FROM users WHERE age = '25';
EXPLAIN SELECT * FROM users WHERE age = 25;

-- 联合索引顺序
EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'John Doe';
EXPLAIN SELECT * FROM users WHERE name = 'John Doe' AND age = 25;

-- OR 条件
EXPLAIN SELECT * FROM users WHERE name = 'John Doe' OR age = 30;
EXPLAIN
SELECT * FROM users WHERE name = 'John Doe'
UNION
SELECT * FROM users WHERE age = 30;

-- ORDER BY 函数操作
EXPLAIN SELECT * FROM users ORDER BY DATE(created_at);
EXPLAIN SELECT * FROM users ORDER BY created_at;

-- 大偏移量分页
EXPLAIN SELECT * FROM users ORDER BY id LIMIT 100000, 10;
EXPLAIN SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

8. 归纳

通过本教程：

掌握了 索引类型、原理与应用场景
学会 识别索引失效 的常见问题
了解 解决方案与优化技巧
学会使用 EXPLAIN、覆盖索引、前缀索引
提供可执行 SQL 脚本，方便演示与实践

索引优化不是单纯增加索引，而是 结合查询模式、数据特性、SQL 写法 全面考虑。
定期检查慢查询、维护索引、优化 SQL 才能让 MySQL 查询达到最佳性能。
高级索引优化案例 : 适合对索引有一定理解能力的开发者

9. 高级索引优化案例

9.1 大表联合索引优化

问题场景

假设有一个大表 orders，包含 1000 万条数据，查询经常用如下条件：

SELECT * FROM orders
WHERE customer_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

问题：

如果没有索引，或者索引顺序不合理，查询可能会全表扫描，性能极差。

优化策略

创建联合索引，并遵循最左前缀原则：

CREATE INDEX idx_customer_status_created ON orders(customer_id, status, created_at DESC);

查询时按索引列顺序书写：

SELECT * FROM orders
WHERE customer_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

这样 MySQL 可以直接利用联合索引完成 WHERE + ORDER BY，无需额外排序。

9.2 范围查询 + 分页优化

问题场景

大表分页查询常见写法：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 50;

OFFSET 很大，MySQL 需要扫描大量行才能返回结果，效率低。

优化方法

基于索引列范围分页（keyset pagination）：

SELECT * FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 50;

如果涉及多列条件，可以结合联合索引：

CREATE INDEX idx_status_id ON orders(status, id);

SELECT * FROM orders
WHERE status='paid' AND id > 1000000
ORDER BY id
LIMIT 50;

优点：查询时间与 OFFSET 无关，适合大表高效分页。

9.3 EXPLAIN 分析慢查询实例

示例表

EXPLAIN SELECT * FROM orders
WHERE customer_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

输出解析

id	select_type	table	type	key	rows	Extra
1	SIMPLE	orders	ref	idx_customer_status_created	100	Using index

type = ref：索引查找
key：使用了联合索引
Extra = Using index：覆盖索引，无需访问表

优化思路

检查 type：尽量避免 ALL（全表扫描）
检查 key：确保使用了合理索引
检查 Extra：是否回表（Using index 为覆盖索引，可避免回表）
如果仍慢，可考虑分区表或分表策略

9.4 覆盖索引优化实战

问题场景

SELECT customer_id, status FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

如果查询的列在索引中，就可以避免回表，提高效率。

优化方法

CREATE INDEX idx_created_status_customer ON orders(created_at, status, customer_id);

SELECT customer_id, status FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

MySQL 可以只扫描索引，避免访问数据行（减少 IO）
对于大表统计类查询尤其有效。

9.5 总结高级优化技巧

联合索引顺序设计：
- 高频过滤列放前面
- ORDER BY 列放在索引末尾可加速排序
范围查询分页优化：
- 避免大 OFFSET
- 使用索引列范围分页（Keyset Pagination）
覆盖索引：
- 查询列全在索引中，减少回表
- 提高大表查询性能
EXPLAIN 使用：
- type、key、Extra 三个字段是判断索引使用效果关键
- 优化前必须 EXPLAIN 验证
大表索引维护：
- 定期 ANALYZE TABLE 更新统计信息
- 删除无用索引，减少写入负担
- 必要时结合分区/分表策略

10. 图解版索引原理与优化

10.1 B-Tree 索引结构

概念：B-Tree（B+Tree）是 MySQL InnoDB 默认的索引结构。

节点结构：
- 根节点、内部节点、叶子节点
- 叶子节点存储索引值和指向数据的指针（非聚集索引）或数据本身（聚集索引）

查询原理图：

          [Root Node]
          /         \
     [Node 10-20]   [Node 21-30]
     /      \       /       \
 [Leaf 11] [Leaf 15] [Leaf 21] [Leaf 25]

查询 id = 15：
- 从根节点判断分支 → 进入 Node 10-20 → 找到 Leaf 15 → 获取数据
优点：快速定位，无需扫描全表
失效情况：使用 %xxx 模糊匹配、函数操作列、类型不一致

10.2 联合索引与最左前缀原则

联合索引：例如 (name, age)

查询可以用索引的情况：

WHERE name = 'John' AND age = 25
WHERE name = 'John'

查询无法用索引的情况：

WHERE age = 25
WHERE age = 25 AND name = 'John'

原理示意图：

联合索引 idx(name, age)
索引树结构：
name=Alice -> age=22
name=Bob   -> age=28
name=John  -> age=25,30

最左前缀查询：
WHERE name='John' -> 可用索引
WHERE age=25     -> 不可用索引

核心：联合索引必须从最左列开始匹配才能利用索引。

10.3 覆盖索引示意

概念：查询列全在索引中，不需要访问数据行。
示意：

索引 idx(name,email)：
+-----+---------------------+ 
| name| email               |  <- 覆盖查询只扫描索引
+-----+---------------------+
| John| john@example.com    |
| Alice| alice@example.com  |
+-----+---------------------+

查询：
SELECT name, email FROM users WHERE name='John';

优点：减少 IO，提升大表查询性能
失效情况：查询列不全在索引中，仍需回表

10.4 Keyset 分页（索引列范围分页）

问题：大 OFFSET 分页效率低
解决方案：用索引列范围分页

示意图：

索引列 id:
[1] -> [2] -> [3] -> ... -> [1000000] -> [1000001] -> ...

分页查询：
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;

扫描路径：
id=1000001 -> id=1000002 -> ... -> id=1000010

优点：查询时间与 OFFSET 无关
高效处理百万级以上数据分页

10.5 索引优化流程图

查询慢? 
   |
   v
使用 EXPLAIN 分析
   |
   v
检查 type / key / Extra
   |
   v
索引未使用?
   |---> 改写 SQL: 避免函数/类型不一致/前置 %
   |---> 创建合适索引: 单列/联合/覆盖
   |---> 优化分页: Keyset 分页
   |
   v
验证 EXPLAIN 是否改善

10.6 总结图解版核心概念

概念	图解要点	优化策略
B-Tree 索引	根→内部→叶子节点快速定位	避免函数、前置 %、类型不一致
联合索引	最左前缀原则	高频过滤列放前面、覆盖常用查询
覆盖索引	查询列在索引中	减少回表，适合 SELECT 列少的查询
Keyset 分页	索引列范围扫描	避免大 OFFSET 分页，提高大表查询性能

通过这部分图解，就可以：

更直观理解索引内部结构
明白为什么索引会失效
理解联合索引顺序与覆盖索引优势
掌握大表分页优化原理

MySQL 索引全面指南：避免索引失效的实践

1. 索引原理与工作机制

1.1 B-Tree 索引

1.2 Hash 索引

1.3 全文索引

2. 索引类型与应用场景

3. 常见索引失效场景与解决方案

3.1 LIKE 前置通配符

3.2 函数操作索引列

3.3 数据类型不一致

3.4 联合索引顺序错误

3.5 OR 条件优化

3.6 ORDER BY / GROUP BY 优化

3.7 大偏移量分页

4. 覆盖索引与前缀索引实践

4.1 覆盖索引

4.2 前缀索引

5. EXPLAIN 高级使用技巧

6. 索引维护与监控

7. 可执行示例脚本（索引失效演示）

8. 归纳

9. 高级索引优化案例

9.1 大表联合索引优化

问题场景

优化策略

9.2 范围查询 + 分页优化

问题场景

优化方法

9.3 EXPLAIN 分析慢查询实例

示例表

输出解析

优化思路

9.4 覆盖索引优化实战

问题场景

优化方法

9.5 总结高级优化技巧

10. 图解版索引原理与优化

10.1 B-Tree 索引结构

10.2 联合索引与最左前缀原则

10.3 覆盖索引示意

10.4 Keyset 分页（索引列范围分页）

10.5 索引优化流程图

10.6 总结图解版核心概念

切换评论系统

阅读工具

大家喜欢

热门文章