MySQL基礎まとめ

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版

インデックステーブル

B+Tree

  • 1つのノードがm個 (m>=2) の子ノードを持つことができる平衡木構造
  • ソートした結果をルートノードに格納して、いくつかの範囲に分ける
  • ルートノードの各範囲について子ノードが存在するように配置する
  • 子ノードが親ノードに従属するように、全てのノードを構成する
  • リーフにのみデータ（ポインタ）が存在する
  • ルートノードから各ノードを辿っていくことで目的のノード（リーフ）を探し出すことができる（計算量はO(logN/logM)）

InnoDBのインデックステーブル

  • Primary Indexはクラスタインデックステーブルに格納される
  • クラスタインデックステーブルのリーフには行データそのものが格納される
  • リーフの行データはPrimary Indexをソートした結果で順番に並んでディスク上に保存される

  • Primary Indexが連続している複数の行を取り出す場合は、シーケンシャルアクセスとなり非常に高速となる

  • Primary Index以外のSecondary Indexはセカンダリインデックステーブルに格納される

  • セカンダリインデックステーブルのリーフにはPrimary Indexが格納される

  • Secondary Indexが連続していても、Primary Indexが連続していなければ、行データを取り出す場合にランダムアクセスとなり遅くなる

  • Primary IndexをAUTO_INCREMENTにすることで、必ずPrimary Indexの値が既存の値よりも大きくなり、B-Treeへの追加が高速になる

  • InnoDBはよく使うクエリに対して、アダプティブハッシュインデックステーブルというバッファ上で利用できるテーブルを構築する（ハッシュをキーとしてテーブル検索するので計算量はO(1)）

  • MyISAMの場合は、インデックステーブルのリーフには行データを指すポインタが格納される

Covering Index

  • Secondary IndexもPrimary Indexのようにクラスタインデックステーブルに格納できないだろうか
  • 複合Index（インデックスのタプル）を使うと、複合Indexのインデックステーブルのリーフには、複合Indexに含まれるデータ（と完全な行データへのポインタ）が格納される
  • ただし、取得するデータと条件句で指定するデータが、全て複合Indexに含まれないといけない
  • また、複合IndexのTop Index（一番左に指定したインデックス）を条件句で指定しないといけない
  • 上記の条件を満たさない場合は結局ランダムアクセスが発生し遅くなる

WAL (Write Ahead Log)

  • まず、更新内容はメモリ上のバッファに書き込まれる
  • 同時に、ディスク上のログファイルにも書き込まれる
  • ログファイルは常にシーケンシャルアクセスが可能なため、ディスク上でも高速に書き込める（永続的なメディアへの同期が実現できているといえる）
  • ログファイルはリングバッファとなっており、その最大容量を指定できる
  • ログファイルの最大容量を使い切った時点で、あるいは、バッファ上のダーティページがある割合を超えた時点で、バッファの内容がディスク上のテーブルスペースに書き込まれる
  • 障害発生時はバッファ上に書き込まれた内容は失うが、ログファイルには残っているため、テーブルスペースの内容にログファイルの内容を追加することで復旧が可能
  • サーバ起動時に、テーブルの内容とログファイルの内容を比較して、ロールフォワードによる復旧が自動的に行われる

DoubleWrite

  • バッファプールのテーブルの内容は、ログファイルを使い切った場合、Dirty-page（更新されたページ）が多くなった場合、にディスク上のテーブルスペースに書き込まれる
  • テーブルスペースに書き込む際、まずダブルライトバッファと呼ばれるディスク上のスペースに書き込む
  • その後、テーブルスペースの該当のページに書き込む
  • これにより、テーブルスペースへ書き込んでいる最中のクラッシュによりデータを失っても、ダブルライトバッファから復旧できる

MVCC (Multi Version Concurrency Control)

レプリケーション

  • データベースに対する全ての変更は、SQLクエリの形（Statement Based Replication）か行データそのものの形（Row Based -）、あるいはその両方の形（Mixed Based -）で、バイナリログというログファイルに保存されている
  • マスターのマスタースレッドがバイナリログを読み込む
  • スレーブのI/Oスレッドがマスタースレッドに接続し、バイナリログを取得する
  • I/Oスレッドがバイナリログを、スレーブ上のリレーログに保存する
  • スレーブのSQLスレッドはリレーログを読み込み各テーブルを更新する
  • 負荷分散として、参照系のクエリは複数のスレーブで処理する

クエリ最適化

  • MySQLのオプティマイザはユーザからのクエリを、最適なクエリに変更して実行する
  • 例えば、INを使っていても、内部的にはEXISTSを使ったクエリに変更される
  • MySQLでは、結果がベクトルになるようなサブクエリを扱うことができない
  • MySQLでは、クエリは、JOIN/UNION/サブクエリ、に分類できる
  • JOINは各テーブルの行をfor文でネストしたような形で各行がアクセスされる（Nested Loop Joinアルゴリズム）ため、インデックスを使用しないと遅くなる
  • EXPLAINの出力を見ることで、インデックスが使われているか、ソートが実行されているか、一時テーブルが作成されているか、などを判別できる

チューニング

セッション

  • セッション（クライアントからの接続）ごとにスレッドが起動される（epollのようにノンブロッキングではない）
    • max_connections
    • innodb_thread_concurrency
  • セッション同時接続数を考慮して、スレッドの消費可能メモリ量を決める必要がある
    • sort_buffer_size, read_buffer_size, read_rd_buffer_size, join_buffer_size, tmp_table_size

バッファ

  • バッファ上のクエリキャッシュやテーブルキャッシュを最大限活用することで、ディスクI/Oが減り、レスポンス性能が向上する
    • query_cache_size
    • query_cache_type
    • innodb_buffer_pool_size

同期

  • クラッシュリスクと性能のトレードオフを考えて、バッファの情報をディスクに同期する間隔を指定する
    • innodb_flush_log_at_trx_commit
      • サーバ起動時に復旧に使われるログファイル（WAL）が対象
      • 0は、1秒毎にwrite/syncする
      • 1は、コミット毎にwrite/syncする
      • 2は、コミット毎にwriteして、1秒毎にsyncする
    • sync_binlog
      • レプリケーションに使われるバイナリログが対象
      • 何回バイナリログが更新されたら、ディスクキャッシュからディスクにsyncするか、を指定する
      • 0は、syncのタイミングをOSに任せる

カウンタテーブル

  • 別のテーブルに同じカウントを付ける場合、カウンタ値のみを持つテーブルを用意してカウンタを払い出してテーブル間で共有する
  • ただし、そのカウンタテーブルにカウンタ値のみ（つまり行がひとつ）を持たせると、テーブルロックはもとより行ロックによっても並行性を落とすことになる
  • 行を複数用意してランダムに更新していき、SELECT SUM(cnt) FROM counter_table でカウンタを読み取る、などの対策が必要になる

Explain

  • スロークエリログなどから時間のかかるクエリを取得し、Explainコマンドでその実行計画を分析してクエリを修正する
  • type=ALLのテーブルフルスキャンや、type=indexのIndexフルスキャン、などはクエリ改善の余地がある

準同期レプリケーション