ゼロから始める DBチューニング(再演)
ゼロから始めるDBチューニング(再演+α)#インフラ勉強会2018/4/29@tenn_25
View Slide
@tenn_25・BtoC向けWebサービスの運用担当・2月に勉強会したけどネットが貧弱だった・最近Jc●mと契約してネットは改善された・どらくえ派自己紹介2
今回のゴール・DBチューニングの観点を知る・DBがどう動いているかを理解する・インデックス、統計情報、実行プランを理解する注意事項 ※今回はリレーショナルデータベースの話なのでNoSQLは違います ※各DB製品によっても細かいところが違います3
アジェンダ1.チューニングのアプローチ2.データベースの仕組み3.データベースのデータ構造4.インデックスの構造5.その他の重要事項6.デモ4
1.チューニングのアプローチ(What)チューニングとは問題解決。根本原因を見つけることが大事※ただし、調査が泥沼になることもあるので深追いは注意。簡単にできて効果が大きい対応をするだけでも良い。5
DBチューニングの場合は、ボトルネックを見つけてそこを対処しようボトルネックの8割はディスクI/O(※個人調べ)まずは「いかにディスクI/Oを減らすか」の視点で!1.チューニングのアプローチ(Where)6
1.チューニングのアプローチ(原因分析のHow)・データベースの症状、範囲を確認する(見える事象)- リソース使用量、クエリの実行時間、どんな変更をしたか、いつからか- 影響範囲(特定の処理だけ?特定のクエリだけ?特定のテーブルだけ?)・データベースの内部情報を調べる(見えない事象)- 各DB製品ごとにツールや方法が違う。- SQLServerの場合…トレース(Profiler)、動的管理ビュー、パフォーマンスモニタ、クエリストア7
1.チューニングのアプローチ(対応のHow)・SQLの見直し - ループ処理、サブクエリ、 JOINなどの見直し ・論理設計の見直し - テーブル設計見直し、非正規化など ・DB内部の設定の見直し -DB内の細かい設定変更、インデックス ←今日の話はこれ・物理設計の見直し -負荷分散、並列処理、 RAIDやファイル構成の見直し、スケールアップ /アウト(札束で殴r8
1.チューニングのアプローチ(まとめ)表面的な症状を見てるだけでは根本原因は分からない。リソース負荷や症状はあくまで「予想の裏付け」程度の参考にする。データベースの内部の情報を調べる必要がある(DB毎に違う)データベースの仕組みを知っている必要がある(今日はこれ)9
2.データベースの仕組み10欲しいデータがメモリに乗ってたら速い。乗ってなかったら仕方なくデータ探しの旅へ…
2.データベースの仕組み11参考 (絵で見て分かるSQLServer内部構造より)
3.データベースのデータ構造データを扱う最小単位を「ページ」と呼ぶ。1ページ = 数KBのブロックで、行ごとにページに格納されている。同じテーブルのページでも並んでたりバラバラだったりする。12
3.データベースのデータ構造テーブルから条件を指定してSELECTする場合…userテーブルのデータ全部を全行取ってきてname列が’tenn’のレコードをクライアントに返す。⇒テーブルスキャンが発生!13
4.インデックスの構造そこでインデックス(索引)ですよ!!!主にB-Treeという木構造から成るインデックスが採用されている・ルートノード…インデックスの始点・中間ノード…次の階層への複数階層存在する・リーフノード…目当てのヒープページ(実際のデータページ)の場所が書いてある14
4.インデックスの構造テーブルにインデックス(索引)を付けると…インデックスを辿って欲しいページだけにアクセスできるインデックス付与することで簡単にディスクIOが減らせる!15
4.インデックスの構造ヒープ…実データそのもののこと。ストレージ上でバラバラ。並んでない。インデックス…検索を高速化させるためにテーブルに付与する情報。索引。16
4.インデックスの構造 (MSSQL)非クラスタ化インデックス17リーフノードには実際のデータへの参照が含まれているだけ索引から目当てのページ(ヒープ)に飛ぶ必要がある(RID Lookup)★技術書などの索引と同じ!
リーフノードには実際のデータが格納されており、インデックスのキーでソートされている。五十音順に辿って行くと、 目当ての言葉に直接辿りつく!★辞書と同じ4.インデックスの構造 (MSSQL)クラスタ化インデックス18
クラスター化インデックスはSQLServerもMySQLも同じセカンダリインデックスのリーフノードには主キーの値が格納されている。4.インデックスの構造 (MySQL)セカンダリインデックス19
どうやってそのSQLを実行するか(データ検索手順書みたいなもの)複雑なクエリになった時に実行プランは色々考えられる実行プランは統計情報を基に決める5.その他の重要事項 実行プラン(クエリプラン)20
5.その他の重要事項 統計情報統計情報は実行プラン(クエリプラン)を決めるために使われる-サンプルが多すぎても良くない(時間がかかる)-サンプルが少なすぎても良くない(信憑性に欠ける)-古くなるとそれもまた良くない(信憑性に欠ける)-インデックス構築時や、ある程度データが更新された時に再作成される21
6.デモSQLServerでデモします(画面共有)あるテーブルにインデックスを付けてみてIOコストが減ることを確認する・テーブルスキャン…ヒープを全件検索(遅い)・インデックススキャン…インデックスのリーフノードを全件検索(遅い)・インデックスシーク…インデックスを辿って最短経路で検索(速い)22