用 ELK 定位慢查询：从日志采集到 3 秒内找到问题 SQL

目录:

痛点：日志里有答案，但你找不到
方案：ELK 慢查询分析管道
实操步骤
3 个常见坑
总结

线上数据库突然变慢，DBA 在几百 GB 日志里翻了两小时才找到根因。这篇文章用 ELK 堆栈把"人肉翻日志"变成"3 秒定位"。

痛点：日志里有答案，但你找不到

一个真实场景：某电商平台大促前夜，MySQL 响应时间从 50ms 飙到 3s。运维看监控只知道"数据库慢了"，但具体是哪条 SQL、哪个表、什么时间开始的？答案藏在 slow query log 里——一个 80GB 的纯文本文件。

用 grep 翻？太慢。用 pt-query-digest 汇总？只有统计没有时间线。真正需要的是：实时采集、结构化解析、秒级检索、可视化趋势。

这就是 ELK（Elasticsearch + Logstash + Kibana）的战场。

方案：ELK 慢查询分析管道

架构很简单：

MySQL slow log → Filebeat → Logstash（解析+结构化）→ Elasticsearch → Kibana

核心思路：用 Filebeat 实时采集慢查询日志，Logstash 用 grok 把非结构化的日志拆成字段（执行时间、SQL、库名、时间戳），写入 ES 后在 Kibana 里做检索和可视化。

实操步骤

第 1 步：开启 MySQL 慢查询日志

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 没走索引也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

写入 my.cnf 确保重启不丢：

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

第 2 步：Filebeat 采集配置

# /etc/filebeat/filebeat.yml
filebeat.inputs:
  - type: log
    enabled: true
    paths:
      - /var/log/mysql/slow.log
    # 慢查询日志是多行格式，用 pattern 合并
    multiline.pattern: '^# Time:|^# User@Host:'
    multiline.negate: true
    multiline.match: after
    fields:
      log_type: mysql_slow
    fields_under_root: true

output.logstash:
  hosts: ["logstash-server:5044"]

关键点：MySQL 慢查询日志一条记录占多行，必须配 multiline 把它们合并成一条事件，否则后面解析全乱。

第 3 步：Logstash 解析管道

# /etc/logstash/conf.d/mysql-slow.conf
input {
  beats {
    port => 5044
  }
}

filter {
  if [log_type] == "mysql_slow" {
    grok {
      match => {
        "message" => [
          "# User@Host: %{USER:db_user}\[%{USER}\] @\s+\[%{IP:client_ip}?\]",
          "# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}"
        ]
      }
    }

    # 提取 SQL 语句（最后一段非注释内容）
    grok {
      match => {
        "message" => "(?m)SET timestamp=%{NUMBER:mysql_timestamp};\n%{GREEDYDATA:sql_statement}"
      }
    }

    # 用 MySQL 的 timestamp 作为事件时间
    date {
      match => ["mysql_timestamp", "UNIX"]
      target => "@timestamp"
    }

    # SQL 指纹：去掉具体值，便于聚合
    ruby {
      code => '
        sql = event.get("sql_statement").to_s
        fingerprint = sql.gsub(/\b\d+\b/, "N")
                        .gsub(/"[^"]*"/, "S")
                        .gsub(/'"'"'[^'"'"']*'"'"'/, "S")
                        .strip
        event.set("sql_fingerprint", fingerprint)
      '
    }

    mutate {
      remove_field => ["message", "host", "agent"]
    }
  }
}

output {
  if [log_type] == "mysql_slow" {
    elasticsearch {
      hosts => ["http://es-server:9200"]
      index => "mysql-slow-%{+YYYY.MM.dd}"
    }
  }
}

这段配置做了四件事： 1. grok 解析 — 提取用户、IP、执行时间、锁时间、扫描行数 2. SQL 提取 — 拿到完整 SQL 语句 3. 时间校正 — 用 MySQL 记录的时间戳而非采集时间 4. SQL 指纹 — 把 WHERE id = 12345 归一化为 WHERE id = N，便于按模式聚合

第 4 步：Kibana 可视化

在 Kibana 创建 Index Pattern mysql-slow-*，然后建一个 Dashboard：

Top 10 慢 SQL（按指纹聚合）：Terms 聚合 sql_fingerprint，排序 avg(query_time) 降序
慢查询趋势图：Date Histogram + query_time 的 avg/p95/max
扫描行数 vs 返回行数：Scatter Plot，rows_examined 对 rows_sent，比值大说明缺索引
来源 IP 分布：Pie Chart，看哪个服务打的慢查询最多

现在回到开头的场景：大促前夜数据库变慢，打开 Kibana，时间范围拉到最近 1 小时，按 query_time 降序排——3 秒内就能看到：某条 SELECT * FROM orders WHERE user_id = N AND status = S 执行了 4.7 秒，扫描了 1200 万行但只返回 3 行。根因清楚了：user_id + status 缺联合索引。

3 个常见坑

坑 1：Filebeat multiline 配没配对，日志全碎

MySQL 慢查询日志的格式是：

# Time: 2026-04-23T05:12:33.123456Z
# User@Host: app[app] @  [10.0.1.5]
# Query_time: 4.731  Lock_time: 0.000 ...
SET timestamp=1745384553;
SELECT * FROM orders WHERE ...;

如果 multiline.pattern 写错，每一行都变成独立事件，grok 解析全失败。测试方法：先用 filebeat test output 验证连通性，再看 Logstash 日志有没有 _grokparsefailure 标签。

坑 2：ES 索引膨胀，磁盘爆掉

慢查询日志量大的时候，ES 每天几个 GB 很正常。必须配 ILM（Index Lifecycle Management）：

PUT _ilm/policy/mysql-slow-policy
{
  "policy": {
    "phases": {
      "hot":    { "actions": { "rollover": { "max_size": "10gb" }}},
      "warm":   { "min_age": "7d",  "actions": { "shrink": { "number_of_shards": 1 }}},
      "delete": { "min_age": "30d", "actions": { "delete": {} }}
    }
  }
}

保留 30 天足够回溯，超期自动清理。别等磁盘 95% 了再想起来。

坑 3：SQL 指纹不做归一化，Top N 全是同一条 SQL 的不同参数

不做指纹处理的话，WHERE id = 1 和 WHERE id = 2 是两条不同的记录，你永远看不到"这类查询整体很慢"。上面 Logstash 里的 ruby filter 就是解决这个问题的——把数字替换成 N，字符串替换成 S，相同模式的 SQL 归为一类。

总结

步骤	组件	作用
采集	Filebeat + multiline	实时读取慢查询日志，多行合并
解析	Logstash grok + ruby	结构化提取字段 + SQL 指纹
存储	Elasticsearch + ILM	索引存储 + 生命周期管理
展示	Kibana Dashboard	秒级检索 + 趋势分析

从"80GB 日志翻两小时"到"Kibana 3 秒定位"，ELK 不是银弹，但在日志分析这个场景里确实好用。关键是：采集别丢、解析别乱、存储别炸、检索要快。

搭好这套管道后，下次数据库告警你不用再 SSH 上去 grep 了——打开浏览器就行。

饮墨

子安饮墨馀三斗，留与卿儿作赋来