インデックスの種類 2020-01-25 第28回 中国地方DB勉強会 @patorash 1 

自己紹介 名前：尾古 豊明（おこ とよあき） twitter： @patorash 所属：株式会社リゾーム システム開発部 役職：専門職 仕事内容： ショッピングセンターに関連するサービス開発 Ruby on Railsで自社サービスの開発・運用（7年目） データベースについて詳しくなりたい。OSS-DB Silver取得済み。 現在、データベーススペシャリスト試験に向けて勉強中 2 

OSS-DB Silverを受けた頃のワイ 3 よくわからんけど普通に使うのB-Tree Indexだけじゃない？ B-Tree Index理解しとけばいいや。 よし、Index完全に理解した。 

データベーススペシャリストの勉強をしてるワイ 4 まったくわからない。 俺は雰囲気でIndexを使っている。 

Indexについて整理 5 

Indexの種類 ● B-tree Index ● Bitmap Index ● Hash Index ● その他（GiSTとか）※今回は取り上げない 6 

B-tree Index ● PostgreSQLのデフォルトのIndex ● MySQLはストレージエンジンによって デフォルトは異なるが、 Inno DBだと デフォルトのIndex ● 木構造で作られる ● BはBalanced ● カーディナリティ（選択性）が 高い列に有効 ● カーディナリティが低い列だと フルスキャンが発生する ● 範囲検索でも使える 31 17 20 35 42 10 15 32 33 37 39 50 52 18 19 25 30 ※EXAM PRESS うかる！データベーススペシャリスト より抜粋 7 

Bitmap Index ● Bitmap形式のIndex ○ 0と1で表現 ● カーディナリティの低い場合に有効 ○ 性別、権限、カテゴリー等の種類の少ない ケースのこと ● PostgreSQLの場合、明示的にこの Indexを 指定することはできないが、実行時に Bitmapを作成し、それを使ってスキャンする ことがある ○ Bitmapの作成にコストがかかるが、 それでもフルスキャンに比べるとマシ というケースで発動 8 性別 権限 浜田 男 管理者 松本 男 編集者 今田 男 ゲスト 東野 男 編集者 板尾 男 管理者 蔵野 男 ゲスト 篠原 女 編集者 YOU 女 ゲスト 

Bitmap Index ● Bitmap形式のIndex ○ 0と1で表現 ● カーディナリティの低い場合に有効 ○ 性別、権限、カテゴリー等の種類の少ない ケースのこと ● PostgreSQLの場合、明示的にこの Indexを 指定することはできないが、実行時に Bitmapを作成し、それを使ってスキャンする ことがある ○ Bitmapの作成にコストがかかるが、 それでもフルスキャンに比べるとマシ というケースで発動 9 男性 女性 管理者 編集者 ゲスト 浜田 1 0 1 0 0 松本 1 0 0 1 0 今田 1 0 0 0 1 東野 1 0 0 1 0 板尾 1 0 1 0 0 蔵野 1 0 0 0 1 篠原 0 1 0 1 0 YOU 0 1 0 0 1 

Bitmap Index ● Bitmap形式のIndex ○ 0と1で表現 ● カーディナリティの低い場合に有効 ○ 性別、権限、カテゴリー等の種類の少ない ケースのこと ● PostgreSQLの場合、明示的にこの Indexを 指定することはできないが、実行時に Bitmapを作成し、それを使ってスキャンする ことがある ○ Bitmapの作成にコストがかかるが、 それでもフルスキャンに比べるとマシ というケースで発動 ● 男で管理者、という条件にしたら、浜田と板 尾がすぐに浮かび上がる 10 男性 女性 管理者 編集者 ゲスト 浜田 1 0 1 0 0 松本 1 0 0 1 0 今田 1 0 0 0 1 東野 1 0 0 1 0 板尾 1 0 1 0 0 蔵野 1 0 0 0 1 篠原 0 1 0 1 0 YOU 0 1 0 0 1 

Hash Index ● 値のHash値を作るIndex ● 完全一致の際に効果的 ● 範囲検索はできない ● Indexの物理量の増え方が一定になる ● PostgreSQLでは使えるが非推奨 ○ WALに書き込めない ○ ストリーミングレプリケーションで 使えない 11 メールアドレス Hash値 浜田 [email protected] 723E7 松本 [email protected] 83AB4 今田 [email protected] 15D1C 東野 [email protected] 9AB2F 板尾 [email protected] 699FE 蔵野 [email protected] 1DAF8 篠原 [email protected] 3E783 YOU [email protected] AB415 

OSS-DB Silverを受けた頃のワイ 12 よくわからんけど普通に使うのB-Tree Indexだけじゃない？ B-Tree Index理解しとけばいいや。 よし、Index完全に理解した。 

PostgreSQL10でHash Indexが強化！ 13 

Hash Index ● 値のHash値を作るIndex ● 完全一致の際に効果的 ● 範囲検索はできない ● Indexの物理量の増え方が一定になる ● PostgreSQL 9.xでは使えるが非推奨 ○ WALに書き込めない ○ ストリーミングレプリケーションで 使えない ● PostgreSQL 10以上で使える！ ○ WALに書き込める！ ○ ストリーミングレプリケーションで 使える！ 14 メールアドレス Hash値 浜田 [email protected] 723E7 松本 [email protected] 83AB4 今田 [email protected] 15D1C 東野 [email protected] 9AB2F 板尾 [email protected] 699FE 蔵野 [email protected] 1DAF8 篠原 [email protected] 3E783 YOU [email protected] AB415 

Hash Indexを試してみた ● データ1万件程度 ● ユニーク制約のついた列 ● 完全一致で利用 ● B-Tree Indexと速度面・データ量で比較 15 

結果 ● 検索速度はB-Tree Indexと、ほぼ変わらず ● 実行計画では、Hash Indexでは若干コストが低い ● Indexのデータ量も1万件程度だと、ほぼ変わらず ○ 行数が増えるとHash Indexのほうがデータ量的に少なくて済んだという記事を見た （@nuko_yokohamaさんの記事） ○ 1000万行入れた時のデータについて。 ○ https://qiita.com/nuko_yokohama/items/05ece8313af0f81a870d 16 

まとめ B-Tree Indexを使っておけば、基本は大丈夫。 Bitmap Index はクエリ実行時に自動で作られる。（B-Tree Indexは必要） Hash Indexは（ほぼ）ユニークで完全一致のケースならば有効。 使ってみてはいかがでしょうか？（ただし、PostgreSQL 10以上） 適用できそうな例： ● メールアドレス ● 電話番号 ● ユーザーコード ● 認証系 ● アクセスキー ● アクセスシークレット 他にありそうな例があったら教えてください！ 17