MySQL-09 查询优化与执行计划

2026-03-17 约 4702 字预计阅读 10 分钟

MySQL 查询优化与执行计划

1. 优化工作流

慢查询 → 找到 SQL → EXPLAIN 分析 → 找出瓶颈 → 针对性优化 → 验证

发现慢 SQL 的途径：
  1. 慢查询日志（long_query_time = 1）
  2. performance_schema.events_statements_summary_by_digest
  3. show processlist（实时）
  4. 监控系统（Prometheus + mysqld_exporter）
  5. 应用层埋点（Go 的 middleware 记录 SQL 耗时）

2. EXPLAIN 全面解读

2.1 基本用法

-- 基本 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 显示更多信息（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1;

-- 实际执行并显示真实统计（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

-- 竖排显示（更易读）
EXPLAIN SELECT * FROM orders WHERE user_id = 1\G

2.2 id 列

-- 简单查询：id=1
EXPLAIN SELECT * FROM orders WHERE id = 1;
-- id = 1

-- 子查询：子查询 id 更大
EXPLAIN
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- id=1: users
-- id=2: orders（子查询）

-- UNION：有 NULL 的行是合并的临时表
EXPLAIN
SELECT id FROM users WHERE status=1
UNION
SELECT id FROM users WHERE status=2;
-- id=1: 第一个 SELECT
-- id=2: 第二个 SELECT
-- id=NULL: UNION RESULT（临时表）

2.3 select_type 列

值	说明
SIMPLE	简单 SELECT，无子查询/UNION
PRIMARY	最外层查询
SUBQUERY	SELECT 中的子查询（非 FROM 子句）
DEPENDENT SUBQUERY	依赖外部查询的子查询（相关子查询）
DERIVED	FROM 子句中的子查询（派生表）
UNION	UNION 中第二个及后续 SELECT
UNION RESULT	UNION 的合并结果
MATERIALIZED	物化子查询（MySQL 5.6+）

2.4 type 列（最重要）

从好到差，遇到 index 以下必须优化：

system  极少，表只有一行
const   主键/唯一键等值查询（id = 1）
eq_ref  JOIN 时被驱动表用主键/唯一键（ON a.id = b.id）
ref     普通索引等值查询（WHERE user_id = ?）
ref_or_null  WHERE col = ? OR col IS NULL
range   范围查询（BETWEEN, IN, >, <, LIKE 'prefix%'）
index   全索引扫描（比 ALL 好，因为索引小于数据）
ALL     全表扫描（性能最差！）

2.5 key_len 列

用于判断联合索引使用了几个字段。

计算规则：

INT NOT NULL          = 4 字节
INT NULL              = 4 + 1（NULL 标志位）= 5 字节
VARCHAR(N) NOT NULL   = N × 字符集字节数 + 2（长度前缀）
VARCHAR(N) NULL       = N × 字符集字节数 + 2 + 1
CHAR(N) NOT NULL      = N × 字符集字节数
DATETIME NOT NULL     = 5 字节（MySQL 5.6+）

-- 联合索引 (user_id BIGINT, status TINYINT)
-- user_id = 8 + 1（NULL）= 9
-- status  = 1 + 1（NULL）= 2
-- 总 key_len = 11 → 说明用了两个字段
-- key_len = 9 → 只用了 user_id

2.6 rows 列

优化器估算的扫描行数，不是精确值。与实际行数差距大时说明统计信息过期。

-- 更新统计信息
ANALYZE TABLE orders;

2.7 Extra 列详解

-- ✅ Using index：覆盖索引，无需回表
-- 出现在 type=index 时表示全索引扫描，配合覆盖索引也是好事

-- ✅ Using index condition：索引下推（ICP）
-- 引擎层过滤更多，减少回表

-- ⚠️ Using where：Server 层过滤
-- 存储引擎返回的数据中，有一部分不满足 WHERE，需要 Server 再过滤
-- 不一定是坏事，但如果 rows 很大、Rows_sent 很小，说明过滤效率低

-- ❌ Using filesort：额外排序
-- 无法利用索引顺序，需要在内存/磁盘排序
-- 内存排序：sort_buffer_size 足够
-- 磁盘排序：会生成临时文件，慢！

