MySQL 索引优化完全指南

为什么需要索引？

没有索引的查询 = 全表扫描，数据量大时性能灾难。

索引类型详解

B+Tree 索引（最常用）

• 适用于：等值查询、范围查询
• 限制：最左前缀原则

哈希索引

• 适用于：精确匹配
• 限制：不支持范围查询

全文索引

• 适用于：文本搜索
• MySQL 5.7+ 支持中文

空间索引

• 适用于：地理位置数据

创建索引的最佳时机

-- 查看慢查询
SHOW SLOW QUERIES;

-- 分析查询
EXPLAIN SELECT * FROM users WHERE email = "test@example.com";

EXPLAIN 关键字段：

• type：访问类型（ALL < index < range < ref < eq_ref < const）
• key：实际使用的索引
• rows：扫描行数
• Extra：额外信息

索引设计原则

✅ 应该创建索引的场景

1. 主键、外键自动创建索引
2. WHERE 子句中的列
3. JOIN 连接条件
4. ORDER BY、GROUP BY 的列
5. 区分度高的列（如邮箱、手机号）

❌ 不适合创建索引的场景

1. 表记录太少（<1000 行）
2. 频繁更新的列
3. 区分度低的列（如性别）
4. 频繁修改的表结构

复合索引技巧

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 有效使用（符合最左前缀）
SELECT * FROM users WHERE name = "张三";
SELECT * FROM users WHERE name = "张三" AND age = 25;

-- 无效使用
SELECT * FROM users WHERE age = 25; -- 无法使用索引

覆盖索引优化

-- 普通查询（回表）
SELECT * FROM users WHERE email = "test@example.com";

-- 覆盖索引（不回表）
SELECT id, email FROM users WHERE email = "test@example.com";

索引失效的常见陷阱

-- ❌ 函数操作导致失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 改写
SELECT * FROM users WHERE created_at >= "2024-01-01";

-- ❌ 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 保持类型一致
SELECT * FROM users WHERE phone = "13800138000";

-- ❌ LIKE 以%开头
SELECT * FROM users WHERE name LIKE "%张%";

-- ✅ LIKE 不以%开头
SELECT * FROM users WHERE name LIKE "张%";

索引维护

-- 查看索引使用情况
SHOW INDEX FROM users;

-- 删除无用索引
DROP INDEX idx_unused ON users;

-- 分析表优化
ANALYZE TABLE users;

实战案例

问题：用户表 1000 万数据，按邮箱查询需要 5 秒

解决：

CREATE INDEX idx_email ON users(email);

结果：查询时间降至 0.05 秒，提升 100 倍！

结语

索引是双刃剑，合理使用才能发挥最大价值！