MySQL 索引优化：让查询速度提升 100 倍

为什么需要索引？

没有索引的查询需要全表扫描（O(n)），有索引的查询是树查找（O(log n)）。百万级数据表中，索引能让查询从几秒降到几毫秒。

索引类型

B+Tree 索引（最常用）

• 适用于：=, >, <, BETWEEN, LIKE "prefix%"
• 最左前缀原则：复合索引 (a,b,c) 只能用于 a 或 a,b 或 a,b,c

哈希索引

• 仅适用于 = 和 IN
• 不支持范围查询和排序

全文索引

• 用于文本搜索
• MATCH() AGAINST() 语法

创建索引

-- 单列索引
CREATE INDEX idx_email ON users(email);

-- 复合索引（注意顺序！）
CREATE INDEX idx_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone);

-- 覆盖索引（包含查询需要的所有列）
CREATE INDEX idx_cover ON orders(user_id, status, created_at);

EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = "test@example.com";

关键字段：

• type：访问类型（system > const > eq_ref > ref > range > index > ALL）
• key：实际使用的索引
• rows：扫描行数（越少越好）
• Extra：额外信息（Using index=好，Using filesort=需要优化）

索引优化实战

场景 1：避免全表扫描

-- ❌ 差：全表扫描
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 好：使用索引
SELECT * FROM users 
WHERE created_at >= "2024-01-01" 
  AND created_at < "2025-01-01";

场景 2：最左前缀原则

-- 索引：(name, age, city)

-- ✅ 可以使用索引
WHERE name = "张三"
WHERE name = "张三" AND age = 25
WHERE name = "张三" AND age = 25 AND city = "北京"

-- ❌ 不能使用索引
WHERE age = 25
WHERE city = "北京"
WHERE age = 25 AND city = "北京"

场景 3：覆盖索引

-- 索引：(user_id, status, created_at)

-- ✅ 覆盖索引（不需要回表）
SELECT user_id, status, created_at 
FROM orders 
WHERE user_id = 123;

-- ❌ 需要回表
SELECT * FROM orders WHERE user_id = 123;

场景 4：LIKE 优化

-- ❌ 不能使用索引
SELECT * FROM users WHERE name LIKE "%张%";

-- ✅ 可以使用索引
SELECT * FROM users WHERE name LIKE "张%";

-- ✅ 全文索引（模糊匹配）
SELECT * FROM users WHERE MATCH(name) AGAINST("张");

索引失效的坑

1. 对索引列做运算

-- ❌ 索引失效
WHERE created_at + INTERVAL 1 DAY > NOW()

-- ✅ 索引有效
WHERE created_at > NOW() - INTERVAL 1 DAY

2. 类型隐式转换

-- phone 是 VARCHAR
-- ❌ 索引失效
WHERE phone = 13800138000

-- ✅ 索引有效
WHERE phone = "13800138000"

3. OR 条件

-- ❌ 可能全表扫描
WHERE name = "张三" OR age = 25

-- ✅ 使用 UNION
SELECT * FROM users WHERE name = "张三"
UNION
SELECT * FROM users WHERE age = 25;

索引维护

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

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

-- 分析表（更新统计信息）
ANALYZE TABLE users;

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

性能对比

场景	无索引	有索引	提升
等值查询	2.5s	0.02s	125 倍
范围查询	3.1s	0.05s	62 倍
排序查询	4.2s	0.03s	140 倍

总结

索引是数据库优化的核心。理解索引原理、正确使用 EXPLAIN 分析、避免常见陷阱，能让你的查询性能提升数十倍甚至上百倍！