SQL 查询优化

SQL 查询优化是提高数据库性能的关键，涉及索引优化、查询重写等技术。

索引优化

创建索引

-- 创建单列索引
CREATE INDEX idx_users_name ON users(name);

-- 创建复合索引
CREATE INDEX idx_users_name_age ON users(name, age);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 创建部分索引
CREATE INDEX idx_users_active ON users(id) WHERE status = 'active';

索引使用原则

-- 避免在索引列上使用函数
-- 不好的写法
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 好的写法
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- 避免在索引列上进行计算
-- 不好的写法
SELECT * FROM orders WHERE total * 1.1 > 1000;

-- 好的写法
SELECT * FROM orders WHERE total > 1000 / 1.1;

查询重写

避免 SELECT *

-- 不好的写法
SELECT * FROM users WHERE id = 1;

-- 好的写法
SELECT id, name, email FROM users WHERE id = 1;

使用 JOIN 代替子查询

-- 子查询
SELECT name FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- JOIN
SELECT u.name FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

使用 UNION ALL 代替 UNION

-- UNION（会去重，性能较差）
SELECT name FROM users WHERE status = 'active'
UNION
SELECT name FROM admins WHERE status = 'active';

-- UNION ALL（不去重，性能较好）
SELECT name FROM users WHERE status = 'active'
UNION ALL
SELECT name FROM admins WHERE status = 'active';

查询执行计划

查看执行计划

-- MySQL
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'John';

-- SQL Server
SET SHOWPLAN_XML ON;
SELECT * FROM users WHERE name = 'John';

执行计划解读

-- 常见的执行计划操作
-- ALL: 全表扫描（性能最差）
-- index: 索引扫描
-- range: 索引范围扫描
-- ref: 非唯一索引扫描
-- eq_ref: 唯一索引扫描
-- const: 常量查找（性能最好）

分区表

-- 创建分区表（按日期分区）
CREATE TABLE orders (
    id INT,
    order_date DATE,
    total DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

缓存优化

-- 启用查询缓存（MySQL）
SET query_cache_type = ON;

-- 缓存特定查询
SELECT SQL_CACHE * FROM products WHERE category = 'electronics';

-- 不缓存特定查询
SELECT SQL_NO_CACHE * FROM orders WHERE status = 'pending';

分页优化

-- 不好的写法（偏移量大时性能差）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 好的写法（使用索引列进行分页）
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 100000, 1) 
ORDER BY id LIMIT 10;

注意事项

1. 索引不是越多越好: 过多的索引会影响写入性能
2. 定期分析表: 保持统计信息最新
3. 避免隐式转换: 保持数据类型一致
4. 使用连接池: 减少连接开销