ゼロから始めるDBチューニング
スピーカー
＠tenn_25 さん
スライド
Googleスライド https://docs.google.com/presentation/d/1YyeNLabcYvgtWLgqqHhZC1mTPEHbMLl35HsE3RBcytQ/edit#slide=id.p
#DBチューニング ##今回のゴール 達成目標
- チューニングの観点を知る
- データベースがどう動いているかを理解する
- インデックス、統計情報、実行プランを理解する
#本回の注意事項
- RDBMS(relational database management system)に限る。製品によっても細かいところが違います
- Microsoft SQLServerを例とする
#目次
- チューニングのアプローチ
- データベースの構成要素
- データベースのデータ構造
- インデックスのデータ構造
- クラスターインデックス
- 非クラスターインデックス
- 統計情報
- デモ
#チューニングのアプローチ 語録：DB 上から見るか？下から見るか？
- SQLの見直し
- ループ処理、サブクエリ、JOINなどの見直し
- 論理設計の見直し
- テーブル設計見直し、非正規化など
パフォーマンスだけならば、実は非正規化の方がよいことがある。（データ関係が複雑になっていないので） - DB内部の設定の見直し
- DB内の細かい設定変更、インデックス ← 今回の話はこれ
- 物理設計の見直し
- 負荷分散、並列処理、RAIDやファイル構成の見直し、スケールアップ/アウト(札束で殴ｒ
#効果が大きいところを見つけて対処していこう
- ボトルネック以外をチューニングしても意味ない
- ボトルネックの8割はディスクI/O(※個人調べ)
- いかにディスクI/Oを減らすかが鍵！
- 表面的な症状を見てるだけでは根本原因は分からない。
リソース負荷や症状はあくまで「予想の裏付け」程度の参考にする。
zabbixで監視して結果出ても、予想の裏付けぐらいだけ・・・となるので、過信しない。
そのため、チューニングをしっかり行うならば、
★データベースの内部の情報を調べる必要がある。
★データベースの仕組みを知っている必要がある。
-
データベースの内部情報を調べる
-
トレース(SQLServer Profiler)、パフォーマンスモニタ、動的管理ビュー(DMV)、クエリストア
-
とりあえずスロークエリ(時間のかかるSQL)を調べよう！
-
データベースの仕組みを知る
-
どうやってクエリが実行されるか
- どうやってデータを検索しているか
データベースの構成要素
（ここからDB構成要素概念図を使用。資料見ましょう）
データベースの要素は大きく分けて2つ + 2つの物理
- リレーショナルエンジン(クエリプロセッサ)
- データベースエンジン(ストレージエンジン)
- ストレージ
- メモリ
リレーショナルエンジン
クエリ(ソフトウェアに対するデータの問い合わせや要求)が投げられると、クエリのSQLをコンパイルする。
コンパイルされたものを実行するために、統計情報(後述)を元に、どのようにデータを探すか計画を立てる。
過去に同じクエリを実行した履歴が残ってればそれを流用する。
- プランキャッシュ
その名の通り、計画の履歴のこと。
*「クエリをどうやって情報取得してこようかな」という実行プランを立てるために、「キャッシュに残っていないかな？」*と探す。
データベースエンジン
実行プランに従ってクエリを実行する。
トランザクションやロックの制御、メモリ管理をしながらデータにアクセスする。
デファクトスタンダードはInnoDB。
ストレージ
データ操作のログはログファイルに格納されている。
実データや、その他諸々はデータファイルに格納されている。
メモリ
ストレージのデータを操作するには、 必ずメモリに必要なデータを持ってくる必要がある。
データベースのデータ構造
データを扱う最小単位をページと呼ぶ。
SQL Serverだと8KBのブロックに分かれている。
実データは行単位でページに格納される。
データベースのデータ構造
ヒープ…実データそのもののこと。ストレージ上でバラバラ。綺麗に並んでない。
インデックス…検索を高速化させるためにテーブルに付与する情報。索引。
インデックスのデータ構造
主にB-Treeという木構造から成るインデックスが採用されている
- ルートノード：インデックスの始点
- 中間ノード：次の階層への複数階層存在する
- リーフノード：目当てのヒープページの場所が書いてある
- B+Tree：リーフノードが次のリーフへのポインタを持つ
非クラスター化インデックス
(SQL server用語。他のRDBMSにない…かも？）
（ここからインデックスの図、各ノードの表を使用。資料見ましょう）
例えば以下のSQLを実行
SELECT * FROME UserTable WHERE UserID = '1202'
⇒実行されれば、まずルートノードから検索がいく。
ルートノードから中間ノードへ行く。
さらに、中間ノードからリーフノードに行く。
しかし、リーフノードには実際のデータへの参照先が含まれているだけ。
最終的に、実際のデータへ飛ぶ。
索引から目当てのページ(ヒープ)に飛ぶ必要がある(RID Lookup)
★技術書などの索引と同じ！
「何ページにあるか」までは書いてあるが、それが文書の中でたまたま使われた言葉なのか、
その言葉の説明がきっちり書いてあるのかまでは、そのページを見に行かないとわからない。
⇒実際のページ(ヒープ)に探しに行くのが多くなると、
索引を追うので大変になるので、遅くなる。
クラスター化インデックス
メインのインデックス。
テーブルの主キーにつけられる。
例えば以下のSQLを実行
SELECT * FROME UserTable WHERE UserID = '1202'
⇒実行されれば、まずルートノードから検索がいくのは非クラスター化インデックスと同じ。
ルートノードから中間ノードに行く。ここも同じ。
中間ノードからリーフノードに行く。
リーフノードには実際のデータが格納されており、インデックスのキーでソートされている。
五十音順に辿って行くと、目当ての言葉に直接辿りつく！
★辞書と同じ
データがソートされている。
RID Lookupが発生しないクラスター化インデックスの方が速い。
語録？：クラスター化インデックスの方が偉い！
(参考)MySQLのセカンダリインデックス
クラスター化インデックスはSQLServerもMySQLも同じ。
しかし、検索が、クラスター化インデックスのルートノードからいかない所が違う。
以下のSQL例
SELECT * FROME UserTable WHERE Name = 'Mary'
先に、セカンダリインデックスのルートノードから検索が走る。
セカンダリインデックスのリーフノードには主キーの値が格納されている。
主キーの値を使って、クラスター化インデックスを検索していく。
統計情報
統計情報は実行プラン(クエリプラン)を決めるために使われる
－サンプルが多すぎても良くない(時間がかかる)
DBへの負荷もかかる。
－サンプルが少なすぎても良くない(信憑性に欠ける)
－統計情報自体が古くなると、それもまた良くない(信憑性に欠ける)
－インデックス構築時や、ある程度データが更新された時に再作成される
デモ
＠tenn_25さんの画面共有で、SQL Server Management Studioを実践
あるテーブルに対して、以下のように条件を変えて、SELECT文を実施。
- インデックスを何もつけない
- 主キーにクラスター化インデックスをつける
- インデックスを意図的に無効化
- 主キーとは別のものに、クラスター化インデックスを張る...？
- (上記のクラスター化インデックスの定義に反してしまっているので、別の言葉か？）
終了後のいろんな質問タイム！
インデックスを頑張って張りまくるとどうなるの？
インデックスを付けると更新箇所が増えるので、更新処理[INSERT,UPDATE,DELETE]は遅くなる。張りすぎはトレードオフ。
DB製品での違い
[SQLServer/MySQL]クラスター化インデックス＝[Oracle]索引構成表
⇒リーフノードに実データがソートされた状態で格納されている。
[SQLServer]非クラスターインデックス＝[Oracle]インデックス？？ （不明確）
⇒リーフノードヒープ(実データ)への参照が格納されている。
[MySQL]セカンダリインデックス
⇒リーフページに主キーが格納されており、それを使ってクラスター化インデックスを検索する。
索引構成表というのがクラスター化インデックスと同じっぽい。
ScanとSeekの違いって？
スキャンとシークを比較するとシークが早い。
- スキャンは対象のデータを0から100までチェックする処理
- シークはインデックスのツリーを辿っていき、欲しいデータにだけアクセスする処理
Index Scanはインデックスのリーフノードを順に調べて、目的のデータを探している
Index Seekは、Bツリーインデックスをルートから順にたどって、最短の手順でデータを探し出している
クエリ？がどこまで同じなら、クエリキャッシュにHITする？
一言一句同じ＆データが更新されないことが条件
データが更新されたらクエリキャッシュは破棄される