名称：查询优化器描述：分析和优化SQL查询以提高性能和效率。

查询优化器技能

分析和优化SQL查询以提高性能和效率。

指令

您是数据库性能优化专家。当调用时：

分析查询性能：
- 使用EXPLAIN/EXPLAIN ANALYZE理解执行计划
- 从日志中识别慢查询
- 测量查询执行时间
- 检测全表扫描和缺失索引
识别瓶颈：
- 查找N+1查询问题
- 检测低效JOINs
- 识别缺失或未使用的索引
- 发现次优WHERE子句
优化查询：
- 添加适当的索引
- 重写查询以提高性能
- 建议缓存策略
- 推荐查询重构
提供建议：
- 索引创建建议
- 查询重写替代方案
- 数据库配置调优
- 监控和警报设置

支持的数据库

SQL：PostgreSQL、MySQL、MariaDB、SQL Server、SQLite
分析工具：EXPLAIN、EXPLAIN ANALYZE、查询分析器
监控：pg_stat_statements、慢查询日志、性能模式

使用示例

@查询优化器
@查询优化器 --分析慢查询
@查询优化器 --建议索引
@查询优化器 --解释 SELECT * FROM users WHERE email = 'test@example.com'
@查询优化器 --修复N+1问题

查询分析工具

PostgreSQL - EXPLAIN ANALYZE

-- 基本EXPLAIN
EXPLAIN
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username;

-- EXPLAIN ANALYZE - 实际运行查询
EXPLAIN ANALYZE
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username;

-- EXPLAIN 带所有选项（PostgreSQL）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT * FROM orders
WHERE user_id = 123
  AND created_at >= '2024-01-01';

阅读EXPLAIN输出：

Seq Scan on users  (cost=0.00..1234.56 rows=10000 width=32)
  Filter: (active = true)

-- Seq Scan = 顺序扫描（全表扫描）- 对大表不好
-- cost=0.00..1234.56 = 启动成本..总成本
-- rows=10000 = 估计行数
-- width=32 = 平均行大小（字节）

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=32)
  Index Cond: (email = 'test@example.com'::text)

-- Index Scan = 使用索引 - 好
-- 成本远低于顺序扫描
-- rows=1 = 准确估计

MySQL - EXPLAIN

-- MySQL EXPLAIN
EXPLAIN
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username;

-- EXPLAIN 带执行统计（MySQL 8.0+）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123;

-- 显示警告以获取优化信息
EXPLAIN
SELECT * FROM users WHERE email = 'test@example.com';
SHOW WARNINGS;

MySQL EXPLAIN输出：

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL    | NULL    | NULL  | 1000 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

-- type=ALL 表示全表扫描 - 不好
-- key=NULL 表示未使用索引

+----+-------------+-------+------+---------------+----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key            | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | users | ref  | idx_users_email| idx_users_email| 767     | const |    1 | NULL  |
+----+-------------+-------+------+---------------+----------------+---------+-------+------+-------+

-- type=ref 表示索引查找 - 好
-- key 显示使用的索引

常见性能问题

1. 缺失索引

问题：

-- 慢查询 - 全表扫描
SELECT * FROM users WHERE email = 'john@example.com';

-- EXPLAIN 显示：
-- Seq Scan on users (cost=0.00..1500.00 rows=1 width=100)
--   Filter: (email = 'john@example.com')

解决方案：

-- 在email列上添加索引
CREATE INDEX idx_users_email ON users(email);

-- 现在EXPLAIN显示：
-- Index Scan using idx_users_email on users (cost=0.29..8.30 rows=1 width=100)
--   Index Cond: (email = 'john@example.com')

-- 查询变得快100倍

2. N+1查询问题

问题：

// ORM代码导致N+1查询
const users = await User.findAll(); // 1个查询

for (const user of users) {
  const orders = await Order.findAll({
    where: { userId: user.id }  // N个查询（每个用户一个）
  });
  console.log(`${user.name}: ${orders.length} orders`);
}

// 总计：1 + N个查询，N为用户数
// 对于100个用户 = 101个查询！

解决方案：

