SQL查询优化

概览

分析SQL查询以识别性能瓶颈并实施优化技术。包括查询分析、索引策略和重写模式以提高性能。

使用场景

慢查询分析和调整
查询重写和重构
索引利用验证
连接优化
子查询优化
查询计划分析（EXPLAIN）
性能基线建立

查询分析框架

1. 分析当前性能

PostgreSQL:

-- 使用执行时间分析查询计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '1 year'
GROUP BY u.id, u.email;

-- 检查表统计信息
SELECT * FROM pg_stats
WHERE tablename = 'users' AND attname = 'created_at';

MySQL:

-- 分析查询计划
EXPLAIN FORMAT=JSON
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY u.id, u.email;

-- 检查表大小
SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.tables WHERE table_schema = 'database_name';

2. 常见优化模式

PostgreSQL - 索引优化:

-- 为频繁过滤的列创建索引
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC)
WHERE status != 'cancelled';

-- 部分索引用于过滤查询
CREATE INDEX idx_active_products
ON products(category_id)
WHERE active = true;

-- 多列覆盖索引
CREATE INDEX idx_users_email_verified_covering
ON users(email, verified)
INCLUDE (id, name, created_at);

MySQL - 索引优化:

-- 为多列过滤创建复合索引
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- 使用FULLTEXT索引进行文本搜索
CREATE FULLTEXT INDEX idx_products_search
ON products(name, description);

-- 大型VARCHAR的前缀索引
CREATE INDEX idx_large_text
ON large_table(text_column(100));

3. 查询重写技术

PostgreSQL - 窗口函数:

-- 低效：多次遍历
SELECT p.id, p.name,
  (SELECT COUNT(*) FROM orders o WHERE o.product_id = p.id) as order_count,
  (SELECT SUM(quantity) FROM order_items oi WHERE oi.product_id = p.id) as total_sold
FROM products p;

-- 优化：单次遍历使用窗口函数
SELECT DISTINCT p.id, p.name,
  COUNT(*) OVER (PARTITION BY p.id) as order_count,
  SUM(oi.quantity) OVER (PARTITION BY p.id) as total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id;

MySQL - JOIN优化:

-- 低效：聚合后JOIN
SELECT user_id, name, total_orders
FROM (
  SELECT u.id as user_id, u.name, COUNT(o.id) as total_orders
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
) subquery
WHERE total_orders > 5;

-- 优化：使用HAVING子句聚合
SELECT u.id, u.name, COUNT(o.id) as total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5;

4. 批量操作

PostgreSQL - 批量插入:

-- 低效：多次往返
INSERT INTO users (email, name) VALUES ('user1@example.com', 'User One');
INSERT INTO users (email, name) VALUES ('user2@example.com', 'User Two');

-- 优化：单次批量
INSERT INTO users (email, name) VALUES
  ('user1@example.com', 'User One'),
  ('user2@example.com', 'User Two'),
  ('user3@example.com', 'User Three')
ON CONFLICT (email) DO UPDATE SET updated_at = NOW();

MySQL - 批量更新:

-- 优化：使用VALUES子句批量更新
UPDATE products p
JOIN (
  SELECT id, price FROM product_updates
) AS updates ON p.id = updates.id
SET p.price = updates.price;

性能监控

PostgreSQL - 长时间运行的查询:

-- 查找慢查询
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 重置统计信息
SELECT pg_stat_statements_reset();

MySQL - 慢查询日志:

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看慢查询
SELECT * FROM mysql.slow_log
ORDER BY start_time DESC LIMIT 10;

优化检查清单

使用EXPLAIN/EXPLAIN ANALYZE优化前后
在WHERE、JOIN和ORDER BY子句中的列上添加索引
在探索大型结果集时使用LIMIT
仅需要特定列时避免SELECT *
使用数据库函数代替应用级处理
批量操作以减少网络往返
对大型表进行分区以提高查询性能
定期使用ANALYZE更新统计信息

常见陷阱

❌ 不要在测试影响之前创建索引 ❌ 不要在没有全文搜索的情况下使用带有前导通配符的LIKE ❌ 不要连接不必要的表 ❌ 不要忽略ORDER BY的性能影响 ❌ 不要跳过EXPLAIN分析

✅ 测试开发中的查询更改 ✅ 部署后监控查询性能 ✅ 定期更新表统计信息 ✅ 使用适当的列数据类型 ✅ 考虑使用物化视图进行复杂聚合

SQL查询优化 sql-query-optimization