この記事はオルターブース Advent Calendar 2021の10日目の記事です。
アドベントカレンダー10日目はテクニカルアーキテクトの木本です。
テクニカルアーキテクトなのに、これまでテクニカルなブログを書いたことがない木本です。
一応、ちゃんと、それなりに、多少なりともテクニカルなお仕事もしていますので、今年はテクニカルな内容で攻めてみたいと思います。
- インデックスとは？
- アプリケーション設計とインデックス
- インデックスが重要になる場面
- インデックスが効かないパターンを減らす
- インデックスによるINSERT・UPDATEのレスポンス低下
- インデックスが効かなくなってしまうケース
- 動的管理ビューで足りないインデックスを見つける
- SQL Server Management Studioで足りないインデックスを見つける
- 自動インデックス管理を利用する
- まとめ（？）
- 余談
インデックスとは？
アプリケーションを開発・運用する上で外せないのが「データの管理」です。（というか、「データを適切に管理する仕組み」がアプリケーションなのですが。）
データの管理と言ってもいろんなやり方がありますが、やはりデータベースで保存・利用することが圧倒的に多いと思います。
現在はデータベースも様々な種類がありますが、業務アプリケーションではリレーショナルデータベースを利用することが多いのではないでしょうか。
そして、リレーショナルデータベースを使ったアプリケーションを運用する上で避けて通れないのが「インデックス」です。
昨年と今年、膨大なデータとインデックスについて考える機会があったので、今回はこのインデックスについて自分の考えをまとめていきたいと思います。
インデックスの仕組みなどの話ではなく、どちらかというと、アプリケーションを設計したり、運用したりする上で、インデックスについてどう考えているか、という内容になります。
リレーショナルデータベースを扱ったことのある方ならご存知だと思いますが、インデックスは「データベーステーブルの中のデータを素早く見つけるため」に使われるもので、データベースに保存されるデータが多くなればなるほど避けては通れなくなるものです。
が、「どの列にインデックスを作成するか」からはじまり、どういうタイプのインデックスにするかとか、断片化対策とか、いろいろと考えなければならない要素があり、理解するのがとても難しい部類の技術だと思います。
正直に言いますと、僕はこのインデックスを扱うのがあまり得意ではありません。
アプリケーション設計とインデックス
僕はアプリケーション設計を行うことが多く、当然データベース設計も行うのですが、基本的にこの設計の時点ではインデックスのことはあまり意識しません。
あたりまえのことですが、まずは業務要件を満たすこと、かつアプリケーションの実装がしやすくなることを最優先に考えるからです。この時点でインデックスのことまで考えていると脳みそがパンクします。
ただ、後々インデックスのことで悩まなくていいように、ということは頭の片隅には置いています。
インデックスが重要になる場面
前述の通り、インデックスが必要になってくるのは「データを探す」場合ですが、業務アプリケーションでは以下のような機能で特に重要になってくると思います。
- データ検索
- 帳票・レポート出力
- バッチ処理
- システムリプレースやシステム統合に伴うデータ移行
バッチ処理、あるいはデータ移行に関しては特に難しいことはないと思います。
これらは「決まった時間内にまとまったデータを処理する」というハードルはありますが、実行されるクエリが固定されるので必要なインデックスが設計・実装の段階で明確に分かりますし、本番を想定したテストで不足に気づいたり、いろいろ試したりすることができます。
つまり、事前に対処しておくことができるわけです。
一方、データ検索や帳票・レポート出力に関しては、ユーザーが任意の条件を指定する仕様になることが多いため、事前にインデックスを作成しておいてもうまく機能しないケースがあります。
そして、こういうのは開発やテストでは気づかず、しばらく運用してから「最近、検索が遅いんですけど・・・」という連絡を受けて発覚するケースが多いですね。
こういう機能で検索パフォーマンスの問題が生じた場合、実際に実行されているクエリを分析して、必要なインデックスを作成するという対処が必要になりますが、指定できる検索条件が多ければ多いほど、検索条件の組み合わせ数も多くなるので、インデックスでは根本的な対応が難しくなっていきます。（なので、「絶対に必要な条件だけ実装する。あったらいいな～というものは実装しない。」という心がけも必要です。）
クラウドを利用する場合はスケールアップで対応することも可能ですが、「夜間のバッチ処理の間だけ」というようなピンポイントな対応ならともかく、日常的に使用する機能のパフォーマンス対策としては適切とは言えないでしょう。
インデックスが効かないパターンを減らす
「その条件だとインデックスが効かないよ」というパターンを極力減らしていくことも、設計の段階で注意したいところです。
たとえば、データ検索時に「IS NULL」を使うとインデックスは効きませんので、できるだけNULLを持たないようにテーブルを設計します。
また、文字列のLIKE検索で後方一致や中間一致を使った場合や、OR条件を使っている場合もインデックスが効きません。
こういった条件は前述の「ユーザーが任意の条件を指定する検索機能」では必ず要望として出てくるので頭が痛いところです。
インデックスによるINSERT・UPDATEのレスポンス低下
インデックスを作成した場合、データの追加・更新時にインデックスの更新処理も同時に行われるため、INSERTやUPDATEのレスポンスが悪くなるという話があります。
たしかにINSERTやUPDATEのレスポンスは低下するのですが、そのデメリットよりも、検索パフォーマンスで得られるメリットの方が大きいので、よほどたくさんのインデックスをベタベタと貼りまくっていない限りは運用上大きな問題になることは少ないのではないかと思います。
インデックスが効かなくなってしまうケース
運用が進むにつれて実行されるクエリのパターンが変化し、当初は効いていたインデックスが効かなくなるということがあります。
また、アプリケーションを改修したことで従来のインデックスが効かなくなるというケースもあります。
アプリケーションを改修した場合、プログラムの実行結果についてはしっかりテストを行うと思いますが、インデックスに関しては意外と見落としがちです。
動的管理ビューで足りないインデックスを見つける
パフォーマンスが低下する問題は運用開始直後にはほとんど見つからず、しばらく運用してから現れることが多いですし、運用が進む中で必要なインデックスが変わっていくこともあるため、定期的に確認するのが望ましいと思います。
SQL Serverでは動的管理ビュー（DMV）で不足しているインデックスを調べられるので、ここで得た結果からどのようにインデックスを追加すべきかを検討することができます。
ただし、動的管理ビューの情報はデータベースが再起動されると消えてしまいますので注意しましょう。
SQL Server Management Studioで足りないインデックスを見つける
マイクロソフトが提供している統合管理ツールSQL Server Management Studio（SSMS）では、クエリの実行計画を確認した時に不足インデックスを教えてくれます。（CREATE INDEX文まで提示してくれます。）
条件が固定されているクエリであれば、こちらの方が分かりやすいと思います。
自動インデックス管理を利用する
Azure SQL Databaseには自動チューニング機能があり、その中にインデックスの自動管理機能も含まれています。
自動インデックス管理には「インデックスの作成」と「インデックスのドロップ（削除）」の2つがあり、規定値ではどちらもオフです。
この機能をオンにすると、データベースに対して実行されるクエリを継続的に監視して、必要なインデックスを作成したり、使用されていないインデックスを削除してくれたりします。
オン・オフの切り替えはAzureポータルでポチポチっと簡単に行うことができます。
必要なインデックスを自動で作成してくれることも重要ですが、不要なインデックスを削除してくれるのが嬉しいですね。
使われないインデックスが存在すると、INSERTやUPDATEのレスポンスが無駄に低下します。
先ほど「INSERTやUPDATEのレスポンスが低下するデメリットよりも、検索パフォーマンスで得られるメリットの方が大きい」と書きましたが、この場合はインデックスが使われていないのでメリットはゼロ。（ごく僅かであったとしても）不要なデメリットは解消しておきたいところです。
また、テーブルデータと同様に、インデックスもディスク領域をそれなりに使用しますので、不要なインデックスを削除することはその節約にも繋がります。
使われていないインデックスを人力で探すのは骨の折れる作業ですし、削除による悪影響があった場合のことを考えると「ま、いっか。」と放置することにもなりがちですので、利用状況にあわせて削除してくれるのはありがたいですね。
もちろん、一度削除したインデックスも、再度必要になれば自動で作成してくれます。
すべてのアプリケーションが定期メンテナンスの下で運用されているわけではありませんし、管理しているアプリケーションが多い場合などはできるだけメンテナンスの負担は減らしたいものです。
そういった意味でも、自動インデックス管理を利用して、パフォーマンスの低下が水面下で進行してしまうリスクを軽減するのは大いにアリだと思います。
まとめ（？）
なにかと難しいことの多いインデックスですが、効果的なインデックスを作成してパフォーマンスが劇的に向上した時にはなんとも言えない達成感があります。
個人的には、8時間以上かかっても終わらなかったバッチ処理が15分で終わったり、48時間放置して終わらなかったデータ移行処理が3時間程度で終わるようになった経験があります。（後者は単に必要なインデックスを貼り忘れてただけですが・・・）
最終的には自動管理で運用するにしても、その仕組みを知っておくことは設計や実装において大いに役立ちますので、また改めて勉強していこうと思います。
余談
今回のブログを書くにあたり、Azure SQL Databaseでいくつかの検証を行いましたが、ボタンをポチポチっとクリックするだけでデータベースサーバーが準備できて、検証が終わったらサクっと削除できて、費用としては2000円弱で済んじゃいましたので、いや～クラウドって本当に素敵だなぁって改めて思いました。（いまさら感）
オルターブース Advent Calendar 2021はまだまだ続きます。お楽しみに！！
