索引与性能优化

-- 创建一张大表来演示索引效果
CREATE TABLE big_orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_name VARCHAR(100),
    amount NUMERIC(10,2),
    status VARCHAR(10),
    order_date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入 100 万条数据
INSERT INTO big_orders (user_id, product_name, amount, status, order_date)
SELECT 
    (random() * 100000)::INT + 1,
    CASE (random() * 5)::INT
        WHEN 0 THEN '笔记本电脑'
        WHEN 1 THEN '手机'
        WHEN 2 THEN '耳机'
        WHEN 3 THEN '运动鞋'
        ELSE 'T恤'
    END,
    (random() * 5000 + 100)::NUMERIC(10,2),
    CASE (random() * 4)::INT
        WHEN 0 THEN '已完成'
        WHEN 1 THEN '已取消'
        WHEN 2 THEN '退款中'
        ELSE '已完成'
    END,
    DATE '2024-01-01' + (random() * 730)::INT
FROM generate_series(1, 1000000);

-- 确认数据已插入
SELECT count(*) FROM big_orders;

-- 查看查询计划
EXPLAIN SELECT * FROM big_orders WHERE user_id = 42;

EXPLAIN ANALYZE SELECT * FROM big_orders WHERE user_id = 42;

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_orders WHERE user_id = 42;

-- 创建 B-Tree 索引（PostgreSQL 默认）
CREATE INDEX idx_orders_user_id ON big_orders(user_id);

-- 创建多列索引（复合索引）
CREATE INDEX idx_orders_user_date ON big_orders(user_id, order_date);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_orders_unique ON orders(order_id);

-- 先清缓存并做无索引查询
\timing on

-- 全表扫描（应该在无索引时运行）
SELECT count(*) FROM big_orders WHERE user_id > 50000 AND user_id < 50010;

-- 创建索引后重新查询
CREATE INDEX idx_big_orders_user ON big_orders(user_id);
SELECT count(*) FROM big_orders WHERE user_id > 50000 AND user_id < 50010;

-- 复合索引的列顺序非常重要！
CREATE INDEX idx_orders_user_status ON big_orders(user_id, status);

-- 这个查询能用上索引（前导列 user_id 匹配）
EXPLAIN ANALYZE SELECT * FROM big_orders 
WHERE user_id = 42 AND status = '已完成';

-- 这个查询可能用不上索引（没有前导列 user_id）
EXPLAIN ANALYZE SELECT * FROM big_orders 
WHERE status = '已完成';

-- 只索引活跃用户（status = 'active' 的只占一小部分）
CREATE INDEX idx_users_active ON users(status) WHERE status = 'active';

-- 只索引已完成的订单（用于统计报表查询）
CREATE INDEX idx_orders_completed ON orders(order_date, total_amount) 
WHERE status = '已完成';

-- 如果经常这样查询
SELECT user_id, order_date, status FROM big_orders 
WHERE user_id BETWEEN 100 AND 200;

-- 创建覆盖索引（包含查询需要的所有列）
CREATE INDEX idx_orders_covering ON big_orders(user_id, order_date, status);

-- 查询计划会显示 "Index Only Scan"
EXPLAIN ANALYZE SELECT user_id, order_date, status 
FROM big_orders WHERE user_id BETWEEN 100 AND 200;

-- 确保两列组合唯一
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 经常按小写查询邮箱
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 查询时必须使用同样的表达式
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- 只允许一条活跃记录有某个邮箱（软删除场景）
CREATE UNIQUE INDEX idx_users_active_email 
ON users(email) WHERE status = 'active';

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('big_orders')) AS 表大小;

-- 查看索引大小
SELECT pg_size_pretty(pg_indexes_size('big_orders')) AS 索引大小;

-- 查找未被使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS 使用次数
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 查看慢查询（需要启用 pg_stat_statements）
SELECT 
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 对布尔列建索引效果不佳（只有 true/false）
CREATE INDEX idx_orders_status ON big_orders(status); 
-- 如果 status 只有 3 个值，这个索引帮助有限

-- ❌ 无法使用索引
SELECT * FROM big_orders WHERE EXTRACT(YEAR FROM order_date) = 2025;

-- ✅ 可以使用索引
SELECT * FROM big_orders 
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

-- ❌ 无法使用普通索引
SELECT * FROM products WHERE name LIKE '%手机%';

-- ✅ 可以使用索引（前缀匹配）
SELECT * FROM products WHERE name LIKE '手机%';

-- 全文搜索场景用 gin 索引

-- 查看关键配置
SHOW shared_buffers;         -- 共享缓冲区（标准建议：25% 内存）
SHOW work_mem;              -- 排序/哈希内存（每个查询）
SHOW maintenance_work_mem;  -- 维护操作（如 VACUUM、CREATE INDEX）
SHOW effective_cache_size;  -- 系统缓存估算值
SHOW random_page_cost;      -- 随机 I/O 成本

-- 查看表的膨胀和死元组
SELECT 
    schemaname,
    tablename,
    n_dead_tup AS 死元组数,
    n_live_tup AS 活跃元组数,
    last_autovacuum AS 上次自动清理时间
FROM pg_stat_user_tables
WHERE tablename = 'big_orders';

-- 手动清理无用的死元组
VACUUM big_orders;

-- 清理并回收空间
VACUUM FULL big_orders;

-- 更新统计信息（让查询计划更准确）
ANALYZE big_orders;