study-infra-0708

データベースの仕組み

Transcript

1. データベースの仕組み #インフラ勉強会 2018/07/08 @tenn_25
2. 社内SEとして運用やってます データベースが好きです ※どらくえ派です 自己紹介 2
3. 目的 いま「分からん」「怖い」「触りたくもない」
4. 目的 １時間後「データベースはいいぞ(^p^)」 ・どうやってSQLは実行されるの？ →わかる ・どうやってデータ操作を正しく行ってるの？ →わかる ・なぜ高速にデータが検索できるの？ →わかる
5. アジェンダ ・データベース基本の「き」 ・データベースの構成要素 ・クエリを実行するまで - クエリの解析 - クエリの書き換え - 実行プランの作成 ・データ処理の仕組み - トランザクション - ロック - キャッシュ - ログ ・まとめ
6. データベース基本の「き」 Q. データベースって何？ A.「複数で共有、利用すること」と「検索、加工すること」を目的に整理されたデータの集 まり 出典：https://www.sejuku.net/blog/8763#MySQL Q.どうやってデータを検索したり更新したりできるの？ A. SQLと呼ばれる言語をデータベースに対して実行する。 検索(SELECT),挿入(INSERT),更新(UPDATE),削除(DELETE)などが行える。 SELECT id,name FROM user WHERE id = ‘1024’; (例)userテーブルからidが 1024番の人のidと名前を検 索するSQL
7. データベースの構成要素 ※〇〇マネージャという名前の機能が明確にあるわけではないよ
8. データベースの構成要素 赤枠の話をします(認証認可や、OSみたいな機能の部分は省略)
9. クエリを実行するまで まずはクエリを実行する前段階の話から
10. クエリを実行するまで クエリの解析(パース) 目的 ・間違ったSQLを弾く ・コンピュータがSQLを解釈できるようにする やること ・構文が正しいかの確認 ・実行ユーザーの権限の確認 ・テーブルが存在するかの確認 ・内部的に右図のような構文木への変換
11. クエリを実行するまで クエリの書き換え(リライタ) 目的 ・不要な演算を回避して少しでも高速化させる ・より最適な実行プラン(後述)を見つける やること ・一定のルールに従って構文木を変換
12. クエリを実行するまで 実行プランの作成(オプティマイズ) 目的 ・SQLには「どう実行するか」の情報が必要なため ・高速にデータを処理できそうな方法を考えるため やること ・統計情報を元にディスクIOやCPU,メモリの使用量を推定 ・統計情報をもとにSQLをどう実行するかを考える(様々な最適化アルゴリズム)
13. クエリを実行するまで 統計情報とは ・テーブルごとに持っているデータのサマリ情報 ・テーブルの行数、インデックス、密度、ヒストグラムなどなど ・基本的には定期的に自動更新される ・この情報が古いと適切な実行プランが作られない可能性が高くなるので重要
14. クエリを実行するまで インデックス(索引) データ検索を高速化するための方法。本の索引と同じ理屈 インデックスを作成すると、欲しいページだけにアクセスできる （詳しくは過去セッション「ゼロから始めるDBチューニング」へ...） 14
15. クエリを実行するまで 実行プランのキャッシュ 同じSQLが何回も実行されたときに毎回実行プランを考えたくない →実行プランをメモリ上にキャッシュさせることで使いまわす (このへんはプランキャッシュ、プロシージャキャッシュとか表記揺れがすごい)
16. クエリを実行するまで 実行プランのキャッシュ ちょっとした問題 WHERE句の変数だけSQLが大量に実行される 毎回別のSQLとして扱われるので キャッシュが効かない上に 無駄なキャッシュが増えまくる
17. クエリを実行するまで 実行プランのキャッシュ そこでパラメータクエリですよ！ 変数の部分以外は一緒なので使い回せる(SQLインジェクション対策にもなる)
18. クエリを実行するまで 実行プランのキャッシュ さらに問題が...(細かい話なので割愛) 実行プランのキャッシュとパラメータクエリをつかうことによって、 「何もしてないのにある日突然クエリが遅くなった！」 という事象が起こる(パラメータスニッフィング ) (この名前はSQLServer用語っぽい...？)
19. クエリを実行するまで そんなこんなありましたが、 ここまでやって、めでたくSQLが実行されます。
20. データ処理の仕組み ここから先は綺麗な流れがあるわけじゃないので、かいつまんで解説 まずはデータ操作についての 基本理念を解説してから 具体的な仕組みを見てみよう
21. データの処理の仕組み ACID特性　データベース界の法律！ リレーショナルデータベースはこれらの特性を保証している。 ・原子性(Atomicity) ある1つの操作が中途半端に実行されることはない。 ・一貫性(Consistency) 制約に違反せずデータの整合性が担保されている。 ・独立性(Isolation) 2つの処理が同時に起こってもお互い影響しない。 ・耐久性(Durability) ある処理が完了した後にそのデータは消えない。
22. データの処理の仕組み ACID特性(具体例) 処理1(T1)…口座Aから口座Xに100万円振り込む。 処理2(T2)…口座Bから口座Xに50万円振り込む。 ・原子性…T1実行中に障害が起きても、「口座 Aから100万円減ったが口座Bは増えていない」という事態 にならないこと。処理はロールバックされ振込が無かったことになること。 ・一貫性…預金が100万円未満の時には振り込めないこと。「預金額はマイナスにならない」という制約に 違反しないこと。 ・独立性…T1とT2が同時に実行された際、「片方の処理が影響を受けてうまく処理されず、講座 Xに50万 円しか入金されていない」という事態にならないこと。 ・耐久性…T1が正常に完了した後に障害が起きても、お金が無くなったりしないこと。
23. データの処理の仕組み トランザクション 1つのまとまった処理を「トランザクション」と呼ぶ。 例:AさんがBさんに100万円を振り込む 1. トランザクション開始 2. Aさんの口座の預金額を-100万円 3. Bさんの口座の預金額を+100万円 4. トランザクション終了(コミットorロールバック) ※ロールバックした場合は1~4の処理は無かったことになる。
24. データの処理の仕組み トランザクションと独立性 もし、同時に１つのトランザクションしか処理されないのであれば 独立性は保証される。 しかし、実際のデータベースは 複数のトランザクションが「同時に」「同じデータに」アクセスする。 だからアレコレ工夫しなくてはならない。
25. データの処理の仕組み トランザクション分離レベル 独立性(分離性)はいくつかのレベルがある。 理想論では全てのトランザクションは完全分離させることが望ましいが、 その分チェック処理が増えるので処理性能が下がる。 そのため、実際のDBはある程度独立性を下げて不整合を許容している。
26. データの処理の仕組み ロック 複数のトランザクションが「同時に」「同じデータに」アクセスする。 この平行性の制御のために、トランザクション内ではデータをロックする。 共有ロックと占有ロックがある。 (例)トランザクション①②③ の順に平行して動く場合
27. データの処理の仕組み デッドロック トランザクション1はデータAの排他ロックを獲得してデータBを待っている トランザクション2はデータBの排他ロックを獲得してデータAを待っている
28. データの処理の仕組み 2相ロック(Two Phase Lock) デッドロックの発生を下げるための仕組み 1トランザクションはロックを獲得する成長フェーズを先に行う 全てのロックを取れたら処理をして その後全てのロックを解放していく。 全てのトランザクションが このルールに従えばデッドロックは 減らせる
29. データの処理の仕組み キャッシュ(メモリ・バッファ) データの操作は必ずメモリ上で行われるので読み込む必要がある。 ディスクに比べ高速なため、必要なデータをできるだけ効率良く読み込む やっていること ・必要なデータをメモリ上のバッファプールに格納する。 ・不要なデータをメモリ上のバッファプールから削除する
30. データの処理の仕組み 効率的にバッファプールに読み込ませる方法 高速化のため、データを処理するよりも前にバッファに読み込ませておく。 1,3,5と読み込まれたら次は7,9,11だろうと推測する方法も(投機的プリフェッチ) (※この仕組みはDBに限った話ではありません) データ1読み込み → データ1処理 データ2読み込み → データ2処理 データ3読み込み → データ3処理 時間
31. データの処理の仕組み どのデータをバッファから削除するか 一番有名なのはLRUアルゴリズム(Least Recently Used) 各キャッシュのページ(DBデータを扱う最小単位)はカウントを持っており、 時間経過によって減っていく。閾値を下回ったらバッファから削除。 キャッシュが利用されるとカウント数が上がる。 ※さっき出た実行プランのキャッシュも同じ方法で削除されます。
32. データの処理の仕組み ログ 処理するたびに実データを書き換えてたら、元に戻せない！ トランザクションのロールバックには必要不可欠。 トランザクションの全ての操作はログファイルとして書き込まれる。 処理途中に障害が起きた場合にログによってデータを元に戻せる。
33. データの処理の仕組み ログ先行書き込み WAL (Write Ahead Log) 目的 原子性と永続性を担保する。 実データより先に必ずログを書き込むことで クライアントへの返答を早くする。 5.ログをディスクに書き込む(コミット) 6.実データをディスクに書き込む(チェックポイント)
34. データの処理の仕組み ログ先行書き込み WAL (Write Ahead Log) Force/No Force コミット前にデータをディスクに書き込む処理を行うのがForce コミット後にデータをディスクに書き込むのがNo Force Steal/No Steal トランザクション内で随時ディスクに書き込めるのがSteal それを許容せず、データをまとめて更新するのがNo Steal Steal/NoForceがもっともパフォーマンスが高い分、管理は複雑 障害時はトランザクションログが必須になる。
35. まとめ ・データベースは様々な仕組みが組み合わさった叡智の結晶 ・データベースはデータがおかしくならないような仕組みを兼ね備えている ・「データのACID性」と「処理速度」はトレードオフ！意図的に一貫性の一部を許容したり 両立させるための仕組みを備えている ・データベースの仕組みを知るとめっちゃ深い！面白い！