MySQLのインデックス周りのチューニングというか調査

MySQL周りでインデックス貼ってないSQLが投げられると死んじゃいますが、そいつを洗い出すのに試行錯誤したのでメモ

前置き

インデックスとは？

[ssl url='http://shibayu36.hatenablog.com/entry/2012/06/02/210848' title='MySQLをさらに理解するために読んだ記事まとめ']

インデックスに関しての説明はこれが一番わかりやすかったので、こちらのまとめを参照。

では調査

スロークエリログで確認

スロークエリログとは、SQLの実行にかかる時間が設定値以上である場合に出力されるログです。(以下、スローログと呼ぶ)

実行にかかる時間の設定はmy.cnfで設定可能です。

スローログが保存されているということは、実行に時間がかかった = インデックスが貼ってないということで、あとから漁ってだめなSQLを投げてないか調査します。

※5.1以上からテーブルに保存できるようになったので、テーブルに保存しているところが多いと思う。

select * from mysql.slow_log where start_time between '2013-06-28 12:00' and '2013-06-28 12:59' order by query_time desc,sql_text desc ;

こんなSQLを投げれば実行に時間がかかるSQLが取れるはず。

ここで問題があって、my.cnfに設定されたスロークエリログの指定された時間以上に処理がかかっている場合に取得することができますが、自分のローカル環境などでやるとデータ量が少なすぎて、SQLの実行速度がmy.cnfに指定された時間を超えない場合があります。

逆に0.01秒とか短い設定にしちゃうと全クエリが乗っちゃったりして使えなかったりします。

※管理者権限でないとmy.cnfをいじれなくて、スローログの設定ができてなかったりします。

その場合は、管理者相当の権限があるユーザーでログインして、"set global log_queries_not_using_indexes = 1;"を叩くと良いかと。

それでもできなかったら、インフラ系の人にお願いするか、諦める。

[ssl url='http://yonchu.hatenablog.com/entry/20100716/1279301089' title='MySQL5.0/5.1でスロークエリログを記録']

log_queries_not_using_indexesに関してはこちらを参照

生のSQLをひたすらExplainする

スローログじゃうまくいかなかったので、結局アプリで発行してるSQLを洗い出して、ひたすらExplainを投げる簡単なお仕事にしました。

Explainについては、こちらを参照

[ssl url='http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html' title='MySQLのEXPLAINを徹底解説!! ']

まず、フレームワークなりでどこかにアプリ側で発行しているSQLを吐き出す設定があるかと思いますので、それを編集して生のSQLを発行する。

※ここでは、その説明はしない。これがなかったら諦める。

あとは吐き出したログをシェルで適当に加工。

# grepでtableを指定すると絞りやすいので、該当のテーブルがわかっているのであれば絞る。

# uniqしているのは、複数行の同じSQLを無くしたいため。

cat sql_log | grep table | awk -F '\|' '{print $4}' | sort | uniq -c | sed -e "s/^/explain/g" | sed -e "s/$/;/g" > sql.log

あとは、出力したファイルの先頭にexplainをつけて、実際のDBに投げまくる。

怪しい結果が返ってきたSQLに関しては、show create tableとかshow index fromとひたすらにらめっこ。

ダメっぽいSQLがあればアプリケーション側のコードを読みにいって該当のコードを調整したり、インデックス貼り直したりして、インデックスが貼ってないSQLを撲滅する。

その他

SHOW FULL PROCESSLISTを叩きまくるってのもできそうだけど、やってない。

まとめ

なんかもっといい方法ありそうなんだが…