MyISAMストレージエンジンの使用時に、MySQL では非常に高速のテーブルロック（複数リーダ/単一ライタ）が使用されます。 このテーブル型の最大の問題は、同じテーブルに対して複数の UPDATE と遅い SELECT が混在する場合に発生します。テーブルでこのような問題が発生した場合は、別のテーブル型を使用してもかまいません。 章?13. ストレージエンジンとテーブルタイプを参照してください。

MySQL はトランザクションテーブル、非トランザクションテーブルの両方で機能します。非トランザクションテーブル（何らかのエラー発生した場合にロールバックができない）での動作をスムーズにするため、MySQL には次のルールがあります。 このルールが適用されるのは正確にSQLモードで作動しているとき、あるいはIGNOREスペシファイヤをINSERTもしくはUPDATEに使用しているときのみです。

SQLのサーバモードの設定をすることで、さらに制御されたデータの扱いを有効にします。このことにより、以前の動作反応を変更することができます。この詳細については、項1.8.6. 「MySQL における制約の処理」、項4.2.6. 「SQL モード」、そして項12.2.4. 「INSERT 構文」を参照してください。

SQL サーバは SQL のさまざまな部分を実装しているので、移植可能な SQL アプリケーションの作成が可能となります。非常に単純な SELECT や INSERT は容易ですが、必要なことが増えれば増えるほど、作成が難しくなります。多数のデータベースを使用しながら素早い速度が要求されるアプリケーションの場合は、さらに難度が上がります。

どのデータベースシステムにも欠点はあります。それはつまり、データベースのすべてに何らかの弱点があります。言い換えると、動作の相違を招くさまざまな設計上の障害があります。

複雑なアプリケーションを移植可能にするには、ともに稼動する必要のある SQL サーバ数を選択する必要があります。MySQL crash-meプログラムを使用すると、データベースサーバの選択に使用できる関数、データ型、制約を調べることができます。現在の crash-meは可能なことすべてのテストを実行できるとは決して言えませんが、約 450 項目のテストが幅広く行われています。 たとえば、crash-meが提供する情報の例として、Infomix や DB2 の使用を可能にするには、18 文字を超えるカラム名は使用できません、といったものがあります。

MySQL ベンチマークと crash-meプログラムはいずれもデータベースへの依存度が非常に低くなっています。これらのプログラムがどのように処理されているかを調べることによって、データベースに依存しないアプリケーションを作成する際に必要なことに関する感覚を得ることができます。ベンチマーク自体は、MySQL ソースディストリビューションの sql-benchディレクトリにあります。 これらはＰｅｒｌで書かれ、ＤＢＩデータベースインターフェースを使用する。DBI の使用それ自体で移植性の問題の1部分を解決できます。これはデータベースから独立したアクセス方法をとるからです。項6.1.4. 「MySQL ベンチマークスィート」を参照してください。

データベースの独立性の獲得を目指す場合は、SQL サーバそれぞれのボトルネックを正しく理解する必要があります。MySQL では、非常に高速にMyISAMレコード（テーブルや行）の取り出しと更新が行われますが、1 つのテーブル上に低速のリーダとライタが混在することに問題があります。異なり、Oracle では、更新直後のレコードがディスクに保存される前にそのレコードにアクセスしようとする際に大きな問題があります。一般にトランザクションデータベースの場合、ログテーブルからのサマリテーブルの生成時は行ロックがほとんど役に立たず、問題が生じやすくなっています。

アプリケーションを実際にデータベース非依存にするには、データ操作に使用する簡単な拡張可能インタフェースを定義する必要があります。 例えば、C++ほとんどのシステムでは C++ が使用できるため、データベースに C++ クラスインタフェースを使用することは道理にかなっています。

あるデータベースに固有の機能を使用する場合（MySQL の REPLACEコマンドなど）は、他の SQL サーバでその機能を実装できるようにするメソッドをコード化する必要があります（ただし低速化します）。低速化しますが、他のサーバで同じ作業を実行できるようにします。

MySQL を使用すると、/*! */構文を使用して MySQL 固有のキーワードをクエリに追加できます。/* */内のコードは、その他の SQL サーバのほとんどでコメントとして処理（無視）されます。コメントの挿入に関する情報は、項8.5. 「コメント構文」を参照してください。

一部の Web アプリケーションのように正確性よりパフォーマンスが重視される場合は、すべての結果をキャッシュするアプリケーションレイヤを作成すると、さらにパフォーマンスを改善できます。一定の期間後に古い結果を '期限切れ' することで、キャッシュを適度に最新の状態に保持できます。これにより、キャッシュを動的に拡大し、通常の状況に戻るまでタイムアウト期限を高速に設定して、高負荷のスパイクを処理するメソッドが提供されます。

この場合、テーブル作成情報にキャッシュの初期サイズと通常時にテーブルがリフレッシュされる頻度に関する情報が組み込まれます。

アプリケーションを実行する代わりにMySQLクエリのキャッシュを使用するのは、効果的です。クエリキャッシュを有効にすることで、クエリ結果が再利用できるかどうかの判断の詳細をサーバに一任します。これによりユーザのアプリケーションは単純化します。項4.13. 「MySQL クエリ キャッシュ」を参照してください。

このセクションではMySQL開発当初のアプリケーションを説明します。

MySQL の初期開発当時は、最大顧客に合わせて MySQL の機能が開発されてきました。この機能は、スウェーデンの最大小売商数社向けにデータウェアハウスを処理するものです。

すべての店舗からボーナスカード取引すべてのサマリを毎週取得し、店舗の所有者が顧客に対する広告キャンペーンの効果を調べる際に役立つ情報を提供するように求められています。

このデータは非常に大量（1 か月に約 700 万のサマリ取引）で、ユーザへの提示に必要な 4?10 年間のデータを保有しています。 このデータから新しいレポートに '即時' アクセスできるようにしたいという顧客からの要求が毎週ありました。

1 か月ごとにすべての情報を圧縮 「トランザクション' テーブル」に格納することでこの要求を解決しました。トランザクションテーブルからさまざまな基準（製品グループ、顧客 ID、店舗など）によって分類されたサマリテーブルを生成する単純なマクロ（スクリプト）セットを開発しています。レポートは Web ページ形式で、Web ページを解析し、SQL ステートメントを実行して、結果を挿入する、短い Perl スクリプトから動的に生成されます。mod_perlの使用のほうが適しているとも言えますが、その当時は利用できませんでした。

グラフィカルデータについては、SQL クエリの結果（この結果に処理を加えて）から GIF を生成する簡単なツールを C で作成しました。これも HTML ファイルを解析する Perl スクリプトから動的に実行されます。

ほとんどの場合、既存のスクリプトをコピーし、その SQL クエリを修正することで新規のレポートを簡単に実行することができます。状況によっては、既存のサマリテーブルにフィールドを追加したり、新規のテーブルを生成することが必要な場合もありますが、これもディスク上にすべてのトランザクションテーブルを保存しているため非常に容易なことです。（現在、少なくとも 50 G のトランザクションテーブルとその他の 200 G の顧客データを保持しています）。

顧客は、ODBC によってサマリテーブルに直接アクセスすることができ、上級ユーザであれば各自でデータを処理することができます。

非常に適度な規模の Sun Ultra SPARCstation（2×200 Mhz）を使用した処理では何も問題が発生していません。徐々にシステムはＬｉｎｕｘに移植されていきました。

このベンチマークスィートは、SQL 実装のパフォーマンスを向上または低下させる操作をユーザに示すことを目的としています。MySQL ソースディストリビューションの sql-benchディレクトリにあるコードと結果を確認することでベンチマークに関するヒントが得られます。

このベンチマークはシングルスレッドであるため、実行される操作の最短時間が測定されていることに注意してください。将来はこのベンチマークスィートにマルチスレッドのテストも多数追加する予定です。

ベンチマークスィートを使用するには、以下の要件を満たす必要があります。

MySQLソースディストリビューション入手後、ベンチマークスィートは、MySQL ソースディストリビューションの sql-benchディレクトリにあります。 ベンチマークテストを実行するには、ロケーションをそのディレクトリsql-benchに変更し、run-all-testsスクリプトを実行します。

shell> cd sql-bench
shell> perl run-all-tests --server=server_name

server_name はサポートされるサーバの 1 つを表します。すべてのオプションとサポート対象サーバの一覧を取得するには、このコマンドを呼び出してください。

shell> perl run-all-tests --help

sql-benchディレクトリ内にcrash-meがあります。 crash-meでは、データベースがサポートする機能と、実際のクエリを実行した場合の機能と制約の判定が試行されます。 たとえば、以下についての判定が行われます。

確実にアプリケーションとデータベースのベンチマークを行い、ボトルネックを検出しておく必要があります。これを修正（または、ボトルネックを 「dummy」に置換）することによって、次のボトルネック（など）の確認が容易になります。現在のアプリケーションの総合的なパフォーマンスが許容できるものであっても、実際にパフォーマンスの強化が迫られる場合に備えて、少なくともボトルネックそれぞれに対して計画を立て解決方法を判定しておく必要があります。

移植可能なベンチマークプログラムの例として、MySQL ベンチマークスィートを取り上げます。詳しくは項6.1.4. 「MySQL ベンチマークスィート」を参照してください。このスィートから任意のプログラムを選び、必要に合わせて修正することができます。これによって、それぞれの問題に対して複数の解決方法を試行して、実際に最も高速が得られるのはどれであるかについてテストすることができます。

これ以外の無料のベンチマークスィートに Open Source Database Benchmark があり、これは http://osdb.sourceforge.net/で入手できます。

一般的には、システムの負荷が非常に高い状況にのみ問題が発生します。負荷の問題が（テスト済の）本稼動のシステムで発生したと問い合わせてくる顧客が多数いました。ほとんどの場合、パフォーマンスに関わる問題は基本的な設計上の問題（高負荷時のテーブルスキャンの不良）かオペレーティングシステムやライブラリの問題が原因だと判明しています。たいていは、システムがまだ本稼動に入っていない場合のほうが問題の修正がはるかに容易です。

このような問題を回避するには、想定可能な最悪の負荷でアプリケーション全体のベンチマークにある程度力を注ぐ必要があります。

その名（Smack = 打ちこわし）のとおり、システムに限界まで負荷をかけることができるため、必ず開発システムでのみ使用するようにしてください。

EXPLAIN tbl_name

または

EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options

EXPLAINステートメントはDESCRIBEのシノニムとして使用するか、MySQLがどのようにSELECT ステートメントを実行するかの情報が得られます。

このセクションでは、クエリ実行情報を得るためのEXPLAINの2つめの使用方法を記述します。DESCRIBEとSHOW COLUMNSステートメントの詳細については、項12.3.1. 「DESCRIBE 構文」と項12.5.4.4. 「SHOW COLUMNS 構文」を参照してください。

