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

MySQL はトランザクションテーブル、非トランザクションテーブルの両方で機能します。 非トランザクションテーブル（何らかのエラー発生した場合にロールバックができない）での動作をスムーズにするため、MySQL には次のルールがあります。

この詳細については、See 項1.8.5. 「MySQL における制約の処理」 を参照してください。

このことは、フィールド内容のチェックに MySQL を使用するのではなく、このチェックをアプリケーションで実行する必要があることを意味します。

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

複雑なアプリケーションを移植可能にするには、ともに稼動する必要のある SQL サーバ数を選択する必要があります。

たとえば、Infomix や DB2 の使用を可能にするには、18 文字を超えるカラム名は使用できません。

MySQL ベンチマークと crash-me プログラムはいずれもデータベースへの依存度が非常に低くなっています。これらのプログラムがどのように処理されているかを調べることによって、データベースに依存しないアプリケーションを作成する際に必要なことに関する感覚を得ることができます。ベンチマーク自体は、MySQL ソースディストリビューションの sql-bench ディレクトリにあります。これは Perl - DBI データベースインタフェース（問題のアクセス部分を解決する）で作成されています。

このベンチマークの結果については、http://www.mysql.com/information/benchmarks.html を参照してください。

これらの結果からもわかるように、データベースのすべてに何らかの弱点があります。言い換えると、動作の相違を招くさまざまな設計上の障害があります。

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

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

あるデータベースに固有の機能を使用する場合（MySQL の REPLACE コマンドなど）は、他の SQL サーバでその機能を実装できるようにするメソッドをコード化する必要があります（ただし低速化します）。MySQL を使用すると、/*! */ 構文を使用して MySQL 固有のキーワードをクエリに追加できます。/**/ 内のコードは、その他の SQL サーバのほとんどでコメントとして処理（無視）されます。

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

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

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

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

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

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

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

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

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

非常に適度な規模の Sun Ultra SPARCstation（2x200 Mhz）を使用した処理では何も問題が発生していません。最近サーバの 1 つを 2 CPU 400 Mhz UltraSPARC にアップグレードし、製品レベルでのトランザクション処理の開始を計画しています。この処理ではデータが 10 倍増加することになります。システムにディスクを追加するだけでこれに対応できると考えています。

安価に CPU 能力を増強できるように Intel-Linux でも実験を行っています。現在、バイナリの移植可能データベースフォーマット（バージョン 3.23 の新機能）があり、アプリケーションの一部への使用を開始することになっています。

当初、Linux では低から中程度の負荷でのパフォーマンスに優れ、Solaris ではディスク IO が非常に高いため高負荷を達成しようとする際のパフォーマンスに優れているという感触を得ましたが、現在のところこれに関する結論は出ていません。Linux カーネルの開発者との協議の結果、これは、Linux のバッチジョブに割り当てられるリソースが多すぎると対話的なパフォーマンスが非常に低くなる副作用の可能性もあリます。これによって大量のバッチが進行中に非常に低速になり、応答不可の状態が発生します。将来の Linux カーネルではこの処理が改善されるでしょう。

このセクションでは、MySQL ベンチマークスィート（および crash-me）の技術的記述を記載する予定ですが、この記述はまだ作成されていません。現状では、MySQL ソースディストリビューションの sql-bench ディレクトリにあるコードと結果を確認することでベンチマークに関するヒントが得られます。

このベンチマークスィートは、SQL 実装のパフォーマンスを向上または低下させる操作をユーザに示すことを目的としています。

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

下表は、Windows NT 4.0 コンピュータ上で ODBC を介していくつかのデータベースサーバにアクセスした場合のベンチマーク結果の比較を示しています。

最初のテストでは、8M のインデックスキャッシュサイズで MySQL が実行されました。

これ以外にもベンチマーク結果を http://www.mysql.com/information/benchmarks.html のサイトに収集しています。

