リレーショナルデータベースの一つである MySQL は、それ自体複雑なソフトウェアです。
クライアントからのコネクションを接続・維持して、クエリをパースして、クエリ最適化を施して、InnoDB を始めとするストレージエンジンと API を経由して連携をとって。進化をし続けるソフトウェアの全貌を理解するのはほとんど不可能ですが、少なくとも押さえておきたい知識があります。
この記事は、アプリケーションディベロッパーになりたての人や、ジュニアレベルの DBA や SRE 向けの記事です。逆にいうと、数年経験があるシニア層にとっては当たり前の知識でしょう。Computer Science の "Database Management System 101" で目にするキーワードを絞って書いたような記事です。
まず、MySQL サーバーとストレージエンジンは別であるという事実を理解しましょう。ストレージエンジンの実装によって、可能なクエリの最適化やデッドロック回避の実装、設定可能な Isolation Level が異なります。デフォルトでは汎用性の高い InnoDB がストレージエンジンとして使われます。一方の MySQL サーバーでは、コネクション管理やクエリのパースに集中できます。MySQL サーバーとストレージエンジンは抽象化された API を通じて租結合に実装されています。これによって、拡張性の高さを実現しているのですね。なぜこの理解が必要かというと、例えば「Row Locks は（サーバー側ではなく）ストレージエンジン側で実装されている」という理解に繋がり、それによって他の機能に対する裏側の仕組みへの理解が深まるからです。
次に、Read Locks と Write Locks を理解しましょう。Shared Locks および Exclusive Locks と呼ばれることもあります。全く難しいコンセプトではありません。テーブルや行を読み込むときに、読み込むだけであれば、誰がいつどのタイミングで読んでも同じ結果が得られます。ですので、他の Read をブロックする必要がないため、Shared Locks と呼ばれます。一方で、書き込みをするということは結果が変わります。その場合、Read するプロセスによって読み込み結果が変わっては困るため、書き込む側が Exclusive Locks、つまり排他ロックを獲得します。
また、ロックできるリソースの粒度も理解しておきましょう。基本的には、粒度が小さい方が他のプロセスの邪魔をせずに済みますが、粒度が細かければ細かいほど「ロックを確保して、処理を知って、ロックを解放する」というように CPU クロックに対するオーバーヘッドが大きくなります。トレードオフですね。まずはテーブルを丸っとロックする Table Locks はわかりやすいですね。ただしこれだと書き込みが多いテーブルに対する Write のパフォーマンスが大きく下がってしまいます。自然な発想として、行ごとにロックしたくなりますね、これが Row Locks です。
そして、もちろんトランザクションについても知っておく必要があるでしょう。まず、ACID 特性についてはどの教科書でもどのブログでも紹介されている基本の概念ですね。Atomicity / Consistency / Isolation / Durability がそれぞれ何を説明するかはざっくり把握しておきましょう。
トランザクションの粒度の一つである Isolation Levels についても欠かせません。ただしこのトピックは少し複雑で、各実装の仕様や標準などの理由により、奥が深いです。ですが、キーワードを聞いたことがある、レベルでも良いので頭に入れておきましょう。ANSI SQL という規格が入り口です。まず、現実問題ほとんど使われることない READ UNCOMMITED と、デフォルトで使われることが多い READ COMMITED は、名前から察することができるでしょう。MySQL のデフォルトである REPEATABLE READ も重要です。また、一番厳しい粒度として SERIALIZABLE の存在も忘れてはいけません。
Isolation Levels について知るためには、自然とそれぞれのレベルがどんなバグを避けられるのか、よくあるデータ不整合のパターンについても自然と目にすることになるでしょう。コミットされていないデータを読み込んでしまう Dirty Reads、読み込んだタイミングによって異なる結果が現れてしまう Non-Repeatable Reads、範囲クエリを利用したときに本来存在すべきではない行が現れてしまうことがある Phantom Reads について調べてみてください。
また、ロックの仕組みには、InnoDB の話では二相コミット (Two-phase locking protocol）が実装されていることを覚えてください。学校で Computer Science を学んだ場合にはきっと耳にしたことがあるでしょう。
複数台クラスター構成を組んでデプロイすることが当たり前になった昨今のデータベース運用ですが、Leader / Follower 構成、およびデータの複製の仕組みについて思いを馳せましょう。裏側では Binary log を非同期で複製しています。全くのリアルタイムではなく、数 ms (リージョンを跨ぐか同じサーバーかどうか、などもちろん場合による) の遅延が生じるのは避けられません。
他には、データベースがデータの Durability を担保するためにはディスクにデータを書き込む必要がありますが、毎回 Disk I/O を発生させていたらディスクも消耗させてしまいますし、遅いったらありゃしません。そのため、Write-Ahead Log (通称 WAL) という仕組みを使って、一旦メモリにバッファしておいてバッチでディスクに書き込む、という処理が行われます。WAL って聞いたことが多い方も多いでしょう。
最後に、インデックスがそもそも何かについては理解しておきましょう。誤解を恐れずに雑にいうなら、別の見方をしたテーブルのもう一つの姿です。ですからストレージも消費しますし、銀の弾丸ではありません。とりあえずインデックスを貼れば万事解決、というわけではないのでご注意を。
DDL や .idb ファイル、メタデータ、MMVC、その他ストレージエンジンごとの特徴やモニタリングのノウハウは、これから学んでいけば良いでしょう。アプリケーションを実装するにあったってのマイグレーションや効率的なインデックスの貼り方など、現場で学ぶべき知識はまだまだあります。以上で書いたキーワードは、ストレージエンジンの実装に限らず、少なくとも MySQL の話をしているならジュニアレベルを含めその場の全員が頭に入れておくべき最低限の内容をお話ししました。
ちなみにここら辺の知識は、『High Performance MySQl』の前半にもまとめられています。良著です。