EXPLAINを利用すると、より速くレコードを検索する SELECTを得るために、どの時テーブルにインデックスを追加しなければならないかを確認できます。 また、EXPLAINを使用して、オプティマイザがテーブルを最適な順序で結合しているかどうかも確認することができます。オプティマイザが特定の順番で結合を行うように強制するにはただSELECTでステートメントをはじめるのではく、SELECTステートメントに SELECT STRAIGHT_JOIN節を追加します。

最適化方法の選択に影響を及ぼすキーの、カーディナリティなどのテーブル統計を更新するために、ANALYZE TABLEを定期的に実行する必要があります。 項12.5.2.1. 「ANALYZE TABLE 構文」を参照してください。

EXPLAINはSELECTステートメントで使用される各テーブルに関する情報を返します。テーブルは、読み取られた順序に従って一覧表示されます。MySQL は、単一スイープ多結合メソッドを使用してすべての結合を解決します。これは、MySQL が最初のテーブルからレコードを読み取ってから、第 2 のテーブル、第 3 のテーブルといった順序で、一致するレコードの検索を行うことを意味します。すべてのテーブルの処理が終わると、選択したカラムと、さらに一致レコードがあるテーブルが検索されるまでのテーブル一覧のバックトラックが出力されます。次のレコードはこのテーブルから読み取られ、処理が次のテーブルから続行されます。

EXTENDEDキーワードが使用された時、EXPLAINはSHOW WARNINGSステートメントをEXPLAINステートメントの後で発行することで閲覧できる余分な情報を表示する。この情報は、SELECTステートメント内でオプティマイザがどのようにテーブル名とカラム名を認証するか、SELECTが再書き込みと最適化ルールの適用後どのように表示されるか、そして最適化プロセスの他の注意点なども表示します。EXPLAIN EXTENDEDはMySQL 5.1.12以降、filteredカラムも表示します。

注:EXTENDED とPARTITIONS キーワードを、同じEXPLAINステートメントで使用することはできません。

EXPLAINの各出力行は１つのテーブルの情報を提供し、各行は以下のカラムを含んでいます。

EXPLAIN出力の rowsカラムのすべての値を掛け算することで、結合がどの程度適しているかを示す指針を取得できます。Thisこれは、クエリの実行時に MySQL で調べる必要があるレコード数の概要を示します。この数値は、max_join_size変数でクエリを制限する際にも使用される他、どのマルチテーブルSELECTステートメントを実行するか、あるいはアボートするかを判別します。項6.5.2. 「サーバパラメータのチューニング」を参照してください。

下記の例は、EXPLAINによって得られた情報を使用して、マルチテーブルjoinを累進的に最適化する方法を示しています。

ここでは、EXPLAINを使用して、SELECTステートメントを調べるとします。

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

この例では以下のように想定しています。

当初、最適化の実行前は、EXPLAINステートメントで次の情報が生成されました。

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

各テーブルで typeが ALL であるため、この出力は MySQL がすべてのテーブルのデカルト積を生成すると示しています。各テーブルのレコードの数の積の分量を調べる必要があるため、これは非常に時間がかかります。この例の場合は、レコードの数が 74 ×2135 ×74 ×3872 = 45,268,558,720 になります。テーブルがこれより大きい場合は、さらに時間がかかると考えられます。

ここでの問題の 1 つは、宣言の方法が異なると MySQL でカラムのインデックスを効率的に使用できないことにあります。この例では、VARCHARと CHARが異なる長さで宣言されていなければ同じになります。tt.ActualPCが CHAR(10)として、et.EMPLOYIDが CHAR(15)として宣言されているため、長さの不一致が発生します。

カラムの長さの不一致を修正するため、ALTER TABLEを使用して ActualPCを 10 文字から 15 文字にします。

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

これで tt.ActualPCと et.EMPLOYIDはいずれも VARCHAR(15)になりました。 ここでまた EXPLAINを実行してみると、以下の結果が得られました。

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

これも完全ではありませんが、かなり改善されています（rows値の積が 74 の係数分だけ減少）。このバージョンの場合実行に数秒かかります。

第 2 の変更を加えると、tt.AssignedPC = et_1.EMPLOYIDと tt.ClientID = do.CUSTNMBRの比較でのカラム長の不一致を解消できます。

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

ここでは、EXPLAINから以下の出力が生成されます。

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

これでほとんど改善されています。 残りの問題は、MySQL ではデフォルトで tt.ActualPCカラムの値の分布が均一であると想定されますが、ttテーブルはこれにあてはまらないことです。これは容易に MySQL に示すことができます。

mysql> ANALYZE TABLE tt;

この追加インデックス情報で、結合が完全になり、EXPLAINで以下の結果が生成されます。

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAINの出力の rowsカラムは、MySQL 結合オプティマイザの学習による推測であることに注意してください。クエリを最適化するには、この数値が実際に近いものであるかどうかを確認するためにrowsのプロダクトとクエリが実際に返す行の数をを比較する必要があります。実際とかけ離れている場合は、SELECTステートメントで STRAIGHT_JOINを使用し、FROM節でテーブルの順序を変えて一覧表示してみるとパフォーマンスを改善できます。

MySQL Enterprise MySQL ネットワーク監視とアドバイスサービス加入者は定期的にプロから最適化のアドバイスを提供されます。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

ほとんどの場合、ディスクシークをカウントしてパフォーマンスを推定できます。 小さいテーブルの場合は一般に 1 つのディスクシークでレコードを検索できます（インデックスがキャッシュされることが多いため）。大きいテーブルの場合の推定では、（B++ ツリーインデックスを使用して）log(row_count) / log(index_block_length / 3 × 2 / (index_length + data_pointer_length)) + 1のシークがレコードの検索に必要になります。 .

MySQL では、インデックスブロックが通常 1,024 バイトで、データポインタは通常 4 バイトです。インデックスの長さが 3（MEDIUMINT）の 500,000 レコードのテーブルの場合は以下のようになります。 log(500,000)/log(1024/3×2/(3+4)) + 1 = 4シーク )

上のインデックスでは約 500,000 × 7 ×3/2 = 5.2M が必要になるため（一般的な状況としてインデックスバッファの 2/3 が使用されていると想定）、メモリにインデックスの多くがあり、OS からデータを読み取り、レコードを検索するには、1 回か 2 回の呼び出しで済むと推定されます。

ただし、書き込みについては、上記の例で新規インデックスの配置場所を探し出すのに 4 シークの要求が、また、インデックスの更新とレコードの書き込みに通常 2 シークが必要になります。

Note that このことは、アプリケーションが対数 Nの分だけ低速になるという意味ではないことに注意してください。OS または SQL サーバですべてがキャッシュされている限り、テーブルが拡大しても速度の低下はわずかです。データがキャッシュできないほど増加すると、ディスクシーク（対数 NN の分だけ増加する）によって最終的にアプリケーションがバインドされるまで大幅に速度の低下が始まります。)これを回避するには、データの増加に合わせてインデックスキャッシュも拡大します。 MyISAMテーブルに関しては、キーキャッシュサイズはkey_buffer_sizeシステム変数に制御されます。項6.5.2. 「サーバパラメータのチューニング」を参照してください。

一般に、低速の SELECT ... WHEREの速度を上げる必要がある場合は、まず、インデックスを追加できるかどうかをチェックします。一般に複数のテーブル間の参照はすべてインデックスを使用して実行する必要があります。EXPLAINコマンドを使用して、SELECTに使用されるインデックスを判定できます。 項6.2.1. 「EXPLAINを使用して、クエリを最適化する」 と　項6.4.5. 「MySQLにおけるインデックスの使用」を参照してください。

MyISAMテーブルのクエリ速度を上げる一般的なヒント。

このセクションではWHERE節をプロセスする際の最適化方法を記述します。WHERE の最適化は、ほとんどの場合 SELECTとともに使用されるため、SELECT部分に適用されますがDELETEや UPDATE のステートメントの WHEREにも同じ最適化が適用されます。

また、このセクションは完全なものではないため、注意が必要です。MySQL は多様な最適化を実行するため、すべてを文書化するには時間が足りませんでした。

MySQL によって実行される最適化の一部をここに紹介します

非常に高速なクエリのサンプルをいくつか紹介します。

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

MySQLは次のクエリで、インデックスツリーのみを使用して解決します（インデックスのあるカラムが数値型であると想定）。

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

次のクエリは、ソートのパスを分けることなく、ソートしたレコードを取り出すためにインデックスを使用します。

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

rangeアクセスメソッドはシングルインデックスを使用して、１つもしくは複数のインデックス値インターバルに含まれるテーブル行のサブセットを取得します。シングルパートかマルチパートインデックスに使用できます。以下のセクションではWHERE 節からどのようにしてインターバルが抽出されるかの詳細説明を記述します。

SELECT * FROM t1
  WHERE key_col > 1 
  AND key_col < 10;

SELECT * FROM t1 
  WHERE key_col = 1 
  OR key_col IN (15,18,20);

SELECT * FROM t1 
  WHERE key_col LIKE 'ab%' 
  OR key_col BETWEEN 'bar' AND 'foo';

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

Index Mergeメソッドは、複数のrangeスキャンを有する行を取得と、それぞれの結果を１つに結合するのにするのに使用されます。この結合によって、基礎スキャンの結合、共通集合、あるいは交差点の結合が生成されます。

EXPLAIN出力では、インデックスメソッドはtype カラムでindex_mergeとして現れます。この場合、keyカラムは使用されたインデックスのリストが含まれ、key_lenはインデックスの最長キー部分が含まれます。

例：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key=30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1=t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1=1
  AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

インデックス結合メソッドは複数のアクセスアルゴリズムがあります。(ExtraフィールドのEXPLAIN出力で見られます。)

以下のセクションはこれらのメソッドの詳細を記述しています。

注:インデックス結合最適化アルゴリズムには以下の欠点があります。

種々のインデックス結合メソッドや他のアクセスメソッドの選択に関しては、選択肢のコスト予想によります。

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name
  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

MySQL では、col_name = constant_value.の場合と同じ最適化を col_name IS NULLに対しても実行できます。 たとえば、MySQL では、インデックスと範囲を使用して、IS NULLで NULLを検索できます。

例：

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

WHERE節内でcol_name IS NULLで定義されたものをNOT NULLと使用する場合、その式は消去して最適化されます。この最適化は、結果的にカラムがNULLを生成する場合には生じません。たとえば、LEFT JOINの右側のテーブルからきている場合。

MySQL はcol_name = expr AND col_name IS NULLの組み合わせを最適化する機能が追加されています。これは解決されたサブクエリではよくあるフォームです。この最適化が使用される場合は、EXPLAIN は ref_or_nullを表示します。

