[ < ] [ > ]   [ << ] [ 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 に独立した単一カラムインデックスが存在する場合、検索されるレコードの少ないインデックスを判定し、そのインデックスをレコードの読み取りに使用して、最も制限性の高いインデックスの検索が試行されます。

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。たとえば、(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 はインデックスを使用します。

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

5.4.4 カラムインデックス

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

テーブルあたりの最大インデックス数とインデックスの最大長は、ストレージエンジンごとに定義されます。 「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 全文検索」 を参照してください。

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

5.4.5 複合インデックス

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 でのインデックスの使用」 を参照してください。

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

5.4.6 MySQL のオープンテーブルのカウント方法

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

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

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

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

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

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

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 で使用可能なファイル記述子数を拡大できます。 「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 を呼び出すか、スレッドが終了するまで閉じられません。 「6.4.9 HANDLER 構文」 節 参照 。 この場合はテーブルがテーブルキャッシュに戻されます（キャッシュが満杯でない場合）。

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

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

5.4.8 1 つのデータベースに大量のテーブルを作成した場合の欠点

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

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

5.5 MySQL サーバの最適化

5.5.1 システム、コンパイル時間およびスタートアップパラメータのチューニング   
5.5.2 サーバパラメータのチューニング   
5.5.3 MySQL の速度に対するコンパイルとリンクの影響   
5.5.4 MySQL でのメモリの使用   
5.5.5 MySQL の DNS の使用   
5.5.6 SET 構文   

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

5.5.1 システム、コンパイル時間およびスタートアップパラメータのチューニング

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

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

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

その他のヒント:

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

5.5.2 サーバパラメータのチューニング

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 セクションを参照してください。 「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 を最後に指定します。最後にしないと、コマンドラインのそれ以降に記載されたオプションの効果が出力に反映されません。

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

5.5.3 MySQL の速度に対するコンパイルとリンクの影響

以下のテストのほとんどは、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 バイナリは、速度および移植性を高めるため静的にリンクされています。

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

5.5.4 MySQL でのメモリの使用

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

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

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

5.5.5 MySQL の DNS の使用

新たなクライアントが 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 を開始します。

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

5.5.6 SET 構文

 
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 構文とその他のデータベースとの互換性を保持を目的にサポートされています。

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

SESSION（デフォルト）を使用している場合、現在のセッションを終了するまで、あるいはこのオプションに別の値を設定するまで、設定したオプションが有効になります。SUPER 特権を必要とする GLOBAL を使用した場合、サーバの再起動が行われるまでオプションが記憶され、新規接続時も使用されます。オプションを永続的にする場合は、オプション設定ファイルに設定します。 「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 コマンドラインオプションを使用して最大値を指定できます。 「4.1.1 mysqld コマンドラインオプション」 節 参照 。

SHOW VARIABLES を使用すると、ほとんどの変数の一覧が出力されます。 「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 の説明に記載されています。 「6.1.5 システム変数」 節 参照 。 「4.1.1 mysqld コマンドラインオプション」 節 参照 。 「4.6.8.4 SHOW VARIABLES」 節 参照 。

AUTOCOMMIT= 0 | 1
1 に設定すると、テーブルに対する変更すべてがただちに実行される。トランザクションを有効にする場合は、BEGIN ステートメントを使用する必要がある。 「6.7.1 START TRANSACTION、COMMIT、ROLLBACK の各構文」 節 参照 。0 に設定した場合は、そのトランザクションを COMMIT で受け入れるか、ROLLBACK で取り消す必要がある。 「6.7.1 START TRANSACTION、COMMIT、ROLLBACK の各構文」 節 参照 。 AUTOCOMMIT モードを 0 から 1 に変更すると、開いているすべてのトランザクションに対して MySQL が COMMIT を自動実行するため注意が必要である。

BIG_TABLES = 0 | 1
1 に設定すると、テンポラリテーブルのすべてがメモリではなくディスクに格納される。これによって速度が少し低下するが、大きなテンポラリテーブルを必要とする大規模な SELECT 操作でもエラー The table tbl_name is full が出力されなくなる。新たに接続した場合のデフォルト値は 0（メモリ内テンポラリテーブルを使用）である。 この変数は旧称 SQL_BIG_TABLES であった。MySQL 4.0 では、MySQL によって必要に応じてメモリ内テーブルがディスクベーステーブルに自動変換されるため、通常この変数の設定が必要な状況はない。

CHARACTER SET character_set_name | DEFAULT
これは、クライアントとの間でやり取りされるすべての文字列に指定のマッピングを行う。 現在、character_set_name の唯一のオプションは cp1251_koi8 のみであるが、MySQL ソースディストリビューションの `sql/convert.cc' ファイルを編集して容易に新規のマッピングを追加できる。デフォルトのマッピングは、DEFAULT の character_set_name 値を使用してリストアできる。

CHARACTER SET オプションを設定する構文は、他のオプションを設定する構文とは異なるため注意が必要である。

DATE_FORMAT = format_str
サーバで DATE 値を文字列に変換する方法を設定する。この変数は、グローバルオプション、ローカルオプション、コマンドラインオプションのいずれでも使用できる。format_str の指定には GET_FORMAT() 関数の使用が便利である。 「6.3.4 日付と時刻関数」 節 参照 を参照。

DATETIME_FORMAT = format_str
サーバで DATETIME 値を文字列に変換する方法を設定する。この変数は、グローバルオプション、ローカルオプション、コマンドラインオプションのいずれでも使用できる。format_str の指定には GET_FORMAT() 関数の使用が便利である。 「6.3.4 日付と時刻関数」 節 参照 を参照。

INSERT_ID = #
次の INSERT や ALTER TABLE コマンドで使用される AUTO_INCREMENT 値を設定する。これは主としてバイナリログとともに使用される。

LAST_INSERT_ID = #
LAST_INSERT_ID() から返される値を設定する。これは、テーブルを更新するコマンドで LAST_INSERT_ID() 関数を使用した場合にバイナリログに格納される。

LOW_PRIORITY_UPDATES = 0 | 1
1 に設定した場合、全ての INSERT、UPDATE、DELETE、および LOCK TABLE WRITE ステートメントが、同じテーブルに対して実行されている SELECT や LOCK TABLE READ がなくなるまで待機する。 この変数は旧称 SQL_LOW_PRIORITY_UPDATES であった。

MAX_JOIN_SIZE = value | DEFAULT
value を超えるレコードの組み合わせを調べることが必要な SELECT ステートメント、または value を超えるディスクシークの実行が見込まれる SELECT ステートメントを許可しない。この値を設定すると、キーの使用が不適切で長時間かかると見込まれる SELECT ステートメントを捕捉できる。DEFAULT 以外の値に設定すると、SQL_BIG_SELECTS 値が 0 にリセットされる。SQL_BIG_SELECTS 値を設定しなおすと、SQL_MAX_JOIN_SIZE 変数は無視される。mysqld を --max_join_size=value オプションを指定して起動すると、この変数にデフォルト値を設定できる。この変数は旧称 SQL_MAX_JOIN_SIZE であった。

クエリ結果がすでにクエリキャッシュにある場合は、結果がすでに計算されており、クライアントへの送信による負荷がサーバにかからないため、結果サイズのチェックは実行されない。

PASSWORD = PASSWORD（'パスワード'）
現在のユーザのパスワードを設定する。すべての非匿名ユーザは各自のパスワードを変更できる。

PASSWORD FOR user = PASSWORD（'パスワード'）
現在のサーバホストの特定ユーザのパスワードを設定する。この実行は、mysql データベースへのアクセスがあるユーザに限られる。ユーザは user@hostname の形式で指定する必要がある。user と hostname は、mysql.user テーブルエントリの User カラムと Host カラムの記載どおりにする必要がある。たとえば、User フィールドと Host フィールドのエントリが 'bob' および '%.loc.gov' の場合は次のように入力する。

 
mysql> SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

これは以下と等しいことになる。

 
mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')
    ->                   WHERE User='bob' AND Host='%.loc.gov';
mysql> FLUSH PRIVILEGES;

QUERY_CACHE_TYPE = OFF | ON | DEMAND
QUERY_CACHE_TYPE = 0 | 1 | 2
スレッドにクエリキャッシュ設定を行う。
オプション 説明
0 または OFF 結果のキャッシュや取り出しを行わない。
1 または ON SELECT SQL_NO_CACHE ... クエリを除くすべての結果をキャッシュする。
2 または DEMAND は、 SELECT SQL_CACHE ... クエリのみをキャッシュする。

SQL_AUTO_IS_NULL = 0 | 1
1（デフォルト）に設定すると、WHERE auto_increment_column IS NULL の構造を使用して AUTO_INCREMENT カラムが含まれるテーブルで最後に挿入されたレコードを検索できる。これは、Access などの ODBC プログラムの一部で使用される。

SQL_BIG_SELECTS = 0 | 1
0 に設定すると、長時間要すると見込まれる（オプティマイザによって MAX_JOIN_SIZE の値を超えるレコード数が調べられると見込まれる）SELECT ステートメントが MySQL によって中止される。 これは不適切な WHERE ステートメントが使用された場合に役立つ。新たに接続した場合のデフォルト値は 1で、すべての 1 ステートメントが許可される。

MAX_JOIN_SIZE を DEFAULT 以外の値に設定すると、SQL_BIG_SELECTS が 0 に設定される。

SQL_BUFFER_RESULT = 0 | 1
SQL_BUFFER_RESULT は SELECT ステートメントからの結果を強制的にテンポラリテーブルに入れる。これは、MySQL によるテーブルロック解除の早期化と、結果セットのクライアントへの送信に長時間かかる場合に役立つ。

SQL_LOG_BIN = 0 | 1
0 に設定すると、クライアントに SUPER 特権がある場合、そのクライアントに関するバイナリログへのログ記録が行われなくなる。

SQL_LOG_OFF = 0 | 1
1 に設定すると、クライアントに SUPER 特権がある場合、そのクライアントに関する標準ログへのログ記録が行われなくなる。

SQL_LOG_UPDATE = 0 | 1
0 に設定すると、クライアントに SUPER 特権がある場合、そのクライアントに関する更新ログへのログ記録が行われなくなる。 バージョン 5.0 以降はこの変数が廃止されている。

SQL_QUOTE_SHOW_CREATE = 0 | 1
1 に設定すると、SHOW CREATE TABLE でテーブル名とカラム名がクオートされる。これはデフォルトでオンになっており、マルチバイト文字などを使用したカラム名をもつテーブルのレプリケーションを可能にする。 「4.6.8.8 SHOW CREATE TABLE」 。

SQL_SAFE_UPDATES = 0 | 1
1 に設定すると、MySQL によって WHERE 節でキーや LIMIT を使用しない UPDATE または DELETE ステートメントが中止される。これで、手入力で SQL ステートメントを作成した場合に誤った更新の捕捉が実現される。

SQL_SELECT_LIMIT = value | DEFAULT
SELECT ステートメントから返されるレコードの最大数。SELECT に LIMIT 節がある場合、SQL_SELECT_LIMIT の値よりも LIMIT のほうが優先される。新たに接続した場合のデフォルト値は "無制限" である。この制限を変更した場合、DEFAULT の SQL_SELECT_LIMIT 値を使用してデフォルト値をリストアできる。

TIMESTAMP = timestamp_value | DEFAULT
クライアントに時間を設定する。これは、レコードのリストアにバイナリログを使用する場合、オリジナル(ログが記録された時点)のタイムスタンプにするために使用される。timestamp_value は MySQL タイムスタンプではなく Unix 基準時点のタイムスタンプにする必要がある。

TIME_FORMAT = format_str
サーバで TIME 値を文字列に変換する方法を設定する。この変数は、グローバルオプション、ローカルオプション、コマンドラインオプションのいずれでも使用できる。format_str の指定には GET_FORMAT() 関数の使用が便利である。 「6.3.4 日付と時刻関数」 節 参照 を参照。

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

5.6 ディスク関連の問題

5.6.1 シンボリックリンクの使用   

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

5.6.1 シンボリックリンクの使用

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

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

5.6.1.1 Unix 上のデータベースに対するシンボリックリンクの使用   
5.6.1.2 Unix 上のテーブルに対するシンボリックリンクの使用   
5.6.1.3 Windows 上のデータベースに対するシンボリックリンクの使用   

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

5.6.1.1 Unix 上のデータベースに対するシンボリックリンクの使用

Unix の場合、データベースのシンボリックリンクは、まず、空き領域のあるディスクにディレクトリを作成し、次に MySQL データベースディレクトリからそのディレクトリへのシンボリックリンクを作成します。

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

MySQL は、1 つのディレクトリに対して複数のデータベースをリンクさせることをサポートしていません。データベースディレクトリをシンボリックリンクに置換すると、複数のデータベースへシンボリックリンクを張らない限り、問題なく機能します。 仮に MySQL データディレクトリにデータベース db1 がある場合に、db1 を指すシンボリックリンク db2 を作成するとします。

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

これで、db1 のテーブル tbl_a が、db2 のテーブル tbl_a としても表示されます。あるスレッドで db1.tbl_a が更新され、別のスレッドで db2.tbl_a が更新されると、問題が発生します。

このようにすることが実際に必要な場合は、`mysys/mf_format.c' で次のコードを変更する必要があります。

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

これを次のようにします。

 
if (1)

Windows では、-DUSE_SYMDIR を使用して MySQL をコンパイルして、ディレクトリへの内部シンボリックリンクを使用できます。これによって、複数のデータベースを複数のディスクに配置できるようになります。 「5.6.1.3 Windows 上のデータベースに対するシンボリックリンクの使用」 節 参照 。

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

5.6.1.2 Unix 上のテーブルに対するシンボリックリンクの使用

MySQL 4.0 より前は、テーブルのシンボリックリンクの実行を非常に慎重に行う必要がありました。シンボリックリンクが行われているテーブルで、ALTER TABLE、REPAIR TABLE、あるいは OPTIMIZE TABLE を実行する際に、シンボリックリンクが削除され、オリジナルファイルに置換されるという問題がありました。これらのステートメントは、データベースディレクトリにテンポラリファイルを作成し、ステートメントの操作が完了するとオリジナルファイルとテンポラリファイルの置換が行われる仕様であるため、この問題が発生しました。

realpath() の呼び出しの機能が完全でないシステムではテーブルのシンボリックリンクを行わないでください（少なくとも、Linux と Solaris では realpath() がサポートされています）。

MySQL 4.0 では MyISAM テーブルでのみシンボリックリンクが完全サポートされています。これ以外のテーブル型で上記のコマンドを使用すると、予想外の問題の発生の恐れがあります。

MySQL 4.0 でのシンボリックリンクの処理は、次のように機能します（ほとんどが MyISAM テーブルのみに適しています）。

サポートされていない事項

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

5.6.1.3 Windows 上のデータベースに対するシンボリックリンクの使用

MySQL バージョン 3.23.16 から、MySQL ディストリビューションの mysqld-max および mysql-max-nt サーバが -DUSE_SYMDIR オプションでコンパイルされるようになりました。これにより、シンボリックリンクを設定して別のディスクにデータベースディレクトリを配置できます。 リンクの設定手順は異なりますが、機能は Unix のシンボリックリンクと同様です。

Windows では、対象ディレクトリへのパスが記載されたファイルを作成して MySQL データベースに対するシンボリックリンクを作成します。ファイル名 `db_name.sym' を使用してデータディレクトリにファイルを保存します。この db_name はデータベース名です。

たとえば、MySQL データディレクトリが `C:\mysql\data' で、データベース foo を `D:\data\foo' に配置する場合、パス名 D:\data\foo\ が記載されたファイル `C:\mysql\data\foo.sym' を作成する必要があります。このようにすると、データベース foo に作成されているすべてのテーブルが `D:\data\foo' に作成されます。 この作業には `D:\data\foo' ディレクトリが存在している必要があります。また、データベース名のディレクトリが MySQL データディレクトリにあるとシンボリックリンクが使用されなくなるため、注意が必要です。言い換えると、すでに `foo' という名前のデータベースディレクトリがデータディレクトリにある場合、これを `D:\data' に移動しないとシンボリックリンクが有効にならないことになります（問題を回避するため、データベースディレクトリの移動時はサーバを実行しないでください）。

どのテーブルを開く場合でも速度が低下するため、これをサポートするように MySQL をコンパイルした場合でも、デフォルトでは有効化されていません。シンボリックリンクを有効化するには、`my.cnf' または `my.ini' ファイルを次のエントリに入力する必要があります。

 
[mysqld]
symbolic-links

MySQL 4.0 では、シンボリックリンクがデフォルトで有効化されています。不要の場合は、skip-symbolic-links オプションで無効化できます。

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

2004年 10月 9日 texi2html にて作成