実践 ハイパフォーマンスMySQL(第2版)を斜め読みして前半の重要なポイントだけをまとめてみた
- 作者: Baron Schwartz,Peter Zaitsev,Vadim Tkachenko,Jeremy D. Zawodny,Arjen Lentz,Derek J. Balling,伊藤直也(監訳),田中慎司(監訳),吉川英興(監訳),株式会社クイープ
- 出版社/メーカー: オライリージャパン
- 発売日: 2009/12/14
- メディア: 大型本
- 購入: 17人 クリック: 373回
- この商品を含むブログ (46件) を見る
実践ハイパフォーマンスMySQLですがMySQL開発者にとっては誰もが目を通しておいた方が良いと推薦される本なので重要なポイントだけをまとめてみたいと思います。まだ前半しか読んでいませんがMySQLの細かいテクニックが載っていて感動です。全部で700ページ近くある本なので斜め読みをした結果を載せて行きます。内容としては実践的なものをカットし、理論的なものだけを抽出しています。今回の記事だけでなく複数回に分けて内容を紹介したいと思います。この記事とは関係なく過去にIndexについての記事を書いたのでそちらも参照してもらえればと思います。MYSQL INDEXのまとめ - Yuta.Kikuchiの日記
目次
MySQLのアーキテクチャ
MySQLの論理アーキテクチャ
- MySQLの論理的なアーキテクチャは以下の3つのレイヤから構成される。
- 接続管理とセキュリティ
- 最適化と実行
- ストレージエンジン
- 最適化と実行例レイヤがMySQLの中枢であり、Query解析、分析、最適化、キャッシュ、組み込み関数、ストアドプロシージャ、トリガ、ビューなどが含まれる。
- ストレージエンジン間の違いを吸収するためサーバとエンジンはAPIを経由してデータのやり取りを行う。
- クライアントから接続されるときは1つのスレッドを作成し、スレッドはコアやCPUと関連付けられる。スレッドは常にキャッシュされる。
- 接続はSSLでも可能である。
- 最適化のレイヤではQueryを解析して内部構造を作成し、Queryの書き換え、テーブルを読み取る順序、使用するインデックスの選択などが行われる。
- オプティマイザが最適化を試みる。オプティマイザについては後で詳しく説明。
- 発行したQueryに対する結果がキャッシュされていれば(クエリキャッシュ)ばその結果を返すだけ。
平行性の制御
- データベースの世界ではロック問題が絶え間なく発生する。ロックシステムの実装は大きく分けて次の２点
- 共有ロック(読み取りロック)
- 排他ロック(書き込みロック)
- ロックの粒度でロック対象のデータ容量を最小に抑える事が望ましい。
- ロックのオーバヘッド(ロックの取得、チェック、解除)にリソースが取られパフォーマンスが低下する可能性がある。
- テーブルロックは最もオーバヘッドが低い。テーブル全体をロックするから1クライアントが書き込み中は他のクライアントからの読み取りや書き込みは全て拒否される。
- 書き込みロックは読み取りロックよりも常に優先される。
- 行ロックは平行性が最も高いがオーバーヘッドも同様に高い。行ロックはストレージエンジンで実装されている。
トランザクション
- トランザクションとは1つの作業単位として不可分に扱われるSQLQueryの集まり。処理を一つにまとめて失敗した時は処理全体をロールバックする事を可能にする。
- トランザクションはACID特性を持つ必要がある。Atomicity(不可分性)、Consistency(一貫性)、Isolaction(分離性)、Durability(永続性)
- トランザクジョンもパフォーマンスを下げるので、不必要であればトランザクションに対応しないストレージエンジンを選ぶ事によって改善できる可能性がある。
- トランザクションは4つの分離レベルが定義されている。分離レベルが低いほど平行性が高くなる。READ UNCOMMITTED、READ COMMITTED、REPEEATABLE READ、SERIALIZABLE。
- デッドロックとは複数のトランザクションが互いに拘束し合うこと。トランザクジョンが異なる順序でリソースをロックしようとした時に発生する。ロックの解放を永遠に待ち続けてしまう。
- デッドロックを回避するために検知システムとタイムアウトを実装している。InnoDBなどは循環依存に気づいてすぐにエラーを返す。InnoDBのデッドロックへの対処法は排他ロック行がもっとも少ないトランザクションをロールバックすることである。
- トランザクションログをはトランザクションの効率化に役立つ。ストレージエンジンは変更が発生する度にディスク上のデータを更新するのではなく、データのメモリ上のコピーを変更する。その後にトランザクションログに変更を記録する。
- MySQLデフォルトではAUTOCOMMITモードで動作している。トランザクジョンを明示的に開始しない限り各Queryを別々のトランザクションで実行する。
- InnoDBとMyISAMの併用をしたときに、トランザクションのロールバックが発生した場合は注意しないといけない。理由は非トランザクションテーブルへの変更を取り消す事ができないからデータの矛盾が発生してしまう可能性がある。
マルチバージョンの平行性制御(MVCC)
MySQLのストレージエンジン
- 各MySQLストレージエンジンのまとめ
ストレージエンジン MySQLバージョン トランザクション ロックの粒度 用途 使用すべきでない状況 MyISAM 全て 不可 同時挿入が可能なテーブル SELECT、INSERT、一括読み込み 読み取りと書き込みの混在 MyISAM Merge 全て 不可 同時挿入が可能なテーブル セグメント化されたアーカイブ、データウェアハウジング 多くのグローバルルックアップ Memory(ヒープ) 全て 不可 テーブル 中間計算、静的なルックアップデータ 大きなデータセット、永続ストレージ InnoDB 全て 可 MVCCを使用する行レベル トランザクション処理 無し Falcon 6.0 可 MVCCを使用する行レベル トランザクション処理 無し Archive 4.1 可 MVCCを使用する行レベル ログ、集計分析 無し CSV 4.1 不可 テーブル ログ、外部データの一括読み込み ランダムアクセスの必要性、インデックスの処理 Blackhole 4.1 可 MVCCを使用する行レベル アーカイブのログ、レプリケーション 意図された用途以外 Federated 5.0 - - 分散データソース 意図された用途以外 NDB Cluster 5.0 可 行レベル 高可用性 ほとんどの典型的な用途 PBXT 5.0 可 MVCCを使用する行レベル トランザクション処理、ログ クラスタ化インデックスの必要性 solidDB 5.0 可 MVCCを使用する行レベル トランザクション処理 なし Maria 6.x 可 MVCCを使用する行レベル MyISAMの代替 なし
ボトルネックの検出:ベンチマークとプロファイリング
ベンチマークを実行する理由
- 現在のパッフォーマンス測定、システムのスケーラビリティ、成長予測、環境の変化、設定をテストするような状況で役立つ可能性がある。
ベンチマーク戦術
- 以下ベンチマークでの誤り
- 実際のデータサイズの一部しか使用しない、不正確に分散されたデータを使用する事。
- 分散されたQueryパラメータを使用する事。
- マルチユーザアプリケーションに対してシングルユーザのシナリオを使用する事。
- 1台のサーバ上で分散アプリケーションのベンチマークを実行する事。
- 現実のユーザの振る舞いと一致しない事。間髪入れずにリンクを次から次へとクリックする事はない。
- ループを使って同じリンクをクリックする事。
- エラーをチェックしない事。
- 再起動直後でのパフォーマンス測定。
- デフォルトのサーバ設定を使用する事。
- ベンチマークは自動化できると望ましい。
- 複数回実行した結果の全体平均か、効果が良かった何回かの平均が望ましい。
スキーマの最適化とインデックス
最適なデータ型の選択
- データ型は最も小さいものを使用するように心がける。
- 文字よりも整数など単純なものがより良い。
- フィールドはNOT NULLとして定義すべき。
- NULL値を設定できる列はインデックス、インデックス統計、値の比較を複雑にする。またより多くの記憶域を使用し、MySQL内部で特殊な処理を要求する。このような列にインデックスを付けるとエントリごとに必要なバイト数が増え固定長のインデックスがMyISAMで可変長のインデックスに書き換えられる事もある。
- NULLの代わりに0や特殊な値、空の文字列を使用するよう検討する。
- DATETIMEとTIMESTAMP型は同じ種類のデータを格納する事が出来る。TIMESTAMP型は半分の記憶域しか使用せず、タイムゾーンに対応し、特殊な自動更新機能も備えている。その反面、許容値の範囲はずっと狭く、特殊な機能があだになることがある。
- 整数型(TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT)のどれか一つを使用する。整数型はUNSIGNED属性を持つ事で整数の上限を約2倍にする事ができる。
- 整数の計算には32ビットアーキテクチャであっても64ビットで計算される。
- 実数型(DOUBLE,FLOUT,DECIMAL)がある。DECIMAL型はMySQL5.0以降小数計算の正確な演算をサポートする。
- 浮動小数点型(DOBULE,FLOUT)の記憶領域はDECIMAL型より少ない。DECIMAL型は記憶領域と計算コストが増えるので正確な結果が必要な場合のみ利用する。
- 文字型はVARCHARとCHAR。
- VARCHARは可変長文字列を格納する。必要な記憶域しか使用しないから固定長の方ほど多くの記憶域を要求しない。ROW_FORMAT=FIXEDで作成されたMyISAMテーブルでは例外があり、行ごとに一定量のディスク領域を使用するため記憶域を無駄にする可能性がある。
- VARCHARはデータの長さを記録するために255バイト以下は1バイト、255バイト以上は2バイト余分に使用する。
- VARCHARは記憶域を節約するために役立つが、更新時に大きくなる可能性があり、余分な作業が増える。
- CHARは固定長の文字列データ型。CHAR型の値を格納する場合、MySQLは末尾のスペースを全て削除する。
- CHARは非常に短い文字列を格納する場合、全ての値がほぼ同じ長さである場合に役立つ。
- CHARはデータの断片化が発生しにくい。頻繁に変化するデータに対してはVARCHARよりも適している。また非常に短い文字列を格納する場合にも役立つ。
- バイナリ文字列を格納するのはBINARYとVARBINARY。バイナリ文字列は文字列と似ているが文字の代わりにバイトを利用する。
- BINARYとVARBINARYはバイナリデータを格納する必要があり、MySQLに値を文字ではなくバイトとして比較させたい場合に役立つ。バイト比較は大文字/小文字の区別が無いなどの利点がある。BINARY型の文字列を1バイトずつ数値として比較する。よって文字列の比較よりずっと単純である可能性があり、高速。
- 大量のバイナリや文字列を格納したい型はBLOBとTEXTである。文字型にはTINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT、バイナリ型にはTINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOBがある。
- BLOB、TEXT型の値を独自のIDを持つオブジェクトとして処理する。
- BLOB、TEXT型のデータは文字列全体でソートするのではなく、最初のmax_sort_lengthだけをソートする。変更したい場合はmax_sort_lengthを調整するかORDER BY SUBSTRINGを利用する。
- MySQLはBINARY、TEXT型にはインデックスを付ける事ができない。
- ENUM型は文字列値を65535個まで格納可能。非常にコンパクトに格納する。
- ENUMのソートは文字列ではなく、内部の整数値で行われる。FILED関数を使ってsortの順序を明示的に指定する事も可能だが、インデックスを使用しなくなる。
- ENUMは文字列のリストが固定であり、文字列の追加削除にはALTER TABLEを使用する必要がある。
- ENUMは整数値として格納するため、文字列表現のルックアップなどオーバーヘッドが伴う。
- 日付と時刻型にはDATETIMEとTIMESTAMP型がある。
- DATETIMEはタイムゾーンに関係なくYYYYMMDDHHMMSS形式で整数にパックする。記憶には8バイト使用される。
- TIMESTAMPはUNIXタイムスタンプと同じ。記憶領域を4バイトしか使用しない。FROM_UNIXTIMEとUNIX_TIMESTAMP関数で日付間の変換を行う。TIMESTAMPは指定せずに挿入すると自動的に現在時刻を入れる。またデフォルト設定がNOT NULL。TIMESTAMPはDATETIMEよりもストレージ効率が良いため使用できるのであれば使用すべき。
- ビットデータ型にはBITとSETがある。
- MySQLではBITを数値型ではなく文字列型で扱う。中に含まれているのはバイナリの0または1であるが、結果を取得したときに文字列に変換される。BIT型は使用しない方が良い。
- 真偽値を格納する場合SETを使って複数の列を一つにまとめることを検討する。SETはストレージ効率が良い、FIND_IN_SETやFIELDといったqueryを使用しやすい関数がある。列変更のコストがかかる。SET型のルックアップにインデックスを使う事ができない。
- SETの代わりとしてぱっくされたビットの集合に整数を利用するとビット演算で操作可能。SETに対する利点はALTER TABLEを使用しないでフィールドが表す列挙を変更可能。欠点はqueryを記述したり理解する事が難しくなる。
- 識別子としての選択で、整数型は素早く処理されAUTOINCREMENTに対応するので一番良い。ENUMとSETは注文状況、製品の種類、性別等の情報を格納するのに適している。
- 文字列型も識別子に利用しない方が良い。MyISAMではデフォルトでパックされたインデックスを使用するがルックアップが低速になる事がある。
インデックス
- インデックスの重要性はデータサイズに比例する。
- インデックスはサーバレイヤではなくストレージレイヤで実装される。エンジンごとに少しずつ動作が異なる。
- MySQLのほとんどのストレージエンジンでサポートされているのはB-Treeインデックス。MyISAMではインデックスを小さくするためプレフィックス圧縮を利用する。InnoDBでは最適化の一部においては圧縮されたインデックスを圧縮できないのでインデックスを圧縮しない。MyISAMは行格納時の物理的な位置に寄ってインデックスがついた行を参照するが、InnoDBはそれらの主キーで参照する。
- ツリーのノードは子リーフへのポインタを保持する。ストレージエンジンはそのポインタを辿る。
- B-Treeインデックスは完全なキーの値、キーの範囲、キーのプレフィックスによる検索に適している。
- ツリーのノードはソートされているので検索とORDER BYクエリの両方に使用する事が出来る。
- B-Treeにはインデックス付きの列の左から検索が始まらないと効果がない、インデックス列をスキップできない、LIKEのような範囲条件ではその列のインデックスが使用されない。
- ハッシュインデックスをサポートしているのはMemoryストレージエンジンだけ。ストレージエンジンはハッシュコードをインデックスに格納し各行へのポインタをハッシュテーブルに格納する。インデックスは短いハッシュ値の格納だけで非常にコンパクト。ハッシュ値の長さは列の型に依存しない。
- ハッシュインデックスにはインデックスの値を使用する事で行の読み取りを回避することはできない、ソートに使用できない、部分マッチングをサポートしない、等価比較をサポートし範囲クエリを高速化できない、競合が発生した場合はリンクリストを辿り正しい値を比較しなければならない、競合の数が多いとメンテナンスに時間がかかる。
- NDB Clusterでは一意なハッシュインデックスをサポート、InnoDBでは適応ハッシュインデックスという機能を備えている。
- URLのような長い文字列を条件指定する場合はカスタムハッシュを使う。URLのハッシュにより整数等を生成してそれに対してインデックスを付与する。しかしこの方法の欠点はハッシュの値を管理しなければならないこと。 ハッシュ関数にはSHA1やMD5などの長い文字列を返す関数を使用するとスペースや比較のパフォーマンスを下げる。SHA1やMD5は競合をほぼ排除するための設計であるが、カスタムハッシュインデックスの主目的は単純な関数でほぼほぼの競合率でより高いパフォーマンスを出す事。
- カスタムハッシュの競合を回避するためには元のキーワードもWHERE句のANDで指定するなど対応が必要。
- その他のインデックスとして空間(Rツリー)インデックス、全文インデックスなどがある。
高いパフォーマンスを実現するためのインデックス戦略
- 列が式の一部や関数に含まれていてはならない。
- BLOB、TEXT、長いVARCHARには完全なインデックスを付ける事ができないのでプレフィックスインデックス(部分インデックス)を利用する。プレフィックスの長さには最頻出値を洗い出す必要がある。
- プレフィックスインデックスはORDER BYやGROUP BYクエリに使用する事ができない。
- クラスタ化インデックスをサポートするのはsolidDBとInnoDBだけである。B-Treeインデックスと行をまとめて同じ構造に格納する。隣接するキーを持つ行が近くに格納されることを意味する。InnoDBでは主キーでクラスタ化する。
- クラスタ化インデックスは関連データを近くに配置、データアクセスが高速、カバリングインデックスを使用するクエリはリーフノードに含まれている主キーの値を使用する事などの利点がある。その反面、データがメモリに収まる場合はクラスタ化はそれほど効果的ではない、挿入速度は挿入の順序に大きく依存する、行更新のコストがかかる、行挿入や手キーの変更によりページ分割の対象となる、行密度が低かったり行が不連続である場合はフルスキャンになる、セカンダリインデックスが大きくなる場合がある、セカンダリインデックスへのアクセスは検索が２回必要などの問題がある。
- MyISAMとInnoDBではデータのレイアウトが異なる。詳細は後から説明。
- InnoDBの主キーはシーケンシャルなので１つ前のレコードの直後に格納される。InnoDBでは新しい主キーが前の主キーよりも大きいとは限らないので、新たに追加する行を配置できる場所を探す。そしてその領域確保のために余分な処理が増えてデータレイアウトも最適なものとは言えない状態になる。
- ランダムな値をクラスタ化インデックスに読み込んだ後にOPTIMIZE TABLEを実行して再構築し、ページがデータとして最適にみたされた状態にする必要がある。
- InnoDBを使用する際には主キーの順序でデータを挿入するようにすべき。また新しい行ごとに規則的に増加するクラスタ化キーを使用すべき。
- ワークロードの平行性が高い場合InnoDBでは主キー順序での挿入がSPOF(Single Point Of Failure)を生み出す可能性がある。ただワークロードにうまく適用できるような調整がかのうかもしれない。
- カバリングインデックスとはQueryを処理するために必要なデータをすべて含んでいるようなインデックスのこと。つまりデータを読み取る事無くインデックスの読み込みだけで全てが解決するインデックスの事。
- カバリングインデックスの利点としては、インデックスサイズはデータサイズより小さいのでアクセスしやすい/メモリに収まりやすい、データキャッシュよりもインデックスをキャッシュする方が良い。
- InnoDBでは特にカバリングインデックスが有効。InnoDBではセカンダリインデックスがリーフノードに主キーを格納できる。
- カバリングインデックスは全てのストレージエンジンでサポートされていない。
- インデックスカバークエリを実行するとEXPLAINのExtra列にUisng Indexが表示される。
- テーブルの全ての列をカバーするインデックスは存在しない。WHERE句はインデックスがカバーする列にのみ言及するためMySQLはインデックスを使ってアクセスをする事ができる。
- インデックスでLIKE演算する事ができない。インデックス演算は単純な比較しかしない。
- InnoDBのセカンダリインデックスにはQueryをカバーするために使用できる「追加の列」があることを意味する。
- MySqlの順序付き結果にはファイルソートとインデックスソートの2種類方法がある。EXPLAINのtype列でindexをスキャンするタイミングが分かる。
- 行のソートと検索に同じインデックスを使用するよう設計するのが望ましい。インデックスで順番付けがうまくいくのはインデックスの順番がORDER BY句と全く同じで全ての列が同じ方向にソートされている場合だけ。複数のテーブルを結合する場合はORDER BY句の全ての列が１つ目のテーブルを参照する場合のみである。ORDER BY句にはインデックスの左端のプレフィックスを形成している必要がある。その他全ての状況ではMySQLはファイルソートを行う。ORDER BY句がインデックスの左端のプレフィックスをし指定しなて良いケースは先頭の列に対する定数が存在するとき。WHERE句またはJOIN句がそれらの列の定数を指定する場合はインデックスの穴埋めを行う。
- MyISAMは圧縮されたインデックスを利用してメモリに収まるインデックスを増やしパフォーマンスを劇的に改善可能かもしれない。デフォルトでは文字列を圧縮するが整数値を圧縮することもできる。
- 圧縮されたMyISAMのインデックスブロックはサイズは小さくなるが特定の操作で失速させる。圧縮プレフィックスの値は前の値に寄って決まる。MyISAMはブロックから目的の値を検出するためには２分探索を実行する事ができず先頭からブロックスキャンをする。シーケンシャルな前方スキャンはうまくできるが、後方スキャン(ORDER BY DESCなど)はうまくいかない。ブロックの途中で１行のレコードを検出しなければならない操作は全て平均でブロックの半分をスキャンしなければならない。
- テーブルのインデックス圧縮の指定はPACK_KEYSのオプションで制御可能。
- 同じ列で複数のインデックスを作成できる。重複インデックスは別々に管理する必要があり、オプティマイザはQueryを最適化する時にそれぞれのインデックスを考慮する。重複インデックスは出来る限り作成されないように細心の注意が必要。またインデックスが冗長インデックスとならないようにもすべき。冗長インデックスは新しいインデックスを追加する時に発生する。例えば途中からインデックスを張り直して単一列でのインデックスが増えたとしてもそれぞれのインデックスを残しておくべきである。
- 新しいインデックスの追加はINSERT/UPDEATE/DELETEの操作パフォーマンスに影響がでる。
- InnoDBはインデックスを使用する場合であっても実際には必要のない行をロックする可能性がある。行の検索とロックにインデックスを使用する事ができない場合はフルテーブルスキャンを実行し全ての行をロックする。InnoDBはセカンダリインデックスに共有ロックを配置できるが排他ロックは主キーへのアクセスを要求するためカバリングインデックスを使用する可能性は排除され、SELECT FOR UPDATEがLOCK IN SHARE MODEや非ロッククエリよりも遥かに低速になる可能性がある。
インデックスのケーススタディ
- インデックスを使用する上で最初に決定しなければならないのはインデックススペースのソートを使用するのかファイルソートを容認するかである。インデックスベースのソートはインデックスとQeury構築方法を制限する。Queryの範囲条件にインデックスを使用する場合、順序の指定に別のインデックスを使用することはできない。
- 識別可能な値とWHERE句で最も使用する列を調べる。識別可能な値を持つ列ではインデックスの選択性(カーディナリティ度)が高い。
- 選択性が非常に高い列にインデックスを付けても無駄というのが今までの見方。インデックスを作成する場合は全ての選択性を考慮すべき。
- 結果が限定的なQueryが実行されたとしてもWHERE句にIN構文を追加するとインデックスが使用される。列の識別可能な値が多いとINのリストが大きくなるのでうまくいかない。
- インデックスの数が増えすぎることも良くないのでインデックスを再利用する事にして条件の組み合わせが多くならないのであるとすれば上のINの手法が利用できる。
- テーブルの中であまり使用されず一般的ではない選択肢に対するインデックスを設計するには単に省略してMySQLに余分にスキャンさせれば良い。
- MySQLが値の範囲を指定する最初の条件まで左端のプレフィックスだけを利用するのでインデックスの最後に指定すると良い。できるだけ多くのインデックスをMySQLに使用させる。
- インデックスにより多くの列を追加してそれらの列がWHERE句の一部でないケースをINにしてカバーする事は可能だがやりすぎは禁物。オプティマイザが評価しなければならない組み合わせが増えて結果的にパフォーマンスを低下させる。Queryの実行よりも最適化のほうに時間がかかるだけでなくメモリを大量に使用する可能性があった。
- 2つ以上の範囲条件指定( BETWEENや大なり小なり等)がある場合MySQLのインデックスは両方を使用できない。その場合は範囲の一つを等価比較にできるようテーブル設計を見直す等。
- 復号インデックスを複数用意するのがQuery実行の観点からは効率が良いと思えるが、全てを管理するオーバーヘッドとそれに必要な余分な領域を考えるとベストな方法であるとは言えない。これはオプティマイザの変更が最適なインデックス戦略に影響を及ぼしかねないケースである。MySQLの将来のバージョンで本物のルーズインデックススキャンを実行できるようになれば1つのインデックスで複数の範囲条件が指定できるはずなのでINリストを使用することはなくなるだろう。
- ソートの最適はとしてはカバリングインデックスを使用し最終的に取得する行の主キー列だけを取得するのも効果的な方法。結果を再びテーブルに結合すれば行の全てを取得する事ができる。これによりMySQLが結局は捨ててしまうデータを収集するために必要な作業が最小限で済む。
インデックスとテーブル管理
- ハードウェアやMySQL/オペレーティングシステムのバグが原因でインデックスの破損に直面する可能性がある。インデックスがは損するQueryは正しい結果を返さなくなり、重複した値が無いにも関わらずキーの重複エラーが発生しロックアップやクラッシュを引き起こす。おかしな状態が出たらCHECK TABLEを実行して破損が無いかを確認する。破損したテーブルはREPAIR TABLEコマンドで修正ができるが全てのストレージエンジンでサポートされていない。その場合は「何もしない」ALTER コマンドを実行する事ができる。myisamchkといったストレージエンジン固有の修復Utilityを使用するかデータをダンプして読み戻すことができる。行データの部分で破損が発生している場合はテーブルバックアップから復元するか、破損したデータからの復元をしなければならない。
- オプティマイザはインデックスの使用方法を決定するために2つのAPI呼び出しを使用する。1つはrecords_in_rangeで入力として範囲の先頭と末尾を受け取り、その範囲に含まれているレコードの数を返す。もう一つはinfo呼び出しでありインデックスの濃度を含め、様々なデータを返す事ができる。行に関する情報がインデックスからオプティマイザに渡されない場合オプティマイザはANALYZE TABLEを使ってインデックス統計を再生成する。オプティマイザのコストはQueryがアクセスするデータ量で決まる。統計が生成されていないまたは無効である場合は、オプティマイザが謝った判断を下す可能性がある。解決策はANALYZE TABLEを利用すること。
- Memoryストレージエンジンはインデックスの統計データを格納しない、MyISAMストレージエンジンは統計データをディスク上に格納しANALYZE TABLEは濃度計算のためにフルインデックススキャンを実行し、そのプロセス間の間テーブル全体がロックされる。InnoDBでは統計データをディスク上に格納せずに最初にテーブルが開かれた特にランダムなインデックスダイプでそれらを予測する。InnoDBの統計データは正確さに欠けるかもしれないが、手動で更新する必要も無い。InnoDBのANALAYZE TABLEはノンブロッキングであり、比較的コストがかからない。
- SHOW INDEX FROMコマンドで得られるCardinality列はストレージエンジンが予測した識別可能な値がインデックスにいくつ含まれているかを示す。
- B-Treeインデックスが断片化してパフォーマンスを低下させる可能性がある。B-Treeは元々リーフにアクセスするためにはランダムなディスクアクセスが必要となる規則である。しかしリーフページが物理的に連続していればパフォーマンスが向上できる。テーブルのデータ記憶域も断片化する可能性がありインデックスの断片化よりも複雑。データの断片化には行の断片化と行内の断片化の2種類がある。MyISAMでは2種類の断片化が発生する可能性があるが、InnoDBでは短い行が断片化することはない。データのデフラグを実行するにはOPTIMIZE TABLEを実行するかデータをダンプして読み戻す。MyISAMではインデックスをソート順で作成するアルゴリズムでインデックスを再構築することでデフラグを実行できる。InnoDBではインデックスをデフラグする方法はない。InnoDBではインデックスを削除し再構築したとしてもデータによってはインデックスが断片化する可能性がある。そのような場合は何もしないALTER TABLEでテーブルを再定義することができる。
正規化と非正規化
- 正規化されたデータベースではファクトはそれぞれ一回出現する。非正規化のデータベースでは情報が重複してしまう。
- パフォーマンス問題の解決策の一つとしてスキーマの正規化は良いアドバイスとなる。理由は正規化された更新は非正規化よりも高速、正規化されていればデータの重複がほとんどないため変更するデータが少なくなる。正規化されたテーブルの方がデータが小さいためメモリにうまく収まる。冗長データがないのでリストを取得する時にDISTINCTまたはGROUP BYのQueryを実行する必要があまり無い。
- 正規化の欠点はテーブルの結合が発生すること。これにより一部のインデックスの仕様が不可能になるケースがある。
- 非正規化されたスキーマの定義は全てが同じテーブルに含まれていて結合が不要になる。テーブル結合が発生しない場合のQueryにとっての最悪なケースはフルテーブルスキャンであり、これはランダムなI/Oを回避するためデータがメモリ内に収まらない場合の結合よりもパフォーマンスが良い可能性がある。また単一なテーブルではより効率的なインデックスの使い方が可能。
- 非正規化の問題は結合であり1つのインデックスでソートとフィルタリングを同時に行う事ができない。
- 正規化/非正規化のスキーマにそれぞれ長所/短所が存在する。
- 非正規化する最も一般的な方法は1つのテーブルから選択された列を別のテーブルに複製するかキャッシュすることである。MySQL5.0以降ではキャッシュされた値の更新にトリガを使用できるので実装が容易。非正規化の場合はデータの更新箇所が増える可能性があるので、データの更新時間とSELECTの実行時間を照らし合わせての判断が必要。
- 親テーブルから子テーブルにデータの移動をするもう一つの理由はソートである。正規化されたテーブルではソートにコストが掛かるが、別テーブルにカラムをキャッシュしそれに対してインデックスを付けるとソートの効率が良くなる。
- キャッシュテーブルとはスキーマから容易にデータを取得できるデータが含まれたテーブル。サマリーテーブルとはGROUP BY Queryからの集計データが含まれたテーブルという意味で使用。これらは取得するデータが多少古くても良い事が許容な場合に利用される。
- メインテーブルにInnoDB、キャッシュテーブルにMyISAMを利用するとインデックスのサイズが小さくなり全文検索を実行できるようになる。
- キャッシュテーブルとサマリテーブルの使用はリアルタイム性を管理するかどうかの判断による。定期的な再構築でも良い方法である場合はこれらのテーブルを利用するとリソース節約だけでなく、断片化が無くソートされたインデックスを持ちより効率的。これらのテーブルを再構築している間に利用するデータがある場合はシャドウテーブルを利用すると良い。シャドウテーブルは一度現在参照しているテーブル(table)のコピーを作成(table_newなど)し、作成したテーブル(table_new)に対して新しいデータの再構築を行う。構築がおわった時点で参照テーブルにリネームさせる。その時に参照テーブルのバックアップを取るとよい。(table_bak)
- カウントを記録するようなテーブルの場合、カラムを+1などの管理で考えると平行性問題にあたることがある。カラムを更新する際のトランザクション全て直列化してしまう。これを回避するためには行を複数用意しておいてランダムなカラムを更新するようにすると平行性が保てる。
ALTER TABLEの高速化
- メモリが不足していてテーブルやインデックスサイズが大きい場合はALTER TABLEに時間がかかってしまう。これを改善しようと各ストレージエンジンで改善の取り組みが行われている。
- MODIFY COLUMNはテーブルの再構築を引き起こすがALTER COLUMNは.frmファイルを変更しテーブルには手をつけないため高速である。
- テーブルの再構築をせずに行える操作はAUTO_INCREMENT属性の削除(追加は行えない)、ENUM定数とSET定数の追加、削除、変更。基本的な手法としては適切なテーブルを持つ.frmファイルを作成し、既存の.frmがある場所にコピーすること。
- 1.目的の変更を除いて全く同じレイアウトで空のテーブルを作成する。
- 2.FLUSH TABLES WITH READ LOCKを実行して使用中の全てのテーブルを閉じ、テーブルが開かれないようにする。
- 3..frmファイルを交換。
- 4.UNLOCK TABLESを実行して読み取りロックを解除する。
- MyISAMのインデックスを素早く構築するにはキーの有効/無効を使う事。DISABLE KEYS ENABLE KEYS。これがうまく行くのは全てのデータが読み込まれるまでMyISAMにインデックスの構築を先送りさせ、その時点でのインデックスをソートに基づいて構築させるため。これによりデフラグされたコンパクトなインデックスを得る事ができる。DISABLE KEYSは一意でないインデックスにのみ適用される。MyISAMは一意なインデックスをメモリ内で構築し、行を読み込む時に確認する。インデックスのサイズがメモリ容量を超えた時点で一気にパフォーマンスが低下する。
- MyISAMのインデックス再構築手順は以下で示す。
- 1.テーブルのみ作成。
- 2.データをテーブルに読み込み.MYDファイルを構築。
- 3.適切な構造を持つ別テーブルを空の状態で作成、インデックスを追加する。これで必要な.frm、.MYIファイルが作成される。
- 4.読み取りロックを使う。
- 5.2つ目のテーブルの.frmファイルと.MYIファイルの名前を変更し1つ目のテーブルの代わりに使用させる。
- 6.読み取りロックを解除。
- 7.REPAIR TABLEを使ってテーブルのインデックスを構築させる。
ストレージエンジンに関する注意点
- MyISAMストレージエンジン
- テーブルレベルのロックが発生
- クラッシュに対する自動データリカバリ機能が欠如
- トランザクションの欠如と一つの文が完了した事を保証しない
- メモリ内でのインデックスのみキャッシュ
- データがコンパクトになる。行は順番にパックされるためディスク消費量が少なくなる。
- Memoryストレージエンジン
- MyISAMと同様にテーブルロックが発生する。
- 可変長なデータ型をサポートしない。BLOB/TEXT型を全くサポートしない。VARCHARもCHARに変換。
- インデックスがハッシュインデックス
- インデックス統計の欠如
- ディスクを使用しないため再起動したときにコンテンツが消失する。
- InnoDB
- 4つの分離レベルのトランザクションをサポートする。
- 外部キーをサポートする唯一の標準ストレージエンジン。
- 行レベルのロックでエスカレーションやノンブロッキング選択は無い。高い平行性が保てる。
- マルチバージョンの平行性を制御するので古いデータが読み取られる可能性がある。マニュアルをよく読む必要がある。
- InnoDBでは主キーによるクラスタ化ができる。
- インデックスを主キーで参照するため主キーを短く保つ事。
- データとメモリの両方をバッファプールにキャッシュする。行の取得を高速化するためにハッシュインデックスを自動で作成する。
- インデックスが圧縮されないので大きくなる。
- インデックスをソートに基づいて構築するのではなく行ごとである。データ読み込みがかなり時間かかることがある。
- 新しいAUTO_INCREMENT値を生成する度にテーブルロックを必要とする。
- テーブル内の行の数を保持しない。よってCOUNT(*)Queryを最適化することは不可能で、フルテーブルスキャンやフルインデックススキャンが必要になる。
