実践ハイパフォーマンスMySQL 第2版 (1章)

MySQL 書評


今の会社で、自分が入社する前から会社で行われている本書の読書会に自分も追いつくべく読み始めました。
感想を書きたいところですが、「1章は概要」＋「イマイチ理解出来ていない」ということで、ただの自分用メモな箇条書きになっています。公開してすみません。。

MySQLのアーキテクチャ

  • ３つの層に分かれている。
    • 接続、認証、セキュリティなどMySQL特有ではないものが含まれている層。
    • クエリ解析、キャッシュ、組み込み関数、ストアドなど中枢部とも言える層。
    • ストレージエンジン。
  • ロックはテーブルロックと行ロックがあり、使用するストレージエンジンによる。MySQL自体はALTER TABLEなどでテーブルロックを使用している。

トランザクション

Atomicity（不可分性）
  • やるかやらないか、ただそれだけ。
Consistency（一貫性）
  • 矛盾はダメ。
Isolation（分離性）
Durability（永続性）
  • 確定したデータは消えない。

分離レベル

READ UNCOMMITTED
  • 他のトランザクションによるコミットされていないデータを読み取ることが出来る。
    • 問題が発生しやすい。
READ COMMITTED
  • MySQL以外のほとんどのDBでデフォルトとなる挙動。
  • コミットするまで他のトランザクションからは見えない。
REPEATABLE READ
  • MySQLのデフォルトの挙動。
  • 同じトランザクション内で読み取る行が「同じに見える」ことを保証する。
  • 単純に書くと簡単ですが、ファントムリードの問題などを考えてみると難しい。。
SERIALIZABLE
  • トランザクションを直列的に実行していく。
  • 安全に処理が出来るが、ロックされる時間が長くなるためタイムアウトやロック競合が頻発する恐れがある。

データ書き込み

  • データはメモリ上のみで更新して、ディスクにはログだけを書き込みことで高速に操作することが出来る。
    • ログの書き込みは狭い範囲へのシーケンシャルなI/Oとなるため。

トランザクション

マルチバージョンの並行性制御(MVCC)

  • 行ロックにひねりを加えたもの。
  • InnoDBではレコードに行が作成されたタイミングと削除されたタイミングを、システムバージョン番号で記録しその番号をもとに処理を行う。
SELECT
INSERT
  • 現在のシステムバージョン番号を記録する。
DELETE
  • 現在のシステム番号を削除バージョンとして記録する。
UPDATE
  • 更新後データを新しいシステムバージョン番号を記録して書き出す。
  • 更新前データの削除バージョンとして現在のシステムバージョン番号として記録する。

ストレージエンジン

  • MySQLのテーブル定義はデータディレクトリの下にあるデータベース名ディレクトリの中に「テーブル名.frm」という拡張子がついて置かれている。
  • テーブルのストレージエンジンを知るには下記SQLを実行する。
mysql> SHOW TABLE STATUS LIKE 'テーブル名'\G
MyISAM
  • 5.5ではデフォルトのストレージエンジンがInnoDBになっていたりとどんどん使われること状況が少なくなりつつあるのかなと思っていますがその特徴を。。
  • データファイルは「.MYD」、インデックスは「.MYI」ファイルに保存されている。プラットフォーム依存していないので、ファイルを別プラットフォームのマシンに持っていって使うことが出来る。
  • テーブルロック。選択クエリが実行されているテーブルに対して新しい行を挿入することが可能。
  • 自動修復と手動修復
  • BLOBとTEXTで最初の500文字でインデックスを作成することが出来る。全文インデックスをサポートしている。
  • 遅延キー書き込み（頻繁に変化する使用頻度の高いテーブルのパフォーマンスを向上させることができる。）
  • myisampackユーティリティにより、読み取り専用の圧縮されたテーブルを作成することが出来る。圧縮によりI/Oが減るのでパフォーマンスが向上する。
MyISAM Merge
  • 複数の同一のMyISAMテーブルを１つの仮想テーブルに組み合わせたものらしい。
InnoDB
  • 一番使うことになりそうで、しっかりと理解しておきたいストレージエンジンです。
  • トランザクション利用目的以外でも、パフォーマンスと自動リカバリを目的に多く利用されている。
  • クラスタ化インデックス。（詳細は後の章で）
  • 非常に高速な主キールックアップが実現されるが、セカンダリインデックス（主キーでないインデックス）に主キー列が含まれるため、小さな主キーを意識することが必要。（インデックスの圧縮もしない）
  • 外部キー制約が使える。
メモリエンジン
  • キャッシュのような使い方で、変化しなくて再起動後に残っている必要がないデータを入れる。（郵便番号など）
  • テーブルロックで固定長だけをサポートする。
Archiveエンジン
  • INSERTとSELECTクエリのみをサポートし、データ書き込みをバッファリングし挿入時にzlibで圧縮する。
  • ログの記録などに向いている。高速にINSERTが出来るので、レプリケーションマスタで使うなど。
CSVエンジン
  • CSVファイルをテーブルとして扱う事ができる。
Federatedエンジン
  • ローカルにデータを格納せずに全ての操作をリモートサーバーに接続して行う。
Blackholeエンジン
  • Insertを格納せずに棄てる。クエリはログに記録されるので監査したい時などに使う？
NDB Clusterエンジン
  • すごく複雑らしい。サーバーRAIDのように、データの断片を複数ノードに分散して格納する。同じ断片が複数のノードが存在するので冗長性と高可用性がある。
Falconエンジン
  • これが執筆された段階では未完成だった。
solidDBエンジン
  • 悲観的な並行性制御と楽観的な並行性制御の両方をサポートしている。
  • InnoDBに似ている点も多く、オンラインバックアップ機能が無償で含まれている。
PBTXエンジン
  • ログの先行書き込みを避けるためにトランザクションログとデータファイルを使用し、トランザクションコミットのオーバーヘッドの多くを解消する。操作によってはInnoDBよりも高速になる。
  • 比較的新しいエンジン。
Maria DB
  • MyISAMのに取って代わることを目的に作られた。(しかし、5.5で取って代わったのはInnoDB)
  • ロードマップ（実現されていない？？）
    • テーブルごとのトランザクショナルまたは非トランザクショナルストレージオプション
    • クラッシュからのリカバリ
    • 行レベルのロックとMVCC
    • BLOB処理の改善
注意点
  • トランザクションを必要とする場合は、InnoDBが安定しており実績もあるのでオススメ
  • 挿入と読み取り（更新はのぞく）の同時性だけが必要な場合はMyISAMが有効。
  • 複数のストレージエンジンを使用しているとバックアップやサーバーの調整が複雑になることも。
  • クラッシュからのリカバリを考慮する場合は、InnoDB。
ストレージエンジン選びの例
  • ログテーブル
    • MyISAMとArchiveエンジンは挿入時のオーバーヘッドが少ない。
    • ログを修正してレポートを作りたい場合は、レプリケーションをしてスレーブで集計処理を行えばいい。
  • 読み取り専用または読み取りがメインのテーブル
    • クラッシュすることを許容出来るのであればMyISAMが高速。
  • 株式指標テーブル
  • 掲示板やフォーラムテーブル
    • 2章で！
  • CD-ROMアプリケーションに格納するテーブル
    • 圧縮したMyISAMがいい。
テーブル変換
  • ALTER TABLE
    • 古いテーブルから新しいテーブルへの行単位のコピーを実施するので遅い。
    • ストレージ固有の機能は無効になる。
mysql> ALTER TABLE hoge ENGINE = InnoDB
  • ダンプとインポート
    • 細かく制御することが出来るけど、注意してやらないと誤操作でデータを失う恐れがある。
  • CREATE と SELECT
    • 新しいテーブルを作成し、そこにINSERT ... SELECT でデータを入れる。
# old_table は MyISAMとする

# 古いテーブルと同じ定義の新しいテーブルを作る
mysql> CREATE TABLE new_table LIKE old_table;
# 新しいテーブルの定義をInno DBに
mysql> ALTER TABLE new_table ENGINE=InnoDB;
# 古いテーブルのデータを新しいテーブルに流し込む
mysql> INSERT INTO new_table SELECT * FROM old_table;

まとめ

  • １章から難しかったです。。でも2章からも頑張って読もうと思います！