名称: 查询专家描述: 掌握SQL和NoSQL系统的数据库查询。生成优化的查询，使用EXPLAIN计划分析性能，设计有效索引，并解决慢查询问题。

查询专家

掌握SQL和NoSQL系统的数据库查询。生成优化的查询，使用EXPLAIN计划分析性能，设计有效索引，并解决慢查询问题。

技能功能

帮助您编写高效、性能良好的数据库查询：

生成查询 - SQL、MongoDB、GraphQL查询
优化查询 - 性能调整和重构
设计索引 - 快速查询的索引策略
分析性能 - EXPLAIN计划和查询分析
故障排除 - 调试慢查询和瓶颈
最佳实践 - 查询模式和反模式

支持的数据库

SQL数据库

PostgreSQL - 高级功能、CTE、窗口函数
MySQL/MariaDB - InnoDB优化、复制
SQLite - 嵌入式数据库优化
SQL Server - T-SQL、执行计划、DMV
Oracle - PL/SQL、分区、提示

NoSQL数据库

MongoDB - 聚合管道、索引
Redis - 键值查询、Lua脚本
Elasticsearch - 全文搜索查询
Cassandra - CQL、分区键

查询语言

SQL - 标准和供应商特定
MongoDB查询语言 - 查找、聚合
GraphQL - 高效数据获取
Cypher - Neo4j图查询

SQL查询模式

SELECT查询

基础SELECT

-- ✅ 仅选择需要的列
SELECT
    user_id,
    email,
    created_at
FROM users
WHERE status = 'active'
    AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;

-- ❌ 避免SELECT *
SELECT * FROM users;  -- 浪费资源

JOIN操作

-- INNER JOIN（最常见）
SELECT
    o.order_id,
    o.total,
    c.name AS customer_name,
    c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at >= '2024-01-01';

-- LEFT JOIN（包含所有左表行）
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- 多个JOIN
SELECT
    o.order_id,
    c.name AS customer_name,
    p.product_name,
    oi.quantity,
    oi.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed';

子查询

-- WHERE中的子查询
SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total > 1000
);

-- 相关子查询
SELECT
    c.name,
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

-- ✅ 更好的做法：使用JOIN替代
SELECT
    c.name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

聚合操作

-- GROUP BY与聚合函数
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    SUM(stock_quantity) AS total_stock
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

-- 多个GROUP BY列
SELECT
    DATE_TRUNC('month', created_at) AS month,
    category,
    SUM(total) AS monthly_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at), category
ORDER BY month DESC, monthly_sales DESC;

-- ROLLUP用于小计
SELECT
    COALESCE(category, '总计') AS category,
    COALESCE(brand, '所有品牌') AS brand,
    SUM(sales) AS total_sales
FROM products
GROUP BY ROLLUP(category, brand);

窗口函数（PostgreSQL、SQL Server、MySQL 8+）

-- ROW_NUMBER
SELECT
    customer_id,
    order_date,
    total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS order_rank
FROM orders;

-- 累积总和
SELECT
    order_date,
    total,
    SUM(total) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- RANK与DENSE_RANK