-- ❌ Using temporary：使用了临时表
-- 通常出现在 GROUP BY、DISTINCT、UNION 中
-- 内存临时表：tmp_table_size 和 max_heap_table_size
-- 磁盘临时表：更慢，转为 InnoDB 或 MyISAM

-- Using join buffer (Block Nested Loop)：JOIN 无索引，使用 join buffer
-- Using join buffer (Batched Key Access)：BKA，批量 key 访问

-- Select tables optimized away：优化器直接算出结果（MIN/MAX+索引）

2.8 EXPLAIN ANALYZE 示例

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id
ORDER BY cnt DESC
LIMIT 10;

/* 输出示例：
-> Limit: 10 row(s)  (actual time=12.3..12.3 rows=10 loops=1)
    -> Sort: cnt DESC  (actual time=12.2..12.2 rows=10 loops=1)
        -> Stream results  (actual time=11.8..12.1 rows=100 loops=1)
            -> Group aggregate: count(o.id)  (actual time=0.1..11.5 rows=100 loops=1)
                -> Nested loop left join  (actual time=0.05..10.2 rows=500 loops=1)
                    -> Filter: (u.status = 1)  (actual time=0.02..1.3 rows=100 loops=1)
                        -> Index scan on u using idx_status  (actual time=0.02..1.1 rows=100 loops=1)
                    -> Index lookup on o using idx_user_id (user_id=u.id)  (actual time=0.05..0.08 rows=5 loops=100)
*/

3. 索引优化

3.1 强制/忽略索引

-- 强制使用某个索引
SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 1;
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1;
-- USE INDEX：建议，优化器还可以选全表
-- FORCE INDEX：强制，优化器只能选这个或全表

-- 忽略某个索引（避免优化器选错）
SELECT * FROM orders IGNORE INDEX (idx_wrong) WHERE user_id = 1;

3.2 统计信息问题

优化器选错索引，通常是统计信息不准确。

-- 更新统计信息
ANALYZE TABLE orders;

-- 查看索引统计
SHOW INDEX FROM orders;
-- Cardinality 列：不同值的估计数量（越大选择性越好）

-- 控制采样数（提高精度，但会增加 ANALYZE 时间）
innodb_stats_persistent_sample_pages = 20  -- 默认 20，可以增大
innodb_stats_auto_recalc = ON             -- 数据变化超过 10% 自动重算

3.3 索引合并（Index Merge）

多个单列索引可以合并使用：

EXPLAIN SELECT * FROM users WHERE name = 'Alice' OR email = 'bob@example.com';
-- type: index_merge
-- key: idx_name,idx_email
-- Extra: Using union(idx_name,idx_email); Using where

但 Index Merge 通常不如联合索引效率高：

-- 如果这个 OR 查询是高频的，考虑改写为 UNION
SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE email = 'bob@example.com' AND name != 'Alice';

4. JOIN 优化

4.1 驱动表选择

-- MySQL 优化器默认选小结果集作为驱动表
-- 可以用 STRAIGHT_JOIN 强制顺序（左表为驱动表）
SELECT STRAIGHT_JOIN u.*, o.amount
FROM users u
STRAIGHT_JOIN orders o ON u.id = o.user_id;

-- 检查 EXPLAIN 中第一行出现的表是驱动表

4.2 被驱动表必须有索引

-- ❌ 被驱动表无索引，导致 Block Nested Loop（BNL）
-- Extra: Using join buffer (block nested loop)

-- 解决：在 JOIN 条件列上建索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

4.3 Join Buffer

# BNL 时使用的 join buffer 大小
join_buffer_size = 2M  # 默认 256KB，复杂 JOIN 可调大

4.4 MySQL 8.0 Hash Join

MySQL 8.0.18+ 对等值条件的 JOIN，当被驱动表无合适索引时，自动使用 Hash Join：

BNL（Block Nested Loop）：O(n × m / join_buffer)
Hash Join：构建哈希表 O(min(n,m))，探测 O(n+m)

Hash Join 通常比 BNL 快很多

4.5 JOIN 数量控制

# 优化器搜索 JOIN 顺序的深度
optimizer_search_depth = 62  # 默认 62（全搜索）
# 表数量 > 7 时，全排列爆炸（7! = 5040），可以限制
optimizer_search_depth = 5   # 超过 5 张表后，贪心算法