Oracle は削除の依頼があったため含まれていません。Oracle のベンチマークはすべて、Oracle から提供されます。上記のベンチマークは標準のインストールが 1 クライアントに対して実行できることを示すことを想定しているため、Oracle のベンチマークは非常に偏りがあると確信しています。

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

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

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

server_name はサポートされるサーバの 1 つを表します。run-all-tests --help を呼び出すと、すべてのオプションとサポート対象サーバの一覧を取得できます。

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

多様なデータベースに関する crash-me の結果は、http://www.mysql.com/information/crash-me.php のサイトにあります。

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

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

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

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

このような問題を回避するには、想定可能な最悪の負荷でアプリケーション全体のベンチマークにある程度力を注ぐ必要があります。これには Super Smack を使用できます。これは、http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz で入手できます。 その名（Smack = 打ちこわし）のとおり、システムに限界まで負荷をかけることができるため、必ず開発システムでのみ使用するようにしてください。

    EXPLAIN tbl_name
か  EXPLAIN SELECT select_options

EXPLAIN tbl_name は、DESCRIBE tbl_name または SHOW COLUMNS FROM tbl_name のシノニムです。

キーワード EXPLAIN を SELECT ステートメントの前に置いた場合、MySQL によってテーブルの結合状況と順序に関する情報が提供され、テーブルの SELECT の処理方法が説明されます。

EXPLAIN を利用すると、より速くレコードを検索する SELECT を得るために、どの時テーブルにインデックスを追加しなければならないかを確認できます。

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

また、オプティマイザが、テーブルを最適な順序で結合しているかどうかも確認することができます。 オプティマイザが特定の順番で結合を行うように強制するには、SELECT ステートメントに STRAIGHT_JOIN 節を追加します。

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

MySQL バージョン 4.1 では、EXPLAIN 出力が変更され、UNION ステートメント、サブクエリ、派生テーブルなどの構造での機能が改善されています。最も重要なことは、id と select_type という 2 つの新しいカラムが追加されたことです。

EXPLAIN の出力は、次のカラムで構成されます。

EXPLAIN 出力の rows カラムのすべての値を掛け算することで、結合がどの程度適しているかを示す指針を取得できます。これは、クエリの実行時に MySQL で調べる必要があるレコード数の概要を示します。この数値は、max_join_size 変数でクエリを制限する際にも使用されます。 See 項5.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,ClientID,ActualPC NULL NULL    NULL 3872
      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,ClientID,ActualPC NULL NULL NULL 3872    Using where
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 where
             ClientID,
             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 に示すことができます。

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

これで結合が完全になり、EXPLAIN で以下の結果が生成されます。

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using where
             ClientID,
             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 結合オプティマイザの学習による推測であることに注意してください。クエリを最適化するには、この数値が実際に近いものであるかどうかを確認する必要があります。実際とかけ離れている場合は、SELECT ステートメントで STRAIGHT_JOIN を使用し、FROM 節でテーブルの順序を変えて一覧表示してみるとパフォーマンスを改善できます。

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

MySQL では、インデックスブロックが通常 1,024 バイトで、データポインタは通常 4 バイトです。インデックスの長さが 3（中位の整数）の 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 シークが必要になります。

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

一般に、低速の SELECT ... WHERE の速度を上げる必要がある場合は、まず、インデックスを追加できるかどうかをチェックします。See 項5.4.3. 「MySQL でのインデックスの使用」。一般に複数のテーブル間の参照はすべてインデックスを使用して実行する必要があります。EXPLAIN コマンドを使用して、SELECT に使用されるインデックスを判定できます。 See 項5.2.1. 「EXPLAIN 構文（SELECT に関する情報の取得）」。

一般的なヒント

WHERE の最適化は、ほとんどの場合 SELECT とともに使用されるため、SELECT 部分に適用されますが、DELETE や UPDATE のステートメントの WHERE にも同じ最適化が適用されます。

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

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

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

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
    ->        WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
    ->        ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> 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;
mysql> SELECT COUNT(*) FROM tbl_name
    ->        WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass: 次のクエリは、ソートのパスを分けることなく、ソートしたレコードを取り出すためにインデックスを使用します。

mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1,key_part2,... ;
mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1 DESC,key_part2 DESC,... ;

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

SELECT * FROM table_name WHERE key_col IS NULL;

SELECT * FROM table_name WHERE key_col <=> NULL;

SELECT * FROM table_name WHERE key_col=# OR key_col=# OR key_col IS NULL

OUTER JOIN に使用されないテーブル上で、WHERE 節内で column_name IS NULL で定義された物を NOT NULL と使用する場合、その式は消去して最適化されます。

MySQL 4.1.1 では、column = expr AND column 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 レベルしか処理できないことに注意が必要です。

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

この状況で MySQL は (t1.a=t2.a AND t2.a IS NULL) の部分に対してキーのルックアップを実行するのみで、b のキー部分は使用できません。

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

DISTINCT は GROUP BY をともなう可能性が高いので、SELECT されないフィールドを ORDER BY または HAVING した時に、どのように MySQL が機能するかを認識しておく必要があります。 See 項6.3.7.3. 「非表示のフィールドに対する GROUP BY」。

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

使用するテーブル内のカラムを使用しない場合、MySQL は最初にマッチするレコードを検索するとただちに未使用テーブルのスキャンを停止します。

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

ここでは、t1 が t2 の前に使用され（EXPLAIN によるチェック）、t2 で最初のレコードが検索されると t2からの読み取り（t1 の特定のレコード）を停止します。

MySQL の A LEFT JOIN B join_condition は以下のように実装されます。

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

テーブル読み取り順序は LEFT JOIN と STRAIGHT JOIN によって強制されるため、チェック対象のテーブル順列が減少し、結合オプティマイザ（テーブルの結合順序を計算する）の動作の速度がさらに上がります。

上記は、該当する種類のクエリを実行した場合であることに注意してください。

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

LEFT JOIN が d の前に読み取るように強制するため、MySQL では b の完全スキャンが実行されます。

この状況はクエリを以下のように変更して修正します。

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

4.0.14 以降、MySQL では以下の LEFT JOIN 最適化が行われます。

生成された NULL レコードで WHERE 条件が常に false である場合、LEFT JOIN は通常の結合に変更されます。

たとえば、t2 カラムが NULL であるとすると、以下のクエリの WHERE 節は false になるため、通常の結合に変換しても問題ありません。

SELECT * FROM t1 LEFT t2 ON (column) WHERE t2.column2 =5;
->
SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column=t2.column;

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

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

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

SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constant GROUP 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 節の条件に一致するレコードの検索にインデックスを使用します）。

MySQL で結果のソートが必要な場合は、以下のアルゴリズムが使用されます。

EXPLAIN SELECT ... ORDER BY を使用すると、MySQL でインデックスを使用してクエリを解決できるかどうかをチェックできます。extra カラムに Using filesort が出力された場合は、MySQL で ORDER BY の解決にインデックスを使用できません。 See 項5.2.1. 「EXPLAIN 構文（SELECT に関する情報の取得）」。

さらに ORDER BY の速度を上げる必要がある場合はまず、ソートフェーズを実行する必要なく MySQL でインデックスを使用できるかどうかを調べます。これが不可能な場合は、以下を実行できます。

デフォルトでは、クエリで ORDER BY x,y[,...] と指定した場合と同様に MySQL によってすべての GROUP BY x,y[,...] クエリがソートされます。ORDER BY 節を明示的に記述した場合、ソートは発生するものの、MySQL はスピードを損なうことなくそれを最適化します。 クエリに GROUP BY が含まれていて、もし結果のソートのオーバヘッドを回避したいならば、ORDER BY NULL を指定することでソートを抑止できます。

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

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

レコード挿入の時間構成の概要は次のとおりです。

ここに示した数値は、時間全体を比例的に配分したものです。テーブルを開く初期オーバヘッドは算入されていません（これは同時実行クエリのそれぞれで 1 回実行されます）。

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

挿入の速度を上げる方法

