SQL実践入門の書籍の中の第10章を読んでみました。
www.amazon.co.jp
個人的に気になるところや、重要な部分を抜粋しながら紹介していきたいと思います（完全な個人的メモです）。
RDBで使われるインデックスの種類
RDBでは基本的には以下3つの種類のインデックスが使用されるようです。
- B-treeインデックス
- ビットマップインデックス
- ハッシュインデックス
インデックスに上記のような種類が存在することは知っていましたが、正直どういう時にどのインデックスを使えば良いのかはわかっていませんでした。書籍ではそこの使い分けの細かい部分までは記載されていませんでしたが、通常であれば「B-tree」インデックスのみを知っていれば良さそうです。
どんなときにもベストなインデックスというわけではないけど、だいたい平均点がとれるようなインデックスという感じみたいです。
インデックスを有効活用するための条件
インデックスは必要なデータを取得するために作成するものですが、むやみに作成すれば良いわけではありません。誤ったインデックスを作成することで、逆に性能が悪化することもあります。そこらへんの話をしていきます。
インデックスをどの列に作成すればよいかの一つ目のポイント「カーディナリティ」
カーディナリティは値のばらつき具合を示す概念です。
たとえばあるネットショッピングのシステムで、注文テーブルがあるとします。注文テーブルでは、主キーとして「注文番号」を設定しているとします。主キーなのでもちろん注文テーブル内で注文番号は一切重複しません。
この場合には、「カーディナリティが高い」と表現します。
逆に、たとえば「商品名」は注文テーブルではまま重複することが考えられます（たくさんの人が同じ商品を買うことはよくあると思います）
この場合には、「カーディナリティが低い」と表現します。
インデックスは、「カーディナリティが高い」列に作成することがインデックスに選択する列の一つのポイントになります。
上記の例では、「商品名」ではなく「注文番号」にインデックスを作成するのが良いということになります。
インデックスをどの列に作成すればよいかの2つ目のポイント「選択率」
選択率とは、絞り込む列の条件が全体からみてどの程度絞り込むことができるかを示す概念です。
先ほどの注文テーブルの例で言えば、「注文番号」は主キーであるため一意となるため選択率は非常に低いことになります。
逆に「商品名」は選択率は注文番号に比べれば、高くなると思います。
インデックスは、「選択率が低い」列に作成することがインデックスに選択する列の2つ目のポイントになります。
上記の例では、「商品名」ではなく「注文番号」にインデックスを作成するのが良いということになります。また、選択率としては5～10%前後というのが目安ということみたいです。全体のテーブルの半分以上が同じ値を持つ列にインデックスを設定しても、選択率が高くなるのでインデックスではなくテーブルフルスキャンの方が速くなる可能性があるみたいですね。
インデックスによる性能向上が難しいケース
ここでは、インデックスによる性能向上が難しいケースを紹介していきます。
絞り込み条件が存在しない
そもそも要件として、絞り込みするための条件がないケースですね。このようなケースでは、絞り込み条件がないのでもちろんインデックスは使えません。かなり極端なケースですが、そのようなことももちろんあると思います。
ほとんどレコードを絞り込めない
これは、先ほどのケースでいえば注文テーブルの「注文ステータス」と呼ぶ列が存在するとします。データの抽出要件で、お届け済みの注文をすべて取得したいといった場合には、注文テーブルの多くのレコードが対象となります。
ある程度システムが運用している場合では、注文テーブルはほぼお届け済みですからほとんどレコードを絞り込むことができません。
これは先ほど紹介した「選択率」が高い状態となっていまい、仮にインデックスを作成していても逆効果になるパターンになりえるみたいです。
個人的には、このケースに該当するパターンをシステムでよく見たことがあります。カラムの種類が2つしかない列にインデックスとしている実装などもありました。これは上記のようにインデックスが逆効果になっているパターンだったのかと、勉強になりました。最初のうちは、抽出条件にある列にインデックスをとりあえず作成すればいいんでしょ、とか思っていたので何も知らないことは怖いですね。。。
他にも入力パラメータによって選択率が変動するケースがあります。これは、システムを使用するユーザーの入力内容によって、選択率が大きく変わるケースですね。たとえば、今回も先ほどの注文テーブルを例にしますが、注文日を列に持っているとします。
ユーザーによって、注文日の範囲検索ができるようなシステムである場合に、注文日を「2022-12-01」とすれば1日分の注文データしか検索されないため、選択率は低くなります。
しかし、注文日を「2022-01-01」～「2022-12-31」の範囲検索とした場合は1年分の検索となり、先ほどと比べて選択率は高くなってしまいます。
インデックスが使えない検索条件
インデックスを上手に適用するためには、ＳＱＬの書き方に一定の条件があります。すべてのRDBで同じではないと思いますが、一般的なものを紹介していますね。個人的には聞いたことがあったので、さらっと紹介します。
LIKE述語の中間一致・後方一致
これはよく言われるケースで、LIKE述語を使うあいまい検索では、前方一致（AAA%）のみインデックスが効きます。中間一致（%AAA%）、後方一致（%AAA）ではインデックスが効きません。
インデックスの列で演算を行っている
たとえば、注文テーブルに「注文数量」列があるとして「WHERE 注文数量 * 5 > 10」のような検索条件の場合にはインデックスが効きません（どんな条件なんだというのは置いておいて・・・）。
インデックスの列に関数を使用している
こちらは、注文テーブルの「商品名」列で言えば、「WHERE LENGTH(商品名) > 10」のような検索条件の場合にはインデックスが効きません。
インデックスの列で否定形を用いている
こちらも、注文テーブルの「商品名」列で言えば、「WHERE 商品名 <> 'パン'」のような検索条件の場合にはインデックスが効きません。
インデックスが使えない場合どう対処するか
今までの内容はインデックスを使う上で基本的な部分かなという感じでしたので、なんとな～く知っているものでした。ここら辺はとても参考になりました。インデックスを上手く使えない場合は、大きく2通りがあるということです。それぞれ簡単に紹介します。
- アプリケーション設計で対処する
- あくまでインデックスで対処する
アプリケーション設計で対処する「外部設計による対処」
ここで言及しているのは、インデックスが効かないようなSQLを実行しないようにシステムの画面設計を変えてしまおうというものです。なるほど、自由な条件で検索できるシステムでは性能劣化となるクエリが発生してしまうので、適切な必須条件をあらかじめ決めておくことで、適切なクエリのみが実行されるように制御するやり方ですね。
これしかないよなと思いつつも、最初からどの程度までは問題ないのかなどが分からなかったり、ユーザーによっては許してくれないこともありそうな気がしたりしますが、そこらへんの妥協点を見つけていくのもできるエンジニアなんでしょうね。本にも記載ありましたが、システムがほぼ完成した後にこのような入力制御の調整をすると、ほぼＮＧとなりそうな感じですね。難しい。。。
アプリケーション設計で対処する「データマート」
アプリケーション設計で対処するもう一つがデータマートを作ることです。
これは、大きなテーブルで必要なデータはその一部のはずなのでそのデータを切り離して、相対的に小さいテーブルを作成してしまうことです。そうすることで、アクセス対象データが小さくなるので、結果I/O量が減り性能向上につながるということですね。
こちらもアプリケーション修正が大変かなと思いつつも、画面を変更しないのでユーザーに何か言われることはない回避方法だなと思いました。外部設計の変更ができない場合の一つの修正案となりそうです。
データマートの注意点として、挙げられていたのがデータの鮮度、つまり作り直しをどのくらいの頻度でするかや、そのデータマートの数ですね。数が多すぎて訳が分からなくならないように注意が必要とのことです。データマート今まで作っているシステムを見たことはないのですが、設計書も作られなさそうなので、後で入った人がよくわからなくなりそうな気はします。
あくまでインデックスで対処する「インデックスオンリースキャン」
アプリケーション設計はしたくない場合は、こちらの方法しかありません。いい感じのインデックスを作ることで、性能向上させる技ですね。
インデックスオンリースキャンとは、実テーブルにはアクセスせずにインデックスのみでアクセスを完結させることのようです。SQL Serverでは、カバリングインデックスと呼んでいるをよく見たり聞いたりしますがそれのことみたいです。
通常であれば、インデックス→実テーブルのアクセスによって目的のデータを取得しますが、カバリングインデックスであればインデックスのみアクセスすることですべてのデータを取得することができます。それはつまり、インデックスに取得する列をすべて含ませるやり方ですね。
このやり方だけであれば、うまくいけばアプリケーションを修正しないでデータベースの設定（インデックス作成）のみなのでとても楽ですね。
注意点として、更新のオーバーヘッドが増える、仕様変更で列が増えたら、結局テーブルへのアクセスが必要になってしまうなどが挙げられてしました。
おわりに
結構なるほどなと思うところが多かったので、とても勉強になりました。パフォーマンスチューニングするときの基礎として、頭に入れておきたい内容だと思いました。パフォーマンスチューニングさらっとできる人はとても、すごいなぁと思います。
少しでもそんな人になれるように日々努力したいと思います。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。
