MySQL予習メモ (2020年12月版)

MySQLとしばらく密にお付き合いすることになりそうなので、色々と予習したことメモ。

InnoDBのバッファプール

• 取得処理では、バッファプールにデータがあればそれ返す。なければ、テーブルスペースから取得してプールして、それを返す。
• 更新系では、プールにデータを書き込む→ログに書き込む→非同期でログファイルの内容がテーブルスペースに書き込まれる。
• テーブルスペースに反映前のログファイルがあるときに、該当データを取得しようとすると、最初にテーブルスペースに書き込まれる。（SELECTだけなのに書き込みが発生する）
• バッファプールにはページ単位で書き込みがされる。
• 物理メモリの75%とかよく言われるらしい。
• ページとは、InnoDBでバッファプールに読み書きする最小単位。1行以上のデータが入る。
• InnoDBでは行の最大サイズはページサイズの半分以下に制限される。
• リンク
  • https://www.slideshare.net/yoku0825/mysql-80571224?from_m_app=ios
  • https://dev.mysql.com/doc/refman/5.6/ja/glossary.html#glos_page
  • https://dev.mysql.com/doc/refman/5.6/ja/column-count-limit.html

MyISAM

• メモリに書き込みして、ディスクへのフラッシュをOS任せにしている。なのでクラッシュしたらフラッシュ前のデータは揮発してしまう。
• トランザクションがなくその分のオーバーヘッドが発生しないため、高速に動作する。
• テーブル単位でロックするため、並行書き込みに優れない。

レプリケーション

• マスターで発行された更新処理を同じ順番でスレーブで実行することで、データの複製を行なっている。
• 実質非同期でデータは複製されている。
• マスターからスレーブに送られるのはバイナリーログ
• バイナリーログの形式
  • SBR: SQLステートメント単位(1SQL 文) 単純なSQL。マスターにあってスレーブにないデータがあっても空振りするだけでエラー出ない。
  • RBR: 行単位。更新前と更新後のデータが含まれる。マスターにあってスレーブにないデータがあるとエラーする。5.1から導入。5.7からデフォルト。
• pt-checksum / pt-table-sync
  • マスタースレーブのデータのズレを検出、修正する。SBRとRBRそれぞれで生成されたチェックサムを比較することでズレを検出する。SBRはスレーブで、RBRはマスターでチェックサムが生成される。
• リンク
  • https://www.slideshare.net/mobile/yoku0825/mysql-86831302

バックアップ

• フルバックアップ(ダンプファイル)と増分バックアップ(バイナリログ)を使ってリストアする。
• バイナリログにはポジション値がある。
• 同じ処理のログでもマスターとスレーブで値が異なる。
• SHOW MASTER STATUSで現在のポジション値を取得できる。mysqldump にオプションつけることで、その値をダンプファイルに含めることができる。
• GTIDはマスタースレーブで共通で使えるポジション値。
• バイナリログファイルのバックアップ
  • myswlbinligコマンドで対象サーバに接続して、バイナリログを追跡してバックアップする。
• バイナリログからリストアする
  • mysqlbinligコマンドでバイナリログからSQLを復元して、リストアする。
• リンク
  • https://www.slideshare.net/yoku0825/mysql-90289401?from_m_app=ios
  • https://dev.mysql.com/doc/refman/5.6/ja/mysqlbinlog-backup.html
  • https://dev.mysql.com/doc/refman/5.6/ja/point-in-time-recovery.html

こんなのあったんだ

• トリガー

  • 特定の操作に対してトリガーをセットできる
  • （ただ、アプリとDBの両方でトリガーが分散して管理コストがあがるので、柔軟性のあるアプリ側に寄せた方が良さそう。）
  • リンク https://dev.mysql.com/doc/refman/5.6/ja/trigger-syntax.html

• ストアドルーチン

  • 複数の処理をまとめたもので、ルーチンの名前を指定することで呼び出すことができる。ストアドプロシージャと関数。
  • リンク
    • https://dev.mysql.com/doc/refman/5.6/ja/stored-routines.html

• イベントスケジューラ

  • DB単体で定期実行ができる。（アプリケーションの定期実行とかぶるので、アプリ側に寄せた方が良さそう。）
  • リンク
    • https://qiita.com/magaming/items/6a941ef1c2f673de8ae7

パフォーマンスチューニング

• Optimizerは、実行計画の決定をする。
• Executer
  • 実行計画の通りにHandlerを叩く
  • Handlerからの結果をもとに残処理を行う。
  • この残処理をさせてしまうのが重たいので、なるべくExecuterに処理をさせないようにする。
  • キーワード
    • Using where Executerがフィルターしてる
    • Using filesort Executerがソートしている
• Handlerは、DBエンジンの抽象化レイヤー
• インデックス
  • 検索時はひとつのテーブルにつき一つのインデックスしか使えない。
  • USE INDEXで使用するインデックスを指定できる。
  • MySQLはB+木でインデックス作っている。
• 設定値
  • innodb_buffer_pool_size
  • innodb_log_buffer_size テーブルスペースに書き込まれるまで、ログファイルはここにいる。
• リンク
  • https://www.slideshare.net/yoku0825/mysql-57449062?from_m_app=ios

ネクストキーロックとは

• InnoDBで使うロックの一種
• 範囲走査するトランザクション内で、別のセッションが書き込んだレコードが読み取られないように(ファントムリード防止)するためのロック。
• インデックスロックとそのインデックスに至るまでのデータがない範囲(ギャップ)に対してロックがかかる。
• リンク
  • https://dev.mysql.com/doc/refman/5.6/ja/innodb-next-key-locking.html

Percona

• Percona
• MySQLのプロ集団企業
• ツールも色々出してる

SHOWステートメントいろいろ

SHOW GLOBAL STATUS
SHOW INNODB STATUS
SHOW MASTER STATUS
SHOW SLAVE STATUS