SELECT
    product_name,
    sales,
    RANK() OVER (ORDER BY sales DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank,
    NTILE(4) OVER (ORDER BY sales DESC) AS quartile
FROM products;

-- LAG和LEAD
SELECT
    order_date,
    total,
    LAG(total, 1) OVER (ORDER BY order_date) AS prev_total,
    LEAD(total, 1) OVER (ORDER BY order_date) AS next_total,
    total - LAG(total, 1) OVER (ORDER BY order_date) AS change
FROM orders;

CTE（公共表表达式）

-- 简单CTE
WITH active_customers AS (
    SELECT customer_id, name, email
    FROM customers
    WHERE status = 'active'
)
SELECT
    ac.name,
    COUNT(o.order_id) AS order_count
FROM active_customers ac
LEFT JOIN orders o ON ac.customer_id = o.customer_id
GROUP BY ac.customer_id, ac.name;

-- 多个CTE
WITH
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
avg_monthly AS (
    SELECT AVG(sales) AS avg_sales
    FROM monthly_sales
)
SELECT
    ms.month,
    ms.sales,
    am.avg_sales,
    ms.sales - am.avg_sales AS variance
FROM monthly_sales ms
CROSS JOIN avg_monthly am
ORDER BY ms.month;

-- 递归CTE（层次结构）
WITH RECURSIVE org_tree AS (
    -- 基础情况
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level,
        ARRAY[employee_id] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归情况
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path || e.employee_id
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY path;

查询优化

1. 有效使用索引

-- 在频繁查询的列上创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 复合索引（顺序重要！）
CREATE INDEX idx_orders_composite
ON orders(status, customer_id, order_date);

-- ✅ 此查询使用索引
SELECT * FROM orders
WHERE status = 'pending'
    AND customer_id = 123
    AND order_date > '2024-01-01';

-- ❌ 此查询不使用索引（跳过第一列）
SELECT * FROM orders
WHERE customer_id = 123;

-- 部分/过滤索引（更小、更快）
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';

-- 覆盖索引（包含所有需要的列）
CREATE INDEX idx_users_covering
ON users(email)
INCLUDE (name, created_at);

2. 避免SELECT *

-- ❌ 错误：检索所有列
SELECT * FROM users;

-- ✅ 正确：仅选择需要的列
SELECT user_id, email, name FROM users;

-- ✅ 正确：对JOIN更高效
SELECT
    u.user_id,
    u.email,
    o.order_id,
    o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

3. 优化JOIN操作

-- ❌ 错误：JOIN后过滤
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';

-- ✅ 正确：JOIN前过滤
SELECT u.name, o.total
FROM users u
INNER JOIN (
    SELECT user_id, total
    FROM orders
    WHERE status = 'completed'
) o ON u.user_id = o.user_id;

-- ✅ 更好：使用WHERE与INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';

4. 使用EXISTS替代IN

-- ❌ 较慢：IN与子查询
SELECT name FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE total > 1000
);

-- ✅ 更快：EXISTS
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
        AND o.total > 1000
);

5. 避免在索引列上使用函数

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

-- ✅ 正确：使用函数索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 或使用不区分大小写的排序规则
SELECT * FROM users
WHERE email = 'john@example.com' COLLATE utf8_general_ci;

6. 限制结果集

-- ✅ 使用LIMIT/TOP进行分页
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- ✅ 使用WHERE提前减少行数
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

7. 批量操作

-- ❌ 错误：多个单次插入
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');

-- ✅ 正确：批量插入
INSERT INTO users (name, email) VALUES
    ('User1', 'user1@example.com'),
    ('User2', 'user2@example.com'),
    ('User3', 'user3@example.com');

-- ✅ 正确：批量更新
UPDATE products
SET price = price * 1.1
WHERE category IN ('Electronics', 'Computers');

EXPLAIN计划

PostgreSQL

-- 简单EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN ANALYZE（实际运行查询）
EXPLAIN ANALYZE
SELECT
    c.name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- 查找：
-- - Seq Scan（坏，需要索引）
-- - Index Scan（好）
-- - Bitmap Heap Scan（对多行好）
-- - Hash Join vs Nested Loop
-- - 高成本数字

MySQL

-- EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

-- 查找：
-- - type: ALL（表扫描，坏）
-- - type: index（索引扫描，好）
-- - type: ref（索引查找，很好）
-- - Extra: Using filesort（可能需要索引）
-- - Extra: Using temporary（可能需要优化）

索引策略

何时索引

✅ 索引这些列：

主键（自动）
外键
WHERE子句中的列
JOIN条件中的列
ORDER BY中的列
GROUP BY中的列

❌ 不要索引：

小表（< 1000行）
低基数列（不同值少）
频繁更新的列
大文本/blob列

索引类型

-- B-Tree（默认，最常见）
CREATE INDEX idx_users_email ON users(email);