LOAD DATA INFILE と INSERT の両方の速度をさらに改善するには、キーバッファを拡張します。 See 項5.5.2. 「サーバパラメータのチューニング」。

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

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

可変長レコードの場合は、合計の長さが今よりも長いものにレコードを更新すると、レコードが分割される場合があることに注意します。このため、頻繁にこれを実行する場合は、ときどき OPTIMIZE TABLE することが重要になります。 See 項4.6.1. 「OPTIMIZE TABLE 構文」。

テーブル内のすべてのレコードを削除する場合は、TRUNCATE TABLE table_name を使用します。 See 項6.4.6. 「TRUNCATE 構文」。

レコード削除に要する時間は、完全にインデックス数に比例します。レコード削除の速度を上げるには、インデックスキャッシュのサイズを拡大します。 See 項5.5.2. 「サーバパラメータのチューニング」。

システム高速化のためのヒント（順不同）

ロックメソッドそれぞれについての説明は付録にあります。 See 項E.4. 「ロック方法」。

InnoDB 型と BDB 型のテーブルを除き、MySQL のロックはすべてデッドロックフリーです。 これは、常にクエリの開始時に必要なすべてのロックを要求し、また、常に同じ順序でテーブルをロックすることによって管理されます。

InnoDB 型のテーブルは、行ロックを自動的に取得し、BDB 型のテーブルは、トランザクションの開始時ではなく SQL ステートメントの処理時にページロックを自動取得します。

MySQL は WRITE ロックに以下のロック方法を使用します。

MySQL は READ ロックに以下のロック方法を使用します。

ロックが解除されると、まず書き込みロックキューのスレッドでロックが使用可能になり、その後読み取りロックキューのスレッドで利用可能になります。

これは、1 つのテーブルに対して更新が多数ある場合に、更新がすべてなくなるまで SELECT ステートメントが待機することを意味します。

テーブルに対して多数の 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;

特定の状況で取り出しに優先順位を設定するには、LOW_PRIORITY オプションを INSERT、UPDATE または DELETE に、あるいは HIGH_PRIORITY オプションを SELECT に使用します。また、--low-priority-updates オプションで mysqld を開始しても同じ効果が得られます。

SQL_BUFFER_RESULT の使用もテーブルロックを短縮するのに役立ちます。 See 項6.4.1. 「SELECT 構文」。

さらに、1 つのキューを使用するように mysys/thr_lock.c のロックコードを変更することもできます。この場合は、書き込みロックと読み取りロックの優先度が同じになり、アプリケーションによっては高速化に役立ちます。

MySQL のテーブルロックコードはデッドロックフリーです。

MySQL は、InnoDB テーブルと BDB テーブルを除くすべてのテーブル型にテーブルロックを使用して、非常に高速なロックを実現します。大型のテーブルの場合、ほとんどのアプリケーションで行ロックと比較してテーブルロックのほうがはるかに優れていますが、これには危険もあります。

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

MySQL バージョン 3.23.7 以降は、MyISAM テーブルへのレコードの挿入を、他のスレッドが同一テーブルから読み取りを行うのと同時に実行できるようになりました。現在のところ、挿入実行時にテーブルのレコード削除後のホールがない場合にのみ、この機能が使用可能になるため注意が必要です。すべてのホールに新規のデータが入力されると、同時挿入が自動的に再度可能になります。

テーブルロックにより、同時に多数のスレッドがテーブルからの読み取りを行うことができますが、あるスレッドがテーブルへの書き込みを行うときは、まず排他処理をする必要があります。更新時は、特定のテーブルにアクセスしようとする他のすべてのスレッドが、更新の準備ができるまで待機します。

一般にテーブルの更新は SELECT より重要だと見なされるため、テーブルを更新するステートメントはすべて、テーブルから情報を取り出すステートメントより優先度が高くなります。これにより、更新では特定のテーブルに対して大量の重いクエリが使用されるため、更新が '資源枯渇' にさらされないことが確実になります（これは、更新を実行するステートメントを LOW_PRIORITY とともに使用するか、SELECT ステートメントとともに HIGH_PRIORITY を使用することで変更できます）。

