無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

第6章 最適化

目次

6.1. 最適化の概要
6.1.1. MySQL の設計上の制約とトレードオフ
6.1.2. 移植性のためのアプリケーション設計
6.1.3. MySQL 使用実績
6.1.4. MySQL ベンチマークスィート
6.1.5. 独自のベンチマークの使用
6.2. SELECTステートメントおよびその他のクエリの最適化
6.2.1. EXPLAINを使用して、クエリを最適化する
6.2.2. クエリパフォーマンスの推定
6.2.3. SELECTクエリの速度
6.2.4. WHERE 節最適化
6.2.5. Range 最適化
6.2.6. インデックス結合最適化
6.2.7. IS NULL最適化
6.2.8. DISTINCT最適化
6.2.9. LEFT JOINRIGHT JOIN最適化
6.2.10. 入れ子結合最適化
6.2.11. 外側Join 単純化
6.2.12. ORDER BY最適化
6.2.13. GROUP BY最適化
6.2.14. LIMITの最適化
6.2.15. テーブルスキャンを避ける方法
6.2.16. INSERTステートメントの速度
6.2.17. UPDATEステートメントの速度
6.2.18. DELETEステートメントの速度
6.2.19. その他の最適化のヒント
6.3. ロック関連の問題
6.3.1. MySQL のテーブルロック方法
6.3.2. テーブルロック関連の問題
6.3.3. 同時挿入
6.4. データベース構造の最適化
6.4.1. 設計上の選択
6.4.2. データの小型化
6.4.3. カラムインデックス
6.4.4. 複合インデックス
6.4.5. MySQLにおけるインデックスの使用
6.4.6. MyISAMキーキャッシュ
6.4.7. MyISAMインデックス統計コレクション
6.4.8. MySQL でのテーブルのオープンとクローズの方法
6.4.9. 1 つのデータベースに大量のテーブルを作成した場合の欠点
6.5. MySQL サーバの最適化
6.5.1. システム、コンパイル時間およびスタートアップパラメータのチューニング
6.5.2. サーバパラメータのチューニング
6.5.3. クエリオプティマイザパフォーマンスの管理
6.5.4. MySQL の速度に対するコンパイルとリンクの影響
6.5.5. MySQL でのメモリの使用
6.5.6. MySQLの DNS の使用
6.6. ディスク関連の問題
6.6.1. シンボリックリンクの使用

最終的に全てのシステムの理解が必要なため、最適化は複雑な作業です。ユーザのシステムやアプリケーションの知識でローカル最適化は可能ですが、システムをさらに最適化するには、システムに関する深い知識が必要になります。

この章ではMySQLを最適化するためのさまざまな手法を、例を交えて説明します。取得には更なる努力が必要ですが、必ずシステムの速度を上げる方法は数多く存在することを覚えていてください。

6.1. 最適化の概要

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

  • ディスクシーク。ディスクが 1 つのデータを検索するには時間がかかります。最新のディスクでは、通常これにかかる平均時間が 10 ms 未満であるため、理論的には 1 秒間に 100 のシークを実行できることになります。新しいディスクではこの時間の改善が緩やかで、1 つのテーブルの最適化が非常に困難です。これを最適化する方法として、複数のディスクにデータを分散することが挙げられます。

  • ディスクの読み取りと書き込み。ディスクが適切な位置にある場合、データの読み取りが必要になります。最新のディスクでは、1 つのディスクで約 10–20MB/s の読み取りが可能になります。これは、複数のディスクから並行した読み取りが可能であるため、シークに比較して最適化が容易に行えます。

  • CPU サイクル。メインメモリにデータがある場合(またはすでにそこに存在している場合)、結果を得るためには処理が必要になります。メモリと比較してテーブルが小さい場合は、最も一般的な制限要因になります。しかし、テーブルが小さくても、一般に速度上の問題は発生しない。

  • メモリ帯域幅。 CPU キャッシュの適正量より多く CPU がデータを必要とする場合、メインメモリの帯域幅がボトルネックになる。これは、ほとんどのシステムに一般的な問題ではないが、認識しておく必要がある。

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

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

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

  • すべてのカラムにデフォルト値がある。

  • カラムに対して'正しくない' 値を挿入した場合や、数値列の数値が大きすぎる場合、MySQL ではエラーを発生するのではなく、「とりうる可能な値のうちの最適値」に値を設定する。数値の場合は 0 で、可能な最小値か最大値になる。文字列の場合は、空白文字かカラムの最大長さにあわせた文字列になる。

  • 計算式はすべて、エラー状態を表示するのではなく、使用可能な値を返す。たとえば、1/0 の場合は、NULLを返す。

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

6.1.2. 移植性のためのアプリケーション設計

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

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

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

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

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

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

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

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

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

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

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

6.1.3. MySQL 使用実績

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

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

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

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

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

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

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

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

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

6.1.4. MySQL ベンチマークスィート

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

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

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

  • ベンチマークスィートは、MySQL ソースディストリビューションによって提供されます。http://dev.mysql.com/downloads/のサイトからリリースされているディストリビューションをダウンロードするか、現在の開発ツリーを使用できる。(詳しくは項2.9.3. 「開発ソース ツリーからのインストール」をご確認ください。)

  • ベンチマークスクリプトは Perl で作成され、データベースサーバへのアクセスには Perl DBI モジュールを使用しているため、DBI のインストールが必要である。テスト対象のサーバのそれぞれにサーバ専用の DBD ドライバも必要である。 たとえば、MySQL、PostgreSQL、DB2 をテストするには、DBD::mysqlDBD::PgDBD::DB2のモジュールをインストールする必要がある。 項2.15. 「Perl のインストールに関する注釈」を参照してください。

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

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

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

shell> perl run-all-tests --help

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

  • サポートされるデータ型

  • サポートされるインデックス数

  • サポートされる関数

  • 使用可能なクエリのサイズ

  • 使用可能な VARCHARカラムのサイズ

6.1.5. 独自のベンチマークの使用

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

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

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

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

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

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

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

第 1 にすべてのクエリに影響を及ぼすことが 1 つあります。アクセス権システムのセットアップの複雑性が増すほど、オーバヘッドも増加します。 GRANTステートメントを発行する際に単純なアクセス権を使用することで、クライアントがステートメントを事項する際のMySQLにアクセス権確認オーバーヘッドを減らすことができます。例えば、テーブルレベルやカラムレベルの特権を許可したくない場合、サーバはtables_privcolumns_privテーブルの内容を確認する必要はまったくありません。同じように、どのアカウントにもリソース制限を設けない場合、サーバは性能リソースカカウンティングを行う必要がありません。大量の処理が必要なときは、GRANT を使用しないことで時間を節約できる場合もあります。

明示的な MySQL 関数に関わる問題がある場合は、常に mysqlクライアントでBENCHMARK()関数の計時を行うことができます。 その構文はBENCHMARK(loop_count,表現)。返される値は常に0ですが、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()はクエリに関数上の問題があるかどうかを調べる際に最適のツールです。

6.2.1. EXPLAINを使用して、クエリを最適化する

EXPLAIN tbl_name

または

EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options

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

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

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

  • EXPLAIN PARTITIONSはMySQL 5.1.5から提供されています。区割りされたテーブルのクエリを調べるときに便利です。詳細については、 項15.3.4. 「パーティション情報の取得」 をご参照ください。

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

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

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

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

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

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

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

  • id

    SELECT識別子。クエリ内におけるこの SELECTの順序番号。

  • select_type

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

    SIMPLE単純なSELECT (UNIONやサブクエリを使用しない)。
    PRIMARY最外部のSELECT
    UNION内の第2およびそれ以降のSELECTステートメント。
    DEPENDENT UNIONUNION内の第2およびそれ以降のSELECTステートメント内のUNION、外側のサブクエリに依存する。
    UNION RESULTUNIONの結果。
    SUBQUERYサブクエリ内の第一SELECT
    DEPENDENT SUBQUERY第1SELECT、外側のサブクエリに依存する。
    DERIVED派生テーブルSELECTFROM節内のサブクエリ)
    UNCACHEABLE SUBQUERY結果がキャッシュされず、外側のクエリの各行ごとに再評価されるサブクエリ。

    DEPENDENTは主に、相互に関係するサブクエリの使用を表します。項12.2.8.7. 「相関サブクエリ」を参照してください。

    依存型サブクエリ」の評価はUNCACHEABLE SUBQUERY評価とは異なります。「DEPENDENT SUBQUERY」に関しては、外側コンテキストの変数の値が異なるたびに、一回のみサブクエリの再評価が行われます。UNCACHEABLE SUBQUERYに関しては、サブクエリは外側コンテキストの各行ごとに再評価されます。サブクエリのキャッシュアビリティは項4.13.1. 「クエリ キャッシュの動作」で記述される制限によります。例えば、ユーザ変数に参照することでサブクエリがキャッシュできなくなります。

  • テーブル

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

  • type

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

    • システム

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

    • const

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

      constPRIMARY KEY/UNIQUEキーを定数と比較する場合に使用される。 .下記のクエリでは、tbl_nameconstテーブルとして使用できる。

      SELECT * FROM tbl_name WHERE primary_key=1;
      
      SELECT * FROM tbl_name
        WHERE primary_key_part1=1 AND primary_key_part2=2;
      
    • eq_ref

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

      =演算子と比較されるインデックスの張られたカラムには、eq_refを使用できる。 較対象のアイテムは定数でも、このテーブル以前に読み取られたテーブルのカラムを使用する式でもかまわない。 下記の例では、ref_tableeq_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は、インデックスの左端の先頭部分のみが結合で使用される場合、またはインデックスが UNIQUEPRIMARY KEYではない場合(すなわち、この結合において、インデックス値から1つのレコードをSELECTできない場合)に使用される。この結合型は、使用されるインデックスと一致するレコードが数レコードしかない場合に適している。

      =あるいは<=>演算子と比較されるインデックスの張られたカラムには、ref を使用できる。 下記の例では、MySQLはref_tablerefが使用される。

      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を使用したレコードの補足検索も追加で実行される。 この結合型の最適化は主としてサブクエリを解決する場合に使用される。 下記の例では、MySQLはref_tableref_or_nullが使用される。

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

      項6.2.7. 「IS NULL最適化」参照。

    • index_merge

      この結合型はインデックス併合最適化が使用されたことを示しています。この場合、出力行のkeyカラムは使用されたインデックスのリストが含まれ、key_lenには使用されたインデックスの最長キー部分が含まれます。詳細は 項6.2.6. 「インデックス結合最適化」 をご覧ください。

    • unique_subquery

      この型は、下記のフォームでINサブクエリの代わりに、refを使用します。

      value IN (SELECT primary_key FROM single_table WHERE some_expr)
      

      unique_subqueryは、効率化のためサブクエリの代わりをつとめるインデックスルックアップ関数です。

    • index_subquery

      この結合型はunique_subqueryに似ています。INサブクエリの代わりに使用されますが、下記のサブクエリのフォームでユニークではないインデックスで使用できます。

      value IN (SELECT key_column FROM single_table WHERE some_expr)
      
    • range

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

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

      SELECT * FROM tbl_name
        WHERE key_column = 10;
      
      SELECT * FROM tbl_name
        WHERE key_column BETWEEN 10 and 20;
      
      SELECT * FROM tbl_name
        WHERE key_column IN (10,20,30);
      
      SELECT * FROM tbl_name
        WHERE key_part1= 10 AND key_part2 IN (10,20,30);
      
    • index

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

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

    • ALL

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

  • possible_keys

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

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

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

  • key

    keyカラムは、MySQL が実際に使用を決定したキー(インデックス)を示す。 MySQL が行をルックアップするためpossible_keysインデックスを使用した場合、キー値としてそのインデックスがリストされる。

    keypossible_keys 値に存在しないインデックスを指名する可能性もあります。これはpossible_keysインデックスのうちどれも行をルックアップするのに適していない場合におこりますが、クエリに選択された全てのカラムは他のインデックスのカラムになります。つまり、指名されたインデックスが選択されたカラムをカバーします。どの行を取得するか判別するのに使用されていなくとも、データ行スキャンよりもインデックススキャンの方が効率的です。

    InnoDBでは、クエリがプライマリキーを選択していてもセカンダリインデックスが選択されたカラムをカバーするかもしれません。これはクエリがプライマリキーを選択した場合もありえるのは、InnoDBが各セカンダリインデックスと共にプライマリキー値も保存するからです。MySQLがクエリを効率的に実行するインデックスを見つけられなかった場合、このkeyNULLになる。

    MySQLで possible_keysカラムに記載されたキーが使用されるように強制するには、クエリでFORCE INDEXUSE INDEX、またはIGNORE INDEXを使用する。 項12.2.7. 「SELECT 構文」を参照してください。

    MyISAMテーブルには、ANALYZE TABLEを実行することでオプティマイザでより適したインデックスを選択する際役立つ。MyISAMテーブルに関しても、myisamchk --analyzeは同じことをします。項12.5.2.1. 「ANALYZE TABLE 構文」項4.9.4. 「テーブル保守とクラッシュ リカバリ」 を参照して下さい。

  • key_len

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

  • ref

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

  • rows

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

  • filtered

    filteredカラムはテーブルの状態によってフィルターされるテーブル行のパーセンテージ(予想)を表示します。つまり、rowsは検査された行の予想数を表示し、rows × filtered / 100は前のテーブルと結合する行の数を表示します。EXPLAIN EXTENDEDを使用すると、このカラムが表示されます。(MySQL 5.1.12の新しい機能です。)

  • Extra:

    このカラムには、MySQL でどのようにクエリが解決されるかに関する追加情報が記載される。下記のリストはこのカラムで表示される可能性のある値を説明する。クエリの速度をできる限り上げたい場合は、Using filesortUsing temporaryExtra値に注目してください。

    • Distinct

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

    • const tablesを読んだ後 Impossible WHERE発見

      MySQL は全てのconst (あと、system) テーブルを読んだ後、WHERE節が常に偽となります。

    • No tables

      クエリにはFROM節がないか、FROM DUAL節があります。

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

    • range checked for each record (index map: N)

      MySQL で使用に適した実際のインデックスを検索できなかったことを示す。代替として、先行テーブルのレコードの組み合わせのそれぞれに対して、使用するインデックス(存在する場合)range またはindex_mergeのチェックが実行され、このインデックスがテーブルからのレコードの取り出しに使用される。非常に高速ではないが、インデックスなしの結合と比較すると高速である。 適用基準は項6.2.5. 「Range 最適化」項6.2.6. 「インデックス結合最適化」で説明されています。ただし、これは前テーブルの全てのカラム値が知られており、定数であるという前提においてです。

    • Select tables optimized away

      クエリはMyISAM用に、インデックスで解決された集約ファンクション(MIN()MAX())そしてCOUNT(*)があり、GROUP BY節は含みませんでした。オプティマイザは1つの行のみが返されるべきと判断しました。

    • Using filesort

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

    • Using index

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

    • Using temporary

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

    • Using where

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

    • Using sort_union(...)Using union(...)Using intersect(...)

      これらはindex_merge結合型でインデックススキャンがどのように併合されるかを示しています。詳細は 項6.2.6. 「インデックス結合最適化」 を参照。

    • Using index for group-by

      Using indexを使用してテーブルをアクセスする方法に似て、Using index for group-byはMySQLが余分なディスクアクセスを実際のテーブルに行うことなく、GROUP BYまたはDISTINCTクエリのカラムを全て取得することができるインデックスを見つけたことを意味します。加えて、インデックスは各グループにとって最も効率的に使われるので、数種類のインデックスしか読まれません。詳細については、項6.2.13. 「GROUP BY最適化」をご参照ください。

    • Using where with pushed condition

      このアイテムはNDB Clusterテーブルにのみ適用されます。それはMySQL クラスタがcondition pushdownを使用して行う、インデックスのないカラムと定数を直接比較(=)の効率化を図ることを意味します。その場合、状態はクラスターのデータノードに「押し戻され」ており、全てのパーティションで同時に評価されます。これはマッチしない行をネットワーク上で送る必要を無くし、コンディションプッシュダウンが使える状態にあり、使用しないケースでそのようなクエリの速度を5乗から10乗に増やす。

      以下のように定義されたクラスタテーブルがあるとします。

      CREATE TABLE t1 (
          a INT, 
          b INT, 
          KEY(a)
      ) ENGINE=NDBCLUSTER;
      

      この場合、コンディションプッシュダウンは下記のようなクエリで使用できます。

      SELECT a,b FROM t1 WHERE b = 10;
      

      これはEXPLAIN SELECTの出力で見られます。例えば

      mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t1
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 10
              Extra: Using where with pushed condition
      

      コンディションプッシュダウンは下記の2つのクエリと一緒には使用できません

      SELECT a,b FROM t1 WHERE a = 10;
      SELECT a,b FROM t1 WHERE b + 1 = 10;
      

      この二つのクエリのうち最初のものに関しては、インデックスがaカラムに存在するため、コンディションプッシュダウンは適用できません。2番目のクエリの場合、インデックスのないカラムbに関する比較は直接的でないため、コンディションプッシュダウンが適用できません。(ただし、b + 1 = 10WHERE 節内でb = 9に減らす場合は適用されます。)

      ただし、>または<演算子を使用している定数とインデックスカラムが比較された場合、コンディションプッシュダウンが使用される場合もあります。

      mysql> EXPLAIN SELECT a,b FROM t1 WHERE a<2\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t1
               type: range
      possible_keys: a
                key: a
            key_len: 5
                ref: NULL
               rows: 2
              Extra: Using where with pushed condition
      

      コンディションプッシュダウンに関して、以下のことに留意してください。

      • コンディションプッシュダウンはMySQLクラスタにのみ関連しており、他の保存エンジンを使用するテーブルに対してクエリを実行するときは起こりえません。

      • コンディションプッシュダウン機能はデフォルトでは使用されません。起動するには、mysqld--engine-condition-pushdownオプションで使用するか、以下のステートメントを実行してください。

        SET engine_condition_pushdown=On;
        

        :コンディションプッシュダウンはBLOBTEXTタイプのどのカラムに対してもサポートされていません。

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

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

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

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

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

  • 比較対象のカラムは以下のように宣言されます。

    テーブルカラムデータ型
    ttActualPCCHAR(10)
    ttAssignedPCCHAR(10)
    ttClientIDCHAR(10)
    etEMPLOYIDCHAR(15)
    doCUSTNMBRCHAR(15)
  • テーブルには以下のインデックスがあります。

    テーブルインデックス
    ttActualPC
    ttAssignedPC
    ttClientID
    etEMPLOYID (プライマリキー)
    doCUSTNMBR (プライマリキー)
  • tt.ActualPC値の分布が均一ではない。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

