MySQLスロークエリーログを集計・可視化する

## 概要

MySQLやMariaDBを運用していると、**スロークエリーログ**（`slow.log`）の分析は欠かせません。しかし、標準ではそのままでは見づらく、分析しづらい形式になっています。

そこで、スロークエリーログを**集計してテーブル単位で可視化するスクリプト**を作成しました。
実行結果を**MarkdownまたはCSV形式**で出力でき、日付やテーブルでの絞り込みも可能です。

## スクリプトの紹介

### スクリプト

```bash:aggregate-slowlog.sh
#!/bin/bash

FORMAT="md"
ENCODING="utf8"
INCLUDE_SAMPLE=false
FROM_EPOCH=0
TO_EPOCH=32503680000
FILTER_TABLES=()
OUTPUT_DIR=""

usage() {
  echo "Usage: $0 --path=/path/to/slow.log [--output=/path/to/outputdir] [--format=md|csv] [--encoding=utf8|utf8-bom|shift_jis] [--detail] [--start='YYYYMMDD [hh[:mm[:ss]]'] [--end='YYYYMMDD [hh[:mm[:ss]]'] [--table=table1,table2]"
  exit 1
}

parse_datetime() {
  input="$1"
  if [[ "$input" =~ ^([0-9]{8})([[:space:]]+([0-9]{1,2})(:([0-9]{1,2}))?(:([0-9]{1,2}))?)?$ ]]; then
    y=${BASH_REMATCH[1]:0:4}
    m=${BASH_REMATCH[1]:4:2}
    d=${BASH_REMATCH[1]:6:2}
    H=${BASH_REMATCH[3]:-0}
    M=${BASH_REMATCH[5]:-0}
    S=${BASH_REMATCH[7]:-0}
    printf -v timestamp "%s-%s-%s %02d:%02d:%02d" "$y" "$m" "$d" "$H" "$M" "$S"
    date -d "$timestamp" +%s 2>/dev/null
    if [ $? -ne 0 ]; then
      echo "Invalid date: $input" >&2
      exit 1
    fi
  else
    echo "Invalid datetime format: $input" >&2
    exit 1
  fi
}

for arg in "$@"; do
  case $arg in
    --path=*) SLOWLOG="${arg#*=}" ;;
    --output=*) OUTPUT_DIR="${arg#*=}" ;;
    --format=csv|--format=md) FORMAT="${arg#*=}" ;;
    --encoding=*) ENCODING="${arg#*=}" ;;
    --detail) INCLUDE_SAMPLE=true ;;
    --start=*) FROM_EPOCH=$(parse_datetime "${arg#*=}") ;;
    --end=*) TO_EPOCH=$(parse_datetime "${arg#*=}") ;;
    --table=*) IFS=',' read -ra FILTER_TABLES <<< "${arg#*=}" ;;
    *) usage ;;
  esac
done

if [ -z "$SLOWLOG" ]; then echo "Error: --path is required."; usage; fi
if [ ! -f "$SLOWLOG" ]; then echo "Error: File '$SLOWLOG' not found."; exit 1; fi
if [ -n "$OUTPUT_DIR" ] && [ ! -d "$OUTPUT_DIR" ]; then echo "Error: Output directory '$OUTPUT_DIR' not found."; exit 1; fi

TMP_DIR=$(mktemp -d)
PARSED="$TMP_DIR/parsed.log"

# ファイル名生成（logファイル名_YYYYMMDDHHMMSS）
BASENAME=$(basename "$SLOWLOG")
NOW=$(date +%Y%m%d%H%M%S)
FILENAME="${BASENAME}_$NOW.$FORMAT"
OUTFILE="$TMP_DIR/$FILENAME"

awk -v from="$FROM_EPOCH" -v to="$TO_EPOCH" '
function to_epoch(y, mo, d, h, mi,    cmd, result) {
  cmd = "date -d \"" y "-" mo "-" d " " h ":" mi "\" +%s"
  cmd | getline result
  close(cmd)
  return result
}
/^# Time: / {
  raw = $3
  y = "20" substr(raw,1,2)
  mo = substr(raw,3,2)
  d = substr(raw,5,2)
  h = (length(raw) >= 8 ? substr(raw,8,2) : "00")
  mi = (length(raw) >= 10 ? substr(raw,10,2) : "00")
  log_epoch = to_epoch(y,mo,d,h,mi)
  keep = (log_epoch >= from && log_epoch <= to)
}
/^# Query_time:/ { qt = $3 }
/^use / { db = $2; gsub(/[`;]/, "", db) }
/^SET timestamp=/ { next }
/^SELECT/ || /^INSERT/ || /^UPDATE/ || /^DELETE/ {
  if (!keep) next
  query=$0
  while ((getline line) > 0) {
    if (line ~ /^#/) break
    query = query " " line
  }
  print qt "|" db "|" query
}
' "$SLOWLOG" > "$PARSED"

awk -v format="$FORMAT" -v include_sample="$INCLUDE_SAMPLE" -v tables_filter="${FILTER_TABLES[*]}" -F'|' '
function extract_tables(sql, arr,    lower, i, tbls, tbl) {
  lower = tolower(sql)
  n = split(lower, arr, /(from|join|update|into)/)
  tbls = ""
  for (i = 2; i <= n; i++) {
    if (match(arr[i], /[ \t\n\r`]*([a-zA-Z_][a-zA-Z0-9_\.]*)/, m)) {
      tbl = m[1]
      sub(/\..*$/, "", tbl)
      if (!(tbl in seen)) {
        seen[tbl] = 1
        tbls = (tbls == "" ? tbl : tbls "," tbl)
      }
    }
  }
  delete seen
  return (tbls == "" ? "unknown" : tbls)
}
BEGIN {
  n_filters = split(tables_filter, filters, /[, ]+/)
}
{
  qt = $1 + 0
  db = $2
  q = $3
  tbls = extract_tables(q, parts)
  matched = (n_filters == 0)
  if (!matched) {
    for (f = 1; f <= n_filters; f++) {
      if (index(tbls, filters[f]) > 0) {
        matched = 1
        break
      }
    }
  }
  if (!matched) next
  key = tbls
  count[key]++
  total_qt[key] += qt
  if (!sample[key]) sample[key] = q
}
END {
  if (format == "csv") {
    if (include_sample == "true") {
      print "No,Schema,Tables,Count,AvgQueryTime(s),SampleQuery"
    } else {
      print "No,Schema,Tables,Count,AvgQueryTime(s)"
    }
  } else {
    if (include_sample == "true") {
      print "| No | Schema | Tables | Count | AvgQueryTime(s) | SampleQuery |"
      print "|----|--------|--------|-------|------------------|--------------|"
    } else {
      print "| No | Schema | Tables | Count | AvgQueryTime(s) |"
      print "|----|--------|--------|-------|------------------|"
    }
  }
  i = 1
  PROCINFO["sorted_in"] = "@val_num_desc"
  for (k in count) {
    avg = total_qt[k] / count[k]
    q = sample[k]
    gsub(/"/, "\"\"", q)
    gsub(/\|/, "\\|", q)
    if (format == "csv") {
      if (include_sample == "true") {
        printf "%d,\"%s\",\"%s\",%d,%.6f,\"%s\"\n", i++, db, k, count[k], avg, q
      } else {
        printf "%d,\"%s\",\"%s\",%d,%.6f\n", i++, db, k, count[k], avg
      }
    } else {
      if (include_sample == "true") {
        printf "| %d | %s | %s | %d | %.6f | `%s` |\n", i++, db, k, count[k], avg, q
      } else {
        printf "| %d | %s | %s | %d | %.6f |\n", i++, db, k, count[k], avg
      }
    }
  }
}' "$PARSED" > "$OUTFILE"

ENCODED_OUTFILE="$OUTFILE"

if [ "$FORMAT" = "csv" ]; then
  case "$ENCODING" in
    utf8-bom)
      ENCODED_OUTFILE="$TMP_DIR/bom_$FILENAME"
      printf '\xEF\xBB\xBF' > "$ENCODED_OUTFILE"
      cat "$OUTFILE" >> "$ENCODED_OUTFILE"
      ;;
    shift_jis)
      ENCODED_OUTFILE="$TMP_DIR/sjis_$FILENAME"
      nkf -s --overwrite "$OUTFILE"
      cp "$OUTFILE" "$ENCODED_OUTFILE"
      ;;
  esac
fi

if [ -n "$OUTPUT_DIR" ]; then
  cp "$ENCODED_OUTFILE" "$OUTPUT_DIR/$FILENAME"
  echo "Output written to: $OUTPUT_DIR/$FILENAME"
else
  cat "$ENCODED_OUTFILE"
fi

rm -rf "$TMP_DIR"
```

### GitHub

https://github.com/Akira-Demizu/Cloud-Tools/blob/main/scripts/aggregate-slowlog/README.md

※ スクリプト単体でも利用可能ですが、実行環境としてDockerも用意しております。

### スクリプトの特徴

- MySQL/MariaDBのslow.logを対象に集計
- クエリに登場する**テーブル名（JOIN含む）を抽出**
- 発生件数・平均クエリ時間（秒）を集計
- サンプルクエリ出力も可能（--detail）
- 出力形式：Markdown（デフォルト）／CSV（Excel対応のUTF-8 BOM付きも可）
- 日時フィルター（--start, --end）
- テーブル名フィルター（--table）

### 使用例

```bash
./aggregate_slowlog.sh \
  --path=/var/log/mysql/slow.log \
  --output=./out \
  --format=csv \
  --encoding=utf8-bom \
  --detail \
  --start="20250610 00" \
  --end="20250610 23" \
  --table=wp_posts,wp_postmeta
```

### オプション

| オプション名   | 必須 | 説明                                                                 | 例                             |
|----------------|------|----------------------------------------------------------------------|--------------------------------|
| `--path=`      | ✅   | 分析対象の slow.log ファイルパス                                     | `--path=/logs/slow.log`       |
| `--output=`    | ❌   | 出力先ディレクトリ（省略時は標準出力）                               | `--output=/tmp`               |
| `--format=`    | ❌   | 出力フォーマット（`md` または `csv`）※デフォルト: `md`              | `--format=csv`                |
| `--encoding=`  | ❌   | 出力ファイルの文字コード（`utf8`, `utf8-bom`, `shift_jis`）※CSV時のみ有効 | `--encoding=utf8-bom`         |
| `--detail`     | ❌   | サンプルクエリを出力に含める                                          | `--detail`                    |
| `--start=`     | ❌   | 対象ログの開始日時（`YYYYMMDD` や `YYYYMMDD hh[:mm[:ss]]` 形式）      | `--start=20250610 04:00`      |
| `--end=`       | ❌   | 対象ログの終了日時（同上）                                            | `--end=20250610 23`           |
| `--table=`     | ❌   | 対象にするテーブルをカンマ区切りで指定（部分一致・フィルタリング）   | `--table=wp_posts,wp_postmeta` |

### 出力例

#### マークダウン

```md
| No | Schema | Tables        | Count | AvgQueryTime(s) | SampleQuery |
|----|--------|---------------|--------|------------------|--------------|
| 1  | sample-net | wp_2_posts    | 2     | 1.971794         | `SELECT SQL_CALC_FOUND_ROWS wp_2_posts.ID FROM ...` |
```

#### CSV

```csvs
No,Schema,Tables,Count,AvgQueryTime(s),SampleQuery
1,sample-net,"wp_2_posts",2,1.971794,"SELECT SQL_CALC_FOUND_ROWS wp_2_posts.ID FROM wp_2_posts WHERE ..."
2,example-db,"some_custom_table",1,0.872115,"SELECT * FROM some_custom_table JOIN another_table ..."
```

### 用途とメリット

- スロークエリの傾向把握・チューニング対象の特定に最適
- JOINテーブルを含めて複雑なクエリ構造も集計
- Markdown出力で報告書などにそのまま貼り付け可能
- CSV（UTF-8 BOM）出力により、Excelでの閲覧も安心

### まとめ

スロークエリの分析は、パフォーマンス改善の第一歩です。
本スクリプトが、WordPressなどの大規模データベースの保守・運用に役立てば幸いです。