SQLアンチパターン読書会 「インデックスショットガン」に参加しました

2013/11/20(水) SQLアンチパターン読書会 「インデックスショットガン」に参加してきました。

DoorKeeper
http://sqlap.doorkeeper.jp/events/7054

以下の書籍をターゲットとした読書会なのです。

SQLアンチパターン (2013/01/26) Bill Karwin 商品詳細を見る

場所はいつもの湯島、株式会社アルティネットさんです。
いつも会場提供ありがとうございます。
参加者は8人かな。毎度の顔馴染みメンバーです。

今回は12章「インデックスショットガン」がターゲットでした。
DB性能と言えば避けられない話題で、大変楽しみでした。

---

■アジェンダ
今回は @tonyuchi さんがスライドを作成し説明してくださいました。感謝！

SQLアンチパターン（インデックスショットガン） from Tomoaki Uchida

書籍の本題に入る前にインデックスの仕組みについての説明があったり、実際にOracleを使って実験し考察までしていたりと、素晴らしいスライドです。

---

■ディスカッション
今回もディスカッションしたいネタをみんなで付箋に書き出しました。



以下、個人メモ。
---
■12.2 インデックスを多く定義し過ぎる
・ほとんどのデータベースは、主キーのインデックスを自動的に作成するので、明示的に定義するのは冗長。
・Oracleは主キーにインデックスを付与しようとするとエラーになるらしい。
・外部キーに自動でインデックスが付与されるか否かは、データベース製品によって異なる。


■12.2.3 インデックスが役立たないとき
・LIKE検索で、中間一致だけでなく後方一致もインデックスが効かない。


■12.5 解決策：「MENTOR」の原則に基づいて効果的なインデックス管理を行う
・「MENTOR」という単語はこの著者のみが使っている独自用語。
・P.130の2行目に「君には、ちょっとしたメンタリングが必要なだけなんだ」とあるが、実はこの文章は「メンタリング」に「MENTOR」を掛けた布石だった！


■オプティマイザ
・オプティマイザには大きく2種類ある。
　①ルールベースオプティマイザ
　②コストベースオプティマイザ
・昔はルールベースオプティマイザが主流で、WHERE句の上に書いたものから順にインデックスが使われた。
・今はコストベースオプティマイザが主流で、WHERE句のインデックスの使い方は書いた順序によらない。
・ただコストベースオプティマイザでも、SQLの可読性の関係で上から書いた方が良い。


■バキューム処理
・PostgreSQLは「追記型アーキテクチャ」なので、掃除しないと遅くなる。
・昔のPostgreSQLは、掃除を自動でするオートバキューム(autovacuum)の機能が無かった。
・(参考) 追記型アーキテクチャ：
　ある行にUPDATE文を実行すると、既存の行の領域には「このデータはこの時点で書き換えられた」ということだけを書き換え、更新後内容の行を新たに追記して書き込む方式のことらしい。
　参考： 第1回 PostgreSQLの概要とアーキテクチャ http://thinkit.co.jp/article/1038/1/page/0/2
・ちなみにMySQLは上書きアーキテクチャ。


■インデックスを付けるタイミング
・インデックスはSQLの結果に影響を与えない。
　→　まず、わかるやつは先にインデックスを付けておく。わからないのは後で付ける。
・まず正規化をしましょう。
・性能問題は、一番遅いボトルネックを対策しないと、他をチューニングしても意味がない。
・MENTORは測定(Measure)と解析(Explain)が先に来ているのが良い点。


■Explain(解析)
・MySQLのEXPLAIN結果は複雑で、初見では読めない。。。
・ベンダの商用データベースのEXPLAINはGUIが充実していて見やすい。
・IBMのDB2は、EXPLAINをツリーで表示させてくれる。
・オープンソースと商用データベースのEXPLAINは、だいぶ差が付いている。
　→　SQLは差別化しにくいので、ここが差別化の要因となってたりする。


■性能チューニング
・ヒント句を使うのはバッチアプリケーションで多い。Webアプリではあんまりない。
・ORMはヒント句が使えないので嫌、という意見もある。
・iBATISとかS2Daoとか、生SQLも書けるORMの方が歓迎される現場もある。
　→　SQLを外出しにできるので、手でチューニングしやすいので差別化できる。
・RDBMSだけが選択肢ではない。
　→　メインはRDBで、サブはDynamo DBを選択したりとか。
　→　あいまい検索とかは全文検索エンジンを使用したりとか。
　→　KeyValue型とか、いろいろ選択肢はある。


■インデックスの数
・１つのテーブルに100以上のインデックスが貼られているのを見たことがある。
　→　データマイニングのアプリで、多面的な分析を考慮してインデックスを貼っていた。
・複合プライマリキーを使うとインデックスが増えがち。


■苦戦した翻訳ｗ
・12章の冒頭に出てくるオクラホマ訛りの翻訳がすごく大変だった。。。
・翻訳者の児島さんに相談して、翻訳を止めた　by @t_wada さん


■ORDER BY 狙いのキーの話
・@kamipo さんのTogetterが参考になる。
　①ORDER BY 狙いのキーの話：　http://togetter.com/li/564015
　②ORDER BY 狙いのキーの話2：　http://togetter.com/li/579823
・Webアプリでは、LimitとOrder by狙いの方を、絞込よりも優先したほうが良い。
・並び順と取得件数を考慮しましょう。
・このスライドも面白くて参考になる。 by @grimrose さん
　とあるイルカのバーボンハウス　( http://www.slideshare.net/yoku0825/ss-27597161 )


■インデックスに対するコメント
・インデックスにもコメントを付けて欲しい。インデックスの設計書とかに。
・SIerとかではインデックス設計書を良く見かける。
　でも、マトリクスに○付けるあのフォーマットは、複合インデックスを作りたくなるので良くないかもｗ


■インデックスの種類
以下のような種類がある。インデックスはSQL標準ではなく、各RDBMSに依存する。
・B-Treeインデックス
・ビットマップインデックス
・降順インデックス
・ハッシュインデックス


■選択性（Selectivity）とカーディナリティ
・選択性は「カーディナリティ」って言葉で使われることもあるが、これは3つの意味があるので紛らわしい。
・「カーディナリティて何ですの」というブログが参考になる。
　http://d.hatena.ne.jp/tgk/20070517/1179414774
　①relationのカーディナリティ
　②relationshipのカーディナリティ
　③キーのカーディナリティ
・テーブル同士の関係は「relationship」で、タプルの数が「relation」である。
・リレーションについてはSQLアンチパターンの付録Aにも書いてある。


■インデックスに関する普遍の事実
・まず、きちんと計測しましょう。
・一番遅い箇所から対策しないと意味がない

---

■KPT（ふりかえり）
今回で第Ⅱ部まで終了し、キリが良いということでKPT（ふりかえり）をやりました。



遠くから撮ったのでピンボケしてます。。。
主催の @natsu_nanana さんが文字に起こしてFacebookのグループに投稿してくださいました。感謝！

---

★感想：
DB性能と言えばインデックス！というくらいに、性能チューニングでインデックスに悩まされた経験がある人は多いのでは。。。
ということで、この章は大変興味がありました。
MENTOR、という考え方は、DB性能を考える上でキーワードとして使えるのでは、と思いました。

あと個人的には、B-Treeインデックスとかビットマップインデックスとかが、どういう仕組みで実現されているのか、中身の構造をちょと勉強したいと思いました。
結局、いつも推測の範囲でやっちゃってたところもあるので、きちんと理解した上で判断することが重要だなぁと思うことが度々あったので。

参加者の皆様、ありがとうございました～

---

★オマケ
とりあえず第Ⅱ部の12章まで終わったので、いったん自分のブログエントリを纏めてみる。
---

1章：SQLアンチパターン読書会 「ジェイウォーク」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-65.html

2章：SQLアンチパターン読書会 「ナイーブツリー」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-70.html

3章：SQLアンチパターン読書会 「IDリクワイアド」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-73.html

3章：SQLアンチパターン読書会 「続・IDリクワイアド」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-77.html

4章；SQLアンチパターン読書会 「キーレスエントリー」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-84.html

5章：SQLアンチパターン読書会 「EAV(エンティティ・アトリビュート・バリュー)」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-90.html

6章：SQLアンチパターン読書会 「ポリモーフィック関連」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-94.html

7章：SQLアンチパターン読書会 「マルチカラムアトリビュート」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-97.html

8章：SQLアンチパターン読書会 「メタデータトリブル」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-105.html

9章：SQLアンチパターン読書会 「ラウンディングエラー」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-109.html

10章：SQLアンチパターン読書会 「サーティーワンフレーバー」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-115.html

11章：SQLアンチパターン読書会 「ファントムファイル」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-118.html

12章：SQLアンチパターン読書会 「インデックスショットガン」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-121.html
---

一応、毎回書いている！ｗ