MySQLにおけるSQLチューニング

はじめに

今回はMySQLのチューニングを行った話を書きたいと思います。企業向けのシステムでOracleやMS SQL Serverだとチューニングやったことあるのですが、MySQLを使ったシステムでチューニングをしたことはありませんでした。今回初めてやりました。

SQL実行計画

SQLのチューニングといえばまずこれですよね。MS SQL Serverの場合はManagement Studioで見れるSQLの実行計画は視覚的にとても分かりやすくてどこを修正したらよいかわかりやすいです。MySQLではEXPLAINということでやりました。

参考：8.8.2 EXPLAIN 出力フォーマット

遅いSQLが以下のような感じのSQLでした。（あくまでイメージです）そこまで複雑なSQLではないのですが実行時間は45秒くらいかかっていました。時系列データはそれぞれ1000万行くらい、マスタはそれぞれ数万行です。

EXPLAINした結果は、時系列データ1と時系列データ2のテーブルのwhere句はちゃんとインデックスがきいていて「Extra」に「Using index condition」が表示されており、またマスタa、マスタbのjoinもインデックスがきいているようで「Extra」に「Using index condition」が表示されていました。問題なのは、いちばん外側の部分にあたる箇所が「type」が「ALL」で「Extra」が「Using temporary;Using filesort;」となっていました。

Webで調べてところ、「Using temporary;Using filesort;」は改善するべきといくつかのサイトに記載されてました。MySQLの公式サイトの説明によると

• Using temporaryクエリーを解決するために、MySQL は結果を保持する一時テーブルを作成する必要があります。 これは一般に、クエリーに、カラムを異なって一覧表示する GROUP BY 句と ORDER BY 句が含まれる場合に発生します。

• Using filesortMySQL はソート順で行を取得する方法を見つけるために、追加のパスを実行する必要があります。 ソートは、結合型に従ってすべての行を進み、ソートキーと WHERE 句に一致するすべての行について行へのポインタを格納して実行されます。 次にキーがソートされ、ソート順で行が取得されます。 セクション8.2.1.16「ORDER BY の最適化」を参照してください。

とあり、group by句、order by句が原因と分かりました。left joinしたテーブルでグループ化、並び替えを行っているのが問題ととらえました。

改善策１

グループ化、並び替えを行っている項目を時系列データ1と時系列データ2のテーブルにカラム追加して対応しました。以下変更後のイメージです。

変更後のSQLでEXPLAINを実行しました。「Using filesort」だけになりました。喜んでSQLを実行しましたが、40秒程度かかりました。1割（45秒→40秒）は減ったのですが思いのほか変わりませんでした。

改善策２

他に改善する方法はないかとSQLをよく見てみるとグループ化とマスタのjoinを同時にやっているのに気が付きました。グループ化した後にマスタをjoinしたほうが対象の行数が減るので早くなる可能性がありました。変更後のイメージです。

変更後のSQLでEXPLAINを実行しました。元からあった「Using temporary;Using filesort;」に加え「Using filesort;」がもう1行追加されました。SQLを実行すると10秒程度になりました。EXPLAINの結果は悪くなりましたが、こちらを採用しました。

おわりに

今回はMySQLでチューニングを行った時のことを思い出しながら書きました。MySQLのEXPLAINは確かに参考になるが、どの個所に何秒かかっているかわからずあまり有益でないと思いました。