MySQL慢查询定位与优化

可视化演示

慢查询是数据库性能的头号杀手，定位和优化它是后端和DBA的日常必修课。本文带你搞懂慢查询日志开启、pt-que... 切换模式查看不同维度。

日志开启配置：给路口装上监控探头

想要治理堵车，第一件事不是修路，而是在每个路口装上监控探头。没有探头数据，你连哪里堵、堵多久、堵多严重都无从知晓。MySQL 的慢查询日志，就是数据库世界的“路口监控探头”——它把所有执行时间超过预设阈值的 SQL 语句，原原本本地记录到日志文件中。

开启它非常简单。在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中设置 slow_query_log = ON 打开开关，用 slow_query_log_file = /var/log/mysql-slow.log 指定日志落盘路径，再用 long_query_time = 1 告诉数据库“超过 1 秒就算慢”。注意这个参数的单位是秒且支持小数，0.5 就代表 500 毫秒。生产环境建议从 1 秒起步，观察日志量后再逐步收紧。

日志里的每条记录包含关键四要素：Query_time 是总执行时间，Lock_time 是锁等待耗时，Rows_examined 是扫描行数，Rows_sent 是实际返回行数。当 Rows_examined 远大于 Rows_sent 时，说明这条 SQL 翻遍全城只为送一份外卖——典型的无效扫描。

装上探头只是第一步，日志文件会像录像带一样越堆越多。下一步，我们需要一套能自动分析海量录像带的“指挥中心系统”。

日志分析工具：指挥中心的拥堵分析大屏

当全城的监控录像带堆积如山，你不可能一卷一卷人工回放。交警大队的指挥中心会把海量数据汇总成一块“大屏”——哪条路最堵、堵了多少次、集中在哪个时段，一目了然。在 MySQL 世界里，pt-query-digest 就是这块“大屏”。

它是 Percona Toolkit 工具包中最著名的慢查询分析工具。读取慢查询日志后，pt-query-digest 会把所有 SQL 按“指纹”聚合——所谓指纹，就是把具体值替换成占位符后的归一化模板，比如 SELECT * FROM users WHERE id = 1 和 WHERE id = 2 会归为同一类。然后统计每一类 SQL 的出现次数、总耗时、平均耗时、最大耗时，并生成图文报告。

报告中最重要的部分是 Query Review 表，它把 SQL 模板按总耗时从高到低排序。实际使用中，重点盯三个指标：总耗时（Sum）反映这类 SQL 对系统的总影响，与出现次数成正比；平均耗时（Avg）反映单次执行的性能；出现次数（Count）反映是否属于高频访问路径。总耗时和平均耗时双高，就是全村的“堵车王”。

大屏锁定了“堵车王”后，要找出它具体堵在哪个路口，还需要经验最丰富的交警到现场画一张勘探图——这就是 EXPLAIN 的舞台。

EXPLAIN定位：交警现场的勘探报告

指挥中心锁定嫌疑路段后，交警立即出警勘探现场——是红绿灯配时不合理？前方修路？还是路口没有方向标识？在数据库世界，EXPLAIN 就是这张“现场勘探报告”。你只需在 SELECT 语句前加上 EXPLAIN 关键字，MySQL 就会告诉你这条 SQL 将如何执行，而且不会真正运行它。

执行 EXPLAIN SELECT ... 后，MySQL 返回一张结果表，每一行描述一个表访问步骤。最值得看的四列是：id 执行顺序、type 访问类型、key 实际使用的索引、rows 估算扫描行数。type 是勘探报告的核心，从好到差依次为：system > const > eq_ref > ref > range > index > ALL。ALL 表示全表扫描，是报告里画满红叉的部分。

rows 列不是精确值，而是优化器基于统计信息估算的扫描行数，rows 越小说明访问路径越精准。type=ref 表示通过普通二级索引定位，是最常见的优化目标；type=range 表示索引范围扫描，多来自 BETWEEN 或 IN；type=ALL 则是必须处理的红色警报。结合 filtered 可以估算最终命中的行数比例。

勘探完毕，确认是“路口设计不合理”之后就可以动手了——在关键路口加装快速专用车道，这正是索引的用武之地。

