ここから本文です

リレーショナルDBで頻繁に更新されるカラムにインデックスをつけるべきか? た...

ide********さん

2011/6/511:57:25

リレーショナルDBで頻繁に更新されるカラムにインデックスをつけるべきか?

たとえば、何らかの状態(ステータス)を意味するカラムを持つテーブルがあり、
そのカラムが頻繁に参照される場合、検索を優先するならイン

デックスを
つけた方が良いと思いますが、ステータスの種類が多く、頻繁に更新される場合、
インデックスを更新する回数も増えるため、更新オーバヘッドによるシステム
パフォーマンスへの影響が気になります。

row数が数億というオーダだった場合、皆さんならインデックスを付けますか?

状態(ステータス)カラムの種類が3つ程度の場合と、10も20もある場合でも
変わってきますかね?

こうなってくると、チューニングの世界でインデックスを付けるかどうか判断する
ことになってきますか?

インデックスを付ける場合の良い指標があれば、ご教授願います。

閲覧数:
4,438
回答数:
4

違反報告

ベストアンサーに選ばれた回答

tum********さん

編集あり2011/6/522:01:33

これはpostgresに限らず、DB論ですが、頻繁に値が変わるカラム、というものはチューニングの指標が立ちませんので、速度アップ目的でindexなどを設定するのは困難です。
このことを、極端な例で説明します。
たとえばある瞬間は全て均等で20000種類の値だったカラムが、ある瞬間では全て同じ値に変わる、というケースを想定してみてください。
20000種類の値を持つ項目ならindexを切れば速度アップが見込めます。
しかし、全て同じ値を持つ項目なら、indexは邪魔以外の何物でもありません。
この両方を想定してチューニングすることなどできないでしょう。

このように一般的なDB論でもindexを切るべきではありませんが、特にpostgresの場合は(たとえばカラムの値が変更されるたびにメンテナンス時間が取れるなど)特殊な状況でない限り、切るべきではありません。
postgresは追記型DBなので、indexに更新が発生すると、インデックステーブルに削除フラグを立てて1行追加します。
つまりレコードが増加するのです。
このレコード数の増加はautovacuum対象外なので、気づくととんでもないサイズのインデックスファイルが出来ていることがあります。
しかも、この状況はreindexか9.0以降のvacuum fullをしない限り回復できません。
reindexもvacuum fullも(数億レコードに対するindexは恐らく最適状態でもトースト起こしてますので)長時間のフルロックが発生しますので、運用中は実行できないコマンドです。

よって、よほど特殊な状況でない限り頻繁に更新の入るカラムにindexを切っても速度低下を招くことになるでしょう。

質問した人からのコメント

2011/6/7 21:53:14

降参 ご回答がありがとうございます。
なるほど。頻繁に変わるカラムにインデックスを付けるのは、よほどの理由(メリット)が無い限り、やめた方が良いということですね。

さらにPostgre(追加型DB)の場合は、データの物理削除に関して運用を含めて十分に考慮する必要がありますね。
大変参考になりました。
ありがとうございました。

ベストアンサー以外の回答

1〜3件/3件中

並び替え:回答日時の
新しい順
|古い順

プロフィール画像

カテゴリマスター

nor********さん

2011/6/521:41:45

「ステータスの種類が多く、頻繁に更新される場合、インデックスを更新する回数も増えるため、更新オーバヘッドによるシステム
パフォーマンスへの影響が気になります。」
基本的に状態の種類というと、あって10未満でしょう。
これぐらいなら、INDEX検索より全件読み込みの方が速いです。INDEXは着けないほうがいいでしょう。
行数が何億となると30パターンぐらいでもフルスキャンの方がスループットがいいと思います。

難しいのは「最終更新時」などですね。それなりに絞り込みができる可能性はありますが、更新が多いテーブルではINDEXツリーの高さが増し、INDEXデータもフラグメンテーションを起こします。結果的にINDEX経由のアクセスのコストが大きくなります。
7×24時間稼働のシステムでなければ、保守時間でINDEXの再構築を行うことで対応することも検討対象になると思います。
(oracleなどでは負荷の少ない時間帯でのrebuild onlineも考えられます)

jac********さん

2011/6/517:12:20

インデックスの目的はとにかく「速さ」ですから、
実際に動かしてみて速い方(速くしたい方)を選ぶのが
最善でしょうね。

たしかに、row数が数億のような巨大データの場合であれば、
検索では速いが更新が遅くなる・・・というケースも考えられます。
そんな場合は、
可能であればテーブルを分割する道を探すと思います。
たとえば、店舗A、B、Cをそれぞれ同じ設計の
別テーブルに保存するなどして、一つのテーブルに負荷が
かかり過ぎないようにするとか・・・
そういう設計なら、インデックスも効率的になりますし、
万が一、ある店舗でデータ障害が発生しても、他の店舗に影響が
及びにくいなど他のメリットもあります。

場合によりけりですが、
必ずしもインデックスだけに頼らない方がいいかも、
という感じでしょうか。

jho********さん

2011/6/512:28:56

おっしゃっているステータスは何種類くらいあるのでしょう?例えば「未処理状態」「受注状態」というようなイメージで、一つのカラムにいくつの状態があるかです
一般に使われるインデックスは、カラムに保存するデータの種類が少ない場合は、あまり効率よく検索できません。また、種類が10種類として、各種類が均等な時もあまり役にたちません。
状態がわかりませんので何とも言えませんが、インデックスをつけた場合、そこそこ弊害がありそうです

あわせて知りたい

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる