MySQL explainをフィルタリングして出力するツール

# はじめに

sqlログからまとめてexplainをするツールを紹介させていただきます。
結果をフィルタリング表示させたりもできます。

**expl**
https://github.com/muroon/expl


例えば、Indexが貼られていないSQLを特定したりするのに便利かと思います。

- explainをフィルタリングできる
  - indexが貼はられていないSQLを検出
  - using filesortが発生しているSQLを検出
  - logの中から特定のtableのSQLのみに限定できる
- explainを集約して実行できる
  - 内部でSQLを解析して同一SQLのexplainは1回のみにできる 

CIツール内で使用してもいいと思います。

# 使用例

下記のようなdatabase, table構成があったとします
<img width="443" alt="er.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76076/5b34e026-0283-e8c5-8499-659c99c2d51b.png">


下記のような独自SQLログを常に吐き出していた場合

```text:sql.log
2019-06-21 01:00:00 select * from user where id = 1;
2019-06-21 01:00:00 select * from item where id = 1;
2019-06-21 01:00:00 select user_item.* from user_item, item where item.id = 1 and user_item.item_id = item.id;
```

## 1. Install

### binaryをdownload

```
# Macの場合
curl -vLJO -H 'Accept: application/octet-stream' https://github.com/muroon/expl/releases/download/v1.0.6/expl_1.0.6_Darwin_x86_64.tar.gz
mkdir expl_1.0.6_Darwin_x86_64
tar -zxvf expl_1.0.6_Darwin_x86_64.tar.gz -C expl_1.0.6_Darwin_x86_64

# Linuxの場合
wget https://github.com/muroon/expl/releases/download/v1.0.6/expl_1.0.6_Linux_x86_64.tar.gz
mkdir expl_1.0.6_Linux_x86_64
tar -zxvf expl_1.0.6_Linux_x86_64.tar.gz -C expl_1.0.6_Linux_x86_64
```

### Go環境がある場合

```
go get github.com/muroon/expl/cmd/expl
```

## 2. 各DB情報を追加

confサブコマンドを実施することにより必要DB情報をconfigファイルに保存します。

```
expl conf add --host localhost --database master --user user --pass password --conf ./config.yaml
```
- confオプション(-c, --conf)でconfigファイル(YAML形式)を指定

## 3. explainを実行

