Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

Indexについて整理 5

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

PostgreSQL10でHash Indexが強化! 13

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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