数据库索引优化：查询提速 100 倍

索引是数据库性能的关键。用对了提速百倍，用错了拖慢十倍。本文详解索引优化的核心要点。

索引原理简述

索引就像书的目录。没有索引时，数据库要全表扫描（翻完整本书）；有索引时，直接定位（查目录）。

什么时候该建索引

✅ 适合建索引的场景

1. 主键和外键 - 自动创建
2. WHERE 子句的列
3. JOIN 连接的列
4. ORDER BY 的列
5. GROUP BY 的列
6. 高选择性的列（不同值多）

❌ 不适合建索引的场景

1. 表太小（< 1000 行）
2. 频繁更新的列
3. 低选择性的列（如性别）
4. 长文本列（用全文索引）

B-Tree 索引（最常用）

-- 创建索引
CREATE INDEX idx_email ON users(email);

-- 复合索引（注意顺序！）
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;                -- ❌ 最左前缀原则

覆盖索引（性能最佳）

-- 索引包含查询的所有列
CREATE INDEX idx_email_name ON users(email, name);

-- 查询只从索引取数据，不查表
SELECT email, name FROM users WHERE email = "a@b.com";

优势：避免回表，速度极快。

索引失效的坑

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. 模糊查询

-- ❌ 索引失效
SELECT * FROM users WHERE name LIKE "%张%";

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

3. 类型转换

-- ❌ 索引失效（字符串转数字）
SELECT * FROM users WHERE phone = 13800138000;

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

4. OR 条件

-- ❌ 可能失效
SELECT * FROM users WHERE email = "a@b.com" OR name = "张三";

-- ✅ 用 UNION
SELECT * FROM users WHERE email = "a@b.com"
UNION
SELECT * FROM users WHERE name = "张三";

分析查询计划

-- 查看 SQL 如何使用索引
EXPLAIN SELECT * FROM users WHERE email = "a@b.com";

-- 关键字段
- type: system > const > eq_ref > ref > range > index > ALL
- key: 实际使用的索引
- rows: 扫描行数（越少越好）
- Extra: Using index（好）, Using filesort（差）

索引维护

-- 查看索引使用情况
SELECT 
    table_name,
    index_name,
    cardinallity,  -- 基数（不同值数量）
    seq_in_index   -- 列在索引中的位置
FROM information_schema.statistics
WHERE table_schema = "your_db";

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

-- 重建索引（碎片化后）
ALTER TABLE users ENGINE = InnoDB;

实战案例

问题：订单查询从 5 秒降到 50 毫秒

-- 原查询（5 秒）
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = "paid"
ORDER BY created_at DESC
LIMIT 20;

-- 优化：创建复合索引
CREATE INDEX idx_user_status_time 
ON orders(user_id, status, created_at);

-- 优化后：50 毫秒（100 倍提升）

总结

索引优化要点：

1. 为查询条件建索引
2. 遵循最左前缀原则
3. 避免索引失效操作
4. 用 EXPLAIN 分析
5. 定期维护清理

索引不是越多越好，合适的才是最好的。