このページは機械翻訳したものです。

8.4.1 データサイズの最適化

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

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

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

• テーブルカラム

• 行フォーマット

• インデックス

• 結合

• 正規化

テーブルカラム

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

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

行フォーマット

• InnoDB テーブルは、デフォルトでは DYNAMIC 行形式を使用して作成されます。 DYNAMIC 以外の行フォーマットを使用するには、innodb_default_row_format を構成するか、CREATE TABLE または ALTER TABLE ステートメントで ROW_FORMAT オプションを明示的に指定します。

  COMPACT、DYNAMIC および COMPRESSED を含む行形式のコンパクトファミリでは、一部の操作で CPU 使用率を増やすコストで行の記憶領域が削減されます。 ワークロードが、キャッシュヒット率とディスク速度によって制限される通常のワークロードであれば、速くなる可能性があります。 CPU 速度によって制限されるまれな例では、遅くなることがあります。

  行形式のコンパクトファミリでは、utf8mb3 や utf8mb4 などの可変長文字セットを使用する場合にも、CHAR カラムの格納が最適化されます。 ROW_FORMAT=REDUNDANT では、CHAR(N) は N×文字セットの最大バイト長を占有します。 多くの言語は主にシングルバイトの utf8 文字を使用して記述できるため、固定記憶域の長さによって領域が無駄になることがよくあります。 行フォーマットのコンパクトファミリでは、InnoDB は、末尾の空白を削除することで、N の範囲内の可変量の記憶域を N×これらのカラムの文字セットの最大バイト長に割り当てます。 一般的な場合にインプレース更新を容易にするために、記憶域の最小長は N バイトです。 詳細は、セクション15.10「InnoDB の行フォーマット」を参照してください。

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

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

インデックス

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

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

• 長い文字列のカラムの最初の文字数に一意の接頭辞がある可能性が高い場合は、カラムの左端に索引を作成するための MySQL サポートを使用して、この接頭辞のみを索引付けすることをお薦めします (セクション13.1.15「CREATE INDEX ステートメント」 を参照)。 短いインデックスほど速くなるのは、必要なディスク領域が少ないだけでなく、インデックスキャッシュでのヒットが多くなり、そのためにディスクシークが少なくなるためでもあります。 セクション5.1.1「サーバーの構成」を参照してください。

結合

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

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

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

正規化

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

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