LoginSignup
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@muroon(Toyohito Murooka)

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

Last updated at Posted at 2019-06-27

はじめに

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. 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サブコマンドを実施することにより、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      |            | 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環境変数で設定可能

ファイルはこちらをベースに各オプション項目を設定して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にするとパフォーマンスに影響が出る
3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?