Edited at

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構成があったとします

er.png

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


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. Download

# Macの場合

curl -vLJO -H 'Accept: application/octet-stream' https://github.com/muroon/expl/releases/download/v1.0.2/expl_1.0.2_Darwin_x86_64.tar.gz
mkdir expl_1.0.2_Darwin_x86_64
tar -zxvf expl_1.0.2_Darwin_x86_64.tar.gz -C expl_1.0.2_Darwin_x86_64

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


2. 各DB情報を追加

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

expl conf add localhost master user password -c ./config.yaml

expl conf add localhost user user password -c ./config.yaml


  • confオプション(-c, --conf)でconfigファイル(YAML形式)を指定


3. explainを実行

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

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

詳しくは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

フィルタリングオプションについてはこちら


環境変数で省略可能

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

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を指定してご利用ください


    • 例えばselect * from item where cateogry_id = 1のようなSQLで考えてみます

    • explはitemというtable名からmaster, userのデータベースにitemテーブルが存在することを認識します

    • このような場合、master, userデータベースの両方に向けてexplain SQLを投げてクエリーsyntaxエラーが起きなかった方を出力しようとします。上記のエラーオプションを付けていないとエラー出力して処理がファイルの途中で落ちます




  • 実行SQLの集約によりパラメータ違いの同一SQLは集約して出力できます


各モード

explはSQLの単体実行、独自ファイルからの読み込み、MySQLクエリーログ(FILE)からの読み込み、MySQLクエリーログ(DB)を想定して下記の3つのモードが存在します。

mode
内容
1コマンド内のクエリー実行数
備考

simple
SQLを直接指定
1クエリーのみ
第三引数にクエリーを指定

log
ログファイルからSQLを読み込み
複数クエリー実行可

MySQLクエリーログ(FILE)または独自のログの場合に使用

log-db
DBからSQLを読み込み
複数クエリー実行可

MySQLクエリーログ(DB)の場合に使用

# 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形式

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 | 0 | ALL | PRIMARY | | 0 | | 22 | 100.0000 | |
| 1 | SIMPLE | tag_memo | 0 | ref | PRIMARY | PRIMARY | 4 | memo_sample.tag.id | 1 | 100.0000 | Using index |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+


実行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環境変数で設定可能

ファイルはこちらをベースに各オプション項目を設定して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も同様ですが、サブコマンド単位のヘルプもあります。