5. 子查询优化

5.1 子查询转 JOIN

-- ❌ 低效：NOT IN 子查询（每次都执行）
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- ✅ 优化：LEFT JOIN + IS NULL
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

-- ❌ 低效：相关子查询（每行都执行子查询）
SELECT u.*, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS cnt
FROM users u;

-- ✅ 优化：JOIN + 聚合
SELECT u.*, COALESCE(o.cnt, 0) AS cnt
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
) o ON u.id = o.user_id;

5.2 EXISTS vs IN

-- 外大内小：用 EXISTS（外层循环少，内层 EXISTS 快速短路）
-- 外小内大：用 IN（内层做一次，外层 IN 查）

-- 大表 users，小表 vip_users
-- ✅ 用 IN（vip_users 小，IN 列表短）
SELECT * FROM users WHERE id IN (SELECT user_id FROM vip_users);

-- 大表 users，大表 orders
-- ✅ 用 EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

5.3 派生表（Derived Table）物化

-- MySQL 5.6+ 会物化派生表（将子查询结果存入临时表），只执行一次
SELECT * FROM (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
) t
WHERE t.total > 10000;

-- 可以通过 optimizer_switch 控制
SET optimizer_switch = 'derived_merge=on';   -- 合并（避免临时表）
SET optimizer_switch = 'derived_merge=off';  -- 物化

6. 分页优化

6.1 深分页问题

-- ❌ 深分页（LIMIT 100000, 20）
-- 需要先读取并丢弃 100000 行，再返回 20 行
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
-- 执行时间随 OFFSET 线性增加

6.2 游标分页（推荐）

-- ✅ 游标分页：用上次查询的最后一个 id 作为游标
-- 前提：id 连续递增（或有单调索引列）

-- 第一页
SELECT * FROM orders ORDER BY id DESC LIMIT 20;
-- 返回最后一行 id = 9980

-- 第二页（传入 last_id = 9980）
SELECT * FROM orders WHERE id < 9980 ORDER BY id DESC LIMIT 20;
-- 稳定 O(log n)，不受页码影响

游标分页的局限：不支持随机跳页，只能顺序翻页。

6.3 延迟关联（Deferred Join）

-- ✅ 延迟关联：先查 id，再回表
SELECT o.*
FROM orders o
JOIN (
  SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20
) t ON o.id = t.id;

-- 原理：子查询只查 id（覆盖索引，不回表），获得 20 个 id 后再 JOIN 回表
-- 比直接 LIMIT 100000, 20 快得多（覆盖索引扫描 vs 数据页扫描）

6.4 分页总数查询优化

-- ❌ COUNT(*) 配合复杂 WHERE，每次都全扫描
SELECT COUNT(*) FROM orders WHERE user_id = 1 AND status = 1;

-- ✅ 方案1：维护计数器表
-- 异步更新（缓存 + 定时写）

-- ✅ 方案2：估算值（对精确度要求不高时）
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- rows 列是估算值，可以直接用于"约 N 条结果"的提示

-- ✅ 方案3：加适当的索引
-- WHERE 列有索引时，COUNT(*) 可以走覆盖索引
CREATE INDEX idx_user_status ON orders (user_id, status);
SELECT COUNT(*) FROM orders WHERE user_id = 1 AND status = 1;
-- Extra: Using index（覆盖索引，不回表）

7. 聚合与排序优化

7.1 GROUP BY 优化

-- 有索引时，GROUP BY 可以利用索引顺序，不需要临时表和排序
-- INDEX (user_id, status)
SELECT user_id, status, COUNT(*)
FROM orders
GROUP BY user_id, status;
-- Extra: Using index

-- GROUP BY 与索引顺序不一致时
SELECT status, user_id, COUNT(*)
FROM orders
GROUP BY status, user_id;
-- 与索引顺序不符，可能 Using temporary + Using filesort

-- 关闭 ONLY_FULL_GROUP_BY 模式（不推荐，可能返回不确定的值）
-- SELECT 中的非聚合列必须在 GROUP BY 中出现
SET sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');

7.2 ORDER BY 优化

-- ✅ 利用索引排序（无需 filesort）
-- INDEX idx_user_created (user_id, created_at)

