数据库查询优化的 8 个关键策略

1. 合理使用索引

索引创建原则

-- ✅ 适合创建索引的字段
- WHERE 子句中的字段
- JOIN 连接字段
- ORDER BY 排序字段
- GROUP BY 分组字段

-- ❌ 不适合创建索引
- 数据量小的表
- 频繁更新的字段
- 区分度低的字段 (如性别)

复合索引最左前缀

-- 索引：(name, age, city)
WHERE name = "John"              -- ✅ 使用索引
WHERE name = "John" AND age = 25 -- ✅ 使用索引
WHERE age = 25                   -- ❌ 不使用索引

2. 避免 SELECT *

-- ❌ 低效
SELECT * FROM users;

-- ✅ 高效
SELECT id, name, email FROM users;

好处:

• 减少网络传输
• 提高缓存命中率
• 可以使用覆盖索引

3. 优化 JOIN 操作

-- ✅ 确保连接字段有索引
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = "completed";

-- ✅ 小表驱动大表
SELECT * FROM small_table
INNER JOIN large_table ON small_table.id = large_table.ref_id;

4. 避免函数操作索引列

-- ❌ 索引失效
WHERE YEAR(created_at) = 2024;
WHERE UPPER(name) = "JOHN";

-- ✅ 保持索引有效
WHERE created_at >= "2024-01-01" 
  AND created_at < "2025-01-01";
WHERE name = "John"; -- 存储时统一大小写

5. 使用 EXISTS 代替 IN

-- ❌ IN 可能低效 (子查询全执行)
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- ✅ EXISTS 找到即停止
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id AND o.total > 100
);

6. 分页优化

-- ❌ 大数据量分页慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- ✅ 使用子查询或延迟关联
SELECT * FROM orders
INNER JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON orders.id = tmp.id;

-- ✅ 或使用游标分页
SELECT * FROM orders
WHERE id > last_seen_id
ORDER BY id LIMIT 20;

7. 批量操作

-- ❌ 逐条插入 (1000 次网络往返)
INSERT INTO users (name) VALUES ("user1");
INSERT INTO users (name) VALUES ("user2");
...

-- ✅ 批量插入 (1 次网络往返)
INSERT INTO users (name) VALUES 
  ("user1"), ("user2"), ("user3"), ...;

8. 使用 EXPLAIN 分析

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

关注要点:

• type: 访问类型 (const > eq_ref > ref > range > index > ALL)
• key: 实际使用的索引
• rows: 扫描行数
• Extra: 额外信息 (Using index, Using temporary, Using filesort)

监控与维护

1. 慢查询日志: 记录执行时间超过阈值的查询
2. 定期 ANALYZE: 更新统计信息
3. 监控索引使用率: 删除未使用的索引
4. 表结构优化: 适当分表、分区

总结

查询优化是持续过程。理解执行计划，针对性优化，监控效果，形成闭环。