システムを運用していくうちに処理の遅さが目立ってきたら、それはSQLをチューニングするタイミングかもしれません。今回は、SQLのチューニングの基本から、問題点を調べる実行計画の表示方法、そして、代表的なSQLのチューニングテクニックについて紹介します。
SQLチューニングのやり方
SQLは、列単位でデータを扱えることから、多くのデータに対する処理を比較的簡単な記述で書ける便利な言語です。しかし、SQLの書き方によっては、パフォーマンスに問題が生じるケースもあります。そのため、実際に動作しているSQLの稼働状況をチェックし、問題がある場合はそれを修正する作業、SQLチューニングが必要となるケースが発生します。
次から、SQLチューニングのやり方について紹介します。
SQLチューニングの目的
先ほど紹介したように、SQLチューニングが必要となるのは、データベースを使っている情報システムのパフォーマンスに問題があるケースです。リリース当初は問題なかったものの、運用しているうちにデータが増えていくことで、当初予想していなかった問題が発覚することがよくあります。
ただし、ただ処理が遅い、というだけではSQLに問題があるとは言えません。システム全体の中でどの処理が遅いか判断し、さらにその処理におけるSQLの実行に時間がかかることが解れば、そのSQLが原因となります。そのため、SQLチューニングの目的は、情報システムの特定の処理で実行されているSQLの処理時間を短縮することです。
このように、SQLチューニングをすることになった場合、その目的を明確にしましょう。
SQLチューニングするには測定手段が必要
SQLチューニングをやると決まったら、よく使われるテクニックを適当に使えば良い、という訳ではありません。まずは、現状、対象のSQLの実行にどれだけの時間がかかるのかを測定しなければなりません。そのうえで、最適なテクニックを適用します。
なお、SQLの実行時間を測定する方法は、SQLからコマンドを実行して処理時間を測定したり、SQLエンジンの実行計画を調べるといった方法があり、データベース管理ソフトによってもやり方が違います。まずは、調べたいSQLを実行しているデータベース管理ソフトに合わせて、処理時間を測定する方法を確認しましょう。
クエリーの内部動作を理解しよう
SQLチューニングのやり方を検索すると、幾つものテクニックが見つかります。しかし、それをそのまま使えばSQLの処理速度を改善できる訳ではありません。そのようなテクニックを解説している記事を読めば解りますが、クエリーの内部動作を理解し、そのうえで改善するテクニックを使わなければなりません。
なお、SQLは、列に格納されたデータをまとめて処理する手順を簡潔に記載できるので、複雑な検索処理でも比較的簡単に記述できます。しかし、その内部では、個々のデータ全てに対してスキャンしたり、ソートしたりと複雑な処理を行っています。そのため、大きなデータを格納したテーブルを対象にすると、大量のメモリーを使用し、実行時間もかかります。対象のSQLで、そのテーブルがどのように扱われるか考えて、SQLをチューニングしなければなりません。
SQLをチューニングするなら実行計画をチェック
先ほど解説したようにSQLのチューニングでは、現状を把握するにいくつもの測定手段を使います。そして、中でもよく使われる方法が、SQLの実行計画によるチェックです。SQLの実行計画を活用してチューニングするスキルは、データベースを扱うエンジニアなら必須のスキルと言えるでしょう。
次から、SQLのチューニングで使われる実行計画を表示する方法について紹介します。
SQLの実行計画を調べる
ほとんどのプログラム言語は、そのソースコードに書かれている順番で処理します。しかし、SQLは、そうではありません。データベース管理ソフトがSQLを解析し、そのSQLを実行するための実行計画を作成して、それに基づいて処理します。
そして、データベース管理ソフトでは、SQLを解析して得られる実行計画を表示させることが可能です。SQLをチューニングをするなら、まずは実行計画を調べてください。
実行計画の表示はEXPLAINを使う
先ほど紹介したようにSQLのチューニングでは、まず、その実行計画をチェックします。そして、SQLの実行計画に関する情報を表示させるためのステートメントがEXPLAINです。なお、よく使われるデータベース管理ソフトのMySQLやPostgreSQL、SQL Serverでは、EXPLAINに続いてSELECTで始まるSQLを記述することで、そのSQLの実行計画を表示します。
EXPLAINの実行例 EXPLAIN SELECT * FROM sample_table_a WHERE type = "fish";
一方、Oracle Databaseでは、EXPLAIN PLAN FOR にSQLを続けて記述することで、そのSQLの実行計画を表示します。
Oralce DatabaseのEXPLAINの実行例 EXPLAIN PLAN FOR SELECT * FROM sample_table_a WHERE type = "fish";
SQLのチューニングのテクニック
次から、SQLのチューニングでよく使われるテクニックについて紹介します。
インデックスを活用する
SQLにおけるインデックスは、テーブルに対する検索時間を短縮できるので、SQLのチューニングでも有効な手段です。扱うデータにもよりますが、インデックスを使っていないSQLにインデックスを適用することで、劇的に検索速度が改善したケースがたくさんあります。
ただし、何でもいいからインデックスを作っておけば処理が速くなる、ということではありません。インデックスとは、検索対象となる列を予めソートして、全ての要素を検索しなくても良い状態にしておくことで、処理速度を改善しています。そのため、インデックスにする列の選択が重要です。列の選択を間違えてしまうと、処理速度が改善しないケースもあるので注意してください。
インデックスの作り方については、下記のページでよく詳しく解説しているので、参考にしてください。
サブクエリーを見直す
サブクエリーは、SELECT文で特定の条件のデータを特定し、それを基にクエリーを実行したり、テーブルに格納さえたデータを加工したリストを作り、それをクエリーに活用するなど、とても便利な機能です。そのため、複雑なSQLでは、複数のサブクエリーを使い、それらで作成したリストを活用してデータを抽出するケースがよく使われます。
しかし、サブクエリーの結果をソートしたり、IN句でテーブル内の全ての要素を検索する処理を実行すると、その分、処理に時間がかかってしまいます。そのため、サブクエリーでのソートをやめたり、IN句を使ったSQLからからEXITSTS句を使ったSQLに見直すなど、サブクエリーの処理の見直しがチューニングのテクニックとしても有効です。
SQLのキャッシュの有効活用を
SQLの検索処理では、直前に使ったテーブルをメモリーに保持し、次の処理でも活用するキャッシュが使われます。そのため、このキャッシュをうまく活用することで、SQLのチューニングが可能です。
例えば、VIEWをテーブルに変える例を紹介しましょう。なお、SQLのVIEWは、複数のテーブルに格納された要素をまとめて表示する機能で、データそのものは別のテーブルにあるものの、SELECT文でテーブルのように扱うことも可能な便器な機能です。
しかし、VIEWを利用するとキャッシュが有効に使われないため、処理が遅くなります。処理速度を改善するなら、SQLで参照するテーブルをチェックし、メモリーに読み込んだテーブルを繰り返し利用できるSQLに書き換えることで、処理速度の改善が可能です。
まとめ
SQLのチューニングの基本として、問題のあるSQLの実行計画の調べ方や、代表的なSQLのチューニング方法について解説しました。もちろん、今回紹介した方法は、チューニングテクニックの一部であり、これだけマスターすればどんなSQLでもチューニングできる訳ではありません。他にも多くのテクニックがあり、それらをSQLの問題点に合わせて適用するのがチューニングの本来のやり方です。ぜひ、今回紹介した内容で満足せず、いろいろな方法を学んでください。
MySQLのEXPLAINの使い方については、下記のページでよく詳しく解説しているので、参考にしてください。
SQLのexplainは、実行計画を表示し遅いSQLのボトルネックを発見する
【MySQL】実行計画を見直して検索速度を上げる。EXPLAINの使い方について解説。