// 使用预加载 - 带JOIN的单个查询
const users = await User.findAll({
  include: [{
    model: Order,
    attributes: ['id', 'total_amount']
  }]
});

for (const user of users) {
  console.log(`${user.name}: ${user.orders.length} orders`);
}

// 总计：1个查询，与用户数无关

SQL等效：

-- 代替多个查询：
SELECT * FROM users;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
-- ...（N个更多查询）

-- 使用单个JOIN查询：
SELECT
  u.id,
  u.name,
  COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

3. SELECT * 效率低下

问题：

-- 获取所有列，但只需要少数几列
SELECT * FROM products
WHERE category_id = 5;

-- 获取：id、name、description（大文本）、image_url、specs（json）、
--         price、stock、created_at、updated_at等

解决方案：

-- 只选择需要的列
SELECT id, name, price, stock
FROM products
WHERE category_id = 5;

-- 好处：
-- - 传输数据更少
-- - 查询执行更快
-- - 内存使用更低
-- - 可以使用覆盖索引

4. 低效分页

问题：

-- OFFSET在大偏移时变慢
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- 数据库必须：
-- 1. 排序所有行
-- 2. 跳过10,000行
-- 3. 返回下20行
-- 随着偏移增加变慢

解决方案：

-- 使用基于游标（键集）的分页
SELECT * FROM users
WHERE created_at < '2024-01-01 12:00:00'
  AND (created_at < '2024-01-01 12:00:00' OR id < 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 或使用索引列：
SELECT * FROM users
WHERE id < 10000
ORDER BY id DESC
LIMIT 20;

-- 好处：
-- - 无论页面如何，性能一致
-- - 高效使用索引
-- - 无需跳过行

5. 在索引列上使用函数

问题：

-- 函数阻止索引使用
SELECT * FROM users
WHERE LOWER(email) = 'john@example.com';

-- EXPLAIN显示顺序扫描（索引未使用）

解决方案1 - 存储小写：

-- 添加计算列
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255)
  GENERATED ALWAYS AS (LOWER(email)) STORED;

CREATE INDEX idx_users_email_lower ON users(email_lower);

-- 查询：
SELECT * FROM users
WHERE email_lower = 'john@example.com';

解决方案2 - 函数索引（PostgreSQL）：

-- 在函数结果上创建索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 现在原始查询使用索引
SELECT * FROM users
WHERE LOWER(email) = 'john@example.com';

解决方案3 - 不区分大小写排序规则：

-- PostgreSQL - 使用citext类型
ALTER TABLE users ALTER COLUMN email TYPE citext;

-- 查询不带LOWER：
SELECT * FROM users WHERE email = 'john@example.com';
-- 自动不区分大小写

6. 低效JOINs

问题：

-- 多个JOINs没有适当索引
SELECT
  u.username,
  o.id as order_id,
  p.name as product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.email = 'john@example.com';

-- 如果缺少索引则慢：
-- - users.email
-- - orders.user_id
-- - order_items.order_id
-- - order_items.product_id

解决方案：

-- 添加必要索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 现在查询为所有JOINs使用索引
-- EXPLAIN将为每个表显示“Index Scan”

7. OR条件

问题：

-- OR阻止高效索引使用
SELECT * FROM users
WHERE username = 'john' OR email = 'john@example.com';

-- 可能无法最佳使用索引

解决方案：

-- 使用UNION以更好使用索引
SELECT * FROM users WHERE username = 'john'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

-- 每个子查询使用自己的索引
-- 自动去重结果

8. NOT IN带子查询

问题：

-- 慢子查询执行
SELECT * FROM users
WHERE id NOT IN (
  SELECT user_id FROM banned_users
);

-- 如果子查询结果大，可能非常慢

解决方案：

-- 使用LEFT JOIN带NULL检查
SELECT u.*
FROM users u
LEFT JOIN banned_users bu ON u.id = bu.user_id
WHERE bu.user_id IS NULL;

-- 或使用NOT EXISTS（通常更快）：
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM banned_users bu
  WHERE bu.user_id = u.id
);

索引优化

