ゲームを題材に学ぶ内部構造から理解するMySQL を読んだ

MySQLの内部構造についてもう少し理解を深めたいと本や資料などを探していた。ネットの記事の方が気軽に情報収集できるので、先にそちらを読むことにした。

上記は2019年の記事でMySQL8系にも対応していた。全8ページあり、1ページもボリュームもそんなになく読みやすかった。1h30minほどで読み切れた。

以下読んでいた時のメモ

# 第1章 DBサーバの構造を知ろう！
- RDBMSはページ単位でデータを読み書きする
- インメモリDBはレコード単位でi/oできる
- MyISAMは固定調のシーケンシャルファイルとしてデータが保存される。超えた場合はフラグメンテーションが起きる。トランザクション処理がない。テーブルロックがかかる。更新が行われないマスタデータに利用すると高速化できる可能性がある。
- PKはクラスタードインデックスという形式で格納される(ルート・ブランチ・リーフ)
- 複合キーを使って同時に使うことが多いデータは同じページになる可能性が高くなるため、i/oの高速化が期待できる
- B-Treeインデックス。mysqlで特定のカラム値のある行を素早く見つけるための仕組み
  - 通常のインデックスは、キーとアドレスの組み合わせで保存される
- 更新処理で行っていること
  - ログバッファ(HDDに遅延書き込み)
  - メモリ上のデータを書き換え
- 再起動時にログファイルのデータをHDDに書き込む
- バックアップファイルとそれ以降のログファイルがあれば、最後にコミットされた時点まで復元可能

# 第2章 ゲーム系で確認すべきパラメータ
- OLTP側は小さいページの方が有利になる可能性がある。
- ただし利用されない空白が多くなる
- TEXT、BLOBなどのデータ型は別ページに保存され
- ページサイズが大きいほど、データが細密に充填されメモリやHDDの利用効率が上がるため、大量のデータの処理に向く
- ログファイルが溢れて、HDDへのフラッシュ（実データの書き込み）が大量に起き、サーバの負荷が高くなる
- innodb_flush_log_at_trx_commit: ログファイルへの書き込みを1sに1回、などに設定できる。書き込み負荷を減らすことに貢献する。デフォルトはコミットごとの書き込み。最大1sデータをログファイルに書き込まれたデータをロストするリスクもある。またバッファが足りなくなったら、1s立たなくても書き込みが行われないことがある
- innodb_buffer_pool_size: 各処理が行える量以上に割り当てないようにしつつ、できる限り大きく取る。目安はキャッシュヒット率99~95%。

# 第3章 実行計画を見てSQLの構造を理解しよう
- sqlはDBエンジンの中のオプティマイザが手続型のソースに翻訳してコンパイルして実行するという処理で行われる
- データと分散具合によってアルゴリズムが変わる
- DBエンジンはヒット率を予想するために事前に統計情報を作っている
- 統計情報が更新されるとき
  - インデックスが全体の数%書きかわったとき
  - ランダムにインデックスの数ページを選んで統計情報を作成する
- oracleは実行計画が精密だが、更新のパフォーマンスが高い
- joinの順番を変えても実行計画は変わらない。使われているテーブルと結合条件が同じであれば

# 第4章 NoSQLとSQLの使いどころを知ろう
- memcacheとHandlerSocket(mysqlをKVSとして使用できるプラグイン)で処理速度の比較の例。今回の実験ではHandlerSocketの方が早かった
- 処理時間のほとんどがネットワーク処理とsqlのオーバーヘッドで費やされている傾向にあった
- NoSQLを使うと集計処理をDB側でできないためN+1のような非効率な処理になりうる
- NoSQLの方がMySQLよりも早いと言われるが、適材適所。ユーザアクションに対して1件のレコードだけを処理する」機能が多ければNosqlの方が適している可能性がある

# 第5章 DB側でやること、アプリ側でやることを見極めよう
- シンプルなクエリを大量に発行する処理(N+1)はsqlのオーバーヘッドとネットワークの処理が無駄に繰り返されるだけなので、パフォーマンス向上にも寄与しない
- アプリケーション鯖で肩代わりできる処理は、キャッシュやソート(ページャーがない)処理くらいしかない
- DBサーバから大量のデータをAPサーバにすべて値渡しして処理するより、DBサーバ内でできる処理は参照渡しで処理するほうが効率が良い

# 第6章 DBサーバでゲームの処理を作るとどうなる？
- MySQLのストアドプロしーじゃはプリコンパイルされないため、case式の方が高速
- さらに速度を追求する場合はUser Defined Function

# 第7章 パーティショニング
- 多くの処理で抽出条件に指定されるカラム（ゲーム系ではユーザIDなど）がある場合、レスポンスやサーバの負荷削減に効果が高い機能
- MySQLでは、サブパーティションまで作成できる
- MySQLでは、パーティショニングキーになるカラムは、プライマリーキー、または、ユニークキーの一部である必要がある
- 外部キー制約がついているテーブルはパーティショニングができない」という制限がある
- 種類
  - ハッシュ: 整数値のカラムの値を分割する個数で割った余りの値で分割する
  - キー
  - レンジ: 保持期限で削除したい場合、DELETE文で削除するとインデックスの更新処理などが必要になるため非常に時間がかかりますが、レンジパーティショニングにしておけば、不要なパーティションをDROP文で捨て去ることができる
  - リスト: Languege = 'jp'などカーディナリティの低いカラムで分割

# 第8章 （最終回）　まとめ―
- 集合のイメージを持つ