この最適化は、すべてのキー部分で IS NULLを 1 つ処理できます。

最適されたクエリのサンプルをいくつか紹介します（t2のキーを（a,b）とします）。

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_nullはまずリファレンスキーの読み取りを行い、その後 NULLキー値のあるレコードの別検索を実行します。

この最適化では、1 つの IS NULLレベルしか処理できないことに注意が必要です。 以下のクエリではMySQL は (t1.a=t2.a AND t2.a IS NULL)の部分に対してキーのルックアップを実行するのみで、bのキー部分は使用できません。

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

DISTINCTが ORDER BYと組み合わされて用いられると、多くの場合はテンポラリテーブルが必要になります。

DISTINCTは GROUP BYをともなう可能性が高いので、選択されないカラムをORDER BYまたは HAVINGした時に、どのように MySQL が機能するかを認識しておく必要があります。 項11.11.3. 「非常時フィールドとの GROUP BY および HAVING」を参照してください。

ほとんどの場合、 DISTINCT節はGROUP BYの特殊ケースと考えられます。例えば、下記の2クエリは等価です

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

等価であることによって、GROUP BYクエリに適用できる最適化はDISTINCT節のあるクエリにも適用できます。さらなるDISTINCTクエリ最適化の可能性については、項6.2.13. 「GROUP BY最適化」を参照してください。

LIMIT row_countを DISTINCTとともに使用した場合、MySQL は一意のレコードを row_count行検索するとただちに検索を停止します。

使用するテーブル内のカラムを使用しない場合、MySQL は最初にマッチするレコードを検索するとただちに未使用テーブルのスキャンを停止します。 ここでは、t1が t2の前に使用され（EXPLAINによるチェック）、t2で最初のレコードが検索されると t2からの読み取り（t1の特定のレコード）を停止します。

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

MySQL の AB join_conditionは以下のように実装されます。

RIGHT JOINの実装は LEFT JOINと類似しています。

結合オプティマイザがテーブルの結合順序を計算します。テーブル読み取り順序は LEFT JOINと STRAIGHT_JOINによって強制されるため、チェック対象のテーブル順列が減少し、結合オプティマイザ（テーブルの結合順序を計算する）の動作の速度がさらに上がります。 LEFT JOINが dの前に読み取るように強制するため、MySQL では bの完全スキャンが実行されることに注目してください。

SELECT *
  FROM a JOIN b LEFT JOIN c ON (c.key=a.key) 
  LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

この場合の修正は逆さに行われ、aとbがFROM節でリストされています。

SELECT *
  FROM b JOIN a LEFT JOIN c ON (c.key=a.key) 
  LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

LEFT JOINにとって、生成された NULL行で WHERE条件が常にfalseである場合、LEFT JOINは通常の結合に変更されます。 たとえば、t2.column1カラムが NULLであるとすると、以下のクエリの WHERE節は falseになります：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

よって、通常の結合に変換しても問題ありません。

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

これでクエリが改善できる場合、MySQL がテーブル t1を読み取る前にテーブル t2を使用できるようになるためスピードが向上します。テーブルの順序を指定して強制する場合は STRAIGHT_JOINを使用します。

結合を表す構文は入れ子結合を許可します。以下は項12.2.7.1. 「JOIN 構文」で記述された結合構文に関連します。

table_factor構文はSQL標準と比較して拡張されています。後者はtable_referenceのみ受付、かっこ内のリストは受け付けません。これは、table_referenceアイテムのリスト内の点（、）が内部結合と等価とする場合、この拡張は控えめです。例 :

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

は以下と等価です。

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

MySQLでは、CROSS JOINはINNER JOINと構文上等価です (置き換え可能です)。標準SQL上等価ではありません。INNER JOINはON節と一緒に使用されます。CROSS JOINは他の使用方法があります。

一般的に、inner joinオペレーションを含むjoin 表現のかっこは無視できます。かっこを取り除きグルーピングオペレーションを左に移動させた後、join 表現は：

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
   ON t1.a=t2.a

以下の表現に変換されます。

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
    ON t2.b=t3.b OR t2.b IS NULL

しかし、二つの表現は等価ではありません。たとえば、t1、t2、そしてt3が以下の状態であるとします。

この場合、最初の表現は(1,1,101,101)、そして(2,NULL,NULL,NULL)を含む行の結果セットを返します。2番目の表現は(1,1,101,101)、(2,NULL,NULL,101)を含む行を返します。

mysql> SELECT *
    -> FROM t1
    ->      LEFT JOIN
    ->      (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
    ->      ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
    -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
    ->      LEFT JOIN t3
    ->      ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

下記の例では、外側　join オペレーションが内側　join オペレーションと一緒に使用されます。

t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

その表現は以下の表現に変換できません。

t1 LEFT JOIN t2 ON t1.a=t2.a, t3.

既存のテーブル状態では、以下の2表現は異なる行セットを返します。

mysql> SELECT *
    -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
    -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

よって、外側　join　演算子を含む　join 表現のかっこを取り除いた場合、元の表現の結果セットを変える可能性があります。

正確には、左外側 join オペレーションの右演算子のかっこを、そして右側　join オペレーションの左演算子のかっこを無視することができません。言い換えれば、外側　join オペレーションの内側テーブル表現のかっこを無視することはできません。他のオペランド（外側テーブルのオペランド）のかっこは無視できます。

以下の表現：

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

はこの表現と等価です：

t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)

テーブルt1,t2,t3と条件P属性t2.b and t3.b.

join 表現(join_table) のjoin　オペレーション実行順序が左から右でない場合、入れ子 join　の話が出てきます。以下のクエリを考慮してください。

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

上記クエリは以下の入れ子　join が含まれると考えられています。

t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3

最初のクエリでは、左　join　オペレーショを使用して入れ子　join　が生成されます。二番目のクエリでは内側 join オペレーションで生成されます。