SELECT * FROM orders
WHERE user_id = 1
ORDER BY created_at DESC;
-- Extra: Using where（如果 SELECT *）
-- 或 Extra: Using index condition

-- ❌ 排序方向不一致，无法利用索引（MySQL 5.7-）
SELECT * FROM orders WHERE user_id = 1
ORDER BY status ASC, created_at DESC;

-- ✅ MySQL 8.0+ 支持降序索引
CREATE INDEX idx_user_status_desc ON orders (user_id, status ASC, created_at DESC);

7.3 排序内存

# 排序使用的内存缓冲
sort_buffer_size = 2M  # 默认 256KB

# 单行数据超过 max_length_for_sort_data 时，MySQL 使用两趟排序（慢）
max_length_for_sort_data = 4096  # MySQL 8.0.20+ 弃用

# MySQL 8.0 使用 sort_buffer_size 控制，并支持 chunk-based 排序

7.4 filesort 算法

MySQL 有两种 filesort 算法：

单次传送（Single-pass）：将 SELECT 的所有列放入 sort buffer，排序后直接返回。内存需求大，但 I/O 次数少。

两次传送（Double-pass）：只将排序列 + 行指针放入 sort buffer，排序后再根据指针回表取数据。I/O 更多，但内存占用少。

MySQL 5.6+ 默认使用单次传送，前提是 sort_buffer_size 足够。

8. SQL 改写技巧

8.1 避免 SELECT *

-- ❌ SELECT *
SELECT * FROM users WHERE id = 1;

-- ✅ 只查需要的列
SELECT id, name, email FROM users WHERE id = 1;

-- 好处：
-- 1. 减少网络传输量
-- 2. 利用覆盖索引避免回表
-- 3. 避免 TEXT/BLOB 列传输（这些列通常很大）

8.2 大 IN 列表分批

// ❌ 一次 IN 1000 个 ID，影响性能
db.Query("SELECT * FROM users WHERE id IN (?)", ids) // 1000 个 id

// ✅ 分批处理
func batchQuery(db *sql.DB, ids []int64) ([]User, error) {
    batchSize := 100
    var users []User
    for i := 0; i < len(ids); i += batchSize {
        batch := ids[i:min(i+batchSize, len(ids))]
        // 查询这批
        rows := queryBatch(db, batch)
        users = append(users, rows...)
    }
    return users, nil
}

8.3 改写 OR 为 UNION

-- ❌ OR 两侧有一个无索引
SELECT * FROM orders WHERE user_id = 1 OR status = 2;

-- ✅ UNION ALL（去重用 UNION，不去重用 UNION ALL 更快）
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 2 AND user_id != 1;

8.4 用 EXISTS 替换 IN（大子查询）

-- ❌ IN 大子查询（先执行子查询，结果可能很大）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- ✅ EXISTS（相关子查询，短路效果）
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.amount > 1000
);

8.5 日期范围替代函数

-- ❌ 对索引列用函数，索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ 范围查询
SELECT * FROM orders
WHERE created_at >= '2024-01-15 00:00:00'
  AND created_at <  '2024-01-16 00:00:00';

SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at <  '2025-01-01 00:00:00';

8.6 合理使用 LIMIT 1

-- 只需要知道"是否存在"时，加 LIMIT 1 找到即停
SELECT 1 FROM orders WHERE user_id = 1 LIMIT 1;
-- 等价 EXISTS 效果，但更直观

-- 比 COUNT(*) > 0 快

9. Optimizer Hints

MySQL 8.0 提供了更丰富的 Optimizer Hints（优化器提示）。

-- 指定 JOIN 算法
SELECT /*+ BNL(o) */ u.*, o.amount
FROM users u JOIN orders o ON u.id = o.user_id;

SELECT /*+ NO_BNL(o) */ ...  -- 禁止 BNL

SELECT /*+ HASH_JOIN(u, o) */ ...  -- 强制 Hash Join（8.0.18+）

-- 指定索引
SELECT /*+ INDEX(u idx_email) */ * FROM users u WHERE email = 'alice@example.com';

-- 控制 JOIN 顺序
SELECT /*+ JOIN_ORDER(u, o, p) */ ...

-- 禁止查询缓存（5.7及以前）
SELECT SQL_NO_CACHE * FROM users WHERE id = 1;

