第26回 中国地方DB勉強会 in 岡山
@masudakz 増田和弘
2019-02-02
名著「SQLアンチパターン」
Bill Karwin 著、和田 卓人、和田 省二 監訳、児島 修 訳
http://www.oreilly.co.jp/books/9784873115894/
2013年1月 初版
12章 インデックスショットガン
闇雲インデックス
- インデックスを定義しない
- インデックスを定義しすぎる
- インデックスを活用しないクエリを実行してしまう
ある日のレビュー前DDL
CREATE TABLE c_results (
c_result_id bigserial not null PRIMARY KEY
, c_id varchar(30) not null
, child_id bigint not null
, study_begin timestamp not null
, study_end timestamp not null
, elapsed_time integer not null
, create_on timestamp not null
, CONSTRAINT child_id_study_begin_c_id_unique UNIQUE (child_id, study_begin, c_id)
) ;
INDEX定義
CREATE INDEX c_results_c_id_idx
ON c_results USING btree (c_id);
CREATE INDEX c_results_child_id_idx
ON c_results USING btree (child_id);
CREATE INDEX c_results_study_end_idx
ON c_results USING btree (study_end);
- 全部のカラムじゃない
- 3カラムとも絞込条件に頻出しそう
- 実際にはもっとカラムがあります。
よさげ？
「うまい手」に見える
3本独立したindexをもっておけば、どう組みあわせたSQLでも、hitsの集合操作で高速
PostgreSQL には Bitmap Index Scan あるし
複合キーは嫌？
btree(child_id, study_end) だと、この2つのAND条件など、評価順が(child_id, study_end)になるタイプのSQLにしか有効にならない。
c_idも使いたいときは別のindexを追加しないといけない
組みあわせ数の増え方は急速だ！
組みあわせ爆発のおそろしさはみんな知ってる！
サービスの紹介
現実の計算量
子供の数：各学年ざっと100万人＝潜在市場
サービスが軌道にのったら、study_beginの24h範囲で 50万hit, child_idで 2000hit, c_idで 30万hit くらいはすぐ。
PostgreSQL の Bitmap Index Scanではサービス性能要件にちと足りない
類似サービスでの記録
-> Bitmap Index Scan on c_results_study_end_idx
(actual time=172.871..172.871 rows=1441720 loops=1)
Index Cond: ((study_end >= ‘2018-12-01') AND (study_end < ‘2018-12-02') AND (child_id = xxxxx))
-> Bitmap Index Scan on c_results_child_id_idx
(actual time=0.163..0.163 rows=1340 loops=1)
Index Cond: (child_id = xxxxx)
Rows Removed by Filter: 1
Total runtime: 180.127 ms
rows=144万!
複数キーindexで分解能を上げる
btree(child_id, study_end) を使って直近resultsをとるなら、index scan 1回
btree(child_id, c_id, study_begin) を使って、IN句で指定した数件のc_id別の直近一覧をとるなら、IN句の指定と同じ回数の index scan。
複数キーindexを使って、それが使えるSQLだけでサービス構成しないと、平均応答時間10-50msecのサービスはできないです。
改修後
CONSTRAINT childid_c_id_study_begin_unique UNIQUE (child_id,c_id,study_begin)
CREATE INDEX c_results_child_id_study_end_idx
ON c_results USING btree (child_id, study_end));
indexを1本にして、UNIQUE制約の順番も、サービスで使いそうなカラム順序になりました。
MENTORの原則
- Measure（測定）
- Explain（解析）
- Nominate（指名）
- Test（テスト）
- Optimize（最適化）
- Rebuild（再構築）
SQLアンチパターン P.141より
「実際には、すべてのアプリケーションに当てはまる答えはありません。」
「あなたこそが、誰よりもデータと運用要件について知っている人間だからです。」
「最も重要なルールは、推測のみに基づいて、闇雲にインデックスをつけてはならない」