sql-optimization

SQL 优化与调优

概述

慢查询分析、执行计划、索引优化等通用 SQL 优化技能。

执行计划分析

MySQL EXPLAIN

-- 基础执行计划
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- JSON 格式
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]';

-- 关键字段解读
-- type: 访问类型 (system > const > eq_ref > ref > range > index > ALL)
-- key: 使用的索引
-- rows: 预估扫描行数
-- Extra: 额外信息 (Using index, Using filesort, Using temporary)

PostgreSQL EXPLAIN

-- 基础执行计划
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 实际执行
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- 详细信息
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = '[email protected]';

-- 关键指标
-- Seq Scan: 全表扫描
-- Index Scan: 索引扫描
-- Bitmap Index Scan: 位图索引扫描
-- actual time: 实际执行时间
-- rows: 实际返回行数

索引优化

索引设计原则

-- 1. 选择性高的列优先
-- 选择性 = 不同值数量 / 总行数
SELECT COUNT(DISTINCT column) / COUNT(*) AS selectivity FROM table;

-- 2. 复合索引列顺序
-- 遵循最左前缀原则
-- 将选择性高的列放前面
CREATE INDEX idx_user ON users(status, created_at, name);

-- 3. 覆盖索引
-- 索引包含查询所需的所有列
CREATE INDEX idx_covering ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

-- 4. 前缀索引（长字符串）
CREATE INDEX idx_email ON users(email(20));

索引使用检查

-- MySQL: 查看索引使用情况
SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'mydb';

-- MySQL: 未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- PostgreSQL: 索引使用统计
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;

索引失效场景

-- 1. 函数操作
-- 错误
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 正确
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 2. 隐式类型转换
-- 错误 (phone 是 varchar)
SELECT * FROM users WHERE phone = 13800138000;
-- 正确
SELECT * FROM users WHERE phone = '13800138000';

-- 3. LIKE 前缀通配符
-- 错误
SELECT * FROM users WHERE name LIKE '%john%';
-- 正确
SELECT * FROM users WHERE name LIKE 'john%';

-- 4. OR 条件
-- 可能不走索引
SELECT * FROM users WHERE status = 1 OR name = 'john';
-- 改写为 UNION
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE name = 'john';

-- 5. NOT IN / NOT EXISTS
-- 尽量避免，改用 LEFT JOIN
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
-- 改写
SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;