エンジニアの副業の始め方・稼ぎ方を学ぶ

データベースの仕組み(アーキテクチャ)をざっくり理解する

 

フリーランスエンジニアをしているrevenue-hackです！

 

普段はGo言語でバックエンドを中心にやっています〜

 

今回はデータベースの特にRDBの仕組み(アーキテクチャ)についてざっくり理解して、なにかに役立てようぜ〜

 

というような内容になります。

 

 

記事執筆者：オザック

Web開発(バックエンド兼インフラ)を生業にしている、エンジニア歴9年以上のrevenue-hackです！

某有名R社で働き、副業も含めて個人事業主で関わってきたプロジェクトは20以上。

DDDやクリーンアーキテクチャ、AWS、RDBの設計、チューニングなどを教えたり、実装していたりします！

現在はフリーランスとして従事。

 

バックエンド、インフラに関する業務での相談事や個人開発の相談事など受けてます！

MENTAでバックエンドやAWSインフラ、RDBの設計、チューニングをサポート！

 

普段ゆる〜くアーキテクチャやデータベースのことなどを発信しています〜

Twitter

 

筆者関連記事: バックエンドのやばい設計記事

バックエンドの設計で直したほうが良いコード9選

 

データベースの仕組み(アーキテクチャ)の記事の対象者

想定読者

  • バックエンドエンジニアでなんとなく仕組み知っておきたい人
  • データベースの仕組みを知ってインデックス貼ったりとかの勉強に役立てたい人
  • 仕組みをざっくり知ってなんとなくデータベース語りたい人

向けです！

 

データベース(RDB)の仕組み(アーキテクチャ)は大体同じ

RDBといえば

  • Oracle
  • SQLServer
  • MySQL
  • Postgres

 

あたりが有名かと思います。

 

ポイント

ただRDBの細かい仕様や設計、命名は違いますが、基本的に全RDBは同じようなアーキテクチャになっています！

 

なので1つのRDBの仕組みを追えばおおよそRDBのアーキテクチャは理解できるようになっています。

 

オザック
今回はMySQLを題材にアーキテクチャを説明していきます!

 

SQLの処理の流れ

まずはアーキテクチャの前にSQLの流れをざっくりおさらい。

 

SQLの処理の流れ

SQLの処理の流れ

簡単な流れ

  • まずクライアントからSQLが投げられると、パーサーでSQLを解析してAST(抽象構文木)を生成する
  • 生成した抽象構文木からオプティマイザが、事前に収集してあったデータを元に、複数通りの実行計画を作り、コストを定量化する
  • その中からコストが一番低い実行計画を実行し、エグゼキュータがストレージエンジンにアクセスしてSQLの結果を出す

と言う流れになっています。

 

解析木の例

RDBの解析木
解析木の例

 

解析木のイメージはこんな感じ。

 

オプティマイザトレースの例

MySQLではjsonでSQLのオプティマイザトレースの結果が出せるようになっています。

実際それを出してみるとこんな感じになってます。

メモ

5.7以降だとちゃんとコストも表示されるので、SQLを調査したい人には良いです！

 

EXPLAIN FORMAT=JSON　SELECT ...

explain時に`FORMAT=JSON`をつけるとOKです！

  1. {
  2.   "query_block": {
  3.     "select_id": 1,
  4.     "cost_info": {
  5.       "query_cost": "404995.39"
  6.     },
  7.     "nested_loop": [
  8.       {
  9.         "table": {
  10.           "table_name": "l",
  11.           "access_type": "ALL",
  12.           "rows_examined_per_scan": 331143,
  13.           "rows_produced_per_join": 331143,
  14.           "filtered": "100.00",
  15.           "cost_info": {
  16.             "read_cost": "4141.79",
  17.             "eval_cost": "33114.30",
  18.             "prefix_cost": "37256.09",
  19.             "data_read_per_join": "5M"
  20.           },
  21.           "used_columns": [
  22.             "emp_no",
  23.             "from_date",
  24.             "to_date"
  25.           ],
  26.           "materialized_from_subquery": {
  27.             "using_temporary_table": true,
  28.             "dependent": false,
  29.             "cacheable": true,
  30.             "query_block": {
  31.               "select_id": 3,
  32.               "cost_info": {
  33.                 "query_cost": "33851.30"
  34.               },
  35.               "grouping_operation": {
  36.                 "using_filesort": false,
  37.                 "table": {
  38.                   "table_name": "dept_emp",
  39.                   "access_type": "index",
  40.                   "possible_keys": [
  41.                     "PRIMARY",
  42.                     "dept_no"
  43.                   ],
  44.                   "key": "PRIMARY",
  45.                   "used_key_parts": [
  46.                     "emp_no",
  47.                     "dept_no"
  48.                   ],
  49.                   "key_length": "20",
  50.                   "rows_examined_per_scan": 331143,
  51.                   "rows_produced_per_join": 331143,
  52.                   "filtered": "100.00",
  53.                   "cost_info": {
  54.                     "read_cost": "737.00",
  55.                     "eval_cost": "33114.30",
  56.                     "prefix_cost": "33851.30",
  57.                     "data_read_per_join": "10M"
  58.                   },
  59.                   "used_columns": [
  60.                     "emp_no",
  61.                     "dept_no",
  62.                     "from_date",
  63.                     "to_date"
  64.                   ]
  65.                 }
  66.               }
  67.             }
  68.           }
  69.         }
  70.       },
  71.       {
  72.         "table": {
  73.           "table_name": "d",
  74.           "access_type": "ref",
  75.           "possible_keys": [
  76.             "PRIMARY"
  77.           ],
  78.           "key": "PRIMARY",
  79.           "used_key_parts": [
  80.             "emp_no"
  81.           ],
  82.           "key_length": "4",
  83.           "ref": [
  84.             "employees.l.emp_no"
  85.           ],
  86.           "rows_examined_per_scan": 1,
  87.           "rows_produced_per_join": 16557,
  88.           "filtered": "4.52",
  89.           "cost_info": {
  90.             "read_cost": "331143.00",
  91.             "eval_cost": "1655.72",
  92.             "prefix_cost": "404995.39",
  93.             "data_read_per_join": "517K"
  94.           },
  95.           "used_columns": [
  96.             "emp_no",
  97.             "from_date",
  98.             "to_date"
  99.           ],
  100.           "attached_condition": "((`employees`.`d`.`to_date` = `employees`.`l`.`to_date`) and (`employees`.`d`.`from_date` = `employees`.`l`.`from_date`))"
  101.         }
  102.       }
  103.     ]
  104.   }
  105. } |

 

選ばれた実行計画をexplainでみるとこんな感じ

+----+-------------+------------+------------+-------+-----------------+---------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------+---------+---------+--------------------+--------+----------+-------------+
| 1 | PRIMARY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | NULL |
| 1 | PRIMARY | d | NULL | ref | PRIMARY | PRIMARY | 4 | employees.l.emp_no | 1 | 4.52 | Using where |
| 3 | DERIVED | dept_emp | NULL | index | PRIMARY,dept_no | PRIMARY | 20 | NULL | 331143 | 100.00 | NULL |
+----+-------------+------------+------------+-------+-----------------+---------+---------+--------------------+--------+----------+-------------+

 

データベース(RDB)の仕組み(アーキテクチャ)について

なんとなーくSQLの処理の仕組みと流れがわかった所で、次は本題のデータベースのアーキテクチャについて説明していきます！

 

注意ポイント

※先に言っておくと、あくまでオーソドックスな流れを紹介するので、今回とは違うエッジケースの処理フローをたどることもあります！

 

RDBのアーキテクチャ(仕組み)

データベースのアーキテクチャ(仕組み)

 

名称 説明 領域
---- ---- ----
Buffer pool データのキャッシュが置かれる領域。基本的にこの領域にテーブルやインデックスデータが置かれて、ここを通してデータが返される。一般的にメモリの50〜80%程度をこの領域に使う インメモリ
Log Buffer ディスク上のログファイルに書き込まれるデータを一時的に保持する領域。redoログにデータをフラッシュする前に、トランザクションがコミットされるまでは個々の領域で保持する インメモリ
Redo Log INSERT時などのCOMMITタイミングでデータが書き込まれる領域。クラッシュリカバリー時に対応出来るように不揮発性ストレージに書くようになっている ストレージ
Tableｓspaces テーブルやインデックスのデータが置かれている領域 ストレージ
Undo Log 図には書いてないてないが、トランザクションのロールバックなどの最新の変更を元に戻すときのデータが格納される領域 ストレージ

 

データベースのストレージエンジンの処理の流れとしてはこのような図になっていて、名前はRDBによって違ったりしますが、アーキテクチャの仕組みはどのRDBも基本的に同じになっています。

 

オザック
なにかデータベースの不具合などを対応する時は上の図を思い出すと良いです！

 

SELECTの処理のフロー

では実際にSELECTのSQLが発行されたときの処理フローを説明していきます。

SELECTの流れ

  • まずデータは基本的にはインメモリのBuffer poolからデータが取り出されるような仕組みになっています。
  • Buffer poolにSELECTしたいデータがなかった場合、Tablespacesからデータをフェッチして取得してきます
  • その時はディスクを読み込みに行くためI/O負荷がかかることになります
  • 一度取得されたデータはBuffer poolの容量が許す限りはキャッシュされます。なので例えば`SELECT * from users where id = 1`というクエリーを一度実行した場合には、次はBuffer poolにデータがあるため、I/O負荷はかからないということになります。

 

参考

※Buffer poolはLRUアルゴリズムでデータの取捨選択をしています。

ざっくり言うと読み込まれる頻度の高いページは残されて、使用頻度の低いページを削除するアルゴリズムになり、そうすることで効率的にキャッシュを運用しています

(ただ厳密にはMySQLではLRUアルゴリズムではないようです)

参考文献

 

※あくまでオーソドックスなフローの説明になるので、その時のCPUやメモリ、各領域の状態によっては上記のようなフローから外れるエッジケースも有るようです

 

INSERTなどの処理フロー

INSERTの流れ

  • INESERT INTO users VALUES (1, 'hoge')のようなSQLを実行した場合は想定してみます。
  • まず基本的にBuffer poolにのデータが書き換えられます(いきなりTablespacesのストレージには書き込みにいかない点は注意です)
  • 続いてそのままLog bufferにINSERTしたデータを書き込みます(ここまではインメモリなので高速)
  • COMMITが呼ばれたタイミングでLog bufferにあるINSERTしたデータをRead Logに書き込みに行きます(ここで初めて不揮発性のストレージに書き込まれて永続化される事となります)
  • 最後にチェックポイントという決まった発生タイミングでTablespacesにINSERTされたデータが書き込まれるというフローになります(チェックポイントのタイミングは様々で、redoログファイルのサイズ分のデータ・ブロック数が書き込まれた時、指定された時間間隔、CPUの負荷が少ない時など様々)

 

データベース(RDB)の仕組みを理解するとインデックスの貼り方のコツがわかってくる

今回はデータベース(RDB)のアーキテクチャの仕組みについてざっくり説明しました。

プロダクトでパフォーマンスのボトルネックになるのは大体データベースです。

 

データベースのアーキテクチャや仕組み、インデックスの効率的な貼り方を知っておくと、Webエンジニアとしてのキャリアでも役に立つので、学ぶと良いです！

 

普段ゆる〜くアーキテクチャやデータベースのことなどを発信しています〜

Twitter@taraganoko1014

 

 

またMENTAで設計(DDDやクリーンアーキテクチャ)やAWSインフラ、IaC化などを教えていたりしまので、興味のある方は↓からどうぞ！

クラス設計(Clean, DDDなど)をカリキュラムを使って教えます！

サーバサイド&Terraform, CI/CD, AWSインフラの技術サポート

データベースの設計と仕組み解説(SQLチューニングもあり)

 

MySQLの仕組みがわかる参考文献

  • https://dev.mysql.com/doc/refman/8.0/ja/innodb-architecture.html
  • https://dev.mysql.com/doc/refman/8.0/ja/innodb-performance-midpoint_insertion.html

 

次読むと良い〜バックエンドのやばい設計記事〜

バックエンドの設計で直したほうが良いコード9選

 

© 2025 エンジニア副業道場 Powered by AFFINGER5