4.1INDEXの仕組み
4.2INDEXの役割
indexはテーブルからデータを高速で取り出すためのRDBMSの仕組み
BTreeINDEX ex:二分探索木やAVL木を元にしたBTree構造に基づくインデックスは「ルートノードと子ノード、リーフノード」から のような形で検索している(検索の仕方はAVL木とめちゃめちゃ似てる)
4.3アンチパターンを生まないために
INDEXを適切に利用する為には -> * INDEXの仕組み・RDBMSがどのように利用するかを知る * INDEXを利用するためのテーブル設計
ex: * 検索結果がテーブル全体の20%未満 検索対象のテーブルが十分に大きい ->場合にはINDEXは使われやすい。
- 条件にその列を使う
4.4INDEXの使い方まとめ
- INDEXの特性をしっかり把握して適切なINDEXを設定する
- INDEXを利用できるクエリを実行する
- INDEXを活用できるテーブル設計をする
- スロークエリログやデータの状態をしっかりモニタリングする
cf:『SQLアンチパターン』 「インデックスショットガン(闇雲にINDEXを設定しまくることを指す)」 INDEXを設定することで INSERT/UPDATE/DELETE が遅くなる問題がある。 複雑な複数列に対するINDEXを設定しすぎると オプティマイザが不適切なINDEXを選ぶことがあるためです。
*MENTORの原則に基づいて対応することが必要 Mesure(計測) Explain（解析） Nominate（指名） Test(試験) Optimize（最適化） Rebuild（再構築）
- またINDEXは一般的に追加よりも削除の方が難しい。
- INDEXまた実体のあるデータであるがためにディスク容量も増えるために、 作りすぎないようにする必要がある
-> 1. このテーブルは１、３，５年後どのくらいの大きさになるか 1. このINDEXは複合INDEXでまとめる、または単一のINDEXで十分絞り込めるのではないだろうか 1. 今このINDEXを貼るべきか
cf:『SQLパフォーマンス詳解』
5.1 削除フラグの危険性について
5.2 とりあえず削除フラグ
-> テーブルに削除の「状態」を持たせている点(問題点)
- クエリの複雑化
- UNIQUE制約が使えない
- カーディナリティが低くなる
5.3 アンチパターンを生まないためには
- 事実のみを保存する(これを大原則とする)
*テーブルに「状態」を持たせるのはダメか * 対象のテーブルが小さく、INDEXが不要 * そのテーブルが関連するテーブルの親になることがなく、 データを取得する際に頻繁にJOINの対象になることがない * UNIQUE制約が不要で、外部キーでデータの整合性を担保する必要がない
->以上は、リファクタリングの際に困難が生じるのは前提として使用する
5.4 フラグのまとめ
https://www.slideshare.net/yoku0825/mysql-52276506?next_slideshow=1 https://www.slideshare.net/t_wada/ronsakucasual?next_slideshow=1 https://www.slideshare.net/yoku0825/mysql-52276506?next_slideshow=1
など本以外の資料もある。
statusカラム ->このケースを取り出すために、 where句を利用する・view側でのバグを防ぐためにif文でチェックを入れる必要が発生する。
送信ステータス(ex:メルマガ) ->
- 配信済みのメールもテーブルに残るためテーブルの肥大化につながる
- トランザクションの問題 (メール送信中は配信が重複しないようにメールの送信者リストに対して排他的ロックをとって管理する必要がある)
6.1ソートの依存性(order by)
6.2リレーショナルモデルとソートの仕組み
- リレーショナルモデル
- 重複がない
- 実在する要素しかない(nullがない)
- 要素に順序がない
ソフトウェアとしてのRDBMSは上記を拡張して作成された。 cf:『理論から学ぶデータベース実践入門』
- ORDER BY のしくみ from on join where group by having select distinct order by limit
の順で評価する。 ->このように、order by はデータが大きければ大きいほど重い処理をする。
where 句狙いのindex 事前にwhere 句を使うことで対象を絞りこむことができる。
order by句狙いの index BTreeINDEXはデータを「ソート済」の状態 ソート処理が不要になる 評価数がLIMITに達した段階で結果を返せる。
6.3 order byを早くするには
データを小さくする
- INDEXを活用する
ページャーとしての活用 created_atなどが順番通りに並んでいることを期待し、idをwhereで指定して取得 * データ量が増えてもINDEXを活用できるため高速 * ページ数が深くなってもoffsetを利用しないために取得業が深くならない。
ページャの難しさ * 途中のデータが削除された場合の表示方法(ズレる、表示されずに飛ばされる行が発生する)
大きなデータをソートしたい場合 1. アプリ側でソート (RDBの負荷を下げることができる。データサイズが大きい場合通信がボトルネックになる)
- ソート済みの結果をキャッシュして利用 (ソートの処理が決まっていて、結果が変更されにくいデータの場合キャッシュが有効、 表示結果が同じ場合はhtml自体をvarnishなどのHTTPアクセラレータでキャッシュする・ CDNを使ってJSONをキャッシュするなどの手法がある)
1.NoSQLなどを利用してソート
6.4order byと where句狙いについて
*改善する際には実行計画を見る
改善するためにはアプリへの影響が大きく、後から修正するのが難しい。 ソートに関する検討・実装は必要
cf:redis 高速に動作するインメモリ型キー値データ構造ストア * 永続的にデータを扱うことは苦手 * 保存できる容量が制限されている
=>キャッシュ・セッション管理・キュー・ソートなどでよく利用される