SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

直近開催のイベントはこちら!

Security Online Day 2022

2022年9月16日(金)10:00~17:10

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

MySQLチューニング虎の巻

MySQL InnoDBストレージエンジンのチューニング(前編)

004


InnoDBのアーキテクチャおさらい

 クエリは、物理レイヤーの特性を意識して書かなければいけないものの、基本的にはロジック(論理)を記述するレイヤーである。前回まで解説したオプティマイザは、論理と物理の中間に位置する(または両者をつなぐ)レイヤーであると言える。つまり、オプティマイザにとっての至上命題は、如何に効率良く下位のレイヤーを使うというものであると言える。一方、下位のレイヤーであるストレージエンジンはまさに実装(物理)の領域である。ユーザーが必要とするクエリに対していかに素早く応答するかが、物理レイヤーたるInnoDBのチューニングにおける最終目標となる。

 物理レイヤーのチューニングをするには、そのアーキテクチャを知ることが何よりも重要だ。アーキテクチャこそが物理レイヤーそのものだと言っても過言ではないからだ。以下では、チューニングを行う上で必須となる、基本的な構成部品について説明する。

全てはインデックス

 InnoDBはクラスタインデックスという構造になっており、データは全て主キーのリーフノードに格納されている。明示的な主キーがない場合には、内部的に6バイトのサイズを持つROWIDフィールドが追加され、主キーとして利用される。このROWIDはユーザーがアクセスすることはできない。セカンダリインデックスには、リーフノードに主キーの値が格納されている。セカンダリインデックスを用いた検索を行うと、セカンダリインデックスから主キーの値を導き出し、その値を用いて主キーが検索されることになるため、2回のインデックス操作が行われることになる。そのため、主キーとセカンダリインデックスでは主キーによる検索のほうが高速になる。次の図は、セカンダリインデックスと主キーを模式的に表したものである。

 このような構造になっているため、セカンダリインデックスは必然的に主キーの値を含んだマルチカラムインデックスとなる。例えば次のようなテーブルがあるとする。

CREATE TABLE t (
    id INT UNSIGNED NOT NULL,
    col1 INT NOT NULL,
    ...中略...,
    PRIMARY KEY(id),
    INDEX ix1 (col1)
) ENGINE INNODB;

 ix1という名前のインデックスは、col1しかカラムが指定されていないが、構造的には(col1, id)というマルチカラムインデックスと同じになる。そのため、Coverying Indexになる機会が増える。

 また、InnoDBではテーブルスキャンは主キーのインデックススキャンと同じである。そのため、レコードが主キーの順序でフェッチされることになり、ORDER BYを指定しなくてもソートされているという思わぬ副作用が生じてしまう。これは思わぬ落とし穴になることがある。これ幸いとORDER BYをつけずにSQLを記述していると、後で他のストレージエンジンやRDBMSへの移植が必要な時に大変な思いをすることになるので注意されたい。

 なお、多くのRDBMSがそうであるように、InnoDBのインデックスはB+ツリー構造になっている。全てのデータがB+ツリーで管理されているといえよう。ただしBLOBは例外である。

テーブルスペース、バッファプール、ログ

 InnoDBのデータは、テーブルスペースと呼ばれる領域に格納される。テーブルスペースは16KBのページ単位で管理されており、このページを用いてB+ツリーが構成される(次期バージョンであるMySQL 5.6では、4KBまたは8KBのページサイズをオプションで選択可能だ)。テーブルスペースにはinnodb_file_per_tableオプションの指定がない限り、共有テーブルスペースに全てのデータが格納される。共有テーブルスペースは、デフォルトでは自動的に拡張するようになっている。固定サイズのものやRAWデバイスを利用するものを作成することも可能だ。innodb_file_per_tableオプションを使用すると、テーブルごとにひとつのテーブルスペースが作成されるようになる。これは便利なオプションで、テーブルスペースは基本的に自動拡張となるが、テーブルをDROPしたりOPTIMIZE TABLEをすることでファイルシステムの領域を回収することが可能だ。

 テーブルへの参照や更新が行われた際、直接テーブルスペースへアクセスされるわけではない。全てのデータは、バッファプール上でキャッシュされる。参照時にはキャッシュに該当するデータがなければ、ディスクからキャッシュへデータがステージングされる。もちろんキャッシュされるのは16KBのページ単位だ。スキャンなどの場合には、64ページ単位(エクステントという)でステージングが行われる。また、データを更新するとそれがいきなりファイルが更新されるわけではなく、全てはキャッシュ上で操作が行われる。テーブルスペースの更新はランダムアクセスになりがちで時間がかかるため、後から非同期で行なわれる。すると、キャッシュとテーブルスペースに差異が生じることになるが、キャッシュ上だけにしか更新が反映されていない状態でMySQLサーバーがクラッシュすると、テーブルスペースにはデータが残らない(消えてしまう?!)ことになる。そのようなデータの消失を防ぐのがログだ。ログへの更新はテーブルスペースとは違い、トランザクションのコミット時に更新が行われるが、常にシーケンシャルになるので高速である。とはいえ、直接ログファイルへ更新すると遅いので、ログへ書き込む内容はログバッファ上でいったんまとめられる。

 InnoDBは再起動時にログの内容を読み込んで更新をREDOする。そうすると、バッファプール上のデータは最新の状態まで復帰することになる。突然クラッシュした場合には、再起動後にトランザクションの状態も復元されるため、それらはREDO後にUNDO(ロールバック)される。

 テーブルスペース、バッファプール、ログは切り離すことができない構成要素なのである。

 他にもまだまだ解説すべきことはあるが、以下で述べるチューニングで必要な仕組みは解説したので、今回のアーキテクチャの話はこの辺で留めておく。次回は、具体的にInnoDBでどこをチューニングしていくかを見ていこう。

この記事は参考になりましたか?

  • Facebook
  • Twitter
  • Pocket
  • note
MySQLチューニング虎の巻連載記事一覧

もっと読む

この記事の著者

奥野 幹也 (オクノ ミキヤ)

日本オラクル株式会社 MySQL Global Business Unitテクニカルアナリスト

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/3820 2012/07/26 13:42

Job Board

PR

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

2022年9月16日(金)10:00~17:10

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング