核心提要

在金融、电商等核心交易场景中，数据库慢查询是引发交易延迟（如支付卡顿、订单提交超时）的高频故障——一条未优化的 SQL 查询可能导致数据库 CPU/IO 飙升，进而引发交易链路级联阻塞。传统排查依赖运维人员手动分析慢查询日志、关联监控指标，平均定位时间超 30 分钟；而 AIOps（智能运维） 通过多源数据融合、异常检测、根因推理，可实现慢查询故障的 5 分钟内自动定位，同时输出优化建议，大幅降低运维成本。

本案例以电商交易系统 MySQL 慢查询导致订单提交延迟为场景，基于 Prometheus + SkyWalking + Elasticsearch + 机器学习模型 构建 AIOps 平台，完整拆解“数据采集→异常检测→根因定位→故障自愈”全流程，最终实现慢查询故障的端到端智能处置。

一、故障背景与传统排查痛点

1. 业务场景与故障现象

• 系统架构：电商交易系统（Java）→ MySQL 数据库（主从架构）→ Redis 缓存；核心链路：用户下单 → 库存扣减 → 订单入库 → 支付回调。

• 故障现象：某时段大量用户反馈订单提交超时（＞5s），交易系统报错 timeout waiting for database response；监控显示 MySQL 主库 CPU 使用率飙升至 95%，磁盘 IOPS 达到阈值，交易成功率从 99.9% 降至 85%。

• 故障根因：营销活动期间，某业务人员上线的统计 SQL（SELECT * FROM order WHERE create_time > 'xxx'）未加索引，且查询全量表数据（百万级），引发慢查询风暴。

2. 传统排查的核心痛点

痛点	具体表现	影响
监控数据孤立	交易延迟、数据库 CPU、慢查询日志分属不同监控系统，无关联分析	无法快速建立“慢查询→资源耗尽→交易延迟”的因果链路
异常检测滞后	依赖人工设置静态阈值（如 CPU＞80% 告警），对突发慢查询敏感度低	故障发生后 10 分钟才触发告警，错过最佳处置时间
根因定位效率低	运维人员需手动下载慢查询日志、分析 SQL 执行计划、关联监控指标	平均定位时间超 30 分钟，交易损失扩大
处置流程繁琐	需手动终止慢查询、添加索引、重启应用，无自动化自愈能力	故障恢复时间超 20 分钟，用户体验极差

二、AIOps 智能定位方案设计

1. 方案核心思路

AIOps 定位慢查询故障的核心是 “多源数据关联 + 异常检测 + 根因推理”，分为 4 个步骤：

1. 全链路数据采集：采集交易链路追踪数据、数据库性能指标、慢查询日志、系统资源监控数据；

2. 多维度异常检测：通过时序异常检测识别数据库指标异常，通过 SQL 指纹分析识别慢查询异常；

3. 根因自动推理：基于因果图谱算法，建立“慢查询→资源耗尽→交易延迟”的关联关系，定位根因 SQL；

4. 故障自愈建议：输出慢查询优化方案（如添加索引），支持自动执行或人工确认。

2. 技术栈选型

技术环节	工具选型	核心作用
全链路追踪	SkyWalking	采集交易链路调用链数据，标记数据库调用耗时节点
指标监控	Prometheus + mysqld_exporter	采集 MySQL 性能指标（CPU/IO/连接数/慢查询数）、系统资源指标
日志采集	Filebeat + Elasticsearch	采集 MySQL 慢查询日志，结构化存储并建立检索索引
特征提取	Python + Pandas	提取 SQL 指纹、执行时长、影响行数等特征
异常检测	孤立森林（IForest）+ 3σ 原则	检测时序指标异常、慢查询频次异常
根因推理	因果图谱（Causal Graph）	基于关联规则挖掘，建立指标与慢查询的因果关系
可视化	Grafana + Kibana	展示异常指标、慢查询详情、根因推理结果

3. 方案架构图

交易系统/数据库 → SkyWalking（链路追踪）→ AIOps 分析引擎
                → Prometheus（指标监控）→
                → Filebeat（慢查询日志）→
AIOps 分析引擎 → 异常检测模块 → 根因推理模块 → 可视化面板/自愈执行器

三、操作步骤

步骤 1：全链路数据采集与标准化

1. 数据采集配置

（1）数据库性能指标采集（Prometheus + mysqld_exporter）

1. 部署 mysqld_exporter，配置 MySQL 连接信息：

   docker run -d \
     --name mysqld-exporter \
     -p 9104:9104 \
     -e DATA_SOURCE_NAME="root:password@(mysql-ip:3306)/" \
     prom/mysqld-exporter:v0.14.0

2. 配置 Prometheus 采集任务（prometheus.yml）：

   scrape_configs:
     - job_name: 'mysql'
       static_configs:
         - targets: ['mysqld-exporter-ip:9104']
       scrape_interval: 10s  # 高频采集，确保捕捉瞬时异常

3. 核心采集指标：

   1. mysql_global_status_slow_queries：慢查询总数

   2. mysql_global_status_cpu_usage：数据库 CPU 使用率

   3. mysql_global_status_innodb_data_reads：磁盘读 IO 次数

   4. mysql_global_status_threads_running：活跃连接数

（2）慢查询日志采集（Filebeat + Elasticsearch）

1. 开启 MySQL 慢查询日志：

   SET GLOBAL slow_query_log = ON;
   SET GLOBAL long_query_time = 1;  # 执行时间＞1s 记录为慢查询
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

2. 配置 Filebeat 采集慢查询日志（filebeat.yml）：

   filebeat.inputs:
   - type: filestream
     paths:
       - /var/log/mysql/slow.log
     fields:
       log_type: mysql_slow_query
   output.elasticsearch:
     hosts: ["es-ip:9200"]
     index: "mysql-slow-query-%{+yyyy.MM.dd}"
   # 慢查询日志结构化处理（使用 ingest pipeline）
   processors:
     - dissect:
         tokenizer: "# Time: %{time}\n# User@Host: %{user}[%{uid}] @ %{host} [%{ip}]\n# Query_time: %{query_time:float}\n# Lock_time: %{lock_time:float}\n# Rows_sent: %{rows_sent:int}\n# Rows_examined: %{rows_examined:int}\nSET timestamp=%{timestamp};\n%{sql}"
         target_prefix: "mysql.slow"

3. 核心结构化字段：query_time（执行时长）、rows_examined（扫描行数）、sql（原始 SQL）、timestamp（执行时间）。

（4）MySQL 慢查询指纹分析：Filebeat Ingest Pipeline 配置

通过 Elasticsearch Ingest Pipeline 替代 Filebeat 本地解析，支持更复杂的慢查询日志结构化与指纹提取，适配不同格式的慢查询日志（如带存储引擎、执行计划的日志），同时减少 Filebeat 端资源消耗。

1. 创建 Ingest Pipeline（Kibana 或 Elasticsearch API 执行）

PUT _ingest/pipeline/mysql_slow_query_pipeline
{
  "description": "MySQL慢查询日志结构化与指纹提取Pipeline",
  "processors": [
    # 步骤1：解析慢查询日志基础字段（适配MySQL 5.7/8.0格式）
    {
      "dissect": {
        "field": "message",
        "ignore_failure": true,
        "pattern": "# Time: %{mysql.slow.time}\n# User@Host: %{mysql.slow.user}[%{mysql.slow.uid}] @ %{mysql.slow.host} [%{mysql.slow.client_ip}]\n# Query_time: %{mysql.slow.query_time:float}\n# Lock_time: %{mysql.slow.lock_time:float}\n# Rows_sent: %{mysql.slow.rows_sent:int}\n# Rows_examined: %{mysql.slow.rows_examined:int}\n# Rows_affected: %{mysql.slow.rows_affected:int}\n# Bytes_sent: %{mysql.slow.bytes_sent:int}\nSET timestamp=%{mysql.slow.timestamp:int};\n%{mysql.slow.sql}",
        "target_prefix": "mysql.slow"
      }
    },
    # 步骤2：适配带执行计划的慢查询日志（补充解析）
    {
      "dissect": {
        "field": "message",
        "ignore_failure": true,
        "pattern": "# Time: %{mysql.slow.time}\n# User@Host: %{mysql.slow.user}[%{mysql.slow.uid}] @ %{mysql.slow.host} [%{mysql.slow.client_ip}]\n# Query_time: %{mysql.slow.query_time:float}\n# Lock_time: %{mysql.slow.lock_time:float}\n# Rows_sent: %{mysql.slow.rows_sent:int}\n# Rows_examined: %{mysql.slow.rows_examined:int}\n# Rows_affected: %{mysql.slow.rows_affected:int}\n# Bytes_sent: %{mysql.slow.bytes_sent:int}\n# InnoDB_trx_id: %{mysql.slow.innodb_trx_id}\nSET timestamp=%{mysql.slow.timestamp:int};\n%{mysql.slow.sql}",
        "target_prefix": "mysql.slow"
      }
    },
    # 步骤3：SQL指纹提取（替换常量、格式化SQL，支持复杂SQL）
    {
      "script": {
        "description": "SQL指纹生成：替换字符串、数字常量，统一格式",
        "lang": "painless",
        "source": """
          if (ctx.mysql?.slow?.sql != null) {
            String sql = ctx.mysql.slow.sql.toLowerCase().trim();
            // 1. 替换字符串常量（单引号/双引号包裹内容）
            sql = sql.replaceAll(/(['"]).*?\1/, "?");
            // 2. 替换数字常量（整数、小数、科学计数法）
            sql = sql.replaceAll(/\b\d+(\.\d+)?(e[+-]?\d+)?\b/, "?");
            // 3. 替换日期时间常量
            sql = sql.replaceAll(/\b\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}\b/, "?");
            // 4. 去除多余空格、换行
            sql = sql.replaceAll(/\s+/, " ").replaceAll(/^\s+|\s+$/, "");
            // 5. 统一LIMIT语法（避免参数位置差异导致指纹不一致）
            sql = sql.replaceAll(/limit\s+\?\s*,\s*\?/, "limit ?, ?");
            // 6. 存储指纹与原始SQL
            ctx.mysql.slow.sql_fingerprint = sql;
            ctx.mysql.slow.original_sql = ctx.mysql.slow.sql;
            // 7. 计算SQL长度
            ctx.mysql.slow.sql_length = sql.length();
          }
        """,
        "ignore_failure": true
      }
    },
    # 步骤4：计算SQL效率指标（扫描行数/返回行数，判断是否走索引）
    {
      "script": {
        "description": "计算SQL效率指标，标记无索引SQL",
        "lang": "painless",
        "source": """
          if (ctx.mysql?.slow?.rows_sent != null && ctx.mysql?.slow?.rows_examined != null) {
            int rowsSent = ctx.mysql.slow.rows_sent;
            int rowsExamined = ctx.mysql.slow.rows_examined;
            // 避免除零异常
            if (rowsSent > 0) {
              ctx.mysql.slow.examine_sent_ratio = (double) rowsExamined / rowsSent;
              // 扫描行数/返回行数>1000，标记为可能无索引
              ctx.mysql.slow.no_index_flag = ctx.mysql.slow.examine_sent_ratio > 1000 ? true : false;
            } else {
              ctx.mysql.slow.examine_sent_ratio = (double) rowsExamined;
              ctx.mysql.slow.no_index_flag = rowsExamined > 10000 ? true : false;
            }
          }
        """,
        "ignore_failure": true
      }
    },
    # 步骤5：时间格式标准化（转为ISO格式，便于时间范围查询）
    {
      "date": {
        "field": "mysql.slow.timestamp",
        "target_field": "mysql.slow.execution_time",
        "formats": ["epoch_second"],
        "ignore_failure": true
      }
    },
    # 步骤6：添加标签字段（便于日志分类检索）
    {
      "set": {
        "field": "log_type",
        "value": "mysql_slow_query",
        "ignore_failure": true
      }
    },
    # 步骤7：过滤无效日志（无SQL内容或执行时长<1s的日志）
    {
      "drop": {
        "if": "ctx.mysql?.slow?.query_time == null || ctx.mysql.slow.query_time < 1 || ctx.mysql?.slow?.sql == null",
        "ignore_failure": true
      }
    }
  ],
  # 错误处理：保留原始日志，标记错误状态
  "on_failure": [
    {
      "set": {
        "field": "ingest_error",
        "value": "{{ _ingest.on_failure_message }}"
      }
    },
    {
      "set": {
        "field": "log_type",
        "value": "mysql_slow_query_error"
      }
    }
  ]
}

2. 配置 Filebeat 输出至 Elasticsearch 并关联 Pipeline

修改 Filebeat 配置，关闭本地 dissect 处理器，指定 Ingest Pipeline 处理日志：

filebeat.inputs:
- type: filestream
  paths:
    - /var/log/mysql/slow.log
  fields:
    log_source: mysql_master  # 标记日志来源（主库/从库）
  processors:
    - drop_fields:
        fields: ["input.type", "agent.version"]  # 过滤无用字段
        ignore_missing: true