-- 哈希索引（仅等式，PostgreSQL）
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

-- GIN（全文搜索、数组、JSONB）
CREATE INDEX idx_posts_content_gin
ON posts USING GIN(to_tsvector('english', content));

-- GiST（几何、全文）
CREATE INDEX idx_locations_gist
ON locations USING GIST(coordinates);

-- 部分索引（过滤）
CREATE INDEX idx_orders_pending
ON orders(customer_id)
WHERE status = 'pending';

-- 表达式索引
CREATE INDEX idx_users_email_domain
ON users((email ~~ '%@gmail.com%'));

复合索引顺序

-- 索引列顺序重要！
CREATE INDEX idx_orders_search
ON orders(status, customer_id, created_at);

-- ✅ 使用索引（最左列）
WHERE status = 'completed'

-- ✅ 使用索引（最左列）
WHERE status = 'completed' AND customer_id = 123

-- ✅ 使用完整索引
WHERE status = 'completed'
    AND customer_id = 123
    AND created_at > '2024-01-01'

-- ❌ 不使用索引（跳过第一列）
WHERE customer_id = 123

-- ❌ 不使用索引（跳过第一列）
WHERE created_at > '2024-01-01'

MongoDB查询

查找查询

// 基础查找
db.users.find({ status: 'active' })

// 查找与投影
db.users.find(
    { status: 'active' },
    { name: 1, email: 1, _id: 0 }
)

// 查找与运算符
db.orders.find({
    total: { $gt: 100, $lt: 1000 },
    status: { $in: ['pending', 'processing'] },
    'customer.city': 'New York'
})

// 查找与排序和限制
db.products.find({ category: 'Electronics' })
    .sort({ price: -1 })
    .limit(10)

// 计数
db.users.countDocuments({ status: 'active' })

聚合管道

// 分组与计数
db.orders.aggregate([
    { $match: { status: 'completed' } },
    { $group: {
        _id: '$customer_id',
        total_orders: { $sum: 1 },
        total_spent: { $sum: '$total' },
        avg_order: { $avg: '$total' }
    }},
    { $sort: { total_spent: -1 } },
    { $limit: 10 }
])

// 查找（JOIN）
db.orders.aggregate([
    { $lookup: {
        from: 'customers',
        localField: 'customer_id',
        foreignField: '_id',
        as: 'customer'
    }},
    { $unwind: '$customer' },
    { $project: {
        order_id: 1,
        total: 1,
        'customer.name': 1,
        'customer.email': 1
    }}
])

// 复杂聚合
db.sales.aggregate([
    // 过滤
    { $match: {
        date: { $gte: ISODate('2024-01-01') }
    }},

    // 添加计算字段
    { $addFields: {
        month: { $month: '$date' },
        year: { $year: '$date' }
    }},

    // 按月分组
    { $group: {
        _id: { year: '$year', month: '$month' },
        total_sales: { $sum: '$amount' },
        order_count: { $sum: 1 },
        avg_sale: { $avg: '$amount' }
    }},

    // 排序
    { $sort: { '_id.year': 1, '_id.month': 1 } },

    // 重塑
    { $project: {
        _id: 0,
        date: {
            $concat: [
                { $toString: '$_id.year' },
                '-',
                { $toString: '$_id.month' }
            ]
        },
        total_sales: 1,
        order_count: 1,
        avg_sale: { $round: ['$avg_sale', 2] }
    }}
])

MongoDB索引

// 单字段索引
db.users.createIndex({ email: 1 })

// 复合索引
db.orders.createIndex({ customer_id: 1, created_at: -1 })

// 唯一索引
db.users.createIndex({ email: 1 }, { unique: true })

// 部分索引
db.orders.createIndex(
    { customer_id: 1 },
    { partialFilterExpression: { status: 'active' } }
)

// 文本索引
db.products.createIndex({ name: 'text', description: 'text' })

// TTL索引（自动删除）
db.sessions.createIndex(
    { created_at: 1 },
    { expireAfterSeconds: 3600 }
)

