查询优化：常见技巧与最佳实践

索引优化

1. 避免函数包裹列

-- 不使用索引
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- 使用索引
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

2. 使用覆盖索引

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

-- 只访问索引，不访问表
SELECT email, name FROM users WHERE email = 'alice@example.com';

3. 避免前导通配符

-- 不使用索引
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 使用索引
SELECT * FROM users WHERE email LIKE 'alice%';

查询优化

4. 避免 SELECT *

-- 浪费资源
SELECT * FROM users WHERE id = 1;

-- 只查询需要的列
SELECT id, name, email FROM users WHERE id = 1;

5. 使用 LIMIT

-- 限制返回行数
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

6. EXISTS 代替 IN（大数据集）

-- IN（子查询返回大量数据时慢）
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- EXISTS（更快）
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

JOIN 优化

7. 小表驱动大表

-- 让小表在前
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;

8. 索引 JOIN 列

-- 确保 JOIN 条件列有索引
CREATE INDEX idx_order_user_id ON orders(user_id);
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

子查询优化

9. 避免相关子查询

-- 慢（每行执行一次子查询）
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- 快（JOIN + GROUP BY）
SELECT u.name, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

分页优化

10. 避免 OFFSET 大数值

-- 慢（跳过前 10000 行）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;

-- 快（使用上一页最大 ID）
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 10;

批量操作

11. 批量插入

-- 慢（多次事务）
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- 快（一次事务）
BEGIN;
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
COMMIT;

12. 批量更新

-- 使用临时表
CREATE TEMP TABLE updates (id INT, new_value INT);
INSERT INTO updates VALUES (1, 100), (2, 200);

UPDATE orders o
SET amount = u.new_value
FROM updates u
WHERE o.id = u.id;

数据类型优化

13. 使用合适的数据类型

-- 不好
status VARCHAR(255)  -- 浪费空间

-- 好
status VARCHAR(20)  -- 或 ENUM

14. 避免 NULL

-- NULL 影响索引效率
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

缓存与连接池

15. 应用层缓存

# Redis 缓存热点数据
user = redis.get(f"user:{id}")
if not user:
    user = db.query("SELECT * FROM users WHERE id = %s", id)
    redis.set(f"user:{id}", user, ex=3600)

16. 使用连接池

# 连接池避免频繁创建连接
pool = psycopg2.pool.SimpleConnectionPool(10, 20, dbname="mydb")
conn = pool.getconn()

监控与分析

17. 慢查询日志

-- PostgreSQL
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 记录 > 1s 的查询

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

18. 定期维护

-- PostgreSQL
VACUUM ANALYZE;

-- MySQL
OPTIMIZE TABLE users;

最佳实践总结

原则	说明
只查询需要的列	避免 SELECT *
使用索引	WHERE、JOIN、ORDER BY 列
避免函数	不要包裹索引列
批量操作	减少往返次数
合适的数据类型	节省空间和提高性能
监控慢查询	定期分析和优化

练习

1. 优化使用函数的查询
2. 创建覆盖索引并验证
3. 将 IN 改写为 EXISTS
4. 优化分页查询（避免大 OFFSET）
5. 批量插入 10000 行数据
6. 优化相关子查询
7. 配置慢查询日志
8. 分析实际项目的慢查询并优化

完成练习后，打开本章答案页核对。

本章小结

第六章学习了索引与性能优化：

• 索引类型：B-Tree、Hash、GiST、GIN
• 索引管理：创建、删除、维护
• EXPLAIN：分析查询计划
• 查询优化：18 条实用技巧

第七章将学习数据定义与约束。

回到第六章概览 · 返回SQL 学习路线