[explainサブコマンド](#explainサブコマンド)を実施することにより、sqlログを読み込みexplainを実行しています。

```
expl explain log -c ./config.yaml -l ./sql.log --format command --format-cmd 'cut -c 21-' -I -v -C
```

- [logモード](#各モード)によりログファイルから読み込みSQL実行(第二引数)
- [logオプション(-l, --log)](#logファイル指定)でログファイルパスを指定
- [confオプション(-c, --conf)](#configファイル指定)で上記のconfigファイルを指定
- [error無視指定(-I, --ignore-error)](#error無視指定)
- [実行SQLの集約(-C, --combine-sql)](#実行sqlの集約)

詳しくは[explainサブコマンドの処理内部](#処理内部)参照

#### 出力結果をフィルタリングする

explainのTYPEがconst以外で絞って表示させる場合

```
# --filter-no-type constを指定

expl explain log -c ./config.yaml -l ./sql.log --format command --format-cmd 'cut -c 21-' -I -v -C --filter-no-type const
```
フィルタリングオプションについては[こちら](#explainの結果のフィルタリング)


#### 環境変数で省略可能

下記のオプションは環境変数で省略可能です

```
EXPL_CONF : -c --conf オプション
EXPL_LOG : -l --log  オプション
EXPL_OPTION : --option-file  オプション(後述)
```

# explainサブコマンド

```
expl explain モード [各オプション]
```

## 処理内部

上記の[使用例](#使用例)にてexplainサブコマンドを実行した場合

- logファイル(sql.log)を1行づつ読み込み、順次SQLを実行して出力します
- 必要に応じてファイルタリングを実施します
- 仮にlogファイル(sql.log)に複数database(master, user)のSQLが両方存在したとしてもツール側で判別します
- itemテーブルのようにmaster, userデータベースに存在する場合は、itemテーブルへのSQLはmaster, userデータベースの両方にexplainを試みます
- 上記のような[logモード](#各モード)で使用する際には[ignore error option](#error無視)を指定してご利用ください
  - 例えば```select * from item where cateogry_id = 1```のようなSQLで考えてみます
 - explはitemというtable名からmaster, userのデータベースにitemテーブルが存在することを認識します
 - このような場合、master, userデータベースの両方に向けてexplain SQLを投げてクエリーsyntaxエラーが起きなかった方を出力しようとします。上記のエラーオプションを付けていないとエラー出力して処理がファイルの途中で落ちます
- [実行SQLの集約](#実行sqlの集約)によりパラメータ違いの同一SQLは集約して出力できます

## 各モード

explはSQLの単体実行、独自ファイルからの読み込み、[MySQLクエリーログ(FILE)](https://dev.mysql.com/doc/refman/5.6/ja/query-log.html)からの読み込み、[MySQLクエリーログ(DB)](https://dev.mysql.com/doc/refman/5.6/ja/log-destinations.html)を想定して下記の3つのモードが存在します。

|mode|内容|1コマンド内のクエリー実行数|備考|
|---|---|---|---|
|simple|SQLを直接指定|1クエリーのみ|第三引数にクエリーを指定|
|log|ログファイルからSQLを読み込み|複数クエリー実行可|[MySQLクエリーログ(FILE)](https://dev.mysql.com/doc/refman/5.6/ja/query-log.html)または独自のログの場合に使用|
|log-db|DBからSQLを読み込み|複数クエリー実行可|[MySQLクエリーログ(DB)](https://dev.mysql.com/doc/refman/5.6/ja/log-destinations.html)の場合に使用|

```
# simple mode
expl explain simple "select * from memo" --database database1 --host localhost --user root --pass ""

# log mode
expl explain log --conf config.yaml --format official --log sql.log

# log-db mode
expl explain log-db --conf config.yaml --format official
```

## 各オプション

### configファイル指定

```
-c configファイル
--conf configファイル

# EXPL_CONF環境変数で省略可能
```

### logファイル指定

```
-l logファイル
--log logファイル

# EXPL_LOG環境変数で省略可能
```

### logファイルのフォーマット

explain実行前にログファイルの行からSQLを取り出します。
下記の2つのオプションを使用します。

- format
- format-cmd

#### format

```
-fm フォーマット
--format フォーマット
```

logファイルフォーマットを指定します。

|format|内容|
|---|---|
|simple|素のSQL|
|official|MySQLの[general_log](https://dev.mysql.com/doc/refman/5.6/en/query-log.html)形式|
|command|OSコマンドにより編集|


#### format-cmd

```
--format-cmd OSコマンド
```

OSコマンドを実行して、ログファイルの行を編集したい場合に実行します。

```
expl explain log --conf config.yaml --log custom_sql.log --format command --format-cmd "cut -c 21-"

# 上記はパイプからsimple modeを実行するの同じ
cut -c 21- custom_sql.log | xargs -I$ expl explain simple "$" --conf config.yaml --format command --format-cmd "cut -c 21-"
```

### explainの結果のフィルタリング

```
--filter-select-type 条件
--filter-no-select-type 条件
--filter-table 条件
--filter-type 条件
--filter-no-type 条件
--filter-extra 条件
--filter-no-extra 条件

# 複数の場合は,区切り
```

各種条件に応じたexplain結果のみを表示する

| option | 内容 |
|---|---|
| filter-select-type | 指定された"Select Type"のみ表示 |
| filter-no-select-type | 指定されなかった"Select Type"のみ表示 |
| filter-table | 指定されたTableのみ表示 |
| filter-no-table | 指定されなかったTableのみ表示 |
| filter-type | 指定された"Type"のみ表示 |
| filter-no-type | 指定されなかった"Type"のみ表示 |
| filter-extra | 指定文字列が含まれているExplain結果のみ表示 |
| filter-no-extra | 指定文字列が含まれているExplain結果を除外して表示 |

```
# TYPEに"ALL"が含まれるものを抽出 

expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --filter-type ALL

  DataBase:  memo_sample
  SQL:       select tag.* from tag, tag_memo where tag.id = tag_memo.tag_id
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
| ID | SELECTTYPE |  TABLE   | PARTITIONS | TYPE | POSSIBLEKEYS |   KEY   | KEYLEN |        REF         | ROWS | FILTERED |    EXTRA    |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
|  1 | SIMPLE     | tag      |            | ALL  | PRIMARY      |         |      0 |                    |   22 | 100.0000 |             |
|  1 | SIMPLE     | tag_memo |            | ref  | PRIMARY      | PRIMARY |      4 | memo_sample.tag.id |    1 | 100.0000 | Using index |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
```
NULL指定は空文字指定

```
# KEYがNULLのものを指定
expl explain --filter-key ""

# 出力 (KEY=空文字として表示)
+----+------------+--------+------------+------+--------------+-----+--------+-----+------+----------+-------------+
| ID | SELECTTYPE | TABLE  | PARTITIONS | TYPE | POSSIBLEKEYS | KEY | KEYLEN | REF | ROWS | FILTERED |    EXTRA    |
+----+------------+--------+------------+------+--------------+-----+--------+-----+------+----------+-------------+
|  1 | SIMPLE     | user   |            | ALL  |              |     |      0 |     |    1 | 100.0000 | Using where |
+----+------------+--------+------------+------+--------------+-----+--------+-----+------+----------+-------------+
```

### 実行SQLの集約

```
-C
--combine-sql
```

同じタイプのSQLは一つにまとめます。
下記のようなパラメータ違いのSQLは一つに集約して実行を行います。

```
# sql1
select * from memo where id = 1;

# sql2
select * from memo where id = 100;
```

```
expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --combine-sql
```

### error無視

```
-I
--ignore-error
```
- 下記のエラーを実行する  
 - explainクエリー実行時のエラー
 - sqlパーサーエラー(内部でsqlの解析を行っています)


### オプションをファイルに保存

```
--option-file オプションファイル

# EXPL_OPTION環境変数で設定可能
```

ファイルは[こちら](https://github.com/muroon/expl/blob/master/option.yaml)をベースに各オプション項目を設定してoption-fileオプションで指定する

指定が重複した場合の優先順位(高い順)
1. コマンド
2. 環境変数
3. オプションファイル

### 詳細出力

```
-v
--verbose
```

最終的に実行に使用されてたオプションの値を出力

```
expl explain simple "select * from memo" -d localhost -H localhost -u root -v
      INPNUT OPTION         VALUE
------------------------+--------------
  database               memo_sample
  host                   localhost
  user                   root
  pass
  conf
  log
  format                 simple
  format-cmd
  filter-select-type
  filter-no-select-type
  filter-table
  filter-no-table
  filter-type
  filter-no-type
  filter-extra
  filter-no-extra
  update-table-map       false
  ignore-error           false
  combine-sql            false
```

### ヘルプ

```
expl explain -h
expl explain --help
```

confも同様ですが、サブコマンド単位のヘルプもあります。

### おすすめの使用法

個人的にはINDEXを使用されていないSQLをまとめて週出するのに便利と思います。

```
# 特定のカラムがしていされているにも関わらずフルスキャンが選択されているもの

expl explain log --log sql.log --filter-type ALL --filter-extra "using where" --filter-key ""

  DataBase:   user
  ParsedSQL:  select * from user where sex = :1
  SQL:        SELECT * FROM user WHERE `sex` = 1
+----+------------+--------+------------+------+--------------+-----+--------+-----+------+----------+-------------+
| ID | SELECTTYPE | TABLE  | PARTITIONS | TYPE | POSSIBLEKEYS | KEY | KEYLEN | REF | ROWS | FILTERED |    EXTRA    |
+----+------------+--------+------------+------+--------------+-----+--------+-----+------+----------+-------------+
|  1 | SIMPLE     | user   |            | ALL  |              |     |      0 |     |    1 | 100.0000 | Using where |
+----+------------+--------+------------+------+--------------+-----+--------+-----+------+----------+-------------+

```

上記を使用しなくてもlog_queries_not_using_indexesをONに指定すればMySQL側で該当SQLログを出力してくれますが、下記の点に注意が必要です。

- 意図的に全件取得しているSQLも対象に含まれる
- ONにするとパフォーマンスに影響が出る