[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5. MySQL の最適化

5.1 最適化の概要   
5.2 SELECT ステートメントおよびその他のクエリの最適化   
5.3 ロック関連の問題   
5.4 データベース構造の最適化   
5.5 MySQL サーバの最適化   
5.6 ディスク関連の問題   

最適化は、システム全体の理解が必要であるため、複雑な作業です。システムやアプリケーションに関する知識が豊富でなくても部分的なローカルの最適化は可能ですが、より高度な最適化が必要になるほど求められる知識も高度になります。

この章では、MySQL 最適化の方法説明し、その例もいくつか紹介します。ただし、常にシステムの速度をさらに上げる補足的な方法もありますが、難度も高くなることを覚えておいてください。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.1 最適化の概要

言うまでもなく、システムの速度を上げる際に最も重要な要素は基本設計です。また、使用するシステムの用途およびそのボトルネックを認識しておく必要もあります。

最も一般的なボトルネックは下記のとおりです。

5.1.1 MySQL の設計上の制約とトレードオフ   
5.1.2 移植性   
5.1.3 MySQL 使用実績   
5.1.4 MySQL ベンチマークスィート   
5.1.5 独自のベンチマークの使用   

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.1.1 MySQL の設計上の制約とトレードオフ

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

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

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.1.2 移植性

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

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

MySQL crash-me プログラム（Web ページ http://www.mysql.com/information/crash-me.php ）を使用すると、データベースサーバの選択に使用できる関数、データ型、制約を調べることができます。現在の Crash-me は可能なことすべてのテストを実行できるとは決して言えませんが、約 450 項目のテストが幅広く行われています。

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.1.3 MySQL 使用実績

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 カーネルではこの処理が改善されるでしょう。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.1.4 MySQL ベンチマークスィート

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

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

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

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

インデックスごとに 2,000,000 レコードの読み取り 秒 秒
mysql 367 249
mysql_odbc 464
db2_odbc 1206
informix_odbc 121126
ms-sql_odbc 1634
oracle_odbc 20800
solid_odbc 877
sybase_odbc 17614

350,768 レコードの挿入 秒 秒
mysql 381 206
mysql_odbc 619
db2_odbc 3460
informix_odbc 2692
ms-sql_odbc 4012
oracle_odbc 11291
solid_odbc 1801
sybase_odbc 4802

最初のテストでは、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 のサイトにあります。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.1.5 独自のベンチマークの使用

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

移植可能なベンチマークプログラムの例として、MySQL ベンチマークスィートを取り上げます。 「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 = 打ちこわし）のとおり、システムに限界まで負荷をかけることができるため、必ず開発システムでのみ使用するようにしてください。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2 SELECT ステートメントおよびその他のクエリの最適化

第 1 にすべてのクエリに影響を及ぼすことが 1 つあります。アクセス権システムのセットアップの複雑性が増すほど、オーバヘッドも増加します。

GRANT ステートメントを何も実行していない場合は、MySQL によってアクセス権チェックが多少最適化されます。大量の処理が必要なときは、GRANT を使用しないことで時間を節約できる場合もあります。GRANT を使用した場合は、アクセス権チェックが多くなり、オーバヘッドが増加します。

明示的な MySQL 関数に関わる問題がある場合は、常に MySQL クライアントでこの関数の計時を行うことができます。

 
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

これは、PentiumII 400MHz 上で MySQL によって 1,000,000 の + 式を 0.32 秒間に実行できることを示しています。 MySQL 関数はすべて最適化されていますが、例外も若干あります。BENCHMARK(loop_count,expression) はクエリに関数上の問題があるかどうかを調べる際に最適のツールです。

5.2.1 EXPLAIN 構文（SELECT に関する情報の取得）   
5.2.2 クエリパフォーマンスの推定   
5.2.3 SELECT クエリの速度   
5.2.4 MySQL による WHERE 節の最適化   
5.2.5 MySQL による IS NULL の最適化   
5.2.6 MySQL による DISTINCT の最適化   
5.2.7 MySQL による LEFT JOIN と RIGHT JOIN の最適化   
5.2.8 MySQL による ORDER BY の最適化   
5.2.9 MySQL による LIMIT の最適化   
5.2.10 INSERT クエリの速度   
5.2.11 UPDATE クエリの速度   
5.2.12 DELETE クエリの速度   
5.2.13 その他の最適化のヒント   

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.1 EXPLAIN 構文（SELECT に関する情報の取得）

 
    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 を定期的に実行する必要があります。 「4.6.2 ANALYZE TABLE 構文」 節 参照 。

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

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

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

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

id
SELECT に割り当てられた ID。クエリ内におけるこの SELECT の順序番号。

select_type
SELECT 節の種類、次のいずれかが示される。

SIMPLE
単純な SELECT（UNION やサブクエリを使用しない）。

PRIMARY
最外部の SELECT

UNION
UNION 内の第 2 およびそれ以降の SELECT ステートメント。

DEPENDENT UNION
UNION 内の第 2 およびそれ以降の SELECT ステートメント、外側のサブクエリに依存する。

SUBQUERY
サブクエリ内の第 1 SELECT。

DEPENDENT SUBQUERY
第 1 SELECT、外側のサブクエリに依存する。

DERIVED
派生テーブル SELECT（FROM 節内のサブクエリ）。

table
結果を得るために参照するテーブル。

type
結合型。各結合型を最適なものから順に紹介する。

system
1 レコードのみで構成されるテーブル（= システムテーブル）。これは、const 結合型の特殊なケースである。

const
テーブルに、一致するレコードが最大で 1 つあり、クエリの開始時に読み取られる。レコードが 1 つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。const テーブルは、1 回しか読み取られないため、非常に高速である。

const は、PRIMARY/UNIQUE キーを定数と比較する場合に使用される。

 
SELECT * FROM const_table WHERE primary_key=1;

SELECT * FROM const_table
WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref
前のテーブルのレコードの組み合わせのそれぞれに対して、このテーブルから 1 レコードずつ読み取られる。これは、const 型以外で最適な結合型である。結合でインデックスのすべての部分が使用され、このインデックスが UNIQUE または PRIMARY KEY である場合に使用される。

= 演算子と比較されるインデックスの張られたカラムには、eq_ref を使用できる。比較対象のアイテムは定数でも、このテーブル以前に読み取られたテーブルのカラムを使用する式でもかまわない。

下記の例では、ref_table で eq_ref が使用される。

 
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

ref
前のテーブルのレコードの組み合わせのそれぞれに対して、インデックス値にマッチするすべてのレコードがこのテーブルから読み取られる。ref は、インデックスの左端の先頭部分のみが結合で使用される場合、またはインデックスが UNIQUE や PRIMARY KEY ではない場合（すなわち、この結合において、インデックス値から1つのレコードをSELECTできない場合）に使用される。この結合型は、使用されるインデックスと一致するレコードが数レコードしかない場合に適している。

= 演算子と比較されるインデックスが張られたカラムには、ref が使用される。

下記の例では、ref_table で ref が示される。

 
SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

ref_or_null
ref と同様だが、NULL を使用したレコードの補足検索も追加で実行される。 「5.2.5 MySQL による IS NULL の最適化」 節 参照 。

 
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

この結合型の最適化は、MySQL 4.1.1 の新機能で、主としてサブクエリを解決する場合に使用される。

range
インデックスを使用して、一定の範囲にあるレコードのみが取り出される。key カラムに使用されるインデックスが示される。 key_len には使用される最長のインデックス部分が記載される。 この型では、ref カラムが NULL になる。

range は、インデックスを張っているカラムが =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、および IN を使用して定数と比較される場合に使用される。

 
SELECT * FROM range_table WHERE key_column = 10;

SELECT * FROM range_table WHERE key_column BETWEEN 10 and 20;

SELECT * FROM range_table WHERE key_column IN (10,20,30);

SELECT * FROM range_table WHERE key_part1= 10 and key_part2 IN (10,20,30);

index
これは、インデックスツリーのみがスキャンされる点を除いて ALL と同じである。一般にインデックスファイルはデータファイルより小さいため、通常は ALL より高速である。

これは、クエリで 1 インデックスの構成部分であるカラムのみが使用される場合にのみ使用される。

ALL
前のテーブルのレコードの組み合わせのそれぞれに対して、フルテーブルスキャンが実行される。一般に、テーブルが const の指定がない第 1 テーブルの場合には適さず、その他の場合はすべて非常に不適である。通常は、さらにインデックスを追加することで ALL を回避し、定数値または以前のテーブルのカラム値を基準にレコードを取り出すようにすることができる。

possible_keys
possible_keys カラムは、このテーブル内のレコードの検索に MySQL で使用可能なインデックスを示す。このカラムはテーブルの順序にはまったく依存しないことに注意する。すなわち、possible_keys のキーの一部は、生成されたテーブルの順序では事実上使用できないことになる。

このカラムが NULL の場合は、対応するインデックスがない。この場合は、WHERE 節でインデックス作成に適するカラムを 1 つ以上参照しているかどうかを調べることでクエリのパフォーマンスを改善できる。参照している場合は適切なインデックスを作成し、再度 EXPLAIN を使用してクエリをチェックする。 「6.5.4 ALTER TABLE 構文」 節 参照 。

テーブルにあるインデックスを調べるには SHOW INDEX FROM tbl_name を使用する。

key
key カラムは、MySQL が実際に使用を決定したキー（インデックス）を示す。選択されたインデックスがない場合、このキーは NULL になる。MySQL で possible_keys カラムに記載されたキーが使用されるように強制するには、クエリで USE KEY/IGNORE KEY を使用する。 「6.4.1 SELECT 構文」 節 参照 。

また、テーブルで myisamchk --analyze（ 「4.5.6.1 myisamchk 起動構文」 節 参照 ）または ANALYZE TABLE（ 「4.6.2 ANALYZE TABLE 構文」 節 参照 ）を実行することも、オプティマイザでより適したインデックスを選択する際に役立つ。

key_len
key_len カラムは、MySQL が使用を決定したキーの長さを示す。key が NULL の場合、この長さは NULL になる。これによって、複合キーで MySQL が実際に使用するパート数が示されることに注意する。

ref
ref カラムは、テーブルからレコードを選択する際に key とともに使用されるカラムまたは定数を示す。

rows
rows カラムは、クエリの実行に際して調べる必要があると MySQL によって判定されたレコードの数を示す。

Extra
このカラムには、MySQL でどのようにクエリが解決されるかに関する追加情報が記載される。以下は、このカラムに記載できる各種テキスト文字列の説明である。

Distinct
マッチした最初のレコードが検索されると、MySQL は現在のレコードの組み合わせによるその後のレコード検索を続行しないことを示す。

Not exists
MySQL でクエリに対する LEFT JOIN 最適化が実行でき、LEFT JOIN に一致するレコードが 1 つ検索されると、前のレコードの組み合わせによるその後のテーブルのレコードについては調べないことを示す。

この例は以下のとおりである。

 
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

t2.id が NOT NULL で定義されているとする。この場合、MySQL で t1 がスキャンされ、t1.id で t2 内のレコードのルックアップが行われる。MySQL によって t2 内のマッチするレコードが検索されると、t2 は t2.id ではないと認識され、t2 内の同じ id を持つ残りのレコードのスキャンは行われない。言い換えると、t2 にあるマッチするレコードの数に関わらず、MySQL で実行が必要なことは t1 のレコードのそれぞれに対して、t2 のルックアップを 1 回実行することだけである。

range checked for each record (index map: #)
MySQL で使用に適した実際のインデックスを検索できなかったことを示す。代替として、先行テーブルのレコードの組み合わせのそれぞれに対して、使用するインデックス（存在する場合）のチェックが実行され、このインデックスがテーブルからのレコードの取り出しに使用される。非常に高速ではないが、インデックスなしの結合と比較すると高速である。

Using filesort
レコードをソートして取り出す方法を決定するには、MySQL はパスを余分に実行しなくてはならないことを示す。 join type に従ってすべてのレコードをスキャンし、WHERE 条件に一致する全てのレコードに、ソートキー + 行ポインタを格納して、ソートは実行される。 その後キーがソートされる。 最後に、ソートされた順にレコードが取り出される。

Using index
インデックスツリーの情報のみを使用してカラム情報がテーブルから取り出され、実際のレコードを読み取るその後の検索を実行する必要がないことを示す。これは、そのテーブルで使用されたカラムがすべて同一インデックスの構成部分である場合に実行できる。

Using temporary
クエリの解決に MySQL で結果を保持するテンポラリテーブルの作成が必要であることを示す。これは一般に、GROUP BY を実行したカラムセットと異なるカラムセットに対して ORDER BY を実行した場合に発生する。

Using where
次のテーブルとの一致が調べられるレコードまたはクライアントに送信されるレコードの限定に WHERE 節が使用されることを示す。この情報がなく、テーブルの型が ALL または index である場合はクエリが正常に実行されないことがある（テーブルのすべてのレコードの取得や検査を意図していない場合）。

クエリを最大限高速に実行する必要がある場合は、Using filesort と Using temporary に注意する必要がある。

EXPLAIN 出力の rows カラムのすべての値を掛け算することで、結合がどの程度適しているかを示す指針を取得できます。これは、クエリの実行時に MySQL で調べる必要があるレコード数の概要を示します。この数値は、max_join_size 変数でクエリを制限する際にも使用されます。 「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 節でテーブルの順序を変えて一覧表示してみるとパフォーマンスを改善できます。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.2 クエリパフォーマンスの推定

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

MySQL では、インデックスブロックが通常 1024 バイトで、データポインタは通常 4 バイトです。インデックスの長さが 3（中位の整数）の 500,000 レコードのテーブルの場合は以下のようになります。 log(500000)/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 の分だけ増加する）によって最終的にアプリケーションがバインドされるまで大幅に速度の低下が始まります。これを回避するには、データの増加に合わせてインデックスキャッシュも拡大します。 「5.5.2 サーバパラメータのチューニング」 節 参照 。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.3 SELECT クエリの速度

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

一般的なヒント

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.4 MySQL による WHERE 節の最適化

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;

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.5 MySQL による IS NULL の最適化

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 のキー部分は使用できません。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.6 MySQL による DISTINCT の最適化

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

DISTINCT は GROUP BY をともなう可能性が高いので、SELECT されないフィールドを ORDER BY または HAVING した時に、どのように MySQL が機能するかを認識しておく必要があります。 「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 の特定のレコード）を停止します。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.7 MySQL による LEFT JOIN と RIGHT JOIN の最適化

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 を使用します。

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.8 MySQL による ORDER BY の最適化

余分なソートを行わずに 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 の解決にインデックスを使用できません。 「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;

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.9 MySQL による LIMIT の最適化

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.10 INSERT クエリの速度

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

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

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

挿入の速度を上げる方法

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.11 UPDATE クエリの速度

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

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.12 DELETE クエリの速度

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.2.13 その他の最適化のヒント

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.3 ロック関連の問題

5.3.1 MySQL のテーブルロック方法   
5.3.2 テーブルロック関連の問題   

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.3.1 MySQL のテーブルロック方法

ロックメソッドそれぞれについての説明は付録にあります。 「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 の使用もテーブルロックを短縮するのに役立ちます。 「6.4.1 SELECT 構文」 節 参照 。

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.3.2 テーブルロック関連の問題

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 ステートメントのすべてに高い優先度を強制的に設定できるようになりました。

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.4 データベース構造の最適化

5.4.1 設計上の選択   
5.4.2 データの小型化   
5.4.3 MySQL でのインデックスの使用   
5.4.4 カラムインデックス   
5.4.5 複合インデックス   
5.4.6 MySQL のオープンテーブルのカウント方法   
5.4.7 MySQL でのテーブルのオープンとクローズの方法   
5.4.8 1 つのデータベースに大量のテーブルを作成した場合の欠点   

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.4.1 設計上の選択

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

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.4.2 データの小型化

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

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

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

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top ] [Contents ] [Index] [ ? ]

5.4.3 MySQL でのインデックスの使用

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

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

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

次の SELECT ステートメントを指定したとします。

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

col1 と col2 に複合インデックスが存在する場合、対応するレコードを直接読み取れます。col1 と col2 に独立した単一カラムインデックスが存在する場合、検索される