MySQLクエリーチューニングことはじめ

はじめに

はじめまして、yoku0825といいます。とある企業のDBAです。
この連載を始めるにあたり、簡単に筆者の背景（この連載が、どんな仕事をしている人間によって書かれたか）を説明しておきたいと思います。

筆者は「とある企業でDBA（データベースを専門で面倒を見る人）」として雇用されています。「データベースの面倒を見る」というと、サーバーサイドアプリケーション（データベースの上のレイヤー）を書く人が担当している場合やインフラエンジニア（データベースよりも下のレイヤー）と呼ばれる人たちが担当している場合を多く耳にしますが、筆者はこれを専門的に、仕事をしている時間はほぼデータベースのことを考えていたり検証したりチューニングしたりしています。

このような背景から、筆者はたしなみ程度にしかプログラムが書けません。サーバーサイドアプリケーションはほぼブラックボックスです（見ようと思えば見られるところにはありますが）。本連載はMySQLのクエリーチューニングを「MySQLしか触れない」人間の目から解説します。MySQLしか触れないというと聞こえが悪くはありますが、「言語やフレームワークに依存せずMySQL側から解決へアプローチする」ための方法を紹介、ということにさせておいてください。なお筆者はDBAではあるものの、OracleにもPostgreSQLにもSQL Serverにも詳しくありません。筆者がただ1つちょっと知っていることは、MySQLに関してのみです。

パフォーマンスチューニングの見方

パフォーマンスチューニングは、端的には「手段は問わない、速くしろ」ということだと思っています。そのためのアプローチとしては、下記のような方法が挙げられます。

• SQLを書き換える
• インデックスを追加する
• パラメーターを変更する
• それ以外の何か

SQLを書き換える、インデックスを追加する

大概の場合、SQLの書き換えとインデックスの追加はセットになります。場合によってはインデックスの追加だけでは収まらず、カラムを足したりテーブルを分割したりがセットになってくることもあります。筆者はこのあたりをまとめて「SQLチューニング」と呼んでいます（`SET SESSION`ステートメントでセッション単位にパラメーターを上書きすることも筆者はこの中に含めていたりします）。

これは筆者がそう呼んでいるだけで、一般的な「SQLチューニング」の範囲とはちょっと違うかも知れません。SQLチューニングは決まれば（つまり元の状態がよほど悪いということでもありますが）100倍以上の性能改善を叩き出せますが、1回のチューニングで影響のある範囲は広くありません。特定のクエリーに対するチューニングなので、別のクエリーが速くなることはほぼないのです。

パラメーターを変更する

パラメーターを1回変更してしまえば（それが決まっていれば）、ほぼ全てのクエリーに対して透過的に（SQLの書き換えなしに）影響を及ぼすことが期待できます。その代わりSQLチューニングほどの性能改善はなく、せいぜい数倍程度です（大概の場合は数倍どころか数%ではないでしょうか。数倍出ればよほど今までの値が悪かったのだろう、ということになります）。大きな効果が見込めるパラメーターは積極的にチューニングしていくモチベーションがありますが、ある程度のところまで行くとパラメーターを細かくチューニングするよりもSQLチューニングに時間をかけた方が効率が良くなります（秘伝のタレに例えられるmy.cnfは、この「ある程度のところ」が詰まっていることが多いです）。

それ以外の何か

スケールアップやスケールアウト（シャーディング）、キャッシュ用ミドルウェアの追加（つまり、その処理にMySQLを直接使わなくする）、RDBMSを入れ替える（例：MariaDBに変更など）、カーネルパラメーターのチューニングなどを想定しています。筆者は立場上、極力「そのMySQLで頑張る」ために力を費やしますが、状況さえ許せばMySQLであることにこだわる必要はありません（たとえばキャッシュ用ミドルウェアの多くはトランザクションに対応していないことが多く、「エラーなく書き込み（RDBMSでいう`COMMIT`）が完了すればサーバーがダウンしてもデータが失われない」ということ（ACID属性の"D"）は保証されないケースが多いです。その状況が許されるならば、です）。

なお、本連載ではSQLの書き換えとインデックスの追加そのものについては触れません。これらが必要そうなクエリーを稼働中のMySQLから推測する手法については、以下の通り説明していく予定です。

本連載各回の解説テーマ

第2回のpt-query-digestと第3回のEXPLAINでは、チューニングしたいSQLがスローログに記録されている場合の調査方法について説明します。また、第4回のPMP for Cactiでは筆者が業務で利用しているMySQLのモニタリングテンプレートのインストールとグラフの簡単な見方について説明します。

第5回のinnotopと、1つ飛ばして第7回のperformance_schemaでは「スローログには出ていないが瞬間的に遅くなることがある」場合や「今はまだ問題になっていないが、いずれ問題になる（かも知れない）クエリーを事前にチェックしたい」場合などに使えるツールを紹介します。

第6回のSHOW PROFILEでは、MySQL組み込みのプロファイラーで遅いクエリーの「どこが遅いのか」を探っていく方法について、最終回のパラメーターチューニングの話をしない理由では「パラメーターチューニングをどのように考えて実践していくべきか」の考え方を説明します。

パフォーマンスチューニングに「銀の弾丸」はない

本連載ではチューニングそのものの方法については詳しく説明しません。それは見出しの通り「銀の弾丸」などはなく、MySQLのパフォーマンスチューニングは計測と改善を繰り返し行っていくべきものだからです。そのため、特定のケースにマッチする改善の手法よりも、繰り返し使われる計測の手法にフォーカスを当てて説明していきます。

また、「遅いクエリーがわかってもどんなインデックスを追加すれば良いのかわからない」人や「そもそもインデックスを追加したことがない」人向けの連載ではありません。「スローログがカウントアップしているのはわかるけれど、それを丁寧に解析している時間がない」人だったり、「問題に備えてモニタリング環境を整備しておきたい人」だったり、「いざ問題が起きた時に手早く状況を確認するための一手を探しておきたい人」だったり、そんな忙しいDBAの一助になれば幸いです。

今回は、第1回として本連載で解説するチューニング手法とその方針等について紹介しました。
次回からの解説をお楽しみに！