当前位置： 代码网 > it编程>数据库>Mysql > 数据库慢查询排查之从EXPLAIN到索引优化详解

数据库慢查询排查之从EXPLAIN到索引优化详解

2026年07月17日 • Mysql •我要评论
一、线上慢查询的连锁反应监控告警突然密集触发，核心订单接口 p99 延迟从 50ms 飙到 3s。数据库连接池耗尽，上游服务跟着超时。排查下来，是一条新增的查询 sql 执行了 12 秒，全表扫描了

一、线上慢查询的连锁反应

监控告警突然密集触发，核心订单接口 p99 延迟从 50ms 飙到 3s。数据库连接池耗尽，上游服务跟着超时。排查下来，是一条新增的查询 sql 执行了 12 秒，全表扫描了 2000 万行数据，直接把订单表锁住了。

这种情况很常见。线上 80% 的数据库性能问题，根源都在索引设计。但加索引不能无脑加——多一个索引就多一份写入开销，索引太多，优化器反而容易选错执行计划。下面结合具体案例，讲讲怎么通过 explain 和执行计划来优化数据库性能。

二、查询执行引擎：从 sql 到磁盘 i/o

2.1 查询执行流程

sql 从提交到返回结果，要经过解析、优化、执行三个阶段。优化器选执行计划时，主要看索引统计信息（cardinality、ndistinct）和成本估算模型。

flowchart td
    a[sql 文本] --> b[解析器: 语法树 ast]
    b --> c[预处理器: 语义检查]
    c --> d[优化器: 成本估算]
    d --> e{选择执行计划}
    e -->|索引可用| f[索引扫描]
    e -->|索引不可用| g[全表扫描]
    f --> h[回表查询: 获取完整行]
    h --> i[过滤与排序]
    g --> i
    i --> j[返回结果集]

    d --> k[统计信息: cardinality, ndistinct]
    k --> e

    style g fill:#f96,stroke:#333
    style h fill:#ff9,stroke:#333

红色节点是全表扫描，性能最差。黄色节点是回表查询，索引覆盖不足时会有额外开销。

2.2 索引扫描类型与成本

扫描类型触发条件i/o 成本适用场景
const/eq_ref主键/唯一索引等值查询o(1)单行精确查找
ref非唯一索引等值查询o(logn + m)少量行匹配
range索引范围查询o(logn + m)时间范围、id 范围
index索引全扫描o(n)覆盖索引，避免回表
all全表扫描o(n)无可用索引

2.3 b+ 树索引的底层结构

innodb 的 b+ 树索引，非叶子节点存键值和子节点指针，叶子节点存完整数据（聚簇索引）或主键值（二级索引）。二级索引查询需要回表——先查二级索引拿主键，再查聚簇索引拿完整行。覆盖索引（covering index）让查询只访问二级索引，不用回表。

三、生产级慢查询诊断与索引优化实战

3.1 explain 执行计划解读

-- 线上慢查询：按用户查最近订单
select order_id, status, created_at, total_amount
from orders
where user_id = 12345
  and status in ('paid', 'shipped')
  and created_at > '2025-01-01'
order by created_at desc
limit 20;

-- explain 分析
explain select order_id, status, created_at, total_amount
from orders
where user_id = 12345
  and status in ('paid', 'shipped')
  and created_at > '2025-01-01'
order by created_at desc
limit 20;

执行计划输出：

idtypekeyrowsextra
1refidx_user_id8500using where; using filesort

问题诊断：

  • type=ref：只用了 user_id 索引，扫了 8500 行
  • using where：在 8500 行里逐行过滤 status 和 created_at
  • using filesort：结果集需要额外排序，索引有序性没利用上

3.2 索引优化：联合索引设计

-- 优化方案：创建联合索引，遵循最左前缀原则
-- 索引列顺序：等值条件列在前，范围条件列在后，排序列最后

-- 错误索引：created_at 在前，等值条件无法利用索引
-- create index idx_wrong on orders(created_at, user_id, status);

-- 正确索引：user_id 等值过滤 → status 等值过滤 → created_at 范围+排序
create index idx_user_status_created
on orders(user_id, status, created_at);

-- 优化后 explain
explain select order_id, status, created_at, total_amount
from orders
where user_id = 12345
  and status in ('paid', 'shipped')
  and created_at > '2025-01-01'
order by created_at desc
limit 20;

优化后执行计划：

idtypekeyrowsextra
1rangeidx_user_status_created42using index condition

效果：扫描行数从 8500 降到 42，filesort 没了，查询时间从 12s 降到 3ms。

3.3 覆盖索引：消除回表开销

-- 如果查询只需要索引列，可以设计覆盖索引
-- 覆盖索引：索引包含查询的所有列，无需回表

-- 原查询需要 total_amount，不在联合索引中，仍需回表
-- 如果 total_amount 查询频率极高，可以扩展索引
create index idx_user_status_created_amount
on orders(user_id, status, created_at, total_amount);

-- 此时 extra 列显示 using index，完全避免回表
explain select order_id, status, created_at, total_amount
from orders
where user_id = 12345
  and status in ('paid', 'shipped')
  and created_at > '2025-01-01'
order by created_at desc
limit 20;

3.4 慢查询自动化诊断脚本

import re
from dataclasses import dataclass
from typing import list, optional

@dataclass
class slowquery:
    """慢查询信息"""
    sql: str
    query_time: float       # 秒
    rows_examined: int
    rows_sent: int
    index_used: optional[str]

