SQL 性能优化：从索引到查询执行计划

索引优化

1. 选择合适的索引类型

-- B-Tree 索引：适合等值和范围查询
CREATE INDEX idx_user_email ON users(email);

-- 复合索引：注意最左前缀原则
CREATE INDEX idx_name_age ON users(name, age);
-- ✅ WHERE name = ? AND age = ?
-- ✅ WHERE name = ?
-- ❌ WHERE age = ?

-- 覆盖索引：避免回表
SELECT email FROM users WHERE name = "John";
-- 如果只有 (name, email) 复合索引，可直接从索引获取

2. 避免索引失效

-- ❌ 对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ✅ 改为范围查询
SELECT * FROM users WHERE created_at >= "2024-01-01";

-- ❌ 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 保持类型一致
SELECT * FROM users WHERE phone = "13800138000";

-- ❌ LIKE 以%开头
SELECT * FROM users WHERE name LIKE "%John%";
-- ✅ 使用前缀匹配
SELECT * FROM users WHERE name LIKE "John%";

查询优化

1. 只查询需要的列

-- ❌
SELECT * FROM users;

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

2. 优化 JOIN

-- 确保 JOIN 字段有索引
-- 小表驱动大表
-- 避免多表 JOIN，考虑拆分查询

3. 分页优化

-- ❌ 深度分页性能差
SELECT * FROM orders LIMIT 100000, 20;

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

执行计划分析

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

-- 关注字段:
-- type: ALL(全表) > index > range > ref > eq_ref > const > system
-- key: 实际使用的索引
-- rows: 扫描行数
-- Extra: Using index(好), Using temporary(注意), Using filesort(注意)

总结

SQL 优化需要结合索引设计、查询改写和执行计划分析。养成使用 EXPLAIN 的习惯，持续监控慢查询，才能保持数据库性能。