数据库索引优化：让查询快 10 倍的秘诀

索引的工作原理

索引就像书的目录，帮助数据库快速定位数据，避免全表扫描。

1. B+ 树索引

MySQL InnoDB 使用 B+ 树结构：

• 数据有序存储
• 支持范围查询
• 树高度通常 3-4 层

2. 创建索引的原则

✅ 应该创建索引的场景

-- 主键和外键（自动创建）
ALTER TABLE users ADD PRIMARY KEY (id);

-- 高频查询的 WHERE 条件
CREATE INDEX idx_email ON users(email);

-- ORDER BY 和 GROUP BY 字段
CREATE INDEX idx_created ON orders(created_at);

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

❌ 避免创建索引的场景

• 表数据量很小（< 1000 行）
• 频繁更新的字段
• 区分度低的字段（如性别）
• 重复创建相同索引

3. 最左前缀原则

联合索引 (a, b, c) 的使用规则：

-- ✅ 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ❌ 无法使用完整索引
WHERE b = 2              -- 跳过 a
WHERE a = 1 AND c = 3    -- 跳过 b

4. 覆盖索引

查询字段都在索引中，无需回表：

-- 索引：idx_name_age (name, age)
-- ✅ 覆盖索引
SELECT name, age FROM users WHERE name = "John";

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

5. 索引失效的陷阱

-- ❌ 函数导致索引失效
WHERE YEAR(created_at) = 2024

-- ✅ 改写
WHERE created_at >= "2024-01-01" 
  AND created_at < "2025-01-01"

-- ❌ 模糊查询开头通配符
WHERE name LIKE "%John%"

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

-- ❌ 类型隐式转换
WHERE phone = 13800138000  -- phone 是字符串

-- ✅ 保持类型一致
WHERE phone = "13800138000"

6. 分析查询性能

-- 使用 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(需优化)

7. 维护索引

-- 查看索引碎片
SELECT table_name, data_free FROM information_schema.tables;

-- 优化表（重建索引）
OPTIMIZE TABLE users;

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

总结

索引是双刃剑：加速查询但拖慢写入。根据实际查询模式设计索引，定期分析优化！