ごきげんよう、あすみです！これはNE Advent Calendar 2022の24日目の記事です！

突然ですが、みなさんの読書スタイルはどんな感じですか？？今時はやっぱりKindle端末が主流なのでしょうか。でも、紙で読むのも好き！って人いますよね。

私はですね・・・もっぱらKindle端末派でして、寧ろ紙じゃない選択肢を取る様になったからこそ今年たくさん本を読めました。 Kindle端末で読む様になってから気づいたのですが、私、紙の本で読むことがそもそもストレスで中々やってなかったんだなあということに気付いたんです。手で押さえておかないと「ﾊﾞﾌッ！！」って閉まったり、そもそも技術書って大きかったり重たかったり・・・。ところがどっこい、Kindle端末だとこれがない。いやあびっくりするほどサクサク読書が進みました。笑

また、私は覚えておきたいな、とか、感動したところに関してはメモを取っておきたい性格なので、左にKindle端末、右にiPadの体勢を取って読みます。

これは今日紹介する本の読書メモの一部なのですが、後から見返した時にギリわかるくらいの抽出でメモをとっています。と言っても、今回紹介する本は割と定義の部分から覚えておきたいところがいっぱいだったのでそれなりに細かい粒度で書いてます。いつもはもっとメモ。

さてさて本題に入りますが、今日は「SQLパフォーマンス詳解」という本を紹介したいと思います！ 200ページの本ですが、元々DB・SQL周りはそんなに強くない私にとっては結構密度が大きく・・・！5日くらいかけてじっくり読みました。

SQLパフォーマンス詳解: 開発者のためのデータベースチューニング解説書

どんな本？

本の中身を少し紹介:「複数のインデックス vs 1つのインデックス？」

はい、記事のタイトルにもなっていますね。こちら、12月に社内勉強会をやったのですが、その時に作ったLT内容でもあります。

議題

SELECT number, name  FROM pokemons WHERE classification = ‘?’ AND series = ‘?’;

上記の様なSQLがあった時、下記のどちらがいいのでしょうか？

複数のインデックス → INDEX (claasficication) と INDEX(series)
1つのインデックス → INDEX(classfication, series)

1つのインデックス、と言っている方は「マルチカラムインデックス」というやつですね。

答え

時と場合によるのですが、多くの場合は「マルチカラムインデックス」の方が最適と本書に教えてもらいました。なぜなのか？の部分について・・・インデックスのそもそもの構造を考えながら解説をしていきます。

インデックスとは？

SQLインデックスの内部構造 : SQLインデックスとは何か

インデックスは、データベースの中で特有の構造を持ち、create index 文で作成されます。独自のディスク領域を必要とし、インデックスを張られたテーブルのデータを保持します。これはつまり、インデックスは純粋に冗長な構造であるといえます。インデックスを作成しても、テーブルのデータは変更されず、テーブルを参照する新しいデータ構造が作られるだけです。

図にするとこんな感じ。インデックスは「辿りやすいデータ構造」が「テーブルのデータとは別に」作られます。（図のピンク背景の部分）だからこそ、「冗長な構造」なのです。

つまり、インデックスの張られているデータ構造を検索する時に実際に走っている処理は、下記となっているのです。

ノードを辿る →
ノードを辿る → …
リーフノードに到着（ = DBのどこにデータがあるかを特定！）→
データにアクセスして、値を取得する

複数のインデックス VS マルチカラムインデックス

上記の構造を踏まえつつ、両者の実際に走っている処理がどうなってるか考えると、

複数のインデックス
- 1つめのインデックスに対して
  - ノードを辿る → ノードを辿る → ... → リーフノードに到着
- 2つめのインデックスに対して
  - ノードを辿る → ノードを辿る → ... → リーフノードに到着
- 上記で出た2つの結果をまとめる
- アクセスするデータが決まったのでテーブルアクセスして欲しいカラムを取得
マルチカラムインデックス
- ノードを辿る → ノードを辿る → ... → リーフノードに到着
- アクセスするデータが決まったのでテーブルアクセスして欲しいカラムを取得

な感じになっており、複数のインデックスだと「上記で出た2つの結果をまとめる」などの処理が入ってきてしまい、この時間を考えると、マルチカラムインデックスの方が処理が速句なることが多いよね、ということなのです。なるほど〜〜〜〜

注意: WHERE A < ‘?’ AND B < ‘?’の時はこの話は適用できない

構造を考えればわかることですが、Bツリーインデックスでこのようなクエリをサポートできません。どうしても、フィルタ述語が必要になってきてしまうからです。

ここについて長く書くと、今日寝る時間がなくなってしまいそうなので、サイトをペタっと貼っておきます・・・

Oracleフィルタ述語はパフォーマンスに対する大きなリスク

小話: インデックスを作りすぎると遅くなる？🤔

インデックスの構造がわかってしまうと、「インデックスを作り過ぎると遅くなる」という話も理解しやすくなります。

インデックスというのは、「別のデータ構造ができる」ので、「実際にDBが更新」された時、インデックスを構成するデータ構造の更新もする必要がある、ということになります。つまり、「インデックスを作る」と、「インデックスの方のデータ構造の更新」分の時間が増えていくことになります。これが、インデックスは作りすぎない方がいいね〜という話だったのでした。

さらに小話: DWHなどの場合

DWHなどは完全な同期更新ではなく、「一定経ったら完璧にするの保証するわ！！」としています。（結果整合性モデル）その代わりにインデックスめっちゃ張りまくって検索爆速にするから、めちゃデカデータでもサクッと検索できるようになってるらしいです・・・🙆‍♀️ （データの同期はアクセスが少ない時間でやっておくことが多い、早朝など🌞）