何时添加索引

添加索引用于：

主键（大多数数据库自动）
外键（对JOINs关键）
WHERE子句中的列
ORDER BY子句中的列
GROUP BY子句中的列
JOIN条件中的列
高基数列（许多唯一值）

索引类型

B树索引（默认）：

-- 最佳用于：等式（=）和范围（<、>、BETWEEN）查询
CREATE INDEX idx_users_created_at ON users(created_at);

-- 适用于：
SELECT * FROM users WHERE created_at > '2024-01-01';
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

复合索引：

-- 在多个列上索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 用于过滤两列的查询
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- 也用于仅第一列的查询
SELECT * FROM orders WHERE user_id = 123;

-- 不用于仅第二列的查询
SELECT * FROM orders WHERE status = 'pending'; -- 不会使用此索引

-- 列顺序重要！选择性最高的优先

部分索引（PostgreSQL）：

-- 仅索引行的子集
CREATE INDEX idx_active_users ON users(email)
WHERE active = true;

-- 更小的索引，对活跃用户查询更快
SELECT * FROM users WHERE email = 'john@example.com' AND active = true;

GIN索引（PostgreSQL - 用于数组、JSONB、全文）：

-- 用于JSONB列
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- 查询JSONB数据
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}';

-- 用于数组列
CREATE INDEX idx_tags ON posts USING GIN(tags);

-- 查询数组
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

全文搜索索引：

-- PostgreSQL
CREATE INDEX idx_products_search ON products
USING GIN(to_tsvector('english', name || ' ' || description));

-- 全文搜索查询
SELECT * FROM products
WHERE to_tsvector('english', name || ' ' || description)
  @@ to_tsquery('english', 'laptop & gaming');

覆盖索引

概念：

-- 覆盖索引包含查询所需的所有列
CREATE INDEX idx_users_email_username ON users(email, username);

-- 此查询可完全从索引回答（无需表访问）
SELECT username FROM users WHERE email = 'john@example.com';

-- PostgreSQL：仅索引扫描
-- MySQL：使用索引

带INCLUDE（PostgreSQL 11+）：

-- 在索引叶节点中包含非索引列
CREATE INDEX idx_users_email ON users(email)
INCLUDE (username, created_at);

-- 查询可使用索引而无需表访问
SELECT username, created_at
FROM users
WHERE email = 'john@example.com';

索引维护

查找未使用索引（PostgreSQL）：

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 删除未使用索引以节省空间并提高写入性能

查找重复索引：

-- PostgreSQL查询查找重复索引
SELECT
  indrelid::regclass AS table_name,
  array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

重建碎片化索引：

-- PostgreSQL
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- MySQL
OPTIMIZE TABLE users;

查询重写示例

示例1：聚合优化

之前：

SELECT
  u.id,
  u.username,
  (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count,
  (SELECT SUM(total_amount) FROM orders WHERE user_id = u.id) as total_spent
FROM users u
WHERE u.active = true;

-- N+1问题：1个查询 + 每个用户2个子查询

之后：

SELECT
  u.id,
  u.username,
  COUNT(o.id) as order_count,
  COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username;

-- 带JOIN的单个查询
-- 快得多！

示例2：EXISTS vs IN

之前：

SELECT * FROM products
WHERE id IN (
  SELECT product_id FROM order_items
  WHERE created_at >= '2024-01-01'
);

-- 子查询返回所有product_id（可能结果集大）

之后：

SELECT p.* FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.product_id = p.id
    AND oi.created_at >= '2024-01-01'
);

-- EXISTS在第一个匹配时停止（更高效）

示例3：避免笛卡尔积

之前：

-- 意外的笛卡尔积
SELECT *
FROM users u, orders o
WHERE u.active = true
  AND o.status = 'completed';

-- 返回每个用户与每个完成订单的组合！
-- 缺失JOIN条件

之后：

SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.active = true
  AND o.status = 'completed';

-- 适当的JOIN条件

示例4：优化DISTINCT

之前：

SELECT DISTINCT user_id
FROM orders
WHERE status = 'completed';

-- DISTINCT需要排序/去重

之后：

SELECT user_id
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

-- GROUP BY通常比DISTINCT快
-- 或如果存在唯一约束：
SELECT DISTINCT ON (user_id) user_id, created_at
FROM orders
WHERE status = 'completed'
ORDER BY user_id, created_at DESC;

监控慢查询

PostgreSQL - pg_stat_statements

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查找最慢查询
SELECT
  substring(query, 1, 50) AS short_query,
  round(total_exec_time::numeric, 2) AS total_time,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_time,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查找调用最多的查询
SELECT
  substring(query, 1, 50) AS short_query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- 重置统计
SELECT pg_stat_statements_reset();

MySQL - 慢查询日志

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录耗时>1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询日志
-- 使用mysqldumpslow工具：
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

性能模式（MySQL）

-- 启用性能模式
SET GLOBAL performance_schema = ON;

-- 查找最慢语句
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS executions,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

最佳实践

做 ✓

使用EXPLAIN 优化前和后
在外键上添加索引 - 对JOINs关键
使用覆盖索引 当可能时
分页大结果集 - 避免加载所有数据
监控查询性能 - 使用pg_stat_statements或慢查询日志
在类生产数据上测试 - 性能随数据量而异
使用连接池 - 减少连接开销
缓存频繁访问数据 - Redis、Memcached
归档旧数据 - 保持活动表更小
定期VACUUM/ANALYZE（PostgreSQL） - 更新统计

不做 ✗

**不要使用SELECT *** - 仅获取需要的列
不要过度索引 - 每个索引减慢写入
不要忽略EXPLAIN警告 - 它们指示问题
不要在索引列上使用函数 - 阻止索引使用
不要获取比需要更多的数据 - 使用LIMIT
不要使用OFFSET进行深度分页 - 使用基于游标的代替
不要忽略数据库日志 - 监控错误
不要过早优化 - 先分析，优化瓶颈
不要忘记写入性能 - 索引减慢INSERTs
不要跳过测试 - 验证优化实际帮助

查询优化检查清单

## 查询优化检查清单

- [ ] 对查询运行EXPLAIN/EXPLAIN ANALYZE
- [ ] 检查查询是否使用索引（大表上无顺序扫描）
- [ ] 验证索引存在于：
  - [ ] 外键列
  - [ ] WHERE子句列
  - [ ] JOIN条件列
  - [ ] ORDER BY列
- [ ] 仅选择需要的列（避免SELECT *）
- [ ] 使用适当的JOIN类型（INNER vs LEFT）
- [ ] 避免N+1查询（使用JOINs或预加载）
- [ ] 对大结果集使用分页
- [ ] 检查未使用索引（减慢写入）
- [ ] 考虑查询缓存用于频繁查询
- [ ] 在类生产数据量上测试
- [ ] 随时间监控查询性能

笔记

始终在优化前后测量
索引创建在大表上可能需要时间
太多索引减慢INSERT/UPDATE/DELETE
保持数据库统计最新（ANALYZE）
考虑读副本用于读重负载
使用数据库特定功能当有益时
记录优化决策供团队参考
定期性能审计防止退化

名称：查询优化器 描述：分析和优化SQL查询以提高性能和效率。

查询优化器技能

指令

支持的数据库

使用示例

查询分析工具

PostgreSQL - EXPLAIN ANALYZE

MySQL - EXPLAIN

常见性能问题

1. 缺失索引

2. N+1查询问题

3. SELECT * 效率低下

4. 低效分页

5. 在索引列上使用函数

6. 低效JOINs

7. OR条件

8. NOT IN带子查询

索引优化

何时添加索引

索引类型

覆盖索引

索引维护

查询重写示例

示例1：聚合优化

示例2：EXISTS vs IN

示例3：避免笛卡尔积

示例4：优化DISTINCT

监控慢查询

PostgreSQL - pg_stat_statements

MySQL - 慢查询日志

性能模式（MySQL）

最佳实践

做 ✓

不做 ✗

查询优化检查清单

笔记

名称：查询优化器描述：分析和优化SQL查询以提高性能和效率。