こんにちは。福岡事務所のOです。
ソフトウェア開発においてデータベースは非常に重要な役割を果たします。
多くのソフトウェア、アプリケーションには何らかの形でデータベースが使用されており、データベースに対する実行命令であるSQLがプログラムに組み込まれています。
作成したアプリケーションの動作速度に問題が生じた際にSQLが原因となる場合も多く、データベースの設計、SQLの記述には細心の注意が必要です。
データベースの種類(Oracle,SQLServer,MySQL等)によって考え方が若干異なることがあり、幅広い知識が必要とされます。
実際にSQLの実行速度に問題があった場合、どのように対処するのが効率的かを普段業務で使用しているSQLServerの例を簡単に説明したいと思います。
SQLのチューニングには様々な手法がありますが、最も影響が大きいのはインデックスを使用する方法です。
インデックスとはその名の通りデータがどの場所にあるかを示す索引です。
辞書の索引をイメージするとわかりやすいと思います。
例えば辞書で「パフォーマンス」の意味を調べる場合は「索引を開く」→「ハ行を探す」→「目的のページを開く」という手順を踏むと思います。
索引が適切に掲載されていれば辞書の厚さ(データ量)がどれほど多くても検索に掛かる手間(処理時間)は変わりません。
インデックスを適切に設定、利用することはSQLのパフォーマンスを向上させる上で欠かせません。
SQLServerのインデックスには「クラスター化インデックス」と「非クラスター化インデックス」があります。
クラスター化インデックスはテーブルに対して1つしか設定できませんがデータが並び替えられて格納されるため高速に検索することができます。
特に指定していなければプライマリーキーはクラスター化インデックスで作成されます。
非クラスター化インデックスはテーブルに対して複数設定できますが、並び替えは行われない為、クラスター化インデックスに対して検索速度が遅くなります。
特に指定していなければユニークキーは非クラスター化インデックスで作成されます。
作成したインデックスの使用状況を知るためには、SQLServerで管理ツールの実行計画を使用します。
実行計画に表示される情報で特に注意すべき検索方法は以下の3種類です。
・Index Seek・・・Indexが適切に指定されている検索
・Index Scan・・・Indexを先頭から検索
・Table Scan・・・テーブルを先頭から検索
「Index Seek」はインデックスが正しく指定できている状態で最も高速です。
「Index Scan」はインデックスは使用されてはいますが、インデックスを先頭から調べる効率が悪い検索です。
辞書の例だと「ハ行」に在ることがわからないような状態です。
複合インデックスが設定されているテーブルでキー指定が足りない場合に発生します。
キー指定したつもりでも、中間一致や後方一致で検索する場合、またインデックス項目に関数を使用する等でIndex Seekできない状況になるとIndex Scanが発生します。
「Table Scan」はインデックスが使用されておらず、テーブルの先頭から検索する最も遅い検索です。
データ量に依存してしまうため大規模なデータだとパフォーマンスに深刻な被害をもたらす可能性があります。
SQLServerでインデックスを使用してパフォーマンスを高めるには、クラスター化インデックスを使用し、「Index Seek」以外を極力排除することが理想です。
実際の業務では、テーブル構成やSQLが複雑になり、理想通りには中々いかないことも多いですが、なるべく気をつけて実装するようにしましょう。