查询优化

-- ❌ 慢查询（10秒）
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

-- ✅ 快查询（0.1秒）
SELECT * FROM orders 
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- ❌ 不好
SELECT * FROM users;

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

-- ❌ 慢
SELECT DISTINCT category FROM products;

-- ✅ 快
SELECT category FROM products GROUP BY category;

-- ❌ 查询所有
SELECT * FROM posts ORDER BY created_at DESC;

-- ✅ 只要前10条
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- ❌ 索引失效
SELECT * FROM users WHERE UPPER(username) = 'ADMIN';

-- ✅ 使用表达式索引或改写查询
SELECT * FROM users WHERE username = 'admin';

-- ❌ 子查询结果很大时很慢
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

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

-- ❌ N+1问题（在应用层）
-- 1. 查询所有文章
SELECT * FROM posts;
-- 2. 循环查询每个作者（执行N次）
SELECT * FROM users WHERE id = ?;

-- ✅ 使用JOIN一次查出
SELECT p.*, u.username 
FROM posts p 
JOIN users u ON p.user_id = u.id;

-- ❌ 循环插入
INSERT INTO logs VALUES (1, 'log1');
INSERT INTO logs VALUES (2, 'log2');
-- ... 1000次

-- ✅ 批量插入
INSERT INTO logs VALUES 
    (1, 'log1'),
    (2, 'log2'),
    -- ...
    (1000, 'log1000');

-- 小表在前，大表在后
SELECT * FROM small_table
JOIN large_table ON small_table.id = large_table.ref_id;

-- 在JOIN列上创建索引
CREATE INDEX idx_large_table_ref_id ON large_table(ref_id);

-- 减少JOIN的表数量
-- 如果不需要某个表的数据，就不要JOIN

-- ❌ 深分页很慢
SELECT * FROM posts 
ORDER BY id 
OFFSET 100000 LIMIT 10;  -- 需要跳过10万行

-- ✅ 使用WHERE代替OFFSET
SELECT * FROM posts 
WHERE id > 100000 
ORDER BY id 
LIMIT 10;

-- ✅ 或者记录上次的位置
SELECT * FROM posts 
WHERE id > :last_id 
ORDER BY id 
LIMIT 10;

-- ❌ COUNT(*)在大表上很慢
SELECT COUNT(*) FROM large_table;

-- ✅ 使用估算（适合不需要精确值的场景）
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'large_table';

-- ✅ 或者维护计数器表
CREATE TABLE counters (
    table_name VARCHAR(50),
    count BIGINT
);