SQL 性能优化：索引、查询与执行计划详解

索引优化

索引类型

• B-Tree: 默认索引，适合范围查询
• Hash: 等值查询，O(1) 复杂度
• 全文索引: 文本搜索
• 复合索引: 多列组合

索引最佳实践

-- ✅ 好：选择性高的列
CREATE INDEX idx_email ON users(email);

-- ✅ 好：复合索引注意顺序
CREATE INDEX idx_last_first ON users(last_name, first_name);

-- ❌ 避免：低选择性列
CREATE INDEX idx_gender ON users(gender);  -- 只有男女两个值

索引失效场景

-- 函数操作导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 类型转换导致失效
SELECT * FROM orders WHERE order_id = "123";  -- order_id 是 INT

-- LIKE 前缀通配符
SELECT * FROM products WHERE name LIKE "%phone%";

查询优化

避免 SELECT *

-- ❌ 差
SELECT * FROM users;

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

使用 LIMIT

SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

优化 JOIN

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

避免子查询

-- ❌ 差
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

-- ✅ 好
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1;

执行计划分析

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

关注：

• type: 访问类型（ALL > index > range > ref > const）
• rows: 预估扫描行数
• Extra: 额外信息（Using index 表示覆盖索引）

慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = "ON";
SET GLOBAL long_query_time = 2;  -- 超过 2 秒记录

SQL 优化是数据库性能的关键，合理使用索引和编写高效查询能带来数量级的性能提升。