output.elasticsearch:
  hosts: ["es-ip:9200"]
  index: "mysql-slow-query-%{+yyyy.MM.dd}"
  # 关联Ingest Pipeline
  pipeline: "mysql_slow_query_pipeline"
  # 认证配置（生产环境必备）
  username: "${ES_USERNAME}"
  password: "${ES_PASSWORD}"
  ssl.certificate_authorities: ["/etc/filebeat/es-ca.crt"]

setup.template.enabled: true
setup.template.name: "mysql-slow-query"
setup.template.pattern: "mysql-slow-query-*"
setup.ilm.enabled: true
setup.ilm.policy_name: "mysql-slow-query-policy"

3. 验证 Pipeline 效果

1. 查看结构化后的日志字段（Kibana Dev Tools 执行）：

GET mysql-slow-query-*/_search
{
  "query": {
    "match": { "log_type": "mysql_slow_query" }
  },
  "fields": [
    "mysql.slow.sql_fingerprint",
    "mysql.slow.query_time",
    "mysql.slow.examine_sent_ratio",
    "mysql.slow.no_index_flag",
    "mysql.slow.execution_time"
  ],
  "size": 10
}

2. 预期结构化结果：

{
  "hits": {
    "hits": [
      {
        "_source": {
          "mysql": {
            "slow": {
              "sql_fingerprint": "select * from order where create_time > ?",
              "query_time": 15.2,
              "examine_sent_ratio": 10000.0,
              "no_index_flag": true,
              "execution_time": "2026-01-20T14:35:22.000Z",
              "original_sql": "SELECT * FROM order WHERE create_time > '2026-01-20 14:00:00'"
            }
          },
          "log_type": "mysql_slow_query",
          "log_source": "mysql_master"
        }
      }
    ]
  }
}

4. 生产环境优化建议

• 适配多版本日志：若存在 MySQL 5.6/5.7/8.0 混合部署，可在 Pipeline 中添加多个 dissect 处理器，通过 ignore_failure: true 自动适配；

• 性能优化：Ingest Pipeline 执行在 Elasticsearch 节点，建议单独部署 Ingest 节点，避免影响数据存储与查询性能；

• 指纹去重：结合 Elasticsearch 聚合功能，按mysql.slow.sql_fingerprint 聚合，统计相同指纹慢查询的频次与耗时分布；

• 定期更新：当业务 SQL 语法变化时，需同步优化 Pipeline 中的脚本逻辑，确保指纹提取准确性。

（3）全链路追踪采集（SkyWalking）

1. 部署 SkyWalking OAP 与 UI，在交易系统中接入 SkyWalking Agent；

2. 配置 Agent 追踪数据库调用：

   agent.service_name=e-commerce-trade
   collector.backend_service=skywalking-oap-ip:11800
   # 开启数据库调用追踪
   plugin.mysql.trace_sql_parameters=true

3. 核心采集数据：交易链路中每个节点的耗时（如 order-db 节点耗时）、调用次数、异常次数。

2. 数据标准化与融合

将三类数据按 时间戳 与 业务 ID 进行关联，生成统一的故障分析数据集，格式示例：

{
  "timestamp": "2026-01-20 14:30:00",
  "trace_id": "trace-123456",
  "trade耗时": 5200,  // 单位 ms
  "db调用耗时": 4800,
  "mysql_cpu": 95,
  "slow_query_count": 120,
  "slow_sql_fingerprint": "SELECT * FROM order WHERE create_time > ?",
  "query_time": 15.2,
  "rows_examined": 1000000
}

步骤 2：多维度异常检测

1. 时序指标异常检测（孤立森林算法）

针对 MySQL CPU、慢查询数、交易耗时等时序指标，使用孤立森林（IForest） 检测异常——该算法适合识别非线性、非平稳时序数据中的异常值，无需标注样本。

（1）训练异常检测模型（完整可运行代码）

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from sklearn.ensemble import IsolationForest
from sklearn.preprocessing import StandardScaler
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')

