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

为什么需要索引？

没有索引的查询是全表扫描，数据量大时性能极差。合适的索引可以让查询从几秒降到几毫秒。

B+ 树原理

MySQL InnoDB 使用 B+ 树索引：

• 叶子节点存储完整数据
• 非叶子节点只存储索引
• 树高度通常 2-4 层
• 每次查询只需 2-4 次 IO

索引类型

主键索引

CREATE TABLE users (
  id INT PRIMARY KEY,  -- 聚簇索引
  name VARCHAR(100)
);

普通索引

CREATE INDEX idx_name ON users(name);

唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);

联合索引

CREATE INDEX idx_name_age ON users(name, age);

最左前缀原则

-- 联合索引 (name, age, city)

-- ✅ 可以使用索引
WHERE name = "John"
WHERE name = "John" AND age = 25
WHERE name = "John" AND age = 25 AND city = "Beijing"

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

索引失效场景

函数操作

-- ❌ 索引失效
WHERE YEAR(created_at) = 2024

-- ✅ 使用索引
WHERE created_at >= "2024-01-01" AND created_at < "2025-01-01"

类型转换

-- ❌ 字符串字段不用引号
WHERE phone = 13800138000

-- ✅ 正确使用
WHERE phone = "13800138000"

LIKE 通配符

-- ❌ 前缀通配符
WHERE name LIKE "%John%"

-- ✅ 前缀匹配
WHERE name LIKE "John%"

OR 条件

-- ❌ 部分情况失效
WHERE name = "John" OR age = 25

-- ✅ 使用 UNION
SELECT * FROM users WHERE name = "John"
UNION
SELECT * FROM users WHERE age = 25

覆盖索引

-- 索引 (name, age)

-- ✅ 覆盖索引，不回表
SELECT name, age FROM users WHERE name = "John"

-- ❌ 需要回表
SELECT * FROM users WHERE name = "John"

EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE name = "John";

关键字段：

• type: system > const > eq_ref > ref > range > index > ALL
• key: 实际使用的索引
• rows: 扫描行数
• Extra: Using index(覆盖索引), Using filesort(需要优化)

索引设计原则

1. 高频查询字段建索引
2. 区分度高的字段优先
3. 联合索引注意顺序
4. 避免过度索引（影响写入性能）
5. 定期清理无用索引

实战案例

-- 优化前：3.5 秒
SELECT * FROM orders 
WHERE user_id = 123 AND status = 1 
ORDER BY created_at DESC LIMIT 10;

-- 优化后：0.03 秒
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at DESC);

总结

索引是数据库优化的利器。理解原理，合理使用，让查询飞起来！