索引优化策略：修建快速专用车道

堵车的路口经勘探发现，问题在于所有左转车辆和直行车辆混在一起抢道。解决办法是修一条“左转快速专用道”，让车辆进入后直接到达目的地。数据库里的索引正是这个机制：为某一列或某几列建立一份有序的目录，查询时像查字典一样直接翻到目标页，而不是逐页翻阅整本书。

MySQL 最常用的是 B+Tree 索引。当我们对多列建立复合索引（比如 INDEX(a,b,c)）时，就引出了最左前缀法则：查询必须先用到索引最左列 a，索引才会被启用。只要 a 在查询条件中，无论 b、c 是否出现，a 都能大幅减少扫描量。另外，覆盖索引指索引中已包含查询所需的全部列，可以免去回表（回到主键索引取完整行）的额外开销，相当于“高速终点直达”。

但加索引并非万能。索引失效的典型场景有：对索引列使用函数（如 WHERE YEAR(create_time)=2024）、隐式类型转换（字符串列与数值比较）、LIKE 以通配符开头（LIKE '%abc'）。建索引前还要评估列的选择性——如果某列只有“男/女”两种值，区分度太低，索引形同虚设。

车道修好后，如果司机的驾驶路线本身就很绕，依然快不起来。所以下一项工作，是为每一辆“慢车”重新规划最优行驶路线。

SQL改写技巧：重新规划行车路线

同一段路程，新手司机可能左拐右绕频繁变道，老司机却走一条大道直达。SQL 的写法也如此——同一个业务目标，不同写法的执行效率可能相差几十倍。SQL 改写，就是给数据库这辆车重新规划一条更高效的行驶路线。

最常见的改写有三类。第一，把 SELECT * 改成 SELECT 需要的列，减少数据传输量，毕竟没人会开车绕城一圈就为了买瓶水。第二，深分页优化——LIMIT 1000000, 10 会让数据库先扫描 100 万行再全部丢弃，等价于开车到终点才发现导航导错了。更优的写法是延迟关联：先只查主键，再回表取出需要的数据。第三，多表关联尽量用 JOIN 而非大量 IN 子查询，并遵循小表驱动大表原则。

LIMIT offset, size 的成本模型可以这样理解：总代价约等于扫描 offset + size 行的成本。当 offset 非常大时，主要开销来自前面 offset 行的无效扫描。改写策略是改用“上一次位置”（keyset pagination）：WHERE id > last_id ORDER BY id LIMIT 10，让扫描量恒定不变。

路线规划得再好，还得看导航软件够不够聪明——MySQL 优化器就是车里的那位导航员。理解它的思考方式，才能写出它真正听得懂的 SQL。

优化器原理：聪明导航员的算路逻辑

同样输入“去机场”，高德地图会综合实时路况、红绿灯数量、限行规则，给出多条路线并标注推荐。MySQL 优化器就是这样的“导航员”——执行 SQL 前，它先基于统计信息估算所有可选执行计划的代价，然后选择代价最小的一套方案。

代价估算的核心数据来自两张统计表：mysql.innodb_table_stats 存表行数等基础信息，mysql.innodb_index_stats 存索引基数（索引去重值的数量）和数据分布。MySQL 为顺序读、随机读、比较操作、临时表等操作定义了不同的“权重单价”。优化器用这套模型为每种执行路径打分，分越低越优先。

查询重写是优化器的隐藏技能。例如 WHERE id IN (1,2,3) 可能被重写为多个等值条件的组合；EXISTS 子查询在特定场景下被改写成半连接（semi-join）。理解优化器后你会发现，与其研究各种偏门技巧，不如做好两件基础事：定期 ANALYZE TABLE 保持统计信息新鲜；避免在 WHERE 子句中使用函数，否则索引很可能被“弃用”。

有了一次成功的路段改造，交警队不会就此收工——真正有经验的城市交通管理者，会把经验固化为制度，让今天的堵点明天不再复发。这正是慢查询治理实践体系的精髓。

治理实践体系：从救火到防火

