411_SQL查询优化与执行计划¶

-- ========== PostgreSQL环境 ==========

-- ========== 1. 查看执行计划（EXPLAIN ANALYZE）==========
-- 先用EXPLAIN看估算计划，再用EXPLAIN ANALYZE看实际执行
EXPLAIN ANALYZE
SELECT g.gene_id, g.gene_name, e.expression
FROM genes g
JOIN gene_expression e ON g.gene_id = e.gene_id
WHERE e.condition = 'treatment'
  AND e.expression > 100;

-- 输出解读示例：
-- Seq Scan on gene_expression  (cost=0.00..4521.00 rows=1500 width=32)
--   Filter: (expression > 100 AND condition = 'treatment')
-- -> 说明在全表扫描，应该加索引！

-- Hash Join  (cost=85.50..892.30 rows=450 width=48)
--   Hash Cond: (e.gene_id = g.gene_id)
-- -> Hash Join说明两表都较大，用哈希连接

-- ========== 2. 创建合适的索引 ==========
-- 单列索引：经常用于WHERE过滤的列
CREATE INDEX idx_expression_condition 
ON gene_expression (condition);  -- 经常按condition过滤

-- 复合索引：多列一起用于过滤时，顺序很重要（选择性高的放前面）
CREATE INDEX idx_expr_condition_value 
ON gene_expression (condition, expression);  -- WHERE condition='x' AND expression>y
-- 注意：复合索引 (condition, expression) 可用于 WHERE condition='x'
-- 但不能用于单独 WHERE expression>y（最左前缀原则！）

-- 覆盖索引（Index Only Scan）：查询列都在索引中，不用回表
CREATE INDEX idx_covering 
ON gene_expression (condition, expression) 
INCLUDE (gene_id, sample_id);  -- INCLUDE列不参与排序，但加入索引存储
-- SELECT gene_id, sample_id WHERE condition='x' AND expression>y
-- 可以只扫索引，不访问原表

-- 部分索引：只索引满足条件的行（节省空间）
CREATE INDEX idx_high_expression 
ON gene_expression (gene_id) 
WHERE expression > 1000;  -- 只索引高表达的行

-- ========== 3. 常见反模式与修复 ==========
-- ❌ 反模式1：WHERE中对列使用函数（导致无法走索引）
SELECT * FROM samples WHERE UPPER(sample_name) = 'S001';
-- ✅ 修复：在插入时就统一大小写，或建函数索引
CREATE INDEX idx_upper_name ON samples (UPPER(sample_name));

-- ❌ 反模式2：WHERE列隐式类型转换
SELECT * FROM gene_expression WHERE gene_id = 12345;  -- gene_id是VARCHAR但传了INT
-- ✅ 修复：确保类型一致
SELECT * FROM gene_expression WHERE gene_id = '12345';  -- 加引号

-- ❌ 反模式3：OR条件（可能导致索引失效）
SELECT * FROM gene_expression WHERE condition = 'T1' OR condition = 'T2';
-- ✅ 修复：用IN替代
SELECT * FROM gene_expression WHERE condition IN ('T1', 'T2');

-- ❌ 反模式4：不必要的SELECT *
SELECT * FROM gene_expression WHERE condition = 'treatment';
-- ✅ 修复：只选需要的列
SELECT gene_id, expression FROM gene_expression WHERE condition = 'treatment';

-- ❌ 反模式5：对大表不加LIMIT
SELECT * FROM gene_expression;  -- 可能有百万行
-- ✅ 修复：加LIMIT
SELECT * FROM gene_expression LIMIT 1000 OFFSET 0;

-- ========== 4. JOIN优化 ==========
-- ❌ 低效：在大表上做笛卡尔积后过滤
SELECT * FROM gene_expression e, samples s
WHERE e.sample_id = s.sample_id;  -- 老式写法，容易遗漏条件变成笛卡尔积

-- ✅ 高效：明确写JOIN，思路清晰
SELECT e.gene_id, e.expression, s.patient_id
FROM gene_expression e
INNER JOIN samples s ON e.sample_id = s.sample_id  -- 明确连接条件
WHERE e.condition = 'treatment';

-- 确保JOIN列有索引（非常重要！）
CREATE INDEX idx_expression_sample ON gene_expression (sample_id);
CREATE INDEX idx_samples_id ON samples (sample_id);

-- ========== 5. 更新统计信息（解决估算偏差问题）==========
-- 当执行计划中 rows估算与actual rows差距很大时，需要更新统计信息
ANALYZE gene_expression;  -- 更新单表统计信息
ANALYZE;                  -- 更新所有表统计信息（耗时）

-- ========== 6. 实用：找出慢查询 ==========
-- PostgreSQL：查询慢查询日志（需开启pg_stat_statements扩展）
SELECT 
    query,
    calls,                                          -- 执行次数
    ROUND(total_exec_time / calls, 2) AS avg_ms,   -- 平均执行时间（毫秒）
    rows / calls AS avg_rows                        -- 平均返回行数
FROM pg_stat_statements
ORDER BY avg_ms DESC  -- 按平均执行时间排序
LIMIT 10;             -- 取最慢的10个查询

-- 查看执行计划
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;  -- 实际执行并显示真实统计

-- 创建索引
CREATE INDEX idx_name ON table (col);
CREATE INDEX idx_name ON table (col1, col2);  -- 复合索引
DROP INDEX idx_name;

-- 查看索引使用情况（PostgreSQL）
SELECT indexname, idx_scan, idx_tup_read FROM pg_stat_user_indexes;

-- 强制使用/禁用索引（PostgreSQL调试用）
SET enable_seqscan = off;   -- 禁用全表扫描（强制用索引）
SET enable_seqscan = on;    -- 恢复默认