はじめに
今回はMySQLのチューニングを行った話を書きたいと思います。企業向けのシステムでOracleやMS SQL Serverだとチューニングやったことあるのですが、MySQLを使ったシステムでチューニングをしたことはありませんでした。今回初めてやりました。
SQL実行計画
SQLのチューニングといえばまずこれですよね。MS SQL Serverの場合はManagement Studioで見れるSQLの実行計画は視覚的にとても分かりやすくてどこを修正したらよいかわかりやすいです。MySQLではEXPLAINということでやりました。
遅いSQLが以下のような感じのSQLでした。(あくまでイメージです)そこまで複雑なSQLではないのですが実行時間は45秒くらいかかっていました。時系列データはそれぞれ1000万行くらい、マスタはそれぞれ数万行です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
select sum(f1),sum(f2),f3,マスタaにある場合マスタa.nameでマスタbにある場合マスタb.name from ( select f1,f2,f3,f4,f5,f6,f7 from ( select f1,f2,f3,f4,f5,f6,f7 from 時系列データ1 where 日付の絞り込み 会社コードの絞り込み union select f1,f2,f3,f4,f5,f6,f7 from 時系列データ2 where 日付の絞り込み 会社コードの絞り込み ) as sub left join マスタa left join マスタb ) as main group by f3 ,マスタaにある場合マスタa.nameでマスタbにある場合マスタb.name order by マスタaにある場合マスタa.nameでマスタbにある場合マスタb.name |
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 filesort
MySQL はソート順で行を取得する方法を見つけるために、追加のパスを実行する必要があります。 ソートは、結合型に従ってすべての行を進み、ソートキーとWHERE
句に一致するすべての行について行へのポインタを格納して実行されます。 次にキーがソートされ、ソート順で行が取得されます。 セクション8.2.1.16「ORDER BY の最適化」を参照してください。
とあり、group by句、order by句が原因と分かりました。left joinしたテーブルでグループ化、並び替えを行っているのが問題ととらえました。
改善策1
グループ化、並び替えを行っている項目を時系列データ1と時系列データ2のテーブルにカラム追加して対応しました。以下変更後のイメージです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
select sum(f1),sum(f2),f3,namae from ( select f1,f2,f3,f4,f5,f6,f7,name from ( select f1,f2,f3,f4,f5,f6,f7,name from 時系列データ1 where 日付の絞り込み 会社コードの絞り込み union select f1,f2,f3,f4,f5,f6,f7,name from 時系列データ2 where 日付の絞り込み 会社コードの絞り込み ) as sub ) as main group by f3 ,name order by name |
変更後のSQLでEXPLAINを実行しました。「Using filesort」だけになりました。喜んでSQLを実行しましたが、40秒程度かかりました。1割(45秒→40秒)は減ったのですが思いのほか変わりませんでした。
改善策2
他に改善する方法はないかとSQLをよく見てみるとグループ化とマスタのjoinを同時にやっているのに気が付きました。グループ化した後にマスタをjoinしたほうが対象の行数が減るので早くなる可能性がありました。変更後のイメージです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
select sum1,sum2,f3,マスタaにある場合マスタa.nameでマスタbにある場合マスタb.name from ( select sum(f1) as sum1,sum(f2) as sum2,f3,f4 from ( select f1,f2,f3,f4,f5,f6,f7 from ( select f1,f2,f3,f4,f5,f6,f7 from 時系列データ1 where 日付の絞り込み 会社コードの絞り込み union select f1,f2,f3,f4,f5,f6,f7 from 時系列データ2 where 日付の絞り込み 会社コードの絞り込み ) as sub ) as main group by f3,f4 ) as total left join マスタa left join マスタb group by f3 ,マスタaにある場合マスタa.nameでマスタbにある場合マスタb.name order by マスタaにある場合マスタa.nameでマスタbにある場合マスタb.name |
変更後のSQLでEXPLAINを実行しました。元からあった「Using temporary;Using filesort;」に加え「Using filesort;」がもう1行追加されました。SQLを実行すると10秒程度になりました。EXPLAINの結果は悪くなりましたが、こちらを採用しました。
おわりに
今回はMySQLでチューニングを行った時のことを思い出しながら書きました。MySQLのEXPLAINは確かに参考になるが、どの個所に何秒かかっているかわからずあまり有益でないと思いました。
“MySQLにおけるSQLチューニング” への 0 件のフィードバック