MySQL バージョン 3.23.7 以降は、max_write_lock_count 変数を使用して、テーブルに対する挿入が一定数行われた後に、MySQL によってテーブルの使用を待機している SELECT ステートメントのすべてに高い優先度を強制的に設定できるようになりました。

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

この問題に対応する解決策は以下のとおりです。

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

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

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

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

MySQL では多様なテーブル型とレコード形式がサポートされます。 適切なテーブル形式を選択することで、パフォーマンスを大幅に改善できます。 See 章?7. MySQL のテーブル型。

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

インデックスは、カラムが特定の値をもつレコードの迅速な検索に使用されます。インデックスがないと、MySQL がレコードを見つけるために、最初のレコードから開始し、テーブル全体を読み取るとが必要になります。テーブルが大きくなると、これにコストがかかります。クエリ対象のカラムにインデックスがあると、MySQL は全てのデータを探すことなく、データファイルの途中にあるシーク対象ポジションを迅速に取得することができます。テーブルに 1000 レコードある場合、シーケンシャルに読み取る場合と比較して少なくとも 100 倍は高速化できます。1000 レコードのほとんどすべてにアクセスする必要がある場合は、ディスクシークが最小になるため、シーケンシャルに読むほうが速くなることに注意してください。

MySQL インデックスのすべて（PRIMARY KEY、UNIQUE、および INDEX）は、B ツリーに格納されます。文字列の頭にある空白と最後にある空白は自動的に圧縮されます。 See 項6.5.7. 「CREATE INDEX 構文」。

インデックスの用途は以下のとおりです。

次の 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 ステートメントがあるとします。

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

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

MySQL は、LIKE の引数がワイルドカード文字で始まらない文字列定数である場合に、LIKE 比較にもインデックスを使用します。たとえば、以下の SELECT ステートメントではインデックスが使用されます。

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

最初のステートメントでは "Patrick" <= key_col < "Patricl" のあるレコードだけが考慮されます。2 つめのステートメントでは "Pat" <= key_col < "Pau" のあるレコードだけが考慮されます。

以下の SELECT ステートメントではインデックスが使用されません。

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

最初のステートメントでは LIKE がワイルドカード文字で始まっています。2 つめのステートメントでは LIKE 値が定数ではありません。

MySQL 4.0 ではこれ以外の LIKE の最適化も実行されます。... LIKE "%string%" を使用し、string が 3 文字より長い場合、MySQL は Turbo Boyer-Moore アルゴリズムを使用して、文字列のパターンを初期化してから、このパターンを使用して検索を素早く実行します。

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

通常 MySQL は、検索するレコードを少なくするために、インデックスを使用します。インデックスは、以下の演算子で比較するカラムに使用されます。 =、>、>=、<、<=、BETWEEN、または、'something%' などのワイルドカード以外のプリフィックスで始まるパターンに対する LIKE。

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

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

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

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

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* Index is not used in
                                                        both AND parts */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows  */

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

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

テーブルあたりの最大インデックス数とインデックスの最大長は、ストレージエンジンごとに定義されます。See 章?7. MySQL のテーブル型。ストレージエンジンのすべてで、1 テーブルあたり 16 以上のインデックスと 256 バイト以上のインデックス長がサポートされます。

CHAR 型および VARCHAR 型のカラムでは、カラムの先頭部分をインデックス化できます。これは、カラム全体をインデックス化する場合と比較して大幅に高速になり、所要ディスク領域も少なくて済みます。カラムの先頭部分をインデックス化する CREATE TABLE ステートメント構文は次のようになります。

INDEX index_name (col_name(length))

この例では、name カラムの最初の 10 文字のインデックスが作成されます。

mysql> CREATE TABLE test (
    ->        name CHAR(200) NOT NULL,
    ->        INDEX index_name (name(10)));