# 1. 模拟时序指标数据（模拟2小时数据，含10分钟慢查询故障时段）
def generate_metrics_data():
    # 时间序列：从2026-01-20 14:00开始，每10秒一条数据
    start_time = datetime(2026, 1, 20, 14, 0, 0)
    time_points = [start_time + timedelta(seconds=i*10) for i in range(720)]  # 720条=2小时
    df = pd.DataFrame({'timestamp': time_points})
    
    # 正常时段数据（14:00-14:30，14:40-16:00）
    normal_cpu = np.random.normal(loc=30, scale=5, size=len(df))  # 正常CPU均值30%
    normal_slow_query = np.random.poisson(lam=2, size=len(df))  # 正常慢查询数2次/分钟
    normal_trade_time = np.random.normal(loc=800, scale=100, size=len(df))  # 正常交易耗时800ms
    
    # 故障时段数据（14:30-14:40，共60条数据）
    fault_start_idx = 180  # 14:30对应索引（180*10秒=30分钟）
    fault_end_idx = 240    # 14:40对应索引
    normal_cpu[fault_start_idx:fault_end_idx] = np.random.normal(loc=90, scale=3, size=60)  # CPU飙升至90%
    normal_slow_query[fault_start_idx:fault_end_idx] = np.random.poisson(lam=30, size=60)  # 慢查询30次/分钟
    normal_trade_time[fault_start_idx:fault_end_idx] = np.random.normal(loc=5000, scale=500, size=60)  # 交易耗时5s
    
    # 数据规整（确保指标合理范围）
    df['mysql_cpu'] = np.clip(normal_cpu, 10, 100)  # CPU限制在10%-100%
    df['slow_query_count'] = np.clip(normal_slow_query, 0, 50)  # 慢查询数限制在0-50
    df['trade耗时'] = np.clip(normal_trade_time, 500, 6000)  # 交易耗时限制在500ms-6s
    
    return df

# 2. 加载并预处理数据
df = generate_metrics_data()
features = ["mysql_cpu", "slow_query_count", "trade耗时"]
X = df[features]

# 3. 数据标准化（消除量纲影响）
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)
df[['cpu_scaled', 'slow_query_scaled', 'trade_time_scaled']] = X_scaled

# 4. 训练孤立森林模型
model = IsolationForest(
    n_estimators=100,  # 决策树数量
    contamination=0.05,  # 异常比例（5%），适配故障时段占比
    max_samples='auto',
    random_state=42
)
df["anomaly"] = model.fit_predict(X_scaled)  # -1为异常，1为正常
df["anomaly_score"] = model.decision_function(X_scaled)  # 异常得分（越低越异常）

# 5. 异常结果标注
df['is_fault'] = 0
df.loc[180:240, 'is_fault'] = 1  # 标记真实故障时段
df['detect_result'] = np.where(df['anomaly'] == -1, 1, 0)  # 检测结果（1=异常，0=正常）

# 6. 模型效果评估
true_positive = ((df['detect_result'] == 1) & (df['is_fault'] == 1)).sum()
false_positive = ((df['detect_result'] == 1) & (df['is_fault'] == 0)).sum()
true_negative = ((df['detect_result'] == 0) & (df['is_fault'] == 0)).sum()
false_negative = ((df['detect_result'] == 0) & (df['is_fault'] == 1)).sum()

precision = true_positive / (true_positive + false_positive) if (true_positive + false_positive) > 0 else 0
recall = true_positive / (true_positive + false_negative) if (true_positive + false_negative) > 0 else 0
f1 = 2 * precision * recall / (precision + recall) if (precision + recall) > 0 else 0

print("=== 异常检测模型效果评估 ===")
print(f"精确率（Precision）：{precision:.2%}")
print(f"召回率（Recall）：{recall:.2%}")
print(f"F1分数：{f1:.2%}")
print(f"故障时段检测覆盖率：{true_positive/60:.2%}")

# 7. 异常结果可视化
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.figure(figsize=(15, 10))

# 子图1：MySQL CPU使用率与异常标记
plt.subplot(3, 1, 1)
plt.plot(df['timestamp'], df['mysql_cpu'], label='CPU使用率（%）', color='#1f77b4', linewidth=1.5)
plt.scatter(df[df['detect_result']==1]['timestamp'], df[df['detect_result']==1]['mysql_cpu'], 
            color='red', s=20, label='检测到的异常', zorder=5)
plt.axvspan(df['timestamp'].iloc[180], df['timestamp'].iloc[240], alpha=0.2, color='orange', label='真实故障时段')
plt.axhline(y=80, color='red', linestyle='--', alpha=0.7, label='CPU阈值（80%）')
plt.title('MySQL CPU使用率与时序异常检测结果', fontsize=12, fontweight='bold')
plt.ylabel('CPU使用率（%）')
plt.legend(fontsize=10)
plt.grid(alpha=0.3)