最初のクエリでは、かっこは取り除いてもかまいません。join 表現の文法構成はjoin オペレーションと同じ実行順序を指令します。2番目のクエリでは、かっこなしでも　join 表現があいまいに解釈されますが、かっこは取り除くことができません。(拡張された構文では、理論上はかっこなしでもパースされますが、2番目クエリの(t2, t3)のかっこは必要です。まだ、クエリはあいまいでない構文構成になります。これはLEFT JOINとONが左と右のデリミタの役割りを右の表現(t2,t3)で果たすからです。）

前述の例でこれらの点を証明しています。

入れ子外側joins を含むクエリは内側join を含むクエリと同じように、パイプライン形式で実行されます。正確には、入れ子ループjoinアルゴリズムが利用されます。入れ子ループjoinがクエリを実行する際利用するアルゴリズムスキーマを思い出してください。例えば、3つのテーブルT1,T2,T3に関するjoinクエリが、以下のフォームであるとします。

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  WHERE P(T1,T2,T3).

ここでは、 P1(T1,T2)とP2(T3,T3)はjoin　条件です（表現につく）。それに引き換え、P(t1,t2,t3)はテーブルT1,T2,T3カラム上の条件です。

入れ子ループjoinアルゴリズムはこのクエリを次のように実行します。

FOR each row t1 in T1 {
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

t1||t2||t3を使用した表記法は「t1、t2、そしてt3行を含むカラムを連鎖させることで作成された行」を意味します。以下の例では、行の名前があらわれる箇所にNULLとある場合、NULLはその行の各カラムに使用されることを意味します。例えば、t1||t2||NULLは「t3の各カラム毎のt1 、t2、そしてNULL行のカラムを連鎖させることで作成された行。」

入れ子のある外側join クエリを見てみましょう。

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON P2(T2,T3))
              ON P1(T1,T2)
  WHERE P(T1,T2,T3).

このクエリでは、入れ子ループパターンを改良することで以下を取得します。

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

一般的に、外側 joinオペレーションの最初の内側テーブル入れ子ループにとって、ループ前に消され、ループ後にチェックされるフラグが導入されます。フラグがオンになるのは、内側オペランドを表すテーブルから外側テーブルの現在行にマッチが見つかったときです。ループサイクルの最後でフラグがＯＦＦの場合は、外側テーブルの現在行でマッチが見つからなかったときです。この場合、行がインナーテーブルのカラムNULL値で補われています。結果行は次の入れ子ループか出力へ、最終確認のため渡されますが、これは行が組み込まれた全ての外側 joinの条件を満たしている場合のみです。

この例では、次の表現で表された外側joinテーブルは組み込まれています。

(T2 LEFT JOIN T3 ON P2(T2,T3))

内側joinを含むクエリにとって、オプティマイザは以下のような異なる順序の入れ子ループが選択できることに注目してください。

FOR each row t3 in T3 {
  FOR each row t2 in T2 such that P2(t2,t3) {
    FOR each row t1 in T1 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

外側テーブルを含むクエリに関しては、オプティマイザは外側テーブルのループが内側テーブルのループの前にくる順序のみ選択可能です。よって、外側joinのクエリにとって、1つの入れ子順序のみ可能となります。以下のクエリでは、2つの異なる入れ子を評価します。

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

以下が入れ子です。

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t1,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

そして

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t3 in T3 such that P2(t1,t3) {
    FOR each row t2 in T2 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

両の入れ子にとって、T1は外側joinで使用されているため、外側ループでプロセスされなければいけません。 T2とT3は内側joinで使用されているため、そのjoinは内側ループで処理されなければいけません。ただし、joinが内側joinのため、T2とT3はどちらの順序でも処理できます。

内側joinの入れ子ループアルゴリズムについては、クエリ実行性能に関する、重大な詳細を省きました。いわゆる、「後入れ先出し」条件に関することには触れませんでした。例えば、WHERE条件P(T1,T2,T3)が接続法によって表されるとします。

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).

この場合、MySQLは内側joinを含むクエリの実行には以下の入れ子ループスキーマを使用します。

FOR each row t1 in T1 such that C1(t1) {
  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {
    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

ここで、書く接続詞C1(T1)、C2(T2)、C3(T3)は評価が可能なよう、最も内側にあるループから最も外側にあるループまで押し出されます。もしC1(T1)が制限力の高い条件である場合、この条件の後入れ先出しはテーブルT1から内側ループに渡される行の数を大幅に減らします。結果的に、クエリの実行時間が大きく短縮できます。

外側joinを含むクエリについては、外側テーブルの現在行に内側テーブルからのマッチがあることが確認できてからWHERE条件がチェックされます。よって、内側入れ子ループの後だし先入れ条件最適化は外側joinを含むクエリには直接適用できません。ここではマッチが見つかった時に起動するフラグに守られた、条件つき後出し先入れの述語を紹介しなければいけません。

例えば、外側joinでは

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

ガードされた後だし先入れ条件を使用した入れ子ループスキーマは以下のようになります。

FOR each row t1 in T1 such that C1(t1) {
  BOOL f1:=FALSE;
  FOR each row t2 in T2
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1 && P(t1,NULL,NULL)) {
      t:=t1||NULL||NULL; OUTPUT t;
  }
}

一般的に、後だし先入れ述語はP1(T1,T2)やP(T2,T3)といったjoin条件から抽出できます。この場合、後だし先入れ述語は対応する外側joinオペレーションNULL-に補われた行のチェックを妨げるフラグによって守られています。

ここで、1つの内側テーブルから同じ入れ子joinへのアクセスキーは、WHERE条件からの述語に誘導されている場合、禁止されています。(この場合条件つきのキーアクセスを使用することはできますが、ＭｙＳＱＬではこのテクニックはまだ使われていません5.1。)

クエリのFROM節内テーブル表現は多くの場合単純化されています。

パーサ段階で、右外側joinオペレーションを含むクエリは左joinオペレーションを含む等価のクエリに変換されます。一般的には、変換は以下のルールに従って実行されます。

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)

T1 INNER JOIN T2 ON P(T1,T2)フォームの全ての内側join表現は T1,T2、P(T1,T2)結合されたWHERE条件によって置き換えられます。(あるいは、組み込まれたjoinのjoin条件が存在する場合は、それに置き換えられます)。

オプティマイザが外側joinオペレーションのjoinクエリ計画を評価する際、各オペレーション時、外側テーブルが内側テーブルより前にアクセスされます。そのようなプランは、入れ子ループスキーマによる外側joinオペレーションを含むクエリの実行のみ可能なため、オプティマイザ選択肢は制限されています。

例えば、以下のようなフォームのクエリがあるとします。

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

R(T2)がテーブルT2からマッチする行を大幅に狭めます。クエリをそのまま実行した場合、オプティマイザはT1より前にT2をアクセスする以外に選択肢が与えられず、非常に非効率的な実行プランになることがあります。

幸い、MySQL WHERE条件がnull-rejectedの場合、そのようなクエリを外側joinオペレーションを含まないクエリに変換します。もし外側join オペレーションがNULL-によって補われたオペレーションのために作成された行のFALSEまたはUNKNOWNに評価される場合は、null-rejectedと呼ばれます。

よって、この外側joinでは：

T1 LEFT JOIN T2 ON T1.A=T2.A

以下のような条件はnull-rejectedです:

T2.B IS NOT NULL,
T2.B > 3,
T2.C <= T1.C,
T2.B < 2 OR T2.C > 1

以下のような条件はnull-rejectedではありません:

T2.B IS NULL,
T1.B < 3 OR T2.B IS NOT NULL,
T1.B < 3 OR T2.B > 3

条件が外側joinオペレーションにとってnull-rejectedか否かを確認する一般的なルールは単純です。以下の場合、条件はnull-rejectedになります。

条件は1つクエリでの外側joinオペレーションでnull-rejectedとなり、他のクエリでnot null-rejected になりえます。以下のクエリでは：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

WHERE条件は、2つ目の外側joinオペレーションではnull-rejectedであり、1つ目の外側joinオペレーションではnot null-rejected です。

もしWHERE条件がクエリの外側joinオペレーションでnull-rejected である場合、外側joinオペレーションは内側joinオペレーションに置き換えられます。

例えば、前のクエリは以下のクエリに置き換えられます。

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

オリジナルクエリには、1つのアクセス順序T1,T2,T3と対応するプランをオプティマイザが評価します。クエリを置き換える場合は、T3,T1,T2アクセスシーケンスを追加で考慮します。

1つの外側joinオペレーションの変換は別のオペレーションの変換を引き起こす場合があります。よって、以下のクエリでは：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

は最初にこのクエリに変換されます。

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

それはこのクエリと等価です。

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

条件がT3.B=T2.Bnull-rejected であることと、外側joinを含まないクエリを取得したため、残る外側joinオペレーションは内側joinに置き換えることができます。

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

時折、組み込まれた外側joinオペレーションを置き換えることができても、組み込まれた外側joinが変換できない場合があります。以下のクエリでは：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

は以下変換されます。

SELECT * FROM T1 LEFT JOIN
              (T2 INNER JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0,

それは組み込まれた外側joinオペレーションを含むフォームにのみ書き換えることができます。

SELECT * FROM T1 LEFT JOIN
              (T2,T3)
              ON (T2.A=T1.A AND T3.B=T2.B)
  WHERE T3.C > 0.

組み込まれた外側joinオペレーションをクエリに変換する場合、WHERE条件とともに組み込まれた外側joinのjoin条件を考慮しなければいけません。以下のクエリでは：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A AND T3.C=T1.C
  WHERE T3.D > 0 OR T1.D > 0

WHERE は組み込まれた外側joinではnot null-rejected ですが、組み込まれた外側joinT2.A=T1.A AND T3.C=T1.Cのjoin条件はnull-rejectedになります。よって、クエリは以下に変換されます。

SELECT * FROM T1 LEFT JOIN
              (T2, T3)
              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
  WHERE T3.D > 0 OR T1.D > 0

余分なソートを行わずに ORDER BYの要求に応じるために、MySQL はインデックスを使用する場合があります。

全ての使用されていないインデックス部分と他の部分が WHERE節内で定数であるカラムである場合、ORDER BYがインデックスに完全にマッチしない場合でもこのインデックスを使用できます。次のクエリではインデックスを使用して ORDER BY部分を解決します。

SELECT * FROM t1 
  ORDER BY key_part1,key_part2,... ;
    
SELECT * FROM t1 
  WHERE key_part1=constant 
  ORDER BY key_part2;
    
SELECT * FROM t1 
  ORDER BY key_part1 DESC, key_part2 DESC;
    
SELECT * FROM t1
  WHERE key_part1=1 
  ORDER BY key_part1 DESC, key_part2 DESC;

MySQL で ORDER BYの解決にインデックスを使用できない場合は以下のとおりです（この場合も MySQL は WHERE節の条件に一致するレコードの検索にインデックスを使用します）。

EXPLAIN SELECT ... ORDER BYを使用すると、MySQL でインデックスを使用してクエリを解決できるかどうかをチェックできます。Extraカラムに Using filesortが出力された場合は、MySQL で ORDER BY の解決にインデックスを使用できません。 項6.2.1. 「EXPLAINを使用して、クエリを最適化する」を参照してください。

ソートキー値と行ポジションだけでなく、クエリに必要なカラムまで記憶するfilesort最適化が使用されます。これにより行の2度読みを避けられます。filesortアルゴリズムは以下のように実行されます。

MySQLの旧バージョンで使用されていたアルゴリズムよりも格段に改良されています。

遅滞を避けるため、この最適化はmax_length_for_sort_dataシステム変数の値をソートタプル内の余分なカラムのトータルサイズが超えない場合使用されます。(この変数が高く設定されると、活発なディスクアク活動に対して低いＣＰＵ活動といった状態が発生します。)

ORDER BY速度を上げたい場合、MySQLが余分な並び替えフレーズよりもインデックスを使用できるか確認してください。これが不可能な場合、以下の手段を試してみてください。

ＭｙＳＱＬはデフォルトで、GROUP BY col1、col2, ...の全クエリをORDER BY col1、col2, ...で指定したかのように、クエリをソートします。同じカラムリストを含むORDER BY節を明示的に取り入れた場合、ソートが実行されるとはいえ、ＭｙＳＱＬは速度ペナルティなしに最適化します。クエリにGROUP BYが含まれていながら、結果のソートに費やすオーバーヘッドを避けたい場合、ORDER BY NULLを指定することでソートを実行しないようにすることができます。例 :

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

GROUP BY節を満たす最も一般的な方法は、テーブル全体をスキャンし、各グループの全ての行が連続する新しいテンポラリテーブルを作成することです。それにより、このテンポラリテーブルを使用してグループの発見や集約ファンクション（の適用が可能になります。場合により、ＭｙＳＱＬはインデックスアクセスを使用することでテンポラリテーブルの作成を回避することが可能です。

GROUP BYインデックスを使用するための最も重要な前提条件は、全てのGROUP BYカラムは同じインデックスから属性を紹介することと、そしてインデックスがキーを正しい順序で保存することです。 (例えば、BTREEはインデックスであり、HASHインデックスではありません)。テンポラリテーブルの使用がインデックスアクセスに置き換えることが可能かは、クエリ内でどのインデックス部分が使用されるか、その部分を指定する条件、そして選択された集約ファンクションにもよります。

インデックスアクセスを通してGROUP BYクエリを実行するには以下に記述された、2つの方法があります。最初の方法では、グルーピングオペレーションは全てのレンジ前提(これが有る場合に限り)と共に適用されます。2つ目の方法は、まずレンジスキャンを実行し、その後結果タプルをグループします。

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

HAVINGを使用するのではなく LIMIT row_countを使用している場合、MySQL によるクエリの処理方法が異なる場合があります。

ＭｙＳＱＬがクエリを解決するのにテーブルスキャンを使用すると、EXPLAINからの出力は、typeカラムのALLを表示します。これはたいてい以下の条件下で生じます。

小さいテーブルに対しては、テーブルスキャンはたいてい適切であり、実行の際の影響は無視されます。大きいテーブルに対しては、オプティマイザが間違ったテーブルスキャンを選択しないように、以下の方法を試してください。

行挿入の時間は、以下の要因によって決定されます。（数はおよその割合を示します。）

テーブルを開く初期オーバヘッドは算入されていません（これは同時実行クエリのそれぞれで 1 回実行されます）。

テーブルのサイズによって対数Nの分だけインデックス挿入の速度が低下します（B ツリー）。

挿入の速度を上げる方法

MySQL Enterprise サーバのパフォーマンスを最適化するための詳しいアドバイスについては、MySQL Network Monitoring and Advisory Serviceを購読してください。多数のアドバイザーがパフォーマンス向上をサポートします。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

更新ステートメントは、SELECTクエリと同様に最適化されますが、書き込みオーバヘッドが加算されます。書き込みの速度は更新対象のデータのサイズおよび更新対象のインデックス数によって異なります。変更がないインデックスは更新されません。

更新の速度を上げるもう 1 つの方法は、更新を遅延して 1 行で多数の更新を後から行うことです。1 行での多数の更新は、テーブルをロックすると同時に行う場合と比較して大幅に高速に実行できます。

可変長テーブルMyISAMの場合は、合計の長さが今よりも長いものに行を更新すると、行が分割される場合があることに注意します。このため、頻繁にこれを実行する場合は、ときどき OPTIMIZE TABLEすることが重要になります。項12.5.2.5. 「OPTIMIZE TABLE 構文」を参照してください。

行の削除に要する時間は、完全にインデックス数に比例します。行削除の速度を上げるには、key_buffer_sizeシステム変数を上げて、キーキャッシュのサイズを拡大します。項6.5.2. 「サーバパラメータのチューニング」を参照してください。

テーブル内のすべての行を削除する場合は、TRUNCATE TABLE tbl_nameのほうがDELETE FROM tbl_nameを使用するより高速です。項12.2.9. 「TRUNCATE 構文」を参照してください。

このセクションでは、クエリ処理高速化のためのヒントを挙げます。

MySQL はMyISAM とMEMORY テーブルにはテーブルレベルロックを使用し、InnoDBテーブルには行レベルロックを使用します。

ほとんどの場合、どのロックタイプがアプリケーションに適しているか推察することが可能ですが、一概にどのロックタイプが優れているかを判断するのは困難です。全てはアプリケーションに依存しており、かつアプリケーションの部分毎に異なるロック型があります。

行レベルロックで保存エンジンを使用するか判断する場合、ユーザはアプリケーションの役割と、使用されているselect と　updateステートメントを確認する必要があります。例えば、大抵のＷｅｂアプリケーションは多くの選択を実行し、相対的に削除はほとんど行わず、主にキー値にもとづいた更新を行い、かつ特定のテーブルに挿入します。ベースMySQL MyISAMセットアップはよくチューニングされています。

MySQL Enterprise MySQL Network Monitoring and Advisory Service はテーブルレベルでの使用時および行レベルロックでの使用時について専門的なアドバイスを提供しています。購読を希望する場合は、http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

ＭｙＳＱＬのテーブルロックは、テーブルレベルロックを使用するストレージエンジンに対して、デッドロックフリーです。デッドロックは、クエリの開始時に、同時に必要とされる全てのロックを常に要求すること、そして同じ順序のテーブルを常にロックすることで回避されます。

MySQLでのテーブルロックメソッドはWRITEロックを用いて以下のように機能します。

MySQLでのテーブルロックメソッドはREADロックを用いて以下のように機能します。

ロックが開放されるとき、writeロックキューのスレッドに対してロックは有効であり、それから read ロックキューのスレッドに対しても有効です。これはテーブルの更新を頻繁に行う場合、SELECTステートメントは更新が行われなくなるまで待機することを意味します。

Table_locks_waitedおよびTable_locks_immediateステータス変数をチェックすることでシステム上でテーブルロック競合を分析できます。

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

MyISAMテーブルが中心に空きブロックを持たない場合、行は必ずデータファイルの後部に挿入される。この場合、併発するINSERTおよびSELECTステートメントをロックなしでMyISAMテーブルに対して自由に混合して使用できます。つまり、他のクライアントが読むのと同時にMyISAM テーブルに行を挿入できます。テーブルの途中で行を更新もしくは削除した場合欠落が生じます。欠落がある場合、同時挿入はできませんが、全ての欠落が新しいデータで満たされた場合は、自動的に再使用可能となります。

この機能はconcurrent_insertsシステム変数によって変更されます。項6.3.3. 「同時挿入」を参照してください。

テーブルに対して多数の INSERTおよび SELECT操作を行う必要がある場合、このような待機を回避するには、テンポラリテーブルに行を挿入し、一定の間隔でテンポラリテーブルからの行で実テーブルを更新します。 これは以下のコードで実行できます。

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDBは行ロックを使用する。InnoDBでは、ＳＱＬステートメントのトランザクションの初めではなく、プロセス中に自動的に入手するため、デッドロックが可能です。

行レベルロックの利点

行レベルロックの欠点

以下の場合、行レベルロックに対してテーブルロックが優勢になります。

高レベルロックで、異なるタイプのロックをサポートすることによって、より簡単にアプリケーションのチューニングが行えます。というのも、ロックオーバーヘッドは低レベルロックに対して少ないからです。

行レベルロック以外のオプション

高速なロックスピードを実現するため、MySQL InnoDB以外の全てのテーブルロックを全てのストレージエンジンに使用します。

InnoDBテーブルの場合は、MySQL でLOCK TABLESによって明示的テーブルをロックした場合のみテーブルロックが使用されます。 InnoDBは自動行レベルロックを使用してトランザクションの独立を確実にするため、これらのテーブル型には、LOCK TABLESをまったく使用しないように推奨します。

大きいテーブルには、ほとんどのアプリケーションでテーブルロックの方が行ロックより適切ですが、落とし穴もあります。

ただし、テーブルロックは以下のシナリオには適していません。

以下のアイテムはテーブルロックによる競合を軽減または回避する方法を記述します。

以下はMySQLにおけるテーブルロックについてのヒントです。

MyISAM テーブルでは、テーブルに削除された行がない場合、SELECTの実行と同時に行を挿入できることに注意してください。

上記がデフォルト作動で、concurrent_insertsシステム変数で制御できます。1に設定された場合、削除された行を含むMyISAMテーブルで同時挿入が起こります。2に設定された場合、削除された行があっても、全ての新しい行がテーブル後部に付加されることで同時挿入が強制的に行われます。???を参照してください。

同時挿入が使用できる状況下では、INSERTステートメントのDELAYED修飾子を使用する必要はほとんどありません。項12.2.4.2. 「INSERT DELAYED 構文」を参照してください。

バイナリログを使用している場合、同時挿入はCREATE ... SELECTもしくはINSERT ... SELECTステートメントの一般的な挿入に変換されます。このことで、バックアップ演算中にログを適用することでテーブルの正確なコピーを再作成することができます。

LOAD DATA INFILEで同時挿入（つまり、途中に空きブロックを含まない）の条件を満たすMyISAMテーブルを使用してCONCURRENTを指定する場合、他のスレッドはLOAD DATA実行中にテーブルからデータを取得することができます。このオプションを使用することで、たとえ他のスレッドが同時にテーブルを使用しているとしても、LOAD DATAのパフォーマンスに幾分かの影響が与えられます。

MySQLはローデータとインデックスデータを別のファイルに格納します。その他のデータベースの多く（ほとんど）は、ローデータとインデックスデータが同じファイルに混在しています。現在の非常に多くのシステムで MySQL の選択のほうが優れていると確信しています。

行データの格納方法には、各カラムの情報を独立した領域に格納する方法もあります（例: SDBM、Focus など）。これは、複数のカラムにアクセスするすべてのクエリでパフォーマンスに影響を及ぼします。パフォーマンスは複数のコラムへのアクセスを開始するとただちに低速化するため、このようなモデルは汎用データベースには適さないと確信しています。

一般的にインデックスとデータが一緒に格納されている場合も多くあります（Oracle、Sybase などの場合）。この場合は、レコード情報をインデックスのリーフページで検索します。このレイアウトで優れている点は、多くの場合インデックスのキャッシュ方法次第でディスクの読み取りを節約できることにあります。このレイアウトの欠点は以下のとおりです。

最も基本的な最適化の 1 つにデータ（およびインデックス）が占めるディスク領域を可能な限り少なくすることがあります。これで、ディスクの読み取りが高速化し、使用メモリも一般に減少するため、大幅な改善が図れます。カラムが小さければインデックス作成で消費されるリソースも少なくなります。

MySQL では多様なテーブル型とレコード形式がサポートされます。 適切なテーブル形式を選択することで、パフォーマンスを大幅に改善できます。章?13. ストレージエンジンとテーブルタイプを参照してください。

ここで紹介する技法を使用すると、テーブルのパフォーマンス改善とストレージ領域の最小化を図ることができます。

MySQL の全てのカラム型にはインデックスを張ることができます。SELECT操作のパフォーマンスの改善には、対応するカラムにインデックスを使用することが最善の方法です。

テーブルあたりの最大インデックス数とインデックスの最大長は、ストレージエンジンごとに定義されます。章?13. ストレージエンジンとテーブルタイプを参照してください。ストレージエンジンのすべてで、1 テーブルあたり 16 以上のインデックスと 256 バイト以上のインデックス長がサポートされます。大抵のストレージエンジンでは、インデックス最大長がより高く設定されています。

インデックス指定でcol_name(N)構文を用いて、文字列カラムの最初のNキャラクタのみを使用したインデックスを作成できます。このようにカラム値のプレフィックスのみをインデックス化すると、インデックスファイルをかなり小さくすることができます。BLOBもしくはTEXTカラムにインデックスを張る場合、インデックスに対してプレフィックス長を指定しなければなりません。例 :

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

プレフィックスは、最大1000バイト長(InnoDBテーブルに対しては767バイト)まで可能です。プレフィックスの最大長はバイトで評価されます。一方、CREATE TABLEステートメント内のプレフィックス長は文字数として解釈されます。プレフィックス長を、マルチバイトキャラクタセットを使用するカラムに対して指定するときこのことを考慮に入れなければいけません。.

FULLTEXTインデックスの作成も可能です。これらはフルテキスト検索に使用されます。FULLTEXTインデックスをサポートするのはMyISAMストレージエンジンだけで、CHAR、VARCHAR、そしてTEXTカラムについてのみサポートされます。インデックスの作成は常にカラム全体を対象として、先頭部分（プリフィックス）のインデックス化は行われません。詳細については、項11.7. 「全文検索関数」をご参照ください。

空間データ型上でインデックスの作成もできます。現在、MyISAM のみが空間型R-treeインデックスをサポートしています。他のストレージエンジンは空間型のインデックス化にB-treesを使用します。 (ただしARCHIVEやNDBCLUSTERといった空間型インデックスをサポートしないものを除きます)

MEMORYストレージエンジンはデフォルトでHASHインデックスを使用しますが、BTREEインデックスもサポートしています。

MySQLでは複数のカラムに対するインデックスを作成できます。インデックスは最大 15 カラムで構成できます。.特定のデータ型に関しては、カラムのプリフィックスをインデックス上で検索することができます。(項6.4.3. 「カラムインデックス」を参照してください）

複数カラムのインデックス（複合インデックス）は、インデックス化されたカラムの値を連結することによって生成された値が含まれ、ソート化された配列と見なすことができます。

MySQL では、WHERE節内でインデックスの第 1 カラムを指定する場合、他のカラムの値を指定しなくても、クエリが高速化できるように複合インデックスが使用されます。

次のようなテーブルが定義されているとします。

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

ここで、インデックス nameは、last_nameと first_name に対するインデックスです。このインデックスは、last_name の範囲、または last_name と first_nameの両方の範囲の値を指定するクエリに使用できます。 したがって、nameインデックスは次のようなクエリに使用されます。

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

しかし、次のクエリには nameインデックスが使用されません。

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

MySQL でインデックスを使用してクエリパフォーマンスを改善する方法の詳細については、項6.4.5. 「MySQLにおけるインデックスの使用」を参照してください。

インデックスは通常迅速に特定のカラム値の行を検索するのに使用されます。インデックスがない場合、関連する行を検索する場合ＭｙＳＱＬは最初の行から始め全テーブルを読まなけれ場なりません。テーブルが大きいほど、コストがかかります。検索しているカラムのインデックスをテーブルが含んでいる場合、ＭｙＳＱＬは全てのデータを検索せずに、データファイルの途中でシークポジションを迅速に決定します。テーブルに1000行ある場合、連続して読む場合よりも少なくとも100倍の速度で処理が行われます。行のほとんどにアクセスしなければならない場合、連続して読むほうがより高速です。というのも、これはディスクシークの最小化が行われるためです。

ほとんどのＭｙＳＱＬインデックスは(PRIMARY KEY、UNIQUE、INDEX、そしてFULLTEXT) はB-treesに保存されています。R-treesを使用する空間データ型インデックスは例外で、MEMORY テーブルでもハッシュインデックスがサポートされています。

文字列のプリフィックスとエンドスペースは自動的に圧縮されます。項12.1.7. 「CREATE INDEX 構文」を参照してください。

一般的に、インデックスは以下のように使用されます。ハッシュインデックスの特徴は(MEMORYテーブルで使用される) このセクションの終わりに説明されています。

MySQLはこれらの演算についてインデックスを使用します。

例えば次のSELECTステートメントを発行したとします。

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

col1とcol2上で複合カラムインデックスが存在する場合、適当な行は直接取得されます。col1とcol2に別々のシングルカラムインデックスが存在する場合、オプティマイザはどのインデックスがより少ない行を検索するかを決定することで、最も制限力のあるインデックスを検索します。また、そのインデックスを使用して行を取得します。

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。たとえば、(col1, col2, col3)に 3 カラムのインデックスがある場合、(col1)、(col1, col2)そして(col1, col2, col3)に対して、インデックスの検索機能を使用できます。

カラムがインデックスの左端の先頭部分を構成していない場合、MySQL では、部分インデックスを使用できなくなります。以下のSELECTステートメントがあります。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

インデックスが(col1, col2, col3)に存在する場合、最初のクエリだけがインデックスを使用できます。3つめと4つめのクエリには、インデックス化したカラムが必要ですが、(col2)と(col2, col3) は (col1, col2, col3)の左端のプリフィックスではありません。

=, >, >=、<, <=あるいはBETWEEN演算子を使用する表現のカラム比較に、B-treeインデックスが使用可能です。LIKE がワイルドカードキャラクタで始まらない定数文字列の場合、インデックスはLIKE比較にも使用できます。例えば、以下のSELECTステートメントはインデックスを使用します。

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

最初のステートメントでは、'Patrick' <= key_col < 'Patricl'を含む行のみ考慮されます。2つ目のステートメントでは、'Pat' <= key_col < 'Pau'を含む行のみ考慮されます。

以下のSELECTステートメントはインデックスを使用しません。

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

最初のステートメントでは、LIKE値はワイルドカードキャラクタで始まります。2つ目のステートメントでは、LIKE値は定数ではありません。

もし... LIKE '%文字列%'そして文字列は3文字より長い場合、ＭｙＳＱＬはTurbo Boyer-Moore アルゴリズムを使用して、文字列のパターンを初期化してから、このパターンを使用して検索をすばやく実行します。

col_name IS NULLを使用した検索では、col_nameにインデックスが張られている場合にインデックスが使用されます。

WHERE節内の全ての ANDにかかっていないインデックスは、クエリの最適化に使用されません。言い換えると、インデックスの使用を可能にするには、インデックスの先頭部分がすべての ANDグループで使用されている必要があります。

次のWHERE節ではインデックスが使用されます。

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

次の WHERE節ではインデックスが使用されません。

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます（この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります）。 ただしこのクエリに、レコードの一部のみを取り出す LIMITが使用されている場合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを使用します。

ハッシュインデックスは先ほど挙げたインデックスとは特徴が異なります。

MySQL Enterprise どのインデックスが要求されるか、あるいは最も効率的な?実際のテーブル使用率が指標となるか、正確に推測するのは困難です。MySQL Network Monitoring and Advisory Service はこの問題に関する専門的なアドバイスを提供します。詳細は http://www-jp.mysql.com/products/enterprise/advisors.html をご覧ください。

ディスクI/Oを最小化するために、MyISAMストレージエンジンは多数のデータベースマネージメントシステムで使用される戦略を利用します。メモリ内で最も頻繁にアクセスされるテーブルブロックを保持するために、キャッシュメカニズムが使用されます。

このセクションでは最初にMyISAMキーキャッシュの基本的な演算について説明しています。キーキャッシュパフォーマンスを向上させる特徴や、キャッシュオペレーションをよりよくコントロールできる特徴について、説明されています。

キーキャッシュのサイズを制限するには、key_buffer_sizeシステム変数を使用します。この変数がゼロにセットされた場合、利用できるキーキャッシュはありません。key_buffer_size値が小さすぎてブロックバッファの最小値が割り当てられない場合、キーキャッシュも使用できません（8）。

MySQL Enterprise key_buffer_sizeのサイズを最適化するための詳しいアドバイスについては、MySQL Network Monitoring and Advisory Serviceを購読してください。http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

キーキャッシュが作動していない場合、インデックスファイルはオペレーティングシステムによるネーティブファイルシステムバッファのみ使用してアクセスされます。(つまり、テーブルインデックスブロックはテーブルデータブロック利用と同様の方法でアクセスされます）

インデックスブロックはMyISAMインデックスファイルにアクセスする隣接ユニットです。.通常、インデックスブロックのサイズは、インデックスB-treeのノードサイズと等価です。(インデックスはB-tree データ構造を使用してディスク上で表現されます。ツリーの元にあるノードはリーフノードです。リーフノードの上層にあるノードは非リーフノードです。)

キーキャッシュ構造内のブロックバッファは全て同サイズです。このサイズは、テーブルインデックスブロックサイズと比べて、等しい/大きい/小さい場合があります。通常これら二つの値のうち一方は、他方の複合となっています。

あるテーブルインデックスブロックのデータアクセスが必要な場合、サーバはまずキーキャッシュのどれかのブロックバッファ内で利用可能かどうかをまずチェックします。可能である場合、サーバはディスク上ではなく、キーキャッシュのデータにアクセスします。つまり、ディスクからではなく、キャッシュから読むかもしくはキャッシュに書きこみます。でなければ、異なるテーブルインデックスブロックを含むキャッシュブロックバッファを選択し、そのデータを要求されたテーブルインデックスブロックのコピーと置き換えます。新しいインデックスブロックがキャッシュ内におかれるとすぐ、インデックスデータはアクセス可能となります。

たまたま置き換えのために選択されたブロックが改良されていた場合、ブロックは「汚染されたモノ」とみなされます。この場合、置き換えられる前に、内容は元のテーブルインデックスにフラッシュされます。

通常サーバはLRU (Least Recently Used)戦略に従います。置換のためブロックを選択した場合、最近使用した中で最も初期に使用されたインデックスブロックを選択します。この選択を簡単にするために、キーキャッシュモジュールは、使用された全てのブロックの特別なキュー(LRU chain) を保持します。ブロックがアクセスされた場合、キューの最後尾に置かれます。ブロックを置換する必要がある場合、キューの最前部にあるブロックは、最近使用した中で最も初期に使用されたものであり、かつ最初の除去対象となります。

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

mysql> SET GLOBAL keycache1.key_buffer_size=0;

mysql> SET GLOBAL key_buffer_size = 0;

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+

key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache
CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;

ストレージエンジンはオプティマイザに使用されるテーブルの統計を集めます。テーブル統計は値グループに基づき、値グループは同じキープリフィックス値を持つ行のセットです。オプティマイザの目的に関しては、標準グループサイズは重要な統計となります。

MySQLは標準値グループサイズを以下のように使用します。

インデックスの標準値グループサイズが増加する毎に、その2つの目的に関してはインデックスは役に立たなくなります。というのも、ルックアップあたりの標準行数は増加するためです。最適化の目的のためにインデックスが有用であるため、各インデックス値はターゲットとするテーブル内の行の数は少なくなければいけません。既存のインデックス値が大量の行を生成する際、インデックスは役に立たなくなり、ＭｙＳＱＬはそれを使用しなくなります。

標準値グループサイズはテーブルカーディナリティと関連しています。これは値グループの数を指します。SHOW INDEXステートメントはN/Sに基づくカーディナリティ値を表示します。これはNがテーブル内の行数であり、Sが標準値グループサイズとなります。その比率はテーブル内の値グループの概数を生成します。

<=> 比較演算子に基づくjoinに対して、 NULLは他の値と同様に扱われます。 NULL <=> NULL、ちょうどN <=> Nが他のNであるように。

ただし、=演算子に基づくjoinに対して、NULLは非NULL値と異なります。expr1 = expr2はexpr1あるいはexpr2（もしくは両方）がNULLである場合、適切ではありません。このことは、フォームtbl_name.key = exprの比較に対するrefアクセスに影響を与えます。exprの現在値がNULLの場合、ＭｙＳＱＬはテーブルにアクセスしません。というのも、比較は適切でないからです。

=比較に対して、NULL値の数がテーブル上にいくらであっても、関係ありません。最適化のためには、関連する値は非NULL値グループの標準サイズにします。ただし、現在MySQLは標準サイズを収集もしくは使用することを許可しません。

MyISAMテーブルに対して、myisam_stats_methodシステム変数を使用することで、テーブル統計コレクションが管理されます。この変数には2つの可能値があり、これらは以下のとおり異なります。

=よりも<=>を使用するjoinを多くユーザが使用している場合、NULL値は比較では特別ではなく、1つのNULLは他のものと等価です。この場合、nulls_equalは適切な統計メソッドです。

myisam_stats_methodシステム変数はグローバルとセッション値を保持しています。グローバル値の設定はMyISAMテーブルに対するMyISAM統計収集に影響を与えます。 セッション値を設定することで、現在のクライント接続のみに対する統計収集に影響が与えられます。これは、既存のメソッドで他のクライントに影響を与えず、テーブルの統計をセッション値myisam_stats_methodに設定することで再生することが可能です。

テーブル統計を再生するために、以下のメソッドを使用してください。

myisam_stats_methodの使用に関する警告。

mysqladmin statusを実行すると、以下の出力が表示されます。

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

テーブルが 6 つしかない場合に Open tables値が 12 と表示されることに、当惑する場合もあります。

MySQL is MySQL はマルチスレッド化されているため、多数のクライアントが同時に同じものに対してクエリを使用することがあります。2 つのクライアントスレッドで 1 つのファイルに異なるステータスが発生する問題を最小にするため、同時に実行しているスレッドがそれぞれで無関係にテーブルを開きます。これはメモリの消費を増やしますが、一般にパフォーマンスは向上します。MyISAMテーブルの場合は、テーブルを開いたそれぞれのクライアントにデータファイルに対するファイル記述子が必要になります。このテーブル型では、インデックスファイルに対するファイル記述子がすべてのスレッドで共有されます。

table_open_cache、max_connections、および max_tmp_tablesサーバ変数は、サーバが開いた状態で保持できるファイルの最大数に影響します。 これらの値の 1 つ以上を増加すると、OS によって制限されている 1 プロセスが持つことができるファイル記述子の最大数まで実行が可能になります。システムごとに方法は多様ですが、多数のオペレーティングシステムでオープンファイルの制限値を上げることができます。 制限値の拡大が可能かどうかの判定、およびその実行方法については、使用するオペレーティングシステムの文書を参照してください。

table_open_cacheは max_connectionsと関係します。たとえば、同時接続数が 200 の場合、最低 200 × Nのテーブルキャッシュサイズが必要です。この Nは結合で使用するテーブル数の最大値を示します。また、テンポラリテーブルとファイル用のファイル記述子も必要です。

あなたのオペレーティングシステムが table_open_cacheの設定に従ったファイル記述子の数を処理できることを確認してください。table_open_cacheの設定が高すぎると、MySQL がファイル記述子を使い果たして接続を拒否し、クエリの実行ができなくなり、信頼性が大幅に低下します。また、MyISAMストレージエンジンでは1つのテーブルごとに 2 つのファイル記述子が必要であることも考慮に入れる必要があります。--open-files-limitスタートアップオプションを使用すると、mysqldで使用可能なファイル記述子数を拡大できます。 .項B.1.2.17. 「'File' Not Found and Similar Errors」を参照してください。

オープンテーブルのキャッシュは、table_open_cacheエントリレベルに保持されます。デフォルト値は 64 です。これは、--table_open_cacheオプション mysqld に与えることで変更できます。mysqldは一時的にさらに多くのテーブルを開いてクエリの実行を実現することがあります。

MySQL Enterprise table_cache設定が低すぎると、パフォーマンスに悪影響を及ぼします。この変数の最適値に関する詳しいアドバイスについては、MySQL Network Monitoring and Advisory Serviceを購読してください。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

以下の状況では、MySQLは使用されていないテーブルが閉じられ、テーブルキャッシュから削除されます。

テーブルキャッシュが満杯になると、サーバでは以下の手順に従って使用するキャッシュエントリを割り当てます。

キャッシュが一時的に拡張された状況で、使用中のテーブルが使用されなくなったときは、そのテーブルが閉じられ、キャッシュからリリースされる。

テーブルは同時アクセスのそれぞれで開かれます。つまり、2 つのスレッドで同じテーブルにアクセスする場合、または 1 つのスレッドが同一クエリでテーブルに 2 回アクセスする場合（テーブルを同一テーブルに結合する場合など）は、テーブル を 2 回開く必要があることになります。 いずれかのMyISAMテーブルを最初に開く際に2 つのファイル記述子が割り当てられ、その後さらにそのテーブルを使用する場合はファイル記述子が 1 つのみ割り当てられます。最初のオープン時の 2 つめの記述子は、インデックスファイルに使用され、この記述子はすべてのスレッドで共有されます。

HANDLER tbl_name OPENステートメントを使用してテーブルを開く場合は、専用テーブルオブジェクトがスレッドに割り当てられます。 このテーブルオブジェクトは他のスレッドと共有されず、スレッドが HANDLER tbl_name CLOSEを呼び出すか、スレッドが終了するまで閉じられません。この場合はテーブルがテーブルキャッシュに戻されます（キャッシュが満杯でない場合）。 項12.2.3. 「HANDLER 構文」を参照してください。

テーブルキャッシュが小さすぎるかどうかは、mysqldの Opened_tables変数のチェックで確認できます。

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

たとえ多くの FLUSH TABLESを実行していない場合でも、この値が非常に大きい場合は、テーブルキャッシュサイズを拡張する必要があります。 項4.2.3. 「システム変数」、項4.2.5. 「ステータス変数」 を参照して下さい。

ディレクトリにMyISAMテーブルが多数ある場合、オープン、クローズ、および作成の動作が低速になります。多数のテーブルに対して SELECTステートメントを実行した場合、必要なテーブルを開くごとに、他のテーブルを閉じることが必要になるため、テーブルキャッシュが満杯の場合にオーバヘッドが少し発生します。このオーバヘッドは、テーブルキャッシュを拡大することで軽減できます。

システムレベルの要素は、その一部を初期段階に決定する必要があるため、この話から始めます。これに該当しない場合は、システムを大きく変えることが重要でないのであれば、このセクションは簡単に目を通せば十分です。ただし、このレベルで変更を行うことでどの程度改善できるのかを自覚しておくことは必ず役に立ちます。

使用するオペレーティングシステムは非常に重要です。複数 CPU のコンピュータを使用するなら、Solaris（スレッド実装機能が優れている）または Linux（2.2 カーネルの SMP サポートが優れている）が良いでしょう。 また、旧バージョンの Linux カーネルのデフォルトには 2G ファイルサイズの制限があります。このカーネルで 2G より大きいファイルがどうしても必要な場合は、ext2 ファイルシステムの LFS (Large File System）パッチを入手する必要があります。 これ以外の ReiserFS や XFS などには 2G の制限がありません。

MySQL を本番稼働させていないため、可能であれば選択前に候補のプラットフォームのテストを実行することを推奨します。

他のヒント：

mysqldサーバで使用されるデフォルトのバッファサイズは次のコマンドで確認できます。

shell> mysqld --verbose --help

このコマンドによって、mysqldオプションと設定可能な変数すべての一覧が生成されます。この出力には、デフォルトの変数値も記載され、以下のように表示されます。

help                              TRUE
abort-slave-event-count           0
allow-suspicious-udfs             FALSE
auto-increment-increment          1
auto-increment-offset             1
automatic-sp-privileges           TRUE
basedir                           /home/mysql/
bind-address                      (No default value)
character-set-client-handshake    TRUE
character-set-server              latin1
character-sets-dir                /home/mysql/share/mysql/charsets/
chroot                            (No default value)
collation-server                  latin1_swedish_ci
completion-type                   0
concurrent-insert                 1
console                           FALSE
datadir                           /home/mysql/var/
default-character-set             latin1
default-collation                 latin1_swedish_ci
default-time-zone                 (No default value)
disconnect-slave-event-count      0
enable-locking                    FALSE
enable-pstack                     FALSE
engine-condition-pushdown         FALSE
external-locking                  FALSE
gdb                               FALSE
large-pages                       FALSE
init-connect                      (No default value)
init-file                         (No default value)
init-slave                        (No default value)
innodb                            TRUE
innodb_checksums                  TRUE
innodb_data_home_dir              (No default value)
innodb_doublewrite                TRUE
innodb_fast_shutdown              1
innodb_file_per_table             FALSE
innodb_flush_log_at_trx_commit    1
innodb_flush_method               (No default value)
innodb_locks_unsafe_for_binlog    FALSE
innodb_log_arch_dir               (No default value)
innodb_log_group_home_dir         (No default value)
innodb_max_dirty_pages_pct        90
innodb_max_purge_lag              0
innodb_status_file                FALSE
innodb_table_locks                TRUE
innodb_support_xa                 TRUE
isam                              FALSE
language                          /home/mysql/share/mysql/english
local-infile                      TRUE
log                               /home/mysql/var/master1.log
log-bin                           /home/mysql/var/master1
log-bin-index                     (No default value)
log-bin-trust-routine-creators    FALSE
log-error                         /home/mysql/var/master1.err
log-isam                          myisam.log
log-queries-not-using-indexes     FALSE
log-short-format                  FALSE
log-slave-updates                 FALSE
log-slow-admin-statements         FALSE
log-slow-queries                  (No default value)
log-tc                            tc.log
log-tc-size                       24576
log-update                        (No default value)
log-warnings                      1
low-priority-updates              FALSE
master-connect-retry              60
master-host                       (No default value)
master-info-file                  master.info
master-password                   (No default value)
master-port                       3306
master-retry-count                86400
master-ssl                        FALSE
master-ssl-ca                     (No default value)
master-ssl-capath                 (No default value)
master-ssl-cert                   (No default value)
master-ssl-cipher                 (No default value)
master-ssl-key                    (No default value)
master-user                       test
max-binlog-dump-events            0
memlock                           FALSE
myisam-recover                    OFF
ndbcluster                        FALSE
ndb-connectstring                 (No default value)
ndb-mgmd-host                     (No default value)
ndb-nodeid                        0
ndb-autoincrement-prefetch-sz     32
ndb-distibution                   KEYHASH
ndb-force-send                    TRUE
ndb_force_send                    TRUE
ndb-use-exact-count               TRUE
ndb_use_exact_count               TRUE
ndb-shm                           FALSE
ndb-optimized-node-selection      TRUE
ndb-cache-check-time              0
ndb-index-stat-enable             TRUE
ndb-index-stat-cache-entries      32
ndb-index-stat-update-freq        20
new                               FALSE
old-alter-table                   FALSE
old-passwords                     FALSE
old-style-user-limits             FALSE
pid-file                          /home/mysql/var/hostname.pid1
port                              3306
relay-log                         (No default value)
relay-log-index                   (No default value)
relay-log-info-file               relay-log.info
replicate-same-server-id          FALSE
report-host                       (No default value)
report-password                   (No default value)
report-port                       3306
report-user                       (No default value)
rpl-recovery-rank                 0
safe-user-create                  FALSE
secure-auth                       FALSE
server-id                         1
show-slave-auth-info              FALSE
skip-grant-tables                 FALSE
skip-slave-start                  FALSE
slave-load-tmpdir                 /tmp/
socket                            /tmp/mysql.sock
sporadic-binlog-dump-fail         FALSE
sql-mode                          OFF
symbolic-links                    TRUE
tc-heuristic-recover              (No default value)
temp-pool                         TRUE
timed_mutexes                     FALSE
tmpdir                            (No default value)
use-symbolic-links                TRUE
verbose                           TRUE
warnings                          1
back_log                          50
binlog_cache_size                 32768
bulk_insert_buffer_size           8388608
connect_timeout                   5
date_format                       (No default value)
datetime_format                   (No default value)
default_week_format               0
delayed_insert_limit              100
delayed_insert_timeout            300
delayed_queue_size                1000
expire_logs_days                  0
flush_time                        0
ft_max_word_len                   84
ft_min_word_len                   4
ft_query_expansion_limit          20
ft_stopword_file                  (No default value)
group_concat_max_len              1024
innodb_additional_mem_pool_size   1048576
innodb_autoextend_increment       8
innodb_buffer_pool_awe_mem_mb     0
innodb_buffer_pool_size           8388608
innodb_concurrency_tickets        500
innodb_file_io_threads            4
innodb_force_recovery             0
innodb_lock_wait_timeout          50
innodb_log_buffer_size            1048576
innodb_log_file_size              5242880
innodb_log_files_in_group         2
innodb_mirrored_log_groups        1
innodb_open_files                 300
innodb_sync_spin_loops            20
innodb_thread_concurrency         20
innodb_commit_concurrency         0
innodb_thread_sleep_delay         10000
interactive_timeout               28800
join_buffer_size                  131072
key_buffer_size                   8388600
key_cache_age_threshold           300
key_cache_block_size              1024
key_cache_division_limit          100
long_query_time                   10
lower_case_table_names            0
max_allowed_packet                1048576
max_binlog_cache_size             4294967295
max_binlog_size                   1073741824
max_connect_errors                10
max_connections                   100
max_delayed_threads               20
max_error_count                   64
max_heap_table_size               16777216
max_join_size                     4294967295
max_length_for_sort_data          1024
max_relay_log_size                0
max_seeks_for_key                 4294967295
max_sort_length                   1024
max_tmp_tables                    32
max_user_connections              0
max_write_lock_count              4294967295
multi_range_count                 256
myisam_block_size                 1024
myisam_data_pointer_size          6
myisam_max_extra_sort_file_size   2147483648
myisam_max_sort_file_size         2147483647
myisam_repair_threads             1
myisam_sort_buffer_size           8388608
myisam_stats_method               nulls_unequal
net_buffer_length                 16384
net_read_timeout                  30
net_retry_count                   10
net_write_timeout                 60
open_files_limit                  0
optimizer_prune_level             1
optimizer_search_depth            62
preload_buffer_size               32768
query_alloc_block_size            8192
query_cache_limit                 1048576
query_cache_min_res_unit          4096
query_cache_size                  0
query_cache_type                  1
query_cache_wlock_invalidate      FALSE
query_prealloc_size               8192
range_alloc_block_size            2048
read_buffer_size                  131072
read_only                         FALSE
read_rnd_buffer_size              262144
div_precision_increment           4
record_buffer                     131072
relay_log_purge                   TRUE
relay_log_space_limit             0
slave_compressed_protocol         FALSE
slave_net_timeout                 3600
slave_transaction_retries         10
slow_launch_time                  2
sort_buffer_size                  2097144
sync-binlog                       0
sync-frm                          TRUE
sync-replication                  0
sync-replication-slave-id         0
sync-replication-timeout          10
table_open_cache                  64
table_lock_wait_timeout           50
thread_cache_size                 0
thread_concurrency                10
thread_stack                      196608
time_format                       (No default value)
tmp_table_size                    33554432
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
updatable_views_with_limit        1
wait_timeout                      28800

現在実行中の mysqldサーバがある場合は、次のステートメントで変数に実際に使用されている値を調べることができます。

mysql> SHOW VARIABLES;

また、次のステートメントでは、実行中のサーバの統計やステータスインジケータを調べることができます。

mysql> SHOW STATUS;

システム変数とステータス情報は、mysqladminでも入手できます。

shell> mysqladmin variables
shell> mysqladmin extended-status

全てのシステムとステータス変数については、本マニュアルの項4.2.3. 「システム変数」と項4.2.5. 「ステータス変数」を参照してください。

MySQL は非常にスケーラブルなアルゴリズムを使用しているため、通常は実行時のメモリ消費が非常に小さくなります。しかし、MySQL に対するメモリを多く割り当てると、通常はパフォーマンスが向上します。

MySQL サーバをチューニングする際に使用される最も重要な変数は key_buffer_sizeと table_open_cacheの 2 つです。他の変数の変更を行う前にこの変数をあらかじめ適切に設定しておくことで自信がつきます。

以下に典型的な変数を実行時に設定している例を示します。

使用可能メモリより大幅に大きいテーブルで GROUP BY または GROUP BYを実行する場合は read_rnd_buffer_sizeの値を大きくしてソート操作後のレコードの読み取りの速度を上げる必要があります。

ユーザのＭｙＳＱＬディストリビューションで、例オプションファイルを使用する場合は、項3.3.2.1. 「あらかじめ形成されたオプション・ファイル」を参照してください。

mysqldまたは mysqld_safeのコマンドラインでオプションを指定した場合、そのサーバの呼び出しでしか有効性が保持されないことに注意してください。 サーバ実行のたびにオプションを使用する場合は、オプション設定ファイルに配置します。

パラメータ変更の有効性を調べるには、次のように実行します。

shell> mysqld --key_buffer_size=32M --verbose --help

変数値は出力の最後付近で一覧表示されます。--verboseと--helpオプションが残っていることを確認してください。でなければ、コマンドラインでそれらの後に表示されるオプションの効果は出力に反映されません。

InnoDBストレージエンジンのチューニングに関する情報は、項13.5.11. 「InnoDB パフォーマンス チューニング ヒント」を参照してください。

MySQL Enterprise チューニングシステムパラメータに関する専門的なアドバイスを受けるには、MySQL Network Monitoring and Advisory Serviceを購読してください。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

クエリオプティマイザの役割はＳＱＬクエリの実行に際し、最適なプランを検索することです。「良い」と「悪い」プランのパフォーマンス性の違いは、マグニチュードの順序(つまり、秒に対する時間、または日にち)であるため、ＭｙＳＱＬを含む大抵のクエリオプティマイザは全ての評価可能なプランの中から最適なプランを検索します。joinクエリに対して、ＭｙＳＱＬオプティマイザによって確認される可能なプランの数は、クエリで参照されるテーブル数と共に、急激に増加します。少ない数のテーブル(通常から7?10以下)に対して、これは問題とはなりません。 しかし、大きなクエリが提出されたとき、クエリの最適化に要する時間はサーバのパフォーマンスを低下させる主な原因となります。

フレキシブルなクエリ最適化メソッドは、最適なクエリ評価プランを、オプティマイザがどれほど徹底的に検索するかを管理します。基本的には、オプティマイザによって確認されるプランが少なければ少ないほど、クエリをコンパイルするのに要する時間も少なくてすむと考えられています。一方で、オプティマイザはいくつかのプランをスキップするため、最適プランを見逃す可能性もあります。

評価するプラン数に対するオプティマイザの動作は二つのシステム変数を通して管理されます。

以下のテストのほとんどは、MySQL ベンチマークを使用した Linux で実行されていますが、これ以外のオペレーティングシステムおよびワークロードに対しても一定の指針になります。

-staticとリンクした場合に最速のバイナリが得られます。

Linux 上では、pgccおよび -O3でコンパイルした場合に最速のコードが得られます。これらのオプションで sql_yacc.ccをコンパイルする場合は、gcc/pgccで関数のすべてをインラインにする際に大量のメモリが要求されるため約 200M のメモリが必要です。MySQL のコンフィギャ時に CXX=gcc も設定して、libstdc++ライブラリ（これは不要です）が含まれないようにします。pgccの一部のバージョンでは、生成されたコードを x586 タイプのプロセッサ（AMD など）すべてで動作可能にするコンパイラオプションを使用しても、コードが純正 Pentium プロセッサでしか実行できないため注意が必要です。

適切なコンパイラおよびコンパイラオプションを使用することで、アプリケーションの速度が 10?30% 改善されます。これは各自で SQL サーバをコンパイルする場合に特に重要です。

Cygnus CodeFusion と Fujitsu コンパイラの両方をテストしましたが、いずれもバグフリーではなく、最適化をオンにして MySQL をコンパイルするには不十分でした。

標準の MySQL バイナリディストリビューションは、すべてのキャラクタセットをサポートするようにコンパイルされています。MySQL のコンパイル時は、使用するキャラクタセットのサポートのみを含めます。 これは--with-charsetオプションからconfigureによって管理されます。

以下に実施した測定結果の一部を紹介します。

pgccによるコンパイルに MySQL AB 提供の バイナリMySQL-Linux ディストリビューションを使用したが、AMD で実行されないコードを生成するバグが pgccにあったため、通常の gcc の使用に戻さざるを得ませんでした。このバグが解決されるまで gccの使用を続行します。 ただし、AMD 以外のコンピュータを使用する場合は、pgccでコンパイルすると高速なバイナリが得られます。標準の MySQL Linux バイナリは、速度および移植性を高めるため静的にリンクされています。

以下の一覧は、mysqldサーバでのメモリの使用方法の一部を示しています。可能な場合は、メモリ使用に関連するサーバ変数名も記載されています。

ps およびその他のステータスプログラムによって、mysqldが大量のメモリを使用していることを示すレポートが行われることがあります。これは、複数のメモリアドレスでのスレッドスタックによって発生します。たとえば、Solaris バージョンの psではスタック間の使用していないメモリが使用メモリにカウントされます。これは、swap -sで使用可能スワップをチェックすることで検証できます。市販のメモリリーク検出装置で mysqldをテストし、メモリリークがないと判明しています。

新たなクライアントが mysqldに接続すると、mysqldによって要求を処理する新規のスレッドが作成されます。このスレッドでは、まずホスト名がホスト名キャッシュにあるかどうかがチェックされます。ない場合は、ホスト名の解決が試行されます。

--skip-name-resolveを mysqldオプションを指定して起動すると、DNS ホスト名ルックアップを無効化できます。ただし、この場合は、MySQL 権限テーブルで IP 番号しか使用できなくなります。

非常に低速の DNS と多数のホストがある場合は、--skip-name-resolveで DNS ルックアップを無効化するか、HOST_CACHE_SIZEの定義（デフォルト値: 128）を拡張し、mysqldを再コンパイルすることで、パフォーマンスを改善できます。

--skip-host-cache オプションを使用してサーバを起動すると、ホスト名キャッシュを無効化できます。ホスト名のキャッシュをクリアするには、FLUSH HOSTSステートメントを使用するか、mysqladmin flush-hostsコマンドを実行します。

TCP/IP 接続すべてを認めない場合は、--skip-networkingオプションを指定して mysqldを開始します。

テーブルとデータベースをデータベースディレクトリから他の位置に移動し、新しい位置へのシンボリックリンクに置換することができます。 これは、たとえば、データベースを空き領域の多いファイルシステムに移動し、テーブルを別のディスクに分散することでシステムの速度を上げる場合などに実行できます。

この推奨される実行方法は、データベースだけ別のディスクへのシンボリックリンクを行い、最後の手段としてのみテーブルのシンボリックリンクを行うことです。

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

shell> cd /path/to/datadir
shell> ln -s db1 db2

if (!(MyFlags & MY_RESOLVE_LINK) ||
    (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

if (1)