// 列出索引
db.users.getIndexes()

// 分析查询性能
db.orders.find({ customer_id: 123 }).explain('executionStats')

GraphQL查询

# 基础查询
query {
  users {
    id
    name
    email
  }
}

# 带参数的查询
query {
  user(id: "123") {
    name
    email
    orders {
      id
      total
      status
    }
  }
}

# 带变量的查询
query GetUser($userId: ID!) {
  user(id: $userId) {
    name
    email
    orders(limit: 10, status: COMPLETED) {
      id
      total
      createdAt
    }
  }
}

# 片段（可重用字段）
fragment UserFields on User {
  id
  name
  email
  createdAt
}

query {
  user(id: "123") {
    ...UserFields
    orders {
      id
      total
    }
  }
}

# 使用DataLoader避免N+1查询
query {
  orders {
    id
    total
    customer {  # 通过DataLoader批处理
      name
      email
    }
  }
}

常见反模式

❌ N+1查询问题

-- 错误：N+1查询
SELECT * FROM customers;  -- 1次查询
-- 然后对每个客户：
SELECT * FROM orders WHERE customer_id = ?;  -- N次查询

-- 正确：单次JOIN查询
SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

❌ 对不同列使用OR

-- 错误：无法有效使用索引
SELECT * FROM products
WHERE name = 'iPhone' OR category = 'Electronics';

-- 正确：使用UNION
SELECT * FROM products WHERE name = 'iPhone'
UNION
SELECT * FROM products WHERE category = 'Electronics';

❌ 隐式类型转换

-- 错误：'123'是字符串，user_id是整数
SELECT * FROM users WHERE user_id = '123';

-- 正确：使用正确类型
SELECT * FROM users WHERE user_id = 123;

查询性能检查清单

[ ] 仅选择需要的列（避免SELECT *）
[ ] 为WHERE/JOIN/ORDER BY列添加索引
[ ] 使用EXPLAIN分析查询计划
[ ] 避免在索引列上使用函数
[ ] 使用EXISTS替代IN处理子查询
[ ] 批量执行INSERT/UPDATE操作
[ ] 使用适当的JOIN类型
[ ] 提前过滤（WHERE在JOIN前）
[ ] 对大型结果集使用LIMIT
[ ] 监控慢查询日志
[ ] 定期更新统计信息
[ ] 尽可能避免SELECT DISTINCT
[ ] 适当使用覆盖索引

资源

PostgreSQL: https://www.postgresql.org/docs/current/performance-tips.html
MySQL: https://dev.mysql.com/doc/refman/8.0/en/optimization.html
MongoDB: https://docs.mongodb.com/manual/core/query-optimization/
使用索引，Luke: https://use-the-index-luke.com/

“过早优化是万恶之源，但慢查询是所有挫折的根源。”

名称: 查询专家 描述: 掌握SQL和NoSQL系统的数据库查询。生成优化的查询，使用EXPLAIN计划分析性能，设计有效索引，并解决慢查询问题。

查询专家

技能功能

支持的数据库

SQL数据库

NoSQL数据库

查询语言

SQL查询模式

SELECT查询

基础SELECT

JOIN操作

子查询

聚合操作

窗口函数（PostgreSQL、SQL Server、MySQL 8+）

CTE（公共表表达式）

查询优化

1. 有效使用索引

2. 避免SELECT *

3. 优化JOIN操作

4. 使用EXISTS替代IN

5. 避免在索引列上使用函数

6. 限制结果集

7. 批量操作

EXPLAIN计划

PostgreSQL

MySQL

索引策略

何时索引

索引类型

复合索引顺序

MongoDB查询

查找查询

聚合管道

MongoDB索引

GraphQL查询

常见反模式

❌ N+1查询问题

❌ 对不同列使用OR

❌ 隐式类型转换

查询性能检查清单

资源

名称: 查询专家描述: 掌握SQL和NoSQL系统的数据库查询。生成优化的查询，使用EXPLAIN计划分析性能，设计有效索引，并解决慢查询问题。