class slowqueryanalyzer:
    """
    慢查询分析器
    解析 mysql slow query log，识别索引缺失和优化机会
    """
    # 常见慢查询模式与优化建议
    patterns = [
        {
            "name": "全表扫描",
            "regex": r"explain.*type:\s*all",
            "advice": "缺少索引或索引未被使用，检查 where 条件列是否有索引",
        },
        {
            "name": "filesort",
            "regex": r"explain.*extra:.*using filesort",
            "advice": "order by 列未利用索引有序性，考虑将排序列加入联合索引末尾",
        },
        {
            "name": "临时表",
            "regex": r"explain.*extra:.*using temporary",
            "advice": "group by 或 distinct 导致临时表，考虑添加覆盖索引",
        },
        {
            "name": "低效范围扫描",
            "regex": r"rows_examined:\s*(\d+)",
            "threshold": 100000,
            "advice": "扫描行数过多，检查索引选择性和查询条件",
        },
    ]

    def analyze(self, query: slowquery) -> list[dict]:
        """
        分析单条慢查询，返回诊断结果
        """
        findings = []

        # 检查扫描行数与返回行数比值
        if query.rows_examined > 0 and query.rows_sent > 0:
            ratio = query.rows_examined / query.rows_sent
            if ratio > 100:
                findings.append({
                    "level": "high",
                    "issue": f"扫描/返回比 {ratio:.0f}:1，索引过滤效率极低",
                    "advice": "检查 where 条件是否匹配联合索引的最左前缀",
                })
            elif ratio > 10:
                findings.append({
                    "level": "medium",
                    "issue": f"扫描/返回比 {ratio:.0f}:1，索引过滤效率偏低",
                    "advice": "考虑扩展联合索引，减少回表过滤的行数",
                })

        # 检查是否未使用索引
        if query.index_used is none:
            findings.append({
                "level": "critical",
                "issue": "未使用任何索引，全表扫描",
                "advice": "为 where 条件列创建联合索引，遵循最左前缀原则",
            })

        # 检查查询时间
        if query.query_time > 1.0:
            findings.append({
                "level": "high",
                "issue": f"查询耗时 {query.query_time:.2f}s，超过 1 秒阈值",
                "advice": "优先检查索引覆盖和扫描行数",
            })

        # 检查 select * 模式
        if re.search(r"select\s+\*", query.sql, re.ignorecase):
            findings.append({
                "level": "medium",
                "issue": "使用 select *，无法利用覆盖索引",
                "advice": "明确指定查询列，配合覆盖索引避免回表",
            })

        return findings

    def suggest_index(
        self,
        table: str,
        where_cols: list[str],
        order_cols: list[str],
    ) -> str:
        """
        根据查询条件生成索引建议
        原则：等值列在前，排序列在后
        """
        # 等值条件列 + 排序列
        index_cols = where_cols + order_cols
        cols_str = ", ".join(index_cols)
        return f"create index idx_auto_{table}_{'_'.join(index_cols)} on {table}({cols_str});"

3.5 优化效果数据

指标优化前联合索引覆盖索引
扫描行数8,500,0004242
回表次数8,500,000420
查询时间12.3s3.2ms1.1ms
cpu 占用85%2%1%
索引磁盘占用01.2 gb1.8 gb

联合索引把查询时间从 12s 降到了 3ms。覆盖索引能再压到 1ms，但索引体积增加了 50%。覆盖索引能省回表，但占空间，得看业务值不值。

四、索引优化的代价

4.1 索引的写入代价

每个索引在 insert/update/delete 时都需要同步维护。一张表 5 个索引，写入开销约为无索引的 3-4 倍。在高写入场景（如日志表），索引越多，写入越慢。日志表建议只保留主键和时间索引，查询走异步导出。

4.2 统计信息失真

mysql 优化器依赖 information_schema.statistics 中的 cardinality 做成本估算。但 cardinality 是采样估算，在数据分布不均匀时严重失真。典型场景：status 列 99% 的值是 'completed'，优化器以为选择性很好，实际扫描 99% 的行。解决方案：手动 analyze table 更新统计信息，或用 force index 强制指定索引。

4.3 联合索引的最左前缀陷阱

联合索引 (a, b, c) 只能支持 a、a,b、a,b,c 三种查询模式。查询条件只有 b 或 b,c 时无法使用索引。更隐蔽的陷阱：where a = 1 and b like '%keyword%'，like 的前缀通配符导致 b 列无法利用索引，只能用到 a 列。

4.4 索引冗余与冲突

已有索引 (a, b) 时，再建 (a) 就是冗余——前者已经覆盖了后者的查询场景。已有 (a, b) 和 (a, c) 时，如果查询条件是 where a = 1 and b = 2 and c = 3，优化器只能选其一，无法同时利用两个索引（mysql 的 index merge 效率通常不如联合索引）。此时应建 (a, b, c) 替代两个索引。

4.5 禁用索引的场景

  • 高频写入、低频查询的日志表
  • 数据量极小（< 1000 行）的配置表，全表扫描比索引查找更快
  • 查询条件极度分散（每个值只匹配 1-2 行），索引维护成本 > 查询收益

五、总结

数据库慢查询优化的核心是减少磁盘 i/o 和计算量。explain 执行计划是诊断的起点，type 列决定扫描方式，rows 列决定扫描规模，extra 列揭示额外开销。联合索引设计遵循"等值列在前、排序列在后"的最左前缀原则，覆盖索引消除回表但增加索引体积。实战数据表明，一个精心设计的联合索引可以将查询时间从 12s 降到 3ms。但索引不是越多越好——每个索引都有写入代价，统计信息失真会导致优化器选错执行计划，联合索引的最左前缀规则限制了查询模式的灵活性。优化就是要在查询速度和写入成本之间找平衡，用 explain 说话，用 benchmark 数据服人。

到此这篇关于数据库慢查询排查之从explain到索引优化详解的文章就介绍到这了,更多相关数据库慢查询排查内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com