(cache) RailsエンジニアのためのSQLチューニング速習会@wantedlyに行ってきた！

『RailsエンジニアのためのSQLチューニング速習会 - connpass』に参加してきました。すごく勉強になったので、その時のメモです。@minami7o さんありがとうございました！

あとこの記事は、エムスリー Advent Calendar 2015 - Qiitaの13日目です。

スライド

Wantedlyの @minami7o さんの発表スライドです。

説明用のブランチ

勉強会で共有されたテストデータを使えるGitHubのブランチです。

git clone git@github.com:south37/sql-tuning.git
cd sql-tuning
bin/rake db:create
pg_restore -j 4 --verbose --no-acl --no-owner -d sql-tuning-dev db.dump

ActiveRecord::Base.explain

ActiveRecord::Baseに#explainがあることを知らなかったです。#explain超便利！

[1] pry(main)> Job.joins(:company).group('companies.country').where('companies.id < 1000').select('companies.country', 'COUNT(jobs.id)').explain
  Job Load (10.3ms)  SELECT companies.country, COUNT(jobs.id) FROM "jobs" INNER JOIN "companies" ON "companies"."id" = "jobs"."company_id" WHERE (companies.id < 1000) GROUP BY companies.country
=> EXPLAIN for: SELECT companies.country, COUNT(jobs.id) FROM "jobs" INNER JOIN "companies" ON "companies"."id" = "jobs"."company_id" WHERE (companies.id < 1000) GROUP BY companies.country
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1184.79..1191.12 rows=634 width=16)
   Group Key: companies.country
   ->  Hash Join  (cost=54.28..1159.79 rows=5000 width=16)
         Hash Cond: (jobs.company_id = companies.id)
         ->  Seq Scan on jobs  (cost=0.00..868.00 rows=50000 width=8)
         ->  Hash  (cost=41.78..41.78 rows=1000 width=16)
               ->  Index Scan using companies_pkey on companies  (cost=0.29..41.78 rows=1000 width=16)
                     Index Cond: (id < 1000)
(8 rows)

ちなみにEXPLAINとは、与えられた文に対して、PostgreSQLプランナが生成する実行計画を表示するための命令です。

実行計画とは、問い合わせ文が参照するテーブル（複数の場合もある）をスキャンする方法（単純なシーケンススキャン、インデックススキャンなど）複数のテーブルを参照する場合に、各テーブルから取り出した行を結合するために使用されます。

実行計画のコストの見方

Explainでのコストの見方はこちら。

indexの仕組み

「B-tree index」とは、バランスド・ツリーインデックスの略です。ソートアルゴリズムや二分木の進化版的なアルゴリズムだそうです。一部のブランチが成長しすぎて、計算量が増えないように再編成(バランシング)する仕組みをもっています。これにより、常に高い検索性能を保つことができるそうです。

indexが効かないパターン

- indexを貼ったカラムに演算(`lower`と演算子を使う場合、ただし後述の方法なら貼れる)
- 絞り込み条件のゆるいwhere (デフォルトだと4/1以下に絞り込まれる必要がある)
  - HDDへのランダムアクセスとシーケンシャルアクセスの速度差が原因

index利用のデメリット

- indexの更新に時間がかかる
- PostgreSQLのカラム更新を高速化するための仕組みの「HOT」が効かない

ちなみに「HOT(Heap Only Tuple)」とは、「インデックスを持たない、ヒープのみのタプル」だそうです。 (1)不要なインデックスの更新を行なわないことによる更新処理コストの削減や、(2)ガベージの自動回収といったことをサポートしてくれます。

インデックスの種類

- Unique Indexes : 重複した値を許可しないようなインデックス
- Multicolumn Indexes : 2つ以上のカラムに対するインデックス
- Indexes on Expressions
  - 関数などの返り値を key として index を作る事ができる

JOIN のアルゴリズム

- Nested Loop Join (遅い)
  - テーブル1とテーブル2に対してすべての組み合わせを試す
  - テーブル2にindexがあれば早くなる
- Hash Join
  - テーブル2に対して、1度フルスキャンしてHashMapを作る
  - テーブル2の全レコードをメモリにのせる必要がある
- Merge Join (早い)
  - ソート済のテーブル1とテーブル2に対して一度だけフルスキャン
  - JOINに使うカラムにはindexを貼る

データ集約

- Group Aggregate
  - 入力されたデータをグループキーでソート後、各グループを順に処理
- Hash Aggregate
  - グループキーをkeyとする、一時的なHash Tableを作成する

sortもindexをはるべき

予めindexを貼っておくとソートされた状態でデータが保持されている
ORDER BYでsortを実施するときにはindexされたカラムを使おう

その他 PostgreSQLの特徴的機能

- JSON型
  - json型とjsonb型がある
  - jsonb型を基本で使うべきだが、すごいきたないデータの場合json型になる
- Hstore型
  - key, valueのペアを一つのカラムに保存可能
- Materialized View
  - キャッシュされたView。高速化は期待できるが手動でRefreshする必要がある
- Stored Procedure (PL/pgSQL)
  - PostgreSQLで実行可能なfunctionを定義可能。

その他勉強になったこと

データの分布 = 「統計情報」が大事
SerializeよりはJSONのほうがいいかも
PostgreSQL - indexの利用状況を閲覧できる - pg_stat_user_indexes
Gem grosser/bitfields 便利そう

まとめ

- SQLの実行児に選ばれる実行計画は、indexの有無や統計情報(データの量・分布)に依存
- 適切なschema, index, queryの選択によって、高速化しよう
- WHERE, JOIN,ORDER BY, GROUP BYのkeyにはindex
- JOINの前に絞り込めるだけ絞り込む
- JSON Typeなどもケースバイケースで