Skip to content

查询优化与 EXPLAIN

中等 约 45 分钟

你将构建什么

一组针对大型订单/客户模式的优化查询,附带前后 EXPLAIN 计划,展示索引和查询重写的影响。

学习目标

  • 阅读和解释 EXPLAIN 输出
  • 识别顺序扫描和高成本节点
  • 为查询模式创建有效索引
  • 重写查询以提升性能

前置要求

  • JOIN 和 WHERE 子句
  • 基本索引知识
  • 聚合查询

步骤

  1. 1

    建立大型数据集

    创建 orders 和 customers 表,填充示例数据以演示优化技术。

    代码
    CREATE TABLE customers (
      id    SERIAL PRIMARY KEY,
      name  TEXT NOT NULL,
      city  TEXT NOT NULL
    );
    
    CREATE TABLE orders (
      id          SERIAL PRIMARY KEY,
      customer_id INTEGER REFERENCES customers(id),
      total       NUMERIC(10,2) NOT NULL,
      status      TEXT NOT NULL,
      created_at  TIMESTAMP DEFAULT NOW()
    );
    
    -- Insert sample data (run multiple times for scale)
    INSERT INTO customers (name, city) SELECT 'Customer ' || i, 'City ' || (i % 10) FROM generate_series(1, 10000) i;
    INSERT INTO orders (customer_id, total, status) SELECT (i % 10000) + 1, (i % 1000)::numeric, CASE WHEN i % 3 = 0 THEN 'shipped' ELSE 'pending' END FROM generate_series(1, 100000) i;

    预期结果

    插入 10000 名客户和 100000 条订单。

  2. 2

    用 EXPLAIN 分析慢查询

    对一个按状态过滤订单并连接客户的查询运行 EXPLAIN ANALYZE。注意 Seq Scan(全表扫描)和高成本数值。

    代码
    EXPLAIN ANALYZE
    SELECT c.name, o.total, o.status
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.status = 'shipped'
    ORDER BY o.created_at DESC
    LIMIT 100;

    预期结果

    计划显示 orders 上的 Seq Scan(无索引)和排序步骤 —— 在大表上都很慢。

  3. 3

    添加索引并重新分析

    在过滤和排序列上创建索引,然后重新运行 EXPLAIN ANALYZE 查看改善。

  4. 4

    优化聚合查询

    查找每个城市的总收入。先写朴素版本,再通过确保连接和分组列有索引来优化。

  5. 5

    重写相关子查询

    相关子查询可能很慢。将「有超过 500 订单的客户」从相关子查询重写为 JOIN 以获得更好性能。

    代码
    -- Slow: correlated subquery
    EXPLAIN ANALYZE
    SELECT name FROM customers c
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500);
    
    -- Fast: JOIN with DISTINCT
    EXPLAIN ANALYZE
    SELECT DISTINCT c.name
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE o.total > 500;

    预期结果

    JOIN 版本通常产生更好的计划,用哈希连接代替嵌套循环。

自己动手试试

你学到了什么

你学会了阅读 EXPLAIN 计划、识别瓶颈(Seq Scan、排序、嵌套循环),并通过有针对性的索引和查询重写进行优化。这是数据库性能调优的核心技能。

相关资源

相关课程

  • 迁移

    带索引的模式演进。

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题