MySQL 索引优化实战指南

索引的重要性

索引是数据库性能的關鍵。正确的索引可以将查询速度提升数百倍甚至上千倍。

索引类型

B-Tree 索引

最常用的索引类型，适用于：

• 等值查询 (=)
• 范围查询 (>, <, BETWEEN)
• 排序 (ORDER BY)
• 前缀匹配 (LIKE "abc%")

哈希索引

仅支持等值查询，速度快但不支持范围查询。

全文索引

用于文本搜索 (FULLTEXT)。

创建索引

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

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

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

索引优化原则

1. 最左前缀原则

复合索引 (name, age, email) 可以支持：

• ✅ WHERE name = ?
• ✅ WHERE name = ? AND age = ?
• ✅ WHERE name = ? AND age = ? AND email = ?
• ❌ WHERE age = ? (跳过 name)

2. 覆盖索引

查询字段都在索引中，避免回表：

CREATE INDEX idx_cover ON orders(user_id, created_at);
-- 这个查询会使用覆盖索引
SELECT user_id, created_at FROM orders WHERE user_id = 1;

3. 避免索引失效

-- ❌ 对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 改为范围查询
SELECT * FROM users WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31";

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

-- ✅ 使用正确类型
SELECT * FROM users WHERE phone = "13800138000";

分析查询性能

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

关注字段：

• type: 访问类型 (ref > range > index > ALL)
• key: 实际使用的索引
• rows: 扫描行数
• Extra: 额外信息 (Using index 表示覆盖索引)

索引维护

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

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

-- 分析表优化
ANALYZE TABLE users;

常见陷阱

1. 过度索引：写操作变慢
2. 索引选择性低：区分度小的列不适合索引
3. 大字段索引：VARCHAR 过长影响性能

合理使用索引是数据库优化的核心技能！