EXPLAIN 与查询优化

第 13 章 · EXPLAIN 与查询优化

本章目标：掌握 MySQL EXPLAIN 执行计划解读；配置 慢查询日志 与 SQLAlchemy 慢 SQL 告警；识别 索引失效 典型场景；在 SQL 层 发现 N+1（对照 ORM 层）；设计 覆盖索引 优化列表与报表；在 shop-db / db-demo 上完成优化清单；对照 gin-web ch12 连接池与 Preload 思路。

学时建议：4～5 小时（含 2 小时 EXPLAIN 实验）

前置：完成 python-database ch05 索引设计、ch09～ch11 SQLAlchemy/Alembic；gin-web ch12 性能章节已读。

13.1 场景说明：商品列表 P99 飙升

db-demo 与 shop-db 共用贤紫优选商品域模型，核心字段约定：

运营反馈：商品列表接口 P99 从 40ms 升到 800ms。ORM 层已 select_related，但 MySQL 仍出现 type=ALL 全表扫描。

HTTP GET /api/v1/products?page=1
        │
        ▼
SQLAlchemy Session ──► EXPLAIN 审计
        │
        ├── 慢查询 log（>200ms）
        ├── 索引是否命中
        └── N+1：循环内二次 SELECT？

优化顺序：度量（慢日志 + EXPLAIN）→ 索引 / 覆盖索引 → 改写 SQL → ORM 批量加载 → 缓存（ch15）。

13.2 EXPLAIN 基础

在 db-demo 连接 MySQL 后执行：

EXPLAIN SELECT id, slug, name, price, is_published
FROM products
WHERE is_published = 1
ORDER BY id DESC
LIMIT 20;

MySQL 8.0 推荐同时看 EXPLAIN ANALYZE（真实耗时）：

EXPLAIN ANALYZE
SELECT id, slug, name, price, is_published
FROM products
WHERE is_published = 1
ORDER BY id DESC
LIMIT 20;

13.2.1 核心列解读

13.2.2 Extra 常见值

13.2.3 type 优先级（从好到差）

system > const > eq_ref > ref > range > index > ALL

教学验收：列表查询至少 range，点查 slug 应 const/ref。

13.3 慢查询日志

13.3.1 MySQL 服务端配置

db-demo/docker/mysql/conf.d/slow.cnf：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.2
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

重启后验证：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

13.3.2 mysqldumpslog 分析

mysqldumpslog -s t -t 10 /var/log/mysql/slow.log
mysqldumpslog -s at -t 5 slow.log   # 按平均时间

记录到 docs/SLOW_SQL.md：

13.3.3 SQLAlchemy 慢查询钩子

db-demo/db/slow_query.py：

import logging
import time
from sqlalchemy import event
from sqlalchemy.engine import Engine

logger = logging.getLogger("shop.slow_sql")
THRESHOLD_SEC = 0.2


@event.listens_for(Engine, "before_cursor_execute")
def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault("query_start_time", []).append(time.perf_counter())


@event.listens_for(Engine, "after_cursor_execute")
def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    start = conn.info["query_start_time"].pop(-1)
    elapsed = time.perf_counter() - start
    if elapsed >= THRESHOLD_SEC:
        logger.warning(
            "slow sql elapsed=%.3fs rows=%s sql=%s",
            elapsed,
            cursor.rowcount,
            statement[:500],
        )

对照 gin-web ch12：Go 版用 GORM slowQueryLogger + zap；Python 用 SQLAlchemy event.listen，阈值统一 200ms。

13.4 索引设计回顾（shop-db）

-- 列表：已发布 + id 倒序
CREATE INDEX idx_products_published_id
ON products (is_published, id DESC);

-- slug 点查（唯一约束通常已带索引）
-- UNIQUE KEY uk_products_slug (slug)

-- 分类列表（若有关联）
CREATE INDEX idx_products_category_published
ON products (category_id, is_published, id DESC);

再次 EXPLAIN 列表 SQL，期望：

13.5 索引失效场景（必背）

以下写法常导致 不走索引 或 走不全：

13.5.1 实验：隐式转换

-- 坏：全表
EXPLAIN SELECT * FROM products WHERE slug = 123;

-- 好
EXPLAIN SELECT * FROM products WHERE slug = 'python-handbook';

13.5.2 实验：最左前缀

联合索引 (category_id, is_published, id)：

-- ✅ 用索引
EXPLAIN SELECT * FROM products WHERE category_id = 3 AND is_published = 1;

-- ❌ 可能不用
EXPLAIN SELECT * FROM products WHERE is_published = 1;

13.6 覆盖索引（Covering Index）

定义：查询所需列全部在索引 B+Tree 中，无需回表查聚簇索引。

13.6.1 商品列表覆盖索引

CREATE INDEX idx_products_list_cover
ON products (is_published, id DESC, slug, name, price);

EXPLAIN SELECT id, slug, name, price
FROM products
WHERE is_published = 1
ORDER BY id DESC
LIMIT 20;
-- Extra: Using index

13.6.2 报表 COUNT 覆盖

EXPLAIN SELECT COUNT(*)
FROM products
WHERE is_published = 1;
-- 若仅 is_published 在索引，可能 Using index

13.7 N+1：SQL 层识别与修复

N+1 不限于 ORM「懒加载」；任何循环内发 SQL 都是 N+1。

13.7.1 问题代码（SQLAlchemy）

# db-demo/scripts/list_with_category.py — 反例
from sqlalchemy import select