BLOB 型および TEXT 型のカラムでは、カラムの先頭部分をインデックス化する必要があります。インデックスが張れる部分の最大長は 255 バイトです。

MySQL バージョン 3.23.23 以降は、特殊な FULLTEXT インデックスも作成できます。これは全文検索に使用されます。FULLTEXT インデックスは、MyISAM テーブル型でのみ、CHAR、VARCHAR、および TEXT カラムに限ってサポートされます。 フルテキストインデックスの作成は常にカラム全体を対象として、先頭部分（プリフィックス）のインデックス化は行われません。詳細については、項6.8. 「MySQL 全文検索」 を参照してください。

MySQL では複数のカラムに対するインデックスを作成できます。インデックスは最大 15 カラムで構成できます（CHAR および VARCHAR カラムではカラムの先頭部分をインデックスの部分として使用することもできます）。

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

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

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

mysql> 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 インデックスは次のようなクエリに使用されます。

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND first_name >="M" AND first_name < "N";

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

mysql> SELECT * FROM test WHERE first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    OR first_name="Michael";

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

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

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

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

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

次のセクションでもこのトピックについてさらに説明します。 See 項5.4.7. 「MySQL でのテーブルのオープンとクローズの方法」。

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

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

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

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

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

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

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

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

テーブルキャッシュが小さすぎるかどうかは、mysqld の Opened_tables 変数のチェックで確認できます。たとえ多くの FLUSH TABLES を実行していない場合でも、この値が非常に大きい場合は、テーブルキャッシュサイズを拡張する必要があります。 See 項4.6.8.3. 「SHOW STATUS」。

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

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

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

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

その他のヒント:

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

shell> mysqld --help

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

back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
connect_timeout          current value: 5
delayed_insert_timeout   current value: 300
delayed_insert_limit     current value: 100
delayed_queue_size       current value: 1000
flush_time               current value: 0
interactive_timeout      current value: 28800
join_buffer_size         current value: 131072
key_buffer_size          current value: 1048540
lower_case_table_names   current value: 0
long_query_time          current value: 10
max_allowed_packet       current value: 1048576
max_binlog_cache_size    current value: 4294967295
max_connections          current value: 100
max_connect_errors       current value: 10
max_delayed_threads      current value: 20
max_heap_table_size      current value: 16777216
max_join_size            current value: 4294967295
max_sort_length          current value: 1024
max_tmp_tables           current value: 32
max_write_lock_count     current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
read_rnd_buffer_size     current value: 262144
slow_launch_time         current value: 2
sort_buffer              current value: 2097116
table_cache              current value: 64
thread_concurrency       current value: 10
tmp_table_size           current value: 1048576
thread_stack             current value: 131072
wait_timeout             current value: 28800

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

mysql> SHOW VARIABLES;

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

mysql> SHOW STATUS;

すべての変数の詳細説明については、本マニュアルの SHOW VARIABLES セクションを参照してください。 See 項4.6.8.4. 「SHOW VARIABLES」。 ステータス変数詳細については、項4.6.8.3. 「SHOW STATUS」 を参照してください。

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

shell> mysqladmin variables
shell> mysqladmin extended-status

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

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

以下に典型的な変数を実行時に設定している例を示します。この例は mysqld_safe スクリプトを使用し、--name=value 構文で変数 name を値 value に設定しています。この構文は、MySQL 4.0 から利用できます。旧バージョンの MySQL の場合は、以下の相違点を考慮してください。

最小 256M のメモリで多数のテーブルがあり、中程度のクライアントで最大のパフォーマンスを得るには、次のように使用します。

shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
           --sort_buffer_size=4M --read_buffer_size=1M &

メモリが 128M で、テーブルは少数で大量のソートの実行が必要な場合は、次のように使用できます。

shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

メモリがほとんどなく大量の接続がある場合は、次のように使用します。

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
           --read_buffer_size=100K &

また、次のようにもできます。

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
           --table_cache=32 --read_buffer_size=8K -O net_buffer_length=1K &

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

