数据库学习教程

    查询优化与执行计划

    数据库优化器会为每条 SQL 选择一条执行路径。学会读懂执行计划，就能定位「为什么这条查询慢」。

    优化器的代价模型

    优化器基于统计信息（行数、索引基数、数据分布直方图）估算每种执行方式的代价，选择代价最低的方案。因此：

    • 统计信息过期会导致选错索引 —— 定期 ANALYZE。
    • 复杂查询（多表连接）的搜索空间巨大，优化器可能做出次优选择。
    • 优化器的目标是「估算代价最小」，不总是「实际最快」。

    读懂 EXPLAIN

    EXPLAIN SELECT u.name, o.amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND u.city = '北京';
    字段关注点
    type访问类型，ALL 全表扫描、index 全索引扫描、range 范围、ref 非唯一索引、eq_ref、const
    key实际使用的索引
    rows预估扫描行数，越小越好
    filtered过滤后剩余行的百分比
    ExtraUsing filesort、Using temporary、Using index 等

    type 的性能排序（由差到好）：

    ALL < index < range < ref < eq_ref < const < system
    Tip

    Extra 中出现 Using filesort 或 Using temporary 往往意味着额外排序/临时表，是重点优化对象。出现 Using index 则说明命中了覆盖索引，是理想状态。

    定位慢查询

    -- MySQL：开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- 超过 1 秒记录

-- 查看当前正在执行的慢 SQL
SHOW FULL PROCESSLIST;

-- MySQL 8：查看语句性能剖析
EXPLAIN ANALYZE SELECT ...;

    PostgreSQL 使用 EXPLAIN ANALYZE 展示实际耗时，注意区分预估行数与实际行数：

    EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

    常见优化手段

    1. 补索引 / 改联合索引：让关键查询走 range 或 ref，避免 ALL。
    2. 消除回表：用覆盖索引满足高频查询。
    3. 改写 SQL：避免函数作用于索引列；用 JOIN 替代相关子查询。
    4. 控制结果集：加 LIMIT、分页时避免大 OFFSET（改用游标/键集分页）。
    5. 减少排序：让 ORDER BY 与索引顺序一致，避免 filesort。
    6. 拆分大查询：把复杂报表拆成预聚合表或异步任务。
    7. 调整连接顺序与统计信息：必要时 ANALYZE TABLE 或加 Hint。
    -- 反例：深分页，越翻越慢
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;

-- 正例：键集分页，用上次最大 id 继续
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 10;
    Warning

    不要盲目加 FORCE INDEX。强制索引在数据分布变化后可能反而更慢，应优先修正统计信息与索引设计。优化前务必用真实数据量测量，避免「小表上的结论」误导。

    分页、批量与拉取大小

    • 深分页是经典性能陷阱，键集分页（Keyset Pagination）几乎总是更优。
    • 批量写入用多值 INSERT 或批量接口，减少网络往返。
    • 大结果集应流式处理，避免一次性加载到内存。

    小结

    • 优化器基于统计信息估算代价，统计过期会选错计划。
    • EXPLAIN 的 type、key、rows、Extra 是定位问题的第一现场。
    • 优化优先级：索引 → SQL 改写 → 结构/架构调整。
    • 一切优化以真实数据与测量的结果为准。