-- 强制优化器重新计算统计
SELECT /*+ SET_VAR(optimizer_switch='mrr=on') */ * FROM orders WHERE id > 1000;

10. Go 中的查询优化实践

10.1 只查需要的字段

// 定义不同场景的结构体
type UserFull struct {
    ID        int64
    Name      string
    Email     string
    Phone     string
    Age       int
    // 20+ 字段...
}

type UserSummary struct {
    ID    int64
    Name  string
    Email string
}

// 列表页用轻量结构体
func ListUsers(db *sql.DB, page, size int) ([]UserSummary, error) {
    rows, err := db.Query(
        "SELECT id, name, email FROM users WHERE status = 1 LIMIT ?, ?",
        (page-1)*size, size,
    )
    // ...
}

// 详情页用完整结构体
func GetUser(db *sql.DB, id int64) (*UserFull, error) {
    // ...
}

10.2 预编译语句缓存

// 高频查询用预编译语句，减少解析开销
type UserRepo struct {
    db         *sql.DB
    stmtGetByID *sql.Stmt
    stmtList    *sql.Stmt
}

func NewUserRepo(db *sql.DB) (*UserRepo, error) {
    r := &UserRepo{db: db}
    var err error

    r.stmtGetByID, err = db.Prepare("SELECT id, name, email FROM users WHERE id = ?")
    if err != nil {
        return nil, err
    }

    r.stmtList, err = db.Prepare("SELECT id, name FROM users WHERE status = ? LIMIT ?, ?")
    if err != nil {
        return nil, err
    }

    return r, nil
}

func (r *UserRepo) GetByID(ctx context.Context, id int64) (*User, error) {
    var u User
    err := r.stmtGetByID.QueryRowContext(ctx, id).Scan(&u.ID, &u.Name, &u.Email)
    return &u, err
}

10.3 慢查询日志集成

// SQL 耗时 middleware（配合 database/sql 的 wrapper）
type LoggingDB struct {
    *sql.DB
    logger *slog.Logger
    slow   time.Duration
}

func (d *LoggingDB) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
    start := time.Now()
    rows, err := d.DB.QueryContext(ctx, query, args...)
    elapsed := time.Since(start)
    if elapsed > d.slow {
        d.logger.Warn("slow query",
            "query", query,
            "args", args,
            "duration", elapsed,
        )
    }
    return rows, err
}

// GORM 慢查询日志
import "gorm.io/gorm/logger"

newLogger := logger.New(
    log.New(os.Stdout, "\r\n", log.LstdFlags),
    logger.Config{
        SlowThreshold:             200 * time.Millisecond,
        LogLevel:                  logger.Warn,
        IgnoreRecordNotFoundError: true,
    },
)
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: newLogger})

10.4 连接池调优

// 根据业务特征调整连接池参数
func configurePool(db *sql.DB, env string) {
    switch env {
    case "production":
        db.SetMaxOpenConns(100)
        db.SetMaxIdleConns(20)
        db.SetConnMaxLifetime(30 * time.Minute)
        db.SetConnMaxIdleTime(10 * time.Minute)
    case "development":
        db.SetMaxOpenConns(10)
        db.SetMaxIdleConns(5)
        db.SetConnMaxLifetime(time.Hour)
    }
}

// 监控连接池状态
func monitorPool(db *sql.DB) {
    stats := db.Stats()
    metrics.Gauge("db.open_conns", float64(stats.OpenConnections))
    metrics.Gauge("db.in_use", float64(stats.InUse))
    metrics.Gauge("db.idle", float64(stats.Idle))
    metrics.Counter("db.wait_count", float64(stats.WaitCount))
    metrics.Gauge("db.wait_duration", stats.WaitDuration.Seconds())
}

小结

优化优先级（从高到低）：

1. 正确使用索引（最大收益）
   → EXPLAIN 查 type 和 Extra
   → 消除全表扫描和文件排序

2. 减少数据量
   → 只 SELECT 需要的列
   → 合理的 WHERE 过滤
   → 分页时用游标而非大 OFFSET

3. JOIN 优化
   → 小表驱动大表
   → 被驱动表有索引

4. 子查询 → JOIN/EXISTS 改写

5. 应用层优化
   → 批量操作
   → 缓存热点数据
   → 读写分离

目录