EXPLAIN 与查询优化 · Java 数据库实战

第 14 章 · EXPLAIN 与查询优化

本章目标：掌握 MySQL EXPLAIN / EXPLAIN ANALYZE 执行计划解读；配置 慢查询日志 与 JDBC/JPA 慢 SQL 观测；识别 索引失效 典型场景；设计 覆盖索引 优化商品列表与报表；在 shop-db / db-demo 上完成优化清单；对照 spring-boot-web ch17 的 N+1、连接池与 Hibernate 统计思路。

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

前置：完成 java-database ch05 索引设计、ch08～ch13 JDBC/JPA/Repository；建议浏览 spring-boot-web ch17 性能章节。

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

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

字段	类型	说明
slug	VARCHAR(128) UNIQUE	URL 标识，如 `java-handbook`
is_published	TINYINT(1)	是否上架
price	BIGINT	价格（分），禁止 float

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

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

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

14.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;

14.2.1 核心列解读

列	含义	健康值
id	SELECT 内子查询序号	数字
select_type	查询类型	SIMPLE 为主
table	访问表名	—
type	访问方式	const/ref/range；避免 ALL
possible_keys	可能用到的索引	非空为佳
key	实际索引	与预期一致
key_len	索引使用字节数	联合索引越完整越大
ref	索引比较列	const/列名
rows	预估扫描行数	越小越好
filtered	按条件过滤比例	高为佳
Extra	附加信息	见下表

14.2.2 Extra 常见值

Extra	含义	行动
Using index	覆盖索引，不回表	✅ 理想
Using where	存储引擎过滤	正常
Using filesort	额外排序	考虑索引含 ORDER BY 列
Using temporary	临时表	GROUP BY / DISTINCT 需优化
Using index condition	ICP 索引下推	8.0 常见，一般 OK

14.2.3 type 优先级（从好到差）

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

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

14.3 慢查询日志

14.3.1 MySQL 服务端配置

my.cnf 或 Docker Compose 挂载：

[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

Docker 开发环境示例：

# docker-compose.dev.yml 片段
services:
  mysql:
    image: mysql:8.0
    command:
      - --slow-query-log=1
      - --long-query-time=0.2
      - --log-queries-not-using-indexes=1
    ports:
      - "3306:3306"

14.3.2 分析工具

# pt-query-digest（选修）
pt-query-digest /var/log/mysql/slow.log | head -40

# MySQL 8 内置
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

14.3.3 JDBC 层慢 SQL 日志

HikariCP + 自定义 DataSource 包装或使用 p6spy（spring-boot-web ch17 同款思路）：

<!-- pom.xml 选修 -->
<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

# spy.properties
driverlist=com.mysql.cj.jdbc.Driver
logMessageFormat=com.p6spy.engine.spy.appender.Slf4JLogger
executionThreshold=200

# spring-boot-web application-dev.yml 对照
spring:
  datasource:
    url: jdbc:p6spy:mysql://127.0.0.1:3306/shop_db

纯 Java shop-db 脚本可在 ReportDailyMain 中记录耗时：

long t0 = System.nanoTime();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    // ...
} finally {
    long ms = (System.nanoTime() - t0) / 1_000_000;
    if (ms > 200) log.warn("slow sql {} ms: {}", ms, sql);
}

14.4 shop-db 索引设计复习

14.4.1 推荐索引（ch05/ch07）

-- slug 点查（详情页）
UNIQUE KEY uk_products_slug (slug);

-- 已发布列表 + 排序
KEY idx_products_published_id (is_published, id DESC);

-- 分类下已发布商品
KEY idx_products_category_published (category_id, is_published, id DESC);

-- 订单报表
KEY idx_orders_created (created_at);
KEY idx_orders_user_created (user_slug, created_at DESC);

14.4.2 覆盖索引示例

列表只需 id, slug, name, price, is_published：

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

EXPLAIN 期望 Extra=Using index。

14.4.3 失效场景清单

SQL 写法	问题	改写
`WHERE YEAR(created_at)=2026`	函数破坏索引	`created_at >= '2026-01-01' AND < '2027-01-01'`
`WHERE slug LIKE '%handbook'`	左模糊	全文索引或 ES（扩展）
`OR is_published=1 OR price>10000`	可能全表	UNION 两条索引查询
隐式类型转换 `slug=123`	列变字符串比较	参数类型匹配
`SELECT *` + 宽行	无法覆盖	只 SELECT 必要列

14.5 典型 SQL 优化实战

14.5.1 已发布商品分页

优化前（filesort + 全表）：

SELECT * FROM products WHERE is_published = 1 ORDER BY id DESC LIMIT 20;

优化后（命中 idx_products_published_id）：

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

验收：type=range 或 ref，key=idx_products_published_id。

14.5.2 slug 点查

EXPLAIN SELECT slug, price, is_published, name
FROM products
WHERE slug = 'java-handbook';

期望：type=const，key=uk_products_slug，rows=1。