MySQL をインストールしたときは、support-files ディレクトリに複数の my.cnf サンプルファイルの、my-huge.cnf、my-large.cnf、my-medium.cnf、および my-small.cnf が格納され、システム最適化のベースとして使用できます。

同時接続が非常に多い場合、接続ごとに mysqld で使用されるメモリを非常に小さくしていないとスワップの問題が発生することがあります。言うまでもなく、すべての接続に使用可能なメモリが十分ある場合は mysqld のパフォーマンスが向上します。

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

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

shell> mysqld --key_buffer_size=32m --help

必ず --help を最後に指定します。最後にしないと、コマンドラインのそれ以降に記載されたオプションの効果が出力に反映されません。

以下のテストのほとんどは、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 のコンパイル時は、使用するキャラクタセットのサポートのみを含めます（オプション --with-charset=xxx）。 標準の MySQL バイナリディストリビューションは、すべてのキャラクタセットをサポートするようにコンパイルされています。

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

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 を開始します。

SET [GLOBAL | SESSION] sql_variable=expression,
    [[GLOBAL | SESSION] sql_variable=expression] ...

SET は、サーバやクライアントの動作に影響を及ぼすさまざまなオプションを設定します。

以下の例は、変数の設定に使用できる各種の構文を示しています。

旧バージョンの MySQL では、SET OPTION 構文の使用を許可していましたが、今は廃止されています。

MySQL 4.0.3 では、GLOBAL オプション、SESSION オプション、および最も重要なスタートアップ変数へのアクセスを追加しています。

LOCAL は、SESSION のシノニムとして使用できます。

1 つのコマンドラインに複数の変数を設定する場合は、最後の GLOBAL | SESSION モードが使用されます。

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

@@variable_name 構文は、MySQL 構文とその他のデータベースとの互換性を保持を目的にサポートされています。

このマニュアルのシステム変数のセクションに設定可能な多様なシステム変数に関する説明があります。 See 項6.1.5. 「システム変数」。

SESSION（デフォルト）を使用している場合、現在のセッションを終了するまで、あるいはこのオプションに別の値を設定するまで、設定したオプションが有効になります。SUPER 特権を必要とする GLOBAL を使用した場合、サーバの再起動が行われるまでオプションが記憶され、新規接続時も使用されます。オプションを永続的にする場合は、オプション設定ファイルに設定します。 See 項4.1.2. 「my.cnf オプション設定ファイル」。

不適切な使用を防ぐため、SET SESSION でしか使用できない変数とともに SET GLOBAL を使用した場合や、グローバル変数に SET GLOBAL を使用しない場合には MySQL からエラーが出力されます。

SESSION 変数を GLOBAL 値に、あるいは GLOBAL 値を MySQL のデフォルト値に設定する場合は、DEFAULT として設定することができます。

SET max_join_size=DEFAULT;

これは以下と等しいことになります。

SET @@session.max_join_size=@@global.max_join_size;

SET コマンドで設定可能なサーバ変数に最大値を設定して制限する場合、--maximum-variable-name コマンドラインオプションを使用して最大値を指定できます。 See 項4.1.1. 「mysqld コマンドラインオプション」。

SHOW VARIABLES を使用すると、ほとんどの変数の一覧が出力されます。 See 項4.6.8.4. 「SHOW VARIABLES」。 @@[global.|local.]variable_name 構文を使用すると特定の変数の値を取得できます。

SHOW VARIABLES like "max_join_size";
SHOW GLOBAL VARIABLES like "max_join_size";
SELECT @@max_join_size, @@global.max_join_size;

以下に、非標準 SET 構文を使用する変数およびその他の変数の一部について説明します。これ以外の変数定義は、システム変数セクションのスタートアップオプションの部分または SHOW VARIABLES の説明に記載されています。 See 項6.1.5. 「システム変数」。 See 項4.1.1. 「mysqld コマンドラインオプション」。 See 項4.6.8.4. 「SHOW VARIABLES」。

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

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

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test mysqld-datadir

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

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

if (1)

[mysqld]
symbolic-links