8.6.1 MyISAM クエリーの最適化

MyISAM テーブルのクエリーを高速化するためのいくつかの一般的なヒント:

• MySQL がクエリーをより適切に最適化できるようにするには、テーブルにデータがロードされたあとに、それに対して ANALYZE TABLE を使用するか、または myisamchk --analyze を実行します。これにより、同じ値がある平均行数を示す各インデックスパートの値を更新します。(一意のインデックスの場合、これは常に 1 です。)MySQL はこれを使用して、非定数式に基づいて、2 つのテーブルを結合する際に選択するインデックスを決定します。SHOW INDEX FROM tbl_name を使用し、Cardinality 値を調べることで、テーブル分析の結果を確認できます。myisamchk --description --verbose はインデックスの分布情報を示します。

• インデックスに従ってインデックスとデータをソートするには、myisamchk --sort-index --sort-records=1 を使用します (インデックス 1 でソートすると仮定して)。インデックスに従って順番にすべての行を読み取りたいと考える一意のインデックスがある場合、これはクエリーを高速にする適切な方法です。この方法で大きなテーブルをはじめてソートするときは、長い時間がかかることがあります。

• 頻繁に更新される MyISAM テーブルに対する複雑な SELECT クエリーを避け、リーダーとライターの競合のために発生するテーブルロックの問題を回避するようにしてください。

• MyISAM は同時挿入をサポートしています。テーブルのデータファイルの途中に空きブロックがなければ、ほかのスレッドがテーブルから読み取るのと同時に新しい行をそれに INSERT できます。これを実行できることが重要な場合、行の削除を避けるようにテーブルを使用することを考慮してください。別の可能性は、テーブルの大量の行を削除したあとに OPTIMIZE TABLE を実行して、テーブルをデフラグすることです。この動作は concurrent_insert 変数の設定によって変更されます。行を削除したテーブルにも新しい行を強制的に追加 (したがって同時挿入を許可) できます。セクション8.10.3「同時挿入」を参照してください。

• 頻繁に変更される MyISAM テーブルでは、すべての可変長カラム (VARCHAR、TEXT、および BLOB) を避けるようにします。テーブルに 1 つしか可変長カラムが含まれていない場合でも、テーブルは動的行フォーマットを使用します。第15章「代替ストレージエンジン」を参照してください。

• 一般に、行が大きくなるためだけに、1 つのテーブルを異なるテーブルに分割することは有益ではありません。行へのアクセスで、もっとも大きくパフォーマンスに打撃を与えるものは、行の先頭バイトを見つけるために必要なディスクシークです。データが見つかったあとは、ほとんどの最新のディスクで、大多数のアプリケーションに十分な速度で行全体を読み取ることができます。テーブルを分割することがかなりの違いをもたらす状況は、固定の行サイズに変更できる動的行フォーマットを使用している MyISAM テーブルの場合か、またはテーブルを著しく頻繁にスキャンする必要があるが、ほとんどのカラムには必要でない場合だけです。第15章「代替ストレージエンジン」を参照してください。

• 通常 expr1、expr2、... の順で行を取得する場合は、ALTER TABLE ... ORDER BY expr1, expr2, ... を使用します。テーブルを大幅に変更したあとにこのオプションを使用することで、パフォーマンスを向上できることがあります。

• 多数の行の情報に基づいたカウントなど、結果を頻繁に計算する必要がある場合、新しいテーブルを導入し、リアルタイムでカウンタを更新する方が望ましいことがあります。次のような形式の更新はきわめて高速です。

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

  これは、テーブルレベルのロック (単一ライターと複数リーダー) しかない MyISAM のような MySQL ストレージエンジンを使用する場合に、きわめて重要です。また、この場合に行ロックマネージャーが実行する必要があることは少ないため、ほとんどのデータベースシステムでパフォーマンスが向上します。

• データが書き込まれるタイミングを知る必要がない場合は、MyISAM (またはその他のサポートされる非トランザクションテーブル) に INSERT DELAYED を使用します。多くの行を 1 回のディスク書き込みで書き込むことができるため、これにより、挿入の全体の影響が少なくなります。

  注記

  MySQL 5.6.6 現在、INSERT DELAYED は非推奨であり、将来のリリースで削除されます。代わりに INSERT (DELAYED を付けない) を使用してください。

• 定期的に OPTIMIZE TABLE を使用して、動的フォーマット MyISAM テーブルの断片化を防ぎます。セクション15.2.3「MyISAM テーブルのストレージフォーマット」を参照してください。

• DELAY_KEY_WRITE=1 テーブルオプションを使用して MyISAM テーブルを宣言すると、テーブルが閉じられるまで、ディスクにフラッシュされないため、インデックスの更新が速くなります。短所は、そのようなテーブルが開いている間に、何かによってサーバーが強制終了させられた場合に、--myisam-recover-options オプションを使用してサーバーを実行するか、サーバーを再起動する前に myisamchk を実行して、テーブルが問題ないことを確認する必要があることです。(ただし、この場合でも、キー情報は常にデータ行から生成できるため、DELAY_KEY_WRITE を使用しても何も失われないはずです。)

• MyISAM インデックスでは、文字列の前後のスペースが自動的に圧縮されます。セクション13.1.13「CREATE INDEX 構文」を参照してください。

• アプリケーションでクエリーや応答をキャッシュしてから、多くの挿入や更新をまとめて実行することによって、パフォーマンスを向上できます。この操作中にテーブルをロックすることで、すべての更新後にインデックスキャッシュが 1 回だけフラッシュされます。同様の結果を得るために、MySQL のクエリーキャッシュを利用することもできます。セクション8.9.3「MySQL クエリーキャッシュ」を参照してください。