mysql> ANALYZE TABLE tt;

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

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

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

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

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

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

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

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

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

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

6.2.3. SELECTクエリの速度

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

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

  • To MySQL によるクエリの最適化を容易にするには、関連データをロードした後にテーブルに対して ANALYZE TABLEあるいはmyisamchk --analyzeを実行する。これはインデックスのために、同じ値があるレコードの平均値を更新する(ユニークインデックスの場合、これは常に 1 になる)。MySQL はこれを使用して、2 つのテーブルを '非定数式' で接続する際に選択するインデックスを判定する。 SHOW INDEX FROM tbl_nameを実行しCardinality値を調べるとmyisamchk --description --verboseはインデックスの分布情報を表示する。

  • インデックスに従ってインデックスとデータをソートするには myisamchk --sort-index --sort-records=1(インデックス 1 でソートする場合)を使用する。速度を上げるには、すべてのレコードの読み取りにユニークインデックスを使用し、そのインデックスに従った順序で読み取りを行うように推奨される。ただし、このソートでは書き込みの最適化はできず、テーブルが大きい場合は時間がかかる。

6.2.4. WHERE 節最適化

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

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

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

  • 不要なかっこの削除。

       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)
    
  • 定数の折りたたみ。

       (a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5
    
  • 定数条件の削除(定数の折りたたみに必要)。

       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
    -> B=5 OR B=6
    
  • インデックスが使用する定数式が評価されるのは、1 回に限られる。

  • COUNT(*) WHEREがない単一テーブルの COUNT(*) は、MyISAMHEAP テーブルのテーブル情報から直接取り出される。 これは、テーブル 1 つのみで使用する場合はすべての NOT NULL式でも実行される。

  • 無効定数式の早期検出。MySQL は実行不可能な SELECTステートメントがある場合、それを迅速に検出し、結果としてレコードを返さない。

  • GROUP BYまたはグループ関数(COUNT()MIN())を使用しない場合、HAVINGWHEREとマージされる。

  • サブ結合のそれぞれに、単純な WHEREが構造化され、サブ結合ごとに迅速に WHERE評価を取得し、可能な限り迅速にレコードをスキップする。

  • クエリ内の他のすべてのテーブルの前に、まず、すべての定数テーブルが読み込まれる。定数テーブルとは以下のものを指す。

    • 空白テーブルまたは 1 行のみのテーブル。

    • UNIQUEインデックスまたは PRIMARY KEYを使う WHERE節とともに使用されるテーブルで、インデックス部分のすべてが定数式とともに使用され、そのインデックス部分が NOT NULLとして定義されている場合。

    以下のテーブルはすべて定数テーブルとして使用される。

    SELECT * FROM t WHERE primary_key=1;
    SELECT * FROM t1,t2
      WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
    
  • テーブルを結合する最適な結合の組み合わせは、すべての可能性を試行してみることで発見される。ORDER BYおよび GROUP BY内の全てのカラムが 1 つのテーブルに存在する場合、結合を行う時は第一にこのテーブルが選ばれる。

  • ORDER BY節とそれと異なる GROUP BY節がある場合、あるいは、ORDER BYまたは GROUP BYに結合キューの第 1 テーブルとは異なるテーブルのカラムが含まれている場合は、テンポラリテーブルが作成される。

  • SQL_SMALL_RESULTを使用する場合、MySQL ではメモリ内のテンポラリテーブルが使用される。

  • オプティマイザがテーブルスキャンをしたほうが効率的と判断しない限り、テーブルインデックスごとにクエリが行われ、スパンがレコードの 30% 以上である最適インデックスが使用される。しかし、現在では一定の率でインデックスかスキャンを使用するか判断されます。今バージョンのオプティマイザはより複雑で、テーブルサイズ、行数、そしてI/Oブロックサイズを基準に推定します。

  • 状況によっては、MySQL でデータファイルの参照もせずにインデックスからレコードを読み取れる場合もある。インデックスから使用されるカラムのすべてが数値型の場合、クエリの解決にはインデックスツリーのみが使用される。

  • レコードのそれぞれが出力される前に、HAVING節と一致しないレコードはスキップされる。

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

SELECT COUNT(*) FROM tbl_name;

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

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

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

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

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

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

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

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

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

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

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

6.2.5. Range 最適化

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

6.2.5.1. シングルパートインデックスのためのRangeアクセスメソッド

シングルパートインデックスでは、インデックス値インターバルはWHERE節内の対応する状態で表現されます。よって「インターバル」よりもrange 状態が使用されます。

シングルパートインデックスのためのrange状態の定義は以下のとおりです。

  • BTREEHASHの両インデックスにとって、=, <=>INIS NULL、またはIS NOT NULL演算子を使用した際の定数値の主部分の比較はrange状態です。

  • BTREEインデックスにとって、><, >=<=, BETWEEN!=<>演算子、またはLIKE 'pattern' ('pattern' がワイルドカードで始まらないとき)を使用しての定数値の主部分の比較は、range状態です。

  • 全インデックスの種類にとって、ORまたはANDで結合されたマルチレンジ状態は、あるレンジ状態を形成します。

先の説明の 「定数値」は以下の1つを意味しています。

  • クエリ文字列の定数

  • 同じ結合のconstまたはsystemテーブルのカラム

  • 相関しないサブクエリの結果

  • 以前の型のサブ表現からのみ生成された表現

ここにWHERE節内でレンジ状態のクエリの例を以下に示します。

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

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

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

非定数値が定数伝播フェーズ中に定数に変換されることに留意してください。

MySQLはWHERE節から、各インデックスよりレンジ状態を抽出しようとします。抽出プロセスの最中、レンジ状態を生成することができない状態は破棄され、重複するレンジを生成する状態は結合され、そして空のレンジを生成する状態は取り除かれます。

key1がインデックスカラムでnonkeyがインデックスカラムでない、下記のステートメントを考慮に入れてください。

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

key1の抽出プロセスは以下のとおりです。

  1. オリジナルのWHERE節で始めてください。

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
    (key1 < 'bar' AND nonkey = 4) OR
    (key1 < 'uux' AND key1 > 'z')
    
  2. nonkey = 4key1 LIKE '%b'はレンジスキャンに使用できませんので、取り除いてください。TRUEと置き換えることが、正しく取り除く方法です。こうすることによって、レンジスキャン中に適合する行を見落としません。TRUEで置き換えたとき、以下になります。

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
    (key1 < 'bar' AND TRUE) OR
    (key1 < 'uux' AND key1 > 'z')
    
  3. 常に真か偽のコラップス状態

    • (key1 LIKE 'abcde%' OR TRUE)は常に真です

    • (key1 < 'uux' AND key1 > 'z')は常に偽です

    定数でこれらの状態を置き換えると、以下になります。

    (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
    

    不必要なTRUEFALSE定数を取り除くことで、以下になります。

    (key1 < 'abc') OR (key1 < 'bar')
    
  4. 重複するインターバルを1つに結合することでレンジスキャンで使用できる最終状態が生成されます。

    (key1 < 'bar')
    

一般的にレンジスキャンで使用される状態は(そして前の例で説明されたとおり)、WHERE節よりも制限されません。MySQLはレンジ条件を満たすが、WHERE節を完全に満たさない行にフィルタをかけるために追加でチェックを行います。

レンジ条件抽出アルゴリズムは任意のデプスの入れ子 AND/OR 生成子を取り扱うことができ、その出力はWHERE節内でどの順序で条件が現れるかに影響されません。

6.2.5.2. 複合パートインデックスのためのレンジアクセスメソッド

複合パートインデックスのレンジ条件はシングルパートインデックスのレンジ条件の拡張です。複合パートインデックスのレンジ条件はインデックス行が1つもしくは複数のキータプルインターバルに含まれるよう制限します。キータプルインターバルはキータプルセット上で、インデックスからの順序づけを使用して定義されます。

例えば、複合パートインデックスがkey1(key_part1key_part2key_part3)として定義され、以下のキータプルのセットがキー順序でリストされたとします。

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

key_part1 = 1の条件は以下のインターバルを定義します。

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

インターバルは前データセットの4、5、そして6番目タプルをカバーし、レンジアクセスメソッドで使用できます。

それに引き換え、key_part3 = 'abc'条件はシングルインターバルを定義せず、レンジアクセスメソッドで使用できません。

以下はレンジ条件が複合パートインデックスでどのように働くかの詳細を説明します。

  • HASHインデックスでは、同値を含む各インターバルが使用できます。これは以下のフォームをとる条件のためのみインターバルが生成できることを意味してます。

        key_part1 cmp const1
    AND key_part2 cmp const2
    AND ...
    AND key_partN cmp constN;
    

    ここでは、const1const2、… は定数で、cmp=<=>、またはIS NULL比較演算子の1つであり、条件は全てのインデックスパートをカバーします。(つまり、N条件があり、各N-パートインデックスごとに1つあります。.)例えば、以下は3パートHASHインデックスのレンジ条件です。

    key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
    

    定数の定義は、項6.2.5.1. 「シングルパートインデックスのためのRangeアクセスメソッド」を参照してください。

  • BTREEインデックスでは、インターバルはANDと結合された条件に使用できることがあります。これは、各条件がキーパートと定数値を、=, <=>IS NULL><>=<=, !=<>BETWEEN、またはLIKE 'pattern'を使用して比較した場合です ('pattern' がワイルドカードで始まらない場合)。条件にマッチする行を全て含むシングルキータプルを判別できるかぎり、インターバルが使用できます。(あるいは、<> or !=が使用されたいれば2インターバル)。例えば、以下の条件では:

    key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
    

    シングルインターバルは:

    ('foo',10,10) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
    

    作成されたインターバルが初期条件よりも行が多い可能性があります。例えば、初期条件を満たさない前インターバルが('foo', 11, 0)の値を含んでいます。

  • インターバルに含まれる行のセットをカバーする条件がORで結合された場合、インターバルの結合に含まれる行のセットをカバーする条件を生成します。ANDで条件が結合された場合、インターバルの交差点に含まれる行のセットをカバーする条件を生成します。例えば、この2パートインデックスの条件について:

    (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
    

    インターバルは:

    (1,-inf) < (key_part1,key_part2) < (1,2)
    (5,-inf) < (key_part1,key_part2)
    

    この例では、最初のラインのインターバルはレフトバウンドに1キーパートを使用し、ライトバウンドには2キーパートを使用します。2番目のラインのインターバルは1キーパートのみ使用します。EXPLAIN 出力のkey_lenカラムは、使用されたキープリフィックスの最大長を示しています。

    場合によって、key_lenはキーパート使用されたことを示すことがありますが、ユーザが期待していたとおりではないかも知れません。key_part1key_part2NULLだったとします。key_lenカラム次の条件の2キーパート長を表示します。

    key_part1 >= 1 AND key_part2 < 2
    

    しかし、実際条件以下に変換されます。

    key_part1 >= 1 AND key_part2 IS NOT NULL
    

項6.2.5.1. 「シングルパートインデックスのためのRangeアクセスメソッド」はシングルパートインデックスでレンジ条件のインターバルを結合か削除の際の最適化がどのように実行されるかを記述しています。マルチパートインデックスのレンジ条件にはアナログステップが実行されます。

6.2.6. インデックス結合最適化

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

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

例:

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

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

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

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

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

  • Using intersect(...)

  • Using union(...)

  • Using sort_union(...)

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

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

  • あるキーでレンジスキャンが可能な場合、インデックス結合は考慮されません。例えば、以下のクエリでは:

    SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
    

    このクエリでは、2つのプランが考えられます。

    • (goodkey1 < 10 OR goodkey2 < 20)条件を使用したインデックス結合スキャン。

    • badkey < 30条件を使用したレンジスキャン。

    ただし、オプティマイザは2つ目のプランしか考慮しません。

  • ユーザのクエリに複雑なAND/OR入れ子を持つWHERE節があり、MySQLが最適なプランを選択しない場合、以下のID法を使用して定義を分布してみてください。

    (x AND y) OR z = (x OR z) AND (y OR z)
    (x OR y) AND z = (x AND z) OR (y AND z)
    
  • インデックス結合は、フルテキストインデックスには適用されません。将来的にリリースされるMySQLのバージョンでカバーできるよう、拡張する予定です。

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

6.2.6.1. インデックス結合共通集合アルゴリズム

このアクセスアルゴリズムが使用できるのは、WHERE節が異なるキーの複数のレンジ条件に変換、ANDで結合され、各コンディションは以下の1つ:

  • このフォームでは、インデックスパーツはN個あります(つまり、全てのインデックスパーツがカバーされています。)

    key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
    
  • InnoDBテーブルのプライマリキーをカバーするレンジ条件。

例:

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

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

インデックス共通集合アルゴリズムは全ての使用されたインデックスの同時スキャンを実行し、結合インデックススキャンから受信した行シーケンスの共通集合を生成します。

クエリで使用される全てのカラムが使用済みインデックスでカバーされている場合、完全なテーブル行は取得されません。(EXPLAIN 出力はExtra フィールド内のUsing indexを含んでいます)。そのようなクエリの例です:

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

クエリ内で使用されたカラムを使用されたインデックスがカバーしない場合、完全な行が取得されるのは全てのキーのレンジ条件が満たされたときのみです。

結合条件のうち1つがInnoDBテーブルのプライマリキー上の条件である場合、行取得には使用されず、他の条件を使用して取得した行にフィルターをかけるのに使用されます。

6.2.6.2. インデックス結合ユニオンアクセスアルゴリズム

このアルゴリズムの適用基準はインデックス結合メソッド共通集合アルゴリズムと似ています。このアクセスアルゴリズムが使用できるのは、WHERE節が異なるキーの複数のレンジ条件に変換、ORで結合され、各コンディションは以下の1つ:

  • このフォームでは、インデックスパーツはN個あります(つまり、全てのインデックスパーツがカバーされています。)

    key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
    
  • InnoDBテーブルのプライマリキーをカバーするレンジ条件。

  • インデックス結合共通集合アルゴリズムが適用できる条件

例:

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

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

6.2.6.3. インデックス結合ソートユニオンアクセスアルゴリズム

このアクセスアルゴリズムは、ORによりWHERE 節が複数のレンジ条件に変換されるが、インデックス結合メソッドユニオンアルゴリズムが適用できない場合に使用します。

例:

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

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

ソートユニオンアルゴリズムとユニオンアルゴリズムの違いは、ソートユニオンアルゴリズムはまず、行を返すまえに全ての行のIDを取得しソートしなければいけないところにあります。

6.2.7. IS NULL最適化

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

例:

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

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

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

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

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

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

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

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

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

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

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

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

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

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

6.2.8. DISTINCT最適化

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

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

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

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

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

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

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

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

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

6.2.9. LEFT JOINRIGHT JOIN最適化

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

  • テーブルBはテーブル AAが依存するすべてのテーブルに依存するように設定される。

  • テーブル Aは、LEFT JOIN条件で使用されるすべてのテーブル(Bを除く)に依存するように設定される。

  • LEFT JOIN条件は、テーブル Bからのレコードの取り出し方法の判定に使用される。(言い換えると、WHERE節の条件はいずれも使用されない)。

  • あるテーブルが全てのテーブルの後に読み取られる場合を除き、通常の最適化全てが行われる。依存関係が循環している場合は、MySQL からエラーが出力される。

  • 標準の WHERE最適化すべてが実行される。

  • AWHERE節の条件にマッチするレコードがあり、BON条件にマッチするレコードがない場合、Bのカラムの値が NULLに設定されたレコードが生成される。

  • テーブルのいずれかに存在しないレコードを検索する際に LEFT JOINを使用した場合: col_name IS NULLWHERE節内で、NOT NULLと定義した col_name を column_name IS NULL で評価した場合、 MySQL は LEFT JOIN条件に一致するレコードを 1 つ検索すると、その後はレコードの検索(特定のキー組み合わせの)を停止する。

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

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

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

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

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

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

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

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

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

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

6.2.10. 入れ子結合最適化

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

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

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

は以下と等価です。

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

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

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

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

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

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

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

  • テーブルt1(1)(2)を含む

  • テーブルt2(1,101)行を含む

  • テーブルt3(101)行を含む

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

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

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

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

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

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

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

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

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

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

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

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

以下の表現:

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

はこの表現と等価です:

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

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

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

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

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

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

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

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

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

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

  • インナーjoins のみ関する表現(アウターjoins は不可)については、かっこは取り除けます。かっこを取り除いて左から右に評価を行うことができます(あるいは、テーブルの評価は好きな順序で行えます)。

  • 一般的に、そとがわjoinや外がわjoin と併合された内側join にとっては、同じではありません。かっこを取り除くことで結果を変えることがあるかもしれません。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

以下が入れ子です。

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

そして

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

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

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

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

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

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

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

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

例えば、外側joinでは

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

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

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

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

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

6.2.11. 外側Join 単純化

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • フォームがA IS NOT NULLで、Aが内側テーブルのどれかの属性である場合

  • UNKNOWNのアーギュメントの内1つがNULLで、且つUNKNOWNと評価する内側テーブルへのリファレンスを含む述語である場合

  • null-rejected条件をコンジャンクトとして含んでいる結合子の場合。

  • null-rejected条件のディスジャンクションの場合。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

は以下変換されます。

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

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

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

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

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

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

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

6.2.12. ORDER BY最適化

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

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

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

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

  • 複数のキーに対してORDER BYを実行する場合。

    SELECT * FROM t1 ORDER BY key1, key2;
    
  • 連続しないキー部分に対してORDER BYを実行する場合。

    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
    
  • ASCDESCが混在している場合。

    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
    
  • 行の取り出しに使用されるキーが ORDER BYの実行に使用されるキーと異なる場合。

    SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
    
  • ORDER BYで多くのテーブルとカラムを結合していて、それら全てがレコードの取り出しに使用される最初の非 const テーブルではない場合(これは EXPLAINで出力される最初のテーブルで、かつ、constメソッドを使用していないテーブル)。

  • ORDER BYGROUP BY式が異なる場合。

  • 使用されたテーブルインデックスが、並び順にレコードを格納していないインデックスタイプの場合。(MEMORYテーブルの HASHインデックスなど)。

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

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

  1. WHERE節とマッチする行を読む。

  2. 各行ごとに、クエリに必要なカラムとソートキー値と行ポジションを含むタプル値を記憶する。

  3. ソートキー値でタプルを並べ替える

  4. 並べ替えられた順序で行を取得しますが、テーブルに2度アクセスするよりも、並べ替えられたタプルから必要なカラムを読み取ります。

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

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

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

  • sort_buffer_size変数のサイズを大きくしてください。

  • read_rnd_buffer_size変数のサイズを大きくしてください。

  • tmpdirを変更することで、秋スペースの要領が多い専用のファイルシステムを示してください。この選択肢はラウンドロビン方式で複数のパスを受領します。Unixでは、パスはコロンを含む文字(‘:’) で分けられ、ウィンドウズ、Netware, そして OS/2 ではセミコロンを含む文字(‘;’)で分けられるべきです。 この特性を利用して複数のディレクトリに渡り負荷を分散することができます。:パスは、同ディスクのパーティションで分けられた領域ではなく、異なる物理的なディスクのファイルシステム内のディレクトリに通じます。

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

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

6.2.13. GROUP BY最適化

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

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

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

6.2.13.1. ルースインデックススキャン

GROUP BYを処理する最も効率的な方法は、グループフィールドを直接取得するのにインデックスが使用されるときです。このアクセスメソッドで、MySQLはキーが順序づけられている、インデックス型の特性を利用します。(例えば、BTREE)。この特性は全てのWHERE条件を満たすインデックス内のキーを考慮せずとも、インデックス内のルックアップグループの仕様を可能にします。.このアクセスメソッドはインデックス内のほんの一部のキーを考慮するため、loose index scanと呼ばれています。WHERE節がない場合、ルースインデックススキャン(loose index scan)はナンバーグループの数だけキーを読みます。これはキーの総数よりも少ない数かもしれません。もしWHERE節がレンジ前提を含む場合、(項6.2.1. 「EXPLAINを使用して、クエリを最適化する」内に記述されたrange join型に関するディスカッションを参照してください), a loose index scan はレンジ条件を満たす各グループの最初のキーを参照し、最低限のキーの数を読みます。これは以下の条件下で可能になります。

  • クエリはシングルテーブル上にある。

  • GROUP BYはインデックスの最初の連続部分を含む。(もし、GROUP BYの代わりにクエリがDISTINCT節を含む場合、全ての異なる属性はインデックスの最初を参照する。)

  • 使用される集約ファンクション(ある場合)はMIN()MAX()であり、全て同じカラムを参照します。

  • クエリで参照されたGROUP BY以外のインデックス部分は定数でなければいけません(つまり、定数と等価であるよう参照されなければいけません)が、MIN()あるいはMAX()ファンクションは例外です。

そのようなクエリのEXPLAIN出力はExtra カラム内のUsing index for group-byを示します。

テーブルt1(c1,c2,c3,c4)にインデックスidx(c1,c2,c3)があることを前提に、以下のクエリがこのカテゴリに属します。

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

以下の理由により以下のクエリはこのクイックセレクトメソッドを使用しての実行はできません。

  • MIN()MAX()以外の集約ファンクションが存在します。例えば:

    SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
    
  • 以下で示されるよう、GROUP BY節内のフィールドはインデックスの最初の部分に属するものではありません。

    SELECT c1,c2 FROM t1 GROUP BY c2, c3;
    
  • クエリは、定数と等価ではない、GROUP BY部分の後にくるキー部分を参照しています。

    SELECT c1,c3 FROM t1 GROUP BY c1, c2;
    

6.2.13.2. タイトインデックススキャン

タイトインデックススキャンは、クエリ条件によって、フルインデックススキャンもしくはレンジインデックススキャンのいずれかとなります。

ルースインデックスに見合う条件がなければ、GROUP BYクエリの一時的なテーブル作成は拒否できます。WHERE節にレンジ条件がある場合、このメソッドはこれらの条件を満たすキーだけを読みます。もしくは、インデックススキャンとして実行されます。この方法はWHERE節に定義された各レンジ内の全てのキーを読むか、もしくはレンジ条件がなければ全てのインデックスをスキャンするため、タイトインデックススキャンと呼ばれています。レンジ条件を満たす全てのキーが認識された後でのみ、タイトインデックススキャンを用いてグループ演算が実行されることに注意してください。

このメソッドを起動させるには、クエリ内にある全てのカラムに対する定数同等条件があれば十分です。ここでいうクエリとは、GROUP BYキーの前もしくは間にくるキー部分を参照するものです。同等条件の定数は検索キーの「ギャップ」を埋めるため、インデックスの完全なプレフィックスを形成できます。これらインデックスのプレフィックスは、インデックスルックアップに使用できます。GROUP BY結果のソートを要求した場合、またはインデックスのプレフィックスである検索キーを形成できる場合、MySQLもまた余分なオペレーションのソートを拒否します。これは、すでに整頓されたインデックス内でプレフィックスを用いて探索することは、全てのキーを順番に検索することになるからです。

以下のクエリは、前述のルースインデックススキャンアクセス方法では機能しません。しかし、タイトインデックススキャンアクセス方法(テーブルt1(c1,c2,c3,c4)上にインデックスidx(c1,c2,c3)があると仮定する)では、機能します。

  • GROUP BYにはギャップがありますが、条件c2 = 'a'によってカバーされます。

    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
    
  • GROUP BYは、キーの最初の部分では開始されませんが、その部分に対してある定数を与える条件があります。

    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
    

6.2.14. LIMITの最適化

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

  • LIMITを使用して数行しか選択していないと、フルテーブルスキャンが行われそうな場合に、MySQL はインデックスを使うことがある。

  • ORDER BY とともに LIMIT row_countを使用している場合、MySQL ではすべてのテーブルがソートされるのではなく、最初の row_count行の検索が行われた時点でただちにソートを終了する。 インデックスを用いて整頓されている場合、これはとても速い方法です。ファイルソートが実行されなければならない場合、最初のrow_count行が検索されたことを確認する前に、LIMIT節を用いないクエリに当てはまる全ての行が選択されなければなりません。そして、それらのほとんど、もしくは全部がソートされなければなりません。いずれの場合でも最初の行が検索された後では、どの結果セットリマインダもソートする必要はありません。また、MySQLもその必要はありません。

  • LIMIT row_countDISTINCT とあわせて使用した場合、MySQL は一意の row_count行を検索するとただちに停止します。

  • GROUP BYがキーを順番に読む(またはキーのソートを実行して読む)ことで解決でき、キーの値が変わるまで サマリが計算される場合もあります。この場合、LIMIT row_countでは不要な GROUP BY 値の計算がすべて行われなくなります。

  • MySQL が要求された行数をクライアントに送信すると、クエリが中止されます(SQL_CALC_FOUND_ROWSを使用していない場合)。

  • LIMIT 0は常に迅速に空のセットを返します。これは、クエリの妥当性チェックに役立ちます。MySQL APIの1つを使用している場合、結果カラム型の獲得も可能です。(この方法は、MySQLモニター(mysqlプログラム)では、Empty setと表示されるだけで働きません。このため、代わりにSHOW COLUMNSまたはDESCRIBEを使用する必要があります。)

  • サーバでテンポラリテーブルを使用してクエリが解決される場合、LIMIT row_count節が必要な領域の計算に使用されます。

6.2.15. テーブルスキャンを避ける方法

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

  • テーブルが小さすぎて、時間のかかるキールックアップよりもテーブルスキャンの実行のほうが遅くなります。このことは、10行以下の行や短い行をもつテーブルにはよく起こることです。

  • インデックスカラムに対して、ONまたはWHERE節内に使用できる制限はありません。

  • インデックスカラムを定数値と比較し、MySQLは(インデックスツリーに基づいて)その定数がテーブルの大きすぎる部分をカバーしているか、またテーブルスキャンが高速に行われるかを計算します。項6.2.4. 「WHERE 節最適化」を参照してください。

  • 他のカラムをとおして、低濃度(多数の行がキー値に当てはまる)でキーを使用します。この場合、MySQLは、 キーを使用して多数のキールックアップが実行され、またテーブルスキャンもより速く行われるであろうと認識します。

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

  • スキャンされたテーブルのキー配置を更新するには、ANALYZE TABLE tbl_name を使用してください。項12.5.2.1. 「ANALYZE TABLE 構文」を参照してください。

  • MySQLに、テーブルスキャンは既存インデックスを使用するのに比べて大変時間がかかることを示すには、FORCE INDEXをスキャンされたテーブルに使用してください。

    SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
      WHERE t1.col_name=t2.col_name;
    

    項12.2.7. 「SELECT 構文」を参照してください。

  • オプティマイザに1,000キーシーク以上のキースキャンがないことを認識させるには、--max-seeks-for-key=1000オプションで mysqldを起動させるか、もしくはSET max_seeks_for_key=1000を使用してください。項4.2.3. 「システム変数」を参照してください。

6.2.16. INSERTステートメントの速度

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

  • 接続: (3)

  • サーバへのクエリの送信: (2)

  • クエリの解析: (2)

  • 行挿入:(1 × 行サイズ)

  • エンデックス挿入:(1 × インデックス数)

  • クローズ: (1)

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

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

挿入の速度を上げる方法

  • 1 つのクライアントから同時に多数の行を挿入する場合は、マルチプルVALUESリストでINSERTステートメントを使用します。これで独立した INSERT ステートメントの使用時と比較して大幅に(場合によっては数倍)速度が上がります。空ではないテーブルにデータを追加する場合は、さらに速度を上げるためにbulk_insert_buffer_size 変数を調整します。項4.2.3. 「システム変数」を参照してください。

  • 異なる複数のクライアントから大量のレコードを挿入する場合は、INSERT DELAYEDステートメントを使用すると速度を上げることができます。項12.2.4.2. 「INSERT DELAYED 構文」を参照してください。

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

  • テキストファイルからテーブルをロードする場合は LOAD DATA INFILEを使用します。通常、これは INSERTステートメントを使用する場合と比較して、20 倍速度が上がります。項12.2.5. 「LOAD DATA INFILE 構文」を参照してください。

  • テーブルにインデックスが多数ある場合、操作を少し追加するだけでMyISAMテーブルのLOAD DATA INFILE の実行速度をさらに上げることができます。以下の手順を使用してください。

    1. CREATE TABLEを使用して、テーブルを作成します。

    2. FLUSH TABLESステートメントまたはmysqladmin flush-tablesコマンドを実行します。

    3. myisamchk --keys-used=0 -rq /path/to/db/tbl_nameを使用します。これでテーブルからすべてのインデックスの使用が削除されます。

    4. LOAD DATA INFILEを使用して、テーブルにデータを挿入します。これはインデックスをまったく更新しないため、非常に高速になります。

    5. テーブルを読み取り専用にする場合は、myisampackを実行してテーブルを小さくします。項13.4.3.3. 「圧縮テーブルの特徴」を参照してください。

    6. myisamchk -rq /path/to/db/tbl_nameを使用してインデックスを作成しなおします。これは、ディスクに書き込む前にメモリにインデックスツリーを作成してディスクシークを回避するため、LOAD DATA INFILE中のインデックス更新が非常に高速になります。生成されたインデックスツリーは完全にバランスが取られています。

    7. FLUSH TABLESステートメントまたはmysqladmin flush-tablesコマンドを実行します。

    データを挿入したMyISAMテーブルが空の場合は、LOAD DATA INFILEは上記の最適化を自動的に実行します。上記手順との主な相違点は、LOAD DATA INFILEステートメントの実行中にサーバにインデックスの再作成を割り当てる場合より、myisamchkにインデックス作成用のテンポラリメモリ割り当てるほうが、より大幅に割り当てることができる点です。

    myisamchkよりも以下のステートメントを使用して、MyISAMのインデックス利用を可能にしたり、不可能にしたりできます。これらのステートメントを使用すると、FLUSH TABLEオペレーションをスキップできます。

    ALTER TABLE tbl_name DISABLE KEYS;
    ALTER TABLE tbl_name ENABLE KEYS;
    
  • 非トランザクショナルテーブル上で、複数ステートメントを使用して実行されるINSERT速度を上げるには、テーブルをロックしてください。

    LOCK TABLES a WRITE;
    INSERT INTO a VALUES (1,23),(2,34),(4,33);
    INSERT INTO a VALUES (8,26),(6,29);
    ...
    UNLOCK TABLES;
    

    主な速度の相違点は、すべての INSERT ステートメントの完了後にインデックスバッファが 1 回のみディスクにフラッシュされることです。通常は、INSERT ステートメントの数と同じだけ、インデックスバッファのフラッシュが行われます。すべての行を 1 つのINSERTで挿入できる場合はロックの必要がありません。

    トランザクショナルテーブルの場合は、LOCK TABLESではなく START TRANSACTIONおよびCOMMIT を使用して速度の改善を図ります。

    ロックは複数の同時接続テストの合計時間も短縮するが、一部のスレッドの最大待機時間は長くなります。(ロックの際に待機するため)例 :

    1. 接続1は1000行を挿入

    2. 接続2, 3,4は1行を挿入

    3. 接続5は1000行を挿入

    ロックを使用しない場合、2、3、4 は 1 と 5 の前に終了します。ロックを使用した場合は、2、3、4 は 1 と 5 の前には終了しない確率が高くなりますが、合計時間は約 40% 短縮されます。

    MySQL では、INSERTUPDATE、および DELETEの演算が非常に速いため、約 5 つより多い挿入や 更新をする前にロックを追加すると、総合的なパフォーマンスを改善できます。1 行で非常に多数の挿入を実行する場合は、ときどき(約 1,000 行ごと)LOCK TABLESUNLOCK TABLES を続けて実行して、他のスレッドからのテーブルへのアクセスを可能にすることができます。これでもパフォーマンの増加が得られます。

    アウトラインストラテジー使用時でも、データのロードにはLOAD DATA INFILEのほうがINSERTよりも大幅に高速です。

  • MyISAMテーブルおよびLOAD DATA INFILEINSERTの両方に対するパフォーマンスの向上には、key_buffer_sizeシステム変数値を上げてキーキャッシュを拡張します。項6.5.2. 「サーバパラメータのチューニング」を参照してください。

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

6.2.17. UPDATEステートメントの速度

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

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

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

6.2.18. DELETEステートメントの速度

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

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

6.2.19. その他の最適化のヒント

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

  • 接続オーバヘッドを回避するには、データベースに対して永続的な接続を使用します。永続的な接続を使用せずにデータベースに対して多数の新規接続を実行する場合は、thread_cache_size変数の値の変更が必要になることがあります。項6.5.2. 「サーバパラメータのチューニング」を参照してください。

  • 常にすべてのクエリがテーブル内に作成したインデックスを実際に使用していることを確認します。MySQLでは、EXPLAINステートメントでこれを実行できます。項6.2.1. 「EXPLAINを使用して、クエリを最適化する」を参照してください。

  • 大量に更新された MyISAMテーブルに対して複雑な SELECT クエリを使用しないようにします。これで、読み手と書き手間の競合から生じるテーブルロックを回避します。

  • 削除されたレコードがない MyISAMテーブルの場合は、別のクエリでそのテーブルからの読み取りが行われるのと同時に行を挿入できます。これがあなたにとって重要ならば、行削除の回避をおこなうテーブルの使用を検討します。また、大量の行削除後のOPTIMIZE TABLEの実行を検討します。項13.4. 「MyISAM ストレージエンジン」を参照してください。

    この動作は、concurrent_inserts変数設定をとおして、変更されます。行が削除されたテーブル上であっても、新しい行を付加できます。(またその結果、同時に挿入することが可能です。)項6.3.3. 「同時挿入」を参照してください。

  • ARCHIVEテーブルで生じるデータ圧縮問題を修復するのに、OPTIMIZE TABLEを使用できます。項13.10. 「ARCHIVE ストレージエンジン」を参照してください。

  • 通常expr1expr2、 ...の順で行を読み取る場合は、ALTER TABLE ... ORDER BY expr1expr2、 ...を使用してください。テーブルが大幅に変更された後にこのオプションを使用すると、パフォーマンスを改善できます。

  • 他のカラムの情報を基にした 「ハッシュされた」カラムを導入することが役立つ場合があります。このカラムが短いもので、一意性がある場合は、多数のカラムに「大きな」インデックスを使用するより大幅に高速化できます。MySQLでは、追加カラムの使用が以下のように非常に容易です。

    SELECT * FROM tbl_name
      WHERE hash_col=MD5(CONCAT(col1,col2))
      AND col1='constant' AND col2='constant';
    
  • 頻繁に変わるMyISAMテーブルでは、全ての可変長カラム(VARCHARBLOB、そしてTEXT)の使用を避けてください。たった1つの可変長カラムを含む場合でも、テーブルではダイナミック行フォーマットが使用されます。章 13. ストレージエンジンとテーブルタイプを参照してください。

  • 一般に、1 つのテーブルを複数のテーブルに分割することは、行が大きくなるだけで高速化の役には立ちません。行にアクセスする際の、最も大きなパフォーマンス要因は、レコードの最初のバイトを見つけるためのディスクシークです。データの検索後、ほとんどの新規ディスクでは、大多数のアプリケーションに十分な速度で行全体を読み取ることができます。テーブルの分割が実際に有効な状況は、固定長テーブルへの変更が可能な可変長MyISAMテーブルの場合か、テーブルのスキャンを非常に頻繁に必要としながらもほとんどのカラムに必要としない場合のみです。章 13. ストレージエンジンとテーブルタイプを参照してください。

  • 多数の行の情報から計算する頻度を非常に高くする必要がある場合(カウントの場合など)、新たなテーブルを導入し、リアルタイムでカウンタを更新するほうがはるかに適しています。以下のような更新は非常に高速にできます。

    UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
    

    これは、MyISAMのようにテーブルロック(複数リーダ/単一ライタ)のみの MySQL ストレージエンジンを使用する場合に、非常に重要です。また、このような場合は行ロックマネージャで必要な作業が少なくなるため、ほとんどのデータベースでパフォーマンスが改善されます。

  • 大きなログテーブルから統計を収集する必要がある場合は、テーブル全体をスキャンするのではなく、サマリテーブルを使用します。サマリの管理は、「リアルタイム」で統計を実行する場合と比較して非常に高速になります。 何らかの変更がある(業務上の決定に応じて)場合は、ログから新規にサマリテーブルを再生成したほうが、実行アプリケーションの変更よりはるかに高速です。

  • 可能であれば、レポートを「リアルタイム」か「集計」かのいずれかに分類するように推奨します。集計レポートに必要なデータは、サマリテーブルから生成され、サマリテーブルは実データから生成されます。

  • カラムにデフォルト値がある利点を生かします。挿入対象の値がデフォルト値と相違する場合のみ明示的に値を挿入します。これで、MySQL が要する解析作業が軽減され、挿入の速度が改善されます。

  • 状況によっては、データをBLOBにパックし、格納したほうが便利です。このような場合は、BLOB へのパックおよびパック解除を行うコードをアプリケーションに追加する必要がありますが、あるステージにおける大量のアクセスを省略できることになります。これは、固定長テーブル構造に準拠しないデータがある場合に実用的です。

  • 通常は、すべてのデータが冗長にならないようにする必要があります。(データベースセオリの第 3 正規化)。しかし、高速化を図る必要がある場合はデータなどの複製やサマリテーブルの作成をためらうべきではありません。

  • ストアドルーチンや UDF(ユーザ定義関数)はパフォーマンスの向上に役立つ手段です。詳しくは、章 17. ストアドプロシージャとファンクションおよび項25.3. 「Adding New Functions to MySQL」を参照してください。

  • アプリケーションのクエリと応答をキャッシュすること、および挿入と更新の同時実行を試行することは必ず高速化に役立ちます。データベースでロックテーブルがサポートされる場合(MySQL や Oracle など)は、これによって確実にすべての更新後にインデックスキャッシュが 1 回だけフラッシュされるようにできます。MySQLのクエリキャッシュも、同様の結果を得るために利用できます。詳しくは項4.13. 「MySQL クエリ キャッシュ」を参照してください。

  • データの書き込みするタイミングを知る必要がない場合はINSERT DELAYEDを使用します。多数の行が 1 回のディスクへの書き込みで書き込まれるため、これで高速化が図れます。

  • SELECTの優先を上げる場合は、INSERT LOW_PRIORITY を使用します。

  • キューをジャンプするようにする場合は、SELECT HIGH_PRIORITY を使用します。言い換えると、書き込み待機中のユーザがいる場合でも、SELECTを実行できるようになる。

  • 1 つの SQLステートメントで多数の行を格納するには、複数行のINSERT ステートメントを使用します。これは、MySQLを含む多数の SQL でサポートされています。

  • 大量のデータをロードする場合はLOAD DATA INFILEを使用します。これは通常のINSERTより高速になります。

  • 一意の値にするには、AUTO_INCREMENTカラムを使用します。

  • 一定の間隔で OPTIMIZE TABLE を使用して、動的MyISAMテーブルの断片化を回避します。項13.4.3. 「MyISAM テーブルストレージフォーマット」を参照してください。

  • さらに高速化が可能であれば、MEMORYテーブルを使用します。詳しくは項13.7. 「MEMORY (HEAP) ストレージエンジン」を参照してください。頻繁にアクセスされる非クリティカルデータ(クッキーなしでユーザに最後に表示されたバナーの情報など)にはMEMORYテーブルを使用します。多くのWebアプリケーション環境では、 揮発性データの処理にユーザセッションも使用できます。

  • Webサーバでは、画像と他のバイナリアセットを通常ファイルとして格納します。言い換えると、データベース内にはファイル参照のみを格納します。この主な理由は、通常の Web サーバのほうがデータベースコンテンツと比較してファイルのキャッシュに優れているためです。このため、ファイルを使用したほうがシステムの高速化を容易に図れます。

  • 別のテーブルで同一情報を扱うカラムは、同じ宣言をし、同じデータ型を持つようにします。この結果、一致カラムに基づく結合速度が速くなります。

  • カラム名はなるべく単純なものに保持します。たとえば、customerテーブルではcustomer_name ではなく name を使用します。他の SQL サーバに移植可能にすることを考慮するなら、名前を 18 文字未満にする。

  • 高速化が大きく必要とされる場合は、複数の SQL サーバがサポートするデータストレージの低レベルインタフェースを調べる必要があります。たとえば、MySQL MyISAMストレージエンジンに直接アクセスすることによって、SQL インタフェース使用時と比較して 2~5 倍の速度が得られることもあります。 これを実行可能にするには、データをアプリケーションと同じサーバに配置し、また通常は 1 プロセスのみからアクセスするようにする必要があります(外部ファイルロックが非常に低速なため)。上記の問題は、MySQL サーバに低レベルの MyISAM コマンドを導入することで解消できます(必要に応じてパフォーマンスを改善する容易な手段の 1 つとなるのです)。データベースインタフェースを慎重に設計することで、この種の最適化を容易にサポートできる。

  • 多くの場合、テキストファイルにアクセスするのと比較して、データベースからデータにアクセスしたほうが高速である。この理由は一般にテキストファイル(数値データ使用時)よりデータベースのほうがよりコンパクトで、必要なディスクアクセスが少ないことによる。また、テキストファイルを解析してレコードとカラムの境界を検索する必要がないため、コードも節約できる。

  • レプリケーションはオペレーションによって、性能向上を図ります。負荷を分散させるため、クライアント修正をレプリケーションサーバに分布できる。バックアップを作成する間マスタの速度が低下するのを避けるため、スレーブサーバを作成することができる。章 5. レプリケーションを参照してください。

  • DELAY_KEY_WRITE=1オプションでMyISAMテーブルを定義すると、ファイルが閉じられるまでディスクにログが記録されないためインデックス更新の速度が上がる。 この欠点は、途中で mysqld の強制終了が発生した場合にテーブルに問題がないことを確認するため、mysqld を開始する前に、テーブルに対して myisamchkを実行するか、--myisam-recoverでサーバを作動させる必要があるということである。キー情報は常にデータから生成可能であるため、DELAY_KEY_WRITEを使用しても何も消失はしない。

6.3. ロック関連の問題

6.3.1. MySQL のテーブルロック方法

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

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

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

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

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

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

  • テーブルにロックがない場合、write ロックをつけてください。

  • もしくは、write ロックキューでロック要求を行ってください。

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

  • テーブルに write ロックがない場合、read ロックをつけてください。

  • もしくは、read ロックキューでロック要求を行ってください。

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

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

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

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

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

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

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

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

行レベルロックの利点

  • 多数のスレッドで異なる行をアクセスする際に、ロックコンフリクトが少なくてすみます。

  • ロールバックの変更がすくなくてすみます。

  • 1つの行を長時間ロックすることが可能です。

行レベルロックの欠点

  • テーブルレベルロックよりもメモリを要します。

  • テーブルの大部分で使用される際、より多くのロックが必要となるためテーブルレベルロックよりも処理速度が遅くなります。

  • データの大部分に対してGROUP BYオペレーションを実行する場合、もしくは全テーブルを頻繁にスキャンする場合他のロックよりもはるかに遅いです。

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

  • テーブルのほとんどのステートメントは read です。

  • 1つのキーリードで取得される1つの行に対して、write が更新もしくは削除される場合、read と write が混合となります。

    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
    DELETE FROM tbl_name WHERE unique_key_col=key_value;
    
  • SELECTが同時INSERTステートメントとごく少数のUPDATEもしくはDELETEステートメントと混合されます。

  • writerを使用しない、全てのテーブルのGROUP BYオペレーションや多くのスキャン。

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

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

  • バージョニング(同時挿入でMySQLに使用されるような):同時に1writerと多数のreaderが存在する場合です。これはアクセス開始時に応じたデータに対して、データベースやテーブルは異なるビューをサポートします。これに対する他の共通用語は「タイムトラベル」、「writeのコピー」または「コピーオンデマンドです。

  • 多くの場合コピーオンデマンドは行レベルロックよりも優勢です。しかし、最悪の場合、通常のロックを使用するよりも、メモリ容量が多く必要となり得ます。

  • 行レベルロックを使用する代わりに、MySQLではGET_LOCK()RELEASE_LOCK()といったアプリケーションレベルロックを利用できます。これらはアドバイザリロックで、特に問題のないアプリケーションでのみ機能します。(詳しくは項11.10.4. 「その他の関数」をご確認ください。)

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

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

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

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

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

  • 一般にテーブルの更新はテーブル検索より重要だと見なされるため、テーブルを更新するステートメントは優先度が高くなります。これにより、更新では特定のテーブルに対して重いSELECTアクティビティが使用されるため、更新が 「資源枯渇」 にさらされないことが確実になります。

  • テーブルロックによって、ディスク容量がいっぱいのため、スレッドが待機するような問題が引き起こされます。またスレッドが処理される前に開きスペースを利用可能にする必要があります。この場合、問題テーブルにアクセスを求める全てのスレッドも、より多くのディスクスペースが利用可能になるまで待機中となります。

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

  • クライアントが実行に長時間かかる SELECTを使用します。

  • その後、別のクライアントが使用テーブルに対して UPDATEを使用する。 このクライアントは SELECTが完了するまで待機が必要になる。

  • 別のクライアントが同一テーブルに対してさらに SELECTステートメントを使用します。UPDATESELECTより優先度が高いため、この SELECTUPDATEが完了するまで待機が必要になります。また、最初の SELECTの完了を待つ必要もあります。

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

  • SELECTステートメントの実行の高速化を試行する。これにはサマリテーブルの作成が必要な場合もあります。

  • --low-priority-updatesのオプションで mysqldを開始する。これは、テーブルを更新(変更)するすべてのステートメントの優先度を SELECTステートメントの優先度より低くします。 この場合、前シナリオの2つ目のSELECTステートメントはUPDATEステートメントの前に実行され、一番目のSELECTが完了するまで待機する必要はありません。

  • SET LOW_PRIORITY_UPDATES=1ステートメントを使用すると、特定の接続からの更新すべてが低い優先度で実行されるように指定できます。項12.5.3. 「SET 構文」を参照してください。

  • LOW_PRIORITY属性を使用して、特定のINSERTUPDATE、または DELETEステートメントの優先度を低く設定できます。

  • HIGH_PRIORITY性を使用すると、特定の SELECT の重要度を高く指定できます。項12.2.7. 「SELECT 構文」を参照してください。

  • max_write_lock_countシステム変数の値を低くしてmysqldを開始し、MySQLに全てのSELECTステートメント(特定数をテーブルに挿入した後に待機しているステートメント)の優先度を一時的に引き上げさせます。これは、一定数のWRITEロックの後にREADロックを設定します。

  • SELECTと結合したINSERT に問題がある場合は、SELECTステートメントとINSERT ステートメントの同時サポートが可能になるため、新規の MyISAMテーブルを使用するように切り替えます。(詳しくは項6.3.3. 「同時挿入」をご確認ください。)

  • 同じテーブル上で insertとdeleteステートメントの混在が多い場合、INSERT DELAYEDが非常に役立つ可能性があります。項12.2.4.2. 「INSERT DELAYED 構文」を参照してください。

  • SELECTDELETEステートメントに問題がある場合、DELETELIMITオプションを使用すると解決できる場合があります。項12.2.1. 「DELETE 構文」を参照してください。

  • SELECTステートメントでSQL_BUFFER_RESULTを使用すると、テーブルロックの持続を短縮することができます。項12.2.7. 「SELECT 構文」を参照してください。

  • 単一クエリを使用するために、mysys/thr_lock.cでロックコードを変更できることもあります。この場合、writeロックとreadロックは同等の優先度をもち、いくつかのアプリケーションにとって役立つものとなります。

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

  • 更新と、同じテーブルの多くの行を調べるセレクトを混合しない限り、同時ユーザは問題を引き起こしません。

  • シングルロックをしようしてのアップデートはロックなしでのアップデートよりも速いため、LOCK TABLESを使用して速度を上げることができます。テーブルのコンテンツを別々のテーブルに分けるのも効果的です。

  • MySQLでテーブルロック関連の問題が生じたとき、テーブルをInnoDBに変換することで性能向上を図ることができます。項13.5. 「InnoDB ストレージ エンジン」を参照してください。

    MySQL Enterprise ロックの競合は性能を著しく低下させます。MySQL Network Monitoring and Advisory Service はこの問題を回避するための専門的なアドバイスを提供します。購読を希望する場合は、http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

6.3.3. 同時挿入

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

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

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

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

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

6.4. データベース構造の最適化

6.4.1. 設計上の選択

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

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

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

  • データの取得時にインデックス全体を読み取る必要があるため、テーブルスキャンの速度が大幅に下がる。

  • クエリでデータを取り出す際にインデックステーブルのみの使用ができない。

  • ノードからインデックスを複製する必要があるため(レコードはノードに格納できないことによる)、大量の領域が消費される。

  • 削除があるとテーブルの速度が次第に低下する(通常、削除ではノードのインデックスが更新されないため)。

  • インデックスデータのみのキャッシュが困難である。

6.4.2. データの小型化

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

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

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

  • できる限り効率性の高い(最小)の型を使用する。MySQL にはディスク領域とメモリを節約できる専用の型がある。 可能な場合は、小さなテーブルの取得には小さな整数型を使用する。たとえば、INTより、25%スペース使用量の少ないMEDIUMINTのほうが適している場合もしばしばあります。

  • できる限り、カラムに NOT NULLを宣言します。これですべてが高速化され、1 カラム当たり 1 ビットを節約できます。アプリケーションで実際に NULLが必要な場合は、必ず使用する必要があるため、注意が必要です。デフォルトですべてのカラムにこれを設定することは避けます。

  • MyISAMテーブルでは、可変長カラム(VARCHARTEXT、あるいはBLOBなど)がまったくない場合は固定長レコード形式を使用します。これで速度が上りますが、領域の消費も増えます。詳しくは項13.4.3. 「MyISAM テーブルストレージフォーマット」を参照してください。たとえCREATE TABLEオプションROW_FORMAT=FIXEDが使用されたVARCHARカラムがあっても、固定長行が必要な場合のヒントが得られます。

  • InnoDBはコンパクトストレージフォーマットを使用します。MySQL 5.0.3以前のバージョンでは、InnoDB行は固定サイズカラムにたいしても、カラム数やカラム長さといった冗長な情報を含みます。 デフォルトでは、コンパクトフォーマット(ROW_FORMAT=COMPACT)でテーブルが作成されます。MySQLの旧バージョンにダウングレードをしたい場合、ROW_FORMAT=REDUNDANTで古いフォーマットを要求できます。

    コンパクトInnoDBフォーマットはUTF8データを含むCHARカラムがどのように格納されるかも変更します。最長UTF8エンコードキャラクタが3バイトであることを前提に、ROW_FORMAT=REDUNDANTでは、UTF-8 CHAR(N)は3× Nバイトを使用します。多くの言語は主にシングルバイトUTF-8キャラクタを用いてかかれるため、固定保存長はスペースを無駄に使用します。ROW_FORMAT=COMPACTフォーマットでは、 InnoDBNから3× Nバイトのストレージ可変容量をこれらカラムに割り当てます(必要であればトレールスペースを取り除いて行います)。最小のストレージ長は、ある場合に適切な更新が行われるように、Nバイトとして保持されます。

  • テーブルのプライマリインデックスを可能な限り短くします。これで、レコードの識別が容易になり効率化が図れます。

  • インデックスの作成は必要なものだけに限定します。インデックスは取り出しに優れていますが、高速保存が必要な場合は適されません。カラムの組み合わせを使用してテーブルを検索し、テーブルにアクセスする場合がほとんどであれば、インデックスを作成します。インデックスの最初の部分は、最も使用頻度の高いカラムにする必要があります。テーブルの検索時に、常に常に多数のカラムを使用する場合は、より重複しているカラムを先に使用するとインデックスの圧縮を改善できます。

  • 文字列の最初の数文字に、一意のプリフィックスがあるカラムが多い場合は、このプリフィックスのみをインデックス化したほうがよりよくなります。MySQL はカラムの最も左側の部分インデックス作成をサポートします(項12.1.7. 「CREATE INDEX 構文」を参照してください)。短いインデックスの速度が速い理由は、占有ディスク領域が小さいことだけではなく、インデックスキャッシュでのヒットが多くなり、所要ディスクシークが少なくなることにもよります。項6.5.2. 「サーバパラメータのチューニング」を参照してください。

  • 状況によっては、スキャンの頻度が高いテーブルを 2 つに分割したほうが有利な場合もあります。これは特に、動的テーブルで、テーブルスキャンの際に対応する行の検索に小さな静的テーブルの使用が可能である場合にあてはまります。

6.4.3. カラムインデックス

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

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

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

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

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

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

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

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

6.4.4. 複合インデックス

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

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

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

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

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

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

SELECT * FROM test WHERE last_name='Widenius';

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

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

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

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

SELECT * FROM test WHERE first_name='Michael';

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

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

6.4.5. MySQLにおけるインデックスの使用

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

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

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

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

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

  • WHERE節にマッチする行を迅速に検索する場合。

  • 行を考慮に入れない場合。複数のインデックス間を選択できる場合、MySQLは通常最小行数を検索するインデックスを使用します。

  • 結合実行時に、他のテーブルから行を取得する場合。

  • 特定のインデックス化されたカラムkey_colに対して、MIN()あるいはMAX()値を検索する場合。これはインデックス内でkey_colより前に発生する全てのキーパーツで、WHERE key_part_N = constantが使用されているかをチェックするプリプロセッサによって最適化されます。この場合MySQLはMIN()もしくはMAX()表現それぞれに対して単一キールックアップを行い、定数で置き換えます。全ての表現が定数で置き換えられた場合、クエリは一度に返されます。例 :

    SELECT MIN(key_part2),MAX(key_part2)
      FROM tbl_name WHERE key_part1=10;
    
  • 使用可能キーの最も左側のプリフィックスでグループ化やソート化が行われる際、テーブルをソートもしくはグループ化する場合に使用します。 (例えば ORDER BY key_part1key_part2)全てのキー部分にDESCが後続する場合、キーは逆の順序で読まれます。項6.2.12. 「ORDER BY最適化」を参照してください。

  • データ行を参照せず値を取得するためにクエリが最適化される場合もあります。クエリがあるキーの最も左側のプリフィックスを形成し、かつテーブル内で数値のみのカラムを使用する場合、選択された値は高速化を図るため、インデックスツリーから取得されることもあります。

    SELECT key_part3 FROM tbl_name 
      WHERE key_part1=1
    

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • = or <=>演算子を使用する等価比較にのみ使用されます。(ただし非常に 高速です)<のように値の範囲を検索する比較演算子には使用されません。

  • オプティマイザはORDER BYオペレーション速度を上げるためにハッシュインデックスを使用することはできません。(このようなインデックスは順序どおりに次のエントリーを検索することはできません)

  • MySQLは2つの値の間にある行の数を判別することはできません。(どのインデックスを使用するかを決定する上、範囲オプティマイザによって使用されます)MyISAMテーブルをハッシュインデックスを含むMEMORYテーブルに変換した場合、これは一部のクエリに影響を与えるかもしれません。

  • 行の検索には自然キーのみが使用できます。(B-tree インデックスでは、どのキーの左端の先頭部を使用しても行を検索できます。)

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

6.4.6. MyISAMキーキャッシュ

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

  • インデックスブロックには、key cache (あるいはkey buffer)という特別な構造が保持されています。その構造には最も頻繁に使用されるインデックスがおかれた多数のブロックバッファが含まれます。

  • データブロックに対して、MySQLは特別なキャッシュを使用しません。代わりに、ネイティブオペレーティングシステムファイルシステムキャッシュに依存します。

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

  • 複合スレッドはキャッシュを同時にアクセスできます。

  • 複合キーキャッシュのセットアップおよび特定のキャッシュに対するテーブルインデックスの割り当てが可能です。

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

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

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

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

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

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

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

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

6.4.6.1. 共有キーキャッシュアクセス

スレッドはキーキャッシュバッファに同時にアクセス可能であり、以下の条件に従います。

  • 更新されていないバッファは複合スレッドによってアクセス可能です。

  • 更新中のバッファは更新が完了するまでスレッドを待機させます。

  • 複合スレッドは、お互いに衝突しない限り、キャッシュブロック置換を引き起こす要求をします(つまり、異なるインデックスブロックを必要とする限り、異なるキャッシュブロックを置換します。)

キーキャッシュの共有アクセスによって、サーバ処理能力が大幅に向上します。

6.4.6.2. 複合キーキャッシュ

キーキャッシュへの共有アクセスはパフォーマンスを向上させますが、スレッド間の競合を完全には削除しません。キーキャッシュバッファへアクセスするためのコントロール構造をめぐって競合が行われます。キーキャッシュアクセス競合をさらに軽減するために、MySQLは複合キーキャッシュも提供しています。この特性によって、異なるキーキャッシュに対して各テーブルインデックスの割り当てが可能となります。

複合キーキャッシュがある場合、サーバは既存のMyISAMテーブルに対してクエリ処理を行う際に、どのキャッシュを使用すべきか知らされていなければなりません。デフォルトでは、全てのMyISAMテーブルインデックスはデフォルトキーキャッシュ内にキャッシュされます。テーブルインデックスを特定のキーキャッシュに割り当てるには、CACHE INDEXステートメントを使用してください。(項12.5.5.1. 「CACHE INDEX 構文」を参照してください。)例えば、以下のステートメントはt1t2、そしてt3テーブルから、hot_cacheと名づけられたキーキャッシュにインデックスを割り当てます。

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

CACHE INDEXステートメント内の参照キーキャッシュは、SET GLOBALパラメータ設定ステートメントを用いてサイズを設定するか、もしくはサーバスタートアップオプションを使用して作成できます。例 :

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

キーキャッシュを破壊するにはサイズをゼロに設定してください。

mysql> SET GLOBAL keycache1.key_buffer_size=0;

デフォルトキーキャッシュを破壊することはできない点に注意してください。この破壊に関してはいずれの試みも無視されます。

mysql> SET GLOBAL key_buffer_size = 0;

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

キーキャッシュ変数は名前と部位のある構造システム変数です。keycache1.key_buffer_sizeにとって、keycache1はキャッシュ変数名であり、key_buffer_sizeはキャッシュ部位です。構造キーキャッシュシステム変数に対して使用される構文の詳細については、項4.2.4.1. 「構造化システム変数」を参照してください。

デフォルトではテーブルインデックスは、サーバ起動時に、メイン(デフォルト)キーキャッシュに割り当てられます。.キーキャッシュが破壊された場合、それに割り当てられた全てのインデックスはデフォルトキーキャッシュに再度割り当てられます。

サーバが込んでいる場合、以下の3つのキーキャッシュを使用する戦略をお勧めします。

  • 全てのキーキャッシュに割り当てられたスペースの20%以上を占める「hot」キーキャッシュ。 検索に使用される頻度が高く、かつ更新されていないテーブルに対してはこれを使用してください。

  • 全てのキーキャッシュに割り当てられたスペースの20%以上を占める「cold」キーキャッシュ。 このキャッシュは、テンポラリテーブルのような中位の集中的に改良されたテーブルに使用してください。

  • キーキャッシュスペースの60%以上を占める「warm」キーキャッシュ。これをデフォルトで全ての他のテーブルに使用されるように、このキーキャッシュをデフォルト設定してください。

上記3つのキーキャッシュを使用する理由のひとつの利点は、1つのキーキャッシュ構造は他二つのキーキャッシュへのアクセスを阻害しない点です。あるキャッシュに割り当てられたテーブルにアクセスするステートメントは、他のキャッシュに割り当てられたテーブルにアクセスするステートメントとは競合しいません。.パフォーマンス向上には他の理由もあります。

  • hotキャッシュはクエリの取得にのみ使用されるため、内容は決して改良されません。結果、インデックスブロックがディスクから引き抜かれなければならない場合でも、置換のために選択されたキャッシュブロック内容は最初にフラッシュされる必要はありません。

  • キャッシュに割り当てられたインデックスにとって、インデックススキャンを必要とするクエリがなければ、インデックスB-treeの非リーフノードに一致するインデックスブロックがキャッシュに残っている可能性が高くなります。

  • テンポラリテーブルに対する最も頻度が高い更新オペレーションは、更新ノードがキャッシュ内にあり、最初にディスクから読まれる必要がない場合、処理速度がはるかに向上します。テンポラリテーブルのインデックスサイズがcoldキーキャッシュのサイズと同等の場合、更新ノードがキャッシュ内にある可能性が高くなります。

CACHE INDEXはテーブルとキーキャッシュの関連性を設けますが、サーバが再起動するたびにその関連性は失われます。サーバが再起動するたびに関連性を有効にしたい場合、オプションファイルを使用することで実行できます。キーキャッシュをコンフィギャする変数設定と、CACHE INDEXステートメントを実行するファイルに名前をつけるinit-fileオプションを含んでください。例 :

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

MySQL Enterprise my.cnf/my.ini オプションファイルに対する最適なコンフィギャを行う際のアドバイスを得るには、MySQL Network Monitoring and Advisory Serviceを購読してください。実際のテーブル使用量に基づいて行うことをお勧めします。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

サーバが再起動するたびにmysqld_init.sqlでのステートメントは実行されている。ファイルはラインごとの1つのsqlのステートメントを含むべきである。次の例はhot_cachecold_cacheに複数のテーブルをそれぞれ割り当てる。

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

6.4.6.3. ミッドポイント挿入戦略

デフォルトでは、キーキャッシュマネージメントシステムは除去されるキーキャッシュブロックの選択に、LRU戦略が用いられますが、ミッドポイント挿入戦略と呼ばれるより複雑なメソッドもサポートします。

ミッドポイント挿入戦略を使用している間は、LRU チェーンは2つに分かれます。hotサブチェインとwarmサブチェイン。2部位間の分け目は固定されていませんが、warm部位が「短すぎない」ように、最低でもkey_cache_division_limit%のキーキャッシュブロックを含むようにします。key_cache_division_limitは構成されたキーキャッシュ変数の構成要素であるため、その値はキャッシュ毎に設定可能なパラメータとなります。

インデックスブロックがテーブルからキーキャッシュに読まれる際、warmサブチェインの後尾に置かれます。ある特定のヒット数後(ブロックへのアクセス後)、hotサブチェインに昇格されます。現在では、ブロックを昇格させるために必要なヒット数は(3) 全てのインデックスブロックで同じです。

サブチェインに昇格されたブロックはチェインの後部に置かれます。それからブロックはこのsubチェイン内をめぐります。もしブロックが十分長い時間subチェインの前部にとどまった場合、warmチェインに降格されます。この時間はキーキャッシュのkey_cache_age_threshold構成要素値によって決定されます。

境界値は、Nブロックを含むキーキャッシュにとって、最後のN × key_cache_age_threshold / 100ヒットでアクセスされなかったhotサブチェイン前部のブロックは、warmサブチェインの前部に移動されます。そこから除去の第一候補となります。というのも、置換ブロックは常にwarmサブチェインの前部から取得されるからです。

ミッドポイント挿入戦略によってキャッシュ内に常に高価値なブロックを保持することができます。プレーンLRU 戦略の使用を好む場合は、key_cache_division_limit値をデフォルトである100に設定しておいてください。.

ミッドポイント挿入戦略によって、インデックススキャンで必要とされるクエリの実行が、高価値なハイレベルB-treeノードと一致するインデックスブロックを、効果的にキャッシュから押し出される際のパフォーマンスを向上させます。これを回避するには、100以下に設定されたkey_cache_division_limitを用いた、ミッドポイント挿入戦略を使用しなければなりません。この結果、ノードに頻繁にヒットする変数は、インデックススキャンオペレーション中もhotサブチェインに保存されます。

6.4.6.4. インデックスプレロード

全てのインデックスのブロックを保持するためのキーキャッシュ内に十分なブロックがある場合、もしくは少なくとも非リーフノードと一致するブロックがある場合、使用する前に、インデックスブロックでキーキャッシュをプレロードするのは理にかなっています。プレロードによって、テーブルインデックスブロックを最も効果的な方法でキーキャッシュバッファ内におくことができます。ディスクから連続してインデックスブロックを読む方法。

プレロードなしでは、ブロックは、クエリの必要に応じてキーキャッシュ内におかれます。ブロックはキャッシュ内にとどまりますが、バッファは足りているため、ディスクからランダムかつ不連続に取得されます。

インデックスをキャッシュにプレロードするにはLOAD INDEX INTO CACHEステートメントを使用してください。例えば、以下のステートメントはt1およびt2テーブルのインデックスノード(インデックスブロック)をプレロードします。

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

IGNORE LEAVESモディファイヤはインデックスの非リーフノードに対してはブロックのみがプレロードされます。したがって、表示されたステートメントはt1から全てのインデックスブロックをプレロードしますが、t2からは非リーフノードに対するブロックのみとなります。

インデックスがCACHE INDEXステートメントを用いてキーキャッシュに割り当てられた場合、プレロードはインデックスブロックをキャッシュ内に置きます。もしくは、インデックスはデフォルトキーキャッシュにロードされます。

6.4.6.5. キーキャッシュブロックサイズ

key_cache_block_size変数を使用して、個々のキーキャッシュのブロックバッファサイズを指定することができます。.これによって、インデックスファイルに対して、I/O オペレーションパフォーマンスのチューニングが許可されます。

I/O オペレーションに対する最適なパフォーマンスは、readバッファサイズがネーティブオペレーティングシステムI/Oバッファサイズと同等の場合に向上します。しかし、キーノードサイズをI/O バッファサイズと等しく設定しても、最適な全体的パフォーマンスを常に保証するわけではありません。ビッグリーフノードを読む場合、サーバは多数の不要なデータを吸収し、実質的に他のリーフノードが読まれるのを阻止します。

現在、テーブル内でインデックスブロックのサイズを変更することはできません。このサイズは、.MYIインデックスファイルが作成される際に、サーバによって設定されます。これは、テーブル定義内に存在するインデックスのキーサイズに依存します。ほとんどの場合、I/O バッファサイズと等価に設定されます。

6.4.6.6. キーキャッシュの再構築

キーキャッシュはパラメータ値を更新することで、随時、再構築されます。例 :

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

key_buffer_sizeまたはkey_cache_block_sizeキーキャッシュ構成部位に構成部位の現在値と異なる値を割り当てた場合、サーバはキャッシュの旧構造を破壊し、新しい値に基づいた新構造を作成します。キャッシュに汚染されたブロックが存在する場合、サーバはキャッシュを破壊/再作成する前にディスクにそれを保存します。他のキーキャッシュパラメータを変更した場合再構築は起きません。

キーキャッシュを再構築する場合、サーバはディスクの汚染されたバッファ内容を最初にフラッシュします。その後、キャッシュ内容は無効となります。しかし、再構築はキャッシュに割り当てられたインデックス使用を必要とするクエリをブロックしません。その代わり、サーバはネーティブファイルシステムキャッシングを使用するテーブルインデックスに直接アクセスします。ファイルシステムキャッシュはキーキャッシュを使用した場合ほど、効率が良くありません。クエリの実行がなされますが、速度の低下が予期されます。キャッシュが再構築された後、キャッシュインデックスが割り当てられる毎に有効となり、インデックスのファイルシステムキャッシングの使用が停止します。

6.4.7. MyISAMインデックス統計コレクション

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

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

  • refアクセスごとに読まれなければならない行の数を推測

  • 一部 joinが生成する行の数を推測するため:つまり、このフォームのオペレーションが生成する行の数になります。

    (...) JOIN tbl_name ON tbl_name.key = expr
    

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

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

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

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

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

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

  • myisam_stats_methodnulls_equalの場合、全てのNULL値は等価として扱われます。(つまり、それらは全てシングル値グループを形成します。)

    NULL値グループサイズは標準非NULL値グループサイズよりはるかに大きくなります。このメソッドは標準値グループサイズを大きくゆがませます。これによりオプティマイザから見たインデックスは非NULL値を検索するjoinに対して役に立たないように見えます。結果的に、nulls_equalメソッドはオプティマイザにrefアクセスに対してインデックスを使用すべきときでも使用しないままにする可能性があります。

  • myisam_stats_methodnulls_unequalの時、NULL値は等価として扱われません。代わりに、各NULL値はサイズ1の別値グループを生成します。

    NULL値が多い場合、このメソッドは標準値グループサイズを小さくゆがませます。もし標準非NULL値グループサイズが大きい場合、NULL値をサイズ1のグループとして取り扱うと、オプティマイザに非NULL値を探させるjoinのインデックス値を過大評価します。結果的に、nulls_unequalメソッドは、他のメソッドの方が適しているにもかかわらず、オプティマイザにこのインデックスをrefルックアップに使用させることがあるかもしれません。

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

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

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

  • myisam_stats_methodを設定し、CHECK TABLEステートメントを発行します。

  • myisamchk --stats_method=method_name --analyzeを実行します。

  • テーブルを変更し統計を旧値とし(例えば、行を挿入し、それから削除します)、そしてmyisam_stats_methodを設定しANALYZE TABLEステートメントを発行します。

myisam_stats_methodの使用に関する警告。

  • 以下で説明されているように、強制的にテーブル統計を明示的に収集させることができます。ただし、MySQLも自動的に統計を収集する可能性があります。例えば、テーブルステートメント実行時、ステートメントの中にはテーブルを改良するものもあり、MySQLは統計を収集する可能性があります。(例えば、これは大量挿入や削除時、もしくはALTER TABLEステートメントの際に起こる可能性があります。)これが生じた場合、統計はmyisam_stats_methodがその時々で持っている値を使用して統計が収集されます。よって、あるメソッドで統計を収集したがmyisam_stats_methodが自動的にテーブル統計が収集された後他のメソッドに設定されている場合、他のメソッドが使用されます。

  • 既存のMyISAMテーブルに対してどのメソッドが統計生成に使用されたかを知ることはできません。

  • myisam_stats_methodMyISAMテーブルにのみ適用されます。他のストレージエンジンはテーブル統計を収集するメソッドが1つしかありません。通常は、nulls_equalメソッドに近いです。

6.4.8. MySQL でのテーブルのオープンとクローズの方法

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

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

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

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

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

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

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

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

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

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

  • キャッシュが満杯のときに、キャッシュにないテーブルをスレッドが開こうとした場合。

  • キャッシュに table_open_cacheを超えるエントリがあり、あるスレッドがテーブルの使用を終えた場合。

  • テーブルフラッシュオペレーションが起きたとき。いずれかのユーザが FLUSH TABLESmysqladmin flush-tablesまたは mysqladmin refreshを実行した場合。

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

  • 現在使用中でないテーブルは、最後に使用した時が古いものから順にリリースされる。

  • キャッシュが満杯でリリース可能なテーブルがなく、新たにテーブルを開く必要がある場合は、必要に応じてキャッシュが一時的に拡張される。

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

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

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

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

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

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

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

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

6.5. MySQL サーバの最適化

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

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

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

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

他のヒント:

  • RAM が十分にある場合は、スワップデバイスすべてを削除できる。オペレーティングシステムによっては、空きメモリがある場合でもスワップデバイスが使用されることがある。

  • 外部ロックを回避する。MySQL 4.0以降、外部ロックはデフォルトで全てのシステムで使用不可能となっています。--external-locking--skip-external-lockingオプションは外部ロックの使用可能および不可能を明示的に宣言します。

    外部ロックを使用しなければ、1つのサーバのみを起動している場合、MySQLの機能性には影響しません。myisamchkを起動する前にサーバを停止してください(または関連するテーブルをロック/フラッシュしてください)。いくつかのシステム上では外部ロックを必ず使用不可に設定します。というのも、機能しないからです。

    外部ロックが使用不可である唯一の状況は、同一データに対して複数の MySQL servers(クライアントではない)を実行している場合と、サーバに対して初めにテーブルのフラッシュとロックを行う指示を出さずに、テーブルに対して myisamchkを実行する場合に限られる。 MySQLクラスタを使用しているとき以外で、同じデータを同時にアクセスするのに複数のMySQLサーバを使用することは推奨されていません

    LOCK TABLESUNLOCK TABLESステートメントは内部ロックを使用するため、外部ロックが不可となっている場合でも使用できます。

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

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

shell> mysqld --verbose --help

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

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

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

mysql> SHOW VARIABLES;

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

mysql> SHOW STATUS;

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

shell> mysqladmin variables
shell> mysqladmin extended-status

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

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

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

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

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

    shell> mysqld_safe --key_buffer_size=64M --table_open_cache=256 \
               --sort_buffer_size=4M --read_buffer_size=1M &
    
  • メモリが 128M で、テーブルは少数で大量のソートの実行が必要な場合は、次のように使用できます。

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

    同時接続が多数ある場合、mysqldが各接続ごとに最小限のメモリを使用するよう設定されていない限り、スワップ問題が起こります。全ての接続にメモリが十分であればmysqldのパフォーマンス性は向上します。

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

    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_open_cache=32 --read_buffer_size=8K \
               --net_buffer_length=1K &
    

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

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

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

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

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

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

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

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

6.5.3. クエリオプティマイザパフォーマンスの管理

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

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

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

  • optimizer_prune_level変数はオプティマイザに、各テーブルにアクセスされた行数の見積もりに基づくプランをスキップさせます。経験上この種類の「推測」は最適プランを見過ごすことはほとんどなく、劇的にクエリをコンパイルする時間を減少させます。(optimizer_prune_level=1) のオプションがデフォルトなのはこのためです。ただし、オプティマイザがより適したクエリプランを見逃したと思う場合は、クエリのコンパイルにかなりの時間を要するリスクを伴いますが、このオプション(optimizer_prune_level=0) は停止できます。この発見的方法を使用しても、オプティマイザは、指数的なプランの数を検索します。

  • optimizer_search_depth変数はオプティマイザが各不完全プランの「」をどのくらい見通すかを示すほか、さらに拡張が必要かを評価します。optimizer_search_depthのさらに小さい値は、マグニチュード順序あるいはクエリのコンパイルに要する時間が劇的に減少します。例えば、12, 13, もしくはそれ以上のテーブルのクエリは、optimizer_search_depthがクエリ内のテーブル数に近い場合、 コンパイルするのに数時間、時には数日間を容易に必要とします。同時に、3か4と等価のoptimizer_search_depthでコンパイルされた場合、オプティマイザは同じクエリでは1分以下でコンパイル可能な場合があります。optimizer_search_depthにとってリーズナブルな値が不明確な場合、変数を0に設定することで、オプティマイザに自動的に値を決定させることができます。

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

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

  • pgccを使用し、すべてを -O6でコンパイルした場合、mysqldサーバは gcc2.95.2と比較して 1% 速度が上がる。

  • 動的にリンクした場合(-staticなし)は、結果が Linux 上で 13% 遅くなった。クライアントアプリケーションには動的リンクの MySQL ライブラリを使用できることに注意する。これは、サーバのパフォーマンス上重大である。

  • 同一ホスト上で実行されるクライアントからサーバへの接続で、Unix ソケットファイルではなく、TCP/IP で接続すると、7.5% パフォーマンスが遅くなった(Unixではlocalhostに接続する場合、MySQL ではデフォルトでソケットファイルが使用される)。

  • TCPクライアントからサーバへの TCP/IP 接続で別のホストにあるリモートサーバに接続した場合、100M イーサネットによる接続でも、同一ホスト上のローカルサーバに接続した場合と比較して、8–11% 遅くなった。

  • 暗号化した接続(内部 SSL サポートによるすべてのデータの暗号化)を使用してベンチマークテストを実行した場合、パフォーマンスが 55% 遅くなった。

  • --with-debug=fullでコンパイルすると、ほとんどのクエリが 20% 遅くなる。 一部のクエリはかなり長くかかった(たとえば MySQL ベンチマークは 35% の速度低下)。--with-debug(=fullなしで)を使用すると、この速度低下は 15% で済む。--with-debug=fullでコンパイルされた mysqldバージョンは、--skip-safemallocオプションで起動すると実行時のメモリチェックを無効化できる。この場合の最終的な結果は、--with-debugで構成した場合に非常に近くなる。

  • Sun UltraSPARC-IIe, Forte 5.0 は、gcc3.2 より 4% 速度が上がった。

  • Sun UltraSPARC-IIe, Forte 5.0 では、64 ビットモードより 32 ビットモードのほうが 4% 速かった。

  • gcc2.95.2 for UltraSPARC にオプション -mcpu=v8 -Wa,-xarch=v8plusaを付けてコンパイルすると、パフォーマンスが 4% 改善した。

  • Solaris 2.5.1, MIT-pthreads は、単一プロセッサ上で Solaris ネイティブスレッドより 8–12% 遅かった。CPU の負荷が増加するとこの差はさらに拡大する。

  • フレームポインタ -fomit-frame-pointerまたは -fomit-frame-pointer -ffixed-ebpなしで gccを使用して Linux-x86 でコンパイルすると、mysqldが 1–4% 速くなった。

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

6.5.5. MySQL でのメモリの使用

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

  • The キーバッファ(変数 key_buffer_size)はすべてのスレッドで共有される。サーバが使用するこれ以外のバッファは必要に応じて割り当てられる。 項6.5.2. 「サーバパラメータのチューニング」を参照してください。

  • それぞれの接続はいくつかのスレッド固有領域を使用します。以下にそれらの領域およびどの変数がサイズをコントロールするかをリストアップします。

    • スタック(デフォルト192KB, 変数thread_stack)

    • 接続バッファ(変数net_buffer_length)

    • 結果バッファ(変数net_buffer_length)

    接続バッファと結果バッファ(ともにnet_buffer_lengthで与えられるサイズで始まる)は必要に応じて max_allowed_packet まで動的に拡張される。結果バッファは各SQLステートメントの後net_buffer_lengthに縮小される。クエリの実行中は現在のクエリ文字列のコピーも割り当てられる。

  • すべてのスレッドで同じベースメモリが共有される。

  • スレッドが必要ない場合、それに割り当てられたメモリはリリースされ、スレッドがスレッドキャッシュに戻るまでシステムに返されます。この場合、メモリは割り当てられた状態のままです。

  • MySQL 5.1.4以前では、圧縮されたMyISAMテーブルのみがメモリーマップされました。MySQL 5.1.4以降では、myisam_use_mmapシステム変数は全てのMyISAMテーブルに対してメモリマッピングを可能にするために、1に設定できます。 項4.2.3. 「システム変数」.

  • テーブルの順次スキャンを行う要求はそれぞれ、read buffer(変数 read_buffer_size)を割り当てる。

  • レコードを ``ランダムな'' 順序で読み取る場合(ソート後など)、random-read bufferが割り当てられディスクシークが回避される(変数 read_rnd_buffer_size)。

  • 結合はすべて 1 回の受け渡しで実行され、ほとんどの結合はテンポラリテーブルを使用せずに実行される。テンポラリテーブルのほとんどはメモリベース(HEAP)テーブルである。レコード長の大きなテンポラリテーブル(すべてのカラム長の合計として算出)や BLOBカラムが含まれるテンポラリテーブルはディスク上に格納される。

    メモリ内のheap テーブルのサイズがtmp_table_sizeを超えた場合、 MySQLはディスクベース MyISAMテーブルに変更されることで自動的に処理される。 この問題を回避するには、tmp_table_sizeオプションを mysqldに設定するか、クライアントプログラムで SQL_BIG_TABLESを設定することで、テンポラリテーブルのサイズを拡張する。 項12.5.3. 「SET 構文」を参照してください。

    MySQL Enterprise MySQL Network Monitoring and Advisory Service購読者はテンポラリテーブルサイズがtmp_table_sizeを超えたときに警告を受けます。実際のテーブル使用に基づいてtmp_table_sizeの最適値のアドバイスを提供します。MySQL Network Monitoring and Advisory Serviceの詳細については、http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

  • ソートを実行する要求のほとんどで、ソートバッファおよび結果セットサイズに応じた 0 から 2 つのテンポラリファイルが割り当てられる。項B.1.4.4. 「Where MySQL Stores Temporary Files」を参照してください。

  • 解析および計算のほとんどすべてが、ローカルメモリストアで実行される。小さいアイテムにはメモリオーバヘッドが不要で、通常の低速メモリの割り当ておよび解放は回避される。メモリは、予測外の規模の文字列の場合のみ割り当てられ、これは、malloc()および free()で実行される。

  • 開かれるMyISAMテーブルにはそれぞれ 1 回開かれ、データファイルは、同時実行スレッドごとに 1 回開かれる。同時スレッドのそれぞれに対して、テーブル構造、各カラムのカラム構造、サイズ 3 × Nのバッファが割り当てられる(Nは、レコードの最大長、ただし BLOBカラムは計算外)。BLOBカラムは、5 から 8 バイトに BLOBデータの長さを加算したバイト数を使用する。MyISAMストレージエンジンは、内部使用のための追加レコードを 1 つ使用する。

  • BLOBカラムがあるテーブルのそれぞれで、大きな BLOB値を読み込むためにバッファが動的に拡張される。テーブルをスキャンする場合は、最大 BLOB値と同じ大きさのバッファが割り当てられる。

  • 使用中テーブルすべてのハンドラ構造がキャッシュに保存され、FIFO 形式で管理される。一般にキャッシュには 64 のエントリがある。テーブルが同時に 2 つの実行スレッドで使用されている場合、キャッシュにはそのテーブルのエントリが 2 つ配置される。項6.4.8. 「MySQL でのテーブルのオープンとクローズの方法」を参照してください。

  • FLUSH TABLESコマンド(または mysqladmin flush-tablesステートメント)によって、使用中でないテーブルすべてが閉じられ、現在実行中のスレッドの終了時に使用中のテーブルすべてが閉じられるように指定される。これで効率的に使用中メモリに空きを作ることができる。 FLUSH TABLESは全てのテーブルが閉じられるまで返されない。

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

6.5.6. MySQLの DNS の使用

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

  • オペレーティングシステムがスレッドセーフの gethostbyaddr_r()gethostbyname_r()の呼び出しをサポートしている場合、スレッドではこれを使用してホスト名の解決が実行される。

  • オペレーティングシステムがスレッドセーフの呼び出しをサポートしていない場合、スレッドでは相互排除ロックを行い、代わりに gethostbyaddr()gethostbyname()が呼び出される。この場合、他のスレッドでは最初のスレッドが相互排除ロックを解除するまでホスト名キャッシュ内のホスト名を解決できなくなることに注意する。

--skip-name-resolvemysqldオプションを指定して起動すると、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を開始します。

6.6. ディスク関連の問題

  • ディスクシークはパフォーマンスに対する大きなボトルネックである。この問題は、データが拡大し、効率的なキャッシュが実行不能になるほど大きくなるにつれて明白になる。大規模データベースで、事実上ランダムにデータにアクセスする場合、読み取りでは最低 1 回、書き込みでは最低 2 回のディスクシークが必要になることがわかる。この問題を最小にするには、シーク回数を減らすようにディスクを使用する。

  • 複数のディスクに対してファイルをシンボリックリンクするか、ストライピングを行って、利用可能なディスクスピンドル数を増加する(およびそれによるシークのオーバヘッドを軽減する)。

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

      MyISAMテーブルの場合、通常のデータディレクトリ内の位置から別のディスクへのインデックスファイルやデータファイルのシンボリックリンクを行う(ストライピングも可能)。これによって、ディスクが他の用途に使用されていなければ、シークと読み取り時間がいずれも改善される。 項6.6.1. 「シンボリックリンクの使用」を参照してください。

    • ストライピング

      ストライピングは、ディスクが多数ある場合に、第 1 ブロックを第 1 ディスクに、第 2 ブロックは第 2 ディスクに、第 Nブロックは(N MOD number_of_disks)ディスクにといった配置を意味する。これにより、通常のデータサイズがストライプサイズより小さい(または完全に一致している)場合にパフォーマンスが大幅に改善する。ストライピングはオペレーティングシステムとストライプサイズへの依存性が非常に高いため、ストライプサイズをさまざまに変えながらアプリケーションのベンチマークを行う。項6.1.5. 「独自のベンチマークの使用」を参照してください。

      ストライピングの速度はパラメータによって大きく異なることに注意する。ストライピングパラメータの設定方法とディスク数によって桁ちがいの差異が発生する。ランダムアクセスか順次アクセスのいずれの最適化を行うかの選択が必要なことに注意する。

  • 信頼性を高めるため、RAID 0+1(ストライピング + ミラーリング)の使用が必要な場合、N個のドライブのデータの保持に2× N個のドライブが必要になる。財務上の余裕がある場合はこれが最適な選択肢になる。しかし、処理の効率化にボリューム管理ソフトウェアへの投資が必要なこともある。

  • データの種類の重大性に応じて RAID レベルを変える選択も推奨される。たとえば、ホスト情報やログなどの重要度の高いデータは、RAID 0+1 または RAID Nディスクに格納し、再生成が可能で重要性が中程度のデータは RAID 0 ディスクに格納することなどができる。RAID Nは、パリティビットの更新に時間がかかるため、書き込みが多いと問題になる場合がある。

  • Linux の場合、hdparmを使用してディスクのインタフェースを構成することでパフォーマンスを大幅に改善できる(負荷時に 100% 改善できることも珍しくない)。次の例は、MySQL(およびその他の多数のアプリケーション)に非常に適した hdparmオプションである。

    hdparm -m 16 -d 1
    

    上記を使用した場合のパフォーマンスと信頼性は使用ハードウェアに依存するため、hdparmの使用後はシステムを総合的にテストするように強く推奨する。詳細については、hdparmのページを参照。hdparmの使用が適切でない場合は、ファイルシステムの損傷が発生することがあるため、テストの際はあらかじめすべてのバックアップを取っておく必要がある。

  • データベースが使用するファイルシステムのパラメータを設定することもできる。

    If ファイルへの最終アクセス時を認識する必要がない(データベースサーバでは重要度が低い)場合、-o noatimeオプションを使用してファイルシステムをマウントできる。これで、ファイルシステムの i ノードへの最終アクセス時間の更新がスキップされ、一部のディスクシークを回避できる。

    多数のオペレーティングシステムで、-o asyncオプションを使用してディスクをマウントし、ファイルシステムが非同期で更新されるように設定できる。使用しているコンピュータが適度に安定している場合は、信頼性を損なわずにさらにパフォーマンスを改善できる(Linux ではこのフラグがデフォルトでオンになっている)。

6.6.1. シンボリックリンクの使用

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

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

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

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

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/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/my_symlink.cを変更することで可能となります。その場合、以下のステートメントを参照してください。

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

このステートメントを以下に変えます。

if (1)

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

realpath()の呼び出しの機能が完全でないシステムではテーブルのシンボリックリンクを行わないでください。(少なくとも、Linux と Solaris では realpath()がサポートされています)。 SHOW VARIABLES LIKE 'have_symlink'ステートメントを発行することで、ユーザのシステムがシンボリックリンクをサポートするかチェックできます。.

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

MySQL 4.0 でのMyISAMテーブルのシンボリックリンクの処理は次のように機能します。

  • データディレクトリには常にテーブル定義ファイル(.frm) 、データファイル(.MYD) およびインデックスファイル(.MYI) がある。データファイルとインデックスファイルは、別の場所に移動し、データディレクトリ内でシンボリックリンクによって置換できる。定義ファイルはこれができない。

  • データファイルとインデックスファイルは、それぞれ独立して別のディレクトリにシンボリックリンクを作成できる。

  • シンボリックリンクは、オペレーティングシステムレベル(mysqld が実行されていない場合)、または SQL で CREATE TABLEDATA DIRECTORYおよび INDEX DIRECTORYオプションを指定して実行できる。 詳しくは項12.1.8. 「CREATE TABLE 構文」を参照してください。あるいは、シンボリックリンクはln -sを使用してコマンドラインから手動で行えますが、 これはmysqldが作動していない場合に限ります。

  • myisamchkは、データファイルやインデックスファイルのシンボリックリンクを置き換えない。myisamchk はリンクで指し示されているファイルに直接作用する。テンポラリファイルはすべてデータファイルやインデックスファイルが配置されているのと同じディレクトリに作成されます。 同様のことがALTER TABLEOPTIMIZE TABLE、そしてREPAIR TABLEステートメントでいえます。

  • :シンボリックリンクを使用しているテーブルをドロップすると、シンボリックリンクとシンボリックリンクが指しているファイルの両方がドロップされる。このため、rootとして mysqldを実行すべきではなく、また、MySQL データベースディレクトリへの書き込みアクセスをユーザに許可するべきでもない。

  • ALTER TABLE ... RENAMEを使用してテーブルの名前を変更し、テーブルを他のデータベースに移動しない場合、データベースディレクトリのシンボリックリンクの名前が新しい名前に変更され、データファイルとインデックスファイルもそれに従って名前が変更される。

  • ALTER TABLE ... RENAMEを使用してテーブルを別のデータベースに移動すると、テーブルが別のデータベースディレクトリに移動され、それまであったシンボリックリンクとそれが指すファイルが削除される(新規テーブルのシンボリックリンクは作成されない)。

  • シンボリックリンクを使用していない場合は、mysqld--skip-symbolic-linksオプションを指定して使用し、確実に誰もデータディレクトリの外でファイルのドロップや名前の変更を行う mysqldを使用できないようにする。

サポートされていないテーブルシンボリックリンクオペレーション

  • ALTER TABLEでは DATA DIRECTORYINDEX DIRECTORY テーブルオプションが無視される。

  • BACKUP TABLERESTORE TABLEではシンボリックリンクが考慮されない。

  • .frmファイルはシンボリックリンクにすることがまったくできない(前述のように、データファイルとインデックスファイルのみシンボリックリンクにできる)。 これを実行した場合(シノニム作成など)、正しい結果が得られなくなる。 MySQL データディレクトリにデータベース db1があり、このデータベースにはテーブル tbl1が、db1ディレクトリには tbl1を指すシンボリックリンク tbl2があるとする。

    shell> cd /path/to/datadir/db1
    shell> ln -s tbl1.frm tbl2.frm
    shell> ln -s tbl1.MYD tbl2.MYD
    shell> ln -s tbl1.MYI tbl2.MYI
    

    あるスレッドで db1.tbl1が読み取られ、別のスレッドで db1.tbl2が更新されると、問題が発生する。

    • クエリキャッシュが「欺かれ」(tbl1が更新されていないと判断され、最新でない結果が返される)。

    • tbl2に対するALTERステートメントもエラーになる。

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

シンボリックリンクはデフォルトで全てのWindowsサーバのため有効になっています。これにより、シンボリックリンクを設定することでデータベースディレクトリを別のディスクにセットすることが可能となります。リンクを設定するプロシージャは異なりますが、データベースシンボリックリンクがUnix上で作動するのと似ています。シンボリックリンクが必要ない場合、--skip-symbolic-linksオプションを使用して動作しないように設定できます。

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

たとえば、MySQL データディレクトリが C:\mysql\dataで、データベース fooD:\data\fooに配置したい場合。このプロシージャを使用してsymlinkをセットしてください。

  1. この作業には D:\data\fooディレクトリが存在している必要があります。 言い換えると、すでに fooという名前のデータベースディレクトリがデータディレクトリにある場合、これを D:\dataに移動しないとシンボリックリンクが有効にならないことになります(問題を回避するため、データベースディレクトリの移動時はサーバを実行しないでください)。

  2. D:\data\foo\パスネームを含むテキストファイルC:\mysql\data\foo.symを作成してください。

この後、fooデータベースで作成される全てのテーブルはD:\data\foo内に作成されます。MySQLデータディレクトリに同じ名前のディレクトリがある場合、シンボリックリンクは使用されません。注意してください。.


powered by SEO.CUG.NET