数据库索引优化：为什么你的查询还是慢

建了索引查询还是慢？可能是这些常见陷阱在作祟。

索引失效的 6 大场景

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. 隐式类型转换

-- phone 是 VARCHAR 类型
-- ❌ 索引失效
SELECT * FROM users WHERE phone = 13800131234;

-- ✅ 加引号
SELECT * FROM users WHERE phone = '13800131234';

3. LIKE 以通配符开头

-- ❌ 全表扫描
SELECT * FROM users WHERE name LIKE '%张%';

-- ✅ 使用前缀匹配
SELECT * FROM users WHERE name LIKE '张%';

-- 或使用全文索引
SELECT * FROM users WHERE MATCH(name) AGAINST('张');

4. OR 条件使用不当

-- ❌ 如果 age 没有索引，整个查询全表扫描
SELECT * FROM users 
WHERE name = '张三' OR age = 25;

-- ✅ 用 UNION 改写
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

5. 复合索引不满足最左前缀

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

-- ✅ 可以使用索引
WHERE name = '张三'
WHERE name = '张三' AND age = 25
WHERE name = '张三' AND age = 25 AND email = '...'

-- ❌ 不能使用索引
WHERE age = 25
WHERE age = 25 AND email = '...'
WHERE name = '张三' AND email = '...'

6. != 或 <> 操作符

-- ❌ 可能导致索引失效
SELECT * FROM users WHERE status != 1;

-- ✅ 改写为 IN
SELECT * FROM users WHERE status IN (0, 2, 3);

索引设计原则

1. 选择高选择性列

选择性 = 不同值数量 / 总行数

-- 性别列不适合单独建索引（只有 2 个值）
-- 状态列视情况而定
-- 用户 ID、邮箱适合建索引

2. 复合索引列顺序

-- 原则：选择性高的列在前
-- 假设：name 选择性 > age 选择性

-- ✅ 更好的顺序
CREATE INDEX idx_name_age ON users(name, age);

-- 考虑查询模式
-- 如果大部分查询只按 age 过滤，则 (age, name) 更好

3. 覆盖索引

-- 查询
SELECT name, email FROM users WHERE age = 25;

-- 创建覆盖索引
CREATE INDEX idx_age_name_email ON users(age, name, email);

-- 无需回表，直接从索引获取数据

4. 前缀索引

-- 长字符串列
-- ✅ 使用前缀索引节省空间
CREATE INDEX idx_email_prefix ON users(email(20));

-- 检查前缀选择性
SELECT COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) 
FROM users;
-- > 0.9 则前缀索引效果好

索引维护

1. 定期分析索引使用

-- MySQL
SELECT * FROM sys.schema_unused_indexes;

-- PostgreSQL
SELECT * FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

2. 删除冗余索引

-- 这些索引冗余
idx1: (a)
idx2: (a, b)  -- 覆盖 idx1
idx3: (a, b, c)  -- 覆盖 idx1 和 idx2

-- 只保留 idx3

3. 监控慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒

-- 分析慢查询
mysqldumpslow -s t -t 10 slow.log

性能对比

场景	无索引	有索引	提升
100 万行精确查询	500ms	5ms	100 倍
范围查询	800ms	50ms	16 倍
ORDER BY	1200ms	30ms	40 倍

检查清单

• 查询条件列是否有索引
• 是否满足最左前缀原则
• 是否对索引列使用了函数
• 是否存在隐式类型转换
• 索引选择性是否足够高
• 是否有未使用的冗余索引
• 是否可以使用覆盖索引

索引是数据库优化的利器，但要用对地方才能发挥最大价值！