# 子图2：慢查询数与异常标记
plt.subplot(3, 1, 2)
plt.plot(df['timestamp'], df['slow_query_count'], label='慢查询数（次/10秒）', color='#ff7f0e', linewidth=1.5)
plt.scatter(df[df['detect_result']==1]['timestamp'], df[df['detect_result']==1]['slow_query_count'], 
            color='red', s=20, label='检测到的异常', zorder=5)
plt.axvspan(df['timestamp'].iloc[180], df['timestamp'].iloc[240], alpha=0.2, color='orange', label='真实故障时段')
plt.axhline(y=10, color='red', linestyle='--', alpha=0.7, label='慢查询阈值（10次/10秒）')
plt.title('慢查询数与时序异常检测结果', fontsize=12, fontweight='bold')
plt.ylabel('慢查询数（次/10秒）')
plt.legend(fontsize=10)
plt.grid(alpha=0.3)

# 子图3：交易耗时与异常标记
plt.subplot(3, 1, 3)
plt.plot(df['timestamp'], df['trade耗时'], label='交易耗时（ms）', color='#2ca02c', linewidth=1.5)
plt.scatter(df[df['detect_result']==1]['timestamp'], df[df['detect_result']==1]['trade耗时'], 
            color='red', s=20, label='检测到的异常', zorder=5)
plt.axvspan(df['timestamp'].iloc[180], df['timestamp'].iloc[240], alpha=0.2, color='orange', label='真实故障时段')
plt.axhline(y=3000, color='red', linestyle='--', alpha=0.7, label='交易耗时阈值（3000ms）')
plt.title('交易耗时与时序异常检测结果', fontsize=12, fontweight='bold')
plt.ylabel('交易耗时（ms）')
plt.xlabel('时间')
plt.legend(fontsize=10)
plt.grid(alpha=0.3)

plt.tight_layout()
plt.savefig('anomaly_detection_result.png', dpi=300, bbox_inches='tight')
plt.close()

# 8. 输出异常数据详情
anomaly_detail = df[df['detect_result']==1][['timestamp', 'mysql_cpu', 'slow_query_count', 'trade耗时', 'anomaly_score']]
print("\n=== 异常数据详情（前10条） ===")
print(anomaly_detail.head(10).to_string(index=False))

# 9. 模型持久化（用于线上部署）
import joblib
joblib.dump(model, 'isolation_forest_model.pkl')
joblib.dump(scaler, 'standard_scaler.pkl')
print("\n模型已保存为：isolation_forest_model.pkl、standard_scaler.pkl")

代码说明：① 模拟含真实故障时段的时序数据，贴近生产场景；② 加入模型效果评估（精确率、召回率），确保检测可靠性；③ 生成可视化图表，直观展示异常检测结果；④ 支持模型持久化，可直接部署到AIOps平台线上运行。

（2）异常触发条件

当满足以下 2 个及以上条件时，判定为指标异常：

• 孤立森林模型标记为异常；

• 交易耗时＞3s（超过正常阈值 3 倍）；

• 慢查询数＞50/分钟（较基线增长 10 倍）。

2. 慢查询 SQL 异常检测（指纹分析 + 频次统计）

（1）SQL 指纹提取

对慢查询日志中的 SQL 进行指纹化处理，合并相同结构的 SQL（参数不同但结构相同），例如：

• 原始 SQL1：SELECT * FROM order WHERE create_time > '2026-01-01'

• 原始 SQL2：SELECT * FROM order WHERE create_time > '2026-01-02'

• 指纹化后：SELECT * FROM order WHERE create_time > ?

实现代码（Python + sqlparse 库）：

import sqlparse

def sql_fingerprint(sql):
    # 解析 SQL，替换常量为 ?
    parsed = sqlparse.parse(sql)[0]
    for token in parsed.flatten():
        if token.ttype in [sqlparse.tokens.Literal.String, sqlparse.tokens.Literal.Number]:
            token.value = "?"
    return str(parsed).strip()

（2）慢查询异常判定

当某一 SQL 指纹满足以下条件时，判定为异常慢查询：

• 平均执行时长＞10s；

• 每分钟执行次数＞20 次；

• 扫描行数（rows_examined）/ 返回行数（rows_sent）＞1000（说明未走索引）。

