Help us understand the problem. What is going on with this article?

インデックス・ショットガンを現場から補足

More than 1 year has passed since last update.

インデックス・ショットガンを現場から補足

by masudakz
1 / 15

第26回 中国地方DB勉強会 in 岡山

@masudakz 増田和弘
2019-02-02


名著「SQLアンチパターン」

Bill Karwin 著、和田 卓人、和田 省二 監訳、児島 修 訳
http://www.oreilly.co.jp/books/9784873115894/
2013年1月 初版
image.png


12章 インデックスショットガン

闇雲インデックス

  1. インデックスを定義しない
  2. インデックスを定義しすぎる
  3. インデックスを活用しないクエリを実行してしまう

ある日のレビュー前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を追加しないといけない
組みあわせ数の増え方は急速だ!
組みあわせ爆発のおそろしさはみんな知ってる!


サービスの紹介

スクリーンショット 2019-01-31 22.03.22.png


現実の計算量

子供の数:各学年ざっと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より

「実際には、すべてのアプリケーションに当てはまる答えはありません。」
「あなたこそが、誰よりもデータと運用要件について知っている人間だからです。」
「最も重要なルールは、推測のみに基づいて、闇雲にインデックスをつけてはならない」

masudakz
生SQL職人の朝は早い. 「余分なカンマ1つ、空白1つの不足でプロセスが死ぬんだ」職人の目が光る. 「JPAがよさそう?駄目だよ,自由がないから」執拗に手書きに拘る. 「実行計画には頼りませんよ」レガシーな処理系が消えるには約10年は掛かるという. 「これからはNoSQL?俺は認めんね」
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away