MySQLストレージエンジンの仕組み(アーキテクチャ)をざっくり理解する

有名なリレーショナルデータベース(RDB)の一つであるMySQLのストレージエンジンの仕組みをざっくり理解して、RDBのパフォーマンスチューニングに活かす

Transcript

1. MySQLストレージエンジン の仕組みをざっくり理解する revenue-hack
2. 自己紹介 • 現在フリーランス • バックエンドとインフラをメインに活動 • 技術顧問とかしてる • Goがメイン(後はPHPやTSとか) • コードのアーキテクチャ設計やAWSが好き • フロント(React, Vue)も一応やっていた • 最近正社員になるか悩み中。。。 • X(@tagaranoko1014)
3. zennで書いた記事の内容を紹介 https://zenn.dev/revenuehack/articles/dc5a901fbe90e6
4. アジェンダ • SQLの処理フローについて • ストレージエンジンの説明 • SELECT文実行時の場合 • DML(INSERT, UPDATE, DELETE)実行時の場合 • まとめ
5. 1. SQLが来て、パースする
6. 1. SQLが来て、パースする 2. オプティマイザが実行計画作る
7. 1. SQLが来て、パースする 2. オプティマイザが実行計画作る 3. エグゼキュータが実行 4. ストレージエンジンからデータ取得
8. 1. SQLが来て、パースする 2. オプティマイザが実行計画作る 3. エグゼキュータが実行 4. ストレージエンジンからデータ取得 ストレージエンジンの話！
9. ストレージエンジンのアーキテクチャ
10. Buffer pool データのキャッシュが置かれる領域。 基本的にここを通してデータが返され る。多くの場合インメモリの 80%程度 がここの領域に当てられる。 インメモリ Log Buffer ディスク上のログファイルに書き込ま れるデータを一時的に保持する領 域。redoログデータをディスクに書き 込むと、遅くなってしまうため、 Log Bufferに書き込み、速くトランザクショ ンを実行出来るようにするためにあ る。 インメモリ Redo Log INSERT時などのCOMMITタイミング でデータが書き込まれる。クラッシュ リカバリー時に対応出来るように COMMIT時にこの不揮発性ストレー ジに書き込まれる ストレージ Tablespaces テーブルやインデックスが置かれる 部分 ストレージ
11. SELECT文のフロー SELECT * FROM `users`; ※これを2回実行した場合の例
12. 1回目SELECT
13. 2回目目以降SELECT
14. DML文(INSERT,UPDATE,DELETE)のフロー BEGIN; INSERT INTO `users` VALUES (1, ‘hoge’); INSERT INTO `users` VALUES (2, ‘fuga’); COMMIT;
15. BEGIN; INSERT INTO `users` VALUES (1, ‘hoge’);
16. INSERT INTO `users` VALUES (2, ‘fuga’);
17. COMMIT;
18. チェックポイントのタイミングwrite！ Tablespacesへはいつ書き込まれる？
19. チェックポイントっていつ？ • ReadLogのサイズがあふれる時 • あまりMySQLが稼働してない時(CPU使用率が低い) • 適当な時間間隔 • …
20. まとめ • バッファプールキャッシュのサイズは重要 • ストレージエンジンの仕組みを理解しておくと、パフォーマン スチューニングに役に立つ