步骤 3：根因自动推理（因果图谱算法）

1. 构建故障因果图谱

基于历史故障数据与关联规则挖掘，构建**“指标-慢查询-故障”** 的因果图谱，核心节点与关系如下：

• 故障节点：交易延迟（trade_delay＞5s）；

• 中间节点：MySQL CPU 飙升（cpu＞90%）、磁盘 IO 饱和（iops＞阈值）；

• 根因节点：异常慢查询 SQL（指纹 X）；

• 因果关系：异常慢查询 → MySQL 资源耗尽 → 交易延迟。

2. 根因推理算法流程

1. 异常事件关联：将时序异常（CPU 飙升）与慢查询异常（指纹 X）按时间戳关联，确认两者发生时间一致；

2. 因果强度计算：计算慢查询频次与 CPU 使用率的 Pearson 相关系数，若系数＞0.8，判定为强相关；

3. 链路耗时验证：从 SkyWalking 链路数据中，提取包含该慢查询的交易链路，确认数据库调用耗时占比＞90%；

4. 根因确认：综合以上 3 点，判定该慢查询 SQL 为交易延迟的根因。

3. 推理结果输出

自动生成根因分析报告，示例如下：

【故障根因定位报告】
1. 故障现象：2026-01-20 14:30 至 14:40，订单提交平均耗时 5.2s，成功率 85%
2. 异常指标：MySQL 主库 CPU 使用率 95%，慢查询数 120 次/分钟
3. 根因 SQL 指纹：SELECT * FROM order WHERE create_time > ?
   - 平均执行时长：15.2s
   - 扫描行数：1000000 行
   - 执行频次：30 次/分钟
4. 因果关系：该 SQL 未加索引，全表扫描导致 CPU/IO 耗尽，引发交易链路阻塞

步骤 4：故障自愈与优化建议

1. 自动处置策略

AIOps 平台支持手动确认执行与自动执行两种自愈模式，针对慢查询故障的处置步骤：

1. 紧急止损：自动生成 KILL 命令，终止所有该指纹的慢查询进程：

   -- 生成的 KILL 命令示例
   SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE info LIKE 'SELECT * FROM order WHERE create_time > %';

2. 优化建议生成：基于 SQL 指纹，自动推荐索引添加方案：

   -- 自动生成的索引建议
   ALTER TABLE `order` ADD INDEX idx_create_time (`create_time`);

3. 效果验证：执行索引添加后，监控慢查询数与 CPU 使用率是否回落，交易耗时是否恢复正常。

2. 长效优化建议

1. SQL 审核机制：在 CI/CD 流水线中接入 SQL 审核工具（如 pt-online-schema-change），禁止无索引的全表查询上线；

2. 慢查询阈值动态调整：基于业务峰值自动调整 long_query_time（如促销期间调整为 0.5s）；

3. 资源弹性扩容：配置数据库 CPU/IO 阈值触发的弹性扩容策略，应对突发流量。

四、故障处置效果对比

指标	传统人工处置	AIOps 智能处置	优化效果
异常检测延迟	10 分钟	10 秒	降低 98.3%
根因定位时间	30 分钟	5 分钟	降低 83.3%
故障恢复时间	20 分钟	3 分钟	降低 85%
交易损失	严重	可忽略	降低 99%

五、核心经验总结

1. 多源数据融合是基础：慢查询故障定位需结合链路、指标、日志三类数据，孤立数据无法构建完整的因果链路；

2. 机器学习提升异常检测精度：孤立森林等无监督算法比静态阈值更适合检测突发慢查询引发的非线性异常；

3. 因果推理而非关联分析：AIOps 核心是“因果”而非“关联”——需通过算法确认慢查询是资源耗尽的因，而非伴随现象；

4. 自愈能力是终极目标：从“自动定位”到“自动处置”是 AIOps 的核心价值，需建立完善的执行权限管控机制（如人工确认后执行）。

附：常见问题排查

1. 异常检测误报率高：优化孤立森林的 contamination 参数，增加多指标联合判定条件，过滤单点指标异常；

2. SQL 指纹提取不准确：优化 sqlparse 解析规则，处理复杂 SQL（如子查询、联表查询）的指纹化；

3. 根因推理错误：增加历史故障案例库，基于规则库修正机器学习推理结果，提升准确率；

4. 自愈执行失败：检查数据库权限（如 KILL 权限、索引添加权限），配置执行超时与回滚机制。