MySQLを高速化するパフォーマンスチューニング入門

１．１　パフォーマンスチューニングとは

データベースのパフォーマンスチューニングとは、データベースのパフォーマンス（性能）の向上をはかることであり、主に次のような性能の向上を目安としています。

■レスポンスタイム

応答時間。命令を入力してから出力が返ってくるまでの時間。

■スループット

単位時間あたりに処理できるデータ量。

■スケーラビリティ

ユーザ数や作業量の増加への適応性。

スループットの向上は多くの場合に応答時間の向上につながりますが、ある重要な問い合わせの応答時間を短縮するために全体的なスループットを犠牲にするケースもあります。

今回の記事では、データ量増加への対応ではなく、実行速度の向上につながる応答時間とスケーラビリティに主眼を置いてご紹介したいと思います。

１．２　サーバチューニングとは

データベースサーバのチューニングとは、サーバ全体のパフォーマンスを向上させることにより、主にスループットの向上をはかります。より高性能なハードウェアへのリプレース以外で行えるチューニングは、主にパラメータ（システム変数やステータス変数）の最適化をはかることになります。

my.iniやmy.cnfなどの設定ファイルの修正で行うことができますが、詳しいご紹介はまた別の機会に行えたらと思います。

１．３　SQLチューニングとは

SQLチューニングとは、個々のSQLを見直すことで、処理速度の向上をはかることです。クエリの見直しだけでなく、インデックスの追加やデータ型の見直しなど、テーブルやカラムに関する部分についても見直しを行います。今回は、このSQLチューニングについて詳しくご紹介していきたいと思います。

SQLチューニングのポイントには、主に次の５つが挙げられます。

２．１　データ型の見直し

テーブルに設定したデータ型と入力したデータのデータ型が異なっていた場合、変更可能であればエラーとならずに暗黙の型変換が行われます。便利な機能のように思えますが、この型変換にかかる時間を回避するためにも、入力されるデータの型をきちんと洗い出して設定することが重要となります。

２．２　インデックスの追加と削除

頻繁に発行するクエリの実行計画を調査し適したインデックスを追加することで、劇的な速度向上が期待できます。

さらに誤ったインデックスが貼られてしまっていたら、削除することで速度が向上する場合もあります。

インデックスの追加方法と実行計画の取得について、詳しくは次の記事をご参照下さい。

関連記事：MySQLでインデックスの追加・確認・削除を行う方法

２．３　SQLの見直し

これは単純で、無駄なループをなくすなどの処理を見直したり、実行が遅い命令を早い命令に置き換えるなどで対応します。

例えば「UNION」は２つのテーブルを結合する機能ですが、重複行削除のための処理が行われるためその分時間がかかります。重複しない、または重複してもよいという場合は積極的に「UNION ALL」を使用することで、速度の向上が期待できます。

ほか、SELECT時に「*（ワイルドカード）」を使用すると不要な列まで取得することになったり、また全列を取得したい場合でも「*」を項目名に置き換える処理が内部で行われるため、その分時間がかかってしまいます。そのため必要な項目名を一つずつ指定することで、速度の向上が期待できます。同じような現象として、列番号を指定すると項目名への読み替えを内部で行う処理が追加されるため、こちらも避けるのが無難です。

２．４　ストアドプロシージャの削除

コールするだけで使用できるストアドプロシージャですが、実は普通にクエリを発行するよりも実行に時間がかかる場合があります。処理に時間がかかっているプロシージャを見つけたら、プロシージャを使用しない場合と実行時間の比較を行ってみて下さい。行っている内容が全く同じでも、実行計画が異なる場合があるようです。

２．５　非正規化で処理速度が向上する？

データベースを正規化しすぎると、検索時にたくさんの表を結合する必要が出てくることから処理速度が低下すると長年言われてきました。しかし非正規化による結合は処理速度の低下に必ずしも影響しないということを示す実証実験の結果も公開されています。

【ITPro - 「DBを正規化すると遅くなる」は誤解，実証実験の結果が公開に】
http://itpro.nikkeibp.co.jp/article/NEWS/20051114/224500/

今のところ、正規化は行った方がメリットが大きいようです。どうしても気になる場合は、最後の手段で行ってみてはいかがでしょうか。

３．１　スロークエリログとは

スロークエリログとはMySQLから出力されるログの一種であり、設定した時間以上に処理開始から終了までの時間がかかったクエリが出力されています。このログに出力されているクエリを調査することで、改善すべきクエリが見えてくるという寸法です。

３．２　スロークエリログの使い方

スロークエリログは、デフォルトで出力がONになっています。出力の有無のほか、出力するログのファイル名や出力対象とする時間を設定ファイルなどで指定することが可能です。

なお出力対象とする時間はデフォルトでは10秒となっており、秒単位で細かく設定することができます。

スロークエリログの詳細な設定方法や出力内容の読み方などについては、次の記事をご参照下さい。

関連記事：MySQLにおける４つのログファイルの設定と確認方法

SQLチューニングのために、パフォーマンスの変化などを可視化した様々なGUIツールが各社からリリースされています。

４．１　MySQL Workbench

MySQL公式で配布されている無料のデータベース管理・開発用ソフトウェアであり、SQLチューニング向けの情報だけでなく、サーバチューニングにも役立つソフトです。

【MySQL Workbench - ダウンロードページ】
https://www-jp.mysql.com/products/workbench/

４．２　MySQL Query Analyzer

DB管理者がクエリのパフォーマンス状況を一覧で監視できるMySQL公式のGUIツールです。有償版でのみ使用可能ですが、商用利用の場合はとても便利なツールとなっています。

【MySQL Query Analyzer - 紹介ページ】
https://www-jp.mysql.com/products/enterprise/query.html

４．３　MySQLTuner

MySQLTunerは、MySQLのチューニングが必要な箇所を解析してくれるライセンス「GNU GPL」のフリーソフトです。

「Security Recommendations」「Storage Engine Statistics」「Performance Metrics」などの項目があり、それぞれ[!!]と表示されている行に書かれている内容を解決していくことでパフォーマンスの向上が図れる便利なツールです。

【MySQLTuner - ダウンロードページ】
https://github.com/major/MySQLTuner-perl

５．１　エキスパートのためのMySQL運用＋管理トラブルシューティングガイド

５．２　MySQL 即効クエリチューニング

今回ご紹介したSQLチューニングは、少しの修正で劇的に速度が向上する場合もあります。機会があれば、ぜひ試してみて下さいね。