8.4.1 データサイズの最適化

ディスク上の領域を最小にするようにテーブルを設計します。これにより、ディスクに対して読み取りおよび書き込みされるデータの量が減ることで、大幅な改善が見られます。内容がクエリー実行中にアクティブに処理される間、テーブルが小さいほど、通常必要なメインメモリーの量は少なくなります。テーブルデータの領域の削減により、インデックスも小さくなり、高速に処理できます。

MySQL は多数のさまざまなストレージエンジン (テーブル型) と行フォーマットをサポートしています。テーブルごとに、使用するストレージとインデックス設定方法を決定できます。アプリケーションに適切なテーブル形式を選択することで、大幅なパフォーマンスの向上が得られることがあります。第15章「代替ストレージエンジン」を参照してください。

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

テーブルカラム

• 可能なかぎりもっとも効率的 (最小) のデータ型を使用します。MySQL にはディスク領域とメモリーを節約する多くの専用の型があります。たとえば、可能な場合は、小さなテーブルを取得するために、小さな整数型を使用します。MEDIUMINT カラムが使用する領域は 25% 少ないため、MEDIUMINT は多くの場合に INT より適切な選択肢です。

• 可能な場合は、カラムを NOT NULL として宣言します。それにより、インデックスを適切に使用し、各値が NULL であるかどうかをテストするためのオーバーヘッドがなくなることで、SQL の操作が速くなります。カラムあたり 1 ビットでいくらかのストレージ領域も節約します。テーブルで実際に NULL 値が必要な場合、それらを使用します。単にすべてのカラムで NULL 値を許可するデフォルトの設定を避けます。

行フォーマット

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

  コンパクト行フォーマットの存在により、行のストレージ領域が約 20% 減少しますが、一部の操作で CPU の使用が増加する犠牲を伴います。ワークロードが、キャッシュヒット率とディスク速度によって制限される通常のワークロードであれば、速くなる可能性があります。CPU 速度によって制限されるまれな例では、遅くなることがあります。

  コンパクト InnoDB 形式では UTF-8 データを含む CHAR カラムが格納される方法も変わります。UTF-8 エンコード文字の最大長が 3 バイトであるとして、ROW_FORMAT=REDUNDANT では、UTF-8 CHAR(N) は 3 × N バイトを占有します。多くの言語は主にシングルバイト UTF-8 文字を使用して書くことができるため、固定のストレージ長は多くの場合に領域を無駄にします。ROW_FORMAT=COMPACT 形式では、InnoDB は N から 3 × N バイトの範囲のストレージの可変容量を、必要に応じて末尾のスペースを取り除いて、これらのカラムに割り当てます。最小のストレージ長は、一般的な場合にインプレース更新を容易にする N バイトとして保持されます。

• テーブルデータを圧縮形式で保存することで、さらに領域を最小にするには、InnoDB テーブルを作成する際に ROW_FORMAT=COMPRESSED を指定するか、既存の MyISAM テーブルに対して、myisampack コマンドを実行します。(InnoDB 圧縮テーブルは読み取り可能で書き込み可能ですが、MyISAM 圧縮テーブルは読み取り専用です。)

• MyISAM テーブルで、可変長カラム (VARCHAR、TEXT、あるいは BLOB など) がない場合は、固定サイズ行フォーマットが使用されます。これは高速ですが、いくらか領域を無駄にすることがあります。セクション15.2.3「MyISAM テーブルのストレージフォーマット」を参照してください。CREATE TABLE オプション ROW_FORMAT=FIXED によって、VARCHAR カラムがある場合でも、固定長の行を必要としていることを伝えることができます。

インデックス

• テーブルのプライマリインデックスは可能なかぎり短くしてください。これにより、各行の識別が容易になり効率的になります。InnoDB テーブルの場合、主キーカラムは、各セカンダリインデックスエントリに複製されるため、多数のセカンダリインデックスがある場合に、短い主キーによって、かなりの領域が節約されます。

• クエリーパフォーマンスを向上するために必要なインデックスのみを作成します。インデックスは取得には有効ですが、挿入および更新操作を遅くします。ほとんどカラムの組み合わせに対して検索することによって、テーブルにアクセスする場合、カラムごとに個別のインデックスを作成するのではなく、それらに対して単一の複合インデックスを作成します。インデックスの最初の部分は、もっとも使用されるカラムにするべきです。テーブルから選択する場合に、常に多くのカラムを使用する場合、適切なインデックスの圧縮を取得するため、インデックスの最初のカラムは、もっとも重複の多いカラムにするべきです。

• 長い文字列カラムで、最初の数文字に一意のプリフィクスがある可能性が高い場合、MySQL のカラムの左端の部分へのインデックスの作成のサポート (セクション13.1.13「CREATE INDEX 構文」を参照してください) を使用して、このプリフィクスのみにインデックスを設定することをお勧めします。短いインデックスほど速くなるのは、必要なディスク領域が少ないだけでなく、インデックスキャッシュでのヒットが多くなり、そのためにディスクシークが少なくなるためでもあります。セクション8.11.2「サーバーパラメータのチューニング」を参照してください。

結合

• 状況によって、頻繁にスキャンされるテーブルを 2 つに分割することで、メリットがある場合があります。これは特に、それが動的形式テーブルで、テーブルのスキャン時に、関連行を見つけるために使用できる小さな静的形式テーブルを使用できる場合に当てはまります。

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

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

正規化

• 通常、すべてのデータを非冗長に維持しようとしてください (データベース理論で第 3 正規形と呼ばれるものを順守します)。名前や住所などの長い値を繰り返す代わりに、それらに一意の ID を割り当て、複数の小さなテーブルで必要なだけこれらの ID を繰り返し、結合句で ID を参照して、クエリーでテーブルを結合します。

• たとえば、大きなテーブルからすべてのデータを解析するビジネスインテリジェンスシナリオなどで、ディスク領域やデータの複数のコピーを維持する保守コストより、速度の方が重要である場合、正規化ルールを緩和して、情報を複製したり、サマリーテーブルを作成したりして、速度を向上させることができます。