🍱

達人に学ぶDB設計徹底指南書を読んで学びになったこと[物理設計編]

2023/07/10に公開

技術書

tech

達人に学ぶDB設計徹底指南書を読んで学びになったことをまとめていくシリーズ
今回の記事は主に物理設計のインデックスに関してまとめていく。

前回の記事はこちら

インデックスとは？

インデックスとはプログラミング的な表現をすると (x, a)という形式でデータが保持される配列
x はキー値、a は実データやポインタが保持される

インデックスがパフォーマンスを上げる有効な手段となる理由

アプリケーション、データ透過性
性能改善の効果が高い

インデックスを貼ってもアプリケーションコードやテーブルのデータ構造に影響を及ぼさない。そのためインデックスを利用するハードルが低い。(影響を及ぼさないことを「透過性」と言う。)
また、データ量が増えていけばインデックスの性能も劣化するが、その劣化のスピードはデータ量増加に対して緩やかなものとなっている。
透過性が高いにも関わらず、性能改善の効果が高いため、インデックスはDBのパフォーマンスを上げるために有効な手段となり得る。

B-tree インデックスとは？

インデックスは複数の種類があるが、抑えるべきインデックスは B-tree である。
B-tree とはその名の通り、木構造でデータを保持する。B-tree が平衡木の構造でデータを格納しているため、どのデータを取り出そうとしても同じスピードで取り出すことができる均一性がある。

平衡木の説明はこちら

またB-tree インデックスはテーブルのデータが増加しても性能劣化が緩やかなため、データ量が増加しても検索、更新にかかる計算スピードが変わらない。インデックスを利用しないフルスキャンの場合はデータ量の増加に比例して検索、更新にかかる時間は大きくなる。

MySQL のインデックスは特別な指定をしない限り、B-tree インデックスが適応される。

B-tree インデックスを利用するべきポイント

データ量が多い
カーディナリティが高い

データ量が多い

データ量が少ない場合はインデックスを貼った時の速度と、フルスキャンによる検索の速度はそんなに変わらない場合がある。（フルスキャンとはテーブル全てのレコードを検索する方法）
データ量が少ないという場合の目安はレコードが10,000行以下の場合。

カーディナリティが高い

カーディナリティはインデックスを貼るかどうかの最も大切な判断材料になる。カーディナリティとは特定のカラムがどのくらいの種類を持つかを表す概念である。
例えば「性別」を格納する場合には「男性」、「女性」、「選択しない」の３種類しか基本的に格納しないためカーディナリティは低いと言える。
しかし「メールアドレス」は、作成されるパターンが何万もの組み合わせがあり、基本的に一意な情報しか格納されないため、カーディナリティが高いと言える。
基本的にカーディナリティが高いカラムにインデックスを貼ることが好ましいとされる。

カーディナリティが高い目安は 「その列をキーに絞り込みを行った結果、全体の5％程度に絞れるかどうかが一つの基準になる」 と述べられている。

例えば「性別」カラムの場合、入る値が３種類に限定されるので（大枠は２種類）、ほぼ性別が半々に分布されている前提であれば、男性で絞り込んだ場合には全体の40%から50%近くに男性を表すカラムが格納されていることになる。この割合は5%よりも大きいので、カーディナリティが低いとされ、インデックスを貼ってもあまり意味がないとされる。

複合カラムに対してインデックスを貼る場合は、カーディナリティは対象の複数のカラムの組み合わせで考える必要がある。
それぞれ単独のカラムのカーディナリティが低くても、組み合わせることで高いカーディナリティとなる場合はインデックスを貼ることが有効である。

また、たとえカーディナリティが高くても特定の値にデータが集中しているカラムに対してはインデックスを貼ることが有効ではない。

カーディナリティが平均的に分散しているカラムに対して、インデックスを貼ることが効果的と言える

意味がないインデックスの使い方

インデックスは基本的に検索条件や結合条件にとして利用されるカラムに貼られるが、以下のような場合にはインデックスを貼っても意味がない。

インデックスを貼ったカラムに演算をおこなっている
IS NULL 述語を利用している(注意点あり)
否定系を利用している
OR を利用している(注意点あり)

インデックスを貼ったカラムに演算をおこなっている

SELECT * FROM users WHERE id + 1 = 100;

インデックスの中で保持されるデータはあくまでそのカラムの値のみである。(idの値のみ)
そのため、インデックスを貼ったカラムに演算をおこなっている場合はインデックスを利用することができない。

インデックスを貼ったカラムに対して、演算を用いた検索をしたい場合は式変換で対応することができる。

SELECT * FROM users WHERE id = 100 - 1;

IS NULL 述語を利用している(注意点あり)

SELECT * FROM users WHERE id IS NULL;

一般的にインデックスはNULLに対してデータの値とはみなしていないため、保持していない。そのため、インデックスを貼っているカラムに対して IS NULL ,IS NOT NULL を用いても使用することはできないと、この本の中で述べられている。

ただ、このブログを書くにあたって調べていくと、MySQL は　NULL にもインデックスを利用できることが分かった。NULL をデータとしてみなすかは DBMS の実装に依存する。

MySQL は、インデックスと範囲を使用して、IS NULL を含む NULL を検索できます。

否定系を利用している

SELECT * FROM users WHERE NOT id = 100;

否定系はインデックスを利用することは出来ない。
利用したとしても検索対象が広範囲になため役にたたない。

OR を利用している(注意点あり)

この本の中で OR を用いた検索もインデックスが利用できないとされているが、こちらも注意点がある。MySQL の場合は、OR を用いた検索でも検索にしている全てのカラムにインデックスが貼られていればインデックスを利用することができる。

参考： https://zenn.dev/team_soda/articles/ce989fa4cbe4c2

MySQL5.6より前では、1テーブルに対する1クエリについて、1インデックスしか使わない仕様だったようです。そのため、検索条件に使用している異なるカラムすべてに個別にインデックスを張っていたとしても、1つのカラムだけしかインデックスを使えないので、他のカラムはフルテーブルスキャンするしかありません。こうなるとまたオプティマイザは「どうせフルテーブルスキャンするんじゃん...」と言って、すべてのインデックスの使用をやめてしまったのでしょう。これがMySQLでOR演算子を使って検索するとインデックスが効かなくなると言われてきた理由だと思います。

まとめ、感想

B-tree インデックスは平衡木の構造でデータを格納している
インデックスはデータ数が多く、カーディナリティが高いカラムに貼るべき
演算や否定形の中でインデックスを用いたカラムを利用しても検索の処理が早まることは無い

今までなんとなく貼っていたインデックスであったが、この本を読み、周辺知識を調べたことで貼るべきパターン、利用できないパターンを学ぶことが出来て勉強になった。