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

このセクションでは、クエリー処理速度を向上するためのさまざまな多くのヒントを示します。

• 接続のオーバーヘッドを回避するには、データベースに対して永続的な接続を使用します。永続的な接続を使用できないため、データベースに対して多くの新しい接続を開始する場合、thread_cache_size 変数の値の変更が必要になることがあります。セクション8.11.2「サーバーパラメータのチューニング」を参照してください。

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

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

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

• ARCHIVE テーブルで発生したデータ圧縮問題を修正するには、OPTIMIZE TABLE を使用できます。セクション15.5「ARCHIVE ストレージエンジン」を参照してください。

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

• 場合によって、ほかのカラムの情報に基づいて「ハッシュされた」カラムを導入することが役立つ場合があります。このカラムが短く、十分に一意で、インデックスが設定されている場合は、多数のカラムに「広範な」インデックスを使用するより大幅に高速化できる可能性があります。MySQL では、この追加カラムをきわめて簡単に使用できます。

  SELECT * FROM tbl_name
    WHERE hash_col=MD5(CONCAT(col1,col2))
    AND col1='constant' AND col2='constant';

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

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

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

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

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

• 大きなログテーブルから統計を収集する必要がある場合は、ログテーブル全体をスキャンするのではなく、サマリーテーブルを使用します。サマリーの管理は、「ライブ」で統計を計算しようとする場合よりはるかに高速になるはずです。状況が変わった (ビジネス上の決定に応じて) 場合、ログから新しいサマリーテーブルを再生成する方が、実行中のアプリケーションを変更するより早いです。

• 可能であれば、統計レポートに必要なデータが、ライブデータから定期的に生成されるサマリーテーブルからのみ作成される「ライブ」または「統計」として、レポートを分類します。

• カラムにデフォルト値があることを利用します。挿入する値がデフォルト値と異なる場合にのみ、明示的に値を挿入します。これにより、MySQL が実行する必要がある解析が減り、挿入速度が向上します。

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

• 通常、すべてのデータを非冗長に維持しようとしてください (データベース理論で第 3 正規形と呼ばれるものを順守します)。ただし、高速化を図るために、情報を複製したり、サマリーテーブルを作成したりすることが有利になる状況もあります。

• ストアドルーチンや UDF (ユーザー定義関数) は特定のタスクでパフォーマンスの向上に適切な方法である場合があります。詳しくは、セクション20.2「ストアドルーチン (プロシージャーと関数) の使用」およびセクション24.3「MySQL への新しい関数の追加」を参照してください。

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

• 1 つの SQL ステートメントで多数の行を格納するには、複数行の INSERT ステートメントを使用します。(これは比較的移植可能な技法です。)

• 大量のデータをロードするには LOAD DATA INFILE を使用します。これは INSERT ステートメントを使用するより高速になります。

• テーブルの各行を 1 つの一意の値で識別できるように、AUTO_INCREMENT カラムを使用します。

• ときどき OPTIMIZE TABLE を使用して、動的形式の MyISAM テーブルによる断片化を回避します。セクション15.2.3「MyISAM テーブルのストレージフォーマット」を参照してください。

• 可能であれば、MEMORY テーブルを使用して、高速化を図ります。セクション15.3「MEMORY ストレージエンジン」を参照してください。Web ブラウザで Cookie が有効にされていないユーザーに対して最後に表示されたバナーに関する情報など、頻繁にアクセスされる非クリティカルデータには MEMORY テーブルが役立ちます。ユーザーセッションも、揮発状態データを処理するために、多くの Web アプリケーション環境で使用できるもう 1 つの代替方法です。

• Web サーバーでは、イメージとその他のバイナリアセットが通常、ファイルとして格納されているはずです。つまり、データベース内にはファイル自体ではなく、ファイルへの参照のみを格納します。ほとんどの Web サーバーは、データベースコンテンツよりファイルのキャッシュに優れているため、ファイルの使用は一般に高速です。

• 対応するカラムに基づいた結合が速くなるように、異なるテーブル内の同一の情報を持つカラムは同一のデータ型を持つように宣言するべきです。

• カラム名が簡単になるようにします。たとえば、customer というテーブルでは customer_name ではなく name のカラム名を使用します。名前をほかの SQL サーバーに移植できるようにするため、18 文字より短くすることを考慮します。

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

• 数値データを使用している場合、多くの場合にテキストファイルにアクセスするより、ライブ接続を使用して、データベースから情報にアクセスする方が高速です。データベース内の情報はテキストファイルよりコンパクトなフォーマットで格納される可能性が高いため、それへのアクセスにかかわるディスクアクセスが少なくなります。さらに、テキストファイルを解析して、行とカラムの境界を見つける必要がないため、アプリケーション内のコードも節約できます。

• レプリケーションは、特定の操作でパフォーマンスの向上を実現できます。クライアントの取得をレプリケーションサーバー間で分散して、負荷を分割できます。バックアップを作成する間のマスターの速度低下を避けるため、スレーブサーバーを使用して、バックアップを作成できます。第17章「レプリケーション」を参照してください。

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

• SELECT ステートメントの優先度を挿入より高くしたい場合、サポートされる非トランザクションテーブルに、INSERT LOW_PRIORITY を使用します。

• キューに割り込んで先に取得されるようにするには、サポートされる非トランザクションテーブルに SELECT HIGH_PRIORITY を使用します。つまり、書き込みの実行を待機している別のクライアントがある場合でも、SELECT が実行されます。

  LOW_PRIORITY と HIGH_PRIORITY はテーブルレベルのロックのみを使用する非トランザクションストレージエンジンにのみ効果があります。