治理堵车最有经验的城市，靠的不是每次堵车后派交警去现场疏通，而是一套长效机制：实时路况预警、新开路段前的车流模拟、季度性的交通规划复盘。慢查询治理同样需要这样的“成熟制度”。

治理体系的第一层是监控告警。把慢查询日志接入 Prometheus 等监控系统，一旦慢查询数量或某类 SQL 耗时超过阈值就触发告警，让“火势”还在苗头时就被扑灭。第二层是压测验证。改动索引或上线重构 SQL 之前，先在测试环境用 sysbench 模拟线上流量，对比优化前后的性能指标，而不是上线后等用户投诉。

第三层是迭代优化：每轮优化后都要收集新数据，回到“日志 → 分析 → 定位 → 优化”的循环起点。团队还应把常用优化沉淀为 SQL 规范，例如“禁止 SELECT *”“分页必须带排序字段”“上线前必须走 EXPLAIN 评审”——把个人经验变成组织制度。整个体系是一个不停旋转的闭环：发现问题、验证方案、实施优化、复盘沉淀。

到此，你已经从“堵车时手忙脚乱的交警”成长为“统筹全局的交通局长”。把方法论变成习惯，慢查询就不再是深夜事故的元凶，而是可控可量化的日常管理对象。

AI 应用场景

Python 演示

#!/usr/bin/env python3# -*- coding: utf-8 -*-"""MySQL slow query log analyzer: Top N slow SQL"""import refrom collections import defaultdict  class SlowLogParser:    def __init__(self, log_path):        self.log_path = log_path        self.queries = []        self.sql_stats = defaultdict(            lambda: {"count": 0, "total_time": 0.0}        )     def parse(self):        with open(self.log_path, "r", encoding="utf-8") as f:            exec_time = 0.0            sql_buf = ""            for line in f:                m = re.search(r"Query_time:\s+([\d.]+)", line)                if m:                    exec_time = float(m.group(1))                    continue                if not line.startswith("#") and exec_time > 0:                    sql_buf += line.strip() + " "                    if line.rstrip().endswith(";"):                        sql = sql_buf.strip()                        self._record(sql, exec_time)                        exec_time = 0.0                        sql_buf = ""     def _record(self, sql, exec_time):        sql_norm = re.sub(r"\d+", "?", sql)        sql_norm = re.sub(r"'.*?'", "?", sql_norm)        self.sql_stats[sql_norm]["count"] += 1        self.sql_stats[sql_norm]["total_time"] += exec_time        self.queries.append((sql_norm, exec_time))     def top_n(self, n=10):        items = sorted(            self.sql_stats.items(),            key=lambda kv: kv[1]["total_time"],            reverse=True        )[:n]        return [(sql, v["count"], v["total_time"]) for sql, v in items]     def report(self, n=10):        print(f"parsed {len(self.queries)} slow queries")        header = f"{'rank':<6}{'count':<8}{'total(s)':<12}sql template"        print(header)        print("-" * 70)        for i, (sql, cnt, total) in enumerate(self.top_n(n), 1):            preview = sql[:50] + ("..." if len(sql) > 50 else "")            print(f"{i:<6}{cnt:<8}{total:<12.2f}{preview}")  def main():    parser = SlowLogParser("mysql-slow.log")    parser.parse()    parser.report(10)  if __name__ == "__main__":    main()

1. m = re.search(r"Query_time:\s+([\d.]+)", line)从日志行中提取执行时间，这是判断“堵不堵”的直接证据。
2. sql_norm = re.sub(r"\d+", "?", sql)把 SQL 中的数字替换成占位符，完成“指纹归一化”，让只有数字不同的同类 SQL 聚到一起。
3. self.sql_stats[sql_norm]["count"] += 1累计该类 SQL 出现的次数，用于发现高频慢查询。
4. self.sql_stats[sql_norm]["total_time"] += exec_time累计该类 SQL 的总耗时，这是排名时最重要的依据。
5. key=lambda kv: kv[1]["total_time"]按总耗时从大到小排序，让“全城最大的堵点”排在最前面。
6. parser.parse()入口处执行解析，一行代码